Marketing analytics: spend, leads and CAC

The CMO wants a one-glance view of paid media in 2026 so far. Using the weekly ad_spend rows, return one row per channel with four columns: channel, total_spend (sum of spend, rounded to 2 decimals), impressions (sum) and clicks (sum). Only channels that have ad_spend rows appear. Sort by total_spend from highest to lowest.

For every campaign with ad_spend rows, the paid media manager wants campaign-level efficiency over the whole period. Return name, ctr_pct = total clicks ÷ total impressions × 100, and cpc = total spend ÷ total clicks, both rounded to 2 decimals. Compute each ratio from the campaign's totals, not from an average of weekly ratios. Sort by ctr_pct from highest to lowest, then name A to Z.

Sales asks where every lead came from. Leads with a NULL campaign_id are organic and must be counted under the channel label 'organic'; all other leads take their campaign's channel. Return channel, leads (number of leads) and customers (leads whose converted_at is not NULL). Channels with no leads do not appear. Sort by leads from highest to lowest, then channel A to Z.

Finance wants budget pacing for all eight campaigns. Return name, budget, spend (total ad_spend for the campaign rounded to 2 decimals, 0 when it has no ad_spend rows) and budget_used_pct = spend ÷ budget × 100 rounded to 2 decimals. Affiliates are paid per customer outside ad_spend, so they show 0 spend here. Sort by budget_used_pct from highest to lowest, then name A to Z.

Before renewing contracts, the CMO wants every campaign that spent money but never produced a single lead. Return name and total_spend (sum of its ad_spend rounded to 2 decimals) for campaigns that have at least one ad_spend row and no rows in leads. Any row order is fine.

The board deck needs CAC for the paid media channels. For each channel that has ad_spend rows, return channel, spend (total ad_spend rounded to 2 decimals), customers (leads from that channel's campaigns whose converted_at is not NULL, 0 if none) and cac = spend ÷ customers rounded to 2 decimals, NULL when customers is 0. Organic and affiliate leads are excluded because their cost is not in ad_spend. Sort by channel A to Z.

Growth wants to know whether lead quality is changing. Group leads created from 2026-01-01 to 2026-09-30 by the month they were created, as text 'YYYY-MM'. Return cohort_month, leads, customers (leads in that cohort whose converted_at is not NULL) and conversion_rate_pct = customers ÷ leads × 100 rounded to 2 decimals. Include organic leads. Sort by cohort_month from oldest to newest.

For every campaign with ad_spend rows, return name, spend (total ad_spend rounded to 2 decimals), revenue (sum of first_order_value over its converted leads rounded to 2 decimals, 0 if none), roas = revenue ÷ spend rounded to 2 decimals, and roas_rank: RANK() by the rounded roas, highest first, so ties share a rank. Sort by roas_rank, then name A to Z. This is first-order ROAS only, not lifetime value.