This is a repost from my main blog
This is actually a revisit of a post I made in 2012 on ‘Using Google Spreadsheets to combine Twitter and Google Analytics data to find your top content distributors’. This idea is worth a revisit as things have got easier to mash Google Analytics data in Google Sheets and my Twitter Archive Google Sheets (TAGS) project has got easier for users to setup. The basic concept is that if you are using Google Analytics to track your website usage you can see the volume of traffic from particular sources including Twitter as a referral source. As referral links from Twitter are unique we can identify who originally tweeted the link to your site and measure its impact (number of visits, purchase, etc.). The actual mechanic of this are outlined in the ‘Referral Traffic’ section of the original post if you’d like more explanation so instead I’m going to jump straight into the new method.
Setting up a TAGS Archive for Google Analytics referral analysis
Before going into the setup I should say that this solution requires that the Google Account you use to setup TAGS also has at least view access to the Google Analytics account you use for your website. With this in mind lets begin:
- First you need some tweets to use with our Google Analytics data which you can do by getting a copy of TAGS – you can use either version 6.0 or 6.1
- In the Readme/Settings in the search term enter the website domain you wish to track (for my website I use hawksey.info – the Twitter Search API isn’t 100% reliable but surprisingly good at resolving urls e.g. you’ll spot a couple of shortened urls in this search for my site)
- From the drop down menu select TAGS > Run Now! (at this point you might want to also use TAGS > ‘Update archive every hour’ to start a continuous collection of data)
Getting all GA with the Google Analytics Add-on
The Google Analytics Add-on is a Google Sheets customisation produced by the Google Analytics team to easily get GA data in Google Sheets. Add-ons are not limited to just customisation written by Google and they can be created by any third party developers coded using Google Apps Script. There are actually a couple of analytics add-ons in the store so you might want to check those out as well. For this solution I’m going to focus on using the official Google Analytics add-on. The steps below guide you through setting it up:
- In your Google Sheet select Add-ons > Get add-ons… and search for ‘analytics’ clicking the ‘Free’ button for Google Analytics
- You should be prompted with a permissions window and if you are happy click on ‘Allow’
- Give it a couple of seconds to let the add-on install and then you can click Add-ons > Google Analytics > Create new report
- This should open a sidebar where you can give the report a name ‘Twitter Referral’ and select your GA account, property and view
- At this point you can skip Metrics and Dimensions and click ‘Create Report’
- To help you get the Metrics and Dimensions used in the rest of this example open this existing report configuration sheet and copy/paste the values from B8:B15 into your own ‘Report Configuration’ sheet in your TAGS Sheet.
- In your TAGS Sheet you can now use the drop down menus to select Add-ons > Google Analytics > Run reports which should pull back some Twitter Referral data into a new ‘Twitter Referral’ sheet similar to the screenshot below
- To finally combine our Google Analytics data to archived tweets open this existing ‘TopTweeters’ and from the sheet tab at the bottom select ‘Copy to…’ and select your new TAGS Sheet as the destination
This should give you an example sheet with the top 10 tweets that have generated the most new visits in the last 7 days. I’m not saying this a perfect example of displaying the data but hopefully it gives you some ideas:
One final thing you might want to do is select Add-ons > Google Analytics > Schedule reports so that your GA data will automatically be refreshed in this sheet.
So there you go linking your Google Analytics data to individual tweets to monitor impact. There is so much more you can do with this basic idea and it would be interesting to start incorporating ecommerce data, hey you might even want to reward people generating income for you. If you’ve got aspirations for a big website it’s worth remembering Google Sheets isn’t a big data solution so you might want to run this on a 7 day window so that you don’t tip over the 2 million cell limit. Enjoy!