My marketing spend ROI tracking template
It's chef's kiss
When lead generation was a big part of my job, I found myself running the same reports over and over again and to save time, ended up creating this monster of a spreadsheet which was the accumulation of all those reports I kept looking up.
I’d find myself asking questions like “oh yeah what was last month’s MQL > opportunity rate again?” and then go run a report, but I found that having every metric I was tracking recorded in a monthly database was just way easier to access.
I also found that inputting the data each month was kind of meditative, and mannually putting it all in also meant I had that focus time where I was looking at the numbers and really reading them and what they meant, rather than just glancing at them.
If you follow me on Twitter, you may have seen me saying I was spending my nights recreating a spreadsheet from memory…
Yes, it’s a lot of columns, but as more and more people in the business saw my spreadsheet, they kept saying “it’d be nice to have X in there as well” - and they were right. So what was a small spreadsheet grew into a big one, and now I’ve spent several nights re-creating it from memory to share with you.
Here’s the breakdown:
I did this monthly, but you can also do it weekly/quarterly/whatever your need is. If you have an idea of your average sales cycle length, it would be most helpful to align this spreadsheet with that cadence. For me, I would put time on the first weekday of the month aside to fill this out.
So, to get started on customising this for yourself, input your channels. I put some examples in to show granularity (e.g. Google Adwords, organic search, and Display Network are all individual), but this should be based on what you’re capable of tracking and what you’re currently promoting. My rule was that once a channel was input once (like if you tested out a new channel), it was there for good and would be included every month after that.
Always put in an “unknown” row for channel because 1. you will always have random leads and you don’t know where they came from and 2. if your “unknown” bucket is really big, it means you have a tracking problem that needs to be solved. If your “unknown” doesn’t get smaller over time, you haven’t fixed your tracking problem.
Then, volume of leads, pretty straight forward. When I got to this section, I would then pull up the details of the “unknown” leads and track down the salesperson assigned to them (if they had one) and ask directly about them. Sometimes, the salesperson mannually input them for whatever reason into Salesforce, and in best case scenarios they could remember what the original channel was. I’d then take that lead out of “unknown” and put them into the correct channel and make a note of it or corrected it in Salesforce. I did this enough times that I could just send a list of names to salespeople at the start of the month and they knew to come back to me with the missing information.
If you have a goal to grow a specific channel, then the % of leads column (which is “how much is each individual channel contributing to the total number of leads”) will help you track that. If this is not a goal, you could delete this but I recommend keeping it to ensure you have a safe spread of risk across your channels and you are not over-relying on one or two.
Cost per lead rounds out the “lead” section.
Next up in a standard funnel is MQL. I have also put in “MQL bucket 1”, it’s really just a placeholder. If there is a specific part of your definition of MQL that you want to track separately, it’s great to put that in this spreadsheet. For example, one of your MQL definitions could be number of licences, and you have a sweet spot where customers who are sized 11-50 licences are the most profitable for you and you’re trying to optimise toward that size customer. I recommend breaking MQL down into more meaningful buckets.
From here it’s quite reptitive, with MQLs to demos (if you do demos), demos to opportunities, and opportunities to close won. Within these groups, there’s the same columns of total number (volume) of Y, X to Y conversion, cost per Y, % of Y. It’s a very granular view of your entire funnel that honestly is great as a retrospective.
Finally, the money maker. ROI in month and ROI all time.
*disclaimer: if you know your shortest sales cycle is more than one month, then obviously adjust the close won in month and ROI in month columns to something more helpful*
This is more of a cohort tracking.
Of the leads you generated this month, how many of them closed or will close before the end of the month?
When you’re capable of making enough quick flips in sales to make back your marketing spend within the same month that you spent the budget, it makes for a very happy finance team.
But also, if you get an ROI of less than 1 at the end of the month, you might actually find that it goes up when the customers who have a longer sales cycle finally close. This is the beauty of the ROI all time metric.
The longer you maintain this spreadsheet, the more you’ll be like “holy shit, something we were doing last August just passed the 1000% ROI mark?” and then you go back and look at the ads you were running in August and see what you were doing that worked SO well.
Thanks to this, I’ve re-run old campaigns that at first seemed like flops but were actually very reliable long-term campaigns.
Spreadsheet housekeeping
A spreadsheet like this can get really big, really fast, so generally I kept it organised with 1 year per tab, with the most recent month at the top. When I first created it, I think I retrospectively filled it in with about 6 months of data, and I recommend that too because otherwise it is depressing looking at an empty spreadsheet, and more than that is too much work.
Now, I filled this template with completely made up numbers but tried to hide a few interesting things here and there. There’s clear drop off points in the funnel of certain channels, a <1 in-month ROI in January which leads to this hypothetical case study reducing their spend in Feb but seeing that spend reduction backfire as it’s based on a hasty decision and lack of data.
There’s some other interesting things to look at in there, but I don’t expect you to dissect it.
Please do copy it, share widely, and let me know what you think of it!
A couple of links
If you work in an agency and are wondering if your salary is above or below market rate, this spreadsheet is a huge project where people have anonymously submitted their pay, location, title, seniority, sexuality, and sometimes the name of where they work.
It’s a very generous community project that could really come in handy! It takes a while to load and is mainly USD, but if you ctrl/command+F “AUD” you’ll spot the Aussie locations quickly.
I’m not much of a device person, but this is really cool and I am tempted to buy it. Also a very slick website.
I hope we all know I am a big fan of marketers caring about accessibility by now, and this article even taught me an extra thing or two. It goes into how you should consider the context of the user when writing image descriptions and gives a helpful formula to follow.