In this tutorial, we will cover what business-to-business (B2B) funnel analysis is, why it’s important, and a step-by-step example of how to build a B2B funnel in SQL.
A sales funnel represents the journey a prospective customer takes on their way to a purchase. In business-to-business sales, the customer is the business or organization that will ultimately purchase the product or service, and engage in a business relationship with the vendor.
The key stages of the funnel are:
B2B Funnel analysis tracks each of these steps on the prospect's journey. Each organization will calculate these metrics slightly differently, and it’s highly likely that the calculation for these metrics will evolve as the company grows. That’s totally normal!
First it’s important to know the different types of leads that are created throughout this process.
Most companies store the information necessary to track the sales funnel in their CRM tool. However this data generally only shows the current state. To determine when different stages of the funnel occurred, the data needs to be pieced together either from the opportunity history data or from the dates recorded for each stage on the lead and opportunity record. In this tutorial, we will piece it together from the dates recorded. This tends to be the most straightforward approach, but can be misleading when opportunities don’t follow the standard order of stages (for example repeat different stages or skip stages). B2B Funnel Analysis requires a single primary source:
In this section, we will show how to build the B2B funnel analysis using SQL. In this tutorial we will assume the data comes from Salesforce, a leading CRM system. We will need to extract the relevant dates: MQL_date, SAL_date from leads and SQL date, SQO date, validation date and closed won date from opportunities.
To track users over time, we want to be able to look at the funnel on any day of interest. This means we will want to be able to join each table of data by date. Unfortunately, many of these tables will have no data for a given day. When no data exists for a given date, we will not get a record in the final merged table and this can cause misleading results when we analyze the funnel. To avoid this problem, we will create a data spine and join it to each of the underlying tables to ensure there are no missing dates.
First, we need to calculate the earliest date and the total number of days between the earliest and latest date for each of the six dates of interest.
With this information, we can calculate the number of days (totaldays) between the first and last dates across all six dates. A date spine can be calculated as
Next, for each of these dates, the KPI needs to be calculated by simply counting the number of times each stage started on a given date. For example, to generate the number of SQOs created on a given date, we can run the following SELECT statement.
Replacing SQO with SQL and Validate dates will generate the SQLs and Validate KPIs. Closed won is a little harder. This is because Salesforce contains a Closed date, but we don’t know if that means it was closed won (a successful sale that we want to track) or closed lost (the prospect decided not to buy and shouldn’t be counted in closed won). Instead, we need to filter on the flag is_won.
Similarly, we can calculate the MQL and SAL with the marketing_qualified_date and sales_accepted_date from the Salesforce leads data.
Using these as a common table expression (CTE), we can join them all to the date spine to generate the daily KPIs. Because not every date will have each of these stages populated, when the CTEs are joined, many of the KPIs will be NULL. In this case, NULL really means the KPI is zero. We can impute the correct value by using the command COALESCE.
Combining all of the above, the SELECT statement to generate the B2B funnel KPIs is
At this point, we have created a daily table of the key B2B sales funnel metrics.
When analyzing the B2B funnel, daily data will be noisy. Often, no activity will occur on a day, leaving all of the metrics zero, other days may see only a single metric set to one. To average out this noise, business leaders most often want to see this aggregated to a higher level often to the month or quarter. Quarterly metrics can be easily created by using the previous SELECT statement as a CTE, casting the interval_start to Quarter with DATE_TRUNC, and aggregating all of the metrics as follows.
We have now created the key metrics for measuring the performance of a B2B sales funnel quarter-to-quarter. These can be visualized with any BI tool or analyzed to understand how the business is performing and to determine where to focus future efforts at improving the user journey.
Visibility into the sales funnel is key to running a successful B2B business. Achieving this visibility requires combining data from multiple sources, but with the power of SQL, we can quickly generate this combined data. From this data, executives will have the ability to understand how the business is performing and make informed, data-driven decisions about what to do next.