Cohort Analysis - Activity

Hello,

I need to build a time-based Cohort Analysis but I’m having trouble adapting the showcases and videos about this topic to my own needs.

Instead of a monthly cohort, I need a quarterly cohort. And instead of calculating when they make their second purchase, I need to see how many customers from that cohort made a transaction in each following quarter.

My main issue is that the DATEDIFF doesn’t let me use a measure as date and I can’t add a column in the sales table with a “Days since Join Date”, where a subtract the Order Date from the Join Date of that particular customer (because of circular references).

What does work for me, is a matrix with both rows and columns being Quarters in Year and Values is Unique Customers. But when I add the periods to the column part of the matrix it can’t find a relationship and I’m not sure how to fix that.

I haven’t found any information online that could help me. I’m not providing any of my dax calculations because I’m just adapting things from the videos and it’s not making much sense. I’m just looking for someone to point me in the right direction to be able to understand the logic behind making this work, Maybe I’m missing a key function or there’s already a video that talks about this?

Thanks!

I think the best idea here would be to upload a demo model that lays out the scenario so that myself for those helping out on the forum can actually see all of the different factors at play.

This is as you can imagine is quite complex analysis so just being able to work out everything without seeing or being able to play around with a model is a bit difficult.

To me I think you’re also more looking at attrition analysis, or some derivative of this is, if you’re trying to compare those who have purchased in one time period versus another. This can sometimes be confused with cohort analysis. Maybe you’re actually doing a combination of both, I’m not 100% sure.

For the comparison of one time period to another what you probably want to be looking at more is the CALCUATETABLE function.

See here for an example

This particular function enables you to compare customers from one time period to another time period and then compare which have purchased in both.

I believe the example I’ve linked to above really covers this in depth. I would also recommend having a look at all the attrition type analysis examples and enterprise DNA online as well.

See how you go with these ideas.

Sam

Hi @Tommy, we’ve noticed that no response has been received from you since January 21, 2020. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!