Calculate Client Churn Rate
l Problem
All records about the sales contracts of an enterprise are listed as below:
The lost clients in a year are the clients whose sales values in the Amount field for the previous year are not 0 but those for the current year are 0. By dividing the number of lost clients in a year by the total number of clients of the previous year, you can get the churn rate in that year. Please count the lost clients in 1998 and calculate the client churn rate.
l Tip
General steps: Because one or more contracts may have been signed with one client, and the one who have not signed any contract shall not be regarded as a client, group function is needed to group the records by client, and then the computation of the client churn rate will be much easier.
1. Select all contracts signed in 1997, group these records by client, and list the clients;
2. Similarly, select all contracts signed in 1998, and list the clients;
3. Subtract the clients in 1998 from the clients in1997, the result will be the lost clients;
4. Divide the number of lost clients by the total clients of 1997. Then, you will get the churn rate.
l Code
|
|
A |
|
|
1 |
=file("C:\\txt\\Contract.txt").import@t() |
The Contract table |
|
2 |
=A1.select(year(SellDate)==1997) |
Select the contracts signed in 1997 |
|
3 |
=A2.group(Client;) |
Group by Client, and list clients |
|
4 |
=A1.select(year(SellDate)==1998) |
Select the contracts signed in 1998 |
|
5 |
=A4.group(Client;) |
Group by Client, and list clients |
|
6 |
=A3.(Client)\A5.(Client) |
Remove clients in the list for 1998 from the list for 1997; the result is the lost clients |
|
7 |
=A6.len()/A3.len() |
Calculate the rate of client churn |
l Result
Lost clients
Client churn rate
