Additional tips to format data in your Signal File Upload spreadsheets

[stevestormoen]

When you upload Signal insights or Marketing Data values from your phone call data to your Invoca account using our file upload features, sometimes it takes extra effort to format your data correctly. This can be for a number of reasons, including formatting edits made automatically by your spreadsheet software.

Here are a few common practices you can use to avoid formatting errors.
 

Remove external formatting in your spreadsheets

Before saving your spreadsheet, select all cells and use the “remove formatting” option in your spreadsheet program.
 

Timestamp Formats

The following formats are supported for both call_start_time and occurred_at_time.
 
All examples below correspond to a date time of 11 April 2016 at 1 PM Pacific Time.
 
Epoch: 10 digit timestamp in UTC seconds since 1/1/70, also known as Unix time_t. UTC milliseconds since 1/1/70 (which is the default in Javascript) are also supported, i.e. a 13 digit start time.
Example (10 digits): 1460404800
Example (13 digits): 1460404800000

Compressed: 17 digit timestamp always parsed in UTC.
Format: YYYYMMDDHHMMSSsss
Example: 20160411130000000

ISO 8601: Timestamp with +/- UTC offset or Z to indicate time is in UTC. Milliseconds are optional.
Format: YYYY/MM/DDTHH:MM:SS.sss+hh:mm
Example (UTC offset of +3 hours): 2016/04/11T23:00:00.000+03:00
Example (UTC offset of -7 hours): 2016/04/11T13:00:00.000-07:00
Example (UTC): 2016/04/11T20:00:00.000Z
Example (no milliseconds): 2016/04/11T13:00:00-07:00

Excel Compatible: Timestamp parsed in the timezone of the oauth token‘s associated network. Milliseconds are optional.
Format: YYYY/MM/DD HH:MM:SS.sss AM/PM
Example: 2016/04/11 13:00:00.000 PM
Example (no milliseconds): 2016/04/11 13:00:00 PM

 

Note:
Note: The file upload user interface includes a timezone dropdown feature, which allows users to select which timezone this format is parsed in. Use the following to format Excel date cells: yyyy/mm/dd hh:mm:ss.000 AM/PM.

Other fields

calling_phone_number: Format this column as follows: ANI in E.164 format +country national_number; example: ‘+1 8885551212’.
advertiser_id_from_network and advertiser_campaign_id_from_network: These columns are only used in certain cases to restrict the Signal insights to calls from certain partners or campaigns. In most situations you can leave these columns blank.
 

Bundled calls

If reporting a Signal on a bundled campaign using transaction_id as the matching identifier, it’s recommended that you report on the final/destination campaign. If reporting a Signal on a call_record_id associated to a bundled call, the Signal will be applied to the last call leg associated to the child campaign in the organization reporting the Signal, this will allow payouts to propagate appropriately.

If the transaction_id or call_record_id's are not available, matching is still possible by using a combination of call_start_time, with an additional optional parameters (i.e. call duration or calling phone number).
 

Related:
• Importing call conversions via Signal file upload 
• How to import Marketing Data values using file upload 
• How to import recordings of your phone calls to create and train Signal AI