Snapshot Of Your Social Media Presence [Part 5]: Dashboard On A Spreadsheet

Social Media Analytics On A Spreadsheet Measure your social media marketing on a spreadsheet? Why bother spending time on this when we have all kinds of analysis tools available to us?

We finally get to the fifth and final installment of the series “Snapshot Of Your Social Media Presence” and to be honest, I expect you to question me with that weird look on your face but I promise to explain why I think you should consider keeping your own numbers in a spreadsheet.

Before I continue, here are the previous posts on this series in case you missed them:

So Why?

There are 4 main reasons:

  • Full visibility. There is no tool that will give you everything you want in one place or the capabilities to customize at this level.
  • You can establish points in time to track data and compare.
  • You can manipulate the data however you want and customize to your needs.
  • Budget.

Establish Metrics

There is no right or wrong here, the decision on what to measure depends only on the business objectives. As I mentioned a couple of times during the series, we are not measuring ROI but online presence. Social media presents a great opportunity for an inbound marketing approach that will allow you to attract your target market, conversion becomes a 2nd step in the equation.

In this example we’ll focus on the following metrics as we have seen throughout the series:

  • The Hub – Which is most likely to be your blog or website. Traffic is a must while rankings such as Alexa, Compete and others are less relevant to your bottom line but still a metric to measure your online presence.
  • Content Performance – Key element if you attract your prospect through content marketing. This will let visualize what kind of content your reader consumes and how it performs in the different social platforms.
  • Network – The size, growth rate and demographics of your network are important depending on your business objectives.
  • Impact – You are already putting your creation in front of people, is it being shared? How much and where? Is this impact intended or unintended? Is it positive or negative? One step closer to conversion.

Let’s Get Jamming

Not that I push MS Excel, in fact, I have almost completely replaced my MS Office usage for Google Docs but when it comes to charts on a spreadsheet, Excel provides with a lot more flexibility. Feel free to use any other product.

You will want to create only one workbook and separate each section into sheets.

Analyzing Social Media On Excel

Next, it is important to determine a starting point, if you’re starting today or if you have been working on an existing strategy. Also critical to establish points in time, both in frequency and specific dates, perhaps once a month on the 5th day of the following month so you allow a few days to have solid digits. Tools are usually not real-time.

Now let’s go into each section of your workbook and establish the data you will want to track on a monthly basis.

Site Traffic

General site traffic. Easiest and cheapest way is to run Google Analytics on your site.

  • Visits
  • Pageviews
  • Average Time on site
  • Bounce Rate

Content Performance

A little bit more complex because we’re analyzing pieces of content you publish. Data will usually come from different sources.

  • Pagerank
  • Pageviews
  • PostRank Engagement Points
  • Post Comments
  • Retweets
  • Facebook Shares
  • Delicious Bookmarks
  • Click-throughs

You can go on here as much as you want including Diggs, average time or bounce rate for each post. My advice is to keep it as simple and relevant as you can or it can easily become a hassle.

Network

Whatever you consider community around your brand in whatever social platforms, maybe Facebook is not important for your business but Linkedin is.

  • Twitter followers
  • Facebook fans
  • RSS Subscriptions
  • Email Subscriptions

Impact

How you determine if your brand or company is achieving the desired impact and how to measure it is really up to you. It will also be harder depending on the data you want to track.

  • Influence
  • Reach
  • Sentiment
  • Traffic from social sites

There are no rules here, if you want to keep your Klout Score or your Sentiment from SocialMention then include them too.

How About Some Screenshots

Here are a couple of examples so you can get an idea, the first is for content performance in which you should get data from different sources, in this case I’m going with the criteria I mention above.

Content Performance Table and Chart

Traffic from social sites

Traffic from social sites

Or your network growth divided by platform.

Social Network Growth

Final Thought

Is it a lot of work? maybe. Is it necessary if you already get this data from separate tools? you decide that. One thing I can tell you is that you need to have full visibility of these digits if you pretend to get any results regardless of the media used.

I did a lot of traditional media in my previous business, you track results on each magazine or TV network, each day of the week or time of the day, each spot or ad. Why would you treat social media different? And if your answer is that social media is free, let me tell you with all due respect that… you are dead wrong.

There are a couple of things I left out in this series like setting monthly goals or tracking some basic SEO performance, we’ll get into that in future posts.

Your Turn…

What is your method? do you focus on different metrics? do you have any suggestions? share your thoughts in the comments. Also, it would be awesome if you can tell me if this was helpful in any way.

  • Anonymous
  • Now this is a Spreadsheet 😉 It's nice to have all that info in one place.. very helpful for me on my “small” scale version lol

    This is just crazy.

  • I'm jealous of your ridiculous traffic….:(

  • Well , the view of the passage is totally correct ,your details is really reasonable and you guy give us valuable informative post, I totally agree the standpoint of upstairs. I often surfing on this forum when I m free and I find there are so much good information we can learn in this forum! http://www.roomcl-ean.com/

  • Thanks Francisco, for showing folks how this all works. Great series !

    Now you hit me where I live in this part of the series. I don't take the time like you do to promote great stuff like this but then I work in the background using metric evidence tools for social graphing our client campaigns. The screenshot pic below from one of our FB demo app albums below is where we start to extract client data to Google docs and Excel, as you can see we have charting on hits but drill far deeper in geo and demographics which I cannot start to emphasize more, numbers are the sizzle of social for campaigns and marketing and what sells these services incredibly well.

    The demo app attached to photo is located on the albums associated page using Facebooks “Single sign on” from our server side. The app delivers video, chat and friend select all in one window and logs every hit on any image or piece of rich media. If you go to the app, rest assured we see when you click on a tab in the player such as “Target your Customer”.

    For a great preview of the app without even authorizing it you can just hit the tab labelled “12Flat” at the top of the page profile and you're in.

    Facebooks new #SDK has really made this technology easy to implement and let us bring in rich content like never before, on our terms and fb guideline.

    We deal in very high traffic applications on these sites and being able to deliver weekly analytics to clients is a must or we'd not be in business.

    App fan page – http://apps.facebook.com/twelveflatog/

    http://www.facebook.com/photo.php?pid=211813&id

  • how to measure social media is always tough task… because social media traffic also comes in direct way to sales as well as inquiry forms

  • Is what you're saying that some social media networks provide less traffic but also yield better sales because they refer a more relevant audience to your niche? If this is what you mean then yes I agree to that.

  • This is all very interesting, but is way above my head.

  • I have enjoyed your graphs. It really makes me stop and take a hard look at what I am doing.

  • Anonymous

    Thank you for posting this great blog. The subject is intriguing to me. I hope you post more about it soon. It gave me good deeper insight.

  • Social media plays an important role in the marketing field. Social media networks provide less traffic but also yield better sales because they refer a more relevant audience to your site.

  • This is very very useful and I will try and build something similar. You are right it all needs to be tracked so that you can see what value you are getting from the effort. Social Media might not have a cost that you pay to someone but it does require you invest time which of course has an opportunity cost.

  • I promise to explain why I think you should consider keeping your own numbers in a spreadsheet.

  • You can establish points in time to track data and compare.