Our Web Analytics World bloggers are sector specialists so it’s important that we keep everyone up to date with how their posts are performing. To do this we’ve created a couple of Excel workbooks to track activity, both on the blog post in terms of views and on social media shares.
After April Wilson’s post about how to create a Content Marketing Analysis (where she used WAW’s analytics as a demo) we thought showing you how we created the report April referenced would be a great follow up.
We’re also hoping you’ll be able to take what we do and run with it to create a tracking method that works for your organisation and social platforms.
As Usual we Started with Excel
We use Excel a lot in our work and never more so than when tracking Social Shares for our posts and we actually use two Excel workbooks to create our final report.
Our main workbook compiles data from two sources: Google Analytics and Social Metrics Pro. Social Metrics Pro is a plugin for WordPress that tracks when people share/like/1+/tweet from our blog pages – if you’re not using WordPress as your CMS you may need to do some digging to find a similar plugin.
Let’s go Data Hunting
Social Metrics Pro
First we access our Social Metrics data from our WordPress Dashboard. For those of you using WordPress and Social Metrics Pro your data output will look the same, for those of you using another CMS and therefore a different plugin you may need to play with this section a bit before you can export what you need.
Social Metrics Pro allows you to export to a CSV or XLS file, but before you export remember to click on the Refresh icon to the far right. It will only refresh your last 20 posts but it does allow you to go back and refresh individual posts as well. After you’ve refreshed all the relevant posts click on the xls export button and a new window opens:
To download your report click on the link and your exported data looks like this:
Save this export because we’ll be adding it to one of our Excel spreadsheets once we have collected all our data!
Next we go to Google Analytics and sign into our WAW account. First of all we want to setup our date range and as we track posts for approx. 6 months, for this example we’ll be using from April 4th to October 17th 2014.
Next we click on Behavior => Site Content => All Pages on the left side navigation and get our general overview of all our pages/posts for that time range and you’ll notice that the data is sorted by URL. We want this sorted by our title so we change our Primary Dimension to “Page Title”:
Now scroll to the bottom of the list and see that we have more than 10 rows of data, we want to show all of them (in this case 1542) so we adjust to show 25 rows and then click on our browser URL which will look ever so attractive like this:
At the end of your URL you’ll see D25 just change that to read D1542 and hit enter – this will show all 1542 rows on your screen. Now you can your data as an xls (top left of your screen) and save it somewhere safe. When you open it don’t panic if you see a blank spreadsheet you should have three tabs and the middle one labelled “dataset1” click on the middle tab and voila! Your data!
Now for some Excel Fun
This is where we create our main report workbook which you can call anything – let’s call it “Social Stats” for fun. We share out on four main social platforms: Twitter, LinkedIn, Google+ and Facebook so we keep track of those as well as our Google Post Views and Facebook Reach. We’ve setup our workbook with multiple date tabs to see changes from week to week, but our header columns on every tab look like this (click to enlarge image):
You may not want to track all of these or you may have more to track than we do so this is the time for you to customise your main workbook. Note that in our header row we call the title “Custom Document Title” because that’s what the field is called in our WordPress install but it may be called something different depending on how your CMS is setup for post optimisation.
And now … Presenting Excel Magic!
The second workbook is where we combine all of the data we’ve just taken from Google Analytics and Social Metrics Pro and compile it to add to our new Social Stats workbook.
We use four tabs in this second workbook and VLookup formulas to pull in the data we want, remember you can rename these tabs to fit your own workbook and tracking needs, and in ours we’ve used easy to remember names:
First we’ll take our Google Export data that we saved somewhere safe. Just cut and paste the entire middle tab labelled “dataset” into the corresponding tab and it should look like this:
Everything on this tab is exactly as it is in the Google Export we downloaded except for the Column 9. We “borrowed” a formula from Annie Cushing (Thanks Annie!) to take the average time that Google gives us and convert it into minutes and seconds and it looks like this: =RC[-5]/60/60/24
Now we get to write some fun functions so we head over to the “For Google Post Titles” tab and in here we’ve used a VLookup function to get the data we need from our export:
- RC1 => This is Column 1 on your current tab and tells the formula you’re looking to match the Custom Document Title title
- ‘Google Export’!C1:C8′ => This is Column 1 on your export tab
- 2 => This number tells the formula to return the data in column 2
- False => this tells the formula to return an exact match to your custom document title (column 1)
When you’re completing this one make sure you have the column numbers correct, i.e. in our spreadsheet average time on post is in column 9 of our export tab and not column 3. Now to populate your new tab with data just copy and paste your Custom Document Titles from your main workbook into column 1 of your tab and again, voila!
If there isn’t a match in the Custom Document Title in column 1 then Excel returns an #N/A symbol which could happen for a few reasons:
- You’ve misspelled your Custom Document Title either in the workbook (on either tab) or in your main workbook
- You’ve changed the title after publishing a post
- Your post had a blank title until you remembered you forgot to add it and went back to change it after publishing
- There’s no custom document title in column 1 therefore no data to pull (in this case think about using the URL in your VLookup or you can go back to Google Analytics change your primary dimension back to page and enter your url in the search box. This will give you post views for that individual post)!
So, copy all the numbers from the “Google Post View” column and use the paste special (right click on your first cell, then select “paste values”) command to paste numerical values and remove the V-Lookup formula.
Social Metrics Pro
Now we’ll add our Social Metrics Pro raw data to the appropriate tab export tab (in our case SM Pro Export):
We only need from Column E to Column L (Post URL to LinkedIn Shares) so copy everything within those columns and paste it to your SM Pro Export tab – this is our raw data. Move over to your “For SM Pro Titles” tab and we’ll begin populating this one like we did for our Analytics data. We’re using the same VLookup formula:
- RC1 => Column 1 which has your URL (on this tab)
- ‘SM Pro Export!C1:C8 => The range on your Export tab that has all your post URLS
- 2 => The column that you want to pull the data from, in this example it’s column 2 which is our Tweets
- False => indicates that you want an exact match returned which in this column means only the Tweet data.
For your rows of data you can copy this exact formula and paste it into the “likes”, “1+” and “LinkedIn” cells just remember to change your column number from 2 to the correct column number. i.e. LinkedIn=5.
Last copy and paste your list of post URLS into Column 1 and watch your spreadsheet get populated! Copy and paste your Google Post Views and Average Time on Post columns into your main Social Tracking Spreadsheet using the paste values option.
Putting it all Together
Now that you’ve copied over all your data from your VLookup file you can save it and close it down – for every time we run this report, we just re-populate the export tabs and add our updated URL and titles which means you never have to touch your VLookup formulas again!
Heading back to our main Social Tracking spreadsheet we now need to calculate changes from week to week. For all our Social tweets/likes/1+/shares we just take our current week and subtract the report date i.e. =RC[-10]-‘WAW Post Data_26Sept’!RC[-10]
For Google Post Views we like to have a percentage so we do the same thing but divide the above calculation by 100 (don’t forget your brackets!):
=( RC[-1]-‘WAW Post Data_26Sept’!RC[-1] ) / ‘WAW Post Data_26Sept’!RC[-1]
The last thing we do is populate our Facebook Reach section. To do this simply log into your Facebook admin page and click on “Insights”:
Then click on “Posts” and you should get a screen that has a graph showing how often your users are logged into Facebook and as you scroll down you should see a list of your Facebook posts and corresponding data:
You want the column labelled “Reach” and you just have to manually add in the number to your spreadsheet. We don’t currently calculate the difference in Reach for each report run but you could certainly add that column to your spreadsheet!
That’s all She Wrote
So there you have it! That’s how we build our social tracking report and then send it out to our bloggers. It helps us to track how posts are doing, if sharing articles out at different times affects how other share them on and also helps us track tweaks in our scheduling and sharing.