Marketing & Ads

-- Assumptions: timeframe=last_30_days | metric=blended_CAC=ad_spend/new_customer_count | grain=sm_channel (sales channel, not marketing 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: platform=tiktok | timeframe=last_90_days | grain=campaign_type+campaign | metric=spend+clicks+impressions | caveat=exclude_gmv_max_for_cpc_ctr_cpm
SELECT
  COALESCE(NULLIF(ad_campaign_type, ''), '(blank)') AS ad_campaign_type,
  COALESCE(NULLIF(ad_platform_campaign_objective, ''), '(blank)') AS ad_platform_campaign_objective,
  ad_campaign_name,
  SUM(ad_spend) AS ad_spend,
  SUM(ad_clicks) AS ad_clicks,
  SUM(ad_impressions) AS ad_impressions,
  SAFE_DIVIDE(SUM(ad_spend), NULLIF(SUM(ad_clicks), 0)) AS cpc,
  SAFE_DIVIDE(SUM(ad_clicks), NULLIF(SUM(ad_impressions), 0)) AS ctr,
  SAFE_DIVIDE(SUM(ad_spend) * 1000, NULLIF(SUM(ad_impressions), 0)) AS cpm,
  SUM(ad_spend) > 0 AND COALESCE(SUM(ad_clicks), 0) = 0 AS has_spend_without_clicks,
  SUM(ad_spend) > 0 AND COALESCE(SUM(ad_impressions), 0) = 0 AS has_spend_without_impressions
FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
WHERE source_system = 'tiktok'
  AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1, 2, 3
HAVING ad_spend > 0
ORDER BY has_spend_without_clicks DESC, has_spend_without_impressions DESC, ad_spend DESC;