Sales rose. Why did recognized revenue fall?
I traced a subscription revenue decline through plan mix, purchase batches, and the difference between new purchases and renewals.
- My role
- End-to-end diagnosis
- Original tools
- SQL · Excel · Tableau
- Deliverable
- Diagnosis & recommendations
The problem
An annual-plan promotion supported purchase-day sales, but recognized revenue was falling. Which part of the business needed attention?
What I did
- Separated purchase-day revenue from revenue spread over the membership period.
- Traced monthly purchase batches, then checked renewal performance, active users, and active purchase rates.
A stronger sales period did not guarantee stronger recognized revenue.
Purchase-day revenue rose 13.9%, while recognized revenue fell 1.9%. The first assigns the full price to the purchase date; the second spreads it over the membership period.
Purchase-day sales and recognized revenue moved apart
View data & definitions
Purchase-day revenue assigns full plan price at purchase. Recognized revenue allocates it over 31 monthly or 365 annual membership days. Both series use a shared index scale, with a focused domain of 88–122. Daily averages compare Dec 3–12 with Dec 13–22. CU means currency units.
| Date | Purchase-day revenue (CU) | Recognized revenue (CU) |
|---|---|---|
| 2022-12-03 | 33,870 | 45,890 |
| 2022-12-04 | 36,381 | 46,132.1 |
| 2022-12-05 | 35,712 | 46,349.5 |
| 2022-12-06 | 35,097 | 46,541.6 |
| 2022-12-07 | 35,280 | 46,744 |
| 2022-12-08 | 36,147 | 46,977.6 |
| 2022-12-09 | 36,606 | 47,218.3 |
| 2022-12-10 | 36,099 | 47,431.8 |
| 2022-12-11 | 35,286 | 47,623.4 |
| 2022-12-12 | 35,187 | 47,830 |
| 2022-12-13 | 40,407 | 47,509.5 |
| 2022-12-14 | 40,932 | 47,190.2 |
| 2022-12-15 | 40,821 | 46,842.3 |
| 2022-12-16 | 40,392 | 46,478.6 |
| 2022-12-17 | 40,305 | 46,134.9 |
| 2022-12-18 | 40,470 | 45,817 |
| 2022-12-19 | 40,497 | 45,492.8 |
| 2022-12-20 | 40,272 | 45,139 |
| 2022-12-21 | 40,383 | 44,776.5 |
| 2022-12-22 | 40,776 | 44,439.1 |
What this changed: A promotion-period sales increase was insufficient to judge the recurring revenue base. I separated annual and monthly contributions.
See how monthly and annual plans contributed
Annual-plan growth did not offset the monthly-plan decline
View data & definitions
| Plan | Dec 3–12 daily recognized revenue (CU) | Dec 13–22 daily recognized revenue (CU) |
|---|---|---|
| Monthly plans | 31,445.7 | 30,235.9 |
| Annual plans | 15,428.1 | 15,746.1 |
| Total | 46,873.8 | 45,982 |
Stronger November batches were being replaced by smaller December batches.
Monthly orders contribute revenue for 31 days. When a large purchase batch expires, its contribution disappears. The heatmap showed why current sales needed to replace the strength of earlier purchases.
Older purchase batches expire. Their replacements matter.
Each row is a reporting date. Each column is a day of membership. An outlined diagonal follows one purchase batch across dates.
On a small screen, scroll the matrix sideways or use the selectors.
4,195 monthly orders are on day 28 of membership on December 13, 2022. They contribute 1,217.9 CU that day. Their final contribution is on December 16, 2022.
View the selected reporting day’s data
| Membership day | Purchase date | Active orders | Daily recognized revenue (CU) |
|---|---|---|---|
| 1 | 2022-12-13 | 2,921 | 848 |
| 2 | 2022-12-12 | 3,043 | 883.5 |
| 3 | 2022-12-11 | 3,028 | 879.1 |
| 4 | 2022-12-10 | 3,075 | 892.7 |
| 5 | 2022-12-09 | 3,114 | 904.1 |
| 6 | 2022-12-08 | 3,089 | 896.8 |
| 7 | 2022-12-07 | 3,036 | 881.4 |
| 8 | 2022-12-06 | 3,033 | 880.5 |
| 9 | 2022-12-05 | 3,084 | 895.4 |
| 10 | 2022-12-04 | 3,115 | 904.4 |
| 11 | 2022-12-03 | 2,810 | 815.8 |
| 12 | 2022-12-02 | 2,760 | 801.3 |
| 13 | 2022-12-01 | 2,769 | 803.9 |
| 14 | 2022-11-30 | 2,723 | 790.5 |
| 15 | 2022-11-29 | 2,743 | 796.4 |
| 16 | 2022-11-28 | 2,700 | 783.9 |
| 17 | 2022-11-27 | 4,157 | 1,206.9 |
| 18 | 2022-11-26 | 4,177 | 1,212.7 |
| 19 | 2022-11-25 | 4,231 | 1,228.4 |
| 20 | 2022-11-24 | 4,240 | 1,231 |
| 21 | 2022-11-23 | 4,190 | 1,216.5 |
| 22 | 2022-11-22 | 4,155 | 1,206.3 |
| 23 | 2022-11-21 | 4,186 | 1,215.3 |
| 24 | 2022-11-20 | 4,237 | 1,230.1 |
| 25 | 2022-11-19 | 4,234 | 1,229.2 |
| 26 | 2022-11-18 | 4,181 | 1,213.8 |
| 27 | 2022-11-17 | 4,156 | 1,206.6 |
| 28 | 2022-11-16 | 4,195 | 1,217.9 |
| 29 | 2022-11-15 | 4,242 | 1,231.5 |
| 30 | 2022-11-14 | 4,227 | 1,227.2 |
| 31 | 2022-11-13 | 4,173 | 1,211.5 |
What this changed: The decline reflected which monthly orders were entering and leaving the revenue base, not just the promotion running that day.
Renewals improved. Active purchases were the weaker link.
Renewal rate rose from 74.8% to 78.0%, and renewal volume increased. Meanwhile, fewer foreground active users and a lower active purchase rate—from 0.300% to 0.255%—reduced active monthly purchases.
Locate the decline before selecting a response
View data & definitions
Foreground DAU is the denominator of the active purchase rate; renewal-eligible memberships are the denominator of renewal rate. Behavior aggregates are rounded examples tracking the monthly order levels; small daily ledger oscillations are omitted. Active purchasers plus automatic renewals make up the monthly order example.
| Period | Foreground DAU | Active monthly purchasers | Active purchase rate | Renewal-eligible | Automatic renewals | Renewal rate |
|---|---|---|---|---|---|---|
| Dec 3–12 | 400,000 | 1,200 | 0.300% | 2,500 | 1,870 | 74.8% |
| Dec 13–22 | 340,000 | 866 | 0.255% | 2,600 | 2,028 | 78.0% |
Recommendation: Focus investigation on active purchase traffic and conversion, while monitoring renewal health separately.
Address the weak revenue driver before changing the promotion.
What I delivered: A diagnosis connecting revenue definitions, plan mix, purchase batches, and purchasing behavior. The analysis recommended monitoring the revenue base and improving active purchase performance; it did not measure the promotion’s incremental effect or a financial saving.
Revenue definitions, assumptions & source notes
Based on my historical financial-revenue investigation. Original analysis: SQL, Excel, and Tableau. The English narrative, Python/vector charts, and interactive batch matrix are portfolio reconstructions.
- Purchase-day revenue follows the original report’s business-revenue view. It is not presented as audited cash flow or profit.
- The replacement ledger has a 9 CU monthly plan lasting 31 days and a 78 CU annual plan lasting 365 days. Monthly recognized revenue is the sum of active monthly orders × 9 ÷ 31; annual recognized revenue is active annual orders × 78 ÷ 365.
- The model excludes refunds, tax, currency changes, discounts, and partial days. A pre-existing annual membership base remains active throughout the display.
- The matrix is an order-contribution view. Repeated diagonal values are the same batch remaining entitled, not customer retention observations.
- The historical review discussed acquisition changes and external disruption; one later conversion decline remained unresolved. This presentation does not assign a controlled causal effect to those factors or rule out annual-plan cannibalization.
Original reports, internal accounting tables, SQL, and customer records remain private.
Download chart data and the contribution matrix