SEO News

Posted by ajfried

This post was originally in YouMoz, and was promoted to the main blog because it provides great value and interest to our community. The author’s views are entirely his or her own and may not reflect the views of Moz, Inc.

No matter what business you are in, it’s a pretty sure thing that someone is going to want to monitor how efficiently and productively you are working. Being able to show these results over time is crucial to maintaining the health of the long-term relationship.

To us, reporting has become much less about the data, and more about the story you can tell with it. Not to say that data isn’t important, but while a big spike in traffic is awesome, the drop that comes after it might not necessarily be a bad thing. It could be seasonal. Or maybe it’s just leveling back after the brand was mentioned in a news report. To be totally honest, the same thing could apply to the spike itself, so hold off on those celebrations!

For the sake of transparency, this is something that we always felt we could improve on more at Kahena. We have tried it all, from email write ups, to using straight ranking reports (blerg, I know – so 2007), to a template-based Excel model (which is what we currently use), and even as far as planning a future web-based reporting platform for all of our clients to log in to and utilize as needed.

This whole project really started in the wake of Google blocking all keyword data and turning it all into [not provided]. This really forced us to break old and bad habits and redefine what important metrics and insights we were sharing with clients.

We had two goals in mind during this project which were to:

1. Automate the report as much as possible so that we have more time to analyze the data and provide deeper, more valuable insights, both for our clients and our own internal strategies.
2. Keep the template intact so there was standardization across our reports.

Categorizing all the data

We held a brainstorm and decided to compartmentalize the data into 3 different categories:

• KPIs: These are the key performance indicators that we felt show bottom line progress from the campaign.
• Leading Indicators: These metrics add value to the story by exhibiting a correlation with the KPIs. These are all things that clients need to know to understand the KPI outcomes.
• Nice to know: These metrics don’t necessarily make or break the campaign, but are notable and worth mentioning because they provide some insight into overall performance.

Here’s what the brainstorm looked like after our meeting:

What this ultimately turned into is the following (click on the report for a full-sized version):

Now, let me be clear, this could have gone in lots of different directions, and in many cases it does depending on the client. We chose the metrics above for our template because we felt they provided the best high-level insight. Let me explain.

The KPI’s we thought were most important were traffic and goals. That was a relatively simple one to decide since this is ultimately what clients hire us to improve.

Leading indicators were a bit more challenging to solidify. We decided that for the purposes of story-telling, landing page data would be able to explain to our clients the progress we were making around specific landing pages we were optimizing. It also would provide more data into which *groups* of keywords were doing well and which needed a bit more attention. We also put an emphasis on vertical rankings (we partner with Rank Ranger to get that data). We specifically did not want to focus on individual keywords, although there is still a section for specific changes which are chosen to highlight campaign progress.

The nice to know information is the one section that we didn’t trend, and in the future we still might decide to do that. For now, we highlight user engagement data like pages per visit, bounce rate and average time on site. The one area we do trend here is site indexation, because that can have a major effect on a site’s search engine health and provides an early warning to any index bloat issues

A bonus area we added to the report allows our account managers to think creatively regarding non-standard data highlights and observations. Interestingly, this is often the section that gets the most comments from clients.

The most time-consuming piece in this whole process was setting up the initial template. The report itself is actually quite simple after that. The client-facing portion is one tab, which allows us to PDF everything, and it pulls data from other data tabs.

Most of the tabs have a similar table which has the date and the metric you want to visualize. This is just copying and pasting numbers from Analytics – nothing fancy. But, if you’re curious, the tables where we put this data look like this:

Pulling the data

Once set up, the only data we are pulling from Analytics is the number of:

• Total organic visits, broken down by the traffic source
• Goal conversions (transactions and revenue for e-commerce)
• Micro goal conversions (newsletter signups, or event tracking on important buttons)
• Total page views
• Bounce rate
• Time spent on site

The report basically does the rest since the data sources for all the graphs and charts have already been selected.

We also download landing page data from Google Analytics which includes two segments: organic and total traffic compared by current and previous month. Similarly, when added into the appropriate columns, the template populates the data into this section:

All this data pulling and automation helps a lot with the time element. The problem we found was with a 10 person team (let alone a team of significantly more people), everyone wanted some level of customization, which was completely understandable. Each client is going to have specific requests, and each account manager is going to want to tweak it to their liking. The problem with everyone customizing the template is that it often resulted in some elements displaying weirdly when we made it into a PDF, or worse, broke some of the formulas that caused the template to work.

When we investigated this, we found that 80% of our clients could use one of 2 templates:

• A conversion and micro conversion report which reported on a goal we had set up and included events and micro conversions we were targeting
• An e-commerce report which reported on transactions and revenue generated from organic search

Keeping the rest of the report intact involved some creativity. For this, if you are not familiar with offsets, then I suggest paying close attention.

The offset function creates magic

This is a little-used Excel function from what I have found, but it’s super powerful. What it does is:

“Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.” Source: Microsoft Excel Help

In other words, one cell could control pivoting the entire report between months. Every single table that you have in the report can be linked to one cell and change as that cell changes. Even better, if you can create a button that is connected to this cell, with the simple click of the button, it can adjust data in various parts of the template such as the date, the header, footer, and even provide a way for clients to navigate themselves through the data (if set up correctly).

Here is how it works for us:

In many ways, this serves as a database that holds historical data so it’s saved for the future. Using this, we can show a trended analysis of the progress we have made over time without having to do a ton of data pulling, saving us a significant amount of time.

Given the amount of reports we, as an agency, run on a monthly basis, we need to find better ways of automation without the restrictions that come with third party tools. We have started developing an interactive web-based reporting tool built on the Microsoft Reporting framework. This will work by setting up scheduled jobs to collect data from various data sources (including Google Analytics) and automatically store them in our database so that we don’t even have to pull the data. This will allow our team (and in the future – our clients) to access this data via pre-templated online reports and more time to tell stories and analyze trends.

For now, we are still using Excel, which our clients still love.

The real point that I want to stress is that no matter what method you use for reports, the story is key. The data is crucial, but the insight around the data is what management wants to see. They want to visually see what is happening with their business, and understand through our analysis why it’s happening.

We have received an incredible amount of feedback from clients who have said things like “I really LOVE the format of having the charts or graphs, and then having your commentary/insights right next to it…TOTALLY works for me”

So they are happy. We are happy.

And that’s our process – I hope you found something you can take away for your reports as well.

One final tip to build the report

This is not something that could be done alone. It really took the entire team’s effort, ranging from junior associates up to the CEO, getting buy-in, agreeing on the metrics, and believing in the vision for what we were trying to accomplish with reporting. The ideas and feedback were invaluable and they should all be proud of what we accomplished together.

Download the template

To help you get started, we have made the template available for you to download. I am happy to answer any specific questions in the comments and help you as you set up your own report for you and your clients.

Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

I covered the Rap Genius website briefly in my 13 amazing interweb things post from a few months ago, in which I highlighted its hip-hop lyric cruncher. This however is just a small corner of its now slightly less burgeoning empire.

The site itself publishes lyrics to rap songs, and using crowd-sourced annotations, adds notes that expand on references within the lyrics themselves. Great for those of us who don’t know the meaning of such terms as ‘whip’, ‘trill’ or ‘what’s crackin?’*

How did Rap Genius break Google’s rules?

Growth hacking. 

In a contrite blog post on Rap Genius’s own site, the engineering team goes into great detail how they manipulated the system and quickly drew the attention of Google through some fairly debauched (their own word) SEO malpractice.

• Rap Genius appended lists of popular song links to guest blogs that were unrelated to the content of the post.
• It offered to promote any blog who linked to Rap Genius in any post, regardless of the relevancy of content.

John Marbach received just such a proposal on 22 December 2013, which he describes on his own blog in great detail, but for your consideration I’ll post the contents of the email that drew Google’s attention, as sent by Mahbod Moghadam of Rap Genius.

Using the potentially huge amounts of search traffic generated by the new Justin Bieber album, Rap Genius were hoping to direct searchers towards its own site. What business Rap Genius has in publishing Justin Bieber lyrics anyway should be discussed elsewhere, but by building these powerful hyperlinks across multiple sites and blogs, this would artificially raise Rap Genius up through the rankings.

You can possibly sympathise with Rap Genius’s desperation when you uncover the fact that 80% of its traffic comes from only 20% of its lyrics. As Marbach agrees, the fast pace of the music industry dictates that 2013’s most popular drivers like Daft Punk’s ‘Get Lucky’ will certainly fade in popularity in 2014, if they haven’t already. However the above email shows a blatant and shameless manipulation of the system.

The fallout

Google caught them, fast, and on Christmas morning gave Rap Genius a severe penalty the like of which only the very worst children on the naughty list receive. A 10 day Google ban. “Take away my PSP, take away my new BMX, anything, but please, please don’t take me off of Google!” I’d imagine Rap Genius was pleading, while on its knees, to the Ghost of Christmas Search Listings.

Even using the search term ‘rap genius’, the website didn’t appear in the Google SERPs until page six.

Everything seems to be back to normal now. Rap Genius has worked hard to remove all of its unnatural links from around the internet, using Google’s own four-step approach:

1. Download a list of links to your site from Webmaster Tools.
2. Check this list for any links that violate our guidelines on linking.
3. For any links that violate our guidelines, contact the webmaster of that site and ask that they either remove the links or prevent them from passing PageRank, such as by adding a rel=”nofollow” attribute.
4. Use the Disavow links tool in Webmaster Tools to disavow any links you were unable to get removed.

As of today (7 January 2014) this is how Rap Genius appears on Google using certain popular search terms:

‘Jay Z lyrics’

Rap Genius appears as the sixth result, if counting news items.

‘Get Lucky lyrics’

Rap Genius is the fourth result.

‘Ice Cube Today Was a Good Day lyrics’

Rap Genius is the third result. So really this whole mess didn’t take too long to correct itself. Perhaps it’s down to the speed of Google’s web crawlers, or perhaps Google did its own manual fix.

I understand that Rap Genius’s 10 day ban was nothing more than a ‘slap on the wrists’, and obviously the attention it has drawn to the site will probably end up driving far more traffic to the site in 2014 then any amount of organic search listings or unnatural link-building ever would. However I feel it’s important to highlight this as an example of how we should all avoid artificial link-building and how quickly Google deals with such transgressions.

*’car’, ‘a portmanteau of true and real’ and ‘hello how do you do?’