Correlate, Damn it!

Identifying the Issue with Your PPC Account

Have you ever caught yourself staring at data from AdWords, trying to find an explanation to why your conversions dropped x% month over month and y% year over year, and not even knowing where to start searching for an explanation?

So many variables come into play on a daily basis in any given PPC account, how do you know what caused this decrease?  You could stare at a spreadsheet and create more pivot tables than you know what to do with, hoping you come across something that will give you a better understanding so that you can fix the problem.

Or, you could utilize a simple Excel formula to point you in the right direction in about 2 minutes’ time.

Yeah, I choose the latter option as well.

So, what’s this secret formula I claim will make your life much easier? It’s called a Pearson correlation formula (or the Pearson product-moment correlation coefficient, for you statistics nerds).  It’s a formula that tells you if a correlation exists between two sets of numbers.  It won’t magically tell you what’s wrong with your PPC account, but it will tell you where to look so you can find the answer(s) you’re looking for.

Mathematically, the Pearson formula is very freaking complicated if you don’t have an advanced understanding of statistics.  For those of you who are interested, here is the actual formula:

What does that mean? If you want the technical explanation, ask a stats professor.  In layman’s terms, “r” is the indicator of whether or not two sets of data have any correlation, or dependence, with each other.  Luckily, this formula already exists in Excel and is much easier to type out than what you see above: “=PEARSON(array 1, array 2)”.

Example

Below is a sample of data for key PPC metrics using 14 weeks of PPC data:

The conversions column is highlighted in red text because that is the metric being focused on.  You see some ups and downs, but you’re not exactly sure what’s causing this conversion trend.  So, instead of sifting through a dozen different reports looking for a needle in a haystack, simply perform a Pearson correlation formula for each metric and compare it to the conversion totals:

Spend “r” =Pearson(A2:A15,F2:F15) = 0.16

Clicks “r” = Pearson(B2:B15,F2:F15) = 0.09

And so on…

After applying this formula to each column, here are the results (highlighted in blue text at the bottom):

The result of this formula will range from -1 to 1, where -1 equals a perfect inverse correlation, 1 equals a perfect correlation, and 0 equals absolutely no correlation.  As you can see, most of the metrics used for this comparison are close to 0, meaning there’s no strong correlation between the trend of that metric and the trend in conversions.  But the conversion rate has a result very close to 1, which means there’s a strong correlation.

So, using this data, I can conclude the change in conversion total is dependent primarily on the change in conversion rate only, rather than the conversion rate combined with another key metric or another key metric entirely.  And if we think about what factors in a PPC account have the most direct impact on the conversion rate only, we can conclude we should take a closer look at our landing pages and on-site activity after an ad has been clicked, rather than other factors like keywords or ad copy.

So, while using Pearson correlation formulas on data doesn’t give you the magic answer you’re looking for, the results can tell you where you should look to find it.  When you’re facing the possibility of browsing through report after report to give you direction, save yourself the time and start with this useful method instead.