Data analysis is an essential part of the day-to-day activities for any PPC account manager. Gladly, we have an access to the enormous amounts of the data and all we need is just the right methods and tools for an analysis.
One of the important questions is the relationships between the metrics. How does an average position depend on max CPC? Or, how does the conversion volume (revenue) correlate with spend? Microsoft Excel is a great platform for understanding and exploring these relationships between two PPC metrics. I have built a Correlation Calculator that helps me to analyze relationships for my Paid Search metrics.
Let’s say I want to see the correlation between spend and number of conversions. First, I get my data ready. It is important for statistical calculations to use as much data as possible, so the larger the data set, the better. Then, I paste it into the columns on the left. One column is for the independent metric, another is for the dependent metric. In this example spend is an independent and conversions are the dependent metric. The calculator will then calculate the strength of the relationship and its significance. I built-in a drop-down list to choose the level of the acceptable significance.
Some things to keep in mind:
Correlation coefficient is a value between -1 and +1 that measures a linear dependence between two variables. The higher the correlation the closer the coefficient to -1 or +1. Being close to 0 means weak or very low correlation.
A correlation can be strong and yet not significant, or, it can be weak but significant. The key factor is the size of the sample (N). For small samples, it is easy to produce a strong correlation by chance, so pay attention to significance to keep from jumping to conclusions (Type 1 error). For large samples, it is easy to achieve significance, so pay attention to the strength of the correlation to determine if the relationship explains very much. Degrees of freedom is the number of independent pieces of data being used to make a calculation. Coefficient of determination explains what percentage of the data is related. T-test assesses whether the means of two groups are statistically different from each other.
If you are interested in reading more on statistical testing for paid search, check out my previous post on A/B Testing for Statistical Significance.