Measuring Twitter Engagement

How do you know if you are making an impact in your social media efforts?

Sometimes it can feel like you are just whistling in the wind. Knowing that people are listening is the encouragement you need to keep going, and knowing which of your activities produces results is the feedback you need in order to experiement and improve.

Measuring Twitter Success

Many people mistake their follower or friend count with engagement, but in fact a friend count, while a nice ego boost, is not a great metric. Each service has “activities”, and how much of this activity you initiate is a better way of working out how well you are doing at engaging your friends or audience. With Twitter it is all about tweets (Twitter messages), but the reactions in the form of clicks, replies and retweets is more important than just quantity.

Well, some celebrities have tens of thousands of Twitter followers. But if they are just broadcasting, with few conversations, then they are not necessarily engaging their audience. It could be a great “feed”, which would be evidenced by a great number of click-throughs, but without interaction there is little added benefit over RSS.

Ideally what you want is to have a good mix of conversations, in the form of messages and replies, for your links to be clicked on or produce further discussion, and for your Tweets to be re-tweeted.

Twitter Metric Monitoring

The obvious first place to look is your follower growth and retention. If your follower count is going down or standing still then you have a problem. People are voting with their feet as it were. There is a service called “Qwitter” that would tell you when people unfollow but it doesn’t seem to be working recently.

To measure your Twitter metrics beyond audience size you need to use online tools, but thankfully Twitter makes this easy via their programming API and 3rd party online services such as RetweetRank. I actually prefer to look through Twitter Search for @replies in most cases so I can read the replies rather than get a raw count, however.

Twitter Search is also valuable in seeing “mentions” – finding how often a word associated you or your organization is used, and if used in a complaint perhaps you can help the person out before the issue festers.

Counting Twitter Link Clicks

For click counting you have any number of options but my personal fave at the time of writing is http://cli.gs which despite the funny name is a very easy to use URL-shortening service with an analytical twist. Sign up and as well as shortening your URLs and saving precious characters out of the 140 you are allowed in a tweet, it will count how many times your shorter web address has been clicked. The more clicks you get, the more interesting you know your tweet was.

What’s the Point?

This is not about scoring points. The numbers are just an indication of progress, a clue to if you are making any impact. Providing the numbers improve then you know what you are doing is getting some sort of result and that you are getting better at it.

Fact is though, it is often easy to latch on to numbers as being the whole aim, rather than one dashboard for your journey toward your goal. Knowing your actual goal and how social media fits in, is the important part.

Are you wanting to increase your visibility? Sales? Donations? Business or career opportunities? Are you just hanging out? If you are using a service to merely entertain and inform yourself then all this is meaningless, but if you have a business or project reason then these metrics need to lead to those business goals, so the consequences of engagement need to be measured also.

How to Use Excel to Identify Your Best Customers

This entry is part 2 of 3 in the series Excel Customer Data Mining

In the previous article I showed how you can measure visitor value and engagement using Excel. Now I would like to show you how you can identify your best, most profitable customers, also using Excel.

Now immediately you might think it is obvious who your most profitable customer is. Isn’t it the one who spends the most?

No!

First, someone could buy your biggest ticket item and then go away never to return. Are they a better customer than one who buys a smaller item each month? This is why we need to look at the data!

We also need to remind ourselves that revenue is not the same as profit. We will get into working out profitable from unprofitable customers and/or product later.

Customers can often talk up their future spending plans which can turn a sales persons head. Ideally what we want to discover who has shown good customer evidence through their actions and an evidence-based prediction of propensity to buy in future.

For simplicity sake in these examples I will be using revenue because I don’t want to distract you from the valuable knowledge that you can gain by getting involved in just getting hold of some of this data. If you have profit data then wherever you see revenue or spend, substitute that with profit or margin.

Discover Your RFM

The technique I am showing you here is called RFM Analysis. RFM stands for

Recency – How recently the customer purchased
Frequency – How often the customer purchases
Monetary Value (or sometimes Margin) – How much they spend

Each of these measures is an important indicator of how good a customer they are, but together you get a potent scoring metric that can tell you a great deal about where (or who) your profit is coming from.

Set Up Your Customer List

If you use a fully-featured ecommerce or CRM system then this information might be readily available to export or in reports. Otherwise you need to talk to your tame techie or do some inputting. For this example I set up a spreadsheet to simulate the output from a shopping cart system.

Customer ID or Name
Date of last purchase
Number of purchases for time frame (in my example I chose 365 days)
Total spend over time frame (again, for my spreadsheet I chose 365 days)

To create the first column I used the formula =A2+1 to create a sequence from 1 to 100.
For the second column I wanted a random date from the last 365 days, so used the formula =TODAY()-INT(RAND()*365) which translates to “Todat minus random days from 1 to 365″.
The third and fourth columns are also randon, 1-20 and 1-2000 respectively. This means the maximum spend would be $2,000 between 1 and 20 purchases, with minimum $1 from 1 purchase.

I then copied the cells and pasted into a new sheet using “Paste Special” as I only wanted the values, not the formulas, just in case it recalculated as I worked with the data.

This gave me 100 “customers” with associated purchase activity.

Now we need to “score” them!

Essentially scoring your customers is as easy as sorting and adding some additional numbers:

Sort by “Last Purchase” descending.
Add a column marked “R” and give the top 20% a score of 5, the next 4, and so on. (Do not use a formula, just copy and paste).
Copy the column of scores so you can paste in the next steps.
Add columns for “F” and “M”.
Sort by “Purchases” descending, score in “F” (paste the scores), then sort by “Total Spend” descending and place the “M” scores.
Now when you sort the spreadsheet back to ordered by “Customer” you will see each customer has the appropriate values for Recency, Frequency and Monetary Value. All we need to do is add those numbers together to get their RFM scores.
Create a new column marked RFM and add up their R+F+M using something like the formula =E2+F2+G2 and paste that in for each customer row.
Now you should be able to sort by the RFM column descending to get the people with the highest score at the top.

Take a look at the top of your results.

Those at the top have spent a lot, recently and often. Your best customers.

Now look at the bottom.

These folks might have spent a lot, but a year ago and not bought anything since, or perhaps made one small purchase more recently. As you can see further up, small purchases made often can make a brilliant customer, and we would want to attract more of them if we can service them without too much hassle and cost.

Using these Results

If you do nothing else, it is a good idea to split the list up into Quintiles/Fifths, the top 20% are your Gold Customers, the next Silver, then Bronze. The next 20-40% of your customers need looking at to learn where you have gone wrong.

Already we have data that can provide us insights.

We have identified two types of customer that just looking at revenue wouldn’t have revealed. High ticket customers who go away, and new, low ticket customers. Those latter type might turn into brilliant customers but it is too early to tell, but they will not grow into top customers without our attention. The former type of customer likely went away because of either poor customer service or because of some other bad experience – ie, we dropped the ball.

If we only looked at $ we would have thought the former were fine and might have considered latter not worthy of much attention compared to the high-rollers!

In the next part of this series we will look at two additional metrics that can tell you a great deal about how profitable these customers really are, Engagement and Support.

Introduction to Using Excel to Calculate Your Website Visitor Value Metrics

This entry is part 1 of 3 in the series Excel Customer Data Mining

Do you know how much each visitor to your website is worth? If you buy advertising, do you know how much money you can spend and still break even? Do you know the long term value of your visitors?

We all want as many visitors as possible to our websites, it almost seems like crazy talk to imply otherwise, but in actual fact for a business website, some visitors are more valuable than others, and some visitors could be even costing you money.

Knowing your average visitor value is therefore essential, and breaking visitor value down further could be a very profitable thing to do.

Even more important is if you are paying for this traffic. You want every spend on your web marketing to work as hard as possible, both to know how much you can invest, and also to trim the fat.

There are custom and commercial tools out there, but in fact, you can do a lot with plain old Microsoft Excel!

Using Excel for Basic Visitor Value

First you need to know how many visitors you get each month, and how much money your website makes.

Do you sell products or services? Maybe you show advertising?

Take your monthly visitors and sales total and plug these numbers into the spreadsheet. In the final column the revenue is divided by the visitors to give your visitor value.

So in this example, 30,000 visitors bringing in a revenue of $10,000 provides an average visitor value of $0.33.

How is this useful? Well, with this in mind, if you are currently paying $0.25 per click in Google Adwords then you can up it and still break even.

Drilling Down Visitor Value

This is not really fair though. As I say in the introduction, not every traffic source is created equal.

Say, for example, you notify your audience about a product in three ways:

Blog Feed
Email
Twitter

In the example shown, Email generates the most revenue, with feed coming second, and Twitter looking pathetic at only $20, BUT, look at the per-visitor value!

This is because the Twitter message converted at a much higher rate, that is a higher percentage of Twitter visitors bought. We will look at conversions in a moment. For now just know the message and channel can have a profound impact on visitor value, even a negative effect, as well as the total revenue where email clearly wins in this case.

Responsives Versus Subscribers

The obvious conclusion you would draw from the above example is “wow, I had better grow my Twitter followers”, but WAIT!

Yes, there is an indication that those Twitter clickers are worth $6.67 on average, but it does not tell you how much your Twitter followers are worth! At this point you just know how much your responsive followers are worth from one test.

To know how responsive your various fans are, we can check the Click Through Rate, or CTR.

CTR is calculated by setting the cell format as percentage using the % button, then taking the visitors and dividing it by the total messages sent (if you have 300 followers then one tweet is sent to 300 people, but if you send it twice then the number of messages sent doubles).

In my case here the formula is =(C7/B7) (where / means divide).

These results can give you additional insights. You can see Feed subscribers are incredibly responsive – a full half clicked through, were as email and twitter followers were not in the same league.

You can not take the results from just one sample too seriously. You need to measure repeatedly for a start. Secondly, if you have 10 followers in total and 3 clicked through, then your click and conversion rates seem high, but we could be dealing with too small numbers to know if your results are statistically significant.

Statistical relevance is too much math for my meager brain, but there are spreadsheets available that will help you do the calculations.

What you do know is that if you can get more of your followers, subscribers or readers to respond, then you will increase their value to you, and while some sources are less responsive than others, their propensity to buy can be very different … that is where conversion rates come in.

Measuring Conversions

Using a tool such as Google Analytics you can automatically measure conversions using “Goals”, but you can do some broad calculations using Excel of course.

Taking the example given above, we can add another couple of columns to reveal a better picture of what went on with that promotion.

Conversions are calculated much like CTR, but CR is percentage of visitors who buy, therefore Sales divided by Visitors presented as percentage format.

So in our example you can see that while this particular sites Feed readers are highly responsive, they are clickers not buyers, whereas this site owner has a small amount of raving fans who were primed to buy following on Twitter.

Why might this be? Well, Twitter is often used for conversation. There might have been many messages leading up to the offer being made preparing those who were interested that something special was going on sale, or perhaps an existing product heavily discounted. The Twitter and email recipients might have been hovering over their keyboard ready, while the blog readers while still interested left it too late and clicked over after the offer had gone away!

Going for Gold

So now we know that not all visitors are equal, but what about customers? Earlier I said that some visitors or customers could in fact be costing you money, how do we know? And what about the long term value?

In the next part of this series I will show you how to split your customers into groups for increased profitability and so you know who to lavish your super special customer service on! We will later also work out who are the best customers long term, and who you might want to stop buying from you.

Subscribe so you don’t miss anything!

How to Make 2009 More Productive by Doing Less

As I am writing to you today on 1st January I thought I would share with you how I plan to make 2009 a more productive year for myself than 2008, and how you can too.

Culling Time Wasters

The first place to look in becoming more productive is where you waste the most time.

My definition of “waste” in this context is activities that do not add much value but take considerable time. Spending time with my family is not a waste of time because we get value from it, while solitary “Tower Defense” web based game playing is likely adding zero value and just eating up precious time.

In the closing months of 2008 I took careful note of where my time was going. I worked out there were several areas I was spending time that could have been made more efficient. Keeping a time diary, even just scratched onto a scrap of paper or scribbled on a whiteboard can help you uncover where your time is going.

I found my biggest time leak was unscheduled interruptions.

For 2009 I will save time with:

Turning off the instant messager - IM conversations are fun and valuable, but not when at the expense of work
Scheduling telephone/skype calls – I am no longer going to be available on demand, the telephone will be on answer machine and skype will be off unless I have set aside to be available.
Smarter email – I just spent hours clearing my inbox down to zero from around 1,800 by setting up folders, rules, unsubscribing from unnecessary lists and ruthlessly deleting – Inspired by @BillT on Twitter
Focused Social Media time – Talking of Twitter, I found my rhythm with social media and now have worked out a social media schedule (which was stretched over the holidays, but you have to bend the rules sometimes!)

Spam and Unwanted Email

Email turns out to be a big part of my day. I don’t want to go the Tim Ferris route; I take pride in answering my own email and having good turnaround times. So rather than outsourcing, autoresponder or support ticket system, I am working on reducing my inbox clutter as much as possible.

A big load on my inbox is newsletters. For many services or products you have to supply an email address, and of course you do not know which will turn out to send you junk and which will be good, so you can’t use a temporary address in case it is the latter. I am taking the advice of my friend Damian who has a catch-all email forwarding set up on one of his domains and signs up to each with a unique email address in the form “list-name@domain.com”. If I get spam to this unique address I will know where it came from.

My email list from address is changing, as is my contact form. I am also moving my family and friends email to a different account so work is split from home. Each source of email will be isolated and easier to prioritize.

Another way I am handling email spam or junk messages is when a newsletter asks for my first name I am using a particular variation of my name, so any messages sent to “Hi _______” will be fitered off to a folder before I even see it, so I can go through them at my leisure, if at all.

Work to Your Rhythm

I have discovered I am most productive for certain tasks at certain points in the day. The problem is I have actually been working against these patterns.

My normal routine was to get up, make coffee, check my email, then work through anything the email demanded, followed by my task list for each day, with phone calls scheduled according to the other parties convenience and taking account the appropriate time zone math. Of course my body and mind were telling me that was a bad way to organize things.

Check your mood, motivation and output when performing certain tasks during the day.

When are you most creative?
When are you better at communicating?
When does your energy droop?
When are you easily distracted?
When can you find “the zone” most easily?

I found between certain hours I could output hundreds of words of writing, while others it was a struggle. At some times I could communicate easily and fluently, while others I wanted to hide from the phone. Logic escaped me at certain times and I just wanted to sleep, whereas at others I could solve problems that seemed impossible hours ago. Telephone calls, as mentioned earlier, saw me working at 2am because the other party was based in a far away time zone. Red Bull and coffee can only go so far.

Just by juggling my schedule I will get far more done.

Your body and mind will tell you when you should do certain things, listen to it!

Got Suggestions?

How can we make our 2009 more productive? Please share your tips, thoughts, experiences, ideas and comments …