Monitoring Social Media Metrics


Lisa’s out of town for Canada Day, so I get the keys to the blog! I also have the keys to her home, so stick around and maybe I’ll post embarrassing pictures from her fridge.

A couple weeks back, Lisa showed us how to make a social media plan and I wanted to dive a little deeper into the “Set Your Metrics” section. As a business owner, you need to determine how to quantify buzz and as a service provider, social media marketers need to prove results. Below is a simple method for monitoring your social media metrics, which can help both parties prove real social media success.

Quick note: I loved Marty Weintraub’s post about How to Build a Reputation Monitoring Dashboard and Duct Tape Marketing’s similar post on how to use Netvibes to track social media mentions. I stole inspiration from them after their original posts, but I wanted something more quantifiable. Our clients have to prove return to their executives and investors and spreadsheets make that process much easier. Everyone likes to see pretty charts that demonstrate growth over time! So, I hacked together this little process using a combination of Yahoo Pipes, various RSS feeds and Google Docs. Keep in mind there’s no limitation to what you can do if you use your imagination.

To get started, you’ll need a Google account and you’ll need to know what you’re tracking. Are you a local business that wants to keep tabs on your reviews? Grab a location-specific feed from Yelp. Are you trying to build engagement on your Twitter account? Grab a feed that tracks your retweets or any #FollowFriday hash tags that mention you. Do you want to track product mentions in videos? Subscribe to an advanced YouTube search feed.

Whatever you want to track, locate and build your feeds. Then drop those into a big list somewhere and we’ll come back to them in a minute.

Now, go sign into Google Docs, if you aren’t worried about the Borg. Create a spreadsheet for your client, or if you are the client, create a spreadsheet for your brand, product, or competitor. How you segment everything is entirely up to you.

Once you’ve created the spreadsheet, add tabs for each social media metric you’re tracking. For example, Yelp reviews, Twitter retweets, YouTube mentions, etc. Now you need to familiarize yourself with Google Doc’s functions and filters. If that looks intimidating, don’t worry, there’s really just a handful that you need to know about for social media monitoring, specifically:

=ImportFeed (“put your feed’s url here”, “items title”,true,15)

This is probably going to be the most common function you use. You’re defining the feed, the “items title,” whether to display feed headers and how many entries should be included on the spreadsheet. Other common item types include:

  • items url
  • items created
  • items summary

There are a number of item types, but those listed above cover the majority of the social media items you’ll want to monitor.

The first item type listed above pulls the feed title. If you’re tracking Yelp reviews, this will display the user’s name and rating. If you’re tracking tweets, this will display the contents of the tweet. And, if you’re tracking YouTube videos, this will display the title of the video. To play with this, just copy and paste each of the following examples into a cell on your spreadsheet:

=ImportFeed ("", "items title", true, 15)

=ImportFeed ("", "items title", true, 15)

=ImportFeed ("", "items title", true, 15)

Moving forward, look at the other aforementioned item types. Besides “items title” there were also, “items url,” “items created,” and “items summary.” They’re pretty straight forward, “items url” pulls the URL of the individual entry. This means you’ll see the individual URL for each Yelp Review, tweet, video, etc. The third, “items created,” pulls the published date for each item. And the fourth, “items summary” pulls a summary, description or blurb for each item much like the contents of a regular old RSS feed that you display in your reader.

Now that you know what you can display, setup the feeds for each tab and determine what you want to see.

Change Colors in Social Media DocsYou can keep your spreadsheet as simple as this, just tracking mentions as they fly by. Or, if you’re reporting specific results, you can refine your feeds based on sentiment, online reputation management alerts, customer service inquiries, etc. Wil Reynolds over at Seer Interactive had an excellent idea to track link opportunities in delicious. As an agency or in-house social media marketer, you can take this a step further by privately bookmarking important action items. Then you subscribe to that feed in your spreadsheet under an URGENT ALERTS tab. You could also do something as simple as “change colors with rules” in Google Docs. You can make a rule that highlights Yelp reviews with less than a 4.0 rating or any other social media metrics you want to be notified of.

Now that you’ve setup alerts, share the spreadsheet with your client or boss and they can log in at anytime to see if there are any fires that need to be put out. This is less invasive than email alerts and you have historical data to track progress over time.

This isn’t a bullet proof solution, but it’s good enough to track engagement, popularity, brand mentions, your competitors, keywords and more.

Your Comments

  • Marty Weintraub

    Very nice extrapolation on the various sources you cited, and thanks for the hat-tip. I love doing things without overpaying for designer methods that can be nearly reproduced at low or no cost. This is a terrific contribution to that sort of thinking.

  • Rhea Drysdale

    Thanks Marty! I keep referencing your ORM dashboard, I know I put it in our ORM Guide, too. It demonstrates the power of DIY monitoring and reporting whether it’s for SEO, social media or reputation management. Frankly, I don’t like how limiting some of the big name tools are. There’s always a feature missing that makes my job harder than it should be. My biggest gripe is always about how the information is displayed. It’s just easier to hack something together in iGoogle or Google Docs. Then again, maybe we’re just control freaks? ;)

  • Therese

    This is great – I am going to give this a try now!

  • Frank Meeuwsen

    Interesting take on social media monitoring. But how do you set up the “historical data to track progress over time”? Because your method only takes the last 15 items from an RSS-feed and displays them. Unless you copy and paste in a timely manner the importFeed-query to new cells, you will not gather historical data over time automatically. Or am I missing something here?

  • Nathan Hangen

    Great tips with the important details included.

    How long should this take to implement?

  • Philipp Sauber

    Very interesting but the formulas don’t work if I just copy them in an Google spread sheet. Even if I delete the spaces.
    Any hint what I’m doing wrong?

  • Rhea Drysdale

    Philipp – try it now. Michael Gray hit me up this morning with the same problem. WordPress was using smart quotes on the code, so I had to embed them in a code tag. You should be able to copy/paste everything perfectly now. Let me know how it goes! :)

  • Yawn Webmaster!

    Oh my goodness, talk about giving the client a complete data-overload!! I am trying to work out what is great about this post and I simply can’t. I can only fathom that the authors probably got off on the technical aspects of being able to make a Google doc dynamically update particular feeds based on criteria x,y,z.

    Quite frankly there are lots of tools out there that will do this for you. Which makes me think that this post should have been called, “how an agency can monitor social media networks for free”. Sure it’s a little bit more wordy, but at least it’s explicit.

    You hit the nail on the head when you say businesses want to know how “determine how to quantify buzz” but all you do is “determine the quantity of the buzz” and that’s pretty useless to a brand manager or someone that wants to action social media data.

    Therefore, as usual, I’d begin at the other end of this Social Media stuff. Start with the client and find out what is important to them.

    For example, a company that has just completed internal research about its customers views on a particular issue, for example “Does customer think we are green”, and finds that customers are happy with the companies green credentials, does not need to see dialogue about a few chaps saying they are not happy with company X’s stance on green. Actually if you go and show then data on this issue and they reply via the web it might actually do more harm than good.

    A challenge of Social Media is not monitoring the data, but making that data relevant and actionable to the business that’s requesting the service. And quite frankly most Social Media services don’t go anywhere near that because a) they don’t build a deep and meaningful relationship with their customers and b) they don’t have acccess to the departments which could benefit from that degree of insights, c) they linger on free.

    Look forward to the next installment :)

  • Rhea Drysdale

    Yawn – would love to check out any paid tools you’ve found helpful for monitoring these kinds of results with the level of detail I need. We track very specific things for every client and that varies depending on the service. We also track online reputation problems and link building. I need a tool that’s flexible enough to do all of that. We’ll eventually build an internal solution if I can’t find the perfect app elsewhere. If you’ve got it, do share, would love to check it out!

  • Philipp Sauber

    @Rhea now it works! great stuff, thanks a lot, will definitely use this for monitoring my company.

  • jlbraaten

    I’ve been doing other things like using Google Reader and social mention to track my social media metrics. This is a really really neat way to use Google Docs to track it all automatically. Great for storage!

  • Yawn Webmaster!

    An SEOs tools are his secret weapons, even those that are publicly available.

  • Rhea Drysdale

    It isn’t the tool, but how the tool is used that makes the difference. And, we’re talking about monitoring mentions not aggressive SEO tactics. Enjoy your top secret tool!

  • Yawn Webmaster!


  • Dave

    Check out WP-Stats-Dashboard for social metrics monitoring on your WordPress dashboard.