Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a challenge I cannot wrap my head around properly.
I have a pretty common ecommerce/ordersystem database with customers, orders, and orderitems tables in it.
A customer has a created_at datefield. The order table has a delivery date and items are related to the order. (and I have a datetable that is related to the order/delivery_date field)
I would like to make a cohort analysis similar to this mockup:
I need to make a cohort churn analysis where I count the actual customers that signed up in a isoweek, and the percentage of the same customers that continues ordering the consequent weeks. Not neccessary like the mockup, but maybe like x customers signed up in 2016 week 34, 80% of theese still ordered in week 35, 65% in week 36 etc. For Week 35 we start the same series of calculation for customers that signed up in that week etc.
Anyone have a suggestion on how to solve this?
Solved! Go to Solution.
Hi @thoberre,
In PowerBI, it seems that we can not create a visual exactly the same as the structure of the mockup you post to show the cohort analysis. But you can try the following workaround to make a customer cohort analysis.
The relationships between these tables are like below:
Then, in User table, create some calculated columns and measures, please refer to the below formulas.
Calculated columns:
SignUpWeek = WEEKNUM(User[created_at])
Diff = [LastOrderWeek]-User[SignUpWeek]
Week1 = IF(User[Diff]>=1,1,0)
Week2 = IF(User[Diff]>=2,1,0)
Week3 = IF(User[Diff]>=3,1,0)
Week4 = IF(User[Diff]>=4,1,0)
Measures:
LastOrderWeek = WEEKNUM(MAX('Order'[delivery_date])) 1 =
CALCULATE (
COUNTA ( User[ID] ),
FILTER (
ALL ( User ),
( WEEKNUM ( User[created_at] ) = WEEKNUM ( MAX ( User[created_at] ) ) )
)
) 2 = SUM(User[Week1]) 3 = SUM(User[Week2])
4 = SUM(User[Week3])
>4 = SUM(User[Week4])
Insert a table visual, drag relative columns to it.
If you have any question, please feel free to ask.
Best regards,
Yuliana Gu
Hi @thoberre,
In PowerBI, it seems that we can not create a visual exactly the same as the structure of the mockup you post to show the cohort analysis. But you can try the following workaround to make a customer cohort analysis.
The relationships between these tables are like below:
Then, in User table, create some calculated columns and measures, please refer to the below formulas.
Calculated columns:
SignUpWeek = WEEKNUM(User[created_at])
Diff = [LastOrderWeek]-User[SignUpWeek]
Week1 = IF(User[Diff]>=1,1,0)
Week2 = IF(User[Diff]>=2,1,0)
Week3 = IF(User[Diff]>=3,1,0)
Week4 = IF(User[Diff]>=4,1,0)
Measures:
LastOrderWeek = WEEKNUM(MAX('Order'[delivery_date])) 1 =
CALCULATE (
COUNTA ( User[ID] ),
FILTER (
ALL ( User ),
( WEEKNUM ( User[created_at] ) = WEEKNUM ( MAX ( User[created_at] ) ) )
)
) 2 = SUM(User[Week1]) 3 = SUM(User[Week2])
4 = SUM(User[Week3])
>4 = SUM(User[Week4])
Insert a table visual, drag relative columns to it.
If you have any question, please feel free to ask.
Best regards,
Yuliana Gu
hi, i have a similar problem like this but i cannot figure out how to solve it.
can someone help?
I think the solution is not correct I have the same concept but the results are wrong, please revise.
Hi Please go threw with the below link
http://community.powerbi.com/t5/Desktop/Cohort-Analysis/m-p/10217#M2088
Thanks
Abhi
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |