Level 3QUERYProject, 8 min
A channel report from one QUERY
The task
Twelve campaigns across four channels. Build the report a marketing lead reads on Monday: spend and revenue by channel, ranked, with return on ad spend, and how many campaigns paid for themselves.
- Spend and revenue by channelIn G1, group by channel and total both spend and revenue:
=QUERY(A1:E13, "select B, sum(C), sum(E) group by B") - Rank it and name the columnsEdit G1: put the biggest revenue first and rename the sums. Add
order by sum(E) desc label sum(C) 'Spend', sum(E) 'Revenue'to the end of the query. - Return on ad spendIn J2, divide each channel's Revenue by its Spend, straight from the QUERY output:
=ARRAYFORMULA(I2:I5/H2:H5). - Which campaigns paid backIn H8, count the campaigns whose Revenue beat their Spend. FILTER the campaign names where E2:E13>C2:C13, then count them with COUNTA.
The data
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Campaign | Channel | Spend | Leads | Revenue | ROAS | ||||
| 2 | Brand terms | Paid Search | $2,400 | 96 | $14,400 | |||||
| 3 | Competitor terms | Paid Search | $3,100 | 41 | $2,950 | |||||
| 4 | Product terms | Paid Search | $5,200 | 88 | $17,600 | |||||
| 5 | Prospecting video | Paid Social | $4,600 | 52 | $3,980 | |||||
| 6 | Lookalike 1% | Paid Social | $3,800 | 61 | $9,120 | |||||
| 7 | Retargeting | Paid Social | $1,900 | 47 | $8,550 | Campaigns that paid back | ||||
| 8 | Webinar promo | $600 | 73 | $4,380 | ||||||
| 9 | Newsletter | $250 | 22 | $1,750 | ||||||
| 10 | Win-back | $400 | 18 | $3,240 | ||||||
| 11 | Display remarketing | Display | $1,700 | 23 | $2,890 | |||||
| 12 | Contextual | Display | $2,200 | 14 | $1,430 | |||||
| 13 | YouTube | Display | $3,300 | 19 | $2,310 |