SEO News

Posted by Annie Cushing

Image from the National Archives

If you want to see how your competitors are gaining a strategic advantage, one of the best tactics to overtake them is to take a deep dive into their backlinks. They leave breadcrumbs behind that reveal their best tactics. Then pivot (no pun intended), glean ideas from their brilliance, and do it even better!

Required skill: pivot tables

If you don’t know how to use pivot tables, you need to check out this video walkthrough. I teach you everything you need to know and then some.

Download example pivot table

I redacted my client’s data from the pivot table in the Excel sheet, but you can get an idea of how I pulled together the data in the “Raw Data” tab and then see how I organized my pivot table in that tab. You can, of course, organize yours however you feel is best. But hopefully this will provide a good jumping-off point. I also sorted my pivot table by domain authority in descending order and then filtered out links from [free-subdomain].wordpress.com.

You can download the Excel workbook from Dropbox.

Steps to pull data together

Step 1: Pull your site’s (or client’s) backlinks — using Open Site Explorer, Majestic SEO, ahrefs, or whatevs — as well as a few of your main competitors. Then pull them together into a formatted table.

Step 2: Add another column and label it “Site.” What I typically do is add the domain (without the http:// or www to minimize noise) and double-click the bottom-right corner of the cell to fill down to the bottom of the data set. Rinse and repeat each time you add a new batch of backlinks. When you finish, you’ll have a single table that contains a mashup of backlinks.

Step 3: Extract the domains from the backlink URLs using the LEFT and SEARCH functions. If you haven’t done this before, I demonstrate how in this video tutorial. (Or you can download the demo workbook from the post and just copy the formulas.)

Step 4: Create your pivot table using these settings:

Step 5: As a general rule, I don’t like how Excel merely indents rows in the default, “Compact” pivot table format. In our data set here, where we have three different values pulled down in rows (Domain, Site, and URL). This can cause your rows to get really congested, and it can be hard to differentiate them. For this reason, if I pull multiple values into the Rows field list, I prefer the Outline layout. You can check it out in the Excel file download.

To set your pivot table to “Outline,” click on any cell in the pivot table and go to Pivot Table Tools > Design tab > Layout > Report Layout > Show in Outline Form (Mac: PivotTable tab > Design > Layout > Outline Layout).

Step 6: I pulled individual observations into text boxes to the left of the pivot table. I used text boxes for a couple reasons:

• I didn’t want to mess up the heights of rows inside my pivot table.
• I could attach links to the text boxes that linked to the individual cells in the pivot table.

Step 7: Dive into the data and start reverse-engineering strategies (using the observations I pulled out as a template, if you’d like). I can assure you there are many more to excavate from that data set!

Video tutorial

For you visual learners out there, I pulled together a 10-minute video walkthrough of some of the key steps I took to organize the data for analysis.

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!

Scammers and spammers operate in almost every niche. If it’s not an email from an African prince, I am being forwarded fake chain messages from friends and family.

This gave me the idea to make use of this spam and become the Snopes.com of my niche. People often search for myth busting information and you can use it as an opportunity to make lemonade from the lemon spam that you receive.

Here’s how to do it:

1. Set up a section on your site where you highlight scams and feature fradulent/spammy emails. You could create a standalone section on your website or it could be part of a weekly feature on your blog. You could call it ‘Spam Sundays’, for example. Once you have decided on a schedule, it is important to stick to it. You can use automated posts to help with this.
2. Ask your blog readers, Facebook fans and Twitter followers to forward any scam emails they receive which are relevant to your niche. Offer a weekly prize to motivate them and to keep them coming back. It’s amazing what people will do for a free tshirt!
3. Go through your own email, searching for any relevant scams or spam. If you use Gmail you can use filters so that any emails that match a certain criteria are automatically highlighted or you can search in Gmail for: ‘in:spam [your niche]’

   

1. Each week, copy and paste the email address, subject and body of any emails into a blog post or article. Include some narrative explaining why the email is obviously fake and how to spot similar ones. For example, people often receive fake phishing emails from HM Revenue and Customs (HMRC) stating that they are due a tax refund. In your post you could mention that HMRC would never send a tax refund notification by email.
2. When the post is live, contact blogs and websites in your niche. Inform the bloggers about the scams that you have uncovered and mention how the information might be useful to their readers. Also ask them to forward any emails that they might receive in the future. This will give them a call to action to become an ally for your worthy cause, helping to form a relationship for any future content that you might need help promoting.
3. Follow up in the comments to any questions that your readers might have.

The benefits of using spam as a content strategy

As ironic as it sounds, using spam as a content strategy will have a number of benefits. Firstly, you will be helping your community. You will be the spam superhero, stopping unsuspecting people from being scammed.

This is a good enough reason to implement this content strategy but there are also added benefits to your business:

Inceased traffic

• When I am forwarded an email that I think might be fake, I copy and paste the title or a snippet from the email into Google. If your site features these emails there is a good chance that you will receive more visitors from these types of searches.
• People are often searching for ‘fake’ or ‘scam’ related keywords. For example, if someone has heard about a tax refund scam, they might use Google to research it further.

  If your website has a section dedicated to featuring scams, with enough authority, you will rank highly in the SERPs for these keywords.

• You will benefit from long tail keywords. The long tail makes up the majority of search volume. The more relevant content and comments you have on your site, the more likely you are to receive visitors from the long tail of search.

Links

People love to win an argument and confirm that they are right. You’ll often find people linking in blogs, emails and comments to Wikipedia or Snopes.com to prove they are correct. If your site becomes the authority in your niche for outing scams, people will naturally start linking to you.

These links will increase the standing of your website and help to lift it in the search engine results pages (SERP) for other keywords too.

Reputation management

A common reputation management problem can arise if people start searching for ‘your company + scam’. When enough people search for this keyword phrase, Google will start to show it as an autosuggestion.

An added benefit of using this content strategy is that negative mentions of your brand will be suppressed in Google as your own site should rank with information about the scams which you have highlighted.

Trust

By helping your community and dispelling myths from spam emails, you will gain trust from potential customers. This will increase your chances of acquiring new customers when people require the service you offer and from people passing on your name through word of mouth. 

User engagement

Visitors to your site who submit fake emails will likely come back to see if their submission was included, especially if there is the chance of winning a prize.

People will also leave and reply to comments and this will help with user engagement and brand related searches.

The principal behind this content strategy is turning a negative into a positive. Do you turn any negatives to your advantage for your website or business?