Tech Tip: Excel Regression Analysis

Regression analysis may sound like a complex data manipulation limited to Statistics majors- but it’s a function that can easily be generated within Excel

Regression analysis allows Google merchants to measure the relationship between two data sets.

For example if you wanted to understand the relationship between your average position and conversion rate then you would use regression analysis to help understand or quantify the relationship which can help you decide if you want to raise bids or not.

Excel Tech tip, regression analysisIn PPC since there are multiple variables involved (average position, CTR, conversion rate, etc.) a simple linear regression won’t be the most accurate measure of the relationship but it can point a merchant into the right direction.

Measure the correlation of product feed and clicks to see if the clicks are completely dependent on number of products in the feed.

Measure Correlation Using Excel

To measure correlation between two data points using regression analysis in Excel, follow the steps below:

  • Enable the correct add-in (e.g. analysis toolpack)
  • View data analysis under data
  • Select your x and y points (2 data elements you’re measuring)

For a step-by-step regression analysis in Excel follow the GIF below:

Excel tech tip

In this example, we calculate the r square (relationship between two numbers) for clicks vs. product count. For example, if the r-square value is closer to 1 the data is highly dependent on one another. If it is further away from 1, (e.g. .05) they aren’t as dependent.

If the r-coefficient is 1, the data suggests y is 100% dependent on x. Similarly if the r-square value is -1 then there’s a 100% inverse relationship between the datasets.

Of course there are multiple types of regressions but the linear one is the simplest. Remember that correlation isn’t necessarily causation.  There are a ton of levers or variables that you should consider with your PPC campaign, so be sure to take a holistic approach to this if you start experimenting with it.

About the AuthorTien Nguyen is a co-founder of CPC Strategy and deals with data feeds in his waking hours and often in his sleep. He spends his free time with Rubik's cubes while rooting for the underachieving Raiders and UCLA sports teams where he graduated with a mathematics/economics degree. See all posts by this author here.