Excel to Find Correlation Between Two Things

Well things are just staying super busy, but I am trying to find a good couple hours this morning to get content out so today we are going to talk about the excel function "correl". It is a great function that can be useful to anyone trying to determine if there is some kind of relationship between two things.


The template will be available for download after purchase.

In the excel template I have here, the two things are going to be ad spend to sales. So, before I get too deep into what the template does, let's define what the correlation coefficient does.This coefficient is also known as R.

R has a scale of relation and it measures how closely one set of data moves in relation to another (positively / negatively / or none).

  • So at 0 there is no correlation.
  • When you get to +-0.5 that means there is some correlation.
  • Anything over +-0.80 is a strong correlation.
A perfect 1 or -1 would be a data set in the follower order: 

Positive 1 Correlation Coefficient:
  • Array 1: (10,11,12,13,14,15)
  • Array 2: (5,10,15,20,25,30)
Perfect Negative -1 Correlation Coefficient:
  • Array 1: (1,2,3,4,5,6)
  • Array 2: (10,9,8,7,6,5,4)
So one application of this was to see if there is a relation between how much money is spent on ads every day and how much sales are made. A strong correlation would mean as the ad spend moves up and down, so do the sales on a given day. You could even measure it by hour.

Much deeper analysis can be done. For example you can track how much profit you are making rather than total revenuer per day if you want to see if your ad spend is related to being more or less profitable in the types of sales you are getting.

Also, you could break it down in terms of individual products or ad campaigns vs. no ad campaigns and see what the numbers look like.

For web marketers, you can replace ad spend with search traffic and compare the traffic to sales or compare keyword rankings to sales. There are all kinds of applications that can help with business decision.

No comments:

Post a Comment