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.

‍

What is B2B Sales Funnel?

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:

‍

• Awareness - Prospect first becomes aware of the product and the value they can derive
• Interest - Prospect begins evaluating the product and comparing it to competitors 
• Decision - Prospect evaluates company, pricing, and product to make a decision
• Action - Prospect converts to a customer via a product purchase or decided not to buy

‍

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.

‍

• Marketing qualified lead (MQL) - contacts that have come in through marketing controlled (often inbound) channels that have not interacted with sales
• Sales accepted lead (SAL) - MQLs that have been approved for follow-up by sales
• Sales qualified lead (SQL) - Leads that have interacted with sales and are potential opportunities for a deal
• Sales qualified opportunity (SQO) - SQLs that have passed initial qualification and are now being forecasted by the company 
• POCs - SQOs who are actively validating the product will meet their needs, this could be through entering into a proof of concept (POC) or Pilot of the technology
• Closed/Won - SQOs that have purchased your product. 

‍

Data needed for B2B Sales Funnel

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:

‍

‍

How to create B2B Sales Funnel in SQL?

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. 

‍

Date Spine

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.

‍

SELECT min(cast({date_column} AS date)) AS min_date,
max(cast({date_column} AS date)) AS max_date
FROM {table}

‍

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

SELECT row_number OVER (ORDER BY NULL) AS interval_id,
DATEADD(‘DAY’, interval_id - 1, minimum_date) AS interval_start,
DATEADD(‘DAY’, interval_id, minimum_date) AS interval_end
FROM table (generator(rowcount => ))

‍

Generate KPIs

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.

SELECT SQO_date,
COUNT(SQO_date) AS SQO
FROM salesforce.opportunities
GROUP BY SQO_date

‍

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.

SELECT close_date,
COUNT(close_date) AS close_won
FROM salesforce.opportunities
WHERE IS_WON = ‘true’
GROUP BY close_date

‍

Similarly, we can calculate the MQL and SAL with the marketing_qualified_date and sales_accepted_date from the Salesforce leads data.

SELECT marketing_qualified_date,
COUNT(marketing_qualified_date) AS MQL
FROM salesforce.leads
GROUP BY marketing_qualified_date

‍

Daily KPIs

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.

‍

COALESCE(field, 0) AS field

‍

Combining all of the above, the SELECT statement to generate the B2B funnel KPIs is

WITH date_spine AS (
  SELECT row_number OVER (ORDER BY NULL) AS interval_id,
  DATEADD(‘DAY’, interval_id - 1, ) AS interval_start,
  DATEADD(‘DAY’, interval_id, ) AS interval_end
  FROM table (generator(rowcount => ))
),
mql AS (
  SELECT marketing_qualified_date,
  COUNT(marketing_qualified_date) AS MQL
  FROM salesforce.leads
  GROUP BY marketing_qualified_date
),
sal AS (
  SELECT sales_accepted_date,
  COUNT(sales_accepted_date) AS SAL
  FROM salesforce.leads
  GROUP BY sales_accepted_date
),
sql AS (
  SELECT SQL_date,
  COUNT(SQL_date) AS SQL
  FROM salesforce.opportunities
  GROUP BY SQL_date
),
sqo AS (
  SELECT SQO_date,
  COUNT(SQO_date) AS SQO
  FROM salesforce.opportunities
  GROUP BY SQO_date
),
val AS (
  SELECT validate_date,
  COUNT(validate_date) AS validate
  FROM salesforce.opportunities
  GROUP BY validate_date
),
cw AS (
  SELECT close_date,
  COUNT(close_date) AS close_won
  FROM salesforce.opportunities
  WHERE IS_WON = ‘true’
  GROUP BY close_date
)
SELECT ds.interval_start AS date,
COALESCE(mql.MQL, 0) AS MQL,
COALESCE(sal.SAL, 0 ) AS SAL,
COALESCE(sql.SQL, 0) AS SQL,
COALESCE(sqo.SQO, 0) AS SQO,
COALESCE(val.validate, 0) AS validate,
COALESCE(cw.closed_won, 0) AS closed_won
FROM date_spine AS ds
LEFT JOIN mql
ON ds.interval_start = mql.marketing_qualified_date
LEFT JOIN sal
ON ds.interval_start = sal.sales_accepted_date
LEFT JOIN sql
ON ds.interval_start = sql.SQL_date
LEFT JOIN sqo
ON ds.interval_start = sqo.SQO_date
LEFT JOIN val
ON ds.interval_start = val.validate_date
LEFT JOIN cw
ON ds.interval_start = cw.closed_date

‍

At this point, we have created a daily table of the key B2B sales funnel metrics.

‍

Aggregation

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.

‍

WITH daily_metrics AS (
  WITH date_spine AS (
    SELECT row_number OVER (ORDER BY NULL) AS interval_id,
    DATEADD(‘DAY’, interval_id - 1, ) AS interval_start,
    DATEADD(‘DAY’, interval_id, ) AS interval_end
    FROM table (generator(rowcount => ))
  ),
  mql AS (
    SELECT marketing_qualified_date,
    COUNT(marketing_qualified_date) AS MQL
    FROM salesforce.leads
    GROUP BY marketing_qualified_date
  ),
  sal AS (
    SELECT sales_accepted_date,
    COUNT(sales_accepted_date) AS SAL
    FROM salesforce.leads
    GROUP BY sales_accepted_date
  ),
  sql AS (
    SELECT SQL_date,
    COUNT(SQL_date) AS SQL
    FROM salesforce.opportunities
    GROUP BY SQL_date
  ),
  sqo AS (
    SELECT SQO_date,
    COUNT(SQO_date) AS SQO
    FROM salesforce.opportunities
    GROUP BY SQO_date
  ),
  val AS (
    SELECT validate_date,
    COUNT(validate_date) AS validate
    FROM salesforce.opportunities
    GROUP BY validate_date
  ),
  cw AS (
    SELECT close_date,
    COUNT(close_date) AS close_won
    FROM salesforce.opportunities
    WHERE IS_WON = ‘true’
    GROUP BY close_date
  )
  SELECT DATE_TRUNC(quarter, ds.interval_start) AS quarter,
  COALESCE(mql.MQL, 0) AS MQL,
  COALESCE(sal.SAL, 0 ) AS SAL,
  COALESCE(sql.SQL, 0) AS SQL,
  COALESCE(sqo.SQO, 0) AS SQO,
  COALESCE(val.validate, 0) AS validate,
  COALESCE(cw.closed_won, 0) AS closed_won
  FROM date_spine AS ds
  LEFT JOIN mql
  ON ds.interval_start = mql.marketing_qualified_date
  LEFT JOIN sal
  ON ds.interval_start = sal.sales_accepted_date
  LEFT JOIN sql
  ON ds.interval_start = sql.SQL_date
  LEFT JOIN sqo
  ON ds.interval_start = sqo.SQO_date
  LEFT JOIN val
  ON ds.interval_start = val.validate_date
  LEFT JOIN cw
  ON ds.interval_start = cw.closed_date)
)
SELECT quarter,
SUM(MQL) AS MQL,
SUM(SAL) AS SAL,
SUM(SQL) AS SQL,
SUM(SQO) AS SQO,
SUM(validate) AS validate,
SUM(closed_won) AS closed_won
FROM daily_metrics
GROUP BY quarter

‍

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.

‍

Conclusion

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.

‍

‍