Journeys & Lead Capture

Lead capture event discovery (top event names, last 30 days)

What you’ll learn: Which normalized funnel events are present in your warehouse so you can pick the correct lead-capture event names (email signup, subscribe, generate lead, etc.) without guessing.

-- Assumptions: timeframe=last_30_days | metric=event_counts | grain=event_name | scope=discovery
SELECT
  COALESCE(sm_event_name, '(null)') AS sm_event_name,
  COUNT(*) AS events
FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY events DESC
LIMIT 50;

Lead capture → next purchase timing (hours) by lead UTM source/medium (last 90 days)

What you’ll learn: For users with a lead capture event, how long it takes to reach their next purchase after that lead (p50/p90 hours). Results are broken out by the UTM source/medium on the user’s first lead event. Purchases that happened before the lead are ignored, so a user who bought first and signed up later still counts if they buy again.

-- Assumptions: timeframe=last_90_days | metric=lead_to_next_purchase_timing_hours | grain=lead_utm_source_medium | scope=event_user_id_non_null
-- Update this list after running the discovery query above:
--   lead_event_names = ('generate_lead', 'sign_up')
WITH lead_events AS (
  SELECT
    event_user_id,
    event_local_datetime,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_source)), ''), '(none)') AS event_utm_source,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_medium)), ''), '(none)') AS event_utm_medium
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE event_user_id IS NOT NULL
    AND DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_event_name IN ('generate_lead', 'sign_up')
),
first_lead AS (
  SELECT
    event_user_id,
    event_local_datetime AS lead_at,
    CONCAT(event_utm_source, ' / ', event_utm_medium) AS lead_utm_source_medium
  FROM lead_events
  QUALIFY ROW_NUMBER() OVER (PARTITION BY event_user_id ORDER BY event_local_datetime ASC) = 1
),
purchase_events AS (
  SELECT
    event_user_id,
    event_local_datetime AS purchase_at
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE event_user_id IS NOT NULL
    AND DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_event_name = 'purchase'
),
next_purchase_after_lead AS (
  SELECT
    l.event_user_id,
    l.lead_utm_source_medium,
    l.lead_at,
    MIN(p.purchase_at) AS next_purchase_at
  FROM first_lead l
  INNER JOIN purchase_events p
    ON p.event_user_id = l.event_user_id
    AND p.purchase_at >= l.lead_at
  GROUP BY 1, 2, 3
)
SELECT
  lead_utm_source_medium,
  COUNT(*) AS purchasers_with_lead_event,
  APPROX_QUANTILES(DATETIME_DIFF(next_purchase_at, lead_at, HOUR), 101)[OFFSET(50)] AS p50_hours_to_next_purchase,
  APPROX_QUANTILES(DATETIME_DIFF(next_purchase_at, lead_at, HOUR), 101)[OFFSET(90)] AS p90_hours_to_next_purchase
FROM next_purchase_after_lead
GROUP BY 1
HAVING purchasers_with_lead_event >= 50
ORDER BY purchasers_with_lead_event DESC
LIMIT 50;

Lead capture → purchase conversion rate (last 90 days)

What you’ll learn: What share of tracked users with a lead capture event later have a purchase event (identity-based, using DISTINCT event_user_id). Useful for directional lead-to-purchase monitoring.

-- Assumptions: timeframe=last_90_days | metric=lead_to_purchase_rate | grain=all_leads | scope=event_user_id_non_null
-- Update this list after running the discovery query above:
--   lead_event_names = ('generate_lead', 'sign_up')
WITH lead_users AS (
  SELECT DISTINCT
    event_user_id
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE event_user_id IS NOT NULL
    AND DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_event_name IN ('generate_lead', 'sign_up')
),
purchase_users AS (
  SELECT DISTINCT
    event_user_id
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE event_user_id IS NOT NULL
    AND DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_event_name = 'purchase'
)
SELECT
  COUNT(DISTINCT l.event_user_id) AS lead_users,
  COUNT(DISTINCT p.event_user_id) AS purchasers_from_leads,
  SAFE_DIVIDE(COUNT(DISTINCT p.event_user_id), NULLIF(COUNT(DISTINCT l.event_user_id), 0)) AS lead_to_purchase_rate
FROM lead_users l
LEFT JOIN purchase_users p
  USING (event_user_id);

This is event-identity based (tracking-user-based), not customer-based. Coverage depends on your tracking setup and identity stitching.

MTA: First-touch vs last-touch marketing channel mix (purchases, last 30 days)

What you’ll learn: For purchases, what the first-touch vs last-touch marketing channels were (journey-level). Useful for quantifying “what brings users in” vs “what closes”.

-- Assumptions: timeframe=last_30_days | metric=purchase_revenue_by_first_last_touch_channel | grain=first_touch+last_touch | scope=mta_purchase_rows_only
SELECT
  COALESCE(first_touch_dimension_value.marketing_channel, '(unknown)') AS first_touch_marketing_channel,
  COALESCE(last_touch_dimension_value.marketing_channel, '(unknown)') AS last_touch_marketing_channel,
  COUNT(DISTINCT purchase_order_id) AS orders,
  SUM(purchase_order_revenue) AS purchase_order_revenue
FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
WHERE sm_event_name = 'purchase'
  AND DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2
ORDER BY purchase_order_revenue DESC
LIMIT 50;

MTA: Time to conversion (days) by first-touch marketing channel (purchases, last 30 days)

What you’ll learn: How long it takes to convert by acquisition channel, using MTA-derived days-to-conversion (journey-level).

-- Assumptions: timeframe=last_30_days | metric=days_to_conversion | grain=first_touch_marketing_channel | scope=mta_purchase_rows_only
WITH purchases AS (
  SELECT
    COALESCE(first_touch_dimension_value.marketing_channel, '(unknown)') AS first_touch_marketing_channel,
    CAST(days_to_conversion.marketing_channel AS INT64) AS days_to_conversion_marketing_channel
  FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
  WHERE sm_event_name = 'purchase'
    AND DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND days_to_conversion.marketing_channel IS NOT NULL
)
SELECT
  first_touch_marketing_channel,
  COUNT(*) AS purchases,
  AVG(days_to_conversion_marketing_channel) AS avg_days_to_conversion,
  APPROX_QUANTILES(days_to_conversion_marketing_channel, 101)[OFFSET(50)] AS p50_days_to_conversion,
  APPROX_QUANTILES(days_to_conversion_marketing_channel, 101)[OFFSET(90)] AS p90_days_to_conversion
FROM purchases
GROUP BY 1
HAVING purchases >= 100
ORDER BY purchases DESC;

MTA landing pages: Top first-touch landing pages by attributed revenue (purchases, last 30 days)

What you’ll learn: Which landing pages are the first-touch landing page for purchases, and the revenue that first-touch attribution assigns to each (directional). Journeys with no valid landing-page touch show as (unknown), and $0 orders are kept in the order count.

-- Assumptions: timeframe=last_30_days | metric=first_touch_landing_page_revenue | grain=first_touch_landing_page | scope=mta_purchase_rows_only
SELECT
  COALESCE(NULLIF(first_touch_dimension_value.landing_page, ''), '(unknown)') AS first_touch_landing_page,
  COUNT(DISTINCT purchase_order_id) AS orders,
  SUM(purchase_order_revenue) AS first_touch_attributed_revenue
FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
WHERE sm_event_name = 'purchase'
  AND DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY first_touch_attributed_revenue DESC
LIMIT 50;

Zero-party attribution: Revenue by post-purchase survey source (new vs repeat, last 90 days)

What you’ll learn: What customers say drove their purchase (post‑purchase survey tags), and how it differs for new vs repeat orders and subscription orders.

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=zero_party_source+new_repeat+subscription_sequence | scope=valid_orders_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_zero_party_attribution_source)), ''), '(none)') AS sm_zero_party_attribution_source,
  sm_valid_order_sequence,
  subscription_order_sequence,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_cancelled_at IS NULL
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1, 2, 3
ORDER BY order_net_revenue DESC
LIMIT 100;

Last-touch Klaviyo orders: New vs repeat × subscription sequence (last 90 days)

What you’ll learn: How last-click orders attributed to Klaviyo perform, segmented by new vs repeat and subscription sequence. This uses sm_utm_source/sm_utm_medium (last-click) from the order attribution hierarchy.

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=utm_medium+new_repeat+subscription_sequence | scope=valid_orders_only_last_click_utm
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_utm_medium)), ''), '(none)') AS sm_utm_medium,
  sm_valid_order_sequence,
  subscription_order_sequence,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_cancelled_at IS NULL
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND LOWER(TRIM(sm_utm_source)) = 'klaviyo'
  AND LOWER(TRIM(sm_utm_medium)) IN ('email', 'sms')
GROUP BY 1, 2, 3
ORDER BY order_net_revenue DESC;

If you don’t see sm_utm_source = 'klaviyo' in your organization, run the “UTM source/medium discovery” template and choose the exact source values for your messaging stack.

Funnel

Pair journey timing with step-level conversion analysis.

Messaging

Compare lead capture and journeys with campaign/flow outcomes.

Attribution & Data Health

Validate touchpoint and attribution data quality.

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

Journeys & Lead Capture

Funnel

Messaging

Attribution & Data Health

​Related Categories

Funnel

Messaging

Attribution & Data Health

Related Categories