Analyzing Organic Search Data To Improve Small Business SEO

SEO is simple.


Virtually every business relies to a certain extent on where they will appear in an online search for their service, product, or company name.

Online businesses, in particular, spend massive resources to ensure that they rank highly on Google, Bing, etc., for any relevant search, and most spend a significant percentage of their marketing budget to define the best keywords, optimize their websites, and execute their strategies.

If you’re writing a business plan and planning to start your business, you should at least have a basic understanding of search engine optimization (SEO).

To most managers (and I include myself in this group), SEO is an opaque mess. The wizards who make a living at this work are enigmatic masters of an obscure and mysterious art. They speak a language that most of us find to be bafflegab and trying to understand them can make one’s head explode.

This doesn’t diminish the importance of understanding the basics of SEO, nor does it excuse you from doing your homework and learning the basics.

There are plenty of great resources out there on the interwebs, and managers must take the time to develop at least a rudimentary understanding of this puzzling and inscrutable art. A great place to start is with the wonderful primer available via Moz.

Now just because SEO strategy may be over your head, this doesn’t mean that you can’t effectively track your rankings and keywords to gain a clearer understanding of where you stand in relation to, well, everyone else in the world.

Little too broad for you? OK, fine, then start by understanding where you rank for the keywords most important to your business.

For instance, if you are a catering company, you’ll want to track words like catering, wedding planning, events, bar mitzvah, party, etc., etc. to see if your company shows up when someone searches for terms like those.

Follow these steps to analyze your organic search data and improve your small business SEO:

1. Fetch!

You’re going to download your data now, but first, you’ll need to set up an account on¬†Google’s Webmaster Tools¬†which should take you all of 5 minutes (here’s a great how-it-works video).

Once you’ve got your site set up, you’ll have a look to understand better how search results lead to your website’s traffic.

How’s that, you say? In the left-hand menu, click on “Search Traffic” and then “Search Queries.” This will display a graph that represents the number of impressions your company received (i.e., the number of users who viewed a page of search results in which your company included), as well as the number of people who clicked through to your website.

Below the graph is a table that lists the actual search terms people typed in as well as columns for the number of impressions for each query, as well as clicks, click-through rate (CTR) and your average position in the results (1-10 equals page one of the results, 11-20 is page 2, etc.). You can sort these results by clicking on the column headers, and you can also mouse over the graph to check your totals day by day.

Now it’s time to start your download, week-by-week.

First, you’ll want to see all of the search terms which results in your company being listed, even the most arcane (think longtail); the default for this page is the top 25 search terms, but you can use the pulldown above the table on the right to display 500 rows of data.

The prior 30 days of data are automatically displayed, but for this exercise, you’ll want to click on the date buttons upper right to look at one week at a time.¬† Set a date for a one-week range as far back as the site will allow (note: Webmaster tools only provide the past 90 days of results).

When you have a single week of data displayed, click the “Download this table” buttons on the upper left to create a CSV file which we will use shortly. Change the date range to the next week and repeat until you have 12 or 13 downloaded CSV files.

You’ll want to rename these files in a manner that makes sense to you so you can keep them organized; I like to use dates or other specific identifiers when naming multiple files of data. Now the fun can commence!

Want a free brand review?
brand identity grader hero
Answer 5 short questions and we will send a custom report with actionable insights and specific actions you can take to build a stronger brand.

We just emailed the info to you.

2. Week over week over week.

Right now, you’re probably asking yourself, ¬†“Why do I have a dozen data files, and what am I supposed to do with them?”

Well, let me tell you: you’re going to copy them into a new Excel spreadsheet and get to work.

That’s right – fire up Excel, and get a brand new clean workbook in front of you. This workbook needs to have one tab for each file you’ve downloaded ‚Äď if you have 12 data files, create one tab for each; I like to rename the tabs, so they match the CSV files I will be copying into each one.

Once you have your weekly tabs created, open those CSV files in excel and copy/paste the data from each into the corresponding tab. Don’t forget to save your work!

3. Pick up the tabs.

Each of your tabs will look the same as the others, with the same column headers and the same number of rows (501, including the header).

For you to manipulate the data, however, you’ll need to add a few more columns to the right of the data in each tab: Date, Year, Month, and Week. For the date, which will be in Column F of your worksheets, use the first date of the week that this particular data-set represents; for instance, if it is for the week of March 1st – March 8th, simply type in 3/1/15 and copy it down the entire column. In Row 2 of the “Year,” “Month,” and “Week” ¬†columns, type in these formulas: =year(F2), =month(F2), and =weeknum(F2). Copy those three formulas down their entire columns to fully populate your data set. (Tip: I like to highlight the added columns in yellow, so I can see at a glance which are the original columns of data and which I have added.)

4. Aggregate.

Now create yet another new tab, this one labeled “Aggregated,” and copy the full data-set from each tab (including your yellow columns) into this tab; when you finish this little chore, you should have a master data-set that has thousands of rows of numbers and includes your date, year, month, and week columns.

This becomes your “master” set and contains the full 90 days’ worth of data, and will allow you to track performance week-over-week, which Webmaster Tools does not provide for.

5. Your table is ready.

Here we go!¬†Excel contains a very powerful feature, without which it would be impossible for us to do meaningful (and fast) data analysis: the “PivotTable” report.

A PivotTable allows you to select a range or sheet of data and look at that information in different ways with just a few mouse clicks. Creating your PivotTable can be a little intimidating, but hang with me, and I’ll show you how to do it.

There is invariably some trial and error in the process, but it is really cool once you have one set up and start getting the results.

A PivotTable is a self-contained chart divided into columns and rows that you define and easily change.

First, let’s select all of the data in the “Aggregated” tab by clicking and dragging starting with column A and going all the way to column I; you should have highlighted all 9 columns of data. Next, click the Data menu and select PivotTable from the list; a window will appear asking you to define the data for your PivotTable – you’ll see that “Use a table or a range in the workbook” is already selected and columns A:I are shown in the “Location” field; click OK and continue to the next step.

A cool-looking window will appear called the “PivotTable Builder;” this is the tool where you customize the information you want to be included in your PivotTable.

You’ll also see that an empty table has appeared on a new sheet of your workbook (you can label this one “Dashboard”) with sections titled Column Area, Row Area, and Values Area; this is your shiny new and un-populated PivotTable, and this is where you will see your answers start to magically appear as we go through the following steps.

6. Filter for best results.

To get you started on your first simple analysis, let’s ask a¬†question to populate the table, “How has my #1 keyword performed over the past 12 weeks?

You’ll see in the Builder a list of “Field names” – these are the column-heading titles for each of our nine columns; start by clicking and dragging “Week” down to the section below called “Row Labels” to look at your data week over week.

We want to understand for our keyword, the “Avg Position” for each week, as well as the number of “Impressions” and “Clicks.” Drag each of those from the Field Names area down to the “Values” area, and you will see the corresponding columns appear in your pivot table.

By default, each item in your Values will default to “Count,” displaying how many times it appears in your data each week; by clicking on the little “i” symbol, you can change that from Count and “Summarize by” Sum,¬†or¬†Average, or¬†Maximum, Minimum, etc., depending on what information you are analyzing; for our purposes, we will change the “Avg Position” column to Average.

Now change the “Summarize by” for Impressions and Clicks to “Sum” and Bingo! You will see, week over week, the average position, total number of impressions, and total clicks for that particular keyword.

You can use your PivotTable to answer other questions by selecting other fields from the list: for instance, which keyword has the best CTR? Or, which week has the most impressions?

Play around with your Pivot Table Builder, try different analyses, and… have fun!

Design Done Better

The easiest way to get affordable, high-quality custom logos, print design, web design and naming for your business.

Learn How to Grow Your Business With Beautiful Design