1 2 Previous Next 22 Replies Latest reply on Jul 26, 2016 10:45 AM by Phillip Black Branched from an earlier discussion.

# Cohort Calculation help - Cohort based on Transaction Gaps

Hi Simon,

I am working on a similar kind of analysis for one of my requirements and would need your help. From the use case below  I want to calculate how soon or often the customer drops and comes back. I tried using cohort analysis but did not give me the right results. Can you please check my workbook and let me know what am I missing?The specified item was not found.

Below is the use case -

Customer  1 had purchased some items in Jan, Feb, March..... and dropped in April, May,June bought again in July. In this case the customer dropped after 3 months and came back after 3 months.

Customer  2 had purchased some items in Jan  and dropped in Feb,March, April, May,June bought again in July. In this case the customer dropped after 1 months and came back after 5 months.

I just want to give the users a high level count of how many customers dropped after 1,2,3,4,5,6........months.

Thanks,

JC

• ###### 1. Re: Cohort Calculation help - Cohort based on Transaction Gaps

I've branched this off into a new discussion, as this is a bit of a different question (and want to keep the original thread single question).

So we may need a few attempts to get exatcly what you want...and depending on some question I have it may be very complicated (or even not possible). So I've started with the simplest version...which we can do through LoD calculations and so won't need to have customer and transaction date in the VizLoD.

So I can see that the data is pre-densified (by this I mean we have an entry for every month/customer whether they made a transaction or not...this makes life a little easier).

First I created a First and Last Transaction date per customer (as a FIXED LoD) so we only start counting 'zero' months between these 2 dates

[First Purchase Month - LOD Customer]

DATE({FIXED [Customers]: MIN(DATETRUNC('month',IIF(ZN([Sales])>0,[Date Of Purchase],NULL)))})

[Last Purchase Month - LOD Customer]

DATE({FIXED [Customers]: MAX(DATETRUNC('month',IIF(ZN([Sales])>0,[Date Of Purchase],NULL)))})

we can then use these 2 fields, and check for any 'zero' sales months (by customer) between these 2 dates

[Months with No Purchase]

IF [Date of Purchase Month]>=[First Purchase Month - LOD Customer]

AND [Date of Purchase Month]<=[Last Purchase Month - LOD Customer]

AND ZN([Sales])<=0

THEN [Date of Purchase Month]

END

As we are using FIXED LoD all the values are returned at row level, and so can be calculated independant of the VizLoD

So our dimension for Months without purchase, as a FIXED LoD is

[Months with No Purchase - Count LoD Customer]

{FIXED [Customers]: COUNTD([Months with No Purchase])}

We can then bring this field into the Row Shelf and COUNTD on customers that fall into each group. In the 'SR01' tab I've kept Customer in the detail, so you can verify which customer fall into each group, althuogh you don't need this to make it work.

However this brings up a few questions which, depending on the answers, may need a completely different method...

How do we deal with customer that have stopped shopping? Customer 1 falls into Months without sales 1, as they have one Month without sales, however after their last purchase (October 2015) there are several months?

How do we deal with 2 lots of 'zero' sales months? Customer 4 has a 2 month break, and then later a 4 month break...this is why they are currently in Months without sales 6

As I said this is probably a 'starter for 10', but hopefully makes sense. If you let me know on how you'd handle the above situations we can think how we can handle them in Tableau.

• ###### 2. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi Simon,

First of All - Thank you very much for helping me out to resolve this issue and starting a new thread as well. I agree with you that this requirement is a little complicated and should be in a new thread.

The business question I am trying to answer is what is the % of customers that are faithful in shopping from our website?

The only variable that effects our customers is some might have dropped off for the winter and then begin activity again for the spring/ summer. It would be good to know how many people fall into this pattern. The "tipping point" (number of months without an order that shifts customers into the lost customer category) would be great to understand. What would be helpful here is to know the % risk of their exit based on x number of weeks they haven't shopped and based on x % their order amount has decreased.

For e.g. Customer 1 - Shopped from June 2015 - August 2015, did not shop in September, came back again in October, November and dropped again till today.

In my visualization i should be able to say -

1. There were 10 customers that dropped after 3 months and was back again after 1 month
2. There were 5 customers that dropped after 2 months and was back again after 6 months.

And when they click on the 1 Month cell, users should be able to see the customers with raw data which i can use action filters when combine all the workbooks in one dashboard.

1. How do we deal with customer that have stopped shopping? Customer 1 falls into Months without sales 1, as they have one Month without sales, however after their last purchase (October 2015) there are several months?

Answer  - In this scenario the visualization will show that Customer 1 dropped after 3 months and came back after 1 Month.

2. How do we deal with 2 lots of 'zero' sales months? Customer 4 has a 2 month break, and then later a 4 month break...this is why they are currently in Months without sales 6

Answer -  In this scenario the visualization will show that Customer 4 dropped after 3 months and came back after 2 Months.

The visualization you created is very helpful. It's showing the users how many months did a customer drop between first and last purchase month. Instead of showing all the customers on the bar chart can we show them a count of customers that have months with no shopping. and when the click on 0,1 or 2 they should be able to see the raw data.

IT

Let me know if you have any more questions.

Thanks,

Jitan

• ###### 3. Re: Cohort Calculation help - Cohort based on Transaction Gaps

hi Jitan,

So (I think) I get all that!...and thanks for the detail on 'what you are trying to measure'...this is a very helpful way of phasing the question.

I'm out with clients the next couple of days, and need to give this a little bit of thought (mainly...can we do this with FIXED LoDs? or are we going to need Table Calcs, which move along the Month/Customer grain to do the calculations). I'll have a bit of time on Friday, so will let it peculate and (hopefully) come back with a solution for you.

• ###### 4. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi Simon,

No Problem! Thanks again for your help.

Jitan

• ###### 5. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi Jitan,

So I’ve had a bit of play with this, and (think) I’ve come up with a way to do this using LoDs…btw in the attached you’ll see a couple of tabs with ‘TC’ in the name. This was me playing with using Table Calcs to achieve this…but as I thought would happen, I get to a point when I have created the ‘Consecutive Months of Shopping’ & ‘Consecutive Months of 1st Break’, which I then want to use as partitions for further calcs (such as COUNTD of customers in each group), but we can’t (alas) partition on table calcs…I’ve left it in for interest.

I did however come across a way to do this using LoDs, meaning that we are creating ‘real’ row-level dimensions…caveat – although the LoDs are fairly simple the logic if a bit of a brain-hurter (well it was for me anyway!). I’d also add that luckily (or unluckily for you, depending how you look at it!) I was bored on the train back from a meeting, with no WiFi and just your workbook to play with, so have gone to town on a ‘candidate’ dashboard! (no need to use this, just a bit of fun to pass away the hours!). I won’t go into how I built it, as you should be able to work it out (it was pretty simple once we’d created the ‘consecutive shopping months – to 1st break’ & ‘consecutive no-shopping months - to 1st resumption’

Right on with the problem! So firstly I’ve only considered the customers 1st purchase to 1st break (consecutive shopping months to 1st break), and 1st break period (consecutive break months from 1st break to 1st resumption). I’ve also not handled where someone has stopped shopping and not resumed (although the same logic can handle this, using {MAX([]Date of Purchase Month)}). I have also put in a ‘Lost Customer’ flag (this is so we can see the %age of customers who go ‘Lost’ based on their consecutive shopping months and break period). I’ve set this on a parameter, so can easily be changed. I do however capture their total non-sale months, after their 1st shop.

As before I created

[First Purchase Month - LOD Customer]

DATE({FIXED [Customers]: MIN(DATETRUNC('month',IIF(ZN([Sales])>0,[Date Of Purchase],NULL)))})

And then a Boolean field off this

[Valid Dates for Customer > 1st Purchase]

[Date Of Purchase]>=[First Purchase Month - LOD Customer]

So we only consider the customer (and start counting months) after their 1st shop.

Next I create a field for the 1st break month

[First Break Month]

{FIXED[Customers]: MIN(IIF([Date of Purchase Month]>[First Purchase Month - LOD Customer] AND [Sales] = 0,[Date of Purchase Month],NULL))}

Looks a bit odd!...this is basic technique for the whole thing. I only return the month if it’s after their 1st purchase date, and the sales = 0 (i.e. a break after their first purchase). I take the MIN of what’s returned and it gives me the 1st break month.

So now we can create out first dimension (number of consecutive months after 1st purchase)

[Consecutive Months Shopping - to 1st Break LoD]

{FIXED [Customers]: COUNTD(IF [Valid Dates for Customer > 1st Purchase]

AND [Date of Purchase Month]<[First Break Month] THEN [Date of Purchase Month] END)}

Similar logic to the above, where we only return Months where the months are between the 1st purchase and 1st break…btw I’ve also created a Bin for these dimensions…but the bin is purely to allow me to use the ‘Show Missing Values’ in my (self-indulgent) Viz!

So next we want to know how long this 1st break is…

[First Resumption Month]

{FIXED[Customers]: MIN(IIF([Date of Purchase Month]>[First Break Month]

AND [Sales] > 0,[Date of Purchase Month],NULL))}

As you can see, very similar to the 1st one

So from this we can create our 2nd dimension, number of months in 1st break

[Consecutive Months Break - to 1st Resumption LoD]

{FIXED [Customers]: COUNTD(IF [Date of Purchase Month]>= [First Break Month]

AND [Date of Purchase Month] < [First Resumption Month] AND [Sales]=0 THEN [Date of Purchase Month] END)}

As you can (hopefully) see we could continue this logic to pick up the number of months following first resumption, then the number of months in 2nd break…etc. unfortunately we need a formula for each break (1st, 2nd, 3rd…) as we can’t (to my knowledge) get Tableau to run over the Customer/Months and keep building for each break. We (kind of) can with the TC method, but then run into the problem that there is not much we can do with them, as we cannot partition over them and so get a count of customers for each consecutive months/break months combination.

So hopefully this makes sense, gives you some ideas (it’s quite a tricky problem to articulate, let alone find a simple viz to give insights), and gets you to what you want.

As always, with these more challenging problems, I’ve learned quite a bit myself which is why I love the community!

2 of 2 people found this helpful
• ###### 6. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi all,

To complement what Simon have done here,

I attach the wb with pure Table Calculations.

One could calculate the sizes of the "Cohorts" --

but only for each Date (Month).

Anything above that is simply mind prohibitive :-)

Yours,

Yuri

2 of 2 people found this helpful
• ###### 7. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi Simon,

Sorry for the late response! Thanks a lot for your help in resolving this issue. It took me some time to digest the calculation. The first break and resumption month is  the key to this analysis. Also, thanks for the visualizations, really helps in understanding the data. I am also building some visualization on my end which will filter based on the first break month. So for ex. if the first break month is after 2 months then the dashboard will be updated only for those customers that had a break after 2 months. This was a complex calculation and it would not have been possible without your inputs.

Again, I appreciate all your help and also agree that this community has lot of information to learn. I have learned my tableau skills using online videos and reading the discussions in this community.

Thanks,

Jitan

• ###### 8. Re: Cohort Calculation help - Cohort based on Transaction Gaps

hi Yuriy,

Nice work...I really like the technique of changing the 'scales' of the various measurements (*1000, *100), in order to isolate the elements (but return a single, numeric, dimension...with some clever custom formatting). I've seen you use this a couple of times, and always forget about using this!

Anything above that is simply mind prohibitive :-)

• ###### 9. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi Simon,

Hope you are doing well. There are couple of questions on this analysis that I would need your suggestion -

1. Is it possible to identify customers who purchased  for one month and never came back.
2. Adding a filter  to identify customers, who in any one month spent the most money and customers who in any one month spent the least money.
4. Identify the customers who shopped during a particular month in the past and identify which of that cohort of customers are still buying from the website.

Thanks,

Jitan

• ###### 10. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi Jitan,

So there are (as always in Tableau) a few ways to do this....so I've give you a few suggestions (which you should be able to use the logic to formulate you 'real' world solution...

In the attached there are folders (of calculated dimension) for Q1, Q2, Q3, Q4...which relate to the 4 questions you've asked below. I've had to make some general assumptions, so may not be perfect, but should hopefully get you started. As you'll see they are all formed in a similar way, just building up the IF logic for each scenario. There is a Tab Q1-Q3 which has the key fields in from the Q1-Q3 dimensions, and then a separate sheet for Q4 (as this runs off a parameter, to choose your historic month)

Take a look through and let me know if anything doesn't make sense

• ###### 11. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi Simon,

Thanks again for your help. It's been a good learning with this analysis,

Based on your suggestions i have come up with 2 dashboards.

Dashboard 1 showing summary of sales by year and consecutive month shopping. Working great with no issue.

Dashboard 2  showing total no of months between First and Second purchase. I need your help on this one - If you look at the screen shot below I have clicked the August row and customers that have 3 months between first and second purchase. This should be actually 2 months. I have used a date difference formula and not sure what am I missing. Can you please check this for me?  Below are the formulas I have used to come for this visualization -

1. Months with 2nd Purchase  -

datediff('month',[First Purchase Month - LOD Customer],[Second Purchase Month - LOD Customer (copy)])

2. FirstPurchase Month - LOD Customer

DATE({FIXED [Customers1]: MIN(DATETRUNC('month',IIF(ZN([Sales])>0,[Date Of Purchase],NULL)))})

3. Second Purchase Month - LOD Customer (Copy)

DATE({FIXED [Customers1]: MIN(DATETRUNC('month',IIF(ZN([Sales])>0,[Repeat Order],NULL)))})

4. Repeat Order

iif([Date Of Purchase]>[First Purchase Month - LOD Customer],[Date Of Purchase],null)

Also, when I click in the month of June  - The visualization shows 577 customers that has no gaps between first and second month of purchase.

But the screen shot shows it has 1 month between first and second purchase.

This should rather show as zero or no gaps between first and second purchase. Can you please look at this workbook and let me know what am i missing in my calculation.

Thank you as always for your help,

Jitan

• ###### 12. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Hi Simon,

I was able to resolve the all the above issues. Thanks again for your help.

Thanks,

Jitan

• ###### 13. Re: Cohort Calculation help - Cohort based on Transaction Gaps

hi Jitan,

Sorry for not getting back to you...been out and about for a few days!!

Glad you managed to find a solution..and that DATEDIFF is an inclusive function so...

DATEDIFF('month',DATETRUNC('month',#01/08/2015#),DATETRUNC('month',#01/11/2015#)) is 3...which it (kind of) is 11-8 = 3!!...in the way you want to use it (the blanks between the 2 dates) you just need to -1 from it.

• ###### 14. Re: Cohort Calculation help - Cohort based on Transaction Gaps

Thanks Simon!

Got one more question for you about grouping the percentage difference of sales from previous month.

So for e.g. There are 10 customers that had shopped in June and July and I want to show a visualization that groups by % Difference.

1. Grouping customers based on % change

a. Decreasing account change: 15% - 30%, 30% - 45%, Everything above 45%

b. Increasing account change: 15% - 30%, 30% - 45%, Everything above 45%

If a user clicks on 15% decreasing account change then the data should show only those set of customers.

Also the data set  will have data for the past months but the calculation should only pick up current and previous months.

 Customer Name May Sales June Sales % Difference from Previous Month Customer 1 120 100 -17% Customer 2 100 120 20% Customer 3 99 75 -24% Customer 4 37 0 -100% Customer 5 180 125 -31% Customer 6 250 200 -20% Customer 7 225 250 11% Customer 8 45 35 -22% Customer 9 87 100 15% Customer 10 55 0 -100% Customer 11 70 0 -100% Customer 12 600 590 -2% Customer 13 444 475 7% Customer 14 450 500 11% Customer 15 345 390 13% Customer 16 325 350 8% Customer 17 275 290 5% Customer 18 220 200 -9%

Let me know if you have any suggestion on this one

Thanks,

Jitan

1 2 Previous Next