Google AdWords ROI for E-Commerce Template

NewImage

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

This week’s Knowledge Land lesson comes from the world of paid search. In particular, we are going to talk about how to do end to end analysis of your Google AdWords 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.

This is precisely what we will be doing in future lessons on Knowledge Land – sharing templates and methods for calculating the return on our marketing investment.

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 AdWords ROI assessment using Google Analytics

ROI Assessment in Google AdWords

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 and Knowledge Land premium members can download an excel spreadsheet that makes this type of analysis a breeze.

[MM_Member_Decision membershipId=’2|3|4|5|6|7′] Your Knowledge Land purchase entitles you to have complete access to this template! Click on the Button below to download.

Download the True Google AdWords ROI template [/MM_Member_Decision]

Now in order to take advantage of these shortcuts, you will need to have the following items in place for your business:

  1. You need to be running campaigns on AdWords to drive traffic to your website
  2. Your website must have Google Analytics installed to measure who is visiting your site
  3. Your links from AdWords need to be tagged to work with Google Analytics (this is called auto tagging)
  4. Your site needs to sell products online
  5. 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).

For Knowledge Land spreadsheet holders, you can fill out these questions on the green Questionnaire tab of your spreadsheet. If you are building an ROI spreadsheet yourself, you should make note of these values to be used in your ROI calculations.

ROI Spreadsheet Questionnaire

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

Margins Tab

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.

In the Knowledge Land ROI spreadsheet, you will want to enter values that match your Ad Group names exactly. Our spreadsheet needs to have an exact match with your Ad Group names in order to work optimally.

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.

Knowledge Land GA Report

  1. Use the following link to install a custom report into your Google Analytics Account: [Click to Install AdWords ROI Report]
  2. 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 AdWords and e-commerce data.
  3. If installed properly, you should be taken to the custom reports section of Google Analytics with your ad groups listed in rows.
  4. Click on Show Rows and expand to as many rows are necessary (our spreadsheet works with up to 1,000 rows currently).
  5. Make sure the date range chosen matches the dates you would like to analyze (we recommend analyzing 30 to 90 days at a time).
  6. Click on Export and choose CSV option.
  7. 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).
  8. If you are using the Knowledge Land template, 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

Net Profit Equation

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.

ROI Equation

Then we get to our True ROI by taking our profit minus our advertising cost divided by advertising cost.

Viewing Your True ROI Dashboard

If you have followed the above steps, you are really rocking your way to advanced analysis! (Note: If you had any troubles with the steps, please email me at jeff at knowledge.land and we can walk through how to make things work properly for you.)

Now it’s time for the fun part: viewing our ROI dashboard.

Branding and Date Range

We start off with branding, a title and date range for our dashboard. Customize the text, colors and logos to fit your needs.

Orders and Revenue by Channel

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.

Key Performance Indicators

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

Ad Group Profitability

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.

Customer Lifetime Profitability

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 AdWords, 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. That’s what makes Knowledge Land different.

[MM_Member_Decision membershipId=’2|3|4|5|6|7′] Your Knowledge Land purchase entitles you to have complete access to this template! Click on the Button below to download.

Download the True Google AdWords ROI template [/MM_Member_Decision] [MM_Member_Decision membershipId=’!2&!3&!4&!5&!6&!7′ hasBundle=’!8′] Become a Knowledge Land Member Today!

[/MM_Member_Decision]

About the Author

Jeff Sauer is an independent Digital Marketing Consultant, Speaker and Teacher based out of a suitcase somewhere in the world. Formerly of Minneapolis, MN and San Francisco, CA.

  • Mayo Racek

    hello, i would like to really bought this template..is it possible, or signu up for your konolwedge land? I follow you, but seems to not work for people outside the US. I am from Slovakia, big fan of you and this template will rock reporting to my clients.

    • knowledgeland

      Hi Mayo. Send me an email jeff at jeffalytics.com and I can get you the template.