Subscribe to get a new lesson each week!

When to Use SQL for Analysis

Last week, we shared some easy ways to get your hands on your raw customer data. This week, we’ll talk about one of the main ways of analyzing raw data—SQL—and when it’s appropriate to use it.

Out-of-the-box tools can get most of the job done

Hopefully, you’re owning (or at least have access to) your raw customer data, even if you don’t need to directly query it just yet. If your product or business is young, you probably should leave it be and answer all of your questions from a set of event analytics and reporting tools. This is preferable, because you want to move fast and let those tools do all of the heavy lifting!

Services like Google Analytics, Mixpanel, Kissmetrics, and Indicative are great for basic product and marketing questions. They’ll help you analyze trends, usage patterns, cohort and funnel analysis, etc. With these types of tools you can answer questions like:

  • Which sites send us the highest converting traffic?
  • Where are the key points people drop off in my funnel?
  • Which features are customers using? Which ones are they not?
  • Did people who signed up around our funding announcement act the same as other cohorts?

It’s not trivial to figure these questions out in SQL, and the out-of-the-box analytics tools will get you your answers much faster — it just takes a few clicks.

But at what point does it make sense to use SQL? The section below enumerate reasons why people migrate off these hosted tools to SQL databases / BI tools like Tableau, Looker, Mode, Chartio, and Periscope.

Specific, granular questions about your business

There comes a point in every business where the trend lines, funnel analyses, and cohort reports just don’t provide the granularity you are seeking.

Analyzing behavioral cohorts and customized sessions

For example, say you want to do a cohort analysis by a particular behavior. How do people who take this action compare to others? Is there one behavior that indicates increased activation and retention? You really need SQL to figure that out. Many folks in the industry call this analysis finding your aha moment.

How about if your business has a unique definition of a “user session”? For example, an ecommerce site and a run tracking app probably have different preferences for setting sessions. Most out-of-the-box tools choose a way to measure sessions and give you very limited options to customize it. In SQL, you can define sessions the way that makes the most sense for your business.

Drilling down into customer behavior

SQL also provides you full range to get as granular as possible with your analysis. Here is a line of questioning that can be answered easily with SQL, but that would be difficult for managed tools to address:

  • How many users created an account on my website, added a product to their cart, and then completed that purchase on their phone?
  • What links and referrers did most people in this group come from?
  • How many users added additional items to a cart on their phone?
  • Who also saw a push notification about completing their order?

Building custom dashboards

In addition to answering ad-hoc questions about your business and product, you can also use SQL to power real-time dashboards with completely customizable metrics that are a lot more meaningful to your business than standard metrics.

Though you can get far with hosted analytics tools (they’re always getting better), not every company is the same. Each company measures its own success by its own self-defined metrics (hopefully not vanity ones!).

Here are some examples of metrics that we powered with SQL:

  • # signups that are “blog assists” — Our blog doesn’t push people hard to sign up. We want them to read the content, like it, read more, and connect it with Segment. Therefore, we measure when a user viewed a blog before signing up (a complicated query for assists) rather than direct signups from blog posts.
  • # connected accounts — A connected account means that a customer is sending data to Segment and off to a certain number of integrations. These accounts are much more engaged and get more value out of the product, so we use this metric to more accurately measure activation.

Join in other data sources with your customer data

For many companies, customer event data becomes more valuable for analysis when used with operational or other kinds of business data. For example, SQL lets you connect marketing channel information with deals closed in a CRM to measure channel effectiveness, or product purchases with a product inventory to help manage retail logistics.

Here are some quick examples of companies who have transitioned to SQL.

Case Study: Segment

We have an inbound sales team at Segment, with most of our inbound leads come from existing users who want features offered on our business tiers. We think of our sales process as opt-in: we’ve all been on the other end of an unsolicited sales call and know that is really hard to get right.

Instead, we give our customers the ability to “raise a hand” to kick off the sales process. Some of these “hand-raises” are explicit, like sending an email asking for a sales demo or chatting with our sales team on Olark. But some of the signals are more subtle: turning on a business-level integration or a sudden spike of API calls beyond their plan. This means that our salesforce needs to look a lot more like a mission control center than a CRM.

We used iFrames, Periscope, and our Redshift, to build real-time dashboards right inside of Salesforce.

Periscope lets us turn custom queries into dashboards easily. Our sales team cares about usage data (higher usage = getting more value from Segment).

Here is a dashboard from Periscope:

Periscope dashboard

These dashboards sit on the Salesforce lead pages via iFrames. Now our sales team can be proactive about helping customers when they see usage ramp up!

More information about setting this up can be found here.

Case Study: Chubbies

Chubbies is a retro-fashion company (“like your pop’s trunks back in the day”) that loves data. They started off simply: Google Analytics with Shopify. But as they grew, they began realizing the limitations of their analytics setup.

“When you have a million different people working in a million different pieces of excel files or data dumps, you’re just prone to error”, says co-founder Rainer Castillo.

They decided to pipe all of their data—including website data, backend shopping data, and inventory management data—into one central location, using RJ Metrics to query and create dashboards.

Now that they own their data, it simplifies and centralizes access to numbers. There is a lot more confidence and trust in their analysis since there is a single, consistent data source. Additionally, they’re able to ask highly granular questions about their customers and business.

The team of “quantjocks” can query the raw data to adjust supply of sizes and colors so that it better represents the demand of the customer base.

Owning and analyzing all of their data is now “table stakes at this point”.

More information can be found at the Chubbies, RJ Metrics Case Study.

Case Study: Tilt

Tilt, a crowd funding platform, was looking for an easy way to measure growth and encourage a data-aware culture. Their goal was to allow everyone data access to explore opportunities. In their early days, they would build reports with Excel, but quickly found this to be limiting and inconvenient.

Tilt’s team decided to put all the data into SQL and partnered with ChartIO for all dashboard and reporting. ChartIO has allowed their team to work more quickly with data. The custom dashboarding (all driven by SQL queries) also provide a great way for anyone to explore insights in an unwiedly set of information. They were able to create a Tilt Social Index for colleges.

The self-service access to data and analysis has empowered their team to move quickly and realize insights via exploration.

More information can be found at the Tilt, ChartIO Case Study.

Onwards and upwards!

Well, that wraps up the Course 101 of Analytics Academy!! Again, the aim of this course is to introduce you to the foundational concepts in your approach to customer data and analytics. We learned to:

  • Use analytics for learning
  • Settle on a few important metrics
  • Understand the breadth of things you can do with customer data
  • Decide the most important things to track from the beginning
  • Use your top metrics to decide what tools to use
  • Store your own data to prepare for future growth
  • Use SQL to answer super granular questions

Next up, we’ll have three courses to level up your analytics knowledge now that you’ve got the basics down.

Have something you want to learn? Tweet @segment for article suggestions!

Collecting customer data, Course 201

Garbage in, garbage out—the quality of your analysis and decision making is only as good as the quality of your data. We’ll go over some tips to make sure that you’re collecting the right data so you can focus on your business.

What you’ll learn:

  • techniques for keeping your tracking data clean
  • setting up a tracking plan and sticking to it
  • naming conventions and why they are important
  • the data you should track client vs. server side
  • common mistakes in analytics implementation

Choosing the right stack, Course 301

There is a growing number of customer data tools out there, making choosing which tool to use overwhelming. From predictive analytics to marketing automation, we’ll help you discover tools for all sorts of use cases.

What you’ll learn:

  • frameworks and criteria for evaluating different types of tools
  • the major players in the analytics, email, messaging, and retargetting spaces
  • the major players in the optimization, customer success, and attribution categories
  • how companies of different industries and sizes have built their stack
  • tactics for evaluating vendors

Leveraging raw data, Course 401

What you’ll learn:

  • how to choose the right database for your needs
  • the steps to building your own ETL pipeline
  • popular technologies in infrastructure management
  • how to integrate multiple types of data sources
  • how to cultivate a data-forward culture

To make sure you’re moving on up from data dunce to Dean’s List, subscribe to Analytics Academy! We’ll notify you when the next courses are ready.

Start your free email course today!