RaqForum 80 No.
2 Reply  •  17 View  • 
     ...

Calculate customer churn rate

 
calculate(27) SPL(55)

The following is the historical sales contract record Contract.txt of a certain enterprise:

..

Customers who had sales last year but not this year are called "lost customers" for this year. The number of "lost customers" divided by the total number of customers last year is the "customer churn rate" for this year. Please find the "lost customers" for 2024 and calculate the "customer churn rate".

First, select all contracts for 2023 and 2024 and list the annual customer list. Subtracting the 2024 list from the 2023 list results in customer churn. The customer churn rate can be obtained by dividing the number of lost customers by the total number of customers in 2023.

A

1

=T("Contract.txt")

2

=A1.select(year(SellDate)==2023).id(Client)

3

=A1.select(year(SellDate)==2024).id(Client)

4

=A2\A3

5

=A4.len()/A2.len()

https://try.esproc.com/splx?5aH

A1 reads historical sales contract data.

A2 and A3 respectively list the customers for 2023 and 2024. The id function is used to remove duplicates, and the results are as follows:

.. ..

A4 calculates the difference set of the two sets to obtain the list of lost customers:

..

A5 calculates the customer churn rate, and the result is as follows:

..


calculate(27) SPL(55)
 
 •  17 View  •