SQL Query Library

-- Assumptions: timeframe=last_30_days | metric=CAC=ad_spend/new_customer_count | grain=sm_channel | scope=all_channels
WITH channel_rollup AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS sm_channel,
    SUM(ABS(ad_spend)) AS ad_spend,
    SUM(new_customer_count) AS new_customers
  FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND ad_spend IS NOT NULL
    AND new_customer_count IS NOT NULL
  GROUP BY 1
),
overall AS (
  SELECT
    '(all_channels)' AS sm_channel,
    SUM(ABS(ad_spend)) AS ad_spend,
    SUM(new_customer_count) AS new_customers
  FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND ad_spend IS NOT NULL
    AND new_customer_count IS NOT NULL
)
SELECT
  sm_channel,
  ad_spend,
  new_customers,
  SAFE_DIVIDE(ad_spend, NULLIF(new_customers, 0)) AS cac
FROM channel_rollup
WHERE ad_spend > 0

UNION ALL

SELECT
  sm_channel,
  ad_spend,
  new_customers,
  SAFE_DIVIDE(ad_spend, NULLIF(new_customers, 0)) AS cac
FROM overall
WHERE ad_spend > 0
ORDER BY cac ASC;

-- Assumptions: timeframe=last_30_days | metric=ROAS=platform_reported_revenue/ad_spend | grain=platform+campaign_type | scope=all_stores
SELECT
  sm_store_id,
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS platform,
  COALESCE(NULLIF(LOWER(TRIM(ad_campaign_type)), ''), '(unknown)') AS campaign_type,
  SUM(ad_platform_reported_revenue) AS platform_reported_revenue,
  SUM(ad_spend) AS ad_spend,
  SAFE_DIVIDE(SUM(ad_platform_reported_revenue), NULLIF(SUM(ad_spend), 0)) AS roas
FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND ad_spend > 0
GROUP BY 1, 2, 3
ORDER BY roas DESC
LIMIT 20;

-- Assumptions: timeframe=last_6_months | metric=ROAS=platform_reported_revenue/ad_spend | grain=month+platform | scope=all_stores
WITH monthly AS (
  SELECT
    DATE_TRUNC(date, MONTH) AS month_start,
    COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS platform,
    SUM(ad_platform_reported_revenue) AS platform_reported_revenue,
    SUM(ad_spend) AS ad_spend
  FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
  WHERE date >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 6 MONTH)
    AND date < DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH)
    AND ad_spend > 0
  GROUP BY 1, 2
)
SELECT
  platform,
  month_start,
  ad_spend,
  platform_reported_revenue,
  SAFE_DIVIDE(platform_reported_revenue, NULLIF(ad_spend, 0)) AS roas
FROM monthly
ORDER BY platform, month_start;

-- Assumptions: timeframe=last_30_days | metric=engagement+platform_attributed_orders_revenue | grain=channel+message_type | scope=all_messages
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(message_type)), ''), '(unknown)') AS message_type,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(message_unique_bounces) AS bounces,
  SUM(message_unique_drops) AS drops,
  SUM(list_subscribes) AS list_subscribes,
  SUM(list_unsubscribes) AS list_unsubscribes,
  SUM(platform_reported_orders) AS platform_reported_orders,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_opens), NULLIF(SUM(message_unique_receives), 0)) AS open_rate,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate,
  SAFE_DIVIDE(SUM(list_unsubscribes), NULLIF(SUM(message_unique_receives), 0)) AS unsubscribe_rate_per_receive
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2
ORDER BY platform_reported_order_revenue DESC;

-- Assumptions: timeframe=last_30_days | metric=platform_attributed_orders_revenue | grain=campaign | scope=campaigns_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  campaign_id,
  ANY_VALUE(campaign_name) AS campaign_name,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(platform_reported_orders) AS platform_reported_orders,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_opens), NULLIF(SUM(message_unique_receives), 0)) AS open_rate,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND LOWER(message_type) = 'campaign'
GROUP BY 1, 2, 3
ORDER BY platform_reported_order_revenue DESC
LIMIT 25;

-- Assumptions: timeframe=last_12_weeks | metric=list_subscribes_unsubscribes_net | grain=week+channel | scope=all_messages
SELECT
  DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  SUM(list_subscribes) AS list_subscribes,
  SUM(list_unsubscribes) AS list_unsubscribes,
  SUM(list_subscribes) - SUM(list_unsubscribes) AS net_list_growth,
  SAFE_DIVIDE(SUM(list_unsubscribes), NULLIF(SUM(list_subscribes), 0)) AS unsubscribe_per_subscribe
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 84 DAY)
GROUP BY 1, 2
ORDER BY week_start, sm_message_channel;

-- Assumptions: timeframe=last_30_days | metric=engagement+platform_attributed_orders_revenue | grain=provider+channel+message_type | scope=message_sends_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(message_type)), ''), '(unknown)') AS message_type,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(platform_reported_orders) AS platform_reported_orders,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_opens), NULLIF(SUM(message_unique_receives), 0)) AS open_rate,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND message_type IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY platform_reported_order_revenue DESC
LIMIT 50;

-- Assumptions: timeframe=last_12_weeks | metric=engagement+platform_attributed_revenue+unsubscribe_rate | grain=week+message_type | scope=flow_vs_campaign_only
SELECT
  DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
  LOWER(message_type) AS message_type,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(list_unsubscribes) AS list_unsubscribes,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_opens), NULLIF(SUM(message_unique_receives), 0)) AS open_rate,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate,
  SAFE_DIVIDE(SUM(list_unsubscribes), NULLIF(SUM(message_unique_receives), 0)) AS unsubscribe_rate_per_receive
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 84 DAY)
  AND LOWER(message_type) IN ('flow', 'campaign')
GROUP BY 1, 2
ORDER BY week_start, message_type;

-- Assumptions: timeframe=last_12_weeks | metric=bounce_rate+drop_rate | grain=week+provider+channel+message_type | scope=message_sends_only
SELECT
  DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(message_type)), ''), '(unknown)') AS message_type,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_bounces) AS bounces,
  SUM(message_unique_drops) AS drops,
  SAFE_DIVIDE(SUM(message_unique_bounces), NULLIF(SUM(message_unique_receives), 0)) AS bounce_rate_per_receive,
  SAFE_DIVIDE(SUM(message_unique_drops), NULLIF(SUM(message_unique_receives), 0)) AS drop_rate_per_receive
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 84 DAY)
  AND message_type IS NOT NULL
GROUP BY 1, 2, 3, 4
HAVING receives >= 1000
ORDER BY week_start, source_system, sm_message_channel, message_type;

-- Assumptions: timeframe=last_30_days | metric=click_rate+platform_attributed_outcomes | grain=message | scope=min_receives_threshold
SELECT
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(message_type)), ''), '(unknown)') AS message_type,
  message_id,
  ANY_VALUE(message_name) AS message_name,
  ANY_VALUE(message_subject) AS message_subject,
  campaign_id,
  ANY_VALUE(campaign_name) AS campaign_name,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(platform_reported_orders) AS platform_reported_orders,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND message_id IS NOT NULL
GROUP BY 1, 2, 3, 4, 7
HAVING receives >= 10000
ORDER BY click_rate DESC, receives DESC
LIMIT 25;

-- Assumptions: timeframe=last_30_days | metric=funnel_step_counts+event_based_conversion_rates | grain=date | scope=all_sources
WITH daily AS (
  SELECT
    DATE(event_local_datetime) AS date,
    SUM(view_item_event_count) AS view_item_events,
    SUM(add_to_cart_event_count) AS add_to_cart_events,
    SUM(begin_checkout_event_count) AS begin_checkout_events,
    SUM(purchase_event_count) AS purchase_events,
    SUM(event_order_revenue) AS event_order_revenue
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1
)
SELECT
  date,
  view_item_events,
  add_to_cart_events,
  begin_checkout_events,
  purchase_events,
  event_order_revenue,
  SAFE_DIVIDE(add_to_cart_events, NULLIF(view_item_events, 0)) AS add_to_cart_per_view_item,
  SAFE_DIVIDE(begin_checkout_events, NULLIF(add_to_cart_events, 0)) AS begin_checkout_per_add_to_cart,
  SAFE_DIVIDE(purchase_events, NULLIF(begin_checkout_events, 0)) AS purchase_per_begin_checkout,
  SAFE_DIVIDE(purchase_events, NULLIF(view_item_events, 0)) AS purchase_per_view_item
FROM daily
ORDER BY date;

-- Assumptions: timeframe=last_7_days | metric=add_to_cart_rate=add_to_cart_events/page_views | grain=page_path | scope=non_null_paths
SELECT
  event_page_path,
  SUM(page_view_event_count) AS page_views,
  SUM(add_to_cart_event_count) AS add_to_cart_events,
  SAFE_DIVIDE(SUM(add_to_cart_event_count), NULLIF(SUM(page_view_event_count), 0)) AS add_to_cart_rate
FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND event_page_path IS NOT NULL
  AND TRIM(event_page_path) != ''
GROUP BY 1
HAVING page_views >= 500
ORDER BY add_to_cart_rate DESC
LIMIT 25;

-- Assumptions: timeframe=last_30_days | metric=funnel_steps+event_based_purchase_rate | grain=utm_source_medium | scope=top_sources_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(event_utm_source)), ''), '(none)') AS utm_source,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_medium)), ''), '(none)') AS utm_medium,
    SUM(view_item_event_count) AS view_item_events,
    SUM(add_to_cart_event_count) AS add_to_cart_events,
    SUM(begin_checkout_event_count) AS begin_checkout_events,
    SUM(purchase_event_count) AS purchase_events,
    SUM(event_order_revenue) AS event_order_revenue
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1, 2
)
SELECT
  CONCAT(utm_source, ' / ', utm_medium) AS utm_source_medium,
  view_item_events,
  add_to_cart_events,
  begin_checkout_events,
  purchase_events,
  event_order_revenue,
  SAFE_DIVIDE(purchase_events, NULLIF(view_item_events, 0)) AS purchase_per_view_item,
  SAFE_DIVIDE(purchase_events, NULLIF(begin_checkout_events, 0)) AS purchase_events_per_begin_checkout_event
FROM base
WHERE view_item_events >= 500
ORDER BY purchase_per_view_item DESC
LIMIT 25;

-- Assumptions: timeframe=last_30_days | metric=funnel_step_ratios | grain=source_system | scope=event_based_monitoring
SELECT
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  SUM(page_view_event_count) AS page_views,
  SUM(view_item_event_count) AS view_item_events,
  SUM(add_to_cart_event_count) AS add_to_cart_events,
  SUM(begin_checkout_event_count) AS begin_checkout_events,
  SUM(purchase_event_count) AS purchase_events,
  SAFE_DIVIDE(SUM(view_item_event_count), NULLIF(SUM(page_view_event_count), 0)) AS view_item_per_page_view,
  SAFE_DIVIDE(SUM(add_to_cart_event_count), NULLIF(SUM(view_item_event_count), 0)) AS add_to_cart_per_view_item,
  SAFE_DIVIDE(SUM(begin_checkout_event_count), NULLIF(SUM(add_to_cart_event_count), 0)) AS begin_checkout_per_add_to_cart,
  SAFE_DIVIDE(SUM(purchase_event_count), NULLIF(SUM(begin_checkout_event_count), 0)) AS purchase_events_per_begin_checkout_event
FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
HAVING page_views >= 5000
ORDER BY page_views DESC;

-- Assumptions: timeframe=last_7_days | metric=hour_over_hour_purchase_deltas | grain=hour+source_system | scope=anomaly_triage
WITH hourly AS (
  SELECT
    event_local_datetime AS hour_start,
    COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
    SUM(purchase_event_count) AS purchase_events,
    SUM(event_order_revenue) AS event_order_revenue
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY 1, 2
),
scored AS (
  SELECT
    source_system,
    hour_start,
    purchase_events,
    LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start) AS prev_purchase_events,
    purchase_events - LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start) AS delta_purchase_events,
    SAFE_DIVIDE(
      purchase_events - LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start),
      NULLIF(LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start), 0)
    ) AS pct_change_purchase_events,
    event_order_revenue
  FROM hourly
)
SELECT
  source_system,
  hour_start,
  prev_purchase_events,
  purchase_events,
  delta_purchase_events,
  pct_change_purchase_events,
  event_order_revenue
FROM scored
WHERE prev_purchase_events >= 10
ORDER BY ABS(pct_change_purchase_events) DESC
LIMIT 50;

-- Assumptions: timeframe=last_30_days | metric=email_signups+purchases | grain=utm_source_medium | scope=event_based
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(event_utm_source)), ''), '(none)') AS utm_source,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_medium)), ''), '(none)') AS utm_medium,
    SUM(email_sign_up_event_count) AS email_signups,
    SUM(purchase_event_count) AS purchase_events,
    SUM(event_order_revenue) AS event_order_revenue
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1, 2
)
SELECT
  CONCAT(utm_source, ' / ', utm_medium) AS utm_source_medium,
  email_signups,
  purchase_events,
  event_order_revenue,
  SAFE_DIVIDE(purchase_events, NULLIF(email_signups, 0)) AS purchase_events_per_email_signup,
  SAFE_DIVIDE(event_order_revenue, NULLIF(purchase_events, 0)) AS revenue_per_purchase_event
FROM base
WHERE email_signups >= 100
ORDER BY email_signups DESC
LIMIT 50;

-- Assumptions: timeframe=last_30_days | metric=cart_drop_off_signals | grain=date | scope=event_based
WITH daily AS (
  SELECT
    DATE(event_local_datetime) AS date,
    SUM(add_to_cart_event_count) AS add_to_cart_events,
    SUM(remove_from_cart_event_count) AS remove_from_cart_events,
    SUM(begin_checkout_event_count) AS begin_checkout_events,
    SUM(purchase_event_count) AS purchase_events
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1
)
SELECT
  date,
  add_to_cart_events,
  remove_from_cart_events,
  begin_checkout_events,
  purchase_events,
  SAFE_DIVIDE(remove_from_cart_events, NULLIF(add_to_cart_events, 0)) AS remove_from_cart_per_add_to_cart,
  SAFE_DIVIDE(begin_checkout_events, NULLIF(add_to_cart_events, 0)) AS begin_checkout_per_add_to_cart,
  SAFE_DIVIDE(purchase_events, NULLIF(add_to_cart_events, 0)) AS purchase_per_add_to_cart
FROM daily
ORDER BY date;

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

-- Assumptions: timeframe=last_30_days | metric=orders+customers+net_revenue | grain=first_vs_repeat | scope=valid_orders_only
SELECT
  CASE WHEN sm_valid_order_index = 1 THEN 'first_valid_order' ELSE 'repeat_valid_order' END AS order_type,
  COUNT(DISTINCT sm_order_key) AS orders,
  COUNT(DISTINCT sm_customer_key) AS customers,
  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 30 DAY)
GROUP BY 1
ORDER BY orders DESC;

-- Assumptions: timeframe=first_orders_last_12_months | metric=repeat_rate=customers_with_2+_orders/customers | grain=first_order_source_medium | scope=valid_orders_only
WITH valid_orders AS (
  SELECT
    sm_customer_key,
    sm_order_key,
    order_processed_at_local_datetime,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    ROW_NUMBER() OVER (
      PARTITION BY sm_customer_key
      ORDER BY order_processed_at_local_datetime
    ) AS rn
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND sm_customer_key IS NOT NULL
),
customer_summary AS (
  SELECT
    sm_customer_key,
    MAX(CASE WHEN rn = 1 THEN source_medium END) AS first_order_source_medium,
    MIN(CASE WHEN rn = 1 THEN DATE(order_processed_at_local_datetime) END) AS first_order_date,
    COUNT(DISTINCT sm_order_key) AS valid_order_count
  FROM valid_orders
  GROUP BY 1
)
SELECT
  first_order_source_medium AS source_medium,
  COUNT(*) AS customers,
  COUNTIF(valid_order_count >= 2) AS repeat_customers,
  SAFE_DIVIDE(COUNTIF(valid_order_count >= 2), COUNT(*)) AS repeat_rate,
  AVG(valid_order_count - 1) AS avg_subsequent_orders
FROM customer_summary
WHERE first_order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY 1
HAVING customers >= 100
ORDER BY repeat_rate DESC
LIMIT 25;

-- Assumptions: timeframe=year_to_date | metric=new_to_repeat_ratio=new_customer_count/repeat_customer_count | grain=week | scope=all_channels
WITH weekly AS (
  SELECT
    DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
    SUM(new_customer_count) AS new_customers,
    SUM(repeat_customer_count) AS repeat_customers
  FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
  WHERE date >= DATE_TRUNC(CURRENT_DATE(), YEAR)
  GROUP BY 1
)
SELECT
  week_start,
  new_customers,
  repeat_customers,
  SAFE_DIVIDE(new_customers, NULLIF(repeat_customers, 0)) AS new_to_repeat_ratio
FROM weekly
ORDER BY week_start;

-- Assumptions: timeframe=last_12_months | metric=new_customers | grain=month | scope=all_channels
WITH monthly AS (
  SELECT
    DATE_TRUNC(date, MONTH) AS month_start,
    SUM(new_customer_count) AS new_customers,
    SUM(new_customer_order_count) AS new_customer_orders,
    SUM(new_customer_order_net_revenue) AS new_customer_order_net_revenue
  FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
  WHERE date >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
  GROUP BY 1
)
SELECT
  month_start,
  new_customers,
  new_customer_orders,
  new_customer_order_net_revenue,
  SAFE_DIVIDE(new_customer_order_net_revenue, NULLIF(new_customer_orders, 0)) AS new_customer_aov
FROM monthly
ORDER BY month_start;

-- Assumptions: timeframe=last_30_days | metric=net_revenue=SUM(order_line_net_revenue) | grain=sku | scope=valid_orders_only
SELECT
  sku,
  ANY_VALUE(product_title) AS product_title,
  SUM(order_line_net_revenue) AS order_line_net_revenue,
  SUM(order_line_quantity) AS units_sold,
  COUNT(DISTINCT sm_order_key) AS orders
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND sku IS NOT NULL
  AND NOT REGEXP_CONTAINS(product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
GROUP BY 1
ORDER BY order_line_net_revenue DESC
LIMIT 10;

-- Assumptions: timeframe=last_30_days | metric=units_sold=SUM(order_line_quantity) | grain=sku | scope=valid_orders_only
SELECT
  sku,
  ANY_VALUE(product_title) AS product_title,
  SUM(order_line_quantity) AS units_sold,
  SUM(order_line_net_revenue) AS order_line_net_revenue,
  COUNT(DISTINCT sm_order_key) AS orders
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND sku IS NOT NULL
  AND NOT REGEXP_CONTAINS(product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
GROUP BY 1
ORDER BY units_sold DESC
LIMIT 20;

-- Assumptions: timeframe=first_valid_orders_last_90_days | metric=units_sold=SUM(order_line_quantity) | grain=product_title | scope=new_customers_valid_orders_only
WITH first_valid_orders AS (
  SELECT
    sm_order_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND sm_valid_order_index = 1
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  ol.product_title,
  SUM(ol.order_line_quantity) AS units_sold,
  COUNT(DISTINCT ol.sm_order_key) AS orders,
  COUNT(DISTINCT ol.sku) AS skus
FROM `your_project.sm_transformed_v2.obt_order_lines` ol
INNER JOIN first_valid_orders fvo
  ON ol.sm_order_key = fvo.sm_order_key
WHERE ol.is_order_sm_valid = TRUE
  AND ol.sku IS NOT NULL
  AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
GROUP BY 1
ORDER BY units_sold DESC
LIMIT 25;

-- Assumptions: timeframe=all_time | metric=order_frequency | grain=product_combination | scope=valid_orders_only
WITH RECURSIVE product_combos AS (
    -- Anchor: Start with individual products per order
    SELECT
        ol.sm_store_id,
        ol.sm_order_key,
        1 AS combo_length,
        CONCAT(ol.product_title, ' - ', ol.product_variant_title) AS combo,
        CONCAT(ol.product_title, ' - ', ol.product_variant_title) AS last_item
    FROM `your_project.sm_transformed_v2.obt_order_lines` AS ol
    WHERE ol.sm_store_id = 'your-sm_store_id'
      AND ol.is_order_sm_valid = TRUE
      AND ol.sku IS NOT NULL
      AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee|order specific)')

    UNION ALL

    -- Recursive: Build combinations up to 5 products
    SELECT
        ol.sm_store_id,
        ol.sm_order_key,
        pc.combo_length + 1,
        CONCAT(pc.combo, ', ', CONCAT(ol.product_title, ' - ', ol.product_variant_title)),
        CONCAT(ol.product_title, ' - ', ol.product_variant_title)
    FROM product_combos AS pc
    INNER JOIN `your_project.sm_transformed_v2.obt_order_lines` AS ol
        ON ol.sm_order_key = pc.sm_order_key
        AND CONCAT(ol.product_title, ' - ', ol.product_variant_title) > pc.last_item
    WHERE pc.combo_length < 5
      AND ol.is_order_sm_valid = TRUE
      AND ol.sku IS NOT NULL
      AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee|order specific)')
)

SELECT
    combo AS product_combinations,
    COUNT(DISTINCT sm_order_key) AS order_frequency,
    combo_length AS num_products
FROM product_combos
WHERE combo_length >= 2
GROUP BY combo, combo_length
HAVING order_frequency >= 100
ORDER BY order_frequency DESC, combo ASC
LIMIT 100;

-- Assumptions: timeframe=last_30_days | metric=AOV=SUM(order_net_revenue)/orders | grain=sm_utm_source_medium | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS marketing_channel,
    sm_order_key,
    sm_customer_key,
    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 30 DAY)
)
SELECT
  marketing_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  COUNT(DISTINCT sm_customer_key) AS customers,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS aov
FROM base
GROUP BY 1
HAVING orders >= 50
ORDER BY aov DESC
LIMIT 50;

-- Assumptions: timeframe=last_30_days | metric=net_revenue=SUM(order_net_revenue) | grain=overall | scope=customers_with_any_subscription_history
WITH subscription_customers AS (
  SELECT DISTINCT
    sm_customer_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND is_subscription_order = TRUE
    AND sm_customer_key IS NOT NULL
),
last_30_valid_orders AS (
  SELECT
    sm_order_key,
    sm_customer_key,
    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 30 DAY)
    AND sm_customer_key IS NOT NULL
)
SELECT
  SUM(CASE WHEN sc.sm_customer_key IS NOT NULL THEN o.order_net_revenue ELSE 0 END) AS revenue_from_customers_with_subscription_history,
  SUM(o.order_net_revenue) AS total_revenue_last_30_days,
  SAFE_DIVIDE(
    SUM(CASE WHEN sc.sm_customer_key IS NOT NULL THEN o.order_net_revenue ELSE 0 END),
    NULLIF(SUM(o.order_net_revenue), 0)
  ) AS pct_of_revenue_from_subscription_history_customers
FROM last_30_valid_orders o
LEFT JOIN subscription_customers sc
  ON o.sm_customer_key = sc.sm_customer_key;

-- Assumptions: timeframe=last_90_days | metric=refund_rate | grain=sm_utm_source_medium | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS marketing_channel,
    order_total_refunds,
    order_net_revenue_before_refunds,
    sm_order_key
  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)
)
SELECT
  marketing_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  COUNTIF(ABS(order_total_refunds) > 0) AS refunded_orders,
  SAFE_DIVIDE(COUNTIF(ABS(order_total_refunds) > 0), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS refund_rate_orders,
  ABS(SUM(order_total_refunds)) AS refund_amount,
  SUM(order_net_revenue_before_refunds) AS revenue_before_refunds,
  SAFE_DIVIDE(ABS(SUM(order_total_refunds)), NULLIF(SUM(order_net_revenue_before_refunds), 0)) AS refund_rate_revenue
FROM base
GROUP BY 1
HAVING orders >= 50
ORDER BY refund_rate_revenue DESC
LIMIT 50;

-- Assumptions: timeframe=last_30_days | metric=orders+net_revenue+share | grain=sm_channel | scope=valid_orders_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS sm_channel,
  COUNT(*) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(COUNT(*), NULLIF(SUM(COUNT(*)) OVER (), 0)) AS pct_orders,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(SUM(SUM(order_net_revenue)) OVER (), 0)) AS pct_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 30 DAY)
GROUP BY 1
ORDER BY orders DESC;

-- Assumptions: timeframe=all_time | metric=discovery | grain=acquisition_order_filter_dimension | scope=cohort_table_only
SELECT DISTINCT
  acquisition_order_filter_dimension
FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
ORDER BY 1;

-- Assumptions: timeframe=last_12_cohort_months | metric=retention_pct+ltv_6m | grain=source_medium | scope=cohort_table_all_orders
WITH pivoted AS (
  SELECT
    acquisition_order_filter_dimension_value AS source_medium,
    cohort_month,
    ANY_VALUE(cohort_size) AS cohort_size,
    MAX(IF(months_since_first_order = 3, customer_count, NULL)) AS customers_m3,
    MAX(IF(months_since_first_order = 6, customer_count, NULL)) AS customers_m6,
    MAX(IF(months_since_first_order = 6, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m6
  FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
  WHERE acquisition_order_filter_dimension = 'source/medium'
    AND sm_order_line_type = 'all_orders'
    AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
    AND months_since_first_order IN (3, 6)
  GROUP BY 1, 2
)
SELECT
  source_medium,
  SUM(cohort_size) AS cohort_customers,
  SAFE_DIVIDE(SUM(customers_m3), NULLIF(SUM(cohort_size), 0)) AS retention_m3,
  SAFE_DIVIDE(SUM(customers_m6), NULLIF(SUM(cohort_size), 0)) AS retention_m6,
  SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m6), NULLIF(SUM(cohort_size), 0)) AS ltv_net_per_customer_m6
FROM pivoted
GROUP BY 1
HAVING cohort_customers >= 200
ORDER BY retention_m6 DESC
LIMIT 25;

-- Assumptions: timeframe=last_12_cohort_months | metric=payback_months | grain=source_medium | scope=cohort_table_all_orders
WITH base AS (
  SELECT
    acquisition_order_filter_dimension_value AS source_medium,
    cohort_month,
    months_since_first_order,
    cohort_size,
    cost_per_acquisition,
    cumulative_order_net_revenue
  FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
  WHERE acquisition_order_filter_dimension = 'source/medium'
    AND sm_order_line_type = 'all_orders'
    AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
    AND months_since_first_order BETWEEN 0 AND 12
    AND cost_per_acquisition IS NOT NULL
    AND cost_per_acquisition > 0
),
per_cohort AS (
  SELECT
    source_medium,
    cohort_month,
    ANY_VALUE(cohort_size) AS cohort_size,
    ANY_VALUE(cost_per_acquisition) AS cac_per_customer,
    MIN(
      IF(
        SAFE_DIVIDE(cumulative_order_net_revenue, NULLIF(cohort_size, 0)) >= cost_per_acquisition,
        months_since_first_order,
        NULL
      )
    ) AS payback_months
  FROM base
  GROUP BY 1, 2
)
SELECT
  source_medium,
  SUM(cohort_size) AS cohort_customers,
  SAFE_DIVIDE(
    SUM(CAST(payback_months AS FLOAT64) * cohort_size),
    NULLIF(SUM(CASE WHEN payback_months IS NOT NULL THEN cohort_size ELSE 0 END), 0)
  ) AS avg_payback_months_weighted,
  SAFE_DIVIDE(
    SUM(CASE WHEN payback_months IS NOT NULL THEN cohort_size ELSE 0 END),
    NULLIF(SUM(cohort_size), 0)
  ) AS pct_customers_in_cohorts_with_payback_within_12m
FROM per_cohort
GROUP BY 1
HAVING cohort_customers >= 200
ORDER BY avg_payback_months_weighted ASC;

-- Assumptions: timeframe=last_12_cohort_months | metric=ltv_to_cac_ratio_6m | grain=source_medium | scope=cohort_table_all_orders
WITH per_cohort AS (
  SELECT
    acquisition_order_filter_dimension_value AS source_medium,
    cohort_month,
    ANY_VALUE(cohort_size) AS cohort_size,
    ANY_VALUE(cost_per_acquisition) AS cac_per_customer,
    MAX(IF(months_since_first_order = 6, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m6
  FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
  WHERE acquisition_order_filter_dimension = 'source/medium'
    AND sm_order_line_type = 'all_orders'
    AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
    AND months_since_first_order = 6
    AND cost_per_acquisition IS NOT NULL
    AND cost_per_acquisition > 0
  GROUP BY 1, 2
)
SELECT
  source_medium,
  SUM(cohort_size) AS cohort_customers,
  SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m6), NULLIF(SUM(cohort_size), 0)) AS ltv_net_per_customer_m6,
  SAFE_DIVIDE(SUM(cac_per_customer * cohort_size), NULLIF(SUM(cohort_size), 0)) AS cac_per_customer_weighted,
  SAFE_DIVIDE(
    SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m6), NULLIF(SUM(cohort_size), 0)),
    NULLIF(SAFE_DIVIDE(SUM(cac_per_customer * cohort_size), NULLIF(SUM(cohort_size), 0)), 0)
  ) AS ltv_to_cac_ratio_m6
FROM per_cohort
GROUP BY 1
HAVING cohort_customers >= 200
ORDER BY ltv_to_cac_ratio_m6 DESC
LIMIT 25;

-- Assumptions: timeframe=last_12_cohort_months | metric=retention_m6+ltv_12m | grain=discount_code | scope=cohort_table_all_orders
WITH pivoted AS (
  SELECT
    acquisition_order_filter_dimension_value AS discount_code,
    cohort_month,
    ANY_VALUE(cohort_size) AS cohort_size,
    MAX(IF(months_since_first_order = 6, customer_count, NULL)) AS customers_m6,
    MAX(IF(months_since_first_order = 12, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m12
  FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
  WHERE acquisition_order_filter_dimension = 'discount_code'
    AND sm_order_line_type = 'all_orders'
    AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
    AND months_since_first_order IN (6, 12)
    AND acquisition_order_filter_dimension_value IS NOT NULL
    AND acquisition_order_filter_dimension_value != ''
  GROUP BY 1, 2
),
aggregated AS (
  SELECT
    discount_code,
    SUM(cohort_size) AS cohort_customers,
    SAFE_DIVIDE(SUM(customers_m6), NULLIF(SUM(cohort_size), 0)) AS retention_m6,
    SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m12), NULLIF(SUM(cohort_size), 0)) AS ltv_net_per_customer_m12
  FROM pivoted
  GROUP BY 1
  HAVING cohort_customers >= 100
)
SELECT
  discount_code,
  cohort_customers,
  retention_m6,
  ltv_net_per_customer_m12
FROM aggregated
ORDER BY ltv_net_per_customer_m12 DESC
LIMIT 25;

-- Assumptions: timeframe=last_12_cohort_months | metric=retention_m6+ltv_12m | grain=first_order_type | scope=cohort_table_all_orders
WITH pivoted AS (
  SELECT
    acquisition_order_filter_dimension_value AS first_order_type,
    cohort_month,
    ANY_VALUE(cohort_size) AS cohort_size,
    MAX(IF(months_since_first_order = 6, customer_count, NULL)) AS customers_m6,
    MAX(IF(months_since_first_order = 12, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m12
  FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
  WHERE acquisition_order_filter_dimension = 'order_type_(sub_vs._one_time)'
    AND sm_order_line_type = 'all_orders'
    AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
    AND months_since_first_order IN (6, 12)
  GROUP BY 1, 2
)
SELECT
  first_order_type,
  SUM(cohort_size) AS cohort_customers,
  SAFE_DIVIDE(SUM(customers_m6), NULLIF(SUM(cohort_size), 0)) AS retention_m6,
  SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m12), NULLIF(SUM(cohort_size), 0)) AS ltv_net_per_customer_m12
FROM pivoted
GROUP BY 1
HAVING cohort_customers >= 50
ORDER BY ltv_net_per_customer_m12 DESC;

-- Assumptions: timeframe=first_valid_orders_last_12_months | metric=repeat_rate_30_60_90 | grain=source_medium | scope=valid_paid_orders_only
WITH first_valid_orders AS (
  SELECT
    sm_customer_key,
    order_processed_at_local_datetime AS first_order_at_local_datetime,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND sm_customer_key IS NOT NULL
    AND sm_valid_order_index = 1
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
),
per_customer AS (
  SELECT
    fo.source_medium,
    fo.sm_customer_key,
    MIN(DATETIME_DIFF(o.order_processed_at_local_datetime, fo.first_order_at_local_datetime, DAY)) AS first_repeat_day,
    COUNT(DISTINCT o.sm_order_key) AS repeat_orders_90d,
    COALESCE(SUM(o.order_net_revenue), 0) AS repeat_revenue_90d
  FROM first_valid_orders fo
  LEFT JOIN `your_project.sm_transformed_v2.obt_orders` o
    ON o.sm_customer_key = fo.sm_customer_key
    AND o.is_order_sm_valid = TRUE
    AND o.order_cancelled_at IS NULL
    AND o.order_net_revenue > 0
    AND o.order_processed_at_local_datetime > fo.first_order_at_local_datetime
    AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
  GROUP BY 1, 2
)
SELECT
  source_medium,
  COUNT(*) AS customers,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30) AS customers_repeat_30d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30), NULLIF(COUNT(*), 0)) AS repeat_rate_30d,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60) AS customers_repeat_60d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60), NULLIF(COUNT(*), 0)) AS repeat_rate_60d,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90) AS customers_repeat_90d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90), NULLIF(COUNT(*), 0)) AS repeat_rate_90d,
  AVG(repeat_orders_90d) AS avg_repeat_orders_90d,
  AVG(repeat_revenue_90d) AS avg_repeat_revenue_90d
FROM per_customer
GROUP BY 1
HAVING customers >= 200
ORDER BY repeat_rate_90d DESC
LIMIT 25;

-- Assumptions: timeframe=first_valid_orders_last_12_months | metric=repeat_rate_30_60_90 | grain=first_order_type | scope=valid_paid_orders_only
WITH first_valid_orders AS (
  SELECT
    sm_customer_key,
    order_processed_at_local_datetime AS first_order_at_local_datetime,
    CASE
      WHEN is_subscription_order = TRUE THEN 'subscription_first_order'
      ELSE 'one_time_first_order'
    END AS first_order_type
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND sm_customer_key IS NOT NULL
    AND sm_valid_order_index = 1
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
),
per_customer AS (
  SELECT
    fo.first_order_type,
    fo.sm_customer_key,
    MIN(DATETIME_DIFF(o.order_processed_at_local_datetime, fo.first_order_at_local_datetime, DAY)) AS first_repeat_day,
    COUNT(DISTINCT o.sm_order_key) AS repeat_orders_90d,
    COALESCE(SUM(o.order_net_revenue), 0) AS repeat_revenue_90d
  FROM first_valid_orders fo
  LEFT JOIN `your_project.sm_transformed_v2.obt_orders` o
    ON o.sm_customer_key = fo.sm_customer_key
    AND o.is_order_sm_valid = TRUE
    AND o.order_cancelled_at IS NULL
    AND o.order_net_revenue > 0
    AND o.order_processed_at_local_datetime > fo.first_order_at_local_datetime
    AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
  GROUP BY 1, 2
)
SELECT
  first_order_type,
  COUNT(*) AS customers,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30) AS customers_repeat_30d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30), NULLIF(COUNT(*), 0)) AS repeat_rate_30d,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60) AS customers_repeat_60d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60), NULLIF(COUNT(*), 0)) AS repeat_rate_60d,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90) AS customers_repeat_90d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90), NULLIF(COUNT(*), 0)) AS repeat_rate_90d,
  AVG(repeat_orders_90d) AS avg_repeat_orders_90d,
  AVG(repeat_revenue_90d) AS avg_repeat_revenue_90d
FROM per_customer
GROUP BY 1
HAVING customers >= 200
ORDER BY repeat_rate_90d DESC;

-- Assumptions: timeframe=first_valid_orders_last_12_months | metric=repeat_rate_30_60_90 | grain=first_order_aov_bucket | scope=valid_paid_orders_only
WITH first_valid_orders AS (
  SELECT
    sm_customer_key,
    order_processed_at_local_datetime AS first_order_at_local_datetime,
    order_net_revenue AS first_order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND sm_customer_key IS NOT NULL
    AND sm_valid_order_index = 1
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
),
bucketed AS (
  SELECT
    sm_customer_key,
    first_order_at_local_datetime,
    CASE
      WHEN first_order_net_revenue < 25 THEN '$0–$25'
      WHEN first_order_net_revenue < 50 THEN '$25–$50'
      WHEN first_order_net_revenue < 100 THEN '$50–$100'
      WHEN first_order_net_revenue < 200 THEN '$100–$200'
      ELSE '$200+'
    END AS first_order_aov_bucket
  FROM first_valid_orders
),
per_customer AS (
  SELECT
    b.first_order_aov_bucket,
    b.sm_customer_key,
    MIN(DATETIME_DIFF(o.order_processed_at_local_datetime, b.first_order_at_local_datetime, DAY)) AS first_repeat_day,
    COUNT(DISTINCT o.sm_order_key) AS repeat_orders_90d,
    COALESCE(SUM(o.order_net_revenue), 0) AS repeat_revenue_90d
  FROM bucketed b
  LEFT JOIN `your_project.sm_transformed_v2.obt_orders` o
    ON o.sm_customer_key = b.sm_customer_key
    AND o.is_order_sm_valid = TRUE
    AND o.order_cancelled_at IS NULL
    AND o.order_net_revenue > 0
    AND o.order_processed_at_local_datetime > b.first_order_at_local_datetime
    AND o.order_processed_at_local_datetime < DATETIME_ADD(b.first_order_at_local_datetime, INTERVAL 90 DAY)
  GROUP BY 1, 2
)
SELECT
  first_order_aov_bucket,
  COUNT(*) AS customers,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30) AS customers_repeat_30d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30), NULLIF(COUNT(*), 0)) AS repeat_rate_30d,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60) AS customers_repeat_60d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60), NULLIF(COUNT(*), 0)) AS repeat_rate_60d,
  COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90) AS customers_repeat_90d,
  SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90), NULLIF(COUNT(*), 0)) AS repeat_rate_90d,
  AVG(repeat_orders_90d) AS avg_repeat_orders_90d,
  AVG(repeat_revenue_90d) AS avg_repeat_revenue_90d
FROM per_customer
GROUP BY 1
ORDER BY CASE first_order_aov_bucket
  WHEN '$0–$25' THEN 1
  WHEN '$25–$50' THEN 2
  WHEN '$50–$100' THEN 3
  WHEN '$100–$200' THEN 4
  WHEN '$200+' THEN 5
  ELSE 99
END;

-- Assumptions: timeframe=first_valid_orders_last_12_months | metric=90d_LTV | grain=first_order_source_medium | scope=valid_orders_only
WITH first_valid_orders AS (
  SELECT
    sm_customer_key,
    order_processed_at_local_datetime AS first_order_at_local_datetime,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS first_order_source_medium
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND sm_customer_key IS NOT NULL
    AND sm_valid_order_index = 1
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
),
customer_90d_ltv AS (
  SELECT
    fo.first_order_source_medium,
    fo.sm_customer_key,
    SUM(o.order_net_revenue) AS ltv_90d
  FROM first_valid_orders fo
  INNER JOIN `your_project.sm_transformed_v2.obt_orders` o
    ON o.sm_customer_key = fo.sm_customer_key
  WHERE o.is_order_sm_valid = TRUE
    AND o.order_cancelled_at IS NULL
    AND o.order_processed_at_local_datetime >= fo.first_order_at_local_datetime
    AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
  GROUP BY 1, 2
)
SELECT
  first_order_source_medium AS source_medium,
  COUNT(*) AS customers,
  AVG(ltv_90d) AS avg_ltv_90d,
  SUM(ltv_90d) AS total_ltv_90d
FROM customer_90d_ltv
GROUP BY 1
HAVING customers >= 200
ORDER BY avg_ltv_90d DESC
LIMIT 25;