Friday, April 06, 2012

How to generate customer purchase cohorts from uCommerce data

I've had a couple of people ask how they can create customer purchase cohorts from their uCommerce data since my last post so here's a quick script.

Depending on how you've setup your uCommerce store, the customer ids might be different so instead of using customer id. I would use the email address of the customer personally as the identifier as this means you'll be able to analyse those customers who have chosen to check out anonymously

Here's the SQL to output the data in a format suitable for www.quickcohort.com.

WITH Actions (FirstAction, LastAction, UniqueId)
AS (
	SELECT min(dateadd(dd, datediff(dd, 0, o.CompletedDate), 0))
		 , max(dateadd(dd, datediff(dd, 0, o.CompletedDate), 0))
		 , ltrim(rtrim(LOWER(cc.EmailAddress)))
	FROM [uCommerce_PurchaseOrder] o LEFT JOIN uCommerce_Customer cc ON cc.CustomerId = o.CustomerId
	GROUP BY ltrim(rtrim(LOWER(cc.EmailAddress)))
)
SELECT a.[FirstAction]
	 , a.[LastAction]
	 , count(a.[UniqueId]) AS [CountOfCustomers]
FROM
	Actions a
GROUP BY a.[FirstAction]
	   , a.[LastAction]
HAVING 
	min(dateadd(dd, datediff(dd, 0, a.[FirstAction]), 0)) IS NOT NULL
ORDER BY a.[FirstAction]
	   , a.[LastAction]

GO

Not using uCommerce as your e-commerce provider? Let me know and I'll knock up a script for you.

How to generate customer purchase cohorts from uCommerce data

Useful Links:            Comments [0]  Trackback

Tags: ASP.Net | Cohorts | eCommerce | uCommerce

 Wednesday, February 22, 2012

How to generate the data needed for a cohort chart- cohort analysis Part 3

Sean Ronan pointed out on twitter that "what" the columns on www.quickcohort.com are which is a good point so I thought I would fill in the gaps a little. The data for a cohort is fairly simple and can be as granular as you decide but the columns needed are:

1. First Action Date/Time
2. Most Recent Action Date/Time
3. Count of customers which have this First/Most Recent Action Date/Times

Most of the time you can strip the time part from the date/time (especially if you're looking at the data on a month basis) but the tricky part is getting the count of users within each date grouping. You can't just select min/max dates as you need the data grouped by your unique customer identifier. If you're running SQL Server 2005+ then you've got the benefit of Common Table Expressions.

For this example, I've assumed a simple order table structure which contains a Customer Reference (CustomerId) and an Order Date (OrderDate). You could however use any date and identifier which groups actions together e.g. ProfileId and LastLoginDate.

SQL 2005 or later

WITH Actions (FirstAction, LastAction, UniqueId)
AS (
	SELECT min(dateadd(dd, datediff(dd, 0, o.[OrderDate]), 0))
		 , max(dateadd(dd, datediff(dd, 0, o.[OrderDate]), 0))
		 , o.[CustomerId]
	FROM Orders o
	GROUP BY CustomerId
)
SELECT a.[FirstAction]
	 , a.[LastAction]
	 , count(a.[UniqueId]) AS [CountOfCustomers]
FROM
	Actions a
GROUP BY a.[FirstAction]
	   , a.[LastAction]
ORDER BY a.[FirstAction]
	   , a.[LastAction]

Otherwise, I think you'll need to write something using temporary tables e.g.:

Pre SQL 2005

CREATE TABLE #Actions(
	FirstAction SMALLDATETIME, 
	LastAction SMALLDATETIME, 
	UniqueId INT
)
INSERT INTO #Actions
(
  FirstAction
 ,LastAction
 ,UniqueId
)
SELECT min(dateadd(dd, datediff(dd, 0, o.[OrderDate]), 0))
	 , max(dateadd(dd, datediff(dd, 0, o.[OrderDate]), 0))
	 , o.[CustomerId]
FROM #Orders o
GROUP BY CustomerId

SELECT a.[FirstAction]
	 , a.[LastAction]
	 , count(a.[UniqueId]) AS [CountOfCustomers]
FROM
	#Actions a
GROUP BY a.[FirstAction]
	   , a.[LastAction]
ORDER BY a.[FirstAction]
	   , a.[LastAction]

DROP TABLE #Actions

This should then generate some data that looks like this:

Which you should just be able to drop into www.quickcohort.com. I've not written a version for MySQL as I suspect someone far better at MySQL will be able to pop something together but the pre SQL 2005 script should work.

How to generate the data needed for a cohort chart- cohort analysis Part 3

Useful Links:            Comments [0]  Trackback

Tags: Analysis | Business | Business Start-up Advice | Client | Cohorts | The Site Doctor

 Monday, February 20, 2012

How to read and interpret a cohort chart - cohort analysis Part 2

In my last post about Cohort analysis I briefly introduced what a cohort graph is, in this one I'm going to go into a little more detail about how you can use it. I'm planning on releasing some code so you can add it to your reporting suite in another post but if you can't wait until then, I've thrown up a quick online cohort generator at: www.quickcohort.com -just dump your data into it and click graph.

What can the cohort chart tell us?

A cohort chart can give you an idea of customer loyalty -and- an indication of potential problems in their lifecycle.

The key metric it gives us is customer loyalty. This is also one of the most frequently ignored metrics in every company (mostly because people aren't clear on how to measure it) yet it's probably one of the most important at the same time.

What is Customer Loyalty?

That depends very much on your business and as a result the graphs will likely look very different. It's probably easiest to compare what a couple of business types -a retailer and an online magazine- might consider loyalty.

As you can see, although there may be some cross overs (Visits to the store and Logins are probably quantified by a similar metric) the "what" depends very much on your business but the longer the duration between the first and last engagement in all scenarios above is how you can demonstrate loyalty.

How should I read a cohort chart?

Unlike most tables which you read left to right (a row at a time), you'll probably get more value out of a cohort chart from reading it by a column at a time. This will enable you to spot possible problems in the user's lifecycle.

Problem points in the user's lifecycle can often be spotted where the colours change in the same column. The greater the difference between the shades of colour, the bigger difference is between the two months. In a perfect world, 100% of the customers from Month 0 will still be using your site in Month 12 but life is rarely like that.

Looking at the chart below of user logins over time, the eager should spot that there are three months which appear to have issues: Month 3, Month 5 and Month 9:

If you're not sure on what you're looking for, you're spotting those columns which have a similarly shaded background which then lightens in the next month (or in the case of Month 9 is completely unshaded.

What does this mean?

As the chart above was logins over time, a quick glance over this cohort chart this would suggest the following to me:

• Overall there is a pretty good retention for the users (the percentage of customers going from month to month are pretty high)
• The majority of users only remain engaged up to month 3, at which point a lot of users lose interest in the site
• There is a clear drop off in customer retention after month 9
• The eldest customers had the best retention rates (despite the drop off in Month 9, 30-40% of visitors were still coming back until then)

When reading a cohort chart, you can generally discount the last cell of each cohort as it's the current month.

What might the cohort chart look like?

Repeat Sales

Logically, to be a repeat customer, you have to make at least two purchase from the retailer so the duration we're interested in (the month) is the period between the first purchase and most recent purchase.

First Date (Month 0): First Purchase
Last Date (Month x): Most Recent Purchase

Check out the cohort chart below and see what you can interpret.

Remember, Month 0 represents the first purchase -all customers appear in this column. Looking over the cohort chart, of the 69 customers the retailer had in October 2010, 39% (27 customers) were still around in November 2010 (month 1), 21% (15 customers) were still around in December 2010 and so on.

None of the 69 customers who made their first purchase in October 2010 are still a customer 12 months later (although if your customers tend to make a purchase near the end of the month, the customer who made a purchase in September 2011 may still make a purchase).

So looking at that chart, 30-40% of customers would make a second purchase 2 months after their first purchase. Of the older customers (those which first purchased before March 2011) 10% would make another purchase 5-6 months later.

What else can we gleam?

There are a few interesting things with the chart above, another is the sudden drop off in month 3 for those customers who first purchased in May/Jun 2011, similarly, the customers who signed up in Mar/Apr 2011 also stumbled in the same month, that could indicate some form of seasonal trend or change in marketing.

With a little background you will be able to get a much better insight into the meaning behind some of the numbers. If for instance you had changed your marketing routine around Jun 2011 this might explain the difference in numbers. It might be that your business is very seasonal (in which as you'd be better to look at a 24 month chart rather than 12 month).

Keeping a record of what you were doing around the different months is important, for instance you might start a pay-per-click campaign. Everyone's happy because you notice an increase in sales (so an increase in Month 0) but are they a valuable customer (a repeat purchaser) or a one-off? Cohort chart analysis will quickly highlight this to you as the increase in Month 0 will be reflected in Month 1.

Although time will tell, it would appear that a lot of customers make another purchase about 2 months after their first. This could be co-incidence or it might be that you're selling a product with a small sample accessory (e.g. a free pack of chalk with each chalkboard) and that sample pack runs out after a couple of months. Alternatively it could be a fault in the product e.g. you sell hinges and the oil runs out after a couple of months so they're buying more grease. By adding a little context to the data you'll likely get even more interesting stats out (we certainly have in the past).

Visits to the store or customer engagement

Things start to get really interesting when you start comparing two cohort charts for the same customer base and period against each other.

First Date (Month 0): First Purchase
Last Date (Month x): Most Recent Login

What's interesting when you compare the two charts is most of the customers who have made a purchase are still returning to the site (over 30% are still logging in in Month 11). This would suggest there's not as greater a problem with customer retention as there is with sales.

This could be because your store sells seasonal products but you keep customers engaged, it might just be a co-incidence but it should drive investigation.

What Can I do with this information?

In isolation it's helpful but only really gives you a top level view on a customer's lifetime with you, it's when you're able to combine this data with knowledge of your business, sales statistics, marketing strategy and information such as a customer's LTV (Lifetime Value) that it gets really interesting and useful.

Using a cohort chart and average sale value, you can use it as part of your sales forecasting and predict what your company's sales will be going forwards e.g.: if the Average Order Value is £10 and your average first 5 months looked like this:

You'll then know that of the 1,000 or so customers which sign up in the current month your revenue is likely to look something like this:

How have I got to those numbers? Well, of the 1,000 customers that purchase this month, 50% will make another purchase on or after month 1 and 20% will make a purchase on or after month 2. With this in mind, of the 1,000 customers from Month 0, 30% will make a purchase in Month 1 so the calculation is as follows:

([Number of customers] * [Percentage Returning in Month]) * [Average Order Value] = [Expected Sales]

(1,000 * 30%) * £10 = £3,000

There are a few assumptions with doing it like this -for instance, customers who purchase monthly will only be counted once etc but this is still a good start.

Using a cohort chart with sales data becomes very powerful as you're able to get a really good insight into whether campaigns are generating worthwhile leads or just generating traffic to the sites. If you're interested in reading more about that I'll overview it in another post as that gets pretty heavy on number crunching.

If you find that customers tend to drop off after a set number of months then it might be worth setting up some form of customer engagement which is triggered just before this point e.g. an email upselling a product that complements theirs or asking them to get in touch with feedback.

How to read and interpret a cohort chart - cohort analysis Part 2

Useful Links:            Comments [0]  Trackback

Tags: Analysis | Business | Business Start-up Advice | Expanding Your Business | Cohorts | Statistics

 Friday, February 10, 2012

Avoid Unrecognized attribute 'xmlns:xdt' configuration error

If you've been using web.config transformations or a nuget package with transformations in at all you'll no doubt have come across the runtime configuration error of "Unrecognized attribute 'xmlns:xdt'. Note that attribute names are case-sensitive" pointing at the start of the <configuration> node (if not see below). This one has been bugging me for a while so I thought I'd work a way around it.

Server Error in '/' Application.

---

Configuration Error

Line 1:  <?xml version="1.0" encoding="utf-8"?>
Line 2:  <configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
Line 3:    <configSections>
Line 4:      <section name="Exceptioneer" type="Something, Assembly" requirePermission="true" />

Source File: c:\domain.com\web.config    Line: 2

The Solution

To work around this, I've assumed you're running some form of custom build already (or have access to add a pre-deploy build step).

1. Download and install the MSBuild Community Tasks
2. Add the new FileUpdate build step below which looks at your config file (you'll need to run this against each one). You'll also need to have a space in the ReplacementText as you can't currently replace with String.Empty.
3. You're done

Simple but effective I think

<ItemGroup>
    <FileUpdateFiles Include="**\*.config" />
</ItemGroup>
<FileUpdate 
    Files="@(FileUpdateFiles)" 
    Regex=" xmlns\:xdt\=&quot;http\://schemas\.microsoft\.com/XML-Document-Transform&quot;"
    ReplacementText=" " />

If you've got a better (automated) solution, please let me know, it's been bugging me for ages!

Avoid Unrecognized attribute 'xmlns:xdt' configuration error

Useful Links:            Comments [0]  Trackback

Tags: ASP.Net | Development

 Friday, January 27, 2012

There’s never a right time

I'm about to go on holiday (yay me!), but have you ever worried beforehand because there's not enough time to complete everything you need to get done before you go?

Lets face it:-

There's rarely enough time to do everything in the average week when you've got a full week; without meetings or interruptions -let alone when you've got a deadline like a holiday!

So relax; make the deadline work for you. Use it as a way to get things done with a little more focus and clarity. Identify what needs to be done before you go and what can wait for your return and then prioritise what needs to be done. If you get it all done -great. If not, I suspect that it can wait for your return so enjoy yourself knowing that you'll get stuck in on your return.

Down time is important for everyone, if your clients worry about you going away, you can reassure them that when you get back you'll be raring to go.

Shameless plug - if you are thinking about going on holiday -but worried about supporting your clients, you should check out Crisis Cover. Crisis Cover allows you to store important information such as usernames and passwords and give on-demand (and audited) access to anyone supporting your clients in your absence.

You can sign up as an supplier here

There’s never a right time

Useful Links:            Comments [0]  Trackback

Tags: Business | Random Thought | The Site Doctor

 Monday, December 12, 2011

Start-up's golden triangle of customer loyalty - cohort analysis Part 1

If you're from a financial or medical background you'll probably already be familiar with cohort analysis but more recently it's become a very popular way of measuring customer loyalty among your consumers. I've been playing with it with our customers for a while now and I think many more businesses can benefit from it's insights.

Once you've worked out the customer's lifetime value (often referred to as LTV), average order spend and time until first purchase, your analysis often end there. LTV is better than nothing however you may be missing some major issues in their journey. Lets for example say you sell a widget. Your widget lasts 12 months but needs to be oiled every 3 months. Your gut tells you that this is the case but proving this is difficult without analysing each client individually.

Individually you can't gain a great insight into your customer as each is slightly different. Cohort analysis works around the granularity and groups customers together into cohorts. Each cohort is based on a fixed point in the customer's timeline with you -for instance the date/time they signed up or their first order. We can then use this fixed point to compare other customers who have gone past the same period in their lifetime (or not as the case may be) to spot trends.

The easiest way to understand you group the users is to imagine the following timeline of customer signups, we have three customers (Green, Blue and Red) and they all signup up at different times throughout a 6 month period:

Spotting trends in these customers is less than obvious however all customers have passed through a number of similar points in their lifecycle (in this example month 1, month 2 and month 3) so looking at the data from this perspective will help you spot the trends:

Most cohort analysis groups customers into monthly groups however the size of each group will depend on the number of signups/orders you have e.g. a system like Twitter will have enough data to produce cohorts on a minute or even second basis. By grouping customers together in this way you can then spot seasonal trends and retention (the length of time you keep a customer).

What does a cohort chart look like?

A cohort chart looks like this:

You get this very distinctive triangle because the users on the first row are your eldest users and will have been with you for the longest time (which is also why they have the longest row). The users on the last row have just joined in the current month so have the shortest row.

The chart above is rather encouraging; it's from one of our e-commerce clients and it shows really a rather dedicated customer base -a year after signing up 41% of the customers are still logging into the system!

You may also notice an interesting dip in retention for those customers who signed up in Dec 2010/Jan 2011. Although additional investigation is required, the type of customer base they have is very busy during these months so they've probably forgotten about signing up. This does however leave a prime opportunity for them to be contacted directly and encouraged back.

Start-up's golden triangle of customer loyalty - cohort analysis Part 1

Useful Links:            Comments [0]  Trackback

Tags: Analysis | Business | Business Start-up Advice | Expanding Your Business | Cohorts | Statistics

 Friday, October 28, 2011

Umbraco developers - remember to disable the umbDebug settings when you go live

Recently I've noticed a growing number of Umbraco developers forgetting to disable the Umbraco debug settings before going live. We all fall foul of this from time to time but it is a security loophole that you can patch incredibly easily.

If you're not familiar with the helpful debugging querystring parameters of umbDebug and umbDebugShowTrace they basically show you the ASP.Net trace output and highlight the various macros used on the page -there's also a useful toggle debugging in Umbraco bookmarklet on cpalm.dk.

Why you should disable trace

If you try it out on your site which has debugging enabled you'll get all sorts of helpful information output to the page including where your website is installed -all very helpful and interesting to hackers. It also identifies your site as an Umbraco site very quickly -again something you would want to avoid if at all possible.

How to disable the debug settings via the web.config

Umbraco helpfully has a built in flag in the web.config appSettings section which allows you to effortlessly toggle the debuging features on/off. To turn it off, search for "umbracoDebugMode" in your web.config and if it's set to "true", change it to false.

<add key="umbracoDebugMode" value="true" />

Should be:

<add key="umbracoDebugMode" value="false" />

For good measure you should also change ASP.Net's built in debug flag:

<compilation defaultLanguage="c#" debug="true" batch="false" targetFramework="4.0">

Should be:

<compilation defaultLanguage="c#" debug="false" batch="false" targetFramework="4.0">

Disable it using UrlRewriting.config

If you prefer the belts and braces method, you can add a rule to your UrlRewriting.config to redirect the user everytime the url includes something that looks suspicious. To do this, just add the following rewrites to your UrlRewriting.config (or replace it completely if you don't have any rules):

<urlrewritingnet xmlns="http://www.urlrewriting.net/schemas/config/2006/07"> 
          <rewrites> 
                    <add name="nodebugaspx" 
                        virtualUrl="(.*).aspx.*umbDebug.*" 
                        rewriteUrlParameter="IncludeQueryStringForRewrite" 
                        redirect="Application" 
                        destinationUrl="~$1.aspx" 
                        ignoreCase="true" />

                    <add name="nodebug" 
                        virtualUrl="(.*).*umbDebug.*" 
                        rewriteUrlParameter="IncludeQueryStringForRewrite" 
                        redirect="Application" 
                        destinationUrl="~$1" 
                        ignoreCase="true" /> 
          </rewrites> 
</urlrewritingnet> 

Umbraco developers - remember to disable the umbDebug settings when you go live

Useful Links:            Comments [1]  Trackback

Tags: ASP.Net | Development | The Site Doctor | Umbraco | Web Development

 Thursday, October 20, 2011

Think about your users when writing your content

Ignoring the aspects of design, SEO duplicate content, underlying code and tone of language, as a content editor you really should give consideration to your user and what they're looking for. I generally steer clear of critiquing -or even commenting on work that isn't our own (or when being asked by the creator) but sadly there still seems to be a real misunderstanding from clients on what makes a usable website.

We recently launched a website for local award winning pie makers - Elm Tree Foods and as a result we've spent a lot of time dealing with other local providers websites/council websites and I'm left stunned by the horrific experience they're offering their users. What riles me more about this though is the fact that most of their users are the sort that need to be helped through the process as they aren't often familiar with the internet (somewhat of an over generalising I realise).

A good example I came across today is Herefordshire's main tourism website: www.visitherefordshire.co.uk. It's well ranked for the search term of "Flavours of Herefordshire" (a good start) but it's then down hill from there. I was trying to find out where the Elm Tree Foods stall would be and when the festival was. We've seen signs locally saying it's at the Hereford Race Course (there's some debate over whether it really is) but we weren't sure that was the case for Elm Tree Foods.

You can try this yourself, see how long it takes you to find out where and when the Flavours of Herefordshire food festival is purely be using www.visitherefordshire.co.uk. Ideally you want all the information on one page.

Step 1: The Landing Page - Homepage

Message on the homepage - good start. Or is it? Take a closer look and you may find that although you've got the dates (and if you continue reading a time) there's still no indication of where the festival is:

Step 2: This week's events in Herefordshire

Clicking the only apparent link on the homepage (I didn't want details on the other events -rather the Flavours of Herefordshire event) takes you through to the listing page which has the Date, location, contact details but no time (which was on the homepage if you remember?).

So we're set? We have the location and the date/time, what more is there?

Step 3: The Flavours of Hereford event landing page (version 1)

Well, not knowing Hereford that well, I don't know where 1 King Street is so need to find that out. Logically I click through onto the event's page and I'm taken to:

Putting to one side the MASSIVE white space on the top right, again there is no mention of when this glorious event will take place.  Presumably they were going to put all the clear location/date/time information in that large white space at the top of the column -but were overwhelmed with their workload forgot.

Another point with this page is that the content talks a lot in the past tense which is very confusing, was this page meant to be released after the event?

I still don't have a single page with all the information on so lets pop back to the homepage to see if that offers anything else.

Step 4: Back to the homepage

Back in the homepage for another look and it turns out the title, although not completely clear, is also a link.

Step 4: The Flavours of Hereford event landing page (version 2)

Clicking the title, I'm taken to this page:

Ok good, I've got loads of helpful information here: "Hereford Race Course for the weekend of Saturday, 22nd October and Sunday 23rd October, 2011 - 10.00am to 4.30pm each day" -exactly what I was after (even though it's hidden away in a paragraph of unnecessary fluff)!

But hang on, I thought it was at "Discover Herefordshire Centre, 1 King Street, Hereford, Herefordshire"? What's this about the Hereford Race Course? Also, the other page didn't mention anything about tickets or prices, does that mean I have to pay now? I'm now confused.

Imagine if you didn't know it wasn't at the race course (as I previously did), you'd now be going to the Hereford race course, paying £7.00 to get in and left disappointed at not getting to try Elm Tree Foods' award winning pies. Bad times. To be clear, I won't know until this weekend whether it is at the Race Course or not (or indeed what will be at 1 King Street) so if you're interested, follow me on Twitter to find out first.

"But it's complicated because we have so much content"

We've all heard it from larger organisations when getting them onto the web. It's not hard to confuse the user -and it's also not difficult to help guide the user either; regardless of how much content you have, you just need to give consideration to the user's journey and what the important messages are at each step.

Although it is still having work done to it, here for comparison is the Elm Tree Foods homepage and event details page. Even when resized, the important information is largely available:

But good design costs too much

I don't know how much www.visitherefordshire.co.uk cost to design and develop however, one thing I'm almost certain of is that the user could have been offered a much better user experience than they are currently receiving.

If after reading this you're concerned about your user's experience, contact The Site Doctor for a website check up.

Think about your users when writing your content

Useful Links:            Comments [0]  Trackback

Tags: CMS | Content | Content Management | Content Management System | Design | Marketing | The Site Doctor | Web Development