Upcoming LunaMetrics Seminars
New York City, Aug 4-8 San Francisco, Aug 11-15 Los Angeles - Anaheim, Sep 8-12 Washington DC, Sep 22-26

Tracking Social Media with Google Spreadsheets – Part 1

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 TwitterThere 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=social&utm_campaign=blog) 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.

Stay tuned for Part 2

Next time, 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.

In the meantime, let me know if this is helpful, or if you have other ways to streamline your social media workflow. The comments are yours!

 

Jim Gianoglio

About Jim Gianoglio

Jim Gianoglio is our Digital Analytics Engineer. He works with implementation, analysis of Google Analytics, and spearheads the LunaMetrics Google Analytics seminars across the country. Want to see him in action? He'll be heading our Google Analytics training in Los Angeles. Before succumbing to the siren song of analytics, he led the SEO campaigns of Fortune 500 companies in the insurance, retail and CPG industries. Things you didn’t know about Jim: he runs marathons, photographs weddings and has done voiceovers for TV commercials.

http://www.lunametrics.com/blog/2012/02/14/tracking-social-media-google-spreadsheets-1/

11 Responses to “Tracking Social Media with Google Spreadsheets – Part 1”

this is absolutley a great tool, i shared it with coleagues and they were thrilled as I am. it makes link tagging so easy, and it helps maintain consistency in the tagging process.
thanks a lot.

YoraY says:

NOBEL PRIZE!!!!!!!!!!!!!!!!!!!!!
real professional solution.

Assaf says:

Great tool!
You might consider work with the chrome extension with the bit.ly and pre-configured sets as well.

Versha says:

Thanks a lot. Great stuff. This works fine with facebook, but when i want to share a tagged link in linkedin, the url reverts back to original one and looses its tags. Would you have a solution for this?

Hi Versha -

Thanks for your comment!

I can’t seem to replicate what you’re experiencing. Can you share more details?

I know LinkedIn changes the shortened link from bit.ly to its own custom shortener (lnkd.in/7YKmg3 for example) but it still redirects to the final URL with the campaign parameters.

Jim

Versha says:

Hi Jim,

Thanks for your reply. You understood the problem correctly. My URL is coming without the campaign parameters. Is it anything related to linkedin paid account? I share the link via share update. Is that the way to do?

Thanks once again for exending your help.
Versha

Hi Versha -

I don’t have a LinkedIn paid account, so unfortunately I can’t test that out to see if that’s what is causing the problem.

I was able to share the link via the “Share an update” box at the top and the campaign parameters came through just fine for me.

Jim,

Great post and great doc you’ve created there. I’m working with a similar doc that pulls in and creates bit.ly links out of long URLs, but my biggest challenge now is figuring out how to create a stats tracking column that can pull in bit.ly click stats to show how many people have clicked on each bit.ly link.

I know bit.ly makes this info public, is there any way to automatically pull that into the spreadsheet so I can have short url generation AND stats on the same doc?

ENRICO says:

Sorry but there is a problem in the spreadsheet “Behind the Scenes”, could you check please?

Hi Enrico -

Did you put your Bit.ly username and API key on the “Technical Details” sheet? It won’t work without that.

Andrew says:

Hi Jim,

Great article and spreadsheet. Thanks for sharing.

I’m curious about how you check your individual post stats though? Do you have any advice on how to download some kind of CSV using this method?

Also, am I right to assume the spreadsheet at the method you explained tracking stats above via the google analytics url builder are different beasts and should not be done together?

Cheers