Video tutorial: Social media analytics for hyperlocal publishers - setting up a KPI spreadsheet

The very thought of analytics can send people running for the hills (or burying heads in the sand), but analytics don’t need to be complicated, expensive or time-consuming. In my video tutorial I show you how to get six great insights from online data. In this blog, I will show you in detail how to set up a simple spreadsheet to capture that data.

But first, let’s take a step back and look at what the term “analytics” actually means. In social media, analytics is a way of transforming your social media data (e.g. number of followers, interactions, etc) into usable, actionable information. When analysing your data, you are looking for meaningful patterns that will then generate insights.

A good, actionable insight might be that you had a peak in new Facebook fans when you ran a video interview with a local celebrity (so maybe you could run more of those) or that the majority of your website referrals come from Twitter (so prioritise marketing on that platform).

Insights are important because, without them, you have no idea if the strategy you’re following is working. Even if you’re a person who prefers the fly-by-the-seat-of-your-pants, planning-is-for-wimps approach to business, that is still a strategy (of sorts). And not testing your strategy is equivalent to going on a diet without weighing yourself. Or putting your money in stocks and keeping your fingers crossed.

Learn to love Excel

It’s worth taking the time to set up a spreadsheet (or your own preferred system) to track and visualise your data. You can use Microsoft Excel, Google Sheets or any other spreadsheet software. If you like you can use this KPI template.

There are a gazillion products and services out there offering you the ultimate social media dashboard or monitoring templates. But it’s easy to find yourself using some data from one tool and further data from another. Even when I’m using one or more paid solutions, I still like to have a simple master spreadsheet to keep all my key data in one place.

This KPI spreadsheet, along with your content calendar, are the two vital templates you need to manage your social media marketing. A KPI – or key performance indicator – is a way of measuring whether or not your business goals are successful. If you don’t apply a measurement, or metric, to your goals, then it’s easier to let them slide.

You probably already know about the importance of setting SMART goals. As a quick reminder, these goals are:

  • Specific
  • Measurable
  • Agreed
  • Realistic
  • Time-based

Creating and filling out a KPI spreadsheet will help you set this SMART goals. 

Make your KPIs social

A social KPI is a key performance indicator that specifically measures social media activity. The more accurately your social media activity ties into your actual business goals, the more chance you have of getting a good ROI (return on investment) – a good return, good value, out all that time and effort you’re putting in.

As a hyperlocal publisher within the Destination Local project, you’ll already have a list of business goals and KPIs. (N.B. For publishers not part of the project, Nesta will share a simple template in the near future, based on what project participants have used to determine and monitor their impact and progress). Take this list and against each goal and KPI, write down a social KPI to support it.  Think of the timeline you want to plan for (e.g. six months or a year) and set hypothetical targets for the end of that period. 

For example, if you have a business goal/ KPI to be making at least £1,000 a month from advertising by the end of the next six months, you could set a social KPI to double your monthly website referrals every month via social media. That increase in referrals will make your publication more attractive to advertisers.

Another business goal might be to build a loyal local community around your online publication. A relevant social KPI could be to increase your Facebook engagement rate. Organic rates tend to be quite low, so 3% might be a reasonable target (you may want to consider Facebook Advertising as it would up this rate considerably).

It’s not always easy to come up with the hypothetical targets six months or a year into the future. You may need to look at a number of different variables, e.g.: how your competitors are doing, what your advertisers have been promised, what you need to achieve to break even, etc. The important thing is to put something down – you need a number to aim for.

Get monitoring

On an agreed date each month (usually at the end, or dated from whenever you choose to start), fill in the monthly columns with actual data. The target at the end of the six-month period will always be visible and, after a couple of months, you should be able to see at a glance whether or not your social media strategy is on track.

This process will enable you to answer plenty of useful questions, such as:

  • Is your social media content effective in driving traffic to your website?
  • Is your audience engaging at the levels you would expect?
  • If not, how can you change things? 
  • If yes, what types of social media content are working best?
  • Is one social platform more effective at driving traffic than the others?
  • If so, how can you get even more value from that platform?

Watch the video tutorial to learn about six great insights analytics can give you.

A guide to social media analytics for hyperlocal publishers from Nesta UK on Vimeo.

There’s a summary of the insights, with details of where to find your data, in my ‘part 2’ blog.

Good luck – and let me know how you get on!

You can find me on Twitter @JemimaG.


Jemima Gibbons

Jemima is a social media strategist. She works with agencies as a freelance consultant, and directly with SMEs through the Social Media Launch Pack. Her book on social media and busine…