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.
In 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:
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.
You Might Be Interested In
