View Distribution Analysis & Listing Scoring Recommendations

Date: 2026-01-16 Focus: Districts with 500+ active REGA-licensed listings

Executive Summary

Analysis of 143,588 active property listings reveals that the platform already favors newer listings - they receive 2.7x more views per day than older listings. Using a properly age-normalized metric (Performance Index), the strongest predictors of high performance are images (98% vs 88%) and from owner (3.2x more likely to be top performers).

---

Key Metrics

VPD (Views Per Day)

VPD = Total Views / Days Since Created

Problem: VPD naturally declines over time, making cross-age comparisons unfair.

Performance Index (PI) - Recommended Metric

PI = Actual VPD / Expected VPD for (Category, District, Age Bucket)

• PI = 1.0 → Performing at expected level for segment and age
• PI > 1.0 → Outperforming peers
• PI < 1.0 → Underperforming peers

This metric is age-neutral - old and new listings can be fairly compared.

---

Key Findings

1. Expected VPD Decay Curve (Global Baseline)

Age Bucket	Listings	Median VPD	P25 VPD	P75 VPD
0-7 days	7,747	10.50	4.50	23.60
8-14 days	6,695	8.10	3.55	18.92
15-30 days	14,277	6.17	2.47	14.88
31-60 days	23,655	4.47	1.82	10.94
61-90 days	20,287	4.04	1.78	9.31
91-120 days	15,854	3.70	1.62	8.38
121-180 days	30,187	3.18	1.21	7.33
180+ days	24,886	2.93	0.98	6.69

VPD declines ~3.6x from new (10.5) to old (2.93) listings.

2. Performance Index Distribution (Age-Normalized)

Using segment-specific expected VPD (comparing to category/district/age peers):

PI Bucket	Listings	Avg Age	% Images	% From Owner
Excellent (3x+ expected)	12,049	105	98.2%	3.8%
Strong (2-3x expected)	10,569	108	97.0%	3.5%
Good (1-2x expected)	57,723	104	94.3%	5.8%
Below Avg (0.5-1x)	42,417	107	93.0%	2.9%
Poor (<0.5x expected)	20,830	108	87.6%	1.2%

Key observations:

• Age is neutral - all tiers have similar avg age (104-108 days), proving PI works
• Images matter significantly: 98.2% vs 87.6% (10.6 percentage points)
• From Owner: 3.8% vs 1.2% (3.2x difference)

3. Global PI (Simpler Calculation)

Using global expected VPD (same baseline for all segments):

PI Bucket	Listings	Avg Age	% Images	% From Owner
Excellent (3x+ expected)	26,740	105	94.5%	8.8%
Strong (2-3x expected)	15,049	106	94.4%	5.8%
Good (1-2x expected)	30,130	107	93.6%	4.1%
Below Avg (0.5-1x)	28,587	103	92.5%	2.9%
Poor (<0.5x expected)	43,082	108	93.1%	0.9%

From Owner signal is stronger with global PI: 8.8% vs 0.9% = 9.8x difference

4. Owner vs Agent Performance

Advertiser Type	Listings	Avg Total Views
Owner	26,809	845
Agent	116,712	661

Owner listings receive 28% more views than agent listings.

5. PI Distribution by Segment

Category	City	Listings	Median PI	P90 PI	P90/Median
Apartment for Sale	Jeddah	18,780	0.16	1.08	6.8x
Villa for Sale	Al Khobar	1,705	0.88	4.47	5.1x
Apartment for Sale	Riyadh	9,475	0.66	3.15	4.8x
Apartment for Rent	Al Khobar	1,374	2.61	11.38	4.4x
Villa for Sale	Riyadh	15,438	1.26	5.03	4.0x
Apartment for Rent	Riyadh	17,134	2.34	8.68	3.7x

P90/median ratio of 3.7x - 6.8x indicates moderate inequality within segments.

---

Quality Signals Summary

Factor	Evidence	Recommendation
Images	98% vs 88% in segment-specific PI	High priority boost
From Owner	3.2x-9.8x more likely to be top performers	Significant boost
Golden Campaign	+188% views	Paid promotion works
Video Content	+49% views	Rich media boost
Lower Price	+65% views vs segment	Competitive pricing signal

---

Recommendations for Scoring

1. Use Performance Index (PI) for Ranking

Replace VPD or total views with PI:

-- Calculate segment-specific expected VPD
expected_vpd = median(VPD) FOR same (category, district, age_bucket)
-- Performance Index
PI = actual_vpd / expected_vpd

2. Image Boost (High Impact)

image_boost = 1.0 + (0.3 * has_images)

3. Owner Boost (High Impact)

owner_boost = 1.0 + (0.25 * is_from_owner)

4. Combined Scoring Formula

listing_score = PI * (1 + 0.3 * has_images) * (1 + 0.25 * from_owner) * (1 + 0.3 * has_video) * (1 + 2.0 * has_premium)

---

Implementation Priority

Priority	Change	Expected Impact	Complexity
1	Use PI instead of VPD/total views	High	Medium
2	Image presence boost	High	Low
3	Owner boost	High	Low
4	Rich media (video) boost	Medium	Low

---

SQL Queries

Expected VPD by Age Bucket (Global)

SELECT
  age_bucket,
  count(*) AS listings,
  round(quantile(0.5) (vpd), 2) AS median_vpd
FROM
  (
    SELECT
      l.id,
      dateDiff(
        'day',
        toDate(fromUnixTimestamp(l.create_time)),
        today()
      ) AS days_active,
      coalesce(sum(s.views), 0) / greatest(days_active, 1) AS vpd,
      multiIf(
        days_active <= 7,
        '01_0-7d',
        days_active <= 14,
        '02_8-14d',
        days_active <= 30,
        '03_15-30d',
        days_active <= 60,
        '04_31-60d',
        days_active <= 90,
        '05_61-90d',
        days_active <= 120,
        '06_91-120d',
        days_active <= 180,
        '07_121-180d',
        '08_180+d'
      ) AS age_bucket
    FROM
      sadb_listings l FINAL
      LEFT JOIN sadb_stats_listings_stats s FINAL ON l.id = s.id
      AND toDate(s.day_time) >= '2024-01-01'
    WHERE
      l.status IN (0, 4)
      AND l.published = 1
      AND l.hidden = 0
      AND l.rega_licensed = 1
      AND l._peerdb_is_deleted = 0
      AND toDate(fromUnixTimestamp(l.create_time)) >= '2024-01-01'
    GROUP BY
      l.id,
      days_active
  )
GROUP BY
  age_bucket
ORDER BY
  age_bucket

Performance Index with Global Expected VPD

SELECT
  pi_bucket,
  count(*) AS listings,
  round(avg(days_active), 0) AS avg_age,
  round(avg(from_owner) * 100, 1) AS pct_owner,
  round(avg(has_img) * 100, 1) AS pct_imgs
FROM
  (
    SELECT
      l.id,
      l.from_owner,
      l.has_img,
      dateDiff(
        'day',
        toDate(fromUnixTimestamp(l.create_time)),
        today()
      ) AS days_active,
      coalesce(sum(s.views), 0) / greatest(days_active, 1) AS vpd,
      -- Expected VPD by age bucket (global)
      multiIf(
        days_active <= 7,
        10.5,
        days_active <= 14,
        8.1,
        days_active <= 30,
        6.17,
        days_active <= 60,
        4.47,
        days_active <= 90,
        4.04,
        days_active <= 120,
        3.70,
        days_active <= 180,
        3.18,
        2.93
      ) AS expected_vpd,
      vpd / expected_vpd AS performance_index,
      multiIf(
        performance_index >= 3,
        'a_excellent_3x+',
        performance_index >= 2,
        'b_strong_2-3x',
        performance_index >= 1,
        'c_good_1-2x',
        performance_index >= 0.5,
        'd_below_avg_0.5-1x',
        'e_poor_<0.5x'
      ) AS pi_bucket
    FROM
      sadb_listings l FINAL
      LEFT JOIN sadb_stats_listings_stats s FINAL ON l.id = s.id
      AND toDate(s.day_time) >= '2024-01-01'
    WHERE
      l.status IN (0, 4)
      AND l.published = 1
      AND l.hidden = 0
      AND l.rega_licensed = 1
      AND l._peerdb_is_deleted = 0
      AND toDate(fromUnixTimestamp(l.create_time)) >= '2024-01-01'
    GROUP BY
      l.id,
      l.from_owner,
      l.has_img,
      days_active
  )
GROUP BY
  pi_bucket
ORDER BY
  pi_bucket

Segment-Specific Expected VPD (Most Accurate)

-- Step 1: Build expected VPD lookup table
CREATE TABLE expected_vpd_lookup AS
SELECT
  category,
  district_id,
  days_bucket,
  quantile(0.5) (vpd) AS expected_vpd
FROM
  (
    SELECT
      l.category,
      l.district_id,
      coalesce(sum(s.views), 0) / greatest(
        dateDiff(
          'day',
          toDate(fromUnixTimestamp(l.create_time)),
          today()
        ),
        1
      ) AS vpd,
      multiIf(
        dateDiff(
          'day',
          toDate(fromUnixTimestamp(l.create_time)),
          today()
        ) <= 7,
        1,
        dateDiff(
          'day',
          toDate(fromUnixTimestamp(l.create_time)),
          today()
        ) <= 14,
        2,
        dateDiff(
          'day',
          toDate(fromUnixTimestamp(l.create_time)),
          today()
        ) <= 30,
        3,
        dateDiff(
          'day',
          toDate(fromUnixTimestamp(l.create_time)),
          today()
        ) <= 60,
        4,
        dateDiff(
          'day',
          toDate(fromUnixTimestamp(l.create_time)),
          today()
        ) <= 90,
        5,
        dateDiff(
          'day',
          toDate(fromUnixTimestamp(l.create_time)),
          today()
        ) <= 120,
        6,
        dateDiff(
          'day',
          toDate(fromUnixTimestamp(l.create_time)),
          today()
        ) <= 180,
        7,
        8
      ) AS days_bucket
    FROM
      sadb_listings l FINAL
      LEFT JOIN sadb_stats_listings_stats s FINAL ON l.id = s.id
      AND toDate(s.day_time) >= '2024-01-01'
    WHERE
      l.status IN (0, 4)
      AND l.published = 1
      AND l.hidden = 0
      AND l.rega_licensed = 1
      AND l._peerdb_is_deleted = 0
      AND toDate(fromUnixTimestamp(l.create_time)) >= '2024-01-01'
    GROUP BY
      l.id,
      l.category,
      l.district_id
  )
GROUP BY
  category,
  district_id,
  days_bucket
  -- Step 2: Calculate PI using lookup
SELECT
  l.id,
  vpd / greatest(lookup.expected_vpd, 0.01) AS performance_index
FROM
  listings l
  JOIN expected_vpd_lookup lookup ON l.category = lookup.category
  AND l.district_id = lookup.district_id
  AND l.days_bucket = lookup.days_bucket