Case study: returns by marketing channel
From: Jun Park, growth lead, Fernhill Home (a fictional online store)
About: Where should next quarter's budget go?
Here are last quarter's twelve campaigns across search, display, email and social. For each campaign, I need click-through rate, cost per conversion and return on ad spend. Then the spend, revenue and ROAS for each channel, and which channel returns the most per dollar.
Three ratios run most marketing reports. Click-through rate (CTR) is clicks divided by impressions. Cost per conversion (also called CPA) is spend divided by conversions. Return on ad spend (ROAS) is revenue divided by spend.
Each is a simple division per row. The trap comes at the channel level: a channel's ROAS is its total revenue divided by its total spend, not the average of its campaigns' ROAS, which would give a tiny campaign the same say as a large one.
All campaigns and figures are made up.
=G2/C2
=SUMIF($B$2:$B$13,L2,$G$2:$G$13)/SUMIF($B$2:$B$13,L2,$C$2:$C$13)
Campaign ROAS, and channel ROAS from totals.