Customer cohort analysis

[thoberre]

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?

[v-yulgu-msft · Accepted 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

[GershwinMunich]

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

can someone help?

[MAAbdullah47]

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

[abhi123]

Hi Please go threw with the below link

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

Thanks 

Abhi