Monthly vs Yearly Subscription Analysis

Date: 2026-01-26 Analysis Period: Full history (Feb 2020 - Jan 2026) Data Source: sadb_subs

Executive Summary

This analysis examines the shift from yearly to monthly subscriptions since the launch of monthly billing in July 2024, and tracks conversion patterns between subscription types.

Important Context: When a subscription lapses (is not renewed), users lose their active listings. This creates strong renewal pressure but also makes monthly subscriptions higher-risk for users who may forget to renew.

Key Metrics

Metric	Value
Total Subscriptions	58,653
Yearly Subscriptions	40,848 (70%)
Monthly Subscriptions	17,805 (30%)
Active Yearly	8,332 (20% of yearly)
Active Monthly	1,964 (11% of monthly)
Monthly Launch Date	July 2024

Note: Active status determined by expire_time > now(), not the status field.

Top Insights

Finding	Impact
~50K net revenue units lost	Converters are main loss; monthly-only mostly incremental
75.8% waited 4.8 years	Monthly-only users wouldn’t have subscribed yearly
Converters: 72% revenue loss	7,448 users pay avg 2.39 months vs 8.7 yearly
Monthly-only: 60% have 1 listing	Small operators, not comparable to yearly users

---

1. Historical Overview

Subscription Type Distribution (All Time)

Type	Total Subs	Active Subs	Active Rate	Unique Users	First Sub
Yearly	40,848	8,332	20.4%	18,383	Feb 2020
Monthly	17,805	1,964	11.0%	7,448	July 2024

Key Finding: Yearly subscriptions have been available since February 2020. Monthly subscriptions officially launched in July 2024 (with limited beta in May-June) and have since captured 30% of all subscriptions in just 18 months.

The low active rate (20% yearly, 11% monthly) reflects that most subscriptions are historical and have expired. Monthly has a lower active rate because users must renew more frequently.

SELECT
  is_monthly_sub,
  count() as total_subscriptions,
  countIf (expire_time > now()) as active_subscriptions,
  round(
    countIf (expire_time > now()) * 100.0 / count(),
    1
  ) as active_rate,
  uniq(mgr_user_id) as unique_users
FROM
  sadb_subs
WHERE
  _peerdb_is_deleted = 0
GROUP BY
  is_monthly_sub
ORDER BY
  is_monthly_sub

---

2. Timeline: Monthly Subscription Adoption

Launch Period Detail

Month	Monthly Subs	Unique Users	Phase
May 2024	71	71	Beta/soft launch
Jun 2024	34	34	Beta
Jul 2024	152	145	Official launch
Aug 2024	634	620	Rapid adoption

Annual Trend

Year	Yearly Subs	Monthly Subs	Monthly %	Notes
2020	4,110	0	0%	Yearly only
2021	5,828	0	0%	Yearly only
2022	7,612	0	0%	Peak yearly growth
2023	7,565	0	0%	Yearly plateau
2024	6,868	4,310	38.6%	Monthly launched July
2025	8,363	12,057	59.0%	Monthly overtakes
2026	502	1,438	74.1%	Monthly dominant

Key Finding: Yearly subscriptions peaked in 2022-2023 (~7,600/year). After monthly launched, yearly dropped to 6,868 in 2024 but recovered to 8,363 in 2025 (highest ever). However, monthly subscriptions grew faster, capturing 59% market share in 2025.

Quarterly Trend

Quarter	Yearly	Monthly	Monthly %	Phase
Q1 2024	1,241	0	0%	Pre-launch
Q2 2024	1,867	105	5.3%	Beta (May-Jun)
Q3 2024	2,446	1,530	38.5%	Launch + rapid adoption
Q4 2024	1,314	2,675	67.1%	Monthly overtakes yearly
Q1 2025	1,939	2,712	58.3%	Stabilization
Q2 2025	1,910	2,422	55.9%	Equilibrium
Q3 2025	2,442	3,244	57.1%	Steady state
Q4 2025	2,072	3,679	64.0%	Monthly growing
Q1 2026	502	1,438	74.1%	Monthly dominant

SELECT
  toStartOfQuarter(createdAt) as quarter,
  countIf (is_monthly_sub = false) as yearly_subs,
  countIf (is_monthly_sub = true) as monthly_subs,
  round(
    countIf (is_monthly_sub = true) * 100.0 / count(),
    1
  ) as monthly_pct
FROM
  sadb_subs
WHERE
  _peerdb_is_deleted = 0
GROUP BY
  quarter
ORDER BY
  quarter

---

3. Conversion Analysis: Yearly ↔ Monthly

All Subscription Transitions (Full History)

Transition Type	Count	Unique Users
Yearly → Yearly (renewal)	40,429	18,347
Monthly → Monthly (renewal)	10,345	2,908
Yearly → Monthly	7,460	7,448
Monthly → Yearly	419	416

Conversion Ratio: 18:1 — For every user who switches from monthly to yearly, 18 users switch from yearly to monthly.

Key Finding: The near 1:1 ratio between yearly→monthly transitions (7,460) and unique users (7,448) indicates most users make this switch only once. It’s a permanent migration, not an oscillation.

WITH
  user_subs_ordered AS (
    SELECT
      mgr_user_id,
      is_monthly_sub,
      createdAt,
      lag(is_monthly_sub) OVER (
        PARTITION BY
          mgr_user_id
        ORDER BY
          createdAt
      ) as prev_sub_monthly
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
  )
SELECT
  CASE
    WHEN prev_sub_monthly = false
    AND is_monthly_sub = true THEN 'Yearly → Monthly'
    WHEN prev_sub_monthly = true
    AND is_monthly_sub = false THEN 'Monthly → Yearly'
    WHEN prev_sub_monthly = false
    AND is_monthly_sub = false THEN 'Yearly → Yearly (renewal)'
    WHEN prev_sub_monthly = true
    AND is_monthly_sub = true THEN 'Monthly → Monthly (renewal)'
  END as transition_type,
  count() as transitions,
  uniq(mgr_user_id) as unique_users
FROM
  user_subs_ordered
WHERE
  prev_sub_monthly IS NOT NULL
GROUP BY
  transition_type
ORDER BY
  transitions DESC

---

4. Retention After Yearly → Monthly Conversion

Context: When subscriptions lapse, users lose their listings. This means users who don’t renew experience immediate business impact, which may drive some to return after a gap.

Reframed Retention Analysis

Retention Status	Users	% of Converters
Churned (1 month, inactive 60+ days)	4,342	58%
Short-term (2-3 months)	1,638	22%
Medium-term (4-6 months)	687	9%
Long-term (7-12 months)	436	6%
Recent convert (too early to tell)	196	3%
Very loyal (13+ months)	149	2%

Key Finding: 58% of yearly→monthly converters truly churned (only 1 month, then inactive 60+ days). This is better than the raw “61% had only 1 subscription” because it accounts for recent converts.

Retention breakdown:

• 58% churned — Converted, paid one month, lost listings, never returned
• 42% retained — Stayed for 2+ months (some with gaps)

WITH
  yearly_to_monthly_users AS (
    SELECT DISTINCT
      mgr_user_id
    FROM
      (
        SELECT
          mgr_user_id,
          is_monthly_sub,
          lag(is_monthly_sub) OVER (
            PARTITION BY
              mgr_user_id
            ORDER BY
              createdAt
          ) as prev_monthly
        FROM
          sadb_subs
        WHERE
          _peerdb_is_deleted = 0
      )
    WHERE
      prev_monthly = false
      AND is_monthly_sub = true
  ),
  converter_monthly_history AS (
    SELECT
      s.mgr_user_id,
      countIf (s.is_monthly_sub = true) as monthly_sub_count,
      max(if(s.is_monthly_sub, s.createdAt, null)) as last_monthly
    FROM
      sadb_subs s
      JOIN yearly_to_monthly_users u ON s.mgr_user_id = u.mgr_user_id
    WHERE
      s._peerdb_is_deleted = 0
    GROUP BY
      s.mgr_user_id
  )
SELECT
  CASE
    WHEN monthly_sub_count = 1
    AND last_monthly < now() - INTERVAL 60 DAY THEN 'Churned (1 month, inactive 60+ days)'
    WHEN monthly_sub_count = 1 THEN 'Recent convert (1 month so far)'
    WHEN monthly_sub_count BETWEEN 2 AND 3 THEN 'Short-term (2-3 months)'
    WHEN monthly_sub_count BETWEEN 4 AND 6 THEN 'Medium-term (4-6 months)'
    WHEN monthly_sub_count BETWEEN 7 AND 12 THEN 'Long-term (7-12 months)'
    ELSE 'Very loyal (13+ months)'
  END as retention_status,
  count() as users
FROM
  converter_monthly_history
GROUP BY
  retention_status
ORDER BY
  users DESC

---

5. Renewal Gap Analysis

Context: Since listings are lost when subscriptions lapse, gaps between renewals indicate periods where users lost their listings and then decided to re-subscribe.

Gap Between Monthly Renewals

Gap Category	Occurrences	Unique Users	% of Renewals
No gap (renewed on time)	13,484	7,448	76%
1-7 days gap	1,486	869	8%
8-30 days gap	1,217	820	7%
1-3 months gap	932	743	5%
3+ months gap	686	638	4%

Key Finding: 24% of monthly renewals have gaps, meaning users let their subscription (and listings) lapse before returning. This suggests:

1. Some users forget to renew and lose listings temporarily
2. Some users intentionally pause and return when needed
3. The listing loss consequence isn’t preventing gaps entirely

WITH
  monthly_subs AS (
    SELECT
      mgr_user_id,
      createdAt,
      expire_time,
      lead(createdAt) OVER (
        PARTITION BY
          mgr_user_id
        ORDER BY
          createdAt
      ) as next_sub_date
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
      AND is_monthly_sub = true
  ),
  gaps AS (
    SELECT
      mgr_user_id,
      dateDiff('day', expire_time, next_sub_date) as gap_days
    FROM
      monthly_subs
    WHERE
      next_sub_date IS NOT NULL
  )
SELECT
  CASE
    WHEN gap_days <= 0 THEN 'No gap (renewed on time)'
    WHEN gap_days BETWEEN 1 AND 7 THEN '1-7 days gap'
    WHEN gap_days BETWEEN 8 AND 30 THEN '8-30 days gap'
    WHEN gap_days BETWEEN 31 AND 90 THEN '1-3 months gap'
    ELSE '3+ months gap'
  END as gap_category,
  count() as occurrences,
  uniq(mgr_user_id) as unique_users
FROM
  gaps
GROUP BY
  gap_category
ORDER BY
  gap_category

Subscription Continuity (Users with 2+ Monthly Subs)

Continuity Pattern	Users	Avg Monthly Subs	Avg Span (Months)
Continuous (no gaps)	1,579	4.8	3.7
Mostly continuous	231	7.6	9.1
Intermittent (some gaps)	420	4.7	7.9
Sparse (large gaps)	410	3.2	10.4

Key Finding: Users with gaps tend to have longer spans but fewer subscriptions, indicating they use the service intermittently — subscribing when they need listings, letting them lapse when not actively selling/renting.

WITH
  user_monthly_history AS (
    SELECT
      mgr_user_id,
      count() as total_monthly_subs,
      dateDiff('month', min(createdAt), max(createdAt)) as span_months
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
      AND is_monthly_sub = true
    GROUP BY
      mgr_user_id
    HAVING
      total_monthly_subs > 1
  )
SELECT
  CASE
    WHEN total_monthly_subs >= span_months THEN 'Continuous (no gaps)'
    WHEN total_monthly_subs >= span_months * 0.75 THEN 'Mostly continuous'
    WHEN total_monthly_subs >= span_months * 0.5 THEN 'Intermittent (some gaps)'
    ELSE 'Sparse (large gaps)'
  END as continuity,
  count() as users,
  round(avg(total_monthly_subs), 1) as avg_subs,
  round(avg(span_months), 1) as avg_span_months
FROM
  user_monthly_history
WHERE
  span_months > 0
GROUP BY
  continuity
ORDER BY
  users DESC

---

6. User Journey: First vs Latest Subscription Type

Users with Multiple Subscriptions (Full History)

First Type	Latest Type	User Count	% of Multi-Sub Users
Yearly	Yearly	8,275	70.4%
Yearly	Monthly	1,138	9.7%
Monthly	Yearly	237	2.0%
Monthly	Monthly	2,102	17.9%

Key Finding: Among the ~11,750 users with 2+ subscriptions:

• 70% who started yearly stayed yearly (loyal yearly base)
• 10% who started yearly ended on monthly (converted)
• 2% who started monthly ended on yearly (rare reverse)
• 18% who started monthly stayed monthly

WITH
  user_subs_ordered AS (
    SELECT
      mgr_user_id,
      is_monthly_sub,
      createdAt,
      row_number() OVER (
        PARTITION BY
          mgr_user_id
        ORDER BY
          createdAt
      ) as sub_order
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
  ),
  first_and_later AS (
    SELECT
      mgr_user_id,
      argMin(is_monthly_sub, sub_order) as first_sub_monthly,
      argMax(is_monthly_sub, sub_order) as latest_sub_monthly,
      max(sub_order) as total_subs
    FROM
      user_subs_ordered
    GROUP BY
      mgr_user_id
    HAVING
      total_subs > 1
  )
SELECT
  first_sub_monthly,
  latest_sub_monthly,
  count() as user_count
FROM
  first_and_later
GROUP BY
  first_sub_monthly,
  latest_sub_monthly
ORDER BY
  first_sub_monthly,
  latest_sub_monthly

---

7. New vs Returning Subscribers

Historical Trend

Year	New Subscribers	Returning	Returning %
2020	3,947	163	4%
2021	3,377	2,451	42%
2022	3,311	4,301	57%
2023	3,588	3,977	53%
2024	5,253	5,925	53%
2025	5,641	14,779	72%
2026	352	1,588	82%

Key Finding: The proportion of returning subscribers jumped significantly in 2025 (72%) and 2026 (82%). This is driven by monthly renewals — monthly subscribers renew 12x per year vs 1x for yearly.

WITH
  first_sub AS (
    SELECT
      mgr_user_id,
      min(createdAt) as first_sub_date
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
    GROUP BY
      mgr_user_id
  )
SELECT
  toYear(s.createdAt) as year,
  countIf (s.createdAt = f.first_sub_date) as new_subscribers,
  countIf (s.createdAt > f.first_sub_date) as returning_subscribers,
  round(
    countIf (s.createdAt > f.first_sub_date) * 100.0 / count(),
    1
  ) as returning_pct
FROM
  sadb_subs s
  JOIN first_sub f ON s.mgr_user_id = f.mgr_user_id
WHERE
  s._peerdb_is_deleted = 0
GROUP BY
  year
ORDER BY
  year

---

8. Package Distribution

Subscriptions by Package (Full History)

Package ID	Type	Subscriptions	Unique Users
1	Yearly	30,181	16,379
3	Yearly	6,571	3,265
2	Yearly	3,643	2,742
4	Yearly	453	220
5	Monthly	16,820	7,106
7	Monthly	985	426

Key Finding: Monthly subscriptions use different packages (5, 7) than yearly (1, 2, 3, 4). Package 5 accounts for 94% of all monthly subscriptions.

SELECT
  is_monthly_sub,
  sub_pkg_id,
  count() as subscriptions,
  uniq(mgr_user_id) as unique_users
FROM
  sadb_subs
WHERE
  _peerdb_is_deleted = 0
GROUP BY
  is_monthly_sub,
  sub_pkg_id
ORDER BY
  is_monthly_sub,
  subscriptions DESC

---

9. Sample User Journeys

Users with Both Subscription Types

Example journeys (Y = Yearly, M = Monthly):

User ID	Journey	Pattern
158068	YYYYY→MMMM→YYY→MMMMM→Y	Oscillator (rare)
3022093	YY→MMMMMMMMMMMMMMMMMMM	Converted, stayed monthly
330206	YY→MMMMMMMMMMMMMMMMM	Converted, stayed monthly
591508	MMMMMMMMMMMMMMMMM→YY	Started monthly, went yearly
3831991	MMMMMMMMMMMMMMMMM→YYY	Started monthly, went yearly

Key Finding: The dominant pattern is Y...Y→MMMM... (long-term yearly subscriber converts to monthly and stays). True oscillators (switching back and forth) are rare.

---

10. Revenue Impact Analysis

Pricing: Yearly subscription costs 8.7x the monthly subscription price. This means a user must stay on monthly for ~9 months to equal the yearly revenue.

Converter Revenue Analysis (Per User)

Metric	Value
Unique users who converted yearly→monthly	7,448
Average monthly subscriptions per user	2.39
Users who reached breakeven (9+ months)	363 (4.9%)

Revenue Comparison Per User

Scenario	Revenue per User (monthly units)
If user paid 1 yearly	8.7
Actual avg monthly paid	2.39
Revenue gap per user	-6.31 (-72%)

For the full cohort of 7,448 converters:

Scenario	Calculation	Total Revenue (monthly units)
If all paid 1 yearly	7,448 × 8.7	64,798
Actual monthly revenue	7,448 × 2.39	17,801
Revenue loss		-46,997 (-72%)

Critical Finding: Each converter generates on average only 27% of what a single yearly subscription would generate.

User Distribution by Months Paid

Months Paid	Users	% of Users	Revenue vs 1 Yearly
1 month	4,538	60.9%	11.5%
2 months	1,123	15.1%	23%
3-4 months	813	10.9%	34-46%
5-8 months	611	8.2%	57-92%
9+ months (breakeven)	363	4.9%	≥100%

Only 4.9% of users paid enough months to match a single yearly subscription.

Monthly-Only Users (Never Subscribed Yearly)

A key question: Is monthly attracting new users who wouldn’t have subscribed otherwise?

User Segmentation

Segment	Users	% of All Users
Yearly only	16,835	69.3%
Monthly only (never yearly)	5,900	24.3%
Both types (converters)	1,548	6.4%

5,900 users have only ever had monthly subscriptions — they never subscribed yearly.

Monthly-Only User Behavior

Metric	Value
Unique users	5,900
Avg months subscribed	2.2
Users with only 1 month	3,801 (64.4%)
Users with 9+ months	231 (3.9%)
Currently active	749 (12.7%)

Key Finding: Monthly-only users have worse retention than yearly→monthly converters:

• 64.4% churn after 1 month (vs 60.9% for converters)
• Only 3.9% reach 9+ months (vs 4.9% for converters)

Distribution by Months Subscribed

Months	Users	%
1 month	3,801	64.4%
2 months	850	14.4%
3-4 months	600	10.2%
5-8 months	418	7.1%
9+ months	231	3.9%

Revenue Comparison by Segment

Segment	Users	Revenue per User (monthly units)
Yearly only	16,835	19.3
Converters (both types)	1,548	23.0
Monthly only	5,900	2.2

Monthly-only users generate only 11% of the revenue per user compared to yearly-only users.

Would Monthly-Only Users Have Subscribed Yearly?

Several signals suggest most monthly-only users would NOT have subscribed yearly:

Signal 1: Account Timing

Account Created	Users	%	Avg Days to First Sub
Before monthly launch (Jul 2024)	4,475	75.8%	1,759 days (4.8 years)
After monthly launch	1,425	24.2%	77 days

75.8% of monthly-only users had accounts for years but never subscribed yearly. They waited an average of 4.8 years before subscribing monthly — strong evidence they wouldn’t have subscribed yearly.

Signal 2: Prior Platform Activity

Activity Before Subscribing	Users	%
Had listings before subscribing	4,498	76.2%
No listings before subscribing	1,402	23.8%

76% were already active on the platform (had listings) but chose not to subscribe yearly.

Signal 3: Operation Size (Listings per User)

Segment	Avg Listings	Median Listings	Users with 10+ Listings
Yearly-only	213.6	55	13,933 (83%)
Monthly-only	19.5	7	2,389 (40%)

Monthly-only users have much smaller operations — median 7 listings vs 55 for yearly.

Signal 4: Listings per Subscription

Subscription Type	Avg Listings/Sub	Median Listings/Sub
Yearly	48.1	6
Monthly	6.0	2

Monthly subscriptions are associated with far fewer listings.

Signal 5: Monthly-Only User Listing Distribution

Listings Tied to Subscription	Users	%
1 listing	3,523	59.7%
2-5 listings	1,294	21.9%
6-10 listings	441	7.5%
10+ listings	642	10.9%

60% of monthly-only users have just 1 listing — likely individual sellers, not professional brokers who would commit to yearly.

Signal 6: New Yearly Acquisition Trend

Quarter	New Yearly Subscribers
Q1 2024 (pre-monthly)	583
Q2 2024 (pre-monthly)	995
Q3 2024 (monthly launched)	757
Q4 2024	502
Q1 2025	390
Q2 2025	286
Q3 2025	325
Q4 2025	252

New yearly acquisitions dropped ~50% after monthly launched — some cannibalization, but baseline suggests many monthly users are incremental.

Estimation Summary

User Group	Count	Likely Would Subscribe Yearly?
Accounts before Jul 2024, waited years	4,475	No — waited 4.8 years on average
New accounts after Jul 2024	1,425	Unknown — could be either
10+ listings (larger operators)	642	Maybe — similar scale to yearly users

Conservative estimate: At most 1,400-2,000 of the 5,900 monthly-only users (~25-35%) might have subscribed yearly. The rest are likely incremental users who would not have committed to yearly subscriptions.

-- Account timing analysis
WITH
  monthly_only_users AS (
    SELECT
      mgr_user_id
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
    GROUP BY
      mgr_user_id
    HAVING
      countIf (is_monthly_sub = false) = 0
      AND countIf (is_monthly_sub = true) > 0
  ),
  user_timing AS (
    SELECT
      m.mgr_user_id,
      toDateTime(u.create_time) as account_created,
      min(s.createdAt) as first_sub_date
    FROM
      monthly_only_users m
      JOIN sadb_users u ON m.mgr_user_id = u.user_id
      JOIN sadb_subs s ON m.mgr_user_id = s.mgr_user_id
    WHERE
      u._peerdb_is_deleted = 0
      AND s._peerdb_is_deleted = 0
    GROUP BY
      m.mgr_user_id,
      account_created
  )
SELECT
  CASE
    WHEN account_created < toDateTime('2024-07-01') THEN 'Before monthly launch'
    ELSE 'After monthly launch'
  END as account_timing,
  count() as users,
  round(
    avg(dateDiff('day', account_created, first_sub_date)),
    0
  ) as avg_days_to_first_sub
FROM
  user_timing
GROUP BY
  account_timing

-- User segmentation query
WITH
  user_segments AS (
    SELECT
      mgr_user_id,
      countIf (is_monthly_sub = false) as yearly_count,
      countIf (is_monthly_sub = true) as monthly_count
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
    GROUP BY
      mgr_user_id
  )
SELECT
  CASE
    WHEN yearly_count > 0
    AND monthly_count > 0 THEN 'Converters'
    WHEN yearly_count > 0
    AND monthly_count = 0 THEN 'Yearly only'
    WHEN yearly_count = 0
    AND monthly_count > 0 THEN 'Monthly only'
  END as segment,
  count() as users,
  round(avg(monthly_count), 2) as avg_monthly_subs,
  round(avg(yearly_count), 2) as avg_yearly_subs
FROM
  user_segments
GROUP BY
  segment

WITH
  yearly_to_monthly_users AS (
    SELECT DISTINCT
      mgr_user_id
    FROM
      (
        SELECT
          mgr_user_id,
          is_monthly_sub,
          lag(is_monthly_sub) OVER (
            PARTITION BY
              mgr_user_id
            ORDER BY
              createdAt
          ) as prev_monthly
        FROM
          sadb_subs
        WHERE
          _peerdb_is_deleted = 0
      )
    WHERE
      prev_monthly = false
      AND is_monthly_sub = true
  ),
  user_monthly_count AS (
    SELECT
      u.mgr_user_id,
      countIf (s.is_monthly_sub = true) as monthly_count
    FROM
      yearly_to_monthly_users u
      JOIN sadb_subs s ON u.mgr_user_id = s.mgr_user_id
    WHERE
      s._peerdb_is_deleted = 0
    GROUP BY
      u.mgr_user_id
  )
SELECT
  count() as unique_users,
  round(avg(monthly_count), 2) as avg_monthly_per_user,
  countIf (monthly_count >= 9) as users_reached_breakeven,
  round(countIf (monthly_count >= 9) * 100.0 / count(), 1) as pct_reached_breakeven
FROM
  user_monthly_count

---

11. What Happens to Listings When Subscriptions Expire?

Listing Visibility by Subscription Status

Subscription Status	Users	Total Listings	Published & Visible	% Visible
Active	5,045	1,506,316	798,801	53%
Expired	18,001	2,525,147	267,951	10.6%

When subscriptions expire, listing visibility drops from 53% to 10.6% — a ~5x reduction. Users don’t lose their listings entirely, but they become unpublished/hidden.

Expired Subscribers: Listings Breakdown

Metric	Value
Users with expired subs	19,130
Total listings	2,526,269
Currently active/visible	33,047 (1.3%)
Inactive/unpublished	2,258,321 (89%)

The 267,951 “published” listings from expired users may be from free tiers, legacy accounts, or data inconsistencies.

Recent Churners (Expired <90 Days)

Churn Type	Users	Total Listings	Avg Listings	Active Listings
Yearly churners	658	163,183	248	12,079
Monthly churners	1,151	55,988	49	6,126

Monthly churners have smaller portfolios (49 vs 248 listings) — consistent with smaller operators who don’t need yearly commitment.

Churned Monthly Users: Listings Tied to Subscription

Metric	Value
Churned monthly users	5,992
Listings tied to subscription (sub_id > 0)	88,024
Listings without sub tie	122,431
Avg listings per user (with sub)	14.7

-- Listing visibility by subscription status
WITH
  user_status AS (
    SELECT
      mgr_user_id,
      max(expire_time) as last_expire,
      CASE
        WHEN max(expire_time) > now() THEN 'Active'
        ELSE 'Expired'
      END as sub_status
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
    GROUP BY
      mgr_user_id
  )
SELECT
  sub_status,
  uniq(u.mgr_user_id) as users,
  count(l.id) as total_listings,
  countIf (
    l.published = 1
    AND l.hidden = 0
  ) as published_visible,
  round(
    countIf (
      l.published = 1
      AND l.hidden = 0
    ) * 100.0 / count(l.id),
    1
  ) as pct_visible
FROM
  user_status u
  JOIN sadb_listings l ON u.mgr_user_id = l.user_id
  AND l._peerdb_is_deleted = 0
GROUP BY
  sub_status

Do Users Create NEW Listings Without an Active Subscription?

Yes — 7,614 users have created 41,580 listings when they had NO active subscription covering that time.

Important Correction: This analysis verifies that each listing was created during a period with no active subscription — not just “after expiry” (which could include users who resubscribed).

Metric	Value
Users who created listings without ANY active subscription	7,614
Listings created without subscription coverage	41,580
Average listings per user	5.5
Currently published & visible	8,302 (20%)
REGA licensed	33,854 (81.4%)

Do These Users Later Resubscribe?

Metric	Value
Users who created listings without subscription	7,614
Later resubscribed	3,526 (46.3%)
Never resubscribed	4,088 (53.7%)

Nearly half (46.3%) of users who create listings without a subscription eventually resubscribe. This suggests:

• Creating listings demonstrates ongoing intent to use the platform
• These users are good reactivation targets
• The other 54% remain unmonetized demand

Status of Unsubscribed Listings

Status	Listings	%
Published & Visible	8,302	20%
Unpublished/Hidden	33,278	80%

Only 20% of unsubscribed listings are published — much lower than the 53% visibility rate for active subscribers. This confirms listings lose visibility without an active subscription.

-- Listings created when user had NO active subscription
WITH
  churned_users AS (
    SELECT
      mgr_user_id,
      max(expire_time) as last_expire
    FROM
      sadb_subs
    WHERE
      _peerdb_is_deleted = 0
    GROUP BY
      mgr_user_id
    HAVING
      max(expire_time) < now()
  ),
  listings_by_churned AS (
    SELECT
      l.id as listing_id,
      l.user_id,
      toDateTime(l.create_time) as listing_created,
      l.status,
      l.published,
      l.hidden,
      l.rega_licensed as is_rega
    FROM
      sadb_listings l
      INNER JOIN churned_users c ON l.user_id = c.mgr_user_id
    WHERE
      l._peerdb_is_deleted = 0
      AND toDate(l.create_time) >= '2024-07-01'
  ),
  listings_with_coverage AS (
    SELECT
      lc.listing_id,
      lc.user_id,
      lc.listing_created,
      lc.status,
      lc.published,
      lc.hidden,
      lc.is_rega,
      max(
        CASE
          WHEN s.start_time <= lc.listing_created
          AND s.expire_time >= lc.listing_created THEN 1
          ELSE 0
        END
      ) as has_covering_sub
    FROM
      listings_by_churned lc
      LEFT JOIN sadb_subs s ON lc.user_id = s.mgr_user_id
      AND s._peerdb_is_deleted = 0
    GROUP BY
      lc.listing_id,
      lc.user_id,
      lc.listing_created,
      lc.status,
      lc.published,
      lc.hidden,
      lc.is_rega
  )
SELECT
  countIf (has_covering_sub = 0) as truly_unsubscribed_listings,
  uniqIf (user_id, has_covering_sub = 0) as users_creating_without_sub,
  countIf (
    has_covering_sub = 0
    AND status IN (0, 4)
    AND published = 1
    AND hidden = 0
  ) as truly_unsubscribed_published
FROM
  listings_with_coverage

Reactivation Opportunity

There are 2.5M listings from users with expired subscriptions, representing potential reactivation opportunities:

Expiry Window	Users	Listings	Avg Listings/User
<30 days	671	149,228	222
30-90 days	1,146	70,263	61
90-180 days	1,751	113,145	65
180-365 days	3,315	300,310	91
1+ year	12,247	1,893,323	155

Key Finding: Users who create listings without a subscription have a 46.3% resubscription rate — significantly higher than typical reactivation rates. These users demonstrate clear intent to continue using the platform.

Opportunity:

• Target recently expired users (especially <90 days) with reactivation campaigns
• Specifically target the 7,614 users who created listings without subscriptions
• Their listings are dormant but could be quickly republished with a new subscription

---

Business Implications

The Listing Loss Factor

Since users lose listing visibility when subscriptions lapse:

Behavior	Implication
76% renew on time	Strong motivation to avoid listing loss
24% have gaps	Some users accept temporary listing loss
58% converter churn	Many willing to lose listings rather than continue

This suggests the listing loss consequence:

• Works for committed users (76% renew on time)
• Doesn’t prevent intermittent usage (24% have gaps)
• Doesn’t retain users who weren’t planning to stay (58% churn)

Positive Signals

Signal	Interpretation
~65% of monthly-only are incremental	Would NOT have subscribed yearly (waited 4.8 years)
5,900 monthly-only users	Brought in users with smaller operations
Total subs growing	2025 had more total subs than any previous year
Yearly still growing	8,363 yearly subs in 2025 (highest ever)
8,332 active yearly users	Strong base of committed annual subscribers

Warning Signs

Signal	Risk
72% revenue loss from converters	7,448 converters pay avg 2.39 months vs 8.7 yearly
Only 4.9% converters reach breakeven	95% pay less than 1 yearly subscription
~50K net monthly units lost	Converter losses far exceed incremental gains
Converter churn: 60.9%	Majority leave after just 1 month
11% monthly active rate	Only 1,964 of 17,805 monthly subs currently active

---

Recommendations

High Priority

1. Address the 72% revenue leakage from converters

   • Converters pay only 27% of what they would on yearly
   • Consider: Should yearly→monthly conversion require yearly expiration first?
   • Test: Offer prorated yearly renewals instead of monthly conversion
   • Implement: Stronger yearly retention campaigns before expiration

2. Increase monthly pricing or reduce yearly discount

   • Current 8.7x ratio strongly favors yearly for committed users
   • Monthly breakeven at 9 months is rarely reached (4.9%)
   • Consider: Adjust ratio to 10x or 11x to improve monthly economics
   • Or: Introduce 6-month option at 5x monthly

3. Investigate the 58% true churn

   • Why do 4,342 users convert from yearly and then churn?
   • Did they achieve their goal (sell/rent property)?
   • Or did they have a bad experience?
   • These users represent ~37,000 “missing” monthly units of revenue

Medium Priority

4. Understand intermittent users

   • 830+ users have gaps but returned (lost listings, came back)
   • Are these seasonal brokers? Occasional sellers?
   • Could a “pause” feature serve them better than full lapse?

5. A/B test commitment incentives

   • Offer meaningful yearly discounts
   • Test quarterly billing as middle-ground
   • Compare retention by default billing option

To Investigate

6. Why do 2% switch from monthly to yearly?

   • 416 users made this rare switch
   • What motivated them? Could be testimonial material
   • Could this behavior be encouraged?

7. Revenue impact analysis

   • Compare revenue before/after monthly launch (July 2024)
   • Are we growing total revenue or just changing billing?

---

Summary

The introduction of monthly subscriptions in July 2024 triggered a massive shift in subscriber behavior:

• Monthly now represents 74% of new subscriptions (2026)
• 7,448 users switched from yearly to monthly (vs 419 the other direction)
• 5,900 users subscribed monthly without ever having yearly
• Monthly users (both converters and monthly-only) have high churn: 60-65% leave after 1 month

User Segmentation

Segment	Users	Avg Revenue/User	Notes
Yearly only	16,835	19.3	Loyal, high-value base
Monthly only	5,900	2.2	New users, very low value
Converters	1,548	23.0	Highest value (yearly + monthly)

The Revenue Impact

With yearly costing 8.7x monthly, the revenue math is stark:

Converters (7,448 users who switched yearly→monthly):

Metric	Value
If each paid 1 yearly	64,798 monthly units
Actual monthly revenue	17,801 monthly units
Revenue loss	-46,997 (-72%)

Monthly-only users (5,900 users who never had yearly):

Metric	Value
If each paid 1 yearly	51,330 monthly units
Actual monthly revenue	12,989 monthly units
Revenue loss	-38,341 (-75%)

Combined revenue gap: ~85,000 monthly units

Key Question Answered

Is monthly flexibility increasing total value?

No. Monthly subscriptions are:

1. Cannibalizing yearly — Converters pay only 27% of yearly value
2. Not attracting high-value new users — Monthly-only users have worse retention than converters (64.4% vs 60.9% churn)
3. Generating minimal incremental revenue — 5,900 new users × 2.2 months = 12,989 monthly units

Are Monthly-Only Users Truly Incremental?

Evidence suggests YES — most monthly-only users would NOT have subscribed yearly:

Signal	Finding
Account timing	75.8% had accounts 4.8 years before subscribing
Prior activity	76.2% had listings but never subscribed yearly
Operation size	Median 7 listings (vs 55 for yearly users)
Listing distribution	60% have just 1 listing

Estimate: Only ~25-35% (1,400-2,000 users) of monthly-only users might have subscribed yearly. The rest are likely truly incremental.

Revised Revenue Impact

If only 1,700 monthly-only users (midpoint) would have subscribed yearly:

Source	Revenue Loss
Converters (7,448 users)	-47,000 monthly units
Monthly-only who might have gone yearly (~1,700 users)	-12,000 monthly units
Total actual loss	~59,000 monthly units
Incremental from monthly-only (~4,200 users × 2.2)	+9,240 monthly units
Net revenue impact	~-50,000 monthly units

The converter revenue loss remains the primary concern. Monthly-only users are mostly incremental but low-value.

Pre vs Post Monthly Launch Comparison

Metric	Pre-Launch (2020-Q1 2024)	Post-Launch (Q3 2024-Q1 2026)
Avg quarterly subs	1,660 yearly	2,010 yearly + 2,110 monthly
Total quarterly	1,660	4,120 (+148%)
Unique users/quarter	~1,200	~2,800

While total subscription volume has increased significantly (+148%), the revenue implications depend on pricing and the churn/gap dynamics among monthly subscribers.