5 Spreadsheet Tips for Manual Link Audits

Posted by MarieHaynes

Link auditing is the part of my job that I love the most. I have audited a LOT of links over the last few years. While there are some programs out there that can be quite helpful to the avid link auditor, I still prefer to create a spreadsheet of my links in Excel and then to audit those links one-by-one from within Google Spreadsheets. Over the years I have learned a few tricks and formulas that have helped me in this process. In this article, I will share several of these with you.

Please know that while I am quite comfortable being labelled a link auditing expert, I am not an Excel wizard. I am betting that some of the things that I am doing could be improved upon if you’re an advanced user. As such, if you have any suggestions or tips of your own I’d love to hear them in the comments section!

1. Extract the domain or subdomain from a URL

OK. You’ve downloaded links from as many sources as possible and now you want to manually visit and evaluate one link from every domain. But, holy moly, some of these domains can have THOUSANDS of links pointing to the site. So, let’s break these down so that you are just seeing one link from each domain. The first step is to extract the domain or subdomain from each url.

I am going to show you examples from a Google spreadsheet as I find that these display nicer for demonstration purposes. However, if you’ve got a fairly large site, you’ll find that the spreadsheets are easier to create in Excel. If you’re confused about any of these steps, check out the animated gif at the end of each step to see the process in action.

Here is how you extract a domain or subdomain from a url:

  • Create a new column to the left of your url column.
  • Use this formula:

    =LEFT(B1,FIND(“/”,B1,9)-1)

    What this will do is remove everything after the trailing slash following the domain name. http://www.example.com/article.html will now become http://www.example.com and http://www.subdomain.example.com/article.html will now become http://www.subdomain.example.com.

  • Copy our new column A and paste it right back where it was using the “paste as values” function. If you don’t do this, you won’t be able to use the Find and Replace feature.
  • Use Find and Replace to replace each of the following with a blank (i.e. nothing):
    http://
    https://
    www.

And BOOM! We are left with a column that contains just domain names and subdomain names. This animated gif shows each of the steps we just outlined:

2. Just show one link from each domain

The next step is to filter this list so that we are just seeing one link from each domain. If you are manually reviewing links, there’s usually no point in reviewing every single link from every domain. I will throw in a word of caution here though. Sometimes a domain can have both a good link and a bad link pointing to you. Or in some cases, you may find that links from one page are followed and from another page on the same site they are nofollowed. You can miss some of these by just looking at one link from each domain. Personally, I have some checks built in to my process where I use Scrapebox and some internal tools that I have created to make sure that I’m not missing the odd link by just looking at one link from each domain. For most link audits, however, you are not going to miss very much by assessing one link from each domain.

Here’s how we do it:

  • Highlight our domains column and sort the column in alphabetical order.
  • Create a column to the left of our domains, so that the domains are in column B.
  • Use this formula:

    =IF(B1=B2,”duplicate”,”unique”)

  • Copy that formula down the column.
  • Use the filter function so that you are just seeing the duplicates.
  • Delete those rows. Note: If you have tens of thousands of rows to delete, the spreadsheet may crash. A workaround here is to use “Clear Rows” instead of “Delete Rows” and then sort your domains column from A-Z once you are finished.

We’ve now got a list of one link from every domain linking to us.

Here’s the gif that shows each of these steps:

You may wonder why I didn’t use Excel’s dedupe function to simply deduplicate these entries. I have found that it doesn’t take much deduplication to crash Excel, which is why I do this step manually.

3. Finding patterns FTW!

Sometimes when you are auditing links, you’ll find that unnatural links have patterns. I LOVE when I see these, because sometimes I can quickly go through hundreds of links without having to check each one manually. Here is an example. Let’s say that your website has a bunch of spammy directory links. As you’re auditing you notice patterns such as one of these:

  • All of these directory links come from a url that contains …/computers/internet/item40682/
  • A whole bunch of spammy links that all come from a particular free subdomain like blogspot, wordpress, weebly, etc.
  • A lot of links that all contain a particular keyword for anchor text (this is assuming you’ve included anchor text in your spreadsheet when making it.)

You can quickly find all of these links and mark them as “disavow” or “keep” by doing the following:

  • Create a new column. In my example, I am going to create a new column in Column C and look for patterns in urls that are in Column B.
  • Use this formula:

    =FIND(“/item40682”,B1)
    (You would replace “item40682” with the phrase that you are looking for.)

  • Copy this formula down the column.
  • Filter your new column so that you are seeing any rows that have a number in this column. If the phrase doesn’t exist in that url, you’ll see “N/A”, and we can ignore those.
  • Now you can mark these all as disavow

4. Check your disavow file

This next tip is one that you can use to check your disavow file across your list of domains that you want to audit. The goal here is to see which links you have disavowed so that you don’t waste time reassessing them. This particular tip only works for checking links that you have disavowed on the domain level.

The first thing you’ll want to do is download your current disavow file from Google. For some strange reason, Google gives you the disavow file in CSV format. I have never understood this because they want you to upload the file in .txt. Still, I guess this is what works best for Google. All of your entries will be in column A of the CSV:

What we are going to do now is add these to a new sheet on our current spreadsheet and use a VLOOKUP function to mark which of our domains we have disavowed.

Here are the steps:

  • Create a new sheet on your current spreadsheet workbook.
  • Copy and paste column A from your disavow spreadsheet onto this new sheet. Or, alternatively, use the import function to import the entire CSV onto this sheet.
  • In B1, write “previously disavowed” and copy this down the entire column.
  • Remove the “domain:” from each of the entries by doing a Find and Replace to replace domain: with a blank.
  • Now go back to your link audit spreadsheet. If your domains are in column A and if you had, say, 1500 domains in your disavow file, your formula would look like this:

    =VLOOKUP(A1,Sheet2!$A$1:$B$1500,2,FALSE)

When you copy this formula down the spreadsheet, it will check each of your domains, and if it finds the domain in Sheet 2, it will write “previously disavowed” on our link audit spreadsheet.

Here is a gif that shows the process:

5. Make monthly or quarterly disavow work easier

That same formula described above is a great one to use if you are doing regular repeated link audits. In this case, your second sheet on your spreadsheet would contain domains that you have previously audited, and column B of this spreadsheet would say, “previously audited” rather than “previously disavowed“.

Your tips?

These are just a few of the formulas that you can use to help make link auditing work easier. But there are lots of other things you can do with Excel or Google Sheets to help speed up the process as well. If you have some tips to add, leave a comment below. Also, if you need clarification on any of these tips, I’m happy to answer questions in the comments section.

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!

Reblogged 3 years ago from tracking.feedpress.it

Lessons from the Front Line of Front-End Content Development

Posted by richardbaxterseo

As content marketing evolves, the list of media you could choose to communicate your message expands. So does the list of technologies at your disposal. But without a process, a project plan and a tried and tested approach, you might struggle to gain any traction at all.

In this post, based on my
MozCon 2014 presentation, I’d like to share the high level approach we take while developing content for our clients, and the lessons we’ve learned from initial research to final delivery. Hopefully there are some takeaways for you to enhance your own approach or make your first project a little less difficult.

This stuff is hard to do

I hate to break it to you, but the first few times you attempt to develop something
a little more innovative, you’re going to get burned. Making things is pretty tough and there are lots of lessons to learn. Sometimes you’ll think your work is going to be huge, and it flops. That sucks, move on, learn and maybe come back later to revisit your approach.

To structure and execute a genuinely innovative, successful content marketing campaign, you need to understand what’s possible, especially within the context of your available skills, process, budget, available time and scope.

You’ll have a few failures along the journey, but when something goes viral, when people respond positively to your work – that, friends, feels amazing.

What this post is designed to address

In the early days of SEO, we built links. Email outreach, guest posting, eventually, infographics. It was easy, for a time. Then,
Penguin came and changed everything.

Our industry learned that we should be finding creative and inventive ways to solve our customers’ problems, inspire, guide, help – whatever the solution, an outcome had to be justified. Yet still, a classic habit of the SEO remained: the need to decide in what form the content should be executed before deciding on the message to tell.

I think we’ve evolved from “let’s do an infographic on something!” to “I’ve got a concept that people will love should this be long form, an interactive, a data visualization, an infographic, a video, or something else?”

This post is designed to outline the foundations on an approach you can use to enhance your approach to content development. If you take one thing away from this article, let it be this:

The first rule of almost anything: be prepared or prepare to fail. This rule definitely applies to content development!

Understand the technical environment you’re hosting your content in

Never make assumptions about the technical environment your content will be hosted in. We’ve learned to ask more about technical setup of a client’s website. You see, big enterprise class sites usually have load balancing, 
pre-rendering, and very custom JavaScript that could introduce technical surprises much too late in the process. Better to be aware of what’s in store than hope your work will be compatible with its eventual home.

Before you get started on any development or design, make sure you’ve built an awareness of your client’s development and production environments. Find out more about their CMS, code base, and ask what they can and cannot host.

Knowing more about the client’s development schedule, for example how quickly a project can be uploaded, will help you plan lead times into your project documentation.

We’ve found that discussing early stage ideas with your client’s development team will help them visualise the level of task required to get something live. Involving them at this early stage means you’re informed on any potential risk in technology choice that will harm your project integrity later down the line.

Initial stakeholder outreach and ideation

Way back at MozCon 2013, I presented an idea called “really targeted outreach“. The concept was simple: find influential people in your space, learn more about the people they influence, and build content that appeals to both.

We’ve been using a similar methodology for larger content development projects: using social data to inspire the creative process gathered from the Twitter Firehose and
other freely available tools, reaching out to identified influencers and ask them to contribute or feedback on an idea. The trick is to execute your social research at a critical, early stage of the content development process. Essentially, you’re collecting data to gain a sense of confidence in the appeal of your content.

We’ve made content with such a broad range of people involved, from astronauts to butlers working at well known, historic hotels. With a little of the right approach to outreach, it’s amazing how helpful people can be. Supplemented by the confidence you’ve gained from your data, some positive results from your early stage outreach can really set a content project on the right course.

My tip: outreach and research several ideas and tell your clients which was most popular. If you can get them excited and behind the idea with the biggest response then you’ll find it easier to get everyone on the same page throughout your project.

Asset collection and research

Now, the real work begins. As I’ve
written elsewhere, I believe that the depth of your content, it’s accuracy and integrity is an absolute must if it is to be taken seriously by those it’s intended for.

Each project tends to be approached a little differently, although I tend to see these steps in almost every one: research, asset collection, storyboarding and conceptual illustration.

For asset collection and research, we use a tool called
Mural.ly – a wonderful collaborative tool to help speed up the creative process. Members of the project team begin by collecting relevant information and assets (think: images, quotes, video snippets) and adding them to the project. As the collection evolves, we begin to arrange the data into something that might resemble a timeline:

After a while, the story begins to take shape. Depending on how complex the concept is, we’ll either go ahead with some basic illustration (a “white board session”) or we’ll detail the storyboard in a written form. Here’s the Word document that summarised the chronological order of the content we’d planned for our
Messages in the Deep project:

messages-in-the-deep-storyboard

And, if the brief is more complex, we’ll create a more visual outline in a whiteboard session with our designers:

interactive-map-sketch

How do you decide on the level of brief needed to describe your project? Generally, the more complex the project, the more important a full array of briefing materials and project scoping will be. If, however, we’re talking simpler, like “long form” article content, the chances are a written storyboard and a collection of assets should be enough.

schema-guide

Over time, we’ve learned how to roll out content that’s partially template based, rather than having to re-invent the wheel each time. Dan’s amazing
Log File Analysis guide was reused when we decided to re-skin the Schema Guide, and as a result we’ve decided to give Kaitlin’s Google Analytics Guide the same treatment.

Whichever process you choose, it helps to re-engage your original contributors, influencers and publishers for feedback. Remember to keep them involved at key stages – if for no other reason than to make sure you’re meeting their expectations on content they’d be willing to share.

Going into development

Obviously we could talk all day about the development process. I think I’ll save the detail for my next post, but suffice it to say we’ve learned some big things along the way.

Firstly, it’s good to brief your developers well before the design and content is finalised. Particularly if there are features that might need some thought and experimental prototyping. I’ve found over time that a conversation with a developer leads to a better understanding of what’s easily possible with existing libraries and code. If you don’t involve the developers in the design process, you may find yourself committed to building something extremely custom, and your project timeline can become drastically underestimated.

It’s also really important to make sure that your developers have had the opportunity to specify how they’d like the design work to be delivered; file format; layers and sizing for different break points are all really important to an efficient development schedule and make a huge difference to the agility of your work.

Our developers like to have a logical structure of layers and groups in a PSD. Layers and groups should all be named and it’s a good idea to attach different UI states for interactive elements (buttons, links, tabs, etc.), too.

Grid layouts are much preferred although it doesn’t matter if it’s 1200px or 960px, or 12/16/24 columns. As long as the content has some structure, development is easier.

As our developers like to say: Because structure = patterns = abstraction = good things and in an ideal world they prefer to work with
style tiles.

Launching

Big content takes more promotion to get that all important initial traction. Your outreach strategy has already been set, you’ve defined your influencers, and you have buy in from publishers. So, as soon as your work is ready, go ahead and tell your stakeholders it’s live and get that flywheel turning!

My pro tip for a successful launch is be prepared to offer customised content for certain publishers. Simple touches, like
The Washington Post’s animated GIF idea was a real touch of genius – I think some people liked the GIF more than the actual interactive! This post on Mashable was made possible by our development of some of the interactive to be iFramed – publishers seem to love a different approach, so try to design that concept in right at the beginning of your plan. From there, stand back, measure, learn and never give up!

That’s it for today’s post. I hope you’ve found it informative, and I look forward to your comments below.

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!

Reblogged 3 years ago from moz.com

Incredible Marketing: Keyword Meta Tag SEO

Erving the Owl from http://incrediblemarketing.com breaks down optimal seo optimization of the keyword meta tag. Animated & Editing by Zack Bujazia. Written …

Reblogged 3 years ago from www.youtube.com

JetDesk Animated Marketing Video

So your small business has you busy…to say the least… JetDesk simplifies your life by simplifying your IT. Our new explainer video for JetDesk. Created b…

Reblogged 4 years ago from www.youtube.com

explanation video

explanation video http://www.amodfilms.com created this animated explanation video that communicate the advantages and process of using The Online Dermatolog…

Reblogged 4 years ago from www.youtube.com

Omisem: Web Design, Animated Videos, Local and Mobile SEO

Omisem specializes in: Web Design, Animated Videos, Local and Mobile SEO as well as comprehensive Keyword Research Meet Jane. Jane has a brilliant idea. She …

Reblogged 4 years ago from www.youtube.com