cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Microsoft v-yulgu-msft
Microsoft

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.

View solution in original post

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

Microsoft v-yulgu-msft
Microsoft

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.

View solution in original post

Re: Customer cohort analysis

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

Highlighted
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors