In-Depth Customer Churn, New Customers & Lost Customers Examples In Power BI


#1

#2

Thanks for the reply, it would be really helpful if you can provide the PBI files so that i can go through it.


#3

Is there anyone who can help me out getting the PBI files? Thanks in advance.


#4

You just need to click on the link and it will take you to where you can download the files.

Chrs, Sam


#5

Thanks, i got your file and its really helpful. I covered lots of KPI’s based from the same file.

Further, i required a similar kind of a help to calculate “Returning Customers”.

Returning Customers = 

VAR vCustomerList =
    VALUES ( 'Customer Segmentation'[UniqueCustomer] )

RETURN
    COUNTROWS (
        FILTER (
            vCustomerList,
            CALCULATE (
                COUNTROWS ( 'Customer Segmentation' ),
                FILTER (
                    ALLSELECTED ( 'Date' ),
                    'Date'[DateAlternateKey] > ( MIN ( 'Date'[DateAlternateKey] ) - [Churn Time Frame Value] )
                 && 'Date'[DateAlternateKey] < MIN ( 'Date'[DateAlternateKey] )
                )
            )
            = 0
        )
    )

How could we convert the same measure based on CALCULATETABLE function?


#6

Did you get the chance to look for this request? Thanks in advance.


#7

You need to define more what a returning customer actually is in your scenario?

Here’s is a long thread where this is covered somewhat

You also need to use a technique like the below, not what you have reference above

Won Clients LTM = 
VAR CustomerLTM = CALCULATETABLE(VALUES('YH Invoices'[Customer]),
                    DATESBETWEEN ('Calendar'[FullDate],
                         [First Date Current Period],[Last Date Current Period]) )
VAR PriorCustomers = CALCULATETABLE(VALUES('YH Invoices'[Customer]),
                       DATESBETWEEN('Calendar'[FullDate],
                        [First Date Prior Period], [Last Date Prior Period] ) )             
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM,PriorCustomers))

You need to adjust the two variables for whatever time frame you require for your returning customers.

Thanks
Sam


#8

I will have a look and go through the content in detail.

In the mean while, i am struggling to find “One Time Customers” in their life span but somehow i am not getting the right results;

One Timer Customers = 

VAR vCustomerLists = VALUES('Customer Segmentation'[UniqueCustomer])

VAR vNewCustomer =
    CALCULATETABLE(
        VALUES('Customer Segmentation'[UniqueCustomer]),
        FILTER (
            ALL ('Date'),
           'Date'[DateAlternateKey] < MIN ( 'Date'[DateAlternateKey])
        )
    )

VAR vNewCustomerNew =
    CALCULATETABLE(
        vNewCustomer,
        FILTER (
            vNewCustomer,
            CALCULATE (
                COUNTROWS ( VALUES ( 'Customer Segmentation'[UniqueCustomer] ) ),
                FILTER (
                    ALL ( 'Date' ),
                    'Date'[DateAlternateKey] < MIN ( 'Date'[DateAlternateKey] )
                )
            )
            = 1
        )
    )
	
RETURN

    COUNTROWS(
        EXCEPT(
            vCustomerLists,
            vNewCustomerNew
        )
    )	

Please could you shed some light on it, how can i achieve that? Thanks in advance.


#9

I’m really not sure what you mean by one time customers?

Can you actually show an example here to give a guide as to the context the formula is being used in.


#10

Thanks for the reply.

“One Time Customers” means who have bought only one times in his entire life span.

If it make sense, please let me know, if not then i will send you the example.

Regards

Adil


#11

Please see the snapshot below for the one time customer;

Regards

Adil


#12

I’m still not sure how you want to visualize the information, but really this should be to difficult if you just want the list.

You just need to work out how many purchases all customer have, then filter out all the ones which are greater than 1

Total Purchases = 
COUNTROWS( Sales )

Single Purchase = 
CALCULATE( [Total Purchases],
    FILTER( Customer, [Total Purchases] = 1 ))

Thanks
Sam


#13

Is it possible to find the SECOND visit statistics using this? That first return visit for a customer specifically.


#14

Yes this is possible.

Check out the below link and 28:44

This will give you ideas around how to craft a formula that finds the second purchase.

If you are unable to work it out from here will probably need to see more around the exact scenario you are working with.

You’ll see though with the formula I’ve showcased here the idea is to first work out the first date a purchase happened (have this in a separate measure), then work out the min date of a purchase AFTER that initial date.

See how you go with these ideas.

Thanks
Sam


closed #15