Mission M27 · SQL · 20 min

Campaign ROI by marketing channel

Request from your manager

The marketing director must cut one channel next year: "Rank Email, Social, Search and Flyer by return on investment. What is the ROI of the best one?"

Data

Download the practice file(s):

⬇ sunshine_grocers.xlsx

Steps

  1. Query the campaigns table (48 campaigns, 12 per channel).
  2. GROUP BY channel and SUM both budget and revenue.
  3. ROI % = 100.0 * (SUM(revenue) - SUM(budget)) / SUM(budget). Aggregate first, then divide — do not average per-campaign ROIs.
  4. ORDER BY roi_pct DESC.
  5. Bonus: add conversion rate = 100.0 * SUM(conversions) / SUM(clicks).

Check your answer

What is the ROI % of the best-performing campaign channel?

Show solution

Flyer leads with 338.25% ROI ($35,500 spent, $155,578.79 revenue), then Social 312.57%, Search 270.23% and Email 199.72%.

SELECT channel,
       SUM(budget)  AS total_budget,
       SUM(revenue) AS total_revenue,
       ROUND(100.0 * (SUM(revenue) - SUM(budget)) / SUM(budget), 2) AS roi_pct
FROM campaigns
GROUP BY channel
ORDER BY roi_pct DESC;

Answer: 338.25 %

← Customer cohort retentionReorder alert: stock below reorder point →