In our lesson about owning your own data, we shared some easy ways to get your hands on your raw customer data.
In this lesson, 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, and Amplitude 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 the following questions:
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 enumerates reasons why people migrate off these hosted tools to SQL databases/BI tools like Looker, Mode, and Periscope. If learning SQL seems like too much of a lift, you can also lean on BI tools like Tableau and Chartio that specialize in providing drag-and-drop reporting on top of SQL databases.
When to use SQL for analysis
SQL is best used when you need to answer specific, granular questions about your business that out-of-the-box tools aren’t equipped to answer. For example, SQL is useful if you want to organize customer behavior into different cohorts and analyze it, combine data from multiple sources or platforms for integrated data analysis, or build your own customized dashboards with unique metrics.
There comes a point in every business where the trend lines, funnel analysis, and cohort reports just don't provide the granularity you are seeking: that's when SQL comes in.
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 e-commerce 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 access 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?
Joining 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.
Salesforce + Marketo = What campaigns drive the most revenue?
Zendesk + Product = What's our average number of tickets per users per plan?
Transactions + Web = Which browsing behaviors lead to the most purchases?
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 power with SQL:
number of qualified opportunities by channel: We combine Salesforce opportunity data with website and Marketo data to identify which channels and campaigns drove the most leads.
number of integration health score: To understand the state of integrations at Segment, we assign each integration a health score that combines the last time the integration was updated (Github), how many support tickets it has (Zendesk), total revenue associated (Stripe and Salesforce), and total users (Segment). This gives us a nice and easy view to prioritize integration updates.
Select from sql.knowledge
SQL gives you the ability to easily manipulate data and tables to answer any question you could have about your customers and business. If you're just getting started with advanced analytics, here are some helpful resources and courses to get up to speed on SQL quickly.
While out-of-the-box tools are easy to use and they help you get most of your analytics work done, they won't always provide the granularity you need to understand your business. With SQL, if you can dream it, you can query it!
Intro to analytics