Basic-replaces-Monthly Subscriber Simulator

Date: 2026-05-15 Baseline: Aug 2025 – Jan 2026 monthly run-rate (last 6 months before monthly was sunset) Ground truth: Feb 2026 – May 2026 (actual post-change activity, partial data) Source: ClickHouse — sadb_invoices × sadb_transactions / sadb_points_transactions

This simulator estimates the change in net new subscriptions per package per month after removing the Bronze/Silver monthly packages and introducing Basic (999 SAR/year) as a lower-priced yearly anchor. Every knob is wired to live recompute; presets seed scenarios from real conversion data.

Note

Counts net subscriptions, not gross. Each cell is INVOICE (+1) − CREDIT_NOTE (−1), matching the reference query. That removes refunds and chargebacks so the output is kept subscribers, not sales attempts.

What the simulator models

For each baseline monthly subscriber (Bronze-M or Silver-M new + renewal), the model distributes them across five destinations when their cycle ends:

1. Buy Basic — convert down to the new 999 SAR/year tier.
2. Upgrade to Bronze yearly — pay 1,999 SAR/year (or 1,600 renewal).
3. Upgrade to Silver yearly — pay 5,999 SAR/year (or 4,800 renewal).
4. Upgrade to Golden yearly — pay 15,999 SAR/year.
5. Churn — drop subscription entirely.

A separate cannibalization knob captures users who would have bought Bronze-Y or Silver-Y at full price but downgrade to Basic instead. A Basic uplift knob adds genuinely new subscribers attracted by the lower price point. Yearly-renewal disruption captures the share of renewing yearly users who now jump down to Basic at renewal.

Historical monthly retention — the critical baseline

Before reading the post-change numbers, anchor on what monthly retention actually looked like when monthly was available. The 2024 – Jan 2026 cohort retention curve (n = 5,392 users whose first monthly invoice was on or before 2025-07-01, giving ≥6 months of forward observation):

Months after first monthly invoice	% of cohort still paying
Month 0 (first month)	100.0%
Month 1	17.2%
Month 2	13.7%
Month 3	11.7%
Month 6	9.1%
Month 12	4.1%

Median tenure = 1 month. Mean tenure = 2.02 months. Roughly 83% of monthly subscribers churned after a single month even when monthly was an option — they treated Bronze-M / Silver-M as a one-month trial, not an ongoing subscription.

Composition of the Aug 2025 – Jan 2026 calibration cohort (n = 2,930):

Tenure bucket	Users	%
1 month only	1,330	45.4%
2 months	485	16.6%
3 – 5 months	632	21.6%
6+ months	483	16.5%

Caution

Reframe the “69% not yet returned” number. 45% of the calibration cohort were 1-month-only users who historically never renewed, monthly available or not. The marginal churn caused by removing monthly is much smaller than the surface number suggests — most of the missing 69% would have left anyway.

Real-world calibration (Feb–May 2026)

Of 2,930 unique monthly subscribers active Aug 2025 – Jan 2026, here’s what they actually did once monthly was removed (measured through 2026-05-15):

Destination	Users	Rate	vs. historical baseline
→ Basic	527	18.0%	New option — no pre-removal analogue
→ Bronze-Y (new)	211	7.2%	New upgrade path
→ Silver-Y (new)	51	1.7%	New upgrade path
→ Golden-Y (new)	3	0.1%	New upgrade path
→ Legacy monthly renewal	115	3.9%	Compare to historical 17.2% Month-1 renew
Not yet returned	2,023	69.0%	Compare to historical ~83% Month-1 drop

Apples-to-apples retention: total post-removal “stayed paying” rate = 18.0% + 7.2% + 1.7% + 0.1% + 3.9% = 30.9%. Historical Month-1 renewal alone was 17.2%. By the end of the observation window the new mix is retaining nearly 2× more of the monthly cohort than the old monthly product retained at the same point in their lifecycle — and that’s before counting any uplift from new buyers attracted by the Basic price point.

The “Realistic” preset uses post-removal observed rates with a churn assumption (62%) that already aligns with the historical 1-month-and-done baseline. The “Retention-adjusted” preset goes further: it separates the always-going-to-churn 45% from the could-have-been-retained 55%, so the conversion knobs apply only to the latter.

Loading simulator…

Model assumptions and caveats

• Net subscribers per month is the unit. The reference query uses SUM(IF type='INVOICE', +1, -1) so refunds/credit notes are netted out — these are kept subscribers, not gross sales.
• High monthly-pool churn is structural, not caused by removing monthly. Pre-removal, 83% of new monthly subs were already churning after Month 1 (17.2% Month-1 renewal, dropping to 9.1% by Month 6). The “Realistic” preset’s 62% churn rate isn’t a sign of damage from the change — it’s just the natural fate of monthly subscribers. The “Retention-adjusted” preset separates the always-leaving 45% from the retainable 55% explicitly.
• Revenue is order-of-magnitude. We multiply net monthly subs × horizon × annual price. This double-counts a renewing yearly subscriber who would only pay once a year — fine as a relative comparison between baseline and scenario, but don’t read the absolute SAR number as accounting truth.
• Monthly pool combines new + renewal Bronze-M and Silver-M. The disposition knobs apply uniformly across this pool; in reality Silver-M users are more likely to upgrade to Silver-Y than to drop to Basic, and 6+ month “loyal” monthlies behave very differently from 1-month-only users — but the aggregate effect is captured.
• Uplift is expressed as % of the monthly pool size so a value of 100% means “Basic doubles the net new-subscriber flow vs the pool that monthly used to generate.” This keeps the unit comparable to the cannibalization knobs.
• No time-dynamics. The simulator gives a steady-state monthly run-rate, not a transition curve. Real-world numbers (Feb 2026) show a sharp spike in Basic as backlog converts, then a settle — model the steady state by sliding Basic uplift lower.

Source query

SELECT
  toStartOfMonth(invoices.payment_time) as month,
  CASE COALESCE(ft.transaction_type, pt.transaction_type)
    WHEN 89 THEN 'Basic (N)'
    WHEN 1  THEN 'Bronze-Y (N)'  WHEN 36 THEN 'Bronze-Y (R)'
    WHEN 3  THEN 'Silver-Y (N)'  WHEN 38 THEN 'Silver-Y (R)'
    WHEN 90 THEN 'Golden-Y (N)'  WHEN 95 THEN 'Golden-Y (R)'
    WHEN 2  THEN 'Pro-Y (N)'     WHEN 37 THEN 'Pro-Y (R)'
    WHEN 54 THEN 'Bronze-M (N)'  WHEN 72 THEN 'Bronze-M (R)'
    WHEN 74 THEN 'Silver-M (N)'  WHEN 86 THEN 'Silver-M (R)'
    WHEN 73 THEN 'Pro-M'
  END AS type,
  SUM(IF(invoices.type = 'INVOICE', 1, -1)) as subscriptions
FROM sadb_invoices invoices FINAL
LEFT JOIN sadb_transactions ft FINAL
  ON invoices.transaction_id = ft.transaction_id AND invoices.payment_type = 'FIAT'
LEFT JOIN sadb_points_transactions pt FINAL
  ON invoices.transaction_id = pt.id AND invoices.payment_type = 'POINTS'
WHERE invoices._peerdb_is_deleted = 0
  AND toDate(invoices.payment_time) >= '2024-01-01'
  AND COALESCE(ft.transaction_type, pt.transaction_type)
      IN (1, 2, 3, 36, 37, 38, 89, 90, 95, 54, 72, 73, 74, 86)
GROUP BY 1, 2
ORDER BY 1, 2

Calibration query (monthly users’ actual post-change behavior)

-- Of monthly subscribers active Aug 2025–Jan 2026, what did they do once
-- monthly was removed? Measured 2026-02-01 → 2026-05-15.
WITH last_monthly_users AS (
  SELECT DISTINCT ft.user_id AS uid
  FROM sadb_invoices i FINAL
  JOIN sadb_transactions ft FINAL ON i.transaction_id = ft.transaction_id AND i.payment_type = 'FIAT'
  WHERE i._peerdb_is_deleted = 0 AND i.type = 'INVOICE'
    AND toDate(i.payment_time) BETWEEN '2025-08-01' AND '2026-01-31'
    AND ft.transaction_type IN (54, 72, 74, 86)
),
post AS (
  SELECT ft.user_id AS uid, ft.transaction_type AS tt
  FROM sadb_invoices i FINAL
  JOIN sadb_transactions ft FINAL ON i.transaction_id = ft.transaction_id AND i.payment_type = 'FIAT'
  WHERE i._peerdb_is_deleted = 0 AND i.type = 'INVOICE'
    AND toDate(i.payment_time) BETWEEN '2026-02-01' AND '2026-05-15'
)
SELECT
  multiIf(tt = 89, 'Basic',
          tt IN (1, 36),  'Bronze-Y',
          tt IN (3, 38),  'Silver-Y',
          tt IN (90, 95), 'Golden-Y',
          tt IN (72, 86), 'Legacy monthly renewal',
          'Other')                              AS destination,
  uniqExact(p.uid)                              AS users
FROM last_monthly_users l LEFT JOIN post p ON l.uid = p.uid
GROUP BY destination ORDER BY users DESC

Historical monthly-retention curve

-- Month-by-month retention for new monthly subscribers (cohort with ≥6 months
-- of forward observation, i.e. first month on or before 2025-07-01).
WITH monthly_invoices AS (
  SELECT
    ft.user_id AS uid,
    toStartOfMonth(i.payment_time) AS mo
  FROM sadb_invoices i FINAL
  JOIN sadb_transactions ft FINAL
    ON i.transaction_id = ft.transaction_id AND i.payment_type = 'FIAT'
  WHERE i._peerdb_is_deleted = 0 AND i.type = 'INVOICE'
    AND toDate(i.payment_time) BETWEEN '2024-01-01' AND '2026-01-31'
    AND ft.transaction_type IN (54, 72, 74, 86)
),
first_mo AS (
  SELECT uid, min(mo) AS first_mo
  FROM monthly_invoices
  GROUP BY uid
),
cohort AS (
  SELECT mi.uid, mi.mo, fm.first_mo,
         dateDiff('month', fm.first_mo, mi.mo) AS month_offset
  FROM monthly_invoices mi
  JOIN first_mo fm ON mi.uid = fm.uid
  WHERE fm.first_mo <= '2025-07-01'
)
SELECT
  month_offset,
  uniqExact(uid) AS users,
  round(uniqExact(uid) * 100.0
        / (SELECT uniqExact(uid) FROM first_mo WHERE first_mo <= '2025-07-01'), 1)
        AS pct_of_cohort
FROM cohort
GROUP BY month_offset
ORDER BY month_offset

Tenure composition of the calibration cohort

-- For the Aug 2025-Jan 2026 cohort, count how many distinct months
-- each user has ever been an active monthly subscriber (2024 onwards).
WITH cohort AS (
  SELECT DISTINCT ft.user_id AS uid
  FROM sadb_invoices i FINAL
  JOIN sadb_transactions ft FINAL
    ON i.transaction_id = ft.transaction_id AND i.payment_type = 'FIAT'
  WHERE i._peerdb_is_deleted = 0 AND i.type = 'INVOICE'
    AND toDate(i.payment_time) BETWEEN '2025-08-01' AND '2026-01-31'
    AND ft.transaction_type IN (54, 72, 74, 86)
),
tenure AS (
  SELECT
    ft.user_id AS uid,
    countDistinct(toStartOfMonth(i.payment_time)) AS months_ever
  FROM sadb_invoices i FINAL
  JOIN sadb_transactions ft FINAL
    ON i.transaction_id = ft.transaction_id AND i.payment_type = 'FIAT'
  WHERE i._peerdb_is_deleted = 0 AND i.type = 'INVOICE'
    AND ft.user_id IN (SELECT uid FROM cohort)
    AND ft.transaction_type IN (54, 72, 74, 86)
    AND toDate(i.payment_time) >= '2024-01-01'
  GROUP BY ft.user_id
)
SELECT
  multiIf(months_ever = 1, '1 month only',
          months_ever = 2, '2 months',
          months_ever BETWEEN 3 AND 5, '3-5 months',
          '6+ months') AS tenure_bucket,
  count() AS users,
  round(count() * 100.0 / (SELECT count() FROM cohort), 1) AS pct
FROM tenure
GROUP BY tenure_bucket
ORDER BY users DESC