Small Business and Startup Tips: Analyzing Your Search Data Mike | April 27th, 2015
SEO is simple. NOT! 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 on their strategies.
To most managers (and I absolutely 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 it is critical that managers 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, the online resource for everything SEO.
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. Get your list together now, because you’re going to use it as you follow these steps to track your search results!
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 better understand 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 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. In addition the prior 30 days of data is automatically displayed, but for the purpose of this exercise, you’ll want to click on the date buttons upper right to look at one week at a time. Set the date for a one-week range as far back as the site will allow (note: Webmaster tools only provides 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 which makes sense to you do 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!
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, simply 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 exactly 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, simply 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.)
Now create yet another new tab, this one labeled “Aggregated” and copy the full data-set from each individual 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 now to track performance week-over-week, which is something that 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 can start getting the results.
A PivotTable is a self-contained chart divided into columns and rows that you define and that you can easily change. To start, 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 customize the information you want 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 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 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 diffeent analyses, and… have fun!