One of the tasks that comes up quite a lot at Home is looking at the impact that algorithms have had on our client’s site or a particular sector in general. There are a lot of different ways of analysing things like these, I just wanted to share one of the methods that we use.
This methodology is used for many things but most commonly for financial, specifically, stock trading. It’s really just looking at a moving-average crossover to compare short-term vs long-term patterns and look for anything out of the norm.
I personally refer to it as “that random Golden Cross chart”. Not sure why you’d care about that either, but I’ve put the effort into finding a link and writing this paragraph now so I’m not going to press delete again!
My charts don’t look as impressive as the financial ones with their day open and close figures and all colour-coded but they do what they need to:
In the chart, the main thing we are looking for are the large peaks and troughs that move significantly far from the red line – I’ve put circles around the main points that stand out. The orange circles are where I broke my database so just ignore those points. The points of interest here are the green circles that occur on the 25th September and the 9th/10th October 2013.
The website that this analysis is based around has previously had an issue with links and has been affected by many of the updates and has had manual penalties applied to them (yep, has been a stressful time here!). The first thing I did when I saw this chart was have a look and see if the dates correlated with any Google updates and, surprise surprise, these dates are just before and just after Penguin 2.1 was rolled out!
I know this doesn’t really tell us anything, nor have I explained how we got to this point. Don’t worry, it’s coming!
So, first of all, the specifics of how this has been done and what it shows… the blue line on the chart shows total number of changed positions that day:
- Keyword 1 goes from #7 to #1 = +6
- Keyword 2 goes from #5 to #6 = -1
- This gives us a total position change of 7 (it’s the actual positions changed we care about, not whether it has gone up or down)
The red line takes an average of the positions changed over the previous X days (7 days in this circumstance because of the amount of data shown, but the higher the value of X the better).
That’s it… simple really isn’t it?
This allows us to look at how much rankings have fluctuated by day compared to how much they fluctuate on average for the site. This enables us to pick out specific points of interest and dig into them further rather than using figures like average rank, or similar, that may hide changes or you may just ignore as the rankings seem to be jumping up and down all the time anyway.
So, how about being able to recreate this yourselves I hear you ask! (for some reason I’ve decided in my head I’m now writing a pantomime)
I’ve attached the spreadsheet I used to create the above chart, I’m now going to go through the very basic process of filling it out:
- You need your ranking data to start with, specifically 3 items:
- keyword (or keyword ID)
- current rank (if there are multiple ranks for the keyword on the date just pull out the best one)
- Delete everything from the table in columns A – G excluding the first row as you’ll need the formulas, unless you want to write your own;
- Paste this data into the first 3 columns on the rawData tab in the spreadsheet;
- Copy down the formulas from the first row in the remaining 4 columns if it hasn’t already done so automatically for you
- The next column is just a unique identifier of date and keyword combined – feel free to change the formula if you have something you’d prefer to use;
- Copy your date column and remove duplicates;
- The de-duplicated list of dates should replace the dates in the other table on the rawData tab (Column I);
- Again, the rest of the formulas should automatically copy down to the full range of your data but if not just make it do so;
- Go to the pivot or chart tab and then refresh the data so that it is now pulling all the raw data that you have just put in;
- Bathe in the glory of an updated report full of your ranking information rather than my bodged data!
Hopefully that should make sense to everybody, although I reserve the right to come back and edit this at some point in time when I get a really obvious mistake pointed out to me!
Looking at the data in this way is really just the start – when a problem/change has been identified the next stage for me is to start doing this analysis including categories or groups of pages to identify if particular areas have been hit harder than others. Equally, I find this kind of analysis useful when run on competitors to then find bits of the site that are working best/worst to then examine further and roll out the insights to our own sites.
I know there are plenty more but you’d be here reading for a week if I tried to document everything. I’d absolutely love it if some of you guys decided to take this further or have a completely different way of simply identifying things that you wanted to share with everybody; don’t be shy, let everybody else know how awesome you are!!