Below are SQL queries I created for a media campaign analysis which includes click-through rates, conversions, ad spend, return on ad spend, and more:

-- CTR: click_through_rate

SELECT
  placement,
  total_clicks/total_impressions::FLOAT * 100 AS ctr
FROM (
  SELECT 
    placement,
    SUM(spend) total_spend,
    SUM(impressions) total_impressions,
    SUM(clicks) total_clicks
  FROM media
  GROUP BY 1) t
ORDER BY ctr DESC;


-- CPC cost_per_click

SELECT
  total_spend/total_clicks::FLOAT cost_per_click
FROM (
  SELECT
    SUM(spend) total_spend,
    SUM(clicks) total_clicks
  FROM media) t;


-- Conversion_rate for purchases made

SELECT
  source,
  medium,
  purchases_made/carts_added::FLOAT * 100 AS conversion_percent
FROM (
  SELECT 
    source,
    medium,
    SUM(add_to_cart) carts_added,
    SUM(purchase) purchases_made
  FROM conversions
  GROUP BY 1,2 ) t
ORDER BY conversion_percent DESC;


-- Cost_per_conversion total spend / total conversion

WITH total_spend AS (
  SELECT
    SUM(spend) sum_spend
  FROM media
),
total_conversions AS (
  SELECT
    SUM(purchase) sum_purchases
  FROM conversions
  WHERE revenue > 0
)
SELECT
  sum_spend,
  sum_purchases,
  sum_spend/sum_purchases::FLOAT AS cost_per_conversion
FROM total_spend
JOIN total_conversions
  ON 1=1;


-- cost per conversion by campaign

WITH total_spend AS (
  SELECT
    CONCAT_WS(' | ', platform, placement) AS platform_placement,
    SUM(spend) sum_spend
  FROM media
  GROUP BY 1
),
total_conversions AS (
  SELECT
    source_medium,
    SUM(purchase) sum_purchases
  FROM conversions
  WHERE revenue > 0
  GROUP BY 1
)
SELECT
  ts.platform_placement,
  ts.sum_spend/tc.sum_purchases::FLOAT AS cost_per_conversion
FROM total_spend ts
JOIN total_conversions tc
  ON ts.platform_placement = tc.source_medium
ORDER BY cost_per_conversion ASC;


-- ROAS Return on ad spend

WITH total_cost AS (
  SELECT
    CONCAT_WS(' | ', platform, placement) AS platform_placement,
    SUM(spend) AS total_spend
  FROM media
  GROUP BY 1
),
total_revenue AS (
  SELECT 
    source_medium,
    SUM(revenue) sum_revenue
  FROM conversions
  WHERE revenue > 0
  GROUP BY 1
)
SELECT
  platform_placement,
  sum_revenue/total_spend::FLOAT * 100 AS return_on_spend
FROM total_cost tc
JOIN total_revenue tr
  ON tc.platform_placement = tr.source_medium
ORDER BY return_on_spend DESC;


-- Cart_Abandon_rate

SELECT
  source_medium,
  (1 - purchases/carts_added::FLOAT) * 100 AS cart_abandon_rate
FROM (
  SELECT
    source_medium,
    SUM(add_to_cart) carts_added,
    SUM(purchase) purchases 
  FROM conversions
  GROUP BY 1) t
ORDER BY cart_abandon_rate;


-- avg spend by campaign

SELECT
  platform_placement,
  AVG(sum_spend) AS avg_spend
FROM (
  SELECT
    CONCAT_WS(' | ', platform, placement) AS platform_placement,
    SUM(spend) sum_spend
  FROM media
  GROUP BY 1) t
GROUP BY 1
ORDER BY avg_spend DESC;


-- How to JOIN into one table?

SELECT 
  *
FROM media2 m
JOIN conversions c
  ON m.platform_placement = c.source_medium
LIMIT 5;


-- click-through rate by month

SELECT
  TO_CHAR(date, 'MON') as month,
  --EXTRACT('month' FROM date::DATE) AS month,
  SUM(clicks)/SUM(impressions)::FLOAT * 100 AS click_through_rate
FROM media
GROUP BY 1;


-- cost per click by month

SELECT
  TO_CHAR(date, 'MON') as month,
  SUM(spend)/SUM(clicks)::FLOAT * 100 AS cost_per_click
FROM media
GROUP BY 1;


-- cost per conversion by month

WITH month_spend AS (
  SELECT
    TO_CHAR(date, 'MON') as month,
    SUM(spend) AS total_spend
  FROM media
  GROUP BY 1
),
month_conversions AS (
  SELECT
    TO_CHAR(date, 'MON') as month,
    SUM(purchase) sum_conversions
  FROM conversions
  WHERE revenue > 0
  GROUP BY 1
)
SELECT
  ms.month,
  total_spend/sum_conversions::FLOAT AS cost_per_Conversion
FROM month_spend ms
JOIN month_conversions mc
  ON ms.month = mc.month;


-- Conversion rate by month

WITH month_clicks AS (
  SELECT
    TO_CHAR(date, 'MON') as month,
    SUM(clicks) AS total_clicks
  FROM media
  GROUP BY 1
),
month_conversions AS (
  SELECT
    TO_CHAR(date, 'MON') as month,
    SUM(purchase) sum_conversions
  FROM conversions
  WHERE revenue > 0
  GROUP BY 1
)
SELECT
  cs.month,
  sum_conversions/total_clicks::FLOAT * 100 AS conversion_rate
FROM month_clicks cs
JOIN month_conversions cn
  ON cs.month = cn.month;


-- revenue by month

SELECT
   TO_CHAR(date, 'MON') as month,
  SUM(revenue)
FROM conversions
WHERE revenue > 0
GROUP BY 1;


-- ROAS by month

WITH revenue_month AS (
  SELECT
    TO_CHAR(date, 'MON') as month,
    SUM(revenue) AS total_revenue
  FROM conversions
  WHERE revenue > 0
  GROUP BY 1
),
total_cost AS (
  SELECT 
    TO_CHAR(date, 'MON') as month,
    SUM(spend) AS total_spend
  FROM media
  GROUP BY 1
)
SELECT
  *,
  total_revenue - total_spend AS difference,
  total_revenue/total_spend::FLOAT AS roas
FROM revenue_month rm
JOIN total_cost tc
  ON rm.month = tc.month;


-- cart abandon rate by month

SELECT
  month,
  (1 - purchases/carts_added::FLOAT) * 100 AS cart_abandon_rate
FROM (
  SELECT
    TO_CHAR(date, 'MON') as month,
    SUM(add_to_cart) carts_added,
    SUM(purchase) purchases 
  FROM conversions
  GROUP BY 1) t
ORDER BY cart_abandon_rate;


-- rCPM revenue generated per 1000 impressions

WITH total_revenue AS (
  SELECT
    TO_CHAR(date, 'MON') as month,
    SUM(revenue) sum_revenue
  FROM conversions
  WHERE revenue > 0
  GROUP BY 1
),
total_impressions AS (
  SELECT
    TO_CHAR(date, 'MON') as month,
    SUM(impressions) AS sum_impressions
  FROM media
  GROUP BY 1
)
SELECT
  ti.month,
  sum_revenue/sum_impressions::FLOAT * 1000 AS rcpm
FROM total_revenue tr
JOIN total_impressions ti
  ON tr.month = ti.month;


-- CPM cost per 1000 impressions

SELECT
  TO_CHAR(date, 'MON') as month,
  SUM(spend)/SUM(impressions) * 1000 AS cost_per_1000
FROM media
GROUP BY 1;

-- performance by month
WITH month_media AS (
  SELECT 
    TO_CHAR(date, 'MON') AS month,
    SUM(spend) AS total_cost,
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions
  FROM media
  GROUP BY 1
),
month_conversions AS (
  SELECT 
    TO_CHAR(date, 'MON') AS month,
    SUM(add_to_cart) AS total_carts_added,
    SUM(purchase) AS total_purchases,
    SUM(revenue) AS total_revenue
  FROM conversions
  GROUP BY 1
)
SELECT
  *,
  total_cost / total_clicks::FLOAT AS cpc_cpa,
  total_cost / total_impressions::FLOAT * 1000 AS cpm,
  total_clicks / total_impressions::FLOAT * 100 AS ctr,
  total_revenue / total_cost::FLOAT AS roas,
  total_revenue / total_impressions::FLOAT * 1000 AS rcpm,
  (total_revenue - total_cost) / total_cost::FLOAT * 100 AS roi,
  total_purchases / total_clicks::FLOAT * 100 AS conversion_rate,
  (1 - total_purchases / total_carts_added::FLOAT) * 100 AS cart_abandon_rate,
  (total_revenue - LEAD(total_revenue) OVER ()) / LEAD(total_revenue::FLOAT) OVER () * 100 AS revenue_percent_change
FROM month_media m
JOIN month_conversions c
  ON m.month = c.month;