Customer Journey

Journey Length between stages eg. from SQL to NewBiz

SELECT
 AVG(stage2stage)
FROM (
 SELECT
   step1.timestamp AS sql_time,
   step2.timestamp AS nb_time,
   DATETIME_DIFF(step2.timestamp, step1.timestamp, day) AS stage2stage
 FROM
   `table.stages` AS step1
 CROSS JOIN
   UNNEST (stage_transitions) AS step2
 WHERE
   step2.name = "SQL"
   AND step2.timestamp >= "2025-01-01"
   AND step1.stage_name = "NewBiz" )

Companies with active events past 7 days

SELECT
 c.properties.name,
 c.properties. country,
 c.properties. industry,
 c.properties. number_of_employees,
 COUNT(e.dd_event_id) AS events_last7days
FROM
 `table.companies` c
LEFT JOIN
 `table.events` e
ON
 c.dd_company_id = e.dd_company_id
WHERE
 CAST(e.timestamp AS DATE) >= CURRENT_DATE() - INTERVAL 7 DAY
 AND CAST(e.timestamp AS DATE) < CURRENT_DATE()
GROUP BY
 c.properties.name,
 c.properties. country,
 c.properties. industry,
 c.properties. number_of_employees