Tracking Social Media With Google Spreadsheets ‑ Part 1

February 14, 2012

This is part 1 of a 2-part series on using Google Spreadsheets to make social media measurement easier, quicker and almost completely automated. When you’re done reading this, Part 2 awaits!

Google Docs and Twitter

There are two problems I’m going to solve for you today:

1. Tracking social media is hard
2. You don’t have time to track social media

Tracking Social Media Is Hard

Let’s tackle the first problem. Yes, tracking social media is incredibly difficult. There are a million things you can track, including content on your site that’s being shared, people that are mentioning your brand or products online, tweets, shares, likes, pluses – you name it. It seems like everything is social these days, which makes tracking it kind of like counting grains of sand as they fall through your fingers.

But you have to start somewhere, and the easiest place to start is on your own site. With your own content. Which raises a number of other tracking issues. For instance, there’s a difference between tracking how many people click the social sharing buttons on your pages vs. how many people click on the links in your tweets vs. how many people copy and paste your URL and share it on their own (and how many people click on the links in their tweets/shares/posts etc.).

Again, you have to start somewhere. Today, we’ll start with tracking how many people click on the links to your content that you share on your social networks. This is actually rather easy, and I’ve broken it down into 5 steps:

  1. Copy the URL of the content you’re sharing
  2. Paste that URL into the Google Analytics URL builder, along with the following:
    1. Campaign Source – twitter (or facebook, linkedin, googleplus, etc.)
    2. Campaign Medium – social
    3. Campaign Name – blog (or something that signifies the type of content you’re sharing)
  3. Take the full URL (for example, http://www.yoursite.com/awesome-blog-post?utm_source=twitter&utm_medium…) and shorten it with Bit.ly (because every character counts when you only have 140)
  4. Share that shortened link on the specific social platform that you identified for the utm_source
  5. Rinse and repeat steps 2-4 for every social network you want to use

Wondering why I chose those source, medium and campaign names? Read up on this refresher for campaign tagging in Google Analytics. Doing the above will make it very easy for you to get insights into which social media channels are worth your time. Just go to Traffic Sources > Sources > Campaigns, click on blog (or whatever campaign name you chose), and do a little happy dance when you see a report like below:

Measuring social media - campaigns report

Notice, I ditched the standard data table view for the sexy comparison view.

I Don’t Have Time to Track Social Media

It’s true, the steps above take time. In fact, it used to take me at least 15 minutes to add the campaign parameters, shorten the links, and then share them on the various social networks. I can’t stand wasting time doing repetitive tasks that can be easily programmed. Thanks to Tom Critchlow’s post on how to build agile SEO tools using Google spreadsheets I can choose to spend my time building tools that do the repetitive nonsense for me. I’ve been using Google spreadsheets to hack together tools very quickly to do all sorts of things, including automating the task of tagging URLs with campaign parameters and shortening the links.

Social media tracking tool

I’ve done the dirty work for you, so go grab your copy of the spreadsheet here. Once it’s open, go to File > Make a copy so you can edit the spreadsheet (you may need to sign in to your Google account  – even if you were already signed in). All you need to do is enter your Bit.ly username and API Key (you can find that here) on the ‘Technical Details’ tab, then enter the URL of whatever page you’re sharing and voila! This tool takes your URL, appends campaign parameters for Twitter, Facebook, Gooogle+ and LinkedIn and shortens each one for you so you can quickly and easily share those links. How you spend the 15 minutes you saved is up to you, but please take just 2 of those minutes and tweet this post. I don’t ask for much ?

How It Works

This is for those of you interested in peeking behind the curtain to see how this works. The real magic happens on the ‘Behind the Scenes’ tab. You’ll see that cell B3 pulls in the URL that is typed in on the main tab. Columns C, D, and E hold our values for utm_source, medium and campaign.

Behind the Scenes tab - social media measurement tool

In cells B8 through B11, you’ll see a formula like this:

=concatenate($B$3, "?utm_source=",C3, "%26utm_medium=",$D$3, "%26utm_campaign=",$E$3)

This simply takes the URL in B3 (the page that you’re sharing) and concatenates that with the values for source, medium and campaign. One thing to note is that instead of a “&” I’ve used “%26” – which is the URL encoding for the ampersand character. This is necessary for the Bit.ly API.

In cells B13 through B16, we take the campaign tagged URLs from the previous step and run them through the Bit.ly API. The following formula accomplishes that:

=concatenate(importdata(concatenate
("https://api-ssl.bitly.com/v3/shorten?login="&'Technical Details'!$A$4&"&apiKey="&'Technical Details'!$A$7&"&longUrl="&B8&"&format=xml")))

The important thing to notice in the above formula is the use of the importdata function. It lets us pull in the results of the Bit.ly shortening operation. For more details, check out the Google spreadsheet function list.

Finally, back on the main tab (ShareMe!), the Bit.ly shortened links are pulled in from the ‘Behind the Scenes’ tab using the following formula:

=mid('Behind the Scenes'!B13, (find("<url>",'Behind the Scenes'!B13)+5), 20)

So if you want to add more social networks (or change the ones listed), you’ll need to go into the ‘Behind the Scenes’ tab and make your modifications there.

Check Out Part Two

In Part Two, I’ll show you how to use scripts within Google Spreadsheets to detect when you’ve published a new post, and automatically send an email with the shortened links.