Call tracking is a vital service for any marketer, and when it comes to call tracking, Twilio is the largest telephony infra structure provider in the market that can help a lot with that; however, Twilio is geared more towards developers, nontechnical users can not do much using Twilio and they have to go for a plug and pay services like, Dialogue Tech and Call rail.
In this post I will provide Twilio's nontechnical users with a solution to create a friendly dashboard using Google Sheets, which could be eventually used with Google Data Studio for better visualization.
Step 1:
Make sure you have a Twilio account with at least one active phone number that redirects to your own phone number, Twilio has a nice guide here that explains how to find and buy a phone number, after buying the phone number you can use Twilio's built in web-hook to redirect it to your own phone number (see below)
Just add your phone number to the end of the web-hook's URL: http://twimlets.com/forward?PhoneNumber=111-111-1111
You can buy multiple phone numbers and use each of them to track a different medium, like organic, paid, and others. In this tutorial you can find how to rotate phone numbers based on incoming traffic to your website, if you are using the the numbers on dedicated landing pages or other mediums like off-line, number's rotation will not be required as each number will be served only for one medium.
Step 2:
Find your Twilio's API credentials by clicking the gear icon at the top >> settings >> copy ACCOUNT SID and AUTH TOKEN
Step 3:
Replace your ACCOUNT SID and AUTH TOKEN in the code below that we will be using in Google Sheets:
function myFunction() {
var ACCOUNT_SID = "*********************************";
var ACCOUNT_TOKEN = "*********************************";
var findmedium = [
{ "number": "+1**********", "medium": "organic" },
{ "number": "+1**********", "medium": "cpc" },
{ "number": "+1**********", "medium": "others" }
];
// you do not need to edit anything below this line
function searchMD(findmedium, num){
for(var i= 0, L= findmedium.length; i<L; i++){
if(findmedium[i].number=== num) return findmedium[i].medium;
}
return '';
}
var numberToRetrieve = 10;
var hoursOffset = 0;
var options = {
"method" : "get"
};
options.headers = {
"Authorization" : "Basic " + Utilities.base64Encode(ACCOUNT_SID + ":" + ACCOUNT_TOKEN)
};
var url="https://api.twilio.com/2010-04-01/Accounts/" + ACCOUNT_SID + "/Calls.json?PageSize=" + numberToRetrieve;
var response = UrlFetchApp.fetch(url,options);
var theSheet = SpreadsheetApp.getActiveSheet();
function search(xyz) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
for(var i=2;i<12;i++){
var value = sheet.getRange(i, 6).getValue();
if(value == xyz){
var match = 1 ;
}
}
return match ;
}
var startColumn = 1;
var theRow = 2 ;
var dataAll = JSON.parse(response.getContentText());
for (i = dataAll.calls.length -1 ; i >= 0 ; i--) {
var found = search (dataAll.calls[i].sid) ;
var calldirection = dataAll.calls[i].direction ;
var midnight = new Date();
midnight.setHours(0, 0, 0, 0);
rowDate = dataAll.calls[i].date_created;
var theDate = new Date (rowDate);
theDate.setHours(theDate.getHours()+hoursOffset);
if (!found && calldirection == "inbound") {
theSheet.insertRowAfter(1);
theColumn = startColumn;
if(isNaN(theDate.valueOf())) {
theDate = 'Not a valid date-time';
theColumn++;
}
else {
theDate.setHours(theDate.getHours()+hoursOffset);
theSheet.getRange(theRow, theColumn).setValue(theDate);
theColumn++;
}
var callduration = dataAll.calls[i].duration/60 ;
callduration = +callduration.toFixed(2);
theSheet.getRange(theRow, theColumn).setValue(dataAll.calls[i].to);
theColumn++;
theSheet.getRange(theRow, theColumn).setValue(dataAll.calls[i].from);
theColumn++;
theSheet.getRange(theRow, theColumn).setValue(callduration);
theColumn++;
theSheet.getRange(theRow, theColumn).setValue(searchMD(findmedium, dataAll.calls[i].to)); //tw searchMD(findmedium, dataAll.calls[i].to) ;
theColumn++;
theSheet.getRange(theRow, theColumn).setValue(dataAll.calls[i].sid);
}
}
}
If you are using multiple phone numbers for different mediums replace the values in the findmedium array, otherwise just leave the code as is.
Step 4:
Create a new sheet in Google Sheets and name the first 6 columns in the top row as below (feel free to change the names to any other names keeping the same order)
Step 5:
In Google Sheet go tools >> script editor >> delete any existing codes and replace them with the codes from step 3
Step 6:
In Google Sheets script editor click on the clock icon to create a trigger >> Click + Add Trigger (bottom right corner) >> Time-driven >> Hour timer >> Every hour >> save
Once you do that, in few hours the data will start to show up in your sheet as below:
Having the call date in Excel will give you a lot of flexibility to manipulate and extract data, for more visualization read step 7 which will explain how to create a simple report in Google Data Studio.
Step 7:
Connect the sheet you created to Google Data Studio by going blank report >> Create data source >> Create new data source >> Google Sheets >> Choose the spread sheet created in step 4 >> Click connect
You should be able to see an empty Google Data Studio report with a grid, now Insert >> Time series >> Add to report
If everything works fine you should be able to see a report like below (a daily total calls duration report) :
There is a lot you can do to customize this report but I am not going to go through all of that, watch this video here to learn more.
Finally I want to say that this post was inspired by a great post provided by Twilio How to Receive SMS Messages into Google Sheets with Apps Script and Twilio and part of code was used in my post.
Please feel free to comment if you have any questions.
No Comments