- Entrepreneurship Handbook
- Posts
- ROI Calculator For Paid Sponsorships
ROI Calculator For Paid Sponsorships
Steal this Gsheet to know if you should pull the trigger on a paid marketing thing or not
![](https://media.beehiiv.com/cdn-cgi/image/fit=scale-down,format=auto,onerror=redirect,quality=80/uploads/asset/file/424c6fa6-dfc8-4eba-9924-c4fb652471cf/3.png?t=1711468393)
|
I learned this too late: how to prove a return on marketing spend.
It baffles many marketers. Myself included for a while.
Meta and Google Ads have made it much easier, but what about the âOtherâ paid marketing channels? How do you know if theyâre worth it?
I encountered this problem many times over the past 5 years. I became tired of not knowing. So I created a spreadsheet that calculates whether a paid ad/sponsorship is worth it or not, and now Iâm pumped to share it with you.
This is a crash course on growth marketing. Itâs a little technical and jargony but itâll be worth it and hereâs why â
If you find a paid marketing channel that achieves a positive ROI, you can potentially âback the truck upâ and dump cash into a âmoney-printing machineâ to grow your business.
Itâs all about having confidence in your forecast.
By the end of this post, youâll have exactly that.
âHalf the money I spend on advertising is wasted. The trouble is, I donât which half.â
Here are the building blocks to build your calculator; I'll walk you through each of these
Want the pre-built spreadsheet for this?
Subscribe to Premium Access below to get the spreadsheet and video tutorial.
![](https://media.beehiiv.com/cdn-cgi/image/fit=scale-down,format=auto,onerror=redirect,quality=80/uploads/asset/file/e99f5419-fdd2-4acd-a55b-50286abc4a97/Screenshot_2024-03-18_at_3.23.45_PM.png?t=1710789832)
ROI Calculator Gsheet
1. Collect campaign performance data estimates
When you become a paid sponsor, you receive some numbers about how the campaign will perform.
These are found in a âmedia kitâ or âsponsor packetâ for the event, newsletter, or media website youâre sponsoring. If they do not provide this data, request it. They should have it from past sponsorships. It will probably be a range.
If they donât have it, take this as a sign to walk away. If they cannot provide any estimates of the value you are purchasing, youâll be shooting in the dark.
What data to ask for
Youâre looking for 3 key pieces of information.
Fee - how much you pay
Impressions - how many people will see your ad
Clicks or CTR - how many clicks your ad will get or the percent of impressions that click on your ad
If you arenât given an estimated number of clicks, you can calculate it by multiplying the number of impressions by the CTR.
Example: 100,000 impressions with a CTR of .7% is ~700 clicks.
We donât care about CPM and CPC right now1 - all we care about is how many visitors are going to land on your website in the next step.
![](https://media.beehiiv.com/cdn-cgi/image/fit=scale-down,format=auto,onerror=redirect,quality=80/uploads/asset/file/3feffaba-44e7-422b-8c13-9027a6b4a837/1.png?t=1711468201)
The âgobletâ funnel
2. Plug in your businessâs funnel conversion rates
Your funnel is the âjourneyâ a user takes from the moment they see your ad to the moment they pay you. Reminder that this funnel is for a B2C site that offers both a free plan and a free trial (yours might not offer either, which weâll address below).
First, grab this data:
Website visitors. Pull the average monthly visitors to your site of the previous 30 days using Google Analytics.
Free Plan, Free Trial, and Paid Plan users - pull the averages for each of these using your product dashboard or a tool like Amplitude, ProfitWell or ChartMogul (GA can do this, too, if itâs set up right).2
For LTV, this is the expected amount of money a customer will pay you, based on your historical data. For example, if a customer spends $50 a month on your SaaS product over their entire relationship with your business, which lasts 6 months, then the LTV is $50*6 months = $300.3
Next, using the data you just gathered, calculate your conversion rates throughout your funnel, like this:
Clicks to Website Visitor rate. This is the percentage of clicks on your ad that turn into actual visitors on your website. It might sound silly because youâd assume that every click equals one visitor to your site, but youâd be surprised how clicks get lost in the shuffle between platforms.
Website Visitor to Free Plan rate. This is the number of visitors who sign up for a free plan. Divide the # of free plan signups by # of website visitors. For example: if you have 100,000 website visitors in the last 30 days and 13,000 free plan signups over the same period, the conversion rate is 13% because 13k/100k = 0.13.
Free Plan to Free Trial rate. This is the number of free plan signups who start a free trial. Divide the # of free trials by the # of free plan signups. For example: if you have 13,000 free plan signups and 6,000 free trial starts, the conversion rate is 46% because 6k/13k = 0.46.
Free Trial to Paid Conversion rate. This is the number of free trials that convert to a paid plan. Divide the # of paid conversions by the # of free trials. For example: if you have 450 paid conversions and 6,000 free trial starts, the conversion rate is 7.5% because 450/6k = .075.
If you do not offer a Free Trial or Free plan, cut those parts out of your funnel and measure conversion from Website Visitors direct to Paid plans - the spreadsheet I made has tabs for all three scenarios.
Reminder. Everything is set up for you if you use my speadsheetâŠ
![](https://media.beehiiv.com/cdn-cgi/image/fit=scale-down,format=auto,onerror=redirect,quality=80/uploads/asset/file/e99f5419-fdd2-4acd-a55b-50286abc4a97/Screenshot_2024-03-18_at_3.23.45_PM.png?t=1710789832)
Subscribe to Premium Access at the bottom of this email to get the Gsheet template and video walkthrough.
3. Set best, average, and worst case scenarios
Next, letâs set up our best, average, and worst case scenarios.
đ This part is helpful for getting team buy-in as it shows you thought through pessimistic and optimistic outcomes.
For worst case, assume 50% of Clicks turn into website visitors. Set your Funnel Conversion rates to your worst monthâs performance. Be conservative. You donât want to be surprised. Drop your LTV lower, perhaps by $10-$30.
For average case, assume 75% of clicks turn into website visitors. Set your Funnel Conversion rates based on the actual averages you found. Set your LTV to what it is today.
For best case, set your conversion rates to 90% or more. Use optimistic Funnel Conversion rates - perhaps the target audience youâre sponsoring is a perfect fit for your product. Add $5 or more to your LTV as perhaps this audience will buy more or stay subscribed longer.
I color code these boxes in the spreadsheet to make it easier.
Worst - red
Average - yellow
Best - green
Time for the last step â
Have you heard of Prompts Daily newsletter? I recently came across it and absolutely love it.
AI news, insights, tools and workflows. If you want to keep up with the business of AI, you need to be subscribed to the newsletter (itâs free).
Read by executives from industry-leading companies like Google, Hubspot, Meta, and more.
Want to receive daily intel on the latest in business/AI?
4. Analyze your CAC and ROAS and make a decision
This is the fun part. Now itâs time to see how this paid opportunity will do. These numbers will tell us if this sponsorship deal is ultimately worth it, because now we can calculate the two most important metrics for growth:
Customer Acquisition Cost (CAC) - how much itâll cost you to get a new paying customer
Return On Ad Spend (ROAS) - how much âprofitâ youâll make from the ad
Putting it all together with an example and video walkthrough
Make the first row the âFeeâ - this is the price youâre paying for the sponsorship. By the way, you might be able to negotiate a better price for your Best case đ - you donât get what donât ask for!
Below that, make the next rows Impressions, CTRs, and Clicks - refer to step 1 to plug in this data that you collected.
The next rows are your funnel steps: Website visitors, Free plans, Free Trials, Paid conversions.
The last three rows are your Revenue, CAC and ROAS.
Finally, make columns for your worst, average, and best case scenarios.
Now itâs time to plug in the data to get your results - watch the following video for an example.
On the other side of this paywall is
A video of me walking you through an example of Adobe deciding to sponsor a community website
The Gsheet calculator
How to analyze CAC and ROAS
You can unlock this video and the Gsheet calculator for $5 - plus, youâll get access our top 3 reads on Medium without paying for a Medium membership). If you find EH useful, please upgrade to buy us a coffee!
![](https://media.beehiiv.com/cdn-cgi/image/format=auto,onerror=redirect/uploads/publication/logo/939df14c-db9f-4dba-8d30-1c11ef4a1093/thumb_EH_ICON.png)
Subscribe to Premium Access to read the rest.
Become a paying subscriber of Premium Access to get access to this post and other subscriber-only content.
Already a paying subscriber? Sign In