ROI Calculator For Paid Sponsorships

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.

Here are the building blocks to build your calculator; I'll walk you through each of these

• 1. Collect data

• 2. Build funnel

• 3. Set scenarios

• 4. Analyze CAC and ROAS

• Adobe example and video walkthrough

Want the pre-built spreadsheet for this?

Subscribe to Premium Access below to get the spreadsheet and video tutorial.

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.

1. Fee - how much you pay

2. Impressions - how many people will see your ad

3. 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 now¹ - all we care about is how many visitors are going to land on your website in the next step.

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:

1. Website visitors. Pull the average monthly visitors to your site of the previous 30 days using Google Analytics.

2. 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).²

3. 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.³

Next, using the data you just gathered, calculate your conversion rates throughout your funnel, like this:

1. 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.

2. 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.

3. 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.

4. 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…

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 —

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:

1. Customer Acquisition Cost (CAC) - how much it’ll cost you to get a new paying customer

2. 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.

Become a paying subscriber of Premium to get access to this post and other subscriber-only content.

Already a paying subscriber? Sign In.