Cohort analysis from single one table

[MAAbdullah47]

Dear All ,

I know there were some posts related to (cohort)  like the one here in this post:

https://community.powerbi.com/t5/Desktop/Customer-cohort-analysis/m-p/87818

but in that post it assumes the data is distributed among many tables with relationships, my case is different I have only one table represent the order line table with the following (columns):

(order id, customer id, order date, total order)

I need to build cohort analysis table ( a monthly basis for each cohort) do the following different results out put:

Output 1) 2 tables list all the retention customers who return-back (2 times or more) with a distribution of the number of customers in each (m) like the following 2 snapshots:

Out put 2)

A-

distribution table Lists all the retention customers on their (2nd) time (m),  like the following examples :

-Customer No 7777 start his first time order in cohort-2016-03 (m1), then on his second time come at (m7)

--> no need to count if he comes in other (m)s after m7.

-Customer No 6666 start his first time order in cohort-2016-05 (m1), then on his second time come at (m5) --> no need to count if he comes in other (m)s after m5.

B-

For each cohort in (A), In addition to the grand-total number of customers, should Counting how many of them (customers) order only one time and how may return-back order for the second time (retention).  here is a snapshot might help:

Thank you all 

[v-caliao-msft]

@MAAbdullah47,

Could you please share us some sample data with a Excel file, so that we can make further analysis.

From your screenshot, it seems that you need to re-shape your table structure. If you have the data like below, then you can use a Matrix visual to get your expected result.

Regards,

Charlie Liao

[MAAbdullah47]

Did you got my last replay ?

[MAAbdullah47]

Send Me Your email please to share the file if you find the information on my thread is not sufficient. 

[MAAbdullah47]

In My Example above for the data:

order-no    date                      customer no           price     

----------      -----                    --------------          ------

1                15/3/2016                       3                      10

3                 17/4/2016                       5                      60

1                15/3/2016                       3                       7

 2                15/3/2016                      7                       12

 18               10/5/2016                     3                        100

 22               10/7/2016                     5                         30

customer no 3 come first time in cohort-2016-03(m1)  and on the same cohort at (m5)===> Retention customer.

customer no 5 come first time in cohort-2016-04(m1)   and on the same cohort at (m7)===> Retention customer.

customer no 7 come first time in cohort-2016-03(m1)   and never come back again ===> Non Retention customer.

For Out Put 1)  I'm measuring the loyalty of the customer  (customer no 7) will not be considered among the loyal at all.

For Output 2) I'm concerned about the site frequency is it attractive enough for the customers to re-visit it again? , in this case, the customer no (7) should count among the Non Retention customer, and customers (3,5) counted as Retention customer on their second visit but I will not count the >2 visits (how many retention and how many not)  cuz my target is to measure each cohort frequency is it big or small.

[MAAbdullah47]

Thank You for your reply, defiantly as I introduced earlier the source data is raw data, soon I'll send it to you by private email if you like, but usually, it is something like this:

order-no    date                      customer no           price     

----------      -----                    --------------          ------

1                15/3/2016                       3                      10

3                 17/4/2016                       5                      60

1                15/3/2016                       3                       7

 2                15/3/2016                      7                       12

 18               10/5/2016                     3                        100

 22               10/7/2016                     5                         30

and so on

the cohorts will be created based on the following process assuming now we are in month April 2017 (means 13 (m) life cycle from March 2016) :

cohort-2016-03      March 2016 cohort (m=1..13).

cohort-2016-04      April 2016 Cohort   (m=1..13) but m13=0 cuz it is period from April 2016 to April 2017 is 12 Months

cohort-2016-05      May  2016 Cohort   (m=1..13) but m 12,13=0 cuz it is period from May 2016 to April 2017 is 11 Months

and so on till

cohort-2017-03     March 2017 Cohort  (m=1..13)  but m from (2..13) =0 cuz the period from  March 2017 to April 2017 Is only 1 month.

Soon I 'll send you the data I 'll try to send it as a private message or could tell me what is your email.