Calculation of customer growth rate under renewal mode
An antivirus software company's products are sold on a time-based purchase basis, and users must continuously renew their "license keys" to continue using the software. "License keys" are divided into six-month authorization, one-year authorization, and lifetime authorization. The details are as follows:
| ID | Key Type | TermOfValidity | Price |
|---|---|---|---|
| 1 | Six months | 6 months | 20 |
| 2 | One Year | 12 months | 36 |
| 3 | Unlimited | Infinity | 500 |
The following is the sales record table AVwareSales.txt of the company in the first few years after implementing this sales mode:
If the customer's authorization expires without renewing the authorization, the software will be discontinued and the user will no longer be considered a company customer. Please calculate the annual customer growth rate of the company over the past few years.
This issue involves year-end settlement, and the year is not fixed, so it needs to loop by day, and at the same time, another table sequence is used to synchronously record the current customers. In the loop of each day, expired users are deleted, new users are added, and old users are renewed. Then, the number of users can be settled at the end of the year. After the loop ends, the growth rate of customer numbers for each year can be calculated using the result table.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | =T("AVwareSales.txt").sort(Date) | >s=date(A1.Date),e=date(A1.m(-1).Date),days=e-s | ||
| 2 | 1 | elapse@m(?,6) | ||
| 3 | 2 | elapse@y(?,1) | ||
| 4 | 3 | 'null | =create(ID,Pattern).record([A2:B4],0).keys(ID) | |
| 5 | =create(User,ExpDate).keys(User) | =create(Year,UsersNum,GrowthRate) | ||
| 6 | for 0,days | >d=s+A6 | >A5.delete(A5.pselect@a(ExpDate!=null && ExpDate<d)) | |
| 7 | for A1.select(date(Date)==d) | =A5.find(B7.User) | =C4.find(B7.KeyID).Pattern | |
| 8 | if C7==null | >A5.insert(0,B7.User,eval(D7,d)) | ||
| 9 | else | >C7.run(ExpDate=if(ExpDate, eval(D7,ExpDate))) | ||
| 10 | if d==e || (day(d)==31 && month(d)==12) | >C5.insert(0,year(d),A5.count()) | ||
| 11 | =C5.run(if(#>1,GrowthRate=round((UsersNum-UsersNum[-1])/UsersNum[-1],2))) |
https://try.esproc.com/splx?4Uh
A1 reads the software sales records and sorts them by order date. C1 prepares some variables for calculation, where s is the earliest order date, e is the latest order date, and days is the date difference used for loop calculation.
A2~B4, write the validity extension formula for each key based on different authorization modes, and use the @m and @ y options of the elapse function to calculate the result after 6 months or 1 year. If it is a lifetime authorization, use null to represent it. C4 organizes authorization information into a table sequence:
A5 creates a table sequence to record the current valid users, while C5 creates a result table to record the annual number and growth rate of users.
Starting from A6, loop according to the duration of the order. In the loop, B6 calculates the current order date d, and C6 deletes customers whose validity period has expired on that day. B7 loops all sales records for the day, C7 searches for user names, D7 searches for corresponding authorization calculation modes. If there is no record in the valid user table, generate a new valid user record, and the validity period is calculated based on the order date. If the user already exists, the validity period will be extended based on the existing data. When B10 determines the end of the year or completion of the loop, C10 writes the current number of customers into the result table of C5.
During the loop of orders, A5 continuously records customer information that is still valid:
After the loop ends, A11 calculates the annual customer growth rate based on the number of customers of each year in C5.
