The e-commerce analyst that shows its work.

                                  -- Analysis: weekly flow vs campaign performance

-- Assumptions: timeframe=last_8_weeks_full | metric=click_rate+platform_attributed_revenue | 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_clicks) AS clicks,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_MULTIPLY(SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)), 100) AS click_rate_pct
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY)
  AND date < DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))
  AND LOWER(message_type) IN ('flow', 'campaign')
GROUP BY 1, 2
ORDER BY week_start, message_type;
                                

                                  -- Analysis: lead event to first purchase latency

-- Assumptions: timeframe=last_90_days | metric=lead_to_purchase_timing_hours | grain=lead_utm_source_medium | scope=event_user_id_non_null+top_sources
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,
    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
WHERE lead_utm_source_medium != '(none) / (none)'
GROUP BY 1
HAVING purchasers_with_lead_event >= 150
ORDER BY purchasers_with_lead_event DESC
LIMIT 50;
                                

                                  -- Analysis: cohort-based LTV:CAC ratio by acquisition source/medium

-- Assumptions: timeframe=last_12_cohort_months | metric=ltv_to_cac_ratio_6m | grain=source_medium | scope=cohort_table_all_orders+scaled_sources
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
    AND acquisition_order_filter_dimension_value != '(none) / (none)'
  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 >= 500
ORDER BY cohort_customers DESC
LIMIT 25;
                                

                                  -- Analysis: current inventory positions for trusted Shopify locations

-- Assumptions: scope=is_inventory_default_total_eligible=TRUE | grain=sku+location | source_system=Shopify
SELECT
  i.sku,
  i.product_title,
  i.inventory_quantity_available AS available_units,
  i.inventory_location_name AS location,
  CASE
    WHEN i.inventory_quantity_available <= 0 THEN 'Out of Stock'
    WHEN i.inventory_quantity_available < 20  THEN 'Low Stock'
    ELSE 'In Stock'
  END AS status
FROM `your_project.sm_transformed_v2.obt_inventory_positions` i
WHERE i.is_inventory_default_total_eligible = TRUE
ORDER BY available_units ASC
LIMIT 20;