Build the future of communications.
Start building for free

Getting started with Twilio and Segment Sources: Using JavaScript and PostgreSQL to track conversion events

5Hi8Jee5P7kWA5KluywmbfMfVUjIj4XFazti91atZ-1vJ6OjqSbsfm5NNQjY4ETAc6duyiXJyIwbOJvjLCXjcjHrD3ZC1Z8hBHzrE_0EeL7635fP7EGuetgZ4J2Qh41ev619Oxq-1

With Segment Sources and Twilio, you can get a holistic view into your customers’ journey through your Twilio voice or SMS app. In just a few clicks you can sync your Twilio logs to a data warehouse for advanced analysis.

twilio sync.png

Combine these logs with data generated by other customer touch points like CRM, helpdesk, payments, email and in-app data to give you a complete view of your customer experience.

To demonstrate this, we are going to modify the Node version of the Twilio call tracking tutorial adding an additional lead conversion measure that will be stored using Segment. We’ll analyze that new measure using SQL and a data warehouse.

In order to build the sample app you’ll need Node.js installed as well as Twilio, Segment, and Heroku accounts. Additionally, you’ll need a tool for visualizing the Lead Conversion data. We’ll show you how to use Wagon, a free SQL editor for writing queries, visualizing results and sharing data and charts.

Start by cloning the call tracking tutorial from GitHub and following the setup instructions in the README. If you want more details on how the application works, check out the full call tracking tutorial walk-through.

Once you have the tutorial working buy a couple of test phone numbers in the app and try them out by calling them.

Adding Lead Conversion Tracking with Segment

With the basic call tracking application running let’s make the modifications to identify which phone numbers led to a “conversion” event. What defines a conversion event is up to you. For example, a conversion might be if the call leads to a sign up or a sale.

In order to record the conversion event we’ll use Segment to collect and route conversion event data to a data warehouse for later analysis.

If you don’t have a Segment account, you can create one here. Follow the instructions to create a Workspace.

In your Segment account, you’ll need to create a new Segment Source that is the “JavaScript Website”:

Screen Shot 2016-04-25 at 11.04.58 AM.png

When you connect the JavaScript Website you’ll have to provide names as such:
Screen Shot 2016-04-25 at 11.06.56 AM.png

Then, you’ll need to get the writeKey, which you’ll can find under “Settings” and then “Keys”:

segment-writekey.png

Open the .env file in the node project and add an additional environment variable for your Segment writeKey.  

SEGMENT_WRITE_KEY=SB3xxxxxx

Next, open config.js and add the code to define cfg.writeKey below where cfg.authToken:

cfg.writeKey = process.env.SEGMENT_WRITE_KEY;

And add the code to add cfg.writeKey to the array of configuration values:

var configured = [
  cfg.accountSid,
  cfg.authToken,
  cfg.mongoUrl,
  cfg.writeKey
].every(function(configValue) {
  if (configValue) {
    return true;
  }
});

Expose the writeKey to the dashboard Jade template by modifying controllers/dashboard.js:

LeadSource.find().then(function(leadSources) {
  return response.render(dashboard, {
    leadSources: leadSources,
    appSid: config.appSid,
    writeKey: config.writeKey
  });
});

We’ll use the writeKey in the rendered template to initialize the Segment JavaScript library.

Lastly, we’ll update the Jade views in our application so that it loads the Segment JavaScript snippet. Add the following JavaScript to the bottom of views/layout.jade, after the lines where jQuery and Bootstrap are being loaded:

script.
  !function(){var analytics=window.analytics=window.analytics||[];if(!analytics.initialize)if(analytics.invoked)window.console&&console.error&&console.error("Segment snippet included twice.");else{analytics.invoked=!0;analytics.methods=["trackSubmit","trackClick","trackLink","trackForm","pageview","identify","reset","group","track","ready","alias","page","once","off","on"];analytics.factory=function(t){return function(){var e=Array.prototype.slice.call(arguments);e.unshift(t);analytics.push(e);return analytics}};for(var t=0;t<analytics.methods.length;t  ){var e=analytics.methods[t];analytics[e]=analytics.factory(e)}analytics.load=function(t){var e=document.createElement("script");e.type="text/javascript";e.async=!0;e.src=("https:"===document.location.protocol?"https://":"http://") "cdn.segment.com/analytics.js/v1/" t "/analytics.min.js";var n=document.getElementsByTagName("script")[0];n.parentNode.insertBefore(e,n)};analytics.SNIPPET_VERSION="3.1.0";<br>  analytics.load('#{writeKey}');<br>  analytics.page();<br>  }}();

Next, open views/dashboard.jade and add a “Lead Converted” button that corresponds with each phone number:

each leadSource in leadSources
  tr
    td #{leadSource.number}
    td #{leadSource.description}
    td #{leadSource.forwardingNumber}
    td
      a.btn.btn-xs.btn-primary.converted-lead(href= ’#’ name= '#{leadSource.number}') Lead Converted
    td
      a.btn.btn-xs.btn-default(
        href= '/lead-source/'   '#{leadSource._id}'   '/edit') Edit

Finally, add the logic that will send the “Lead Converted” event to Segment as a block script at the bottom of the file (after where js/pieCharts.js is loaded):

script.
  var links = document.querySelectorAll('.converted-lead');
  for (var i = 0; i < links.length; i  ) {
    var phoneNumber = links[i].getAttribute('name');
    analytics.trackLink(links[i], 'Lead Converted', {
      phone_number: phoneNumber
    });
  }

This code uses the Segment library’s trackLink() function to send information about the conversion event to Segment.

Now, run the app:

$ nodemon .

Point your browser to http://localhost:3000. If you open the Network tab in your console, then click the “Lead Converted” button, you’ll see a request being sent to Segment:

lead_converted.gif
Now whenever you receive a call that ends up converting into a sign up or a sale you can click that button to let Segment know.

Next let’s dive into connecting a Twilio Source and the Postgres database together in our Segment workspace so we can look at which phone numbers are generating the most conversions.

Connecting Twilio Sources

Awesome! We are logging lead conversions but now need a way to connect that conversion data to Twilio call data. We can do that by allowing Segment to pull our Twilio call logs via a Twilio Source in the Segment Source Catalog:

Click the Twilio tile and hit the green “Connect” button.  You will be prompted to log in with your Twilio account credentials and give permission or Segment to read your Twilio logs. Configuring this Source allows Segment to sync the call logs into separate database which we will set up next.

Connecting a Postgres Data Warehouse

Segment Warehouses offers a “Bring Your Own Database” option which can sync various data sources into a Redshift or Heroku Postgres database. For our application we want Segment to sync both our Twilio call logs and the Lead Conversions data its storing for us into a single database.  

Since Heroku offers a free tier we’ll use that. If you don’t have a Heroku account, please sign up for free here. Once you have a Heroku account, provision a free cloud Postgres database. Click “Create database” and wait a few moments while one is created for you.

With the database created click through it and get the Postgres database credentials. It should look something like this:


You’re going to need these credentials in two places: one to connect to Segment and one to connect to your SQL editor.

First let’s connect the Postgres database to Segment Warehouses using the credentials provided by Heroku by following this guide.

Now that the Twilio call log and Segment lead conversion data is being synced into our database we just need a way to query it. This is where Wagon comes in.

Sign up and download their app onto your computer and connect the Postgres database using the same Heroku credentials.

Run this simple query to confirm that the data is there and everything is wired up:

select *
  from twilio.calls

The query will show you the list of calls made to your Twilio phone numbers:

twilio-calls-sql-results.png

Wewt! We now have a single database storing both our Twilio call logs and the lead conversions tracked via Segment. Having the data together will let us use both to begin to divide information about which of our phone numbers is performing the best.

Joining Lead Conversions and Calls

Our data is syncing into a single database but each source is still stored in separate tables.  We want to build some SQL queries that can tell us more about how our phone numbers are converting.  To do that we’ll start by a common table expression below that’ll create a table with call and lead data together, which will be used by all future queries in this section to build more complex queries.

with

leads as (
    select l.sent_at as converted_at,
        l.phone_number as number
        from call_tracking_js.lead_converted l
),

calls_with_conversion_information as (
    select *
        from twilio.calls c
        join leads l
        on l.number = c._to
        where l.converted_at >= c.received_at
        and l.converted_at <= c.received_at   c.duration::integer * interval '1 second'   30 * interval '1 minute'
        order by converted_at desc
)

Note that the twilio.calls table is joined with the leads table on the phone number and a window of time. In this case we’re using the duration of the call plus an additional 30 minutes, just in case.

To run this query as it stands append this SELECT query to the bottom:

select * from calls_with_conversion_information

Once you’ve got this initial set of queries working remove or comment out that last SELECT query as we’ll create some new more interesting queries in the next section.

Measuring Calls and Conversions by Week

One way to measure the performance of our phone numbers is a week-over-week comparison of conversions. This SELECT query does just that—a quick assessment at how many calls you’re getting and leads you’re converting overall.

To use this query paste it into your SQL Editor after the common table expression we created in the prior section.

select date_trunc('week', c.received_at) as week,
    count(l.converted_at) as conversions,
    sum(case when l.converted_at is null then 1 else 0 end) did_not_convert
    from twilio.calls c
    left join calls_with_conversion_information l
    on c.id = l.id
    group by week
    order by week desc

Running this query in Wagon will return a table, which can be manipulated easily in “Chart” mode so that we can visualize the query results. Fill out the settings on the left like this:

To get a chart like this (make sure the chart type is “Stacked Bar” chart):

The green bar indicates calls that did not result in a conversion, whereas the blue bar indicates calls that led to conversions. Now we can see at a glance which weeks saw more phone calls and more conversions.

Conversion Rate by Phone Number and by Week

Another great question to ask is which phone number has the highest conversion rate. Knowing this we might decided pour additional advertising dollars into the locations where we’ve placed that number.

The SQL to uncover this is below.  To use this query paste it into your SQL Editor after the common table expression we created earlier in the post.

select date_trunc('week', c.received_at) as week,
    c._to as phone_number,
    count(l.converted_at) as converted,
    sum(case when l.converted_at is null then 1 else 0 end) did_not_convert,
    count(l.converted_at)   sum(case when l.converted_at is null then 1 else 0 end) as total,
    ((count(l.converted_at)   0.0) / (count(l.converted_at)   sum(case when l.converted_at is null then 1 else 0 end))) as conversion_rate
    from twilio.calls c
    left join calls_with_conversion_information l
    on c.id = l.id
    group by week, phone_number
    order by week desc

Like in the prior section we can use the charting capabilities in Wagon to easily create a line chart from the query results.

Here are the proper settings to create the line chart:


Now you can see at a glance how each phone number has performed over time.

What next?

In this post we learned how easy it is to combine your Twilio call logs with custom events tracked by Segment in order to gain insights into business operations. By combining different data sources using a Segment Warehouse with a few simple SQL queries we can begin to ask and answer more complex questions about how our business is performing.

What are the questions that you need answered for your business? Are your text messages creating more engaged customers? Is your IVR experience losing you revenue?  

Using Segment to bring information sources together is the first step in beginning to find the answers you need.

Feel free to reach out if you have any questions or want to show off what you built!

For help with anything Twilio related reach out to:

For any questions on Segment you can contact:

  • friends@segment.com
Authors
Sign up and start building
Not ready yet? Talk to an expert.