How to use spreadsheet data for call campaigns

When creating scripts for call campaigns, you can use spreadsheet data containing phone numbers. This can be not only the numbers themselves, but also additional information about customers, such as their city of residence, interests, loyalty index, doctor’s appointment time, order number, etc.

caution

The spreadsheet data can only be fetched during the call campaign.

Please note that when you test your script in the test widget, you will not get data from the spreadsheet. The error that you will see is the expected behavior, since information from the spreadsheet can only be retrieved from the phone channel.

How to retrieve spreadsheet data

To display the contents of a phone number spreadsheet, you need to format such a spreadsheet. We will use the column names to display the desired information from the spreadsheet. For example, let’s create the following spreadsheet:

caution

Column names must not contain spaces. For example, instead of Loyalty index you need to write Loyalty_index

Add a system variable

To get data from a spreadsheet, you need to use $rawRequest.originateData.payload.column_name system variable in your script. The column name the data of which you want to use must be entered into the variable after the dot.

For example, if you have a name column, then in notepad you would have: $rawRequest.originateData.payload.name

Below is an example of $rawRequest values:

$rawRequest.originateData.payload.phone
$rawRequest.originateData.payload.name
$rawRequest.originateData.payload.country
$rawRequest.originateData.payload.region
$rawRequest.originateData.payload.plan
$rawRequest.originateData.payload.age
$rawRequest.originateData.payload.loyalty_index

Use the system variable containing the name of the desired column in the Text block:

In this case, when calling the number 141105555555, the bot will say: “Hello, John!”. And when calling the number 141155501234, the bot will say “Hello, Alex!”.

Why does testing fail

When you use the $rawRequest values listed above in your script, you will get an error when testing the script in the test widget. This is because data from the spreadsheet can only be retrieved from the phone channel.

Therefore, we recommend adding $rawRequest values to display the name when you are ready to start your outbound call campaign. You can retrieve the username and other information from the phone number spreadsheet only during the call process. In the test widget, this won’t work.

If you are currently testing your script and need to test any third-party functionality, postpone using $rawRequest until you can start your real call campaign and test it on the phone channel.

$rawRequest content examples

In the given spreadsheet, the $rawRequest variable contains the following data:

{
    "event": "accepted",
    "trunkId": 0,
    "accountId": 247854563,
    "caller": "141105555555",
    "extension": "141105555555",
    "channel": "SIP/0-0000001f",
    "originateData": {
        "callTaskId": 12754,
        "callId": 12757,
        "botToken": "TufcgsZw:d82fe12af68ff365455d1365e33fc7f3487699c1",
        "payload": {
            "name": "John",
            "plan": "Standard",
            "phone": "141105555555",
            "region":"Glasgow",
            "country": "USA",
            "age": "43",
            "loyalty_index": "2"
        }
    },
    "headers": {}
}

After the user’s response, $rawRequest will also contain the text of the user’s message:

{
    "event": "asr",
    "trunkId": 0,
    "accountId": 247854563,
    "caller": "141105555555",
    "extension": "141105555555",
    "channel": "SIP/0-00000021",
    "data": {
        "cause": "OK",
        "text": "yes",
        "resourcePath": null,
        "callRecordingFile": "247854563/2019-08-02/0/79105555555/14:10:17.602-Bna",
        "interrupted": false
    },
    "originateData": {
        "callTaskId": 12762,
        "callId": 12765,
        "botToken": "TufcgsZw:d82fe12af68ff365455d1365e33fc7f3487699c1",
        "payload": {
            "name": "John",
            "plan": "Standard",
            "phone": "141105555555",
            "region":"Glasgow",
            "country": "USA",
            "age": "43",
            "loyalty_index": "2"
        }
    },
    "webhookData":{
        "sessionId": "247854563-247854563-YAP-1776693371-79105555555.b4bd33d0-5343-41c1-8ae5-a189e1e5240d"
    }
}