main - custom

Stats entertainment: creating a KPI dashboard

This post is part of a series of articles about presenting and interpreting statistics for business and marketing. The previous post in the series was “Stats entertainment: working with percentages

What is a KPI dashboard?

KPI stands for Key Performance Indicator, and KPIs comprise whatever data you consider to be useful in measuring business performance, for example sales figures, marketing spend or any other aspect of your business or your industry that you care to measure.

A KPI dashboard is a place where you can keep this data so that you can refer back to it whenever you need to compare one set of data to another.

KPI dashboards can be created using a variety of applications, but personally, I favour using Excel (or any other spreadsheet program), as it’s easy to set up, to use and to keep updated; and any graphs you create can easily be copied into other applications, like Word, if you want to use them in a report. However, if you can afford it, you might prefer to cut out all the work and purchase specialist business intelligence software, such as idashboards, which might make life a lot easier.

You can use your KPI dashboard to measure a whole host of data in graphs and charts, but there’s no hard and fast rule as to what exactly you should be measuring. This really boils down to what you think would be worth tracking, and may be very specific to your industry. But since we’re on the subject, here are a few suggestions:

  • sales revenue, which can be from a variety of income sources
  • number of units sold
  • number of clients or partners joining
  • number of clients or partners leaving
  • customer segmenting (e.g. by industry, age, location, etc)
  • lifetime value of customers or partners
  • newsletter subscriptions
  • cost per acquisition (i.e. amount spent on marketing to acquire each new customer)
  • longevity of client relationships
  • website or blog stats
  • your company’s share value
  • sources of new customers (e.g. advertising, direct mail, search engines etc)
  • value to the business of each source of new customers

Information sources

You’ll be collating your data from a number of sources, and it’s important to make sure that you start off as you mean to go on – by creating and maintaining the right sort of admin systems, which enable you to measure useful data (efficient administration is a whole other subject, however, so I’ll save it for another post).

Your information sources may include the following:

  • your bookkeeping/accounts system
  • your customer database
  • your website statistics package
  • your newsletter broadcasting software
  • application forms
  • questionnaires/customer surveys

and if you’re including external data in your KPI dashboard – such as stats about your industry – then no doubt you’ll use the Internet a lot, too.

Basic tips for presenting statistics

Your stats should tell a story, so spend a little time in advance on planning your KPI dashboard and the story you want it to tell. It’s much easier to plan it all out first (you could use a mind mapping tool, such as Freemind – it’s very easy to use, and it’s free) than it is to set up your KPI dashboard and then discover, months down the line, that it’s not telling the story you want it to tell.

Think about the best layout for your dashboard; will you have a new worksheet for each month, with a separate sheet for year-on-year stats? Or will you have a new worksheet for each set of data you’re measuring? It’s really worth thinking about this in advance.

Decide how often you’re going to update your dashboard, and try to stick to your plan. Update too regularly, and you’re making a lot of work for yourself; update it too infrequently, and your story will often be out of date. The frequency of your updates will really depend on a lot of factors, including the way your business works, what it is your business does, and how busy you are; so have a good think in advance about how often, realistically, you’ll be updating your dashboard.

Remember: as the creator of your dashboard, you will know at a glance what each set of stats represents – but if you share your dashboard with other people, they may struggle to understand your graphs and charts without a little help. So, make sure your story tells itself clearly:

  • use a title for each of your graphs or charts, to explain what the graph or chart is measuring, the time period it relates to, etc
  • clearly label the x- and y-axes in graphs; for example, period of time covered, number of sales, etc
  • use a legend, especially if you’re using a graph or chart comparing one set of data against another (for example, September sales compared to October sales)
  • use data labels if there’s enough space; in Excel, hovering your mouse over a data series will tell you its value, but if you’re copying and pasting your charts into Word – or printing them – this trick won’t work, and your readers will be left guessing.

Creating your KPI dashboard

Now that you’ve planned and prepared where your story is coming from (your data sources) and how you’re going to tell it (your mind map), your Excel skills are going to come into play (by the way – if you’re not very confident using Excel, you’ll find Carl Nixon’s newsletter very handy; Carl gives away free Excel tips in each issue, so I recommend subscribing).

Create your new spreadsheet, and in this create as many worksheets as you’re going to need (you can always add more later, or delete any that you don’t need). Make sure you rename each worksheet, to avoid confusion later (numbered sheets very quickly become confusing).

Try to work on one sheet at a time; you’re far less likely to confuse yourself! If you’ve decided to split your spreadsheet into months, work on one month at a time; if you’re using a new worksheet for each type of data, again work on each data set at a time. This has the added bonus that once the first sheet is fully set up, you can copy some of its contents into any related sheets if you need to, and tweak your data so that it’s correct for that sheet; this’ll save you loads of time (the AutoSum function is also very useful here).

An example of a KPI dashboard

I’m not going to go into heaps of detail here about how to use Excel; there are far better sources of information for that (see the link to Carl Nixon’s website, above); however, I’m going to be writing some further posts in this series, each of which will cover a different type of graph or chart (this is when the fun really starts), and these posts will include a few Excel tips – so keep checking back if you think they’ll be useful to you.

So I’ll end this post now with an example of how a finished KPI dashboard, organised by topic, might look (these are screen grabs; if you’d like a copy of the original spreadsheet, including all the source data, please get in touch).

This KPI dashboard is based on made-up data for a fictional company, the details of which are as follows:

  • The company’s revenue comes from selling memberships to an online cookery club with access to exclusive features and downloads for members
  • A three-month membership costs £30
  • A six-month membership costs £50
  • An annual membership costs £80
  • The site uses AdSense to generate advertising revenue
  • There is a recipe book for sale on the “store” area of the site, which is sold at £10 to non-members and £5 to members
  • The company has been running since June 2007

Click the image to enlarge it and watch the animation, or request a copy of the spreadsheet

So that’s all the basics covered: why stats aren’t scary, how to work out percentages, and how to create a KPI dashboard.  In the rest of the Stats entertainment series I’ll be looking at the different types of graphs and charts you can use, when it’s appropriate to use them (or not), and how to create them in Excel.  I hope you’ve found the series useful so far – please leave a comment to let me know how you’re getting on.

, , , , ,

6 Responses to Stats entertainment: creating a KPI dashboard

  1. infocaptor 2nd October 2008 at 1:33 pm #

    you could also try infocaptor for creating dashboards. It is very easy to connect to excel or any database

  2. debbidoo 2nd October 2008 at 1:39 pm #

    Hi, infocaptor,

    Please post a link and a bit of information about your product – saves us all having to Google you ;)

    Thanks

    Debs

  3. Worf 3rd October 2008 at 8:35 pm #

    If you are using excel to store your business data you might consider this great tool called Prism by SiSense,
    it allows you to create dashboards and reports from excel in no time

    you can find it in
    http://www.sisense.com

  4. infocaptor 13th October 2008 at 5:52 pm #

    Hi Debs,
    Usually due to all the comment spam plugins I have stopped putting urls where I comment.

    Here is the URL for your readers http://www.infocaptor.com

  5. debbidoo 14th October 2008 at 1:32 pm #

    Eek, and that’s exactly what happened! Never mind, I rescued your comment – and thanks for stopping by again to share your URL, much appreciated :)

  6. ngo 16th January 2009 at 6:19 am #

    Tks very much for your useful post.

    Other sources about KPI, I think also useful

    http://www.humanresources.hrvinet.com/human-resources-kpi/

    rgs

Leave a Reply

Website by debbidoo/WP Websites Wales.