Update April 2016: This calculator is somewhat dated now (it has been here for over 2 years). It still works and does the job well, however it's admittedly not very user friendly. I'm working on a better version, which will be published soon.
Please be sure to read the detailed guide below before using the spreadsheet.
|Click the image above to get to the calculator|
This digital marketing strategy template will help you work out projected ROI from your paid marketing channels (PPC, Display, Private Banner and Email Marketing/Lead Nurturing).
You can use it to test the viability of new channels and to work out what you will get back on the investment you make. Crucially, this sheet also takes into consideration the lead nurturing/email marketing element over time, which is often forgotten about.
The only areas of the sheet you need to edit are the areas in pale blue from Line 26 down - if you add your values in here, you will see overview figures underneath that will highlight your overall ROI from the figures you enter.
You can then examine the rest of the sheet to see how these figures were arrived at and for more specific information for each channel.
You can play about with the pre-set values in the main body of the spreadsheet if you really want to, just be aware that editing the wrong cell will send the calculator out of whack.
Areas For You To Fill In
In order for this calculator to work, you need to have some basic information or estimates at hand:
- - Budgets - how much will you spend on each channel, such as Google Adwords, Facebook Ads etc
- - How much is an average cost per click on each platform for your keywords?
- - If you are paying per impression, what is your CPM (cost per thousands of impressions)?
- - Lifetime value of a customer - how much do you think a customer is worth to you over over the long term? An educated guess is fine, but try to be realistic as this will skew the results if it's too far off
- - Landing page conversion rate
- - Lead to customer conversion rate
- - Email marketing conversion rate
Additional note: this part of the spreadheet is now light blue.
|Fill in the values and the spreadsheet will give you quick results as you type, in green|
Purchase Model - This is a description of how you pay for each channel. For example, PPC will be paid per click (CPC) and Banner Advertising would normally be paid per thousands of impressions (CPM). Display advertising can be paid either way, depending on your own settings.
£ Rate (CPC/CPM) - This describes how much you are paying for the above mentioned purchase models. How much is the average Cost-per-Click? How much do you pay for one thousand banner impressions on average
Number Of Impressions Purchased - Obvious one - how many units of 1000 impressions are you buying? Example - if you are buying 500,000 impression, you are buying 500 units (500,000/1000=500)
Number Of Clicks - The total number of clicks you will receive according to budget at cost per click, or according to number of impressions and click through rate.
Landing Page Conversion Rate - This is the percentage rate at which your landing pages convert at. For example, if 100 people visit and you end up with 20 leads, that's a 20% conversion rate.
New Leads From Landing Page Conversion (Avg Monthly) - This is the actual number of new leads that your landing pages will have generated, according to the conversion rate above.
Conversion Of Leads To Customers - This is the average percentage rate of leads that turn into paying customers. For example, if you have 100 leads and 15 of them turn into customers, that's a 15% conversion rate of leads to customers.
Number Of New Customers Acquired - This is the total number of new customers acquired according to the calculation above.
Cost Per Customer - This tells you how much each customer has cost you from each channel. For example, if you spend £100 on Adwords PPC and you get 4 customers, your cost per customer is £25.00.
LTV (Lifetime Value) Per Customer - This is the value that each customer has to your business over the long term. For example, if your product costs £100 and you think they average customer will buy 3 products over the long term, your lifetime value per customer is £300.00.
Number Of New Leads Who Didn't Convert To Customers - Simple - this is the number of leads from your landing pages who didn't convert to customers. So, if you had 100 leads and 15 of them turned into customers, the number of leads who didn't turn into customers is 85.
Total New Customers Acquired Annually - Again, this is easy - the total number of customers that each channel would deliver over a year (monthly customer acquisition figure x 12)
Total Lifetime Revenue From Channel - This is the amount of lifetime revenue that each channel will deliver. Using the example above, if each customer is worth £300.00 to your business over the long term and the channel brings you 50 new customers, that's £15,000.00 in total lifetime revenue.
Email Marketing/Lead Nurturing Channel Definitions
Monthly Cost - The amount that your email marketing software and/or service costs you.
Month - Yep, just the month of the year here :-)
Database Size - This tells you how much your email marketing/lead nurturing database size will grow according to your advertising channels, landing page and lead-to-customer conversion rates
Conversion Rate - Typical email marketing conversion rates - it's set to 1% in the spreadsheet, although you can change this value to suit your own business/industry
New Acquisitions - The number of new acquisitions that your email marketing/lead nurturing campaign will deliver according to the number of leads being fed into the system and the email marketing conversion rate.
Cost Per Acquisition - How much each new customer that's delivered through your email marketing system costs you (email marketing monthly costs/number of new customers).
LTV Per Acquisition - Same as above - the amount each new customer is worth to your business over the long term.
Lifetime Revenue - Again, same as above - the amount of new lifetime revenue your email marketing channel will deliver you (LTV x number of new customers that month).
Total New Acquisitions Per Annum - Simple - the total amount of new customers that your email marketing channel is delivering your business
Total Lifetime Revenue - Simple again - the amount of total lifetime revenue that your email marketing channel will deliver you
Overall Performance/ROI Definitions
Total New Sponsors - total new sponsors that the channel will deliver annually
Total Lifetime Revenue - total new lifetime revenue that the channel will deliver annually
Total Annual Costs - total costs for the channel annually
ROI% - the crucial figure! ROI from your channel
Other Costs Pulled From Marketing Budget - any other costs from your digital marketing strategy that you want to consider when you work out your overall ROI - things such as design, software (not email) etc.
Hope it helps!
Thanks for reading,
By Alan MacDougall
Got a nagging marketing question? Ask me here.
_ _ _
Revenue Builder is a small business marketing strategy advice blog, written to help small business owners and startups maximise sales revenue.