Yesterday I shared some interesting information about how Google+ was assigning a unique, UNIX timestamp (client-side) to every clicked link. More than a few people asked how to actually get that data, since it’s not obvious. Today, we’re going to dive deep down the rabbit hole and show you how to do it yourself, step by step. You’ll need Google Analytics, a text editor, and a spreadsheet program. You will also need to get your nerd on. Ready?
Caveat lector: this guide overwrites the User Defined variable inside Google Analytics. If you currently have custom data being recorded in User Defined, do not use this guide as it will overwrite your existing User Defined data!
First things first. You’ll notice that Google Analytics doesn’t record query strings. Google+ sends traffic by query string. Remember, this is how a link appears from Google+ to your site:
This is how the same link, and all Google+ links, show up in Google Analytics:
Notice that all the tracking stops after the question mark in the referring URL? That’s where the good stuff is. So how do we get to it? Google Analytics provides an obscure way to record query strings that requires you to override its default behavior. Here’s how to set it up. First, go to your Google Analytics account’s settings. This is the main settings area for your account, not any one particular tab.
In your site’s settings, add a custom advanced filter:
Once you have the filter screen up, make a filter that looks like this:
Hit save, and your Google Analytics account will automatically begin collecting query data. Let it collect at least a day’s worth of data, more if you’re not especially active on Google+. Then go into your Google Analytics account and look at the User Defined data:
In order to find just Google+ data and not other query strings, you’ll need to apply a filter to the data. Use sa=z and you’ve got a list of what you’re looking for. If you want to isolate a specific URL that you shared on Google+, use that instead of the blanket sa=z variable; remember to URL encode it or you won’t get anything useful.
Next, set the rows displayed to 500 (the maximum Google Analytics will let you export from a single sub-report like this) and then hit your export button to your preferred data format:
Now you’ve got your CSV file that’s in a format which is terribly unhelpful. Open up your text editor, trim off the top section, replace commas or tabs with line breaks, and extract any line containing sa=z to a separate file. If you use BBEdit on the Mac or UltraEdit on the PC, this should be relatively trivial. What you should be left with is a pile of URLs that looks like this:
Once you’ve got this pile of URLs, you need to break each URL into pieces so you can export the G+ timestamps. To do this, in your text editor, execute a find and replace for the & and = characters, replacing them with commas. Then open the file in a spreadsheet package. You’ll end up with a nice, neat list of columns. Sort this by an individual post you want to measure, which should be column 6. Isolate the post, then sort by timestamps, ascending. Hit the charting button on the timestamp column and voila! You have a velocity chart for that post, because UNIX timestamps are sequential integers.
Remember that you’ll need to flip the axes to put the timestamps along the X axis; that will show you how tightly packed the middle of the chart is, where your posts have taken off.
Try this methodology out and see if it works for you, if it sheds any light. Those of you who are hardcore spreadsheet jockeys can even do comparisons of different posts to see how different kinds of content can have different sharing velocities. If you trim the last 3 digits off the G+ timestamp, you can also then transform it into standard spreadsheet datetime formats and assess what times of day and days of the week your content reaches velocity the fastest.
For example, if you notice that the inflection point on your posts tends to be around 11 AM local time, you have a better idea of when you might want to push out material that needs attention. I’m sure someone will eventually turn this sort of complex data analysis into an overly simple “When is the best time to Plus” blog post, so to avert this, I recommend that you do the data analysis for yourself. Everything you need has been provided for you already.
I hope this post gives you some additional ideas for data analysis using Google+ timestamps, as well as gives you some new things to learn with the tools you already have. You have everything you need already to do some amazing stuff. Go forth and do so.
You might also enjoy:
- Marketing Data Science: Introduction to Data Blending
- Is Social Listening Useful?
- Understand the Meaning of Metrics
- How I Think About NFTs
- Retiring Old Email Marketing Strategies
Want to read more like this from Christopher Penn? Get updates here:
Get your copy of AI For Marketers