Pivot tables are going to be your new best friend in SEO. In today’s video tutorial we are going to show how to use a pivot table to break out the data in Google Webmaster Tools in days, months, and quarters.
Google Webmaster Tools
Google Webmaster Tools is a great tool to get an idea of the impressions and the clicks to your website. Now with Google Analytics not providing the keyword data this tool has become even more powerful for marketers. While the data some might argue is not 100% accurate, it is all about getting a macro idea as to what is happening. With Google Webmaster Tools we want to click on search queries and go as far back as we can for the most recent day of data provided. As a note, I would recommend doing this every month and combining the data into one large excel file. Then you can run a remove duplication, condition statement so you get every month’s data. The step that you want to go forward with is changing the rows to 500 and click on download chart data. You can use a Google Document but I would recommend just using excel if you want to follow step by step in this tutorial. See the picture below as to what you need to set up in Google Webmaster Tools
The Excel File
This is my excel file below. You will see that the data is broken out by the day and shows the impressions and the clicks. We want to take this a step further. In the top left column (A1) we want to write in the word Date. Next we want to select the data across with our mouse by holding down the left mouse button till we get to the clicks column. Next we want to select the entire data series. As a note you can do this much faster by using an excel short cut key command. If you are using a PC you want to use the CTRL + SHIFT + DOWN key which will select all of the data on excel. This is what it will look like below Now we want to go to insert and select the Pivot Table icon. See below We want to press OK and now excel has opened up a new tab where we can work on our pivot table for our SEO purposes. This should look like something like this below.
The PIVOT Table
Now we have created our new tab called sheet 1 and it will look like this below. We want to go to the right side of the screen and click off all of the boxes that say Date, Impressions, and Clicks. Now we have our data presented in front of us. This is where the pivot table comes in handy. We want to go to any of the dates in the column and we want to right click on one of those cells. Next we want to look for the section that says Group. It will look like this below. We want to go to any of the dates in the column and we want to right click on one of those cells. Next we want to look for the section that says Group. It will look like this below. Once we have selected our group section the default will be set to the group months. For now we can click on that and you will notice that the data went from individual days to grouping the data into months. In the video tutorial above, I mentioned that this data could be misleading if you were presenting this to your boss, your client, your team, or even yourself. If you notice in the picture above August is the weakest compared to the other months. This can look really bad in a report. What you should do is again right click on one of the months and go back to the group that says Group. Now we want to check off a few more boxes just so we are looking at the data clearer than before.
Start messing around with different pivot functions
Here I personally like to break out my group data into three categories just so I am looking at the data from a macro level down to a micro level. I choose the sections by quarter, month, and day. Now moving forward I can click on the minimize button at any time and either collapse or bring up certain sections to figure out what month might of been up or down and then see what days could of been off. This is a great way just to tie in the holidays that might of skewed your results to your client or to your team. Once I have clicked on all three and hit OK, now I have new data that I can look. See below to what I can mess around with in excel with the data. You can as a bonus break out the PIVOT table into graphs to break out which quarters performed better, which months performed better, or even what day during the month out performed all of them. This is a great way to show the power of a blog post that was written or some sort of positive (or negative) press release coverage where your brand name is in the news. Either way you should keep this excel sheet opened as a reference so each month you can update it with new information.