Try it with connectors from Salesforce, Greenhouse, Shopify and more.
Funnel analysis crops up everywhere. It can be used to analyze success of sales and marketing teams, a customer's progression through a website, or the efficiency of a recruiting pipeline. A famous example in the startup world is David McClure’s AARRR (pirate) framework for product metrics. In general, it is a useful approach to analyzing just about any process that involves multiple steps and attrition.
Some Example Data
Let’s walk through an example involving some mock sales data. In our sales model, we have the following progression of events:
- Lead - a potential client has been identified
- Marketing Qualified - the marketing team has determined that a potential client is a good fit, based on broad characteristics
- Sales Qualified - the sales team has determined that potential client is a good fit, based on conversations with the client
- Negotiated - the sales team has discussed pricing and features with a potential client
- Customer - the sales team has closed new business with client
- Renewed - the client has renewed their contract for the next period
The particulars of a funnel can vary. In the actual Salesforce schema, you will likely be looking at the “opportunity_history” table, and examining steps with names like “contacted,” “qualified,” and “won.” What is most important to making this model work is that the expected value of a client increases as they progress down the funnel, while the raw number of clients decreases.
We’ll use a table with the following fields:
- account_id - a unique identifier for client accounts
- date - date on which an event occurred
- event - type of event, from the above progression
| account_id | date | event |
|---|---|---|
| 000033 | 1/5/2018 | lead |
| 000063 | 2/28/2019 | negotiated |
| 000050 | 2/5/2018 | sales_qualified |
| 000001 | 6/18/2018 | lead |
| 000053 | 11/12/2018 | lead |
| 000091 | 2/14/2019 | negotiated |
The “events” table contains only accounts that were opened in fiscal year 2018. Cohort analysis is a subject for another post.
Queries
The simplest thing you might consider doing is this:
| event | event_count |
|---|---|
| lead | 153 |
| marketing_qualified | 71 |
| sales_qualified | 29 |
| negotiated | 13 |
| registered | 5 |
| renewed | 2 |
A straightforward aggregation, and a great start for getting the absolute magnitudes of each step. But what if we want percentages?
| event | event_count | event_percentage |
|---|---|---|
| lead | 153 | 1.0 |
| marketing_qualified | 71 | 0.4650 |
| sales_qualified | 29 | 0.1895 |
| negotiated | 13 | 0.0850 |
| registered | 5 | 0.0327 |
| renewed | 2 | 0.0131 |
We’ve turned the first query into a common table expression, and then referred to a specific value from it. And what if we want to measure the percentage of prospective clients who are lost at each step.? We add a window function called “lag” that returns the preceding data for each row.
Start for free
Join the thousands of companies using Fivetran to centralize and transform their data.
Funnel Analysis and Conversion Metrics in SQL
Funnel Analysis and Conversion Metrics in SQL
Try it with connectors from Salesforce, Greenhouse, Shopify and more.
Funnel analysis crops up everywhere. It can be used to analyze success of sales and marketing teams, a customer's progression through a website, or the efficiency of a recruiting pipeline. A famous example in the startup world is David McClure’s AARRR (pirate) framework for product metrics. In general, it is a useful approach to analyzing just about any process that involves multiple steps and attrition.
Some Example Data
Let’s walk through an example involving some mock sales data. In our sales model, we have the following progression of events:
- Lead - a potential client has been identified
- Marketing Qualified - the marketing team has determined that a potential client is a good fit, based on broad characteristics
- Sales Qualified - the sales team has determined that potential client is a good fit, based on conversations with the client
- Negotiated - the sales team has discussed pricing and features with a potential client
- Customer - the sales team has closed new business with client
- Renewed - the client has renewed their contract for the next period
The particulars of a funnel can vary. In the actual Salesforce schema, you will likely be looking at the “opportunity_history” table, and examining steps with names like “contacted,” “qualified,” and “won.” What is most important to making this model work is that the expected value of a client increases as they progress down the funnel, while the raw number of clients decreases.
We’ll use a table with the following fields:
- account_id - a unique identifier for client accounts
- date - date on which an event occurred
- event - type of event, from the above progression
| account_id | date | event |
|---|---|---|
| 000033 | 1/5/2018 | lead |
| 000063 | 2/28/2019 | negotiated |
| 000050 | 2/5/2018 | sales_qualified |
| 000001 | 6/18/2018 | lead |
| 000053 | 11/12/2018 | lead |
| 000091 | 2/14/2019 | negotiated |
The “events” table contains only accounts that were opened in fiscal year 2018. Cohort analysis is a subject for another post.
Queries
The simplest thing you might consider doing is this:
| event | event_count |
|---|---|
| lead | 153 |
| marketing_qualified | 71 |
| sales_qualified | 29 |
| negotiated | 13 |
| registered | 5 |
| renewed | 2 |
A straightforward aggregation, and a great start for getting the absolute magnitudes of each step. But what if we want percentages?
| event | event_count | event_percentage |
|---|---|---|
| lead | 153 | 1.0 |
| marketing_qualified | 71 | 0.4650 |
| sales_qualified | 29 | 0.1895 |
| negotiated | 13 | 0.0850 |
| registered | 5 | 0.0327 |
| renewed | 2 | 0.0131 |
We’ve turned the first query into a common table expression, and then referred to a specific value from it. And what if we want to measure the percentage of prospective clients who are lost at each step.? We add a window function called “lag” that returns the preceding data for each row.
Related blog posts
Start for free
Join the thousands of companies using Fivetran to centralize and transform their data.
.svg)
.svg)
.svg)