As a marketer you hear the term "marketing funnel" all the time, but do you know how to calculate yours? Learn how to model your funnel from CPC to LTV.
- [Instructor] You've probably heard of a marketing funnel, but do you know how to make one in Excel? Let's walk through how to do that. To follow along, go to the exercise file 2.1- Mapping out your marketing funnel. In this tab we provided all of the formulas you need from how much it costs to get someone to click on an ad, all the way through to the lifetime value of your customers. Let's go through a few examples. First, let's work out clickthrough rate. For this, we just need to choose Clicks, divided by Impressions.
Hit Enter. Here we go, 0.63% of the people who saw our ad actually clicked through. Conversion rate, also important. Going to choose Orders, divided by Clicks. And we have about 1.5% of the people who went to our website actually bought something. If we want to scroll down a little bit further and see why this is as low as 1.46%, we can actually break this out into two different metrics. "Add to Basket Rate", and "Basket to Order Rate". Those are the two steps that someone needs to go through to actually buy something on the website.
So let's work out "Add to Basket Rate", which is Add to Basket, divided by Clicks. So, only 3% of the website visitors actually add something to basket. Once they add something to basket, we're just going to divide Orders by Add to Basket. A whopping 50% actually complete their purchase. So if we wanted to improve this, we'd want to look at this metric here. Now let's work out some of the cost metrics. Let's do Cost per Click. This is Spend, divided by Clicks.
So it costs us 82 cents to get someone to the website. What's our Cost per Order? That is, again, Spend, divided by Orders. 56 bucks to sell something on our website. But what is the product worth? For that we need "Average Order Value", which is the Revenue, divided by your conversions, in this case Orders. So it's costing us 56 bucks to sell a $200 product. What's our "Return on Investment"? For that we just need to, in brackets, subtract the Revenue from the Spend, and then divide that by the Spend.
Making a 253% return on investment, not bad. But how much is a customer worth to us? They're not going to buy just one thing. If we work in an estimate of how many orders someone makes over their lifetime, in this case we've chosen 3.5. To work out the lifetime value of a customer, we just need to times the Average Order Value, by our estimate of how many times they're going to purchase in their lifetime. Almost $700. Now that you've worked out your key funnel metrics, you have a model of your business, and you can use that to make marketing decisions confidently.
Michael begins with the basics, showing how to work with marketing data sources and explaining basic formulas for analysis. Next, he walks through how to build a data-driven marketing plan, including mapping out your marketing funnel, reviewing volume vs. cost tradeoff, and running A/B testing. Michael shows how to analyze a campaign's performance, working with PivotTables, charts, and dashboards. Finally, he demonstrates how to set up marketing campaigns in bulk, including manipulating text for ad copy generation and automating the most boring, repetitive tasks with macros.
- Cleaning up data formatting
- Using formulas for analysis
- Building a data-driven marketing plan
- Top-down vs. bottom-up marketing plans
- Running A/B tests
- Analyzing campaign performance
- Plotting data visually
- Building report templates and dashboards
- Building bulksheets for bulk campaigns on AdWords and Facebook