cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
thoberre Visitor
Visitor

Customer cohort analysis

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.

 

2016-11-09_00h29_50.png

 

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:

 

2016-11-09_00h39_09.png

 

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Customer cohort analysis

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:

1.PNG

 

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.

2.PNG

 

If you have any question, please feel free to ask.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
abhi123 Frequent Visitor
Frequent Visitor

Re: Customer cohort analysis

Hi Please go threw with the below link

 

http://community.powerbi.com/t5/Desktop/Cohort-Analysis/m-p/10217#M2088

 

Thanks 

Abhi

v-yulgu-msft Super Contributor
Super Contributor

Re: Customer cohort analysis

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:

1.PNG

 

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.

2.PNG

 

If you have any question, please feel free to ask.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Customer cohort analysis

I think the solution is not correct I have the same concept but the results are wrong, please revise.

GershwinMunich Frequent Visitor
Frequent Visitor

Re: Customer cohort analysis

hi, i have a similar problem like this but i cannot figure out how to solve it.

can someone help?

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 302 members 4,047 guests
Please welcome our newest community members: