True ROI Measurement for E-Commerce Websites Using AdWords, Google Analytics and Excel

This past Saturday I gave a presentation at Measurecamp in London about the topic of ROI. This is something that has been at the top of my mind recently as I have been working on some projects to help paid search advertisers easily determine the return on their investment for their e-commerce websites.

[Need a quick way to calculate ROI on your paid search ads? Download our Google AdWords Budget Calculator.]

While you may not be a heavy paid search advertiser or even have an e-commerce website, the principles that are learned here are applicable to any business that generates revenue from their website (whether directly or indirectly). Learning how to measure ROI for these specific types of websites will be the subject of future posts on Jeffalytics.

This post summarizes what was shared in the presentation, and if you would like to learn more about the topic I will be posting two more articles on this topic in the upcoming weeks. Subscribing to my newsletter will help you stay informed when new content is posted.

Presentation: Putting ROI into Context

Let’s get things started by sharing the Measurecamp presentation. It’s interesting how things change as you take a presentation from concept to execution. When pitching the idea for the presentation, I originally wanted to talk about the specifics of the way ROI is calculated in Google Analytics and share how this calculation is dangerous if you provide it out of context.

As I started to build the presentation, I noticed that it was missing something important: a definition of ROI and business context as to why the Google Analytics calculation is dangerous. So I built that into the beginning of the presentation and then worked to prove my theory at the end.

The result is a humorous (at least I hope so) introduction into the problems we are facing and how I propose that we find a solution.

The session was filmed and if/when that video becomes available I will share it in this space as well.

True ROI Measurement for E-Commerce Websites Using AdWords, Google Analytics and Excel

Do You Have TBAGD?

While you have surely never heard this term TBAGD before (because it is something I made up just for this presentation), you may have a case of TBAGD and not even know it!

Tool Blindness

Tool Blindness

The “TB” stands for Tool Blindness and is something that many of us suffer during the early parts of our careers. As we are learning how to do our jobs, we put trust in our surroundings and assume that they will provide us with the guidance necessary to make an informed decision.

Our surroundings are often made up of people who provide mentorship (our managers, professors, co-workers and industry thought leaders), as well as the institutional knowledge and intellectual capital of our organization (i.e. the proprietary documents and tools that are provided by the organization as part of doing business).

While the guidance from our mentors is often provided with significant business context and caveat emptor, the information we glean from documents and tools of our organization are rarely presented with proper context into how the information should be used.

This causes an effect that I call “tool blindness” in which the person using the tool assumes absolute faith in what the outputs of the tool without applying any critical thinking or understanding business context. The result is usually sub-par analysis, “data puking” and a loss of faith in the person delivering the information.

If analogies are your thing, tool blindness is like the picture above, which shows a fishing boat being approached by a giant whale, unaware of what lies beneath. In this case the fishing boat can only see things on the horizon and is blind to what is happening beneath them. This blindness creates a dangerous (and potentially fatal) situation for the fishermen. While I don’t foresee tool blindness being life threatening for any analyst, it’s important to understand that tool blindness is a problem we all face.

Google Dependency

Google Dependency

The “GD” stands for Google Dependency and is an effect that is created by relying solely on tools provided by a single vendor without also thinking critically about their motives for providing the tool. I am a huge fan of the tools that Google provides to marketers (for FREE in most cases), but I also realize that there is a motive behind giving these tools away for free and take that into advisement with every decision I make while using them.

I believe that the reason why tools like Google Analytics and Google Tag Manager are provided for free to users is because Google believes that it creates more educated marketers and website owners. A more educated website owner is able to understand whether they are making money off of their marketing activities, and if they are making money off of their activities, they will spend more money with online advertising.

A large part of Google’s growth has been fueled by small to medium sized businesses (SMBs) that are confident with advertising online and measuring the effects of advertising through Google Analytics. Before Google Analytics, robust website measurement tools were cost prohibitive to SMBs, so website advertising was based on faith over analysis. Requiring faith in advertising  limits the number of participants and profits for Google.

While we are fortunate to have Google Analytics in our lives, we also need to remember the motives behind making it free. While paid search is prominently featured throughout the tool, we need to realize that paid search marketing is not the center of the universe for most websites, and that when we let the tool lead us down certain paths of analysis it may serve Google better than it serves the business owner.

My Advice: Use the tool, but make sure you don’t develop a dependence on just the advertising metrics that Google provides you. While it is a thing of beauty to be able to use a single tool for multiple levels of analysis, we also need to know that there is more to running a successful business than minding the reports provided in Google Analytics. This is a lesson that I learned the hard way as I ran an e-commerce business that appeared to be successful in Google Analytics, yet failed to make a profit in real life.

What is ROI?

Now that we have established why tool blindness and Google Dependence (TBAGD) can be a problem if left unchecked, we can start to understand how to use this information to improve the way we are received as marketers and analysts.

The first order of business is to understand exactly what ROI stands for and also to provide some context into what might be the right way to measure return on investment.

ROI very simple because it has two inputs:

  • Investment
  • Return

Divide investment by return and you have ROI.

Thank You Captain Obvious

Yes, this is about as obvious as it comes, but it also the reason why ROI is such a misunderstood and misrepresented topic in business.

This is because the word return can mean many things to many people.

To an analyst responsible for search advertising (and within Google Analytics), it will mean top line revenue generated from advertising spend. The investment is how much you spend with Google and the return is the total amount of money made by your company.

The rest of the presentation talks about why this is probably not the proper way to measure ROI for your campaigns, and how you can limit your career prospects if you are not careful.

Tool Blindness and Google Dependence = Beer Goggles for Analysts

Beer Goggles

I failed to mention earlier that Measurecamp is an “Unconference”, which means it does not follow the standard rules we might follow while presenting. There is a swear jar, but the only swear words were buzzwords like “Big Data” making their way into presentations. The usage of actual swear words are encouraged during presentations.

Knowing this in advance, I decided to take some liberties with the analogies I used to present my case for the evolution of an analyst. While I might normally use an evolutionary picture to describe how we grow as analysts and marketers, for this presentation I used the concept of “beer goggles” to describe how we can start to look better in the eyes of our suitors. My apologies if you are offended by this type of thing, but I meant it in a very cheeky way.

The basic concept is that when we suffer from Tool Blindness and Google Dependency, we don’t appear very attractive to those we are trying to impress. Looking at the picture above, we are the equivalent of the 8 PM picture when we suffer from TBAGD and send reports without context or analysis.

I provided an example of the ROI report in Google Analytics, which is a report that is commonly referred to by paid search analysts working on e-commerce websites.

ROI Report Google Analytics

By the very definition of return on investment, this report is accurate.

  • We have an investment – how much you spent with Google AdWords
  • We have a return – how much top line revenue was generated by the Google spend.

In Google’s world, this is the appropriate usage of the term ROI. Since they only definitively know these two inputs for your site, this is all that they are responsible for reporting.

While researching the many meanings of ROI, I came across an excellent quote from the folks at Investopedia that sums up why we need to be careful with the way we use the term ROI:

“ROI calculations can be easily manipulated to suit the user’s purposes, and the result can be expressed in many different ways. When using this metric, make sure you understand what inputs are being used.”

In your world, you are better served to translate return on investment into the terms that really matter to the people running your business.

Calculating True Return on Investment for Paid Search Advertising

What really matters to a business? Profit. The amount of profit generated by a business is what matters to executives, because that is how their performance is often judged and it directly affects how they are compensated.

Unfortunately, finding the profit you generate for your business and advertising and your true return on investment is a little more complicated than what can be provided to you in a single free analytics tool. This is where we need to step outside of our comfort zone and job title to generate a more accurate picture of the value we bring to our business.

Having recently been in a leadership role for several businesses that reported their finances using a profit and loss statement, I have begun to adopt the P&L model when it comes to determining ROI on advertising. Here is an example of what a P&L statement looks like for most businesses:

Google P&L Statement

Google P&L Statement courtesy of Businessweek

Since this is a standard form of measurement for many businesses, it may help you to adopt this style of measurement for your analysis as well. Here is a simplified equation of how this should work for a business:

Revenue to Profit

Starting with your top line revenue, you can remove the amount of money that you spent on advertising the business, the amount of money you spent to create and distribute that individual product, and subtract the fixed costs that you are paying as a business. At the end, you have the net profit for your business. You also have many caveats to beware of here:

  • Advertising investment may not be its own line item on a financial statement, but is included separately here for illustrative purposes
  • Your fixed costs will need to be distributed across all of your marketing activities, so paid search should not be subtracting 100% of your fixed costs unless it is 100% of your sales. Some fixed costs (PPC agency fees) will be 100% attributed to paid search. Others (website hosting fees) will not.
  • Product cost is another word for variable cost
  • This is a very simplified picture that may raise several questions if presented within your business. It is a good idea to take the information provided and use it to refine the approach for your specific needs.

Once we have established net profit estimates for your efforts, we can start to determine what your “true” ROI is for online advertising.

*Note: I am not an accounting professional, so please do not take any of this information as accounting advice. This is meant for illustrative purposes only.

ROI Equation

No matter what you are using as the return variable to calculate ROI, the formula that you use will be the same. You take the amount of gain from the investment and subtract your investment. Then you divide by your total investment to get the percentage you have returned on your investment.

ROI Equation

Example of the True ROI Equation in Action

Formulas are nice, but case studies are better, right? If you want to see this formula in action, then you’re in luck. I have created a sample data set that will help us understand the difference between the ROI reported in our favorite analytics tool and the actual return we received in profit for our business.

Let’s start with the Google Analytics ROI metric. In the example below, we have made a 656% return on our ad spend with Google. While this would be listed as ROI in Google Analytics, a better term to use in this case is Return on Ad Spend (ROAS) to describe the top line revenue we generate through advertising.

Return on Ad Spend in AdWords

This number looks tremendously attractive to anyone looking at this report, because it indicates that we are simply killing it with our online advertising. At that rate of return, a business should be putting 10x as much money into online advertising, right?

Not exactly!

Remember, our mission is to get our reports to reflect the items that show up in the P&L statement for our business and to reflect the profit that we generate. We need to figure out our net profit to determine our true ROI.

Unfortunately, this is not something that can be done in Google Analytics. Maybe this will be possible in the future with dimension widening in Universal Analytics, but currently we must get the data out of Google Analytics to perform this type of analysis.

My tool of choice for manipulating data outside of Google Analytics is to download data into an excel spreadsheet and use this tool to perform the rest of my data analysis. You can start by exporting your AdWords cost data and e-commerce revenue using a custom report in Google Analytics.

I export this data into excel and perform several calculations to put the revenues and expenses for the business into a format that looks similar to a profit and loss statement for a business. It looks something like this:

Net Profit

In the case of this business, if we subtract how much money the products cost and our fixed costs (things like advertising agency fees and website hosting costs), this business ends up with a very small amount of net profit compared to their revenue.

Things aren’t looking nearly as good when we peel back the onion and report on matters that are important to the executives of your business.

True ROI for this business

Using the equations outlined above, we find that the true ROI for paid search for this business is only 44%, which means that we had a lower return than we spent on advertising. This is a fairly common scenario for many companies advertising online.

True ROI Calculation

Would you expect that the CFO of a company that is receiving a 44% return on their advertising investment to put more money into paid search, less money or keep budgets the same?

As a marketer or analyst, it’s this type of analysis that gets you noticed within your organization. It’s stepping above and beyond the standard way of reporting your marketing activity and trying to speak on the same level as the executives that determine your budgets.

Successfully make it to this level of analysis and you’re starting to look a lot more attractive to your business!

Removing Beer Goggles

But don’t stop there! Let’s talk about how you can make yourself look like a supermodel.

Incorporate Lifetime Value (LTV) into your ROI calculation

Veteran analysts may have read the above description and realized that there is something missing from the analysis. That is because the above equations are assuming that a customer purchases exactly 1 time from your business. For the majority of businesses, customers purchase more than one time, so it is important to understand the lifetime value of your customers when figuring out the return on your marketing efforts.

For the purposes of the presentation, I made two assumptions about this sample business to help demonstrate just how much Lifetime value impacts our impressions of advertising success.

We assumed that there was no future advertising spend needed to make a customer purchase again and we assumed that the customer purchased 1.5 times on average. These are very simplistic assumptions that likely will vary for your business.

Assumptions for Lifetime Value

If a customer purchases 1.5 times off the same advertising spend, our Return on Ad Spend is now over 1,000%. In addition, we are now generating more profit than we spent on advertising, thus giving us a true ROI over 100%.

Profiting from Lifetime Value

This is the type of analysis that can create an undeniable impact on the budgets for your business. If you can present the executives in your company with a case that for every $1.00 they give you to advertise online, they make their dollar back + $0.38, you will be regarded in a very pleasant light throughout your business. Not only that, but you will have a much easier time justifying an incremental budget for advertising next year.

By curing our tool blindness and getting rid of our Google dependency, we are beginning to walk, talk and act like an executive.

Do this enough times, and you’re going to look like a supermodel!

Beer Goggles

What do you think about this true ROI calculation?

If this ROI analysis spreadsheet were a piece of software, it would still be in Alpha release mode. That means it is something that is ready for public consumption in small doses. Let’s call it version 0.5.

The measurecamp audience provided some quality feedback and debate about the formulas used in the spreadsheet and I will be incorporating the valuable feedback into the next release of the sheet. What else would you like to see from an analysis of this nature?

Get the True ROI Measurement for E-Commerce Websites Using AdWords, Google Analytics and Excel Spreadsheet for FREE

I am giving away all of the hard work and intellectual capital that went into this spreadsheet to all interested parties for FREE over at Knowledge Land. All that you need to do is head over to the site and sign up for a free membership. I will then upgrade you to a professional membership while Knowledge Land is in alpha mode (until July 1, 2014). After July 1st, you will have to pay for this ROI spreadsheet. If you are interested in joining me, please take the following steps:

  • Go to http://knowledge.land/ and click on the free account button
  • Send an email to jeff at knowledge.land once you are signed up and ask for access to the spreadsheet
  • I will upgrade your account and send you a link to download the spreadsheet
  • You will be subscribed to my weekly email newsletter where I share developments with you and ask for feedback on how to continue to make your membership valuable
Just note that the site is very much in alpha mode and I am still developing premium content that can be shared on a consistent basis. What you see today is just a small taste of the future.

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.