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, and shorten it with (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 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:

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 API.

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

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 shortening operation. For more details, check out the Google spreadsheet function list.

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

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 is a Manager for the Analytics & Insight department. He works with implementation, analysis and training of Google Analytics and Google Tag Manager. Before focusing on 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’s biked from Pittsburgh to Washington DC in 41 hours, roasts coffee beans and has done voiceovers for TV commercials.

  • 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

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

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

  • Versha

    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 to its own custom shortener ( for example) but it still redirects to the final URL with the campaign parameters.


  • Versha

    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.

  • 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 links out of long URLs, but my biggest challenge now is figuring out how to create a stats tracking column that can pull in click stats to show how many people have clicked on each link.

    I know 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?


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

  • Hi Enrico –

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

  • Andrew

    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?


  • angiephillips

    Hi Jim, looks like a great tool but I’m not getting any results. This article/comments are 3 yrs old. Does it need update. I get ERROR: In FIND evaluation, cannot find ” within ‘500MISSING_ARG_ACCESS_TOKEN’.

    • Jim Gianoglio

      The Bitly API has been updated since I wrote this. ApiKey authentication (which is what this Google Sheet uses) is deprecated in favor of OAuth requests. However, it still works (for how long is anyone’s guess).

      The confusion is that you have to use your API Key found here – – instead of the Generic Access Token from this page –

      Once you plug in your API Key the Google Sheet should work!

Contact Us.

Follow Us



We'll get back to you
in ONE business day.
Our Locations
THE FOUNDRY [map] LunaMetrics

24 S. 18th Street
Suite 100

Pittsburgh, PA 15203


4115 N. Ravenswood
Suite 101
Chicago, IL 60613


2100 Manchester Rd.
Building C, Suite 1750
Wheaton, IL 60187