Forum Discussion

Fangyi8's avatar
Fangyi8
Frequent Visitor
3 years ago
Solved

Client Classification

Hi guys,

please I need your help with classifying customers according to the following criteria,

New client - made a one purchase in the month of December 2023,

Active client - made a purchase this month and has made 2 or more purchases in the last 60 days,

Passive client - made a purchase one purchase this month and 1 purchase in the last 60 days,

Returning client - made a purchase this month but hasn't made a purchase in the last 60 days.

Lost client - Hasn't made any purchase in the last 60 days.

 

I am attaching sample data but if you want to see the whole thing, I can send it.

Deal StageDeal idDate of the dealName of clients
Payment received6202.04.2022ООО "Аделит"
Payment received17012.09.2022ООО "Аделит"
Payment received23817.12.2022ООО "Аделит"
Payment received25907.01.2023ООО "Аделит"

 

  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Fangyi8 

     

    You can try the following methods.
    Sample data:

    Classification = 
Var _currentmonth=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',MONTH([Date of the deal])=MONTH(TODAY())
                                                                            &&[Name of clients]=EARLIER('Table'[Name of clients])))
Var _newclient=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',[Name of clients]=EARLIER('Table'[Name of clients])))
Var _last60day=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',[Date of the deal]>=TODAY()-60
                                                                         &&[Date of the deal]<MAX('Table'[Date of the deal])
                                                                        &&[Name of clients]=EARLIER('Table'[Name of clients])))
Return
SWITCH(TRUE(),
_currentmonth=1&&_newclient=1,"New client",
_currentmonth>=1&&_last60day>=2,"Active client",
_currentmonth=1&&_last60day=1,"Passive client",
_currentmonth>=1&&_last60day=BLANK(),"Returning client",
_last60day=BLANK(),"Lost client")

    Please check the attachment.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, Fangyi8 

     

    You can try the following methods.
    Sample data:

    Classification = 
Var _currentmonth=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',MONTH([Date of the deal])=MONTH(TODAY())
                                                                            &&[Name of clients]=EARLIER('Table'[Name of clients])))
Var _newclient=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',[Name of clients]=EARLIER('Table'[Name of clients])))
Var _last60day=CALCULATE(COUNT('Table'[Date of the deal]),FILTER('Table',[Date of the deal]>=TODAY()-60
                                                                         &&[Date of the deal]<MAX('Table'[Date of the deal])
                                                                        &&[Name of clients]=EARLIER('Table'[Name of clients])))
Return
SWITCH(TRUE(),
_currentmonth=1&&_newclient=1,"New client",
_currentmonth>=1&&_last60day>=2,"Active client",
_currentmonth=1&&_last60day=1,"Passive client",
_currentmonth>=1&&_last60day=BLANK(),"Returning client",
_last60day=BLANK(),"Lost client")

    Please check the attachment.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.