How SQL Changed Twilio Segment

For the first couple years at Segment we solved most of our biggest business problems using regular ol’ gut instinct. Everyone was in constant conversation with our initial customers, so it was okay to use intuition to choose our pricing model or prioritize new features. But as we’ve grown, we needed a more analytical approach. About six months ago we started using SQL for analysis, and that’s completely changed how we answer questions about our business.

Direct access to SQL has made us much faster at answering questions, and more teammates are digging in themselves instead of relying on intuition or being blind to siloed information. There are a number of tools on our platform that have made querying and sharing our data around the company much easier.

This article will show you how we use Segment SQL + Mode to speed up our decision-making. We use Mode to query and build reports that can be run by anyone on the team (even non-technical people!). There are a ton of great SQL tools out there, so make sure you find the right SQL stack for your business case.

The Dark Ages

A few months back we had a ton of questions to answer, but no single repository of data to help us answer them. Our support data lived in Zendesk, payment data in Stripe, production database in Mongo and API usage in Redis. In order to answer anything, we had to write 150+ lines of javascript.

The cumbersome process of pulling these data sources together made it too costly to analyze. Zendesk and Stripe’s out-of-the-box reporting was good, but we really needed to combine data across sources. So although the raw data was available, our teams were essentially still in the data dark ages.

For example, our conversion funnel took weeks to get right, and we were constantly bothering our engineers for changes. For our sales team to function, we wrote a plugin for our chat-bot hermes to generate a .csv file of all of our enterprise clients. Another plugin would tell us how many users were using different integrations on our platform. Here’s the code:

Each one of these one-off programs distracted our engineering teams from building a great product, and blocked product, success, sales, and marketing teams from making decisions as they waited for data.

The SQL Renaissance

When we started building Segment SQL, it was meant to be a data warehouse for our largest customers, who were already loading raw Segment data into Redshift. But during alpha we gave it a spin for ourselves and found that even for companies at our size (25 people at the time), getting our data into SQL was really powerful.

We were already tracking events like this:

And all of a sudden, these events were also available to us in SQL:

For the first time we could see how users went from signing up, to sending in support tickets, to upgrading subscriptions. We were able to visualize sign-up rates for users who read a blog post and submitted a help-desk ticket, or track average time spent on the pricing page. Life was pretty good.

But we still found that running queries and building reports was restricted to a small group of SQL power users.

Those who didn’t know SQL stood in increasingly long bread lines. While some teams were able to query the data they needed, reports remained siloed. We were also wasting time re-creating queries that had been written by other teams, or resolving differences in queries across teams.

Adding Mode to the Stack

Enter Mode. Mode makes it easy to create on-the-fly analysis and share it across the organization. As part of our New Year’s resolutions, many around the office decided to get better at SQL. Mode’s SQL School was an amazing place to start (and our marketing team also recommends Periscope’s SQL for marketers!). Mode even came onsite to give us some hands on training.

With SQL, questions that previously took an engineer a ton of time to answer could now be answered with a simple query. And with Mode, these queries are stored as reports that can be shared and re-run by anyone in the organization with a Mode account.

In a matter of weeks, we went from a handful of reports to over a hundred. And now it wasn’t just a few of us creating them, it was every team: from marketing, to partners, to success, to product. Teams were no longer waiting on engineers to access the data, they were querying and making decisions completely by themselves. And better yet, teams were seeing each other’s queries and building on them to make more powerful ones.

This is our actual Mode feed from the time of writing. I think it gives a good snapshot in just how integral SQL (and sharing queries with Mode) has become to our team!

A Few Favorites

As an analyst at Segment, I’m always interested to see what reports other teams are creating. Every once in a while I’ll troll our team’s new reports page to see what people are learning. Here’s a bunch of my favorites:

Growth

Remember the conversion funnel that took us a ton of engineering hours to get? Well, here it is using regular-old SQL! Not only does it save us time, but SQL allows us to do custom funnel analysis that’s tricky to do with out-of-the-box tools. For example, we can easily add caveats to exclude users that received an invite or submitted a support ticket!

Product

We love our product team’s interactive user flow analysis, which let’s you get a feel for how users are spending time in our app. We borrowed this one from the Mode playbook!

Partners

This is a snapshot of the number of accounts using each of our integrations, which helps our Partners team prioritize partner outreach and co-marketing. While it looks like a simple-enough bar chart, this report used to take a bunch of engineering hours to produce!

Success

Our success team has done some analysis combining ticket data from Zendesk with things like subscription plans and usage to help understand our gross margin. Above is a peek at our daily ticket volumes by business customers. Our amazing success team will never pass up an opportunity to help a customer, even on the weekends!

SQL + Collaboration = Speed

SQL has helped our teams build, learn and share faster, which is helping us move quicker and make more informed decisions. We’ve found that Mode’s Github-like approach to SQL encourages a ‘build-upon-the-past’ mentality that saves us all – not just the analysts – a ton of time.

If you’re just getting started with SQL or interested in learning more, our partners have a ton of great content to get going. Mode’s SQL playbook is a good jumping off point for some common patterns, or if you’re just getting started with Segment + SQL, our SQL partners JackDB, Looker, Chartio, and Xplenty have some good guides to get you set up!