Cohort Workflow

[Likness]

What are the workflow steps to create a Time 0 based on a business event then align subsequent periods e.g. Time 1, Time 2, etc.

[MichalM]

@Likness 

I'm afraid it's going to be quite difficult to point you in the right direction without understanding more context around the issue you're facing. 

• What does your data look like?
• Can you provide an anonymised sample?
• What is the business event?
• How do you identify it in the data?
• How is the next period defined? +n hours/days/weeks/months?

[Likness]

I can share an anonymised sample the data includes the following: Unique Customer ID; Year/Month of purchase; Product ID; Sale Amount

I'm thinking of this as a classic cohort analysis:

1. Determine the month a customer makes their first product purchase based on the presence of a sales figure e.g. Customer 835835 first purchased product "K" in 2018-08 for $14,013.
2. Setting that month to Month 0 then track the next six months of sales to measure their behaviour e.g. ongoing, increasing or decreasing sales of product "K" in Month 1, 2, 3, 4, 5, 6.
3. Find other customers who purchase "K" for the first time, regardless of month, and align them to Month 0 for the same tracking.  

I hope to visualize the behaviour trend associated with the purchase of the product at scale.   

[neilgallen · Accepted solution]

based on your explanation I think this gets you there. In short:

Using the summary tool, find the first date for each customer and product combination.

Use the generate rows tool to create new records, one for each month after this date until 6 months have passed.

Join back to your original data (using customer id, product, and date as keys) to find purchases in those months.

Use the tile tool to create a record it (leveling everyone to months 0-6), but we have to subtract one from that value because tile tool starts at 1, not zero.

From here you could do whatever analysis you wanted...

[Likness]

Thanks! That is exactly what I was looking for.

[EllenBrown]

This looks like a great basis for something I've been trying to do.  I'll need to add to it, but I think it will work.

Thanks!!