Articles in this section

How do I construct data for a FTP integration?

Avatar
Bethany Priode
  • Updated
Follow

FTP Data Construction: Daily Totals vs Records

There are two methods for constructing data: Daily Totals and Records.

For examples below assume three FTP Upload metrics have already been created within Ambition: Calls, Emails, Appointments.

Use the Daily Totals method if you will be processing data into metrics within your system and then sending the daily values.

Use the Records method if you will be transmitting individual records to Ambition where you will build out business logic within our UI to convert data into daily values.

Every row/record must contain:

  1. Unique Employee Identifier (Email, Extension, ID)
  2. Date OR Date/Time(Standard YYYY-MM-DD hh:mm:ss Format)

Each row/record is keyed to a specific date, not a given week or month.

  1. If you are sending data via CSV (as opposed to JSON) you must include every header even if there are no values for given time period.
  2. If Records format, each row/record has to have a Unique Record Identifier.

Note: By default, we assume Date/Times are sent in local timezones to Ambition. However, if you would like to send Date/Times in UTC, contact gethelp@ambition.com to aid in setup.

 

Daily Totals

In this example we have Jason with 11 Calls, 12 Emails, 3 Appointments and Sarah with 6 Calls, 9 Emails, 10 Appointments for August 17th, 2020.

CSV Example

email,date,calls,emails,appointments
jason@yourcompany.com,2020-8-17,11,12,3
sarah@yourcompany.com,2020-8-17,6,9,10

 

JSON Example

[
  {
    "email": "jason@yourcompany.com",
    "date": "2020-8-17",
    "calls": 11,
    "emails": 12,
    "appointments": 3
  },
  {
    "email": "sarah@yourcompany.com",
    "date": "2020-8-17",
    "calls": 6,
    "emails": 9,
    "appointments": 10
  }
]

 

You can upload multiple days at once, popular for backfilling historical data for analytical purposes or reconciling previous values due to backdating issues.

email,date,calls,emails,appointments 
jason@yourcompany.com,2020-8-13,32,3,1
jason@yourcompany.com,2020-8-14,1,3,0
jason@yourcompany.com,2020-8-15,5,8,4 
jason@yourcompany.com,2020-8-17,11,12,3

 

Daily Totals Uniqueness Constraint

When sending Daily Totals data the uniqueness constraint will be the unique employee identifier and datefields which effectively means that an employee can only have one value per Metric for a specific date. This allows you to automatically overwrite/update data.

Example

Ambition processes the following data at 9:00AM

email date calls
jason@yourcompany.com  2020-8-17 2 

Ambition then processes the following data at 9:05AM

email date calls
jason@yourcompany.com  2020-8-17

3

Ambition will overwrite the previous value of 2 with a new value of 3.

We recommend setting up your system to send files every X minutes/hours to automatically overwrite previous data.

 

Records

Let's assume we've created an additional Metric, Revenue.

In this example we have eight CRM records owned by Jason. Ambition is responsible for applying user-defined logic to determine if these records count towards Metrics... in this case Jason is credited with 2 Calls, 1 Email, 1 Appointment, $700.11 Revenue for August 17th, 2020.

Metric Logic

# Call Logic
"type" EQUALS "call"
"status" EQUALS "completed"
"date" EQUALS "created_date"

# Email Logic
"type" EQUALS "email"
"status" EQUALS "completed"
"date" EQUALS "created_date"

# Appointment Logic
"type" EQUALS "appointment"
"status" EQUALS "completed"
"date" EQUALS "appointment_date"

# Revenue Logic
"type" EQUALS "booking"
"status" EQUALS "completed"
"date" EQUALS "bookings_date"
SUM "revenue"
 CSV
email,created_date,record_id,type,status,appointment_date,booking_date,revenue
jason@yourcompany.com,2020-8-14 8:23:11,abc123,appointment,completed,2020-8-17,,
jason@yourcompany.com,2020-8-17 7:59:23,abc124,call,completed,,,
jason@yourcompany.com,2020-8-17 8:01:45,abc125,call,completed,,,
jason@yourcompany.com,2020-8-17 8:31:34,abc126,email,completed,,,
jason@yourcompany.com,2020-8-17 10:44:33,abc127,appointment,not started,2020-8-18,,
jason@yourcompany.com,2020-8-17 11:01:09,abc128,appointment,not started,2020-8-20,,
jason@yourcompany.com,2020-7-6 13:12:57,abc129,booking,completed,,2020-8-17,300.11
jason@yourcompany.com,2020-7-27 14:04:19,abc130,booking,completed,,2020-8-17,400.00

 

Records Uniqueness Constraints

When sending Record-based data you will need to map a unique record identifier to a) avoid duplicate data and b) retroactively update data.

Example

Ambition processes the following data on 8/17/2020:

JSON

[
  {
    "email": "jason@yourcompany.com",
    "date": "2020-8-17 8:31:11",
    "record_id": "10002acda13",
    "booking_date": "2020-8-17",
    "revenue": 1000
  }
]

Jason now has $1,000 Revenue for 8/17/2020.

Unfortunately a 50% refund must be issued and Ambition processes the following data on 8/24/2020:

JSON

[
  {
    "email": "jason@yourcompany.com",
    "date": "2020-8-24 11:10:43",
    "record_id": "10002acda13",
    "booking_date": "2020-8-17",
    "revenue": 500
  }
]

Assuming that record_id was established as the unique field Ambition would automatically update Jason's Revenue value on 8/17/2020 for record_id 10002acda13 to be $500 as opposed to $1,000.

Related articles

Comments

0 comments

Article is closed for comments.