Best way to show relationships/correlations

[ccastell88]

Hi guys,

Basically i want to see with the data attached if there is a relationship between organic traffic and backlinks for all the pages.

I also want to see whether there is a relationship between organic traffic and anchor text ration.

What would be a good way to do this with excel?

Or even display it?

Thanks,
Chris

[MrShorty]

I think that, before we can even talk about "the best" way, you need to simply explore the data. Much of this is not necessarily Excel specific, it is probably more of a statistical question than an Excel question.

Often, the first thing I will do is to plot the data on a scatter chart, with one variable along the x axis and one variable along the y axis. https://support.office.com/en-us/art...5-104A9018B86E

The use of the =CORREL() or LINEST() or other statistical functions can help numerically analyze the data to see if the science of statistics has anything to say about how the two variables are interrelated (list of functions: https://support.office.com/en-us/art...rs=en-US&ad=US ). This requires some understanding of the statistics behind the analysis, and I can't say from this side of the internet how knowledgeable you are in statistics.

That's how I usually start. The results of those preliminaries will then inform the next steps in analyzing the relationship, if any.

[xladept]

Just looking at a scaled line graph - it appears that the backlinks and the anchor text ratio are opposites with a very strong negative correlation - to get it I multiplied the anchors by 100

It also appears that the current field has a huge outlier smack dab in the middle of it.