Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Customer Repurchase Cycle

Hi Community and PowerBi Masters,   I am currently working on an analysis for which I try to understand the consumer behavior in detail.  The corresponding data that I have is (name of table: All_...
  • colacan's avatar
    colacan
    4 years ago

    Anonymous  Hi, considering your issues I made measures as below. I hope this helps you.

     

    //[NumOfTotPurchase]: This counts all purchases even if order numbers are the same. e.g. if Emil purchased 4 macbooks on the same day under same oerder number, it still counts 4.

    NumOfTotPurchase = countrows(All_Platforms)   

    //[NumOfVisit]:  This counts only number of visit regardless number of purchases e.g. If Emil purchased 4 macbooks, if counts only 1 as long as all purchases are done under same order number
     
    NumOfVisit =
    VAR currentuser = MAX( All_Platforms[User Name] )
    VAR NumOfVisit =
    COUNTROWS(
    DISTINCT(
    SELECTCOLUMNS(
    FILTER( All_Platforms, All_Platforms[User Name] = currentuser ),
    "OrderNum", All_Platforms[Order Number],
    "Name", All_Platforms[User Name]
    )
    )
    )
    Return NumOfVisit
     
    //[NumOfReVisit]:  This is the same as above [NumOfVisit] but it excludes the first visit. hence the formular is simply [NumOfVisit]-1
     
    NumOfReVisit = [NumOfVisit]-1
     
    //[AVG_Time_Revisit_Indv]:  This is similar to the measure [AverageTime_Revisit] which I provided previously but it is revised to ignore bulk purchase under the same order number. But this measure cannot be used universal purpose(e.g. KPI card) since it needs specific "Name".  Just make this measure in your model but don't use this. this measure will be used internally to make the final new measure
     
    AVG_Time_Revisit_Indv =
    VAR currentuser = MAX( All_Platforms[User Name] )
    VAR NumberOfDates =
    CALCULATE(
    DATEDIFF(
    FIRSTNONBLANK( All_Platforms[OrderTime], All_Platforms[OrderTime] ),
    LASTNONBLANK( All_Platforms[OrderTime], All_Platforms[OrderTime] ),
    DAY
    ),
    All_Platforms[User Name] = currentuser
    )
    VAR NumberOfRePurcases =
    COUNTROWS(
    DISTINCT(
    SELECTCOLUMNS(
    FILTER( All_Platforms, All_Platforms[User Name] = currentuser ),
    "OrderNum", All_Platforms[Order Number],
    "Name", All_Platforms[User Name]
    )
    )
    ) - 1
    VAR Avg_Time_Interval =
    CALCULATE(
    DIVIDE( NumberOfDates, NumberOfRePurcases ),
    ALLSELECTED( all_Platforms )
    )
    Return Avg_Time_Interval
     
    //[Avg_Revisit_Interval]:  This is the one you can use to show revisit interval for individual as well as overall time interval (for KPI card)
     
    Avg_Revisit_Interval =
    AVERAGEX(
    ADDCOLUMNS(
    VALUES( All_Platforms[User Name] ),
    "AvgTime", [AVG_Time_Revisit_Indv] ),
    [AVG_Time_Revisit_Indv]
    )
     
    //[Time_till_next_visit ]:  This is the measure you can use along with speciifc date which shows the number of dates till next visit. e.g. if you select the date when Emil visited, this measure will show the number of dates till Emil's next visit
     
    Time_till_next_visit =
    VAR current_date = max(All_Platforms[OrderTime])
    VAR Next_visit =
        CALCULATE(
            min(All_Platforms[OrderTime]),
            All_Platforms[OrderTime] > current_date)
    RETURN DATEDIFF(current_date,Next_visit,DAY)
     
    //[Avg_Revist_Time_From]:  This is similar to above [Time_till_next_visit ] which you can use along with speciifc date. While [Time_till_next_visit ] shows the number of dates till next visit, this measure show average number of dates for revisit calculating till last visit date. You need to create the measure [NumOfVisit] first to create this measure
     
    Avg_Revist_Time_From =
    VAR current_date = max(All_Platforms[OrderTime])
    VAR Fianl_visit =
        CALCULATE(
            MAX(All_Platforms[OrderTime]),
            All_Platforms[OrderTime] > current_date)
    VAR DateDifference = DATEDIFF(current_date,Fianl_visit,DAY)
    VAR Num_visit =
        CALCULATE(
            [NumOfVisit],
            All_Platforms[OrderTime] > current_date)
    RETURN DIVIDE( DateDifference,Num_visit)
     
     
    Please mark this as solution if this helped you.
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello colacan,

 

wow, thanks a lot, again. The explanations are really, really helpful! 🙂

I tried to create the code myself but so far I got stuck with both issue 1 and 2. Would be great if you can help me with that again. Really brilliant from you, thanks!

 

 

I am super sorry to further bother but I discovered another problem.

Issue

I now edited the data and have for one specific day, with one order number, many purchases from one customer (emil) for one product (macbook in category laptop). That decreased the the average time between purchases from that user. However, the real average time between purchases has not changed. He just purchased more items within one order. 

How can I change the code to treat a single, large order (with one order number) as a single transaction?

 

Obviosuly also happy if anyone else would want to assist colacan with his great work so far:)

Thanks a lot and best regards!

Jan

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I still did not manage to solve that issue and would be super grateful if someone can assist! 🙂 

Thanks, community!

  • colacan's avatar
    colacan
    Icon for Resolver II rankResolver II
    4 years ago

    Anonymous  Hi, considering your issues I made measures as below. I hope this helps you.

     

    //[NumOfTotPurchase]: This counts all purchases even if order numbers are the same. e.g. if Emil purchased 4 macbooks on the same day under same oerder number, it still counts 4.

    NumOfTotPurchase = countrows(All_Platforms)   

    //[NumOfVisit]:  This counts only number of visit regardless number of purchases e.g. If Emil purchased 4 macbooks, if counts only 1 as long as all purchases are done under same order number
     
    NumOfVisit =
    VAR currentuser = MAX( All_Platforms[User Name] )
    VAR NumOfVisit =
    COUNTROWS(
    DISTINCT(
    SELECTCOLUMNS(
    FILTER( All_Platforms, All_Platforms[User Name] = currentuser ),
    "OrderNum", All_Platforms[Order Number],
    "Name", All_Platforms[User Name]
    )
    )
    )
    Return NumOfVisit
     
    //[NumOfReVisit]:  This is the same as above [NumOfVisit] but it excludes the first visit. hence the formular is simply [NumOfVisit]-1
     
    NumOfReVisit = [NumOfVisit]-1
     
    //[AVG_Time_Revisit_Indv]:  This is similar to the measure [AverageTime_Revisit] which I provided previously but it is revised to ignore bulk purchase under the same order number. But this measure cannot be used universal purpose(e.g. KPI card) since it needs specific "Name".  Just make this measure in your model but don't use this. this measure will be used internally to make the final new measure
     
    AVG_Time_Revisit_Indv =
    VAR currentuser = MAX( All_Platforms[User Name] )
    VAR NumberOfDates =
    CALCULATE(
    DATEDIFF(
    FIRSTNONBLANK( All_Platforms[OrderTime], All_Platforms[OrderTime] ),
    LASTNONBLANK( All_Platforms[OrderTime], All_Platforms[OrderTime] ),
    DAY
    ),
    All_Platforms[User Name] = currentuser
    )
    VAR NumberOfRePurcases =
    COUNTROWS(
    DISTINCT(
    SELECTCOLUMNS(
    FILTER( All_Platforms, All_Platforms[User Name] = currentuser ),
    "OrderNum", All_Platforms[Order Number],
    "Name", All_Platforms[User Name]
    )
    )
    ) - 1
    VAR Avg_Time_Interval =
    CALCULATE(
    DIVIDE( NumberOfDates, NumberOfRePurcases ),
    ALLSELECTED( all_Platforms )
    )
    Return Avg_Time_Interval
     
    //[Avg_Revisit_Interval]:  This is the one you can use to show revisit interval for individual as well as overall time interval (for KPI card)
     
    Avg_Revisit_Interval =
    AVERAGEX(
    ADDCOLUMNS(
    VALUES( All_Platforms[User Name] ),
    "AvgTime", [AVG_Time_Revisit_Indv] ),
    [AVG_Time_Revisit_Indv]
    )
     
    //[Time_till_next_visit ]:  This is the measure you can use along with speciifc date which shows the number of dates till next visit. e.g. if you select the date when Emil visited, this measure will show the number of dates till Emil's next visit
     
    Time_till_next_visit =
    VAR current_date = max(All_Platforms[OrderTime])
    VAR Next_visit =
        CALCULATE(
            min(All_Platforms[OrderTime]),
            All_Platforms[OrderTime] > current_date)
    RETURN DATEDIFF(current_date,Next_visit,DAY)
     
    //[Avg_Revist_Time_From]:  This is similar to above [Time_till_next_visit ] which you can use along with speciifc date. While [Time_till_next_visit ] shows the number of dates till next visit, this measure show average number of dates for revisit calculating till last visit date. You need to create the measure [NumOfVisit] first to create this measure
     
    Avg_Revist_Time_From =
    VAR current_date = max(All_Platforms[OrderTime])
    VAR Fianl_visit =
        CALCULATE(
            MAX(All_Platforms[OrderTime]),
            All_Platforms[OrderTime] > current_date)
    VAR DateDifference = DATEDIFF(current_date,Fianl_visit,DAY)
    VAR Num_visit =
        CALCULATE(
            [NumOfVisit],
            All_Platforms[OrderTime] > current_date)
    RETURN DIVIDE( DateDifference,Num_visit)
     
     
    Please mark this as solution if this helped you.