Guided project · 5 steps Cellwise Plus
UTM channel rollup in Excel
Clean up inconsistent UTM tags, then roll sessions and revenue up by medium and find the medium that earns most per visit.
You will use: LOWER, SUMIFS, ROUND, MAX, MATCH, INDEX
Starting data
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | utm_source | utm_medium | Sessions | Conversions | Revenue | Source / medium | Conv. rate | Medium | Sessions | Revenue / session | |
| 2 | cpc | 4210 | 126 | 11340 | cpc | ||||||
| 3 | paid_social | 2650 | 41 | 3280 | |||||||
| 4 | paid_social | 1180 | 17 | 1445 | paid_social | ||||||
| 5 | newsletter | 1460 | 52 | 4940 | |||||||
| 6 | Newsletter | 390 | 11 | 1045 | Best medium | ||||||
| 7 | paid_social | 1720 | 23 | 1840 | |||||||
| 8 | bing | cpc | 640 | 22 | 1870 | ||||||
| 9 | klaviyo | 820 | 30 | 2610 |
Build it step by step
Step 1
Google Analytics treats "Facebook" and "facebook" as two different sources, so tagged links split into rows. Build one clean label in F2: the source and medium in lowercase, joined by " / ". Fill to F9.
Step 2
Conversion rate is conversions divided by sessions. Write it in G2 and fill to G9.
Step 3
Total sessions for the medium in I2. SUMIFS ignores upper and lower case, so "Email" and "email" are added together. Write it in J2 and fill to J4.
Step 4
Revenue per session tells you what a visit from each medium is worth. Divide that medium's revenue (SUMIFS on column E) by its sessions in J2, rounded to cents. Write it in K2 and fill to K4.
Step 5
Which medium earns the most per session? Put its name in J6.