Journeys & Lead Capture

-- 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;

-- Assumptions: timeframe=last_90_days | metric=lead_to_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,
    ARRAY_AGG(STRUCT(event_local_datetime, event_utm_source, event_utm_medium) ORDER BY event_local_datetime ASC LIMIT 1)[OFFSET(0)] AS lead
  FROM lead_events
  GROUP BY 1
),
first_purchase AS (
  SELECT
    event_user_id,
    MIN(event_local_datetime) AS first_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'
  GROUP BY 1
),
joined AS (
  SELECT
    CONCAT(lead.event_utm_source, ' / ', lead.event_utm_medium) AS lead_utm_source_medium,
    lead.event_local_datetime AS first_lead_at,
    p.first_purchase_at,
    DATETIME_DIFF(p.first_purchase_at, lead.event_local_datetime, HOUR) AS hours_to_first_purchase
  FROM first_lead l
  JOIN first_purchase p
    USING (event_user_id)
  WHERE p.first_purchase_at >= l.lead.event_local_datetime
)
SELECT
  lead_utm_source_medium,
  COUNT(*) AS purchasers_with_lead_event,
  APPROX_QUANTILES(hours_to_first_purchase, 101)[OFFSET(50)] AS p50_hours_to_first_purchase,
  APPROX_QUANTILES(hours_to_first_purchase, 101)[OFFSET(90)] AS p90_hours_to_first_purchase
FROM joined
GROUP BY 1
HAVING purchasers_with_lead_event >= 50
ORDER BY purchasers_with_lead_event DESC
LIMIT 50;

-- 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);

-- 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;

-- 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;

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

-- 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;

-- 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;