5 Reasons to Consider Microsoft Access for SEO

The SEO industry has a substantial investment in Excel.  It makes sense, to a degree.  Data exported from our favorite tools is easily imported into Excel, can be manipulated, can be put into a Pivot Table, and so forth.  But perhaps the obsessive reviewing we bring to SEO tools like Open Site Explorer, Raven Tools, and browser plug-ins should be focused, for a moment, on what we use to analyze the data from these tools.

This isn’t intended to be a how-to.  My intention is to inspire you to change the way you think about analysis.  I’m assuming that the concept of databases is not foreign, but there will be a brief summary of resources at the end of this post.  In some ways this is a promotion of SQL querying—but Access has invaluable connections with Excel.  So, without further ado, five reasons you should be using Access!

1.     You Need to Know What You Need to Know!

The fundamental way to interact with data in Access is querying.  Querying is thinking. That’s all it is.  It may take a few minutes—or perhaps longer—to figure out exactly how to form your query.  Time spent thinking is not time wasted.  In fact, taking the time to think about what you need to get from your data will save you from wasting time in Excel performing manipulations that are unnecessary or have marginal relevance.

For example, assuming you have imported a CSV file from Open Site Explorer into Access, you might run the following query on it:

SELECT * FROM [OSE]
WHERE [OSE].Origin LIKE “External” AND
[OSE].[Target URL] LIKE “https://www.distilled.net[/]”;

Run Query

…in order to return a list of all external links to the homepage of www.distilled.net.  Sure, you could accomplish a similar feat with a table in Excel—but in Access you must put words to your thought process.  I find this makes my analysis much more precise.

2.     You Don’t Need to Know What You Don’t Need to Know!

Data in Excel has a way of expanding—a calculated field, a flag describing a link, a new sheet with an important subset of data.  The problem is that once your data expands, it never really contracts again.  Having five extra columns in a sheet or ten extra pages in a book only makes your analytical life more difficult.

Access solves this issue in three important ways.  First, it gives you a different table for each data set and each query.  These are much more manageable than Excel sheets, and they are logically separated based upon their functionality.  Second, calculated fields can be generated per-query in Access, so that each query contains exactly the fields necessary for you to work with.  Finally, you don’t need to see the data to interact with it.  You simply address each column of data by its name when querying or creating a pivot table.  Another example-by-query:

SELECT IIf(InStr(1,lcase([OSE].[URL]),“blog”),“True”,“False”)
AS BlogLink FROM [OSE];

This query produces a list of True/False values representing whether each link in the OSE list has “blog” in the URL.  No extraneous data.  Of course this is not the most comprehensive logical test of whether a site is a blog, but it is an excellent demonstration of how using queries abstracts data and prevents data overload.    This list can be be visualized like this:

Access Chart

Which leads us to my next point:

3.     You Don’t Need Pretty Graphs!

Analysis and reporting are not the same. When you are analyzing data for an SEO project you develop a keen understanding of the data you are working with.  You don’t need to prettify that data for yourself—you need it fast and accurate.  The client, on the other hand, might need a different representation of data.  Naturally, the graphs for analysis and reporting do have an intersection—they both depend upon accurate data representation.

Access provides you with data sets which will necessarily be well conceived (see the first two points).  If you have the information you need at your fingertips you can easily take that data from Access to Excel where you will be able to make a graph most beautiful in seconds because you won’t have to mess around with the data.

Export to Excel

4.     Keyboard Shortcuts FTW!

Efficiency is sexy.  Keyboard shortcuts are efficient.

Access Keyboard Shortcuts

It’s no secret that Excel is a click-happy program.  Sure, once you have a nice sheet set up, you can tab through it and enter data pretty quickly.  Most of us in SEO, though, don’t do a lot of manual data entry—we get huge batches of data from services like Open Site Explorer, then we import and click the bejeezus out if it in Excel to get what we need.

Access is generally easy to navigate with the keyboard.  This, combined with the querying process and the detachment of analysis from the raw data, makes Access much more efficient than Excel.  Yes, you will have to use your mouse at some point.  But when you take this point alongside the others in this list, I think you will find that your analysis is considerably more speedy, robust, and focused.

5.     There Are a Lot of Links Out There!

Link profiles for successful web site can easily number in the tens of thousands.  Working in Excel with more than 15,000 links—especially when using tables and calculated fields—can be horrendously cumbersome as the software increasingly taxes the hardware.  And competitive analysis might see you working with several of these tremendous link sets.

Access can handle queries involving multiple tables and queries of 100,000 links each without a hiccup.  What else need you know?

In Summary

Excel works pretty OK.  Really.  But if you are working with huge data sets, if you want to be more precise, if you want to be more efficient, Access is worth a try.  Excel will always be there for you, but you owe it to yourself to let Access take you to new and exciting places.

Where Do I Start?

If you don’t work with Access regularly, some of this might be a little overwhelming.  The best advice I have is to download Access and start using it.  After that, you might try Microsoft’s Getting Started with Access.  As always, there is no substitute for hands-on experience.

Benjamin Estes

Benjamin Estes

Benjamin is a senior consultant who joined Distilled in 2010. Having earned a BA in Mass Media, his intention is to continue studying the ways in which people interact with media and apply those lessons to his consulting. Ben-h264 // Born and...   read more

Get blog posts via email

5 Comments

  1. Nice article, Benjamin. I'd definitely agree, Excel works OK, but heavy duty analysis is definitely best left to Access. Another very nice feature, which you alluded to with the "you don't need pretty graphs" is that Access has aggregation capabilities in queries (the little "sum" or epsilon symbol in the "Design" tab of the ribbon) so you can group data by site, and do things like sum all occurring of that site. And for any readers who also handle Google PPC campaigns, Access is a huge advantage to optimizing campaign performance through analysis!

    reply >
  2. Benjamin Estes

    Thanks, Brandon! You're right, aggregation is huge. It enables you to do things with one or two queries that would take several sheets to accomplish in Excel! ...but I can't go giving away the secrets of my future posts yet ;)

    reply >
  3. Great post Benjamin, excel drives me nuts sometimes when trying to work with data and i have found access to be a good companion when i need to just pull out certain details quickly using a simple query.

    It also helps to be a bit of a jack of all trades using the right tool to solve a problem quickly so a basic understanding of SQL is always good.

    reply >
  4. Great post. Using the right tool for analysis is critical for efficiency and accuracy. That also requires an understanding of what you're seeking to find. But assuming that's understood, the tools make all the difference.

    reply >
  5. You may be covering it in a future post, but I'd also remind people that if you really want some data management controls, use the free version of SQL Express. You can then lay an Access project on top of that data and also easily incorporate it into some other potential applications and tools with the new SQL back-end. Attaching to a SQL back-end will also provide the ability for allowing multiple people to connect to the same data at the same time for analysis with another copy of the Access front-end. Multiple users in an Access project can start getting a little hairy with data corruption.

    reply >

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>