cf #72: an ugly-but-functional reporting spreadsheet
also, a love note to VLOOKUP
You are reading contentfolks—a fortnightly blend of sticky notes, big content ideas, and small practical examples. Thank you for being here! ~fio
Hey there 👋
One of the first things I did after joining Float last month was build myself an ugly-but-functional content reporting spreadsheet and spend half a day manually populating it, column by (painful) column.
Analytics tools and AI-powered magic can make your life easier and turn avalanches of data points into useful insight in about five minutes—but something important gets lost when you don’t get your metaphorical hands a little dirty in the data. Or at least that’s how it always feels to me, which is why I’ve been defaulting to a (semi)manual reporting spreadsheet for the last 6 years.
Let me show you 😉
Tab 1: the dashboard
I primarily use my ugly-but-functional spreadsheet to keep an eye on organic content growth. The dashboard reports on two key metrics:
Monthly organic sessions
The most revenue-relevant metric we can track with our current setup (e.g. signups, leads, MQLs, $$$)
The data is pulled from Tabs 2 and 3 (see below) and plotted onto a chart. Kind of ugly, super simple, and extremely effective—if anyone on the team wants to know how things are going, this is where they’ll always find their top-level answer.
Tab 2: organic traffic
Tab 2 works as follows:
I list all my content URLs in column A
At the beginning of each month, I download the previous month’s organic sessions from GA4 and use my absolute favourite Excel formula of all time, VLOOKUP, to match each piece with its traffic value for that month
Conditional formatting is automatically applied to the column, so month-on-month growth (or lack thereof) is clear at the individual piece level
The most painful part of building this spreadsheet is starting it from scratch when you have data from several months that needs to be pull in. Once everything is in place, updating this tab on a monthly basis takes me less than 10 minutes.
Tab 3: the most revenue-adjacent metric
Tab 3 works exactly like Tab 2, except:
Instead of using values exported from GA, it uses a revenue-adjacent metric (e.g. signups or MQLs) exported from whichever reporting tool we use
Column B is a sum of each piece’s values for that calendar year
Sorting this sheet by decreasing column B values shows me the most valuable content pieces from a revenue perspective (aka the ones that need to be monitored and protected). I add a blue border around them to remind myself I need to pay special attention to their month-over-month performance.
Tab 4: final comparison
In Tab 4, I simply bring the traffic values from Tabs 2 and the revenue metric from Tab 3 together. Now I see my most valuable pieces and their organic performance, so I have an easy way to prioritise optimisation work wherever needed. In this example, row 8 would be a great candidate for further investigation.
That’s kind of it, really. There’s other stuff I might look at if I need to report on something specific (e.g. cluster rankings or share of voice), but honestly these ugly four tabs do 90% of the job every time. And yes, I know that I could automate all of the above—but I don’t want to, and I don’t think I should, either!
Some shortcuts are forever useful (VLOOKUP: true game-changer), but I still find it valuable to do some of the tedious stuff manually. When I’m new to a team or content programme, this spreadsheet is an excellent way to get close to dozens or hundreds of existing pages within an afternoon. Over time, this level of hands-on involvement also helps me develop and fine-tune my gut feeling for what is ‘normal’ or not.
Fun fact: doing this spreadsheet after joining Float is how I spotted a reporting bug in GA4 😅 Here is me pinging analytics expert Steve Lamar after I noticed discrepancies between the data exported as a .csv and the numbers in GA4’s interface:
And after trying to replicate this bug and polling other analytics folks for about half an hour, here is Steve confirming that I was not, in fact, losing my mind in a spreadsheet:
…we’re still waiting for Alphabet to send us a quadrillion dollars for spotting this bug 🐛
