Calculate User Growth Rate under Recurring Charge Mode
Problem
An antivirus software company’s product is sold by billing users according to different authorization terms, a sales mode in which a user must continually buy “license key” so that he/she can continue using the software. The “license key” includes half-a-year authorization, one-year authorization and lifetime authorization. For details, please see the following data:
| 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 of this company in the first few years after the adoption of this kind of sales mode:
If the user fails to continue buying the authorization upon the expiration of the preceding authorization, then the company stops the authorization of the software and no longer deems this user as a customer. Please calculate the annual user growth rate of this company in the first a few years.
Tip
General steps: As this question involves the year-end settlement and the years are uncertain, it is necessary to run a loop by days; and at the same time, use another table sequence to synchronously record the current customers. In the loop of each day, delete expired users, add new customers, renew charges for old users, and just settle the number of users at the end of the year.
1. Firstly write out the valid time extension of each kind of key and write them into a table sequence. As the time extension is uncertain (such as, a solar month of 31 days, a solar month of 30 days, common year, and leap year), here we use after function to write down the expressions for calculating the time of extensions, which can be invoked directly in subsequent calculations.
2. Firstly do some preparatory work before the loop. This includes creating a table sequence that records the current valid users and establishing a resulting table sequence that records the annual number of users and growth rate.
3. Firstly set the scope for the loop by day, namely, the date scope of the sales records.
4. loop through each day within the specified time interval. In the loop body, firstly delete the expired user for the current day.
5. Find out the sales records of the current day.
6. Loop through these records and find out the corresponding customers in the customer table. If there is none, create one.
7. According to the kind of key they bought in the sales record, calculate the validity period for the users.
8. At the end of each year, write the number of the current customers into the preceding resulting table.
9. After the loop is over, use the resulting table to calculate the growth rate of the number of customers each day.
Code
| A | B | C | D | ||
|---|---|---|---|---|---|
| 1 | =file("C:\\txt\\AVwareSales.txt").import@t().sort(Date) | sales record table | |||
| 2 | /ID | /Pattern | write out the equations of various kinds of Keys for future use. | ||
| 3 | 1 | elapse@m(?,6) | |||
| 4 | 2 | elapse@y(?,1) | |||
| 5 | 3 | ’null | |||
| 6 | =create(ID,Pattern).record([A3:B5],0) | =A6.keys(ID) | write KeyID and equation into the table sequence. | ||
| 7 | =create(User,TermOfValidity) | =A7.keys(User) | create a current user table. | ||
| 8 | =create(Year,NoOfUsers,GrowthRate) | create a result table that records growth rate. | |||
| 9 | =date(A1.Date) | =date(A1.m(-1).Date) | loop the start date and the end date | ||
| 10 | for | >A7.delete(A7.select(TermOfValidity!=null && TermOfValidity<A9)) | loop each day. Delete users having exceeded the validity date on the current day. | ||
| 11 | =A1.select(date(Date)==A9) | find out the sales record of the current day. | |||
| 12 | for B11 | =A7.find(B12.User) | loop these sales records, and find out the corresponding sales record. | ||
| 13 | if C12==null | >A7.insert(0,B12.User,A9) | if there is none, create a record. | ||
| 14 | >C12=A7.m(-1) | ||||
| 15 | >C12.run(TermOfValidity=eval(A6.find(B12.KeyID).Pattern,TermOfValidity)) | according to the corresponding equation of key, calculate new validity period. | |||
| 16 | if month(A9)==12 && day(A9)==31 | at the end of the year | |||
| 17 | >A8.insert(0,year(A9),A7.count()) | write down the number of the current users. | |||
| 18 | >A9=elapse@d(A9,1) | loop variable increases by one day. | |||
| 19 | if A9>B9 | break | if the loop exceeds the date scope of the sales record, just end the loop. | ||
| 20 | =A8.run(if(#>1,GrowthRate=round((NoOfUsers-NoOfUsers[-1])/NoOfUsers[-1],2))) | calculate out the annual customer growth rate. |
Result
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
Chinese version