Cohort Analysis are used to study the behaviour within your customer group and gives you the ability to understand your customer preferences, causes and even actions on changes to your product or strategy.

We love Tableau!

Are you running an ecommerce business, offering services through digital channels where you interact regularly with your clients? You definitely need to have a look at your cohorts!

It shows you exactly which marketing activities led to wich amount of commercial success and breaks down your clients buying activities into a meaningful fraction and shows you exactly the amount of days/ weeks your client took to repurchase or reorder.

 

An example of Churn Rates with newly acquired customers within a period:


This example shows you how often customer (which were acquired in a month) have re-ordered in the following month.

What does this visualisation tells us?

  • Only 20% of our customer re-order in the next month
  • We loose nearly 4 out of 5 customers
  • We need to focus more on marketing activities within 30 Days after the first purchase

An example of Customer Live Time Values by acquisition year:

This example shows the average total spending of a customer acquired in a specific year.

What does this visualisation tells us?

  • 2014 Cohort is performing much better than 2013
  • 2015 and 2016 are both behind the average customer spendings of the cohorts 2014

Both examples show us that marketing and sales activities shall be planned wisely around the performance of any cohort and that any customer group can behave differently.

What is the minimum data input needed for a comprehensive cohort analysis?

To perform a meaningful analysis your daily transaction/ sales data should include a:
– Transaction Date
– Unique Customer ID
– Type of Service/ Product
– EAN (ecommerce only)
– Number of sold products
– Revenue
– Revenue before discounts/ or Voucher usage
– Date of first Purchase

In some cases it might happen that the CRM does not track the “Date of first purchase”. For this case make sure to add a field in your data set which filters the “minimum date” out and gives it back to each single transaction.

 

This is how its done in Tableau

Step 1. Create Calculated Field
Date First Order
{ FIXED [Unique Customer ID] : min([Transaction Date])}

Step 2. Create Calculated Field
Repeat Purchase
iif([Transaction Date]>[Date First Order (Unique Customer ID)],[Transaction Date],null)

Step 3. Create Calculated Field
2nd Purchase
{ FIXED [Unique Customer ID] : min([repeat purchase])}
Must be a Dimension!

Step 4. Create Calculated Field
Week to repeat purchase
DATEDIFF(‚week‘,[Date First Order],[2nd Purchase])
!!!Must be a Dimension!!!

Step 5. Create Calculated Field
Month of First Order
DATETRUNC(‚month‘, [Date First Order])

Step 6.
Date First Order to Columns
Week to repeat purchase to Rows

Step 7.
Add Month of first order to Filters

Step 8.
Add First Order Date as a Color Filter. Make sure to set it to Count(Distinct)

 

The GANDT-Ventures Team

 

Please contact us if you would like to know more about this topic:

GANDT Ventures GmbH
Jacek Kozera
j.kozera@gandt.ch
+41 76 2215 500