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 counts stage occurrences (dd_stage_id) and conversions via stage_transitions, matching Analytics Hub stage_reporting and stage_conversion metrics.

WITH
  tb_mql AS (
  SELECT
    dd_stage_id
  FROM
    `table.stages`
  WHERE
    stage_name = 'MQL'
    AND timestamp >= '2025-01-01' ),
  tb_sql AS (
  SELECT
    COUNT(DISTINCT to_st.dd_stage_id) AS converted_sql
  FROM
    `table.stages` AS st,
    UNNEST(st.stage_transitions) AS trans
  INNER JOIN
    `table.stages` AS to_st ON trans.dd_stage_id = to_st.dd_stage_id
  WHERE
    st.stage_name = 'MQL'
    AND to_st.stage_name = 'SQL'
    AND st.timestamp >= '2025-01-01' ),
  tb_newbiz AS (
  SELECT
    COUNT(DISTINCT to_st.dd_stage_id) AS converted_newbiz
  FROM
    `table.stages` AS st,
    UNNEST(st.stage_transitions) AS trans
  INNER JOIN
    `table.stages` AS to_st ON trans.dd_stage_id = to_st.dd_stage_id
  WHERE
    st.stage_name = 'MQL'
    AND to_st.stage_name = 'NewBiz'
    AND st.timestamp >= '2025-01-01' )
SELECT
  (SELECT COUNT(DISTINCT dd_stage_id) FROM tb_mql) AS mql,
  (SELECT converted_sql FROM tb_sql) AS sql,
  (SELECT converted_newbiz FROM tb_newbiz) AS newbiz