cancel
Showing results for
Did you mean:
Highlighted
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.

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?

1 ACCEPTED SOLUTION

Accepted Solutions
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:

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.

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
Frequent Visitor

## Re: Customer cohort analysis

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

Thanks

Abhi

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:

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.

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.
Member

## Re: Customer cohort analysis

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

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?

Announcements

#### Community Highlights

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

#### Power Platform Summit North America

Register by September 5 to save \$200

#### 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.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 302 members 4,047 guests
Recent signins: