Funnel

This SQL query provides a crucial view into your marketing and sales funnel performance, specifically tracking the progression of companies from MQL (Marketing Qualified Lead) through to New Business.

Please note the sequential and date-dependent nature of this query:

• MQLs are identified from January 1, 2025, onwards.
• SQLs are defined as companies that transitioned from an MQL to a SQL.
• New Business represents companies that further transitioned from either an MQL or an SQL to become a new customer.

This query precisely counts the distinct number of companies reaching each stage, providing you insights into the conversion rates and the health of your pipeline from the beginning of 2025.

WITH
  tb_mql AS (
  SELECT *
  FROM
    `table.stages` AS s
  WHERE
    s.stage_name = 'MQL'
    AND s.timestamp >= '2025-01-01' ),
  tb_sql AS (
  SELECT
    s.dd_company_id,
  FROM
    tb_mql AS s
  LEFT JOIN
    UNNEST(s.stage_transitions) AS st
  WHERE
    st.name = 'SQL' ),
  tb_newbiz AS (
  SELECT
    s.dd_company_id,
  FROM
    tb_mql AS s
  LEFT JOIN
    UNNEST(s.stage_transitions) AS st
  WHERE
    st.name = 'NewBiz' )
SELECT
  COUNT(DISTINCT mql.dd_company_id) AS mql,
  COUNT(DISTINCT sql.dd_company_id) AS sql,
  COUNT(DISTINCT newbiz.dd_company_id) AS newbiz
FROM
  tb_mql AS mql
LEFT JOIN
  tb_sql AS sql
ON
  mql.dd_company_id = sql.dd_company_id
LEFT JOIN
  tb_newbiz AS newbiz
ON
  mql.dd_company_id = newbiz.dd_company_id
  AND SQL.dd_company_id = newbiz.dd_company_id