Why brands need social media monitoring and analytics

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Last week, I was asked to give a short (10 minute) talk about social media monitoring and analytics. I’m not exactly what you might call an expert in this field, but I have been around the loop with this as part of some work I did a couple of years ago, when the company I work for started to make better use of social media, although I’ve since moved on to other activities.

In researching for my talk, I decided to reach out via my own social network(s) and have to say that I was pretty amazed by the response. Within a few minutes of tweeting, I had responses that gave me some fantastic angles to cover.

Looking for inspiration for 10min. presentation on social media monitoring... any insight appreciated - no vendor pitches please
@markwilsonit
Mark Wilson

Even though I can’t share the presentation (I used some slides that were “loaned” to me by others), I can write about what I presented, so here goes – with special thanks to David Gentle (@DaveGentle), Graeme Goulden and Jim Millen at Fujitsu; Brent Ozar (@BrentO), Consultant and Author at Brent Ozar PLF, LLC; Louise Parker (@LouParker), Sales Director at Sentiment Metrics; and Eileen Brown (@EileenB), Author and Social Media Consultant at Amastra – as well as to the many other people who offered assistance but whose work I haven’t “borrowed” here!

“Social working”

Many organisations are experimenting with (or even gaining real value from) social media and are rapidly trying to discover ways to exploit these new channels before their competitors do. The challenge here is that the steps required to gain value from social media go against conventional wisdom: In business, we want things to be routine and predictable. Social media, however, is organic in nature and is driven by people’s passions and interests.

Integrating the virtual and physical worlds

One way to consider social networks is that we need to look after customers in the virtual world, just as we would in a physical context.

For example, in a restaurant, management touch tables and customer check-backs are an integral part of the service in the UK and elsewhere. They are used to see that customers are happy with their meal, ideally before any simmering concerns become full-on dissatisfaction. In the past, restaurant customers didn’t talk to each other. But with social media, they do, and bad experiences snowball fast.  In real life, you can hear your customers complaining and shuffling toward the door. The same happens in social media too.

Smart organisations can use this to their advantage and build brand advocacy. For example, one upset Mum sent a tweet after she had taken her four-year-old child to McDonald’s to complete his Marvel Set figurines only to receive a Littlest Pet Shop character (another type of toy) instead.  Instead of sending a tweet to apologise, McDonald’s took the initiative to send a real mail and titled it “you tweeted, we listened”, including the missing Marvel figure with a handwritten note. Cool right?  Especially for McDonalds as that mum had a popular blog and she wrote about it leading to 50,000 page views a month and, even better, the story was included in the updated version of Charlene Li and Josh Bernoff (Forrester Research)’s book Groundswell.

Some organisations are particularly smart and they integrate customer feedback into their loyalty programmes and CRM systems. In an example, which Eileen Brown wrote about on her ZDnet blog, a customer tweeted to say how much she loved cornbread when staying at Gaylord hotels (a US hotel chain).  The company offered her some, but by then she’d already left. A few weeks later and she stayed at another one of their hotels and, a short while after checkin, some cornbread was delivered to her room – without even requesting it.  She then blogged about the great customer service, closing the communication loop and making it clear that she was a satisfied customer.

What’s the alternative to this? Well, sadly, things don’t always work out so well. These pictures have been around for a few years now but became Internet memes. Thankfully the names of the restaurant chains involved are not so easy to find…

 

Losing sight of the individual

The next part of my talk was borrowed from Chris Brown and Louise Parker’s SoConBuzz talk where Chris highlights a hotel on Trip Advisor which ranked really highly (9 out of 1068 hotels in London) but for which some very negative reviews really stand out – this is the social media challenge – we need to be able to identify these individual responses, this individual dissatisfaction and react at the tactical level to address specific concerns.

 

Four phases to actionable insight

Louise continues her SoConBuzz presentation by talking about four phases to actionable insight. Watch the video for the full details but, in short:

  1. Listen – monitor content and actively respond to it.
  2. Categorise – filter out the noise and focus on what’s important.
  3. Analyse – analyse conversation threads and identify leading topics.
  4. Act – drive actionable insight and add real value.

The value of social media

The real value of social media is not so much as another channel to market but as a method to understand what customers are saying about you – and to engage. But, more than that, by integrating with other data it’s possible to provide a holistic view of the organisation, enabling both tactical and strategic change to take place.

My take on social media monitoring and analytics

It’s all very well quoting chunks of other people’s work but I did mention at the head of this post I’ve had some experience in this area as, two years ago, I began an experiment with corporate blogging, backed up with other social media channels. Before launch, we wanted to measure a baseline so that we could measure the impact of our efforts but we found that enterprise tools (e.g. Radian 6) were expensive and free alternatives lack functionality, granularity, or just don’t really join up. Most importantly, any social media programme needs commitment of resources – agencies can only go so far.

I’ve found that the majority of social media case studies are from business to consumer (B2C) organisations. In the business to business (B2B) space, customer conversations tend to be fewer, and are less likely to be online – and even for B2C organisations there will be differences between sectors.

Critically, for marketing teams it’s important to understand that social media is different to other marketing communications channels – it shouldn’t be led by campaigns with time and budget limitations – social media endures after these have ended – and it’s a conversation, not a monologue.

tl;dr

Social networks are an important element of customer interaction and integrating virtual and physical worlds is crucial. In summary, organisations need to:

  1. Understand what it is they want to measure – and there will be a human element to this too as humour, irony and sarcasm are still challenges for many tool sets.
  2. Make sure that they can take action with what they find or else the exercise becomes just an expensive way to collect a lot of information, and possibly to leave customers feeling ignored (in my experience @VirginTrains is selective in the tweets that it responds to, in stark contrast to the other rail operator on my journey, which is @LondonMidland).

In addition, beware that social media has a global reach – we found it practically impossible to monitor social media for a single geographic region – and make sure you know how you will cope in the event of a crisis (there are some well-known examples in the form of Eurostar and the Ford Motor Company).

And finally…

If you’re a manager who is struggling to “get” what social media is about (you’re probably not reading this blog), but consider this tweet:

RT @ Blogging about the real challenge with enterprise social http://t.co/geYQMJSr < agree - social business is human business!
@Annemcx
Anne McCrossan

Euan Semple (@Euan)’s post is short and to the point but it makes an important point: to understand social networks you have to experience them – “feel the fear”.

Useful Links: June 2012

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

A list of items I’ve come across recently that I found potentially useful, interesting, or just plain funny:

  • UK train times – UK train times and detailed timetable information (platforms, non-advertised stops, etc.) – taken from the National Rail API used by many apps
  • TheBookYard – UK Apple spare parts and refurb specialist
  • Swivl – Personal cameraman for hands free video (spotted at Raspberry Jam thanks to Leon Cych)

Choosing a new mid-range hardtail mountain bike for single-track trails

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Last week I went mountain biking with some of the guys in the running club that I belong to (quite a few of the runners are duo- or tri-athletes… not me though!). I had a fantastic evening blasting around the woods and even managed to get both wheels off the ground on one of the jumps – not bad considering I was on my fourteen year old Trek 830 and very out of practice.

My bike has served me well but it has no suspension and is fitted with road tyres – these days it’s better suited to a quick trip to the shops, or towing my son around a country park on a connector bar so I decided to buy something with a little more… va va voom… for summer evenings on cross country trails.

The trouble is, I don’t know a huge amount about mountain bikes, so I needed to learn – and learn fast. This post is sharing some of what I discovered.

  1. First up, was to determine my budget – it’s no good comparing bikes that are completely different.  I also decided that I wanted a 26″ bike – I’m told 29ers are great for going fast – and if you’re tall – but I’m not! Also, hardtail (front suspension only) seemed appropriate for my type of riding – full suss (full suspension) bikes are really for more hardcore guys taking on board some serious downhill runs.
  2. Nothing beats testing bikes before committing and I found that most of the shops near me were really helpful and happy for me to have a short ride – either in the car park/up and down the street or even further, leaving behind my driving license and car keys as security.  I’m not sure they would have been so happy if I took the bike up to the woods for a real test, but it’s enough to get some idea.
  3. With one exception (Evans Cycles in Milton Keynes), where the sales guy spoke so quickly I struggled to keep up (and talked at me, rather than listening), I was able to pick up some good tips – Trek’s Milton Keynes store were particularly helpful, as were Roy Pink in Newport Pagnell and Leisure Lakes in Daventry – and I got some good advice to help me through the minefield of component names.
  4. For Shimano (I’m told that) Deore is the starting point for mid-range gear, with SLX, XT, and XTR rising up the spec sheet. Similarly, for components with a number, higher is better (e.g. a Shimano M525 hub is better than an M475). I was also told that there’s a argument to get higher-spec shifters because dérailleurs get smashed and replaced… shifters tend not to. None of the bikes I seriously considered had SRAM components (although I did have a quick look at bikes from Boardman, Whyte and Cube that do) so I’m not sure how they compare, but I did have to weigh up the Avid Elixir brakes against the Shimanos – all I could do there was take advice – and any of them will stop me a lot more quickly than the calliper brakes on my current bike!
  5. As for gears, 30-speed seems to be the norm these days, with most of the bikes I considered running a Shimano HG62-10 11-36 cassette (11 is the number of teeth on the smallest cog, 36 on the largest) with a 42/32/24 crank seeming to be fairly standard up front).
  6. I’m sure I should have learned about handlebars, stems, headsets and grips – but I didn’t. I stuck with the essentials – and what was comfortable/looked good (although I’ll probably look to replace the pedals pretty quickly).

After a few days of Internet trawling and shop visits, my shortlist was:

All of these are good 26″ mountain bikes and all weigh about the same, although the Talon and the Spesh are, arguably, a level down from the other four. My heart fancied the Cannondale, but my head said Trek so I decided to score the bikes against one another using a simple system whereby I assigned points for each component – for example, Rock Shox Recon Silver TK forks got 1 point, Silver with Remote Lockout got 2, and Rock Shox Reba Gold RL got 3. Similarly, Shimano SLX shifters scored more than Deores, etc. Then, add up the totals and see how the bikes compare.

After that assessment the decision was clear – the Trek 6500 is head and shoulders above the rest (especially the Cannondale) when it comes to spec. and I read that the frame/fork combination is the most important selection – other components can be upgraded later. On that front Trek offers a limited lifetime warranty on its frames, so they obviously believe in the quality (and I’ve been really happy with my 830).

I’ve now ordered my Trek 6500 and, hopefully, it should be with me in a few days time.

Now I can’t wait pick up the new bike and get out onto some decent single track…

Short takes: Raspberry Jam (#RaspberryJam) and Milton Keynes Geek Night (#MKGN)

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

This week, aside from struggling with the culture shock of getting back to work after a fortnight of Internet abstinence, and getting very angry with Microsoft, I spent my evenings at two fantastic community events. Both of them deserve a lot more space on the blog but I’m short of time right now, so a teeny overview will have to suffice.

Raspberry Jam

Complete with scones (really!), Raspberry Jam was a fantastic evening of RaspberryPi fans talking about some of the things that they are up to and hosted by Alan O’Donohoe (@teknoteacher):

  • Genevieve Smith-Nunes (@pegleggen) talked about the HackDay she’s organising at her school with 250 Year 9 students who will be building “something” with RaspberryPi (and the website is a constantly moving feast as its the kids who are building it). She’s also teaching Scratch to kids in Year 1 and 2 (my children are Foundation and Year 2, so this is very interesting to me).

(great quote by the way – “nobody’s the teacher; everyone’s the teacher; and we’re all students”).

  • Neil Ford (@NeilCFord) talked about Portable Pi – a project for taking Raspberry Pis on field trips to provide a portable website for kids to upload research data where there is no mobile phone reception. The shopping list for a Portable Pi is available on Amazon (although I can’t see the battery on there right now).
  • Neil also mentioned the Young Rewired State Festival of Code that’s happening in August – teaching kids to code, hoping to create the next Mark Zuckerbergs, and to keep them in the UK.
  • There was a demo of the Acorn RISC OS running on a Raspberry Pi. I’d forgotten just how advanced it was, back in the days before Windows… what’s more, it is tiny (6MB) and includes BASIC. Great for starting to code…
  • John Bevan (@bevangelist) showed us Mozilla Thimble – a really simple tool to teach people how to publish on the web and one of a wider suite of tools that Mozilla is creating.

Plus there was loads of opportunity for networking, snacks, beer, soft drinks and more. Hosted at the Mozilla Space in London (a great venue for like-minded open source-oriented people), the organisers are looking for more RaspberryJam events to be created across London, the south-east (and presumably further)… jump on the #RaspberryJam hashtag for more.

You can also watch the recording (filmed on an iPhone using a rather cool tracking tripod head called a Swivl) on YouTube:

MK Geek Night

Regular readers will know that, about once a month, I head down to Digital Surrey, which usually has some great speakers on topics of interest to “digital” (media/marketing/tech) types, like me. With the Digital Bristol and Digital Berkshire spin-offs, I considered starting a Digital MK or a Digital Buckinghamshire but I simply don’t have the time (or the contacts). That’s why I was so excited to see Richard Wiggins and David Hughes announce Milton Keynes Geek Night.

Wow! 190 people in a community arts centre in a converted bus station; two big speakers; three 5-minute lightning talks and some one-minute pitches. What a great evening.

  • Jon Hicks (@HicksDesign) spoke about iconography, using the redesign of Skype’s emoticons as a case study. Who knew there was so much to designing icons? I had an inkling there would be, and it’s pretty fascinating stuff (for geeks).
  • Kate Kenyon (@Kate_Kenyon) told us, in just 5 minutes, how to slash content and create better websites that work for users, not just company politics.
  • James Parker (@MrJamesParker) gave us some tips on how Twitter helped him to become a better designer.  I won’t leave you hanging – and they are not just for designers either – they are:
    1. Follow people relevant to you (not just celebrities).
    2. Don’ be a passive user – get involved in the conversation.
    3. Make friends – contacts are everything.
    4. Follow me.
  • Code Club (@CodeClub) were there – there are 120 schools and 1436 volunteers signed up now (maybe more this morning) to teach our children to write code with curriculum changes and after-school clubs. It’s a pity I don’t cut code for a living as this is a great initiative to get involved in.
  • Brendan Dawes  (@BrendanDawes) gave a whacky but enlightening talk on low-tech hacks and making “things” from “stuff” (that description simply doesn’t do the talk credit – I’ll write more in another post, I hope)
  • And then there were the one minute pitches for employment opportunities, user groups, special interests, etc.

Fantasmagorical!

The next event is scheduled for 20 September. Full speaker line-up is yet to be announced, but includes Relly Annett-Baker (@RellyAB) talking on content strategy and, based on the inaugural event, I have high hopes that Richard and David will find more great speakers. Follow @MKGeekNight on Twitter for more details.

Proof that Milton Keynes has geeks and about 200 more roundabouts than Old Street. #MKGN
@dvlngl
Lawrence Archard

Microsoft (finally) gets its mobility act together – but cuts loose early adopters of the Windows Phone OS

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Last night, Microsoft announced plans for the next version of its Windows Phone operating system – Windows Phone 8. In many ways it was a great announcement. Windows smartphones will have a “common core” with desktop Windows. The Windows ecosystem is converging, maybe a little late, but I said Windows 8 could be a turning point for Microsoft and Windows Phone seems to be a part of that.

Tom Warren had a great post up almost immediately at the Verge on what what was announced for Windows Phone 8. But Tom also highlighted, as did Simon Bisson at ZDNet, that there was a sting in the tail. A very big sting. And its target is the very people who adopted Windows Phone 7 – arguably the community that Microsoft needs in order to make Windows Phone 8 a success.

Current generation Windows Phone (Mango) devices will not be upgradable to Windows Phone 8 (Apollo).

There will be an update for Windows Phone 7, taking it to 7.8 (extending Microsoft’s marketing abuse of version numbers) but it’s little more than a few cosmetic changes. Windows Phone 7 apps will run on Windows Phone 8 but not vice versa (exceptions being those that are not compiled to take advantage of new Windows Phone 8 functionality, or Siverlight apps for Windows Phone, themselves sidelined for XAML/C#). Given that we’re starting out from a fairly limited pool of apps, that pool is likely to get smaller as apps are updated; and it pretty much kills the current Windows Phone market stone dead.

I switched to Windows Phone because I thought it was fresh, different, and because Microsoft positioned it as the future of their smartphone story. The big reset happened when Windows Mobile was killed off two years ago in favour of Windows Phone. I thought (still do think) that iOS has become stale, its UI is tired and has become clunky in places (in fairness, so is Windows Phone at times) but at least the aging iPhone 3GS that my employer provides runs the latest version of iOS. Meanwhile, Android is fragmented and has its own problems around security and an incoherent tablet story (don’t write it off just yet though). I didn’t buy an HTC HD2 because I knew that Windows Mobile 6.5 devices wouldn’t be upgradable to Windows Phone 7 (that much was already known long before Windows 7 appeared). Instead, I waited for Nokia to release some (semi-) decent hardware for Windows Phone and, just 7 months later, they made it obsolete – and I simply don’t buy that they were unaware of Microsoft’s roadmap for Windows Phone. I know that technology adoption is a risky business but I expect my device to at least last as long as a standard mobile phone contract (2 years) and my Lumia 800 has a limited future ahead of it.

So my few months old Nokia Lumia 800 is EOL'ed in a few months. Gee thanks Microsoft.
@jonhoneyball
Jon Honeyball
Several people making the very valid point that Microsoft is rewarding its early adopters by cutting them adrift. Goodwill evaporates.
@bazzacollins
Barry Collins

Some say that users will always complain: either that there’s no legacy support; or that legacy support is bloating the OS – but a published roadmap that allows consumers to make informed choices (together with N-1 version support) should really be the minimum acceptable standard.

Microsoft owned the roadmap. Microsoft controlled the reference architecture. Microsoft prevented OEMs from increasing the hardware capabilities of Windows Phone devices (screen resolution, adding multiple cores, etc.) and now Microsoft is preventing even recent hardware from running its latest phone OS. In short, Microsoft is screwing its early adopters.

I really do hope that all those consumers that Microsoft and Nokia have been (knowingly) marketing dead-end Lumia devices too of late have an opportunity to force support for Windows Phone 7-class hardware to continue until Windows 9 comes along (giving users 2-3 years of current device support). Unfortunately, I don’t think that will happen (unless there are some very smart lawyers involved).

One thing’s for sure. This Windows Phone user will be thinking very, very carefully before committing to any future mobile device purchases running Windows. Once bitten, twice shy.

@ +1 And brand trust will become more important as more and more personal stuff is inside your phone
@caro_milanesi
carolina milanesi

Microsoft Surface: my attempt to cut through the hype

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Over the last 24 hours, I’ve watched the hype build about Microsoft’s mysterious mystery event (thank goodness I missed the build-up last week as I was still on holiday in France…), watched the news break, and watched everyone either go ooooh, ahhhh, or hrmmm…

I couldn’t stand it any more and decided that I too should weigh in with my comments on some of the comments I’ve seen about Microsoft Surface. I may even come back and add to this list over the next few days:

  • Microsoft is too late to the tablet game: Maybe they are. There’s the iPad, and then there’s… well, no-one really. But there’s still plenty to play for. Maybe back in 2007 someone asked for a tablet and got a table instead? Seriously, the device we previously knew as Surface was rebranded PixelSense last year, but we don’t seem to get the PixelSense screen tech in the Surface tablets.
  • It looks good: it does – really good. But we don’t yet know enough about the Surface hardware – if this is underpowered, or battery life is poor, or the screen is unresponsive, then it will fail, just like all the other iPad wannabes.
  • The keyboard in the cover is a gimmick/great idea/an admission that soft keyboards don’t work: horses for courses, I’d say – there are times when I use my iPad keyboard and times when I elect for a physical version – this way we get both.
  • Microsoft is cutting OEM’s throats? Are they really? My view (personally, not as an employee) is that it’s saying “come on guys, this is what can be done when you put your mind to it – stop letting Apple run away with the tablet market and design something that’s just as good, now that we have (finally) got an operating system (nearly) ready for you”. But there is an issue when (presumably) Microsoft doesn’t charge itself $85 per device for a copy of Windows.
  • This will undermine Ultrabook sales: perhaps it will, but however big the marketing push, they would have been niche anyway. Do IT Managers really have money to spend on “sexy” laptops when functional ones cost half as much? It might have killed off the Windows tablet market though, except that Surface will only be available from Microsoft Stores and online, which limits its availability somewhat, and makes it a consumer-only purchase. OEMs don’t really need to worry too much (sure, PC sales are in decline… but there are many factors behind that and mobile devices have been expected to surpass PC for a while now). And for those of us outside the US… we might not even get a sniff.
  • Ah, so it’s for consumers, so it puts Microsoft back in the game when it comes to consumerisation? Hrm Not really. On BYOD, there seems to be a shift towards choose your own device (CYOD) – i.e. we’ll give you more choice, maybe even let you contribute to have a better device, but it needs to run Windows. CIOs do need to re-architect applications to embrace cloud, mobility, big data and consumerisation – but that’s a big ask and it’s not happening overnight. Until then there’s life in Windows 7 (and 8) for a while. And laptops/tablets are only one side of the story; Microsoft is still struggling for smartphone market share…
  • Two versions of Windows, both on Surface devices, one that runs Windows RT and one for Windows 8 Pro – what gives? On this I agree, it will confuse the market. Maybe the x86 hardware should have been a reference platform for OEMs to sell in the business market, with ARM to consumers?
  • Analysts say… Really. There is some really good insight there, seriously. But now what do CIOs say? How about: where will this help me to deliver business value; what’s the impact on the rest of the IT environment; how can I transition to become a competitive (internal) IT service provider who no longer cares about devices and operating systems? Having said that, I think Forrester’s Sarah Rotman Epps is correct to highlight issues with the way Windows is marketed and sold, and IDC’s Crawford Del Prete (@Craw) is right on the money:
MSFT Surface must win the hearts of consumers before the minds of CIOs. Good start #surface
@Craw
Crawford Del Prete

For some time now, we (geeks, tech journalists and IT types like me) have lambasted Microsoft for being unimaginative, lacking innovation, and for being late to market. This time they have something bold, exciting and that could really shake up the way that PCs look and feel. They’ve also kept it secret and created a buzz (albeit a little too early, some might say) perhaps a bit like another company that seems to get credit for everything it does…

Let’s give the Surface a chance to get out of the door before we write it off, hey? It could actually be really good.

Now, what are they doing about smartphones?

Creating a dashboard using a SharePoint list (part 4): bringing it all together

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

This week has been a bit of a SharePoint-fest as I’ve walked through the stages of creating my SharePoint dashboard. For obvious reasons, I can’t share the version I use at work, so I’ve created a mockup using the SharePoint site that comes with my Office 365 subscription. Feel free to take a look…

The three posts that explain how I built this dashboard can be found below:

As the code examples I gave use quite a lot of SharePoint formulas for calculated values, I thought it might also help to link to Microsoft’s formulas and functions documentation as well as their examples of common SharePoint formulas.

Right, that’s enough SharePoint for now… next week will be back to the usual mix of tech, marketing, photography and whatever else I stumble across…

Creating a dashboard using a SharePoint list (part 3): indicating several states of progress

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

Yesterday’s post looked at using ticks/crosses on my SharePoint dashboard to track progress on activities. I was quite happy with this, but my manager wanted to show progress, rather than a simple binary done/not done – and I wanted it to be visual, not text-based.

We settled on a system using a series of circles with various stages of “filling-in” (none, quarter, half, three-quarters, full) to show how far an activity had progressed, and I amended the code that I used for the ticks and crosses.

Using the same principle as with the ticks and crosses, this time the font changed from Wingdings to Arial Unicode MS, which includes the appropriate shapes under the block elements and geometric shapes subrange:

First of all, I changed my column to indicate progress from a Yes/No to a Choice with radio buttons for Not Started; Started; Additional Information Required; Almost Complete; Complete and Not Applicable.

Next I needed to adapt the formula used to calculate the correct HTML code for the display column by nesting repeated iterations of

IF([Task Progress]=”Started”,”

?
“,”
?
“)

Eventually this ended up as:

=IF([Task Progress]=”Complete”,”

?
“,IF([Task Progress]=”Started”,”
?
“,IF([Task Progress]=”Additional Information Required”,”
?
“,IF([Task Progress]=”Almost Complete”,”
?
“,IF([Task Progress]=”Not Applicable”,”
“,”
?
“)))))

The code is not pretty, but it seems to work, and the result is something like this:

Creating a dashboard using a SharePoint list (part 2): tick/cross status indicators

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

In my last post, I wrote about how I used a script I found on the Internet and a couple of calculated columns to show a Red/Amber/Green (RAG) status on a list in SharePoint. I also wanted to clearly indicate which items in my list were complete, and which were still ongoing.

My inspiration for this came from Chris Bell’s posts on displaying tick marks in HTML, combined with the same code that converts my text RAG Status to HTML.

This time, I converted a Yes/No Checkbox (actually, it’s TRUE/FALSE) to HTML code for a tick or a cross in another column, using the following formula:

=IF([Task Completed]=TRUE,”

ü
“,”
û
“)

The end result is something like this:

It needs the Wingdings font to be installed, but everyone who needs to access this list is using a Windows PC – feels like a bit of a fudge but it works for now… and can always be replaced with a graphic (as I did for the KPI indicators on the RAG status).

Creating a dashboard using a SharePoint list (part 1): colour coded status indicators

This content is 13 years old. I don't routinely update old blog posts as they are only intended to represent a view at a particular point in time. Please be warned that the information here may be out of date.

I’ve been having some fun over the last couple of weeks, developing a dashboard in SharePoint to track a number of activities that I’m co-ordinating.

Within our company there are some people who criticise SharePoint as a platform, largely because of some of the confusing messaging around some of the collaboration features and the way in which it gets mis-used as a document store (complete with folders!) but, whilst it can certainly be infuriating at times, I have worked on quite a few sites over the years where it’s struck me just how powerful the concept of a list is (I should know that really – I remember spending quite a bit of time discussing linked lists in my Computer Studies degree, but that was 20 years ago…).

I wanted to create a list with a red/amber/green status for each item – but I didn’t just want the words – I wanted colour to jump out of the page and say – this is the stuff that’s on track… and this isn’t…

Some calculated values and a script to generate HTML from text

A bit of digging around on the ‘net turned up a method that works for me – and the details are in Anjali Bharadwa’s post on colour coding custom lists and tasks:

Firstly, I  interrogate the contents of my text column (in my case this is called RAG Status, with pre-defined choices or Red, Amber and Green) and convert that to an HTML colour code in another column (called Color). The formula is  as follows:

=IF([RAG Status]=”Red”,”red”,IF([RAG Status]=”Amber”,”orange”,”green”))

I created a page that includes a Web Part that displays a view on my list and used a hidden Content Editor Web Part below this, containing Anjali’s script (there’s an alternative script available from Christophe Humbert at PathToSharePoint but the download link wasn’t working on the day I needed it…), to read this and to generate HTML in another column with a calculated value (called RAG).  For SharePoint 2007, the script can be included within the HTML source for the webpart but, for SharePoint 2010, it’s necessary to link to a separate file (Adam Preston explains how to do this in his post on inserting JavaScript into a Content editor Web Part) in the This time, the formula is:

=”

n

Using a particular font is risky (how do you know that everyone has it?) but in this case I could be sure that all of my audience would be running our corporate build with Windows and Internet Explorer. I later changed the formula to use a different symbol:

=”

?

Sorting the RAG status

One annoyance with this approach was that the list was sorted based on my RAG Status column, but I wanted Red-Amber-Green and the alphabetical order was Amber-Green-Red. My fix for this was to change the RAG Status choices to include a number so they become: (1) Red; (2) Amber; and (3) Green.

I also tweaked the calculated value for the Color column:

=IF([RAG Status]=”(1) Red”,”red”,IF([RAG Status]=”(2) Amber”,”orange”,”green”))

With this change in place, my column ordering works – and it gave me the opportunity to change the way I represent the status too…

Alternative visualisations

Path to SharePoint has some ideas for alternative visualisations for colour-coding (and a follow-up post). I particularly like the KPI indicator version, which is based on some of SharePoint’s standard images:

_layouts/images/kpidefault-0.gif
_layouts/images/kpidefault-1.gif
_layouts/images/kpidefault-2.gif

To use these, I changed the formula for my RAG column to:

=”

This change also means that the Color column is redundant, although I’ve left it in place in case I want to go back to it later.

What next

So that’s my RAG status sorted. In the next post, I’ll explain how I used this method to show ticks and crosses to indicate activity completeness.