How to Run a Regression Analysis to Forecast the Return on PPC Spending

Early in my career I began working with an early stage, SaaS start-up with only a handful on employees. With a relatively inexpensive product and a very small marketing budget, one of the company's Co-founders had been tasked with testing out some initial customer acquisition strategies. Bidding on highly targeted keywords in Google Adwords was one of the logical places to start. 

After devouring a few books on pay-per-click advertising, the company's Co-founder began bidding on the keywords that most accurately described our product. He found some immediate success, the company's customer base started to grow, and he began to expand his target list of keywords until he got to the point where he was spending a few thousand dollars a month in Adwords. At this point, he hit his first “ceiling” – a spending level where he felt he could not tap into additional keywords or expand spending on existing keywords that he was bidding on cost effectively. “I think we’ve tapped out on the opportunity that PPC spending represents in our market,” he thought. “Time to go test some new marketing channels.”

As the company continued to grow, it eventually reached a point where it was ready to bring on its first marketing employees. Shortly thereafter, a decision was made to hire a good paid search agency to see if we could expand our PPC programs above and beyond the initial ceiling that our Co-founder had found. Fast forward a few years, and the company would be spending (with great return) more than 50 times what it spent when it reached its first PPC "ceiling."

Along the way, one of the most common questions that our Co-founders would ask me is “when are we going to hit our next PPC ceiling? At some point we’re going to tap out the opportunity that PPC represents, and we need to be ready for that so we can grow additional marketing channels and continue to grow the business.” It was a valid concern - PPC advertising had become a key part of our customer acquisition strategy, and while we'd continued to successfully scale this channel we knew that at some point we'd reach a point of diminishing returns. This reality was in direct conflict with our growth goals as a company, which continued to be increasingly aggressive. Finding an answer to their question became that much more pressing - how much longer could we continue to scale our paid search programs? How far were we from our PPC ceiling?

To start, it’s important that you have a good understanding of the size of your addressable market as well as a sense of the search volume for keywords related to your business. But as I thought about how I could better address my boss’ concerns, I recalled some of the spreadsheet jockeying I had done during the course of my MBA in an Information Systems and Decision Sciences course. Regression analysis was a tool that could help me.

Regression analysis is a tool that looks at the relationship between an independent variable (in this case, PPC spending) and a dependent variable (which could be anything from free trial sign-ups to customers acquired – we would eventually run regression analyses to help us understand and forecast both). Essentially, you want to track the relationship between your spending and the return on that spending over time. Based on your historical data, you can then forecast what the return will be at higher spending levels assuming that there’s a linear relationship between the two variables.

This is usually the point where the record skips a beat and the music stops altogether – anyone with a solid understanding of Adwords will tell you that the relationship between spending and return on that spending over time is not linear. While that’s true, in almost every market there is a linear relationship between spending and return on that spending for a significant period of time. What you really want to understand is how long that relationship will remain linear and how close you are to reaching a point of diminishing returns as you continue to increase spend (this is when you know you’re approaching your ceiling). Excel can help there too. Keeping track of a correlation coefficient alongside your regression analysis gives you insight into how linear the relationship between the variables is, so you can very clearly see when you’re approaching that point of diminishing returns.

Regardless, I know that many people will criticize or at least question the value of this activity. There are too many nuances or subtleties at play, different keywords attract different audiences that generate different quality leads, etc. There's too much inherent complexity in how paid online lead generation works for this to make much sense. While I understand (and don't completely disagree with) those sentiments, I'm still advocating that this is a valuable exercise for two reasons. First, it showed me that the relationship between our spending and the return on our spending was still very linear and helped me answer my boss’ question – we weren’t anywhere near our PPC spending ceiling. But perhaps even more importantly, it helped me forecast with eerie accuracy what return we could expect if we were to spend more aggressively.

Below are the basic steps required to run this type of analysis, along with an example for illustrative purposes (this is not real data). I’m using free trial sign-ups as the dependent variable in this example – you may want to use customers, leads, demos scheduled, or some other outcome that you’re looking for your paid spending to generate.

1.     Open a new excel sheet. Label column A “Spending” and column B “Free trials.” Add this data to reflect your last 6 or 12 months of PPC performance.

2.     Download Excel’s Data Analysis Toolpack. It’s free and contains the regression analysis tool we’ll be using. Instructions on how to download the toolpak can be found here.

3.     Click on the “Data” tab in the top navigation of your Excel sheet, then click “Data Analysis” in the toolbar at the top of the page (this will typically be all the way to the right within the toolbar).

4.     Select “Regression” from the list of data tools, then click “OK.”

5.     At this point the Regression Analysis tool will open. In the “Input Y range” put in the range of the cells representing your dependent variable data (Free trials). In the “Input X range” put the range of the cells representing your independent variable data (Spending). Click OK.

6.     At this point the tool will open up a new sheet and will return what looks like a bunch of mumbo jumbo to most people (myself included). You’ll need to find a couple of specific values if you’re interested in predicting return on spend at higher levels. You’re looking for the “Coefficient” values that were returned – there should be one for “Intercept” and one for “Spending.”

7.     Further down the sheet with all those crazy values, add new “Spending” values in column A that go above and beyond the levels you’ve spent at historically (I recommend $10,000 increments). In Column B, we’ll need to use a formula to calculate the return you can expect at those higher spending levels based on historical data.

8.     The formula to enter into column B is: =Intercept Coefficient + Spending Coefficient*The cell containing the spending level you’re calculating the anticipated return on. It’s safe to assume in almost all instances that the formula will look like =B$17+B$18*the cell immediately to the left of the cell you are entering this formula into.

9.     Copy that formula down so that an anticipated return is calculated for all of the theoretical higher spending levels you’ve added in column A. Wa-la – you now have a really good idea of the return you can expect at higher levels of spending, of course assuming that the relationship between the variables remains linear.

The good news is that determining the strength of the linear relationship between the variables is the easy part.

1.     Go back to your initial excel sheet, the one that contains your historical data on spending versus return on that spending. In any empty cell type “=correl.” This is the excel command for calculating a correlation coefficient.  A correlation coefficient simply quantifies the extent to which there is a linear relationship between two variables on a scale of -1 to 1.

2.     Enter you’re the range of your historical spending data as array 1, and the range of your historical return on spending data as array 2. Hit “Enter” and your correlation coefficient is calculated.

3.     The closer your correlation coefficient is to 1, the stronger the linear relationship between the two variables is. Keep track of your correlation coefficient over time – I recommend updating it every month. You’ll see the value of the correlation coefficient drop pretty significantly and consistently when you’ve approached your PPC ceiling.

This may sound complex and convoluted, especially for marketers who are used to spending their days thinking about branding, writing copy, or attending trade shows. I promise, with a little practice this is a lot less painful than you might think. You can run this entire analysis in less than 15 minutes, and in my experience it's been a very valuable tool when it comes to understanding when you're approaching your PPC "ceiling." Better yet, you can show with astonishing accuracy the return you can expect if you spend at higher levels. What marketer doesn't want to do that?

As a final disclaimer, this has proven to me to be a valuable analytical exercise and forecasting tool - but it is NOT, and never will be, a replacement for creative new paid search strategies that help you get in front of valuable audiences in new ways.