As marketers, we tend to measure the ROI (return on investment) of programs that we are responsible for delivering. That means we often report on how many raw sales are delivered from our marketing budget without providing context into how these efforts truly affect our business.
These reports don’t usually include information about the true profitability of our campaigns by including product margins or fixed costs, because it’s not our responsibility. This often creates a disconnect between the marketing department and executives who are responsible for running a profitable business. For more information on why this is so important, please see my recent presentation on establishing True ROI.
Understanding how to measure the true ROI of marketing
In this post, we are going to talk about how to do end to end analysis of your Google Ads campaigns to determine what type of return on investment you are receiving for your search advertising.
While this lesson will cover the process that we need to go through in order to establish our ROI from paid search campaigns for e-commerce websites (i.e. websites where you can purchase online), the principles that we will be learning here will also apply to many other areas of analysis. That means we can use what we have learned here to establish ROI calculations for any marketing activity for our business.
Since we need to start somewhere, the simplest way to measure your online advertising ROI is by using reports that are available within Google Analytics.
Quick and dirty marketing Google Ads ROI assessment using Google Analytics
Good news: We have sound some shortcuts that will help you create an ROI assessment for your paid search marketing efforts in an hour or two. Now in order to take advantage of these shortcuts, you will need to have the following items in place for your business:
- You need to be running campaigns on Google Ads to drive traffic to your website (consider DSA if you haven't already)
- Your website must have Google Analytics installed to measure who is visiting your site
- Your links from Google Ads need to be tagged to work with Google Analytics (this is called auto tagging)
- Your site needs to sell products online
- You need to have configured Google Analytics e-commerce reports to track your online transactions
As you can see, we are making a LOT of assumptions that may stop you dead in your tracks. If you can trust me for a moment, you will understand why each of these items are important.
Note: Even if you don’t have these items in place, you will still learn a TON by following along with this lesson.
Gathering Data to Calculate Your ROI
In order to do a proper calculation of ROI for your campaigns, you will need to gather some data about what you are trying to analyze. This information includes the range of dates you will be analyzing, how many of your sales come from phone calls, your fixed costs and the average number of lifetime purchases for each of your customers. Fixed costs include things like agency fees, website hosting costs, customer service and anything that does not change month over month (or quarter over quarter).
The fields highlighted in blue above are variables and changing them will update several of the calculations in your spreadsheet. If you are not 100% sure about what values you should be entering into your spreadsheet, it is alright to enter placeholder values until you have a clear number you would like to use. This number can be $0 or 1 purchase in many cases.
Entering Your Product Margins
In addition to asking some questions about how your business operates, we will also need to know the margins for the products that you sell. To make this calculation as accurate as possible, we ask that you enter product margins at an Ad Group level (because often products have different margins and ad groups are how individual products are differentiated).
For many reasons, it may be difficult to obtain the exact margins for each of your products, so if you are unable to obtain this information, we recommend entering a 50% margin for each product.
Pulling Data Out of Google Analytics
The next step in creating an ROI analysis is to take the information you would like to analyze out of Google Analytics and inserting it into a spreadsheet for further analysis. While this may sound like a daunting task to develop from scratch, we have created a custom report in Google Analytics that allows you to do this with just a few clicks.
- Use the following link to install a custom report into your Google Analytics Account: [Click to Install Google Ads ROI Report]
- Upon clicking on this link, you will be asked which of your Google Analytics accounts you would like to install the report into. Choose the web property and view that contains your Google Ads and e-commerce data.
- If installed properly, you should be taken to the custom reports section of Google Analytics with your ad groups listed in rows.
- Click on Show Rows and expand to as many rows are necessary (our spreadsheet works with up to 1,000 rows currently).
- Make sure the date range chosen matches the dates you would like to analyze (we recommend analyzing 30 to 90 days at a time).
- Click on Export and choose CSV option.
- Open the CSV file in excel and select all data rows (this will likely start at row 8 and will go all the way until you see a total row at the end. Do NOT include the total row).
- Copy the rows of data in this spreadsheet and paste into the red “raw ad group data” tab of your spreadsheet, replacing the data that is currently in place.
When done successfully, you will have pulled all of the data that you need out of Google Analytics to perform this analysis.
Calculating Gross Revenue, Net Profit and True ROI
Now that we have collected the necessary information about your ad campaigns, we can create a series of calculations that can be used to determine the true ROI of your paid advertising.
To obtain our total revenue, we take our online revenue and increase it by our phone call percentage. This can be done for each ad group or for all revenues.
Our ad spend was pulled from the custom report in Google Analytics and is used alongside total revenue to determine our return on ad spend using the calculation of Total Revenue/Ad Spend.
We determine our cost of goods sold (i.e. product costs) by taking our margins for each ad group and dividing into product revenue. In our example, if we have 23% margins on products, our cost of goods sold is 77% of the product revenue. Subtract product cost and advertising from total revenue and we have our gross revenue.
Now we need to take our fixed costs our gross revenue to determine our net profit.
Then we get to our True ROI by taking our profit minus our advertising cost divided by advertising cost.
Viewing Your True ROI Dashboard
Now it’s time for the fun part: viewing our ROI dashboard.
We start off with branding, a title and date range for our dashboard. Customize the text, colors and logos to fit your needs.
Now we look at orders and revenue by channel. If your business does not accept phone orders, you may not need this section of your report. Others may find that they have more channels than just web and phone orders.
Next we have key performance indicators. As we discussed in our monthly reporting template, we can use these key performance indicators to provide insights and implications into how they affect business performance.
In the case of this spreadsheet, we made the decision to show our KPIs with metrics that often make their way into a Profit and Loss statement (P&L) for a business. This is something that can be easily understood by business owners and executives and is an attempt to form a common understanding between marketing and finance. (This may evolve as the spreadsheet evolves.)
After showing KPIs we start to get granular by showing which ad groups are creating the most profit, and which are creating the least. These ad groups are dynamically generated based on highest and least gross profit.
Last, we try to show how powerful advertising can be when we incorporate the lifetime value of our customers into the equation. In many industries, advertising is not profitable from day one, and it may take several months or years for a customer to become profitable. This section of the dashboard helps us account for this type of business.
Why Calculating True ROI is so Important
We all want to be data driven marketers, because when we make decisions based on the facts in front of us, we make great decisions.
Yet, there were times that what I thought of as “data driven marketing” fell on deaf ears when presented to business owners and key stakeholders of an organization! Why wouldn’t an organization want to take action on all of this great data I was presenting?
It took me years to understand that it wasn’t the data that was the problem, but rather how I presented the story. I wasn’t presenting my argument in terms that they could understand!
This spreadsheet helps our data driven recommendations get recognized by speaking in a language that executives understand.
You can read a million articles on the strategies and tactics of using Google Ads, but I have yet to see one go as far as to tell you how to calculate the exact amount of profit generated from these ads and teaching how to find your true return on investment.