Cohort analysis from single one table

Question

Dear All ,&nbsp;&nbsp;I know there were some posts related to (cohort) &nbsp;like the one here in this post:https://community.powerbi.com/t5/Desktop/Customer-cohort-analysis/m-p/87818&nbsp;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,&nbsp;total order)&nbsp;I need to build cohort analysis table ( a monthly basis for each cohort) do the following different&nbsp;results out put:&nbsp;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:&nbsp;&nbsp;&nbsp;&nbsp;Out put 2)&nbsp;A-distribution table Lists all the retention customers on their&nbsp;(2nd) time (m), &nbsp;like the following examples :-Customer No 7777 start his first time order in cohort-2016-03 (m1),&nbsp;then on his second time come at (m7)--&gt; no need to count if he comes&nbsp;in other (m)s after m7.-Customer&nbsp;No 6666 start his first time order in cohort-2016-05 (m1),&nbsp;then on his second time come at (m5) --&gt; no need to count if he comes&nbsp;in other (m)s after m5.&nbsp;B-For each cohort in (A), In addition to the grand-total number of customers,&nbsp;should Counting how many of them (customers) order only&nbsp;one time&nbsp;and how may&nbsp;return-back order for the second time (retention). &nbsp;here is a snapshot might help:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thank you all&nbsp;

v-caliao-msft · Answer

MAAbdullah47,
&nbsp;
Could you please share us some sample data with a Excel file, so that we can make further analysis.
&nbsp;
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&nbsp;a Matrix visual to get your expected result.
Regards,
Charlie Liao

maabdullah47 · Answer

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:&nbsp;&nbsp;order-no &nbsp; &nbsp;date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;customer no &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; price &nbsp; &nbsp;&nbsp;---------- &nbsp; &nbsp; &nbsp;----- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-------------- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15/3/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;103 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 17/4/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;601 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15/3/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15/3/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp;18 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/5/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100&nbsp;22 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/7/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp;and so on&nbsp;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) :&nbsp;cohort-2016-03 &nbsp; &nbsp; &nbsp;March 2016 cohort (m=1..13).cohort-2016-04 &nbsp; &nbsp; &nbsp;April 2016 Cohort &nbsp; (m=1..13) but m13=0 cuz it is period&nbsp;from April&nbsp;2016 to April&nbsp;2017 is 12 Monthscohort-2016-05 &nbsp; &nbsp; &nbsp;May &nbsp;2016 Cohort &nbsp;&nbsp;(m=1..13) but m 12,13=0 cuz it is period&nbsp;from May 2016 to April&nbsp;2017 is 11 Months&nbsp;and so on till&nbsp;cohort-2017-03 &nbsp; &nbsp; March 2017 Cohort &nbsp;(m=1..13) &nbsp;but m from (2..13) =0 cuz the period&nbsp;from &nbsp;March 2017 to April 2017 Is only 1 month.&nbsp;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.&nbsp;&nbsp;&nbsp;&nbsp;

maabdullah47 · Answer

In My Example above for the data:&nbsp;order-no &nbsp; &nbsp;date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;customer no &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; price &nbsp; &nbsp;&nbsp;---------- &nbsp; &nbsp; &nbsp;----- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-------------- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;------1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15/3/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;103 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 17/4/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;601 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15/3/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15/3/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 12&nbsp;18 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/5/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;100&nbsp;22 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10/7/2016 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp;customer no 3 come first time in cohort-2016-03(m1) &nbsp;and on the same cohort at (m5)===&gt; Retention&nbsp;customer.customer no 5&nbsp;come first time in cohort-2016-04(m1) &nbsp;&nbsp;and on the same cohort at (m7)===&gt; Retention&nbsp;customer.customer no 7&nbsp;come first time in cohort-2016-03(m1) &nbsp;&nbsp;and never come back again ===&gt; Non Retention&nbsp;customer.&nbsp;For Out Put 1) &nbsp;I'm measuring the loyalty of the customer &nbsp;(customer no 7) will not be considered&nbsp;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&nbsp;customer, and customers (3,5) counted as&nbsp;Retention&nbsp;customer on their second visit but I will not count the &gt;2 visits (how many retention&nbsp;and how many not) &nbsp;cuz my target is to measure each cohort frequency is it big or small.&nbsp;&nbsp;&nbsp;

maabdullah47 · Answer

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

maabdullah47 · Answer

Did you got my last replay ?

Forum Discussion

Cohort analysis from single one table

5 Replies

Related content

Cohort analysis

Cohort Analysis tutorial

Cohort Analysis

Customer cohort analysis

Cohort Analysis Revenue