Excel Magic

Excel is an indispensable tool for any digital marketer, as explained by Cathal Melinn on the DMI podcast. He and Will Francis explore its enduring importance and advocate for crafting custom metrics to gain deeper insights into user behavior and purchasing patterns.

You can also listen to the full podcast on Life as an Analyst.

TRANSCRIPT

Will: So, I mean, what are the most valuable tools in your box when you work in analytics?

Cathal: So, the most valuable tool I think to any analyst is Excel or spreadsheets. Now, I know that there's a lot of other kinds of solutions, you know, dashboards that you can create and direct APIs, and integrations with websites. But data crunching in a spreadsheet allows you to take data from two separate sources and combine them under the same kind of rule and methodology. Because if you're applying an Excel formula that divides one number by another number, that's what it is. But if, you know, and you've defined what that is, if that's what a conversion rate is, it's one number divided by another number and that your conversion rate. It could differ if you're using conversion rate in Google analytics is your transactions divided by your sessions. But, you know, maybe sessions isn't the metric you want to use. Maybe users is, which would be my preference.

And likewise, maybe if your E-commerce site, your Shopify or your Magento or your bespoke site says that your conversion rate is the number of unique cookies on a browser or the number of times that the shopping cart opens divided by, you know, into the number of transactions. Maybe that's how they work a conversion rate. So, if you use native metrics to try and tell a story, it can just distort the image you need, what I call a single source of truth. And that's unified data in a spreadsheet or in some kind of table or whatever that you create and you define rather than taking predefined metrics from the platforms. I like to make up my own metrics.

Will: That's very interesting. So, I mean, you know, I'd go further. I'd say that, you know, anybody in business or marketing should have a decent grasp of Excel or numbers, or Google sheets, or whatever your preferred spreadsheet software is. Because, like you say, you can, you know, achieve so much that you can't do with a native platform.

Cathal: Well, that's the thing. And it's like, even though you can use a dashboard or any of the native platforms, your understanding of how the metrics are created, what they mean is much deeper and richer if you know how to create that formula yourself. And one of the knock-on effects I've discovered as an analyst, it's something I really want to talk about because it's something that no one really mentions to most, but I'm trying to champion it at the minute, and that is, make up your own metrics. And I'm not saying talk BS, I'm saying, if you want to know what a particular metric is that isn't in the standard suite of metrics in your dashboard, make it up. Like, you noticed in my example that the forecasting I talked about cost per user rather than cost per click. And there's a reason for that. Because users is, I prefer the user metric than a click metric.

So, a user in any analytics tool is generally a unique browser cookie. So, it's, in all intents and purposes a person, it's a person who is using a browser to access your site. The technical reason is it's a unique cookie on a browser. But let's just like get philosophical about this and say, well, look, that is actually the closest metric we have to person. We don't have a metric in Google Analytics and in Adobe and anything like that called people. We sell to people and people visit our website. So, we need a people metric. The best defacto people metric we have is users. So...

Will: Because the way people's buying journeys are very messy and they can take place over a number of visits, over sometimes long period of time.

Cathal: Lots of sessions, it's like one user, one person can have lots of sessions. And if you want to know how much...if you think about it, work backwards from, say your E-commerce transactions. You want to say, what is the conversion rate for a single human on your site? So, the natural conversion rate in Google Analytics is the number of transactions divided by the number of sessions. So, multiple visits by a single user. So, it's blurry because sessions don't buy, people do.

Will: I can see that on a tee-shirt.

Cathal: Yes. Well, and the followup is when we get to media. So, rather than using the default conversion rate in Google Analytics, which is transactions divided by sessions, I create my own one, which is user conversion rate, which is a transactions divided by users. Okay, so you just do that in Excel and find out what is that conversion rate. So now you know what the conversion rate of a single user is on your site. So, then you figure out, well, what does it cost to get a single user on my site?

So, the metric you're given in Facebook, and LinkedIn, and Google and all that stuff is cost per click. Okay? A user will click, a person may click multiple times and have multiple sessions. So you've got this compound effect of blurry metrics that are layering on top of each other and you're meant to use that conversion rate and that cost metric. So, for me, it's not clear cut. So I work out what's my user conversion rate and what's my cost per user, and that gives me a much clearer journey from, what does it cost to get a human on the site and how likely is that human to buy.