Skip to content
Aqar Data Analysis

Underperforming Listings and Promotion Impact (Event Data)

This repeats the underperforming listings and promotion impact analysis using only the client event stream analytics.events, the source behind Metabase question 1149:

  • Impressions = listing_impression
  • Views = listing_view
  • Leads = listing_call + listing_whatsapp + listing_msg

Promotion schedules still come from the promotion tables, since they aren’t events. Contact events are only populated from 2026-08-31. The app version that sends these events was rolled out in phases during September, so event coverage grows over the month (see Data Coverage Notes).

Scope: active property listings (status IN (0, 4) AND published = 1 AND rega_licensed = 1) created 2024+ with valid coordinates.

Peers are defined by distance, not district: nearby never-promoted listings of the same category and rent period, within 2 km and widening to 5 km (see Proximity peers). The per-listing comparison is stored daily in the Metabase table metabase.listing_proximity_peers, which feeds the promotion target list (Q1177). SOV, boosts and notification messages are measured separately. Golden campaigns are left out of the comparison; they only exclude overlapping events.

Revised 2026-10-08: replaced category × district peer clusters with proximity peers (≥50 peers, ≥20 for land and buildings), added the listing_proximity_peers table, dropped golden, extended the event study to promotions starting up to 2026-09-30, reported each product separately, and added boost prices from sadb_transactions.

Executive Summary

Section titled “Executive Summary”

  • 36% of listings with a local peer group underperform it. Of 111,692 active listings with a local peer group (≥50 nearby peers, ≥20 for land and buildings; 28 days to 2026-10-07):

    • 14.0% have low visibility (<50% of the peer median impressions)
    • 9.2% have low CTR (shown but not clicked)
    • 12.7% have low conversion (viewed but not contacted)
    • 64.1% are healthy

    Another 19,229 listings (15%) have too few comparable listings within 5 km and aren’t classified.

  • Low visibility is about age and presentation, not price.

    • These listings are older (median 72 days), have fewer photos (5) and less video (26%), and are priced at the local median (1.04×).
    • 65% got no lead in 28 days.
    • Only 0.1% were promoted (SOV/golden), against 1.3% of healthy listings.

  • Low CTR on rentals is partly a price problem. Low-CTR Apartment for Rent listings ask 1.16× nearby same-size units (44% more than 20% above). Sale apartments with low CTR are priced at the local median (1.00×).

  • Promotion lifts on event data (7 days from start, placebo-adjusted):

    Product Promotions analysed Impressions Views Leads Leads/7d before → after Incremental leads/7d SAR per incremental lead
    SOV 519 +88% +91% +62% 3.09 → 5.16 +2.0 ~60
    Boost 74 +1198% +326% +198% 3.68 → 13.30 +8.8 ~15
    Notification msgs 11 −4% +39% 0% 5.27 → 5.91 ~0 inconclusive (n = 11)

    The 14-day stats-based study has larger samples. It gives boosts ~9 SAR and notification messages ~86 SAR per incremental lead (n = 336 and 141).

  • Boosts are the cheapest leads. The boost price comes from sadb_transactions: 84.99 SAR for 150 guaranteed views, 164.99 for 250, 299.99 for 500. An average 130 SAR paid per boost buys ~8.8 extra leads in the first week, ~15 SAR each, against ~60 for SOV. Boosts can only be bought on iOS.

  • Underperformers gain the most from SOV. Listings below half their peer expectation get +200% leads (1.10 → 3.47 per week), against +23% for strong listings. That is the argument for aiming SOV at the low-visibility segment that almost never buys it.

Proximity Peers Table (metabase.listing_proximity_peers)

Section titled “Proximity Peers Table (metabase.listing_proximity_peers)”

Metabase transform 5, “Listing Proximity Peers”, rebuilds this table every day at 1 AM Riyadh (job tag “daily”). It has one row per active property listing that was live for the whole window. The window is the last 28 days of analytics.events, ending yesterday (Riyadh time).

Column Meaning
window_start, window_end The 28-day event window
listing_id, user_id, category, city_id, district_id, lat, lon Listing identity and location (location is POINT (lon lat))
rent_period, beds, area_bin Peer keys: sale / yearly / monthly / other; bedrooms capped at 6; floor(log2(area)) (−1 = missing)
created_on, price_yearly_or_meter, images, has_video Listing attributes. Price is yearly for rentals (monthly × 12) and per m² for land
impressions, views, leads Event counts in the window
sov_in_window, golden_in_window Chargeable SOV or paid golden in the window. These listings are never used as peers
peer_level, peer_radius_m, peers The first level with at least min_peers peers (1–3 = 2 km, 4–6 = 5 km; 1/4 match beds + area bin, 2/5 beds, 3/6 neither; 0 = no group), its radius and peer count
min_peers 50, or 20 for land and buildings (categories 2, 7, 15, 16)
peer_med_im, peer_med_ctr, peer_med_cvr, peer_med_ld, peer_med_price Peer medians: impressions, views/impressions, leads/views, leads, price
price_vs_peers Price ÷ peer median price
funnel_class 0 no peer group, 1 low visibility, 2 low CTR, 3 low conversion, 4 healthy

  • The transform takes ~70 s. Nearby pairs come from a 0.06° grid join (±1 cell covers 5 km) and are then filtered by greatCircleDistance.

  • Threshold choice. The table needs ≥50 peers, so medians aren’t driven by a handful of listings. Land and buildings (Land/Building for Sale/Rent) need only ≥20, because they are too sparse for 50: with 50, 29% of Land for Sale and 46% of Building for Sale had no group. With the mixed rule, 14.7% of all listings have no group, including 14.7% of Land for Sale, 21.4% of Building for Sale, 45% of Building for Rent and 83% of Land for Rent. The share with no group if every category used the same threshold:

    Minimum peers No group Level 1 (2 km, same beds + area) Median peers at level 1
    10 6.9% 60.9% 46
    20 10.4% 47.5% 67
    30 13.2% 39.0% 89
    50 18.1% 29.1% 137

Event Data Overview

Section titled “Event Data Overview”

Event totals for active property listings created before 2026-09-01, 2026-09-01 to 2026-09-28:

Metric Events
Impressions (listing_impression) 103.8M
Views (listing_view) 22.97M
Leads 805k
– WhatsApp 507k
– calls 244k
– msgs 54k

The impression definition differs by platform. In the week of 09-20 to 09-26, impressions per view were 2.5 on iOS, 3.4 on Android and 14.9 on web. Impression-based comparisons are made against nearby listings of the same type, which have a similar platform mix, so this mostly cancels out. Absolute CTRs across platforms are not comparable, though.

Data Coverage Notes

Section titled “Data Coverage Notes”

Two things affect how complete each data source is.

  1. Events: phased app rollout. The new app version that sends analytics.events wasn’t forced on all users, and the rollout was phased. It is believed to have completed around the week of 2026-09-21. Users still on versions from before the force update keep producing no events. listing_view events by platform show where the ramp came from:

    • iOS (v3.71+) and web sent events for the whole month.
    • Android sent almost nothing in the week of 08-31 (63 views). It started in the week of 09-07 (0.58M views) and doubled the week after (1.18M, 3.5.19 → 3.5.21).

  2. Stats table: view dedup and server-side impressions. Confirmed in api-service (origin/prod, 2026-09-30):

    • Only views are rate-limited.
      • Legacy path: Listing.increment → validStatsRate sets a Redis key listing_views:{listing_id}:{ip} with a 24-hour TTL. It’s a rolling 24 hours from the first view, not a calendar date.
      • Events path: the same key is used, so the two paths can’t double-count.
      • Only the first open per listing per IP counts. It fails open on Redis errors and only applies on the web API server.
      • For listings, impressions, calls, WhatsApp and messages are not rate-limited. For real-estate projects, every counter type is IP-gated per project and type.
    • The events API doesn’t drop views. It stores every listing_view and tags it props['counted'] = '1'/'0'. Only counted views fan out to listings_stats.views. In September, 13% of iOS, 18% of Android and 22% of web listing_view events were gated out, so raw event views are ~1.2× the deduplicated count.
    • Stats impressions are counted per search response, server-side. When a search request carries track_impressions, Search.js adds one impression for every listing in the returned page, whether or not it is ever scrolled into view. Since August 2026, client listing_impression events (≥50% of the card visible) are also fanned out to listings_stats.impressions. This explains why stats impressions are ~6.4× event impressions, unevenly across listings: listings that sit on frequently loaded result pages collect server-side impressions without being seen.
    • The legacy increment path skips listings not in status = 0. Suspended listings (status 4) get no legacy increments.

    Sources: entities/Listing.js (increment, validStatsRate), entities/Search.js, modules/events/registry.js, modules/events/usecases/emit_event.js and fan_out_legacy_stats.js. aqar-platform-history also records a “stats rate limiter” and a “stats validator ip address” fix, both disabled or changed on 2020-10-22 (Mustafa Nimir). Those predate the current view gate.

Weekly totals across all listings show both effects:

Week (Mon) Views: events Views: stats Events ÷ stats Leads: events Leads: stats Events ÷ stats
2026-08-31 7.21M 9.01M 0.80 265k 446k 0.59
2026-09-07 8.47M 9.15M 0.93 312k 433k 0.72
2026-09-14 9.58M 8.60M 1.11 367k 400k 0.92
2026-09-21 9.35M 8.13M 1.15 352k 366k 0.96
  • Event coverage ramps up through September. Event leads went from 59% of the stats count in the first week to 96% in the last. Most of the rollout landed by mid-September. The remaining gap is plausibly users on older app versions.
  • Once rollout is mostly done, events count more views than stats (1.11–1.15×). That is the view dedup: events keep every open, and stats counts one per listing, per IP, per 24 hours. Events views filtered to props['counted'] = '1' are the like-for-like comparison with stats. Neither source is complete: events miss users on old versions, and stats misses repeat opens.
  • Contacts aren’t rate-limited on either side. The remaining lead gap (0.96 in the last week) is therefore most plausibly old app versions, not dedup.

How this affects the analysis:

  • Part A (classes) uses 2026-09-10 to 10-07, which is mostly after the rollout. Each listing is compared with its own peers over the same 28 days, so any remaining growth affects all peers the same way, as long as adoption doesn’t differ much between neighbouring listings.
  • Part B (event study): every pre-period falls earlier than its post-period, so raw before → after numbers are inflated by rollout growth (roughly +20% on leads week over week in early September). The adjustment uses non-promoted peers on the same calendar days, which cancels this common growth. The placebo confirms it: its adjusted lead ratio is 0.96, not ~1.2. A bias remains only if brokers who buy promotions have audiences that adopted the new app at a different rate. The raw “before → after” columns in the tables are not adjusted and include rollout growth.

Part A — Underperforming Listings Landscape

Section titled “Part A — Underperforming Listings Landscape”

Source: metabase.listing_proximity_peers, window 2026-09-10 to 2026-10-07. This is after the app rollout was mostly complete, so event coverage is near full for most of the window.

Each listing is compared with its proximity peers and assigned the first funnel stage where it falls below 50% of the peer median:

  1. Low visibility: impressions < 0.5 × peer median
  2. Low CTR: views/impressions < 0.5 × peer median CTR
  3. Low conversion: leads/views < 0.5 × peer median
  4. Healthy

Overall profile (28 days)

Section titled “Overall profile (28 days)”
Class Listings Share Avg impressions Avg views Avg leads Zero leads Median price vs peers Priced >20% above Median imgs Has video Promoted (SOV/golden) Median age (d) Avg peers
Low visibility 15,667 14.0% 173 42.3 1.39 65% 1.04 24% 5 26% 0.1% 72 173
Low CTR 10,256 9.2% 1,510 109.0 2.28 58% 1.04 26% 8 47% 1.1% 67 194
Low conversion 14,135 12.7% 883 190.6 1.34 59% 1.04 31% 6 31% 0.9% 67 94
Healthy 71,634 64.1% 872 237.5 9.79 24% 1.00 20% 6 37% 1.3% 64 154
No peer group 19,229 (14.7% of all) 1,156 237.4 8.39 24% 4 27% 0.7% 77
  • Low visibility: the listings get a fifth of the healthy impressions. They are older, with fewer photos and videos, but not overpriced. This is a ranking/freshness gap, which is what SOV and boosts address.
  • Low CTR: the listings get 1.7× the impressions of healthy listings and have more media (8 photos, 47% video), but are clicked much less. Price is part of it, mainly in rentals.
  • Low conversion: viewed almost as often as healthy listings, but 59% get no lead at all. Contact options or listing details are the next thing to check.
  • No peer group: land is still the largest share (2,957), then villas for sale (2,216) and apartments for rent (1,363), mostly outside dense areas. On average they get 8.4 leads/28d (healthy: 9.8), but they can’t be benchmarked locally.

By category (share of each category’s active listings)

Section titled “By category (share of each category’s active listings)”
Category Listings No peer group Low visibility Low CTR Low conversion Healthy Low-CTR median price vs peers (% >20% above)
Apartment for Sale 35,222 2.9% 16.4% 11.7% 5.5% 63.6% 1.00 (12%)
Apartment for Rent 24,693 5.5% 12.8% 9.5% 13.2% 59.0% 1.16 (44%)
Villa for Sale 20,560 10.8% 11.3% 5.8% 15.8% 56.3% 1.04 (26%)
Land for Sale 20,145 14.7% 10.3% 6.0% 14.6% 54.4% 1.06 (29%)
Big Flat for Sale 8,321 12.2% 13.5% 6.9% 9.6% 57.9% 1.07 (22%)
Building for Sale 3,559 21.4% 7.1% 3.8% 17.1% 50.6% 1.22 (51%)
Villa for Rent 3,506 23.9% 7.4% 6.2% 13.1% 49.5% 1.12 (35%)
  • Apartment for Sale has the most low-visibility and low-CTR listings, and the fewest leads per listing. Its low-visibility listings average 0.2 leads in 28 days (89% get none).
  • Villa and Land for Sale lose more listings at the conversion stage (15–16%). They are viewed but rarely contacted (71–77% zero leads).
  • Land and buildings are compared with 20+ nearby peers (avg ~40) instead of 50+, so their medians are noisier.

By city

Section titled “By city”
City Listings No peer group Low visibility Low CTR Low conversion Healthy
Riyadh 60,909 6.3% 13.5% 7.3% 13.7% 59.1%
Jeddah 31,930 6.6% 14.7% 11.7% 5.7% 61.4%
Al Khobar 6,528 7.1% 11.2% 8.4% 10.8% 62.5%
Dammam 6,434 10.0% 9.9% 7.1% 16.2% 56.8%
Medina 3,501 34.4% 7.2% 5.6% 12.7% 40.2%
  • In Jeddah, peer groups are very large (avg ~340 peers for low-visibility and low-CTR listings). Even healthy listings average only 5.1 leads/28d, and 49% get none.
  • In Riyadh, low-CTR listings are more expensive (1.10× peers, 35% priced >20% above).
  • In Jeddah, low-CTR listings aren’t (1.00×). There, the issue is more likely presentation or listing quality than price.
  • Medina is too sparse for local peers: 34% of its listings have no group.

Part B — Do Promotions Lift Performance?

Section titled “Part B — Do Promotions Lift Performance?”

Method

Section titled “Method”

Promotions aren’t randomly assigned, so this uses an event study with difference-in-differences:

  1. Events. First promotion per listing that started between 2026-09-07 and 2026-09-30, the latest date that still leaves a full 7-day post window in the events data (through 2026-10-07). Each listing is compared on its own 7 days before vs. the 7 days from the start.
  2. Expected post-period from the 50 nearest peers.
    • Candidate peers are never-promoted listings of the same category and rent period. They must have been live for the whole 14-day window, created at least 7 days before the event and not closed before it ended.
    • The first proximity level with ≥10 candidates is used, keeping its 50 nearest listings. For SOV, 270 events used level 1, 98 level 2, 88 level 3 and 63 levels 4–6.
    • Expected post = the listing’s own pre-period × (peers’ daily-mean post ÷ peers’ daily-mean pre), with at least 5 peers reporting on every day.
    • Lift = Σ actual ÷ Σ expected − 1.
  3. Placebo. 3,143 randomly sampled never-promoted listings with random pseudo-event dates go through the same pipeline. Raw placebo ratios are 1.00 (impressions), 1.01 (views) and 0.96 (leads). All lifts are divided by the matching placebo ratio (the segment’s own when it has ≥40 listings, otherwise overall).
  4. Exclusions.
    • Listings younger than 14 days at the event.
    • SOV in the prior 28 days (for SOV).
    • Any other SOV, golden, notification or boost within ±7 days.
    • Listings with views on fewer than 4 of 7 days in either window.
  5. Cost.
    • SOV cost is the sum of daily budgets (points ÷ 100) on chargeable bidding days within the 7-day post window. That averages 118 SAR over 3.1 days.
    • Boost cost is the price paid in sadb_transactions (transaction_category = 4, listing id parsed from the note) for purchases from the day before the boost record to the end of the post window. That averages 130 SAR per boost. 3 of the 74 boosts have no purchase (admin grants) and count as 0.

Overall effect (placebo-adjusted)

Section titled “Overall effect (placebo-adjusted)”
Product n Impressions Views Leads Impressions/7d before → after Views/7d before → after Leads/7d before → after Incremental leads SAR per incremental lead
SOV 519 +88% +91% +62% 578 → 1,128 90.0 → 179.1 3.09 → 5.16 +2.0 ~60
Boost 74 +1198% +326% +198% 824 → 11,036 108.6 → 489.3 3.68 → 13.30 +8.8 ~15
Notification msgs 11 −4% +39% 0% 509 → 519 115.1 → 172.4 5.27 → 5.91 ~0 n/a
Placebo (raw) 3,143 0% +1% −4% 268 → 279 62.5 → 65.3 2.35 → 2.47

SOV

Section titled “SOV”
Segment n Impressions Views Leads Leads/7d before → after Incremental leads SAR per incremental lead
Under (<0.5× peers) 91 +441% +228% +200% 1.10 → 3.47 +2.3 ~56
Typical (0.5–1.5×) 276 +173% +106% +88% 2.56 → 4.73 +2.2 ~50
Strong (>1.5×) 152 +43% +60% +23% 5.25 → 6.93 +1.3 ~96
Apartment for Rent 129 +68% +90% +75% 5.20 → 9.76 +4.2 ~28
Villa for Sale 95 +137% +108% +83% 2.02 → 3.42 +1.6 ~90
Apartment for Sale 94 +147% +173% +190% 1.21 → 3.02 +2.0 ~54
Land for Sale 70 +129% +59% +36% 1.16 → 1.61 +0.4 ~251
Big Flat for Sale 40 +91% +97% +65% 1.50 → 2.48 +1.0 ~121
Riyadh 288 +101% +89% +51% 4.03 → 6.67 +2.3 ~55
Jeddah 84 +51% +88% +78% 2.83 → 4.51 +2.0 ~66
Al Khobar 43 +273% +139% +144% 1.30 → 2.58 +1.5 ~62
Dammam 30 +223% +123% +212% 0.80 → 2.13 +1.5 ~81
Apartment for Rent / Riyadh 79 +85% +91% +73% 5.85 → 11.37 +4.8 ~26
Villa for Sale / Riyadh 56 +142% +118% +60% 2.25 → 4.04 +1.5 ~97
Big Flat for Sale / Riyadh 39 +90% +100% +65% 1.54 → 2.51 +1.0 ~121
Apartment for Sale / Riyadh 31 +95% +181% +210% 1.39 → 4.77 +3.2 ~32
Apartment for Sale / Jeddah 30 +170% +201% +48% 1.20 → 1.60 +0.5 ~264
  • SOV’s relative lift is ~9× larger for underperformers than for strong listings. The cost per incremental lead is also lower (~50–56 SAR vs ~96).
  • Apartments for rent give the cheapest SOV leads (~26–28 SAR). Land gives the most expensive (~250 SAR), because land listings get few leads to begin with.
  • Placebo impressions for the “under” bucket rise +9% on their own (regression to the mean). Dividing by the placebo removes that effect.

Boost

Section titled “Boost”
Segment n Impressions Views Leads Leads/7d before → after Incremental leads Avg paid (SAR) SAR per incremental lead
All 74 +1198% +326% +198% 3.68 → 13.30 +8.8 130 ~15
Under (<0.5× peers) 17 +3314% +1022% +1023% 0.71 → 9.94 +9.1 107 ~12
Typical (0.5–1.5×) 35 +3162% +345% +183% 3.57 → 12.23 +7.9 133 ~17
Strong (>1.5×) 22 +639% +230% +138% 6.14 → 17.59 +10.2 141 ~14
150-view package (84.99 SAR) 50 +1193% +324% +194% 3.24 → 12.36 +8.2 90 ~11
Apartment for Rent 30 +1476% +353% +169% 5.27 → 19.80 +12.4
Riyadh 41 +1236% +369% +287% 3.46 → 15.05 +11.2
  • A boost guarantees a number of views (150, 250 or 500), and the listing is pushed until it gets them. Impressions grow ×13 and leads ×3 in the first week.
  • It adds ~4× the incremental leads of an SOV campaign at a similar outlay (130 vs 118 SAR), so each extra lead costs ~15 SAR against ~60 for SOV.
  • The 250- and 500-view packages have only 9 and 12 boosts in this window. The stats-based study compares packages over 14 days.

Notification messages

Section titled “Notification messages”

Only 11 paid notification campaigns between 2026-09-07 and 09-30 survive the exclusions, so the event data can’t measure them (views +39%, leads 0%). Use the stats-based estimate (+28% leads, ~86 SAR per incremental lead, with the lift concentrated in underperforming listings) until more event history accumulates.

Recommendations

Section titled “Recommendations”
  1. Aim SOV at low-visibility listings.
    • They are 14% of listings with a local peer group, respond with +200% leads at ~56 SAR per incremental lead, and only 0.1% of them are promoted today.
    • Q1177 lists them with a “your listing gets less than half the exposure of similar listings nearby” rationale.
  2. Offer a boost first wherever it can be bought. At ~15 SAR per incremental lead (~9 SAR on 14-day stats data), boosts are the cheapest promotion, but only iOS sells them. Bringing the purchase back to Android and web would extend it to most brokers.
  3. Hold notification messages to underperforming listings until a holdout test confirms the stats-based effect.
  4. For low-CTR rentals, nudge price, not visibility. They ask 1.16× nearby same-size units and are already over-exposed. For low-CTR sale listings (not overpriced), prompt for better photos and titles.
  5. Use ~60 SAR per incremental lead as the SOV benchmark on event-tracked contacts (~40 SAR on stats leads).
  6. Rerun in November with 14-day windows. Starting from 2026-09-21, after rollout was largely complete, gives notification messages enough clean events.
  7. Document the stats-table counter semantics in CLICKHOUSE_SCHEMA.md, along with the event app-version coverage:
    • Views are deduplicated per listing + IP per rolling 24 hours.
    • Impressions are counted per search response server-side, plus client events.
    • Contacts are not deduplicated.
  8. Sync listing_notification_campaigns and boosted_listings to ClickHouse. Both are MySQL-only today and have to be exported by hand for any promotion analysis. Boost prices are already in ClickHouse through sadb_transactions.

Promotion Target List (Metabase Q1177)

Section titled “Promotion Target List (Metabase Q1177)”

The recommendations are published as a rolling target list: Q1177 — Promotion target list (collection “2 - Listings”). It reads metabase.listing_proximity_peers, so it updates daily after transform 5 runs.

  • Eligible listings: listings with a proximity peer group (peer_level > 0: ≥50 peers, ≥20 for land and buildings) and no SOV or golden in the window.
  • Filters: recommended_action (required, default SOV), city (default Riyadh) and test_group. Only rows with est_extra_leads_per_week ≥ 1 are listed.
Class Condition Recommended action Eligible listings (2026-10-08) With est. ≥1 lead/week
Low visibility price ≤1.2× peers SOV 11,915 1,280
Low visibility or low CTR price >1.2× peers Price nudge (no promotion) 6,388 1,535
Low CTR price ≤1.2× peers Photo / title nudge (no promotion) 7,494 645
Low conversion Content / contact nudge (no promotion) 14,013 2,732

  • Golden is no longer recommended. Low CTR at a fair price is a presentation problem, so it gets a photo/title nudge instead.

  • Boosts and notification messages aren’t separate actions in the list. All three products lift low-visibility listings. SOV stays the default because every broker can buy it.

    • Boosts are cheaper per lead (~15 SAR on event data vs ~60 for SOV) but can only be bought on iOS. Offer a boost to brokers on iOS.
    • Offer notification messages to the same underperformers.

  • peer_level, peer_radius_m, peers, beds and area_bin_m2 show which local market a listing was compared with.

  • est_extra_leads_per_week ranks listings within an action. It is the listing’s lead gap to its peer median (per week), times the category’s SOV lead responsiveness relative to the overall SOV lift. The responsiveness comes from the 14-day stats study, which has the larger samples:

    • Apartment for Sale 1.60
    • Big Flat for Sale 1.18
    • Land for Sale 1.00
    • Apartment for Rent 0.93
    • Villa for Sale 0.76
    • Building for Sale 0.58
    • other categories 1.0

    It is a prioritisation score, not a forecast.

  • test_group is a stable 50/50 split (cityHash64(listing_id, 'promo_target_v1') % 2). Contact or promote only the test half, then compare both halves’ event leads over the next 7–14 days. That gives an unbiased estimate of the targeted effect.

  • Check price_vs_peers below ~0.5 by hand. These are usually price entry errors (for example a monthly rent entered as yearly) rather than real bargains.

  • data_through is the last day of the event window.

-- Promotion target list (event data). Reads metabase.listing_proximity_peers (Metabase transform 5, refreshed daily at 1 AM Riyadh):
-- rolling 28 days of analytics.events ending yesterday, each active listing compared with nearby never-promoted listings of the
-- same category and rent period (first level with >= 50 peers, >= 20 for land and buildings: 2 km same beds + area bin -> 2 km same beds -> 2 km -> 5 km ...).
-- Method: /findings/2026-09-30_underperforming_listings_promotion_impact_events/
WITH R AS (
  SELECT
    p.listing_id AS listing_id,
    p.user_id AS user_id,
    p.category AS category,
    p.district_id AS district_id,
    p.beds AS beds_b,
    p.area_bin AS area_b,
    p.peer_level AS peer_level,
    p.peer_radius_m AS peer_radius_m,
    p.peers AS peers,
    today() - p.created_on AS age_days,
    p.impressions AS im, p.views AS vw, p.leads AS ld,
    p.peer_med_im AS peer_med_im,
    p.peer_med_ld AS peer_med_ld,
    round(p.price_vs_peers, 2) AS price_vs_peers,
    p.funnel_class AS funnel_class,
    p.window_end AS data_through,
    multiIf(
      p.funnel_class = '1 low visibility' AND (p.price_vs_peers <= 1.2 OR p.price_vs_peers IS NULL), 'SOV',
      p.funnel_class IN ('1 low visibility', '2 low CTR') AND p.price_vs_peers > 1.2, 'Price nudge (no promotion)',
      p.funnel_class = '2 low CTR', 'Photo / title nudge (no promotion)',
      p.funnel_class = '3 low conversion', 'Content / contact nudge (no promotion)',
      'None'
    ) AS recommended_action,
    -- SOV lead-lift responsiveness by category relative to the overall SOV lift (distance-peer event study, stats data, Mar-Sep 2026)
    transform(p.category, [1, 6, 3, 2, 22, 7], [0.93, 1.60, 0.76, 1.00, 1.18, 0.58], 1.0) AS category_response,
    round(greatest(p.peer_med_ld - p.leads, 0) / 4 * category_response, 2) AS est_extra_leads_per_week,
    if(cityHash64(p.listing_id, 'promo_target_v1') % 2 = 0, 'test', 'holdout') AS test_group
  FROM metabase.listing_proximity_peers p
  WHERE p.peer_level > 0
    AND NOT p.sov_in_window
    AND NOT p.golden_in_window
)


SELECT
  R.listing_id,
  R.user_id,
  cat.name_en AS category,
  d.city_name_en AS city,
  d.district_name_en AS district,
  R.beds_b AS beds,
  if(R.area_b >= 0, concat(toString(pow(2, R.area_b)), '-', toString(pow(2, R.area_b + 1) - 1)), 'n/a') AS area_bin_m2,
  R.funnel_class,
  R.recommended_action,
  R.est_extra_leads_per_week,
  R.im AS impressions_28d,
  R.peer_med_im AS peer_median_impressions,
  R.vw AS views_28d,
  R.ld AS leads_28d,
  R.peer_med_ld AS peer_median_leads,
  R.price_vs_peers,
  R.age_days, R.peer_level, R.peer_radius_m, R.peers, R.test_group, R.data_through
FROM R
LEFT JOIN sadb_categories cat FINAL ON cat.category_id = R.category
LEFT JOIN sadb_districts d FINAL ON d.district_id = R.district_id
WHERE R.recommended_action != 'None'
  [[ AND recommended_action = {{ recommended_action }} ]]
  [[ AND city = {{ city }} ]]
  AND est_extra_leads_per_week >= 1
  [[ AND test_group = {{ test_group }}]]
ORDER BY
  R.est_extra_leads_per_week DESC,
  R.peer_med_im - R.im DESC

Caveats

Section titled “Caveats”
  • Phased rollout. Event coverage grew through September: leads were 59% of the stats count in the week of 08-31 and 96% in the week of 09-21. Part A’s window (from 09-10) is mostly post-rollout. In Part B, peer and placebo adjustment remove the common trend, but the raw before → after columns do not.
  • The stats table can’t serve as ground truth either. Stats views are deduplicated per listing + IP per rolling 24 hours, and stats impressions include server-side counts for every listing in a search response.
  • Peer coverage. 15% of listings have too few comparable listings within 5 km (50, or 20 for land and buildings). This concentrates in Land for Rent (83%), Building for Rent (45%), villas for rent and smaller cities (Medina 34%). They don’t appear in Q1177.
  • The promotion study uses 7-day windows. SOV campaigns average ~3 active days, so a 7-day window captures most of their effect but none of their persistence.
  • Impressions per view differ by platform (web ≈ 15, iOS ≈ 2.5). Comparing listings with their immediate neighbours reduces but doesn’t remove this.
  • SOV cost uses the daily budget as the spend, which may overstate the actual charge.
  • Bedrooms and area are broker-entered. Missing or zero area falls into its own bin, and bedrooms are capped at 6.
  • Small cells (notification 11, boost 74, most segment rows 30–130) are directional only.
  • Boost costs come from purchases matched by listing and date. Boosts with no purchase (admin grants) count as free.

SQL Queries

Section titled “SQL Queries”

All queries run on ClickHouse through Metabase (db 8), because analytics.events isn’t visible to the ClickHouse MCP user.

Event data totals

Section titled “Event data totals”
SELECT
  countIf (event_name = 'listing_impression') AS impressions,
  countIf (event_name = 'listing_view') AS views,
  countIf (event_name = 'listing_call') AS calls,
  countIf (event_name = 'listing_whatsapp') AS whatsapp,
  countIf (event_name = 'listing_msg') AS msgs
FROM
  analytics.events
WHERE
  event_date BETWEEN '2026-09-01' AND '2026-09-28'
  AND listing_id IN (
    SELECT
      id
    FROM
      sadb_listings FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND status IN (0, 4)
      AND published = 1
      AND rega_licensed = 1
      AND create_time >= 1704067200
      AND toDate(toDateTime(create_time)) < '2026-09-01'
  )

Weekly coverage: events vs stats

Section titled “Weekly coverage: events vs stats”
WITH
  e AS (
    SELECT
      toMonday(event_date) wk,
      countIf (event_name = 'listing_view') vw,
      countIf (
        event_name IN ('listing_call', 'listing_whatsapp', 'listing_msg')
      ) ld
    FROM
      analytics.events
    WHERE
      event_date BETWEEN '2026-08-31' AND '2026-09-27'
      AND listing_id > 0
    GROUP BY
      wk
  ),
  s AS (
    SELECT
      toMonday(toDate(day_time) - 1) wk,
      sum(views) vw,
      sum(calls + whatsapp + msgs) ld
    FROM
      sadb_stats_listings_stats FINAL
    WHERE
      day_time BETWEEN toUInt16(toDate('2026-09-01')) AND toUInt16(toDate('2026-09-28'))
    GROUP BY
      wk
  )
SELECT
  e.wk,
  e.vw,
  s.vw,
  round(e.vw / s.vw, 3) vw_ratio,
  e.ld,
  s.ld,
  round(e.ld / s.ld, 3) ld_ratio
FROM
  e
  JOIN s ON s.wk = e.wk
ORDER BY
  e.wk

Proximity peers table (Metabase transform 5 → metabase.listing_proximity_peers)

Section titled “Proximity peers table (Metabase transform 5 → metabase.listing_proximity_peers)”

{{ table }} is the transform’s table variable for analytics.events.

-- Proximity peer groups for active property listings (rolling 28 days of analytics.events, ending yesterday, Riyadh time).
-- Peers: never-promoted listings of the same category and rent period, nearest level with >= 50 peers (>= 20 for land and buildings, categories 2, 7, 15, 16):
--   1: <=2 km, same beds + area bin | 2: <=2 km, same beds | 3: <=2 km | 4: <=5 km, same beds + area bin | 5: <=5 km, same beds | 6: <=5 km
-- Method: /findings/2026-09-30_underperforming_listings_promotion_impact_events/
WITH
  toDate(now(), 'Asia/Riyadh') - 28 AS d0,
  toDate(now(), 'Asia/Riyadh') - 1 AS d1,
cats AS (SELECT category_id, positionCaseInsensitive(name_en, 'rent') > 0 AS is_rent FROM sadb_categories FINAL),
L AS (
  SELECT toInt64(l.id) AS id, toInt64(l.user_id) AS user_id, l.category AS category, l.city_id AS city_id, l.district_id AS district_id,
    toDate(toDateTime(l.create_time)) AS cdate,
    toFloat64(if(l.category = 2, l.meter_price, if(l.rent_period = 2, l.price * 12, l.price))) AS p,
    if(k.is_rent, multiIf(l.rent_period = 2, 'monthly', l.rent_period = 1, 'other', 'yearly'), 'sale') AS rp,
    least(greatest(l.beds, 0), 6) AS beds_b,
    if(l.area > 0, toInt32(floor(log2(l.area))), -1) AS area_b,   -- doubling bins: 128-255, 256-511 m2, ...
    toFloat64OrNull(extractAll(l.location, '-?\\d+\\.\\d+')[2]) AS lat,   -- location is POINT (lon lat)
    toFloat64OrNull(extractAll(l.location, '-?\\d+\\.\\d+')[1]) AS lon,
    if(l.imgs = '', 0, length(splitByChar(',', l.imgs))) AS n_img, l.has_video AS has_video
  FROM sadb_listings l FINAL LEFT JOIN cats k ON k.category_id = l.category
  WHERE l._peerdb_is_deleted = 0 AND l.status IN (0, 4) AND l.published = 1 AND l.rega_licensed = 1
    AND l.create_time >= 1704067200 AND toDate(toDateTime(l.create_time)) <= d0   -- live for the whole window
    AND lat BETWEEN 15 AND 33 AND lon BETWEEN 34 AND 56
),
E AS (
  SELECT toInt64(listing_id) AS id,
    countIf(event_name = 'listing_impression') AS im,
    countIf(event_name = 'listing_view') AS vw,
    countIf(event_name IN ('listing_call', 'listing_whatsapp', 'listing_msg')) AS ld
  FROM {{ table }}
  WHERE event_date BETWEEN d0 AND d1 AND listing_id IN (SELECT id FROM L)
    AND event_name IN ('listing_impression', 'listing_view', 'listing_call', 'listing_whatsapp', 'listing_msg')
  GROUP BY id
),
sov AS (
  SELECT DISTINCT toInt64(listing_id) AS id FROM sadb_sov_daily_biddings FINAL
  WHERE _peerdb_is_deleted = 0 AND non_chargeable = 0 AND toDate(start_datetime) BETWEEN d0 AND d1
),
golden AS (
  SELECT DISTINCT toInt64(listing_id) AS id FROM sadb_golden_campaigns FINAL
  WHERE _peerdb_is_deleted = 0 AND status IN ('paid', 'done') AND toDate(start_date) <= d1 AND toDate(end_date) >= d0
),
X AS (
  SELECT L.id AS id, L.user_id AS user_id, L.category AS category, L.city_id AS city_id, L.district_id AS district_id,
    L.cdate AS cdate, L.p AS p, L.rp AS rp, L.beds_b AS beds_b, L.area_b AS area_b, L.lat AS lat, L.lon AS lon,
    L.n_img AS n_img, L.has_video AS has_video,
    E.im AS im, E.vw AS vw, E.ld AS ld,   -- LEFT JOIN misses are 0
    L.id IN (SELECT id FROM sov) AS sov_in_window, L.id IN (SELECT id FROM golden) AS golden_in_window,
    toInt32(floor(L.lat / 0.06)) AS cy, toInt32(floor(L.lon / 0.06)) AS cx   -- 0.06 deg cells: +-1 cell covers 5 km
  FROM L LEFT JOIN E ON E.id = L.id
),
pool AS (SELECT * FROM X WHERE NOT sov_in_window AND NOT golden_in_window),   -- peers are never promoted in the window
A AS (SELECT id, category, rp, beds_b, area_b, lat, lon, cy + dy AS py, cx + dx AS px FROM X ARRAY JOIN [-1,-1,-1,0,0,0,1,1,1] AS dy, [-1,0,1,-1,0,1,-1,0,1] AS dx),
PR AS (
  SELECT a.id AS id,
    countIf(d <= 2000 AND b.beds_b = a.beds_b AND b.area_b = a.area_b) AS n1,
    countIf(d <= 2000 AND b.beds_b = a.beds_b) AS n2,
    countIf(d <= 2000) AS n3,
    countIf(b.beds_b = a.beds_b AND b.area_b = a.area_b) AS n4,
    countIf(b.beds_b = a.beds_b) AS n5,
    countIf(1) AS n6,
    [quantileIf(0.5)(b.im, d <= 2000 AND b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.im, d <= 2000 AND b.beds_b = a.beds_b), quantileIf(0.5)(b.im, d <= 2000), quantileIf(0.5)(b.im, b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.im, b.beds_b = a.beds_b), quantileIf(0.5)(b.im, 1)] AS m_im,
    [quantileIf(0.5)(b.vw / nullIf(b.im, 0), d <= 2000 AND b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.vw / nullIf(b.im, 0), d <= 2000 AND b.beds_b = a.beds_b), quantileIf(0.5)(b.vw / nullIf(b.im, 0), d <= 2000), quantileIf(0.5)(b.vw / nullIf(b.im, 0), b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.vw / nullIf(b.im, 0), b.beds_b = a.beds_b), quantileIf(0.5)(b.vw / nullIf(b.im, 0), 1)] AS m_ctr,
    [quantileIf(0.5)(b.ld / nullIf(b.vw, 0), d <= 2000 AND b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.ld / nullIf(b.vw, 0), d <= 2000 AND b.beds_b = a.beds_b), quantileIf(0.5)(b.ld / nullIf(b.vw, 0), d <= 2000), quantileIf(0.5)(b.ld / nullIf(b.vw, 0), b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.ld / nullIf(b.vw, 0), b.beds_b = a.beds_b), quantileIf(0.5)(b.ld / nullIf(b.vw, 0), 1)] AS m_cvr,
    [quantileIf(0.5)(b.ld, d <= 2000 AND b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.ld, d <= 2000 AND b.beds_b = a.beds_b), quantileIf(0.5)(b.ld, d <= 2000), quantileIf(0.5)(b.ld, b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.ld, b.beds_b = a.beds_b), quantileIf(0.5)(b.ld, 1)] AS m_ld,
    [quantileIf(0.5)(b.p, d <= 2000 AND b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.p, d <= 2000 AND b.beds_b = a.beds_b), quantileIf(0.5)(b.p, d <= 2000), quantileIf(0.5)(b.p, b.beds_b = a.beds_b AND b.area_b = a.area_b), quantileIf(0.5)(b.p, b.beds_b = a.beds_b), quantileIf(0.5)(b.p, 1)] AS m_p
  FROM A a JOIN pool b ON b.cy = a.py AND b.cx = a.px AND b.category = a.category AND b.rp = a.rp
  WHERE b.id != a.id AND (greatCircleDistance(a.lon, a.lat, b.lon, b.lat) AS d) <= 5000
  GROUP BY a.id
),
C AS (
  SELECT X.*,
    toUInt8(if(X.category IN (2, 7, 15, 16), 20, 50)) AS min_peers,   -- land and buildings are sparse: 20 peers
    toUInt8(multiIf(PR.n1 >= min_peers, 1, PR.n2 >= min_peers, 2, PR.n3 >= min_peers, 3, PR.n4 >= min_peers, 4, PR.n5 >= min_peers, 5, PR.n6 >= min_peers, 6, 0)) AS lvl,
    [PR.n1, PR.n2, PR.n3, PR.n4, PR.n5, PR.n6] AS ns,
    if(lvl > 0, ns[lvl], 0) AS peers,
    if(lvl > 0, PR.m_im[lvl], NULL) AS peer_med_im,
    if(lvl > 0, PR.m_ctr[lvl], NULL) AS peer_med_ctr,
    if(lvl > 0, PR.m_cvr[lvl], NULL) AS peer_med_cvr,
    if(lvl > 0, PR.m_ld[lvl], NULL) AS peer_med_ld,
    if(lvl > 0, PR.m_p[lvl], NULL) AS peer_med_price
  FROM X LEFT JOIN PR ON PR.id = X.id
)
SELECT
  d0 AS window_start, d1 AS window_end,
  id AS listing_id, user_id, category, city_id, district_id, lat, lon,
  rp AS rent_period, beds_b AS beds, area_b AS area_bin,
  cdate AS created_on, p AS price_yearly_or_meter, n_img AS images, has_video,
  im AS impressions, vw AS views, ld AS leads,
  sov_in_window, golden_in_window,
  lvl AS peer_level, min_peers,
  [0, 2000, 2000, 2000, 5000, 5000, 5000][lvl + 1] AS peer_radius_m,
  peers, peer_med_im, peer_med_ctr, peer_med_cvr, peer_med_ld, peer_med_price,
  round(p / nullIf(peer_med_price, 0), 3) AS price_vs_peers,
  multiIf(lvl = 0, '0 no peer group',
          im < 0.5 * peer_med_im, '1 low visibility',
          vw / nullIf(im, 0) < 0.5 * peer_med_ctr, '2 low CTR',
          peer_med_cvr > 0 AND ld / nullIf(vw, 0) < 0.5 * peer_med_cvr, '3 low conversion',
          '4 healthy') AS funnel_class
FROM C

Part A: class profile from the table

Section titled “Part A: class profile from the table”
SELECT
  dim,
  val,
  cls,
  n,
  tot,
  round(100 * n / tot, 1) AS pct,
  med_price_ratio,
  pct_price_20_above,
  med_imgs,
  pct_video,
  pct_promoted,
  med_age_d,
  pct_zero_leads,
  avg_im,
  avg_vw,
  avg_ld,
  avg_peers,
  med_radius_m
FROM
  (
    SELECT
      dim,
      val,
      cls,
      n,
      sum(n) OVER (
        PARTITION BY
          dim,
          val
      ) AS tot,
      med_price_ratio,
      pct_price_20_above,
      med_imgs,
      pct_video,
      pct_promoted,
      med_age_d,
      pct_zero_leads,
      avg_im,
      avg_vw,
      avg_ld,
      avg_peers,
      med_radius_m
    FROM
      (
        SELECT
          dv.1 AS dim,
          dv.2 AS val,
          funnel_class AS cls,
          count() n,
          round(quantile(0.5) (price_vs_peers), 2) med_price_ratio,
          round(avg(price_vs_peers > 1.2) * 100) pct_price_20_above,
          round(quantile(0.5) (images)) med_imgs,
          round(avg(has_video) * 100) pct_video,
          round(
            avg(
              sov_in_window
              OR golden_in_window
            ) * 100,
            1
          ) pct_promoted,
          round(quantile(0.5) (window_start - created_on)) med_age_d,
          round(avg(leads = 0) * 100) pct_zero_leads,
          round(avg(impressions)) avg_im,
          round(avg(views), 1) avg_vw,
          round(avg(leads), 2) avg_ld,
          round(avg(peers)) avg_peers,
          round(avg(peer_radius_m)) med_radius_m
        FROM
          metabase.listing_proximity_peers p
          LEFT JOIN sadb_categories c FINAL ON c.category_id = p.category
          LEFT JOIN sadb_districts d FINAL ON d.district_id = p.district_id
          ARRAY JOIN [
            ('all', 'all'),
            ('cat', c.name_en),
            ('city', d.city_name_en),
            ('level', toString(peer_level)),
            ('rp', rent_period)
          ] AS dv
        GROUP BY
          dim,
          val,
          cls
      )
  )
WHERE
  tot >= 3000
  OR dim IN ('all', 'level', 'rp')
ORDER BY
  dim,
  tot DESC,
  val,
  cls

Peer threshold coverage

Section titled “Peer threshold coverage”
WITH
  toDate(now(), 'Asia/Riyadh') - 28 AS d0,
  toDate(now(), 'Asia/Riyadh') - 1 AS d1,
  cats AS (
    SELECT
      category_id,
      positionCaseInsensitive(name_en, 'rent') > 0 AS is_rent
    FROM
      sadb_categories FINAL
  ),
  L AS (
    SELECT
      toInt64(l.id) AS id,
      l.category AS category,
      if(
        k.is_rent,
        multiIf(
          l.rent_period = 2,
          'monthly',
          l.rent_period = 1,
          'other',
          'yearly'
        ),
        'sale'
      ) AS rp,
      least(greatest(l.beds, 0), 6) AS beds_b,
      if(l.area > 0, toInt32(floor(log2(l.area))), -1) AS area_b,
      toFloat64OrNull(extractAll(l.location, '-?\\d+\\.\\d+') [2]) AS lat,
      toFloat64OrNull(extractAll(l.location, '-?\\d+\\.\\d+') [1]) AS lon
    FROM
      sadb_listings l FINAL
      LEFT JOIN cats k ON k.category_id = l.category
    WHERE
      l._peerdb_is_deleted = 0
      AND l.status IN (0, 4)
      AND l.published = 1
      AND l.rega_licensed = 1
      AND l.create_time >= 1704067200
      AND toDate(toDateTime(l.create_time)) <= d0
      AND lat BETWEEN 15 AND 33
      AND lon BETWEEN 34 AND 56
  ),
  sov AS (
    SELECT DISTINCT
      toInt64(listing_id) AS id
    FROM
      sadb_sov_daily_biddings FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND non_chargeable = 0
      AND toDate(start_datetime) BETWEEN d0 AND d1
  ),
  golden AS (
    SELECT DISTINCT
      toInt64(listing_id) AS id
    FROM
      sadb_golden_campaigns FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND status IN ('paid', 'done')
      AND toDate(start_date) <= d1
      AND toDate(end_date) >= d0
  ),
  X AS (
    SELECT
      *,
      id IN (
        SELECT
          id
        FROM
          sov
      )
      OR id IN (
        SELECT
          id
        FROM
          golden
      ) AS promo,
      toInt32(floor(lat / 0.06)) AS cy,
      toInt32(floor(lon / 0.06)) AS cx
    FROM
      L
  ),
  pool AS (
    SELECT
      *
    FROM
      X
    WHERE
      NOT promo
  ),
  A AS (
    SELECT
      id,
      category,
      rp,
      beds_b,
      area_b,
      lat,
      lon,
      cy + dy AS py,
      cx + dx AS px
    FROM
      X
      ARRAY JOIN [-1, -1, -1, 0, 0, 0, 1, 1, 1] AS dy,
      [-1, 0, 1, -1, 0, 1, -1, 0, 1] AS dx
  ),
  PR AS (
    SELECT
      a.id AS id,
      countIf (
        d <= 2000
        AND b.beds_b = a.beds_b
        AND b.area_b = a.area_b
      ) AS n1,
      countIf (
        d <= 2000
        AND b.beds_b = a.beds_b
      ) AS n2,
      countIf (d <= 2000) AS n3,
      countIf (
        b.beds_b = a.beds_b
        AND b.area_b = a.area_b
      ) AS n4,
      countIf (b.beds_b = a.beds_b) AS n5,
      countIf (1) AS n6
    FROM
      A a
      JOIN pool b ON b.cy = a.py
      AND b.cx = a.px
      AND b.category = a.category
      AND b.rp = a.rp
    WHERE
      b.id != a.id
      AND (
        greatCircleDistance(a.lon, a.lat, b.lon, b.lat) AS d
      ) <= 5000
    GROUP BY
      a.id
  )
SELECT
  th,
  lvl,
  count() n,
  round(
    100 * n / sum(n) OVER (
      PARTITION BY
        th
    ),
    1
  ) pct,
  round(quantile(0.5) (ns [lvl])) med_peers
FROM
  (
    SELECT
      X.id,
      [PR.n1, PR.n2, PR.n3, PR.n4, PR.n5, PR.n6] AS ns
    FROM
      X
      LEFT JOIN PR ON PR.id = X.id
  )
  ARRAY JOIN [10, 20, 30, 50] AS th
  ARRAY JOIN [toUInt8(arrayFirstIndex(x -> x >= th, ns))] AS lvl
GROUP BY
  th,
  lvl
ORDER BY
  th,
  lvl

Notification and boost events (MySQL, Metabase db 2 “Aqar Production”)

Section titled “Notification and boost events (MySQL, Metabase db 2 “Aqar Production”)”

These tables are not synced to ClickHouse. Events are encoded as listing_id * 1000 + (day_number - 20500) and pasted into the event study as literal arrays. Offsets 203–226 = 2026-09-07 to 09-30.

SELECT
  'notif' k,
  COUNT(*) n,
  JSON_ARRAYAGG (listing_id * 1000 + d) j
FROM
  (
    SELECT
      lnc.listing_id,
      FLOOR(
        MIN(
          UNIX_TIMESTAMP (COALESCE(nc.start_sending_on, lnc.createdAt))
        ) / 86400
      ) - 20500 d
    FROM
      listing_notification_campaigns lnc
      LEFT JOIN notification_campaigns nc ON nc.id = lnc.notification_campaign_id
    WHERE
      lnc.status = 'paid'
      AND lnc.createdAt >= '2026-08-20'
      AND lnc.createdAt < '2026-10-01'
    GROUP BY
      lnc.listing_id
    HAVING
      d BETWEEN 203 AND 226
  ) x
UNION ALL
SELECT
  'boost',
  COUNT(*),
  JSON_ARRAYAGG (listing_id * 1000 + d)
FROM
  (
    SELECT
      listing_id,
      FLOOR(MIN(create_time) / 86400) - 20500 d
    FROM
      boosted_listings
    WHERE
      create_time >= UNIX_TIMESTAMP ('2026-08-20')
      AND create_time < UNIX_TIMESTAMP ('2026-10-01')
    GROUP BY
      listing_id
    HAVING
      d BETWEEN 203 AND 226
  ) y

Promotion event study with proximity peers

Section titled “Promotion event study with proximity peers”

Replace [...] with the arrays from the export above. The bpay CTE matches boosts to their purchase price (query). It was run once per product (('sov'), ('notif', 'boost'), ('placebo'), ~30–55 s each) to stay under the 60-second Metabase proxy timeout.

WITH
toDate('2026-09-07') AS w0, toDate('2026-09-30') AS w1, 7 AS W,
[...] AS notif_arr,
[...] AS boost_arr,
xev AS (
  SELECT toInt64(intDiv(x, 1000)) AS lid, 'notif' AS product, toDate('1970-01-01') + toInt32(20500 + x % 1000) AS E FROM (SELECT arrayJoin(notif_arr) AS x)
  UNION ALL
  SELECT toInt64(intDiv(x, 1000)) AS lid, 'boost' AS product, toDate('1970-01-01') + toInt32(20500 + x % 1000) AS E FROM (SELECT arrayJoin(boost_arr) AS x)
),
sov_days AS (
  SELECT toInt64(listing_id) AS listing_id, groupUniqArray(toDate(start_datetime)) days
  FROM sadb_sov_daily_biddings FINAL
  WHERE _peerdb_is_deleted = 0 AND start_datetime >= '2026-01-01' AND non_chargeable = 0
  GROUP BY listing_id),
gold AS (
  SELECT toInt64(listing_id) AS listing_id, toDate(start_date) s, toDate(end_date) e
  FROM sadb_golden_campaigns FINAL
  WHERE _peerdb_is_deleted = 0 AND status IN ('paid','done') AND end_date >= '2026-01-01'),
gold_r AS (SELECT listing_id, groupArray((s, e)) r FROM gold GROUP BY listing_id),
promoted AS (SELECT listing_id AS pid FROM sov_days UNION DISTINCT SELECT listing_id FROM gold UNION DISTINCT SELECT lid FROM xev),
cats AS (SELECT category_id, positionCaseInsensitive(name_en, 'rent') > 0 AS is_rent FROM sadb_categories FINAL),
base_l AS (
  SELECT toInt64(l.id) AS id, l.category AS category, l.district_id AS district_id, l.city_id AS city_id, toDate(toDateTime(l.create_time)) AS cdate,
    if(l.close_time > 0, toDate(toDateTime(l.close_time)), toDate('2100-01-01')) AS xdate,
    if(k.is_rent, multiIf(l.rent_period = 2, 'monthly', l.rent_period = 1, 'other', 'yearly'), 'sale') AS rp,
    least(greatest(l.beds, 0), 6) AS beds_b,
    if(l.area > 0, toInt32(floor(log2(l.area))), -1) AS area_b,
    toFloat64OrNull(extractAll(l.location, '-?\\d+\\.\\d+')[2]) AS lat,   -- POINT (lon lat)
    toFloat64OrNull(extractAll(l.location, '-?\\d+\\.\\d+')[1]) AS lon,
    toInt32(floor(lat / 0.06)) AS cy, toInt32(floor(lon / 0.06)) AS cx
  FROM sadb_listings l FINAL LEFT JOIN cats k ON k.category_id = l.category
  WHERE l._peerdb_is_deleted = 0 AND l.rega_licensed = 1 AND l.create_time >= 1704067200
    AND (l.close_time = 0 OR l.close_time >= toUnixTimestamp(toDateTime(w0 - W)))   -- live at some point in the study window
    AND lat BETWEEN 15 AND 33 AND lon BETWEEN 34 AND 56),
ev AS (
  SELECT listing_id AS lid, 'sov' AS product,
         arrayMin(arrayFilter(x -> x >= w0 AND x <= w1, days)) AS E,
         length(arrayFilter(x -> x >= E - 28 AND x < E, days)) AS prior_promo
  FROM sov_days WHERE arrayExists(x -> x >= w0 AND x <= w1, days)
  UNION ALL
  SELECT listing_id AS lid, 'golden' AS product, min(s) AS E, 0 AS prior_promo FROM gold WHERE s >= w0 AND s <= w1 GROUP BY listing_id
  UNION ALL
  SELECT lid, product, E, 0 AS prior_promo FROM xev WHERE E >= w0 AND E <= w1
  UNION ALL
  SELECT id AS lid, 'placebo' AS product, w0 + toInt32(cityHash64(id, 7) % 24) AS E, 0 AS prior_promo
  FROM base_l WHERE cityHash64(id) % 25 = 0 AND id NOT IN (SELECT pid FROM promoted) AND cdate <= E - 14 AND xdate >= E + W
),
multi AS (
  SELECT a.lid AS lid, a.product AS product FROM ev a JOIN ev b ON a.lid = b.lid
  WHERE a.product != b.product AND a.product != 'placebo' AND b.product != 'placebo' AND abs(a.E - b.E) < W GROUP BY a.lid, a.product
),
ev1 AS (
  SELECT ev.lid AS lid, ev.product AS product, ev.E AS E, ev.prior_promo AS prior_promo,
    ev.product != 'sov' AND arrayExists(x -> x >= ev.E - W AND x < ev.E + W, sd.days) AS sov_overlap,
    arrayExists(t -> t.1 < ev.E + W AND t.2 > ev.E - W, gr.r) AS gold_overlap
  FROM ev LEFT JOIN sov_days sd ON sd.listing_id = ev.lid LEFT JOIN gold_r gr ON gr.listing_id = ev.lid
  WHERE ev.product != 'golden'   -- golden is not reported; golden campaigns only exclude overlapping events
),
ev2 AS (
  SELECT ev1.lid AS listing_id, ev1.product AS product, ev1.E AS E, l.category AS category, l.district_id AS district_id, l.city_id AS city_id,
    l.cdate AS cdate, l.rp AS rp, l.beds_b AS beds_b, l.area_b AS area_b, l.lat AS lat, l.lon AS lon, l.cy AS cy, l.cx AS cx
  FROM ev1 JOIN base_l l ON l.id = ev1.lid
  WHERE l.cdate <= ev1.E - 14 AND ev1.prior_promo = 0 AND NOT ev1.sov_overlap AND NOT ev1.gold_overlap
    AND (ev1.lid, ev1.product) NOT IN (SELECT lid, product FROM multi)
    AND ev1.product IN ('sov', 'notif', 'boost', 'placebo')
),
-- distance peers: never-promoted listings of the same category and rent period that existed before the window
cand AS (
  SELECT e.product AS product, e.listing_id AS listing_id,
    countIf(d <= 2000 AND b.beds_b = e.beds_b AND b.area_b = e.area_b) AS n1,
    countIf(d <= 2000 AND b.beds_b = e.beds_b) AS n2,
    countIf(d <= 2000) AS n3,
    countIf(b.beds_b = e.beds_b AND b.area_b = e.area_b) AS n4,
    countIf(b.beds_b = e.beds_b) AS n5,
    countIf(1) AS n6,
    groupArraySortedIf(50)((d, b.id), d <= 2000 AND b.beds_b = e.beds_b AND b.area_b = e.area_b) AS t1,
    groupArraySortedIf(50)((d, b.id), d <= 2000 AND b.beds_b = e.beds_b) AS t2,
    groupArraySortedIf(50)((d, b.id), d <= 2000) AS t3,
    groupArraySortedIf(50)((d, b.id), b.beds_b = e.beds_b AND b.area_b = e.area_b) AS t4,
    groupArraySortedIf(50)((d, b.id), b.beds_b = e.beds_b) AS t5,
    groupArraySortedIf(50)((d, b.id), 1) AS t6
  FROM (SELECT *, cy + dy AS py, cx + dx AS px FROM ev2 ARRAY JOIN [-1,-1,-1,0,0,0,1,1,1] AS dy, [-1,0,1,-1,0,1,-1,0,1] AS dx) e
  JOIN base_l b ON b.cy = e.py AND b.cx = e.px AND b.category = e.category AND b.rp = e.rp
  WHERE b.id != e.listing_id AND b.cdate <= e.E - W AND b.xdate >= e.E + W AND b.id NOT IN (SELECT pid FROM promoted)
    AND (greatCircleDistance(e.lon, e.lat, b.lon, b.lat) AS d) <= 5000
  GROUP BY e.product, e.listing_id
),
lvl AS (
  SELECT product, listing_id,
    toUInt8(multiIf(n1 >= 10, 1, n2 >= 10, 2, n3 >= 10, 3, n4 >= 10, 4, n5 >= 10, 5, n6 >= 10, 6, 0)) AS lvl,
    [t1, t2, t3, t4, t5, t6][lvl] AS peers
  FROM cand WHERE lvl > 0
),
pe AS (
  SELECT l.product AS product, l.listing_id AS listing_id, e.E AS E, l.lvl AS lvl, arrayJoin(l.peers).2 AS peer_id
  FROM lvl l JOIN ev2 e ON e.product = l.product AND e.listing_id = l.listing_id
),
need_ids AS (SELECT listing_id AS id FROM ev2 UNION DISTINCT SELECT peer_id FROM pe),
allst AS (
  SELECT toInt64(listing_id) AS id, event_date AS d,
    toInt64(countIf(event_name = 'listing_impression')) AS im, toInt64(countIf(event_name = 'listing_view')) AS vw,
    toInt64(countIf(event_name IN ('listing_call', 'listing_whatsapp', 'listing_msg'))) AS ld
  FROM analytics.events
  WHERE event_date BETWEEN '2026-08-31' AND '2026-10-07' AND listing_id IN (SELECT id FROM need_ids)
    AND event_name IN ('listing_impression', 'listing_view', 'listing_call', 'listing_whatsapp', 'listing_msg')
  GROUP BY id, d
),
sov_spend AS (
  SELECT toInt64(listing_id) AS listing_id, toDate(start_datetime) AS d, sum(budget) / 100 AS sar
  FROM sadb_sov_daily_biddings FINAL
  WHERE _peerdb_is_deleted = 0 AND start_datetime >= '2026-08-31' AND non_chargeable = 0
  GROUP BY listing_id, d
),
bpay AS (   -- boost purchases (iOS in-app): price paid in SAR, listing id parsed from the transaction note
  SELECT toInt64OrNull(extract(tm.value, '#(\\d+)')) AS listing_id, round(toFloat64(t.value), 2) AS v,
    toDate(toTimeZone(t.createdAt, 'Asia/Riyadh')) AS d
  FROM sadb_transactions AS t FINAL
  INNER JOIN sadb_transactions_meta AS tm FINAL ON tm.transaction_id = t.transaction_id
  INNER JOIN sadb_transactions_meta_keys AS mk FINAL ON mk.meta_key_id = tm.meta_key_id
  WHERE t._peerdb_is_deleted = 0 AND tm._peerdb_is_deleted = 0 AND t.transaction_category = 4 AND mk.name = 'note'
    AND t.createdAt >= '2026-08-31'
),
tr AS (
  SELECT e.product AS product, e.listing_id AS listing_id, any(e.category) AS category, any(e.district_id) AS district_id,
    sumIf(s.im, s.d >= e.E - W AND s.d < e.E) AS pre_im, sumIf(s.im, s.d >= e.E AND s.d < e.E + W) AS post_im,
    sumIf(s.vw, s.d >= e.E - W AND s.d < e.E) AS pre_vw, sumIf(s.vw, s.d >= e.E AND s.d < e.E + W) AS post_vw,
    sumIf(s.ld, s.d >= e.E - W AND s.d < e.E) AS pre_ld, sumIf(s.ld, s.d >= e.E AND s.d < e.E + W) AS post_ld,
    countIf(s.vw > 0 AND s.d >= e.E - W AND s.d < e.E) AS act_pre, countIf(s.vw > 0 AND s.d >= e.E AND s.d < e.E + W) AS act_post
  FROM ev2 e JOIN allst s ON s.id = e.listing_id
  GROUP BY e.product, e.listing_id
),
pday AS (   -- peers' daily means over the listings that have a row that day
  SELECT p.product AS product, p.listing_id AS listing_id, any(p.lvl) AS lvl, s.d - p.E AS k,
    count() AS n, avg(s.im) AS m_im, avg(s.vw) AS m_vw, avg(s.ld) AS m_ld
  FROM pe p JOIN allst s ON s.id = p.peer_id
  WHERE s.d >= p.E - W AND s.d < p.E + W
  GROUP BY p.product, p.listing_id, k
),
expd AS (
  SELECT product, listing_id, any(lvl) AS lvl, count() AS days, min(n) AS min_n,
    sumIf(m_im, k < 0) AS e_pre_im, sumIf(m_im, k >= 0) AS e_post_im,
    sumIf(m_vw, k < 0) AS e_pre_vw, sumIf(m_vw, k >= 0) AS e_post_vw,
    sumIf(m_ld, k < 0) AS e_pre_ld, sumIf(m_ld, k >= 0) AS e_post_ld
  FROM pday GROUP BY product, listing_id
  HAVING days = 2 * W AND min_n >= 5
),
j AS (
  SELECT t.product AS product, c.name_en AS cat, dd.city_name_en AS city, x.lvl AS lvl,
    if(t.product = 'sov', sp.sar, 0) AS sov_sar, if(t.product = 'sov', sp.days, 0) AS sov_days_post,
    if(t.product = 'boost', bq.paid, 0) AS boost_sar,
    if(t.product = 'boost', multiIf(bq.first_v = 84.99, '150 views', bq.first_v = 164.99, '250 views', bq.first_v = 299.99, '500 views', 'no purchase found'), 'n/a') AS boost_pkg,
    multiIf(t.pre_im < 0.5 * x.e_pre_im, '1_under', t.pre_im <= 1.5 * x.e_pre_im, '2_typical', '3_strong') AS pre_bucket,
    t.pre_im AS t_pre_im, t.post_im AS t_post_im, t.pre_vw AS t_pre_vw, t.post_vw AS t_post_vw, t.pre_ld AS t_pre_ld, t.post_ld AS t_post_ld,
    t.pre_im * x.e_post_im / nullIf(x.e_pre_im, 0) AS x_post_im,
    t.pre_vw * x.e_post_vw / nullIf(x.e_pre_vw, 0) AS x_post_vw,
    t.pre_ld * x.e_post_ld / nullIf(x.e_pre_ld, 0) AS x_post_ld
  FROM tr t JOIN expd x ON x.product = t.product AND x.listing_id = t.listing_id
  LEFT JOIN (
    SELECT e.listing_id AS listing_id, sum(s.sar) AS sar, count() AS days FROM ev2 e JOIN sov_spend s ON s.listing_id = e.listing_id
    WHERE e.product = 'sov' AND s.d >= e.E AND s.d < e.E + W GROUP BY e.listing_id
  ) sp ON sp.listing_id = t.listing_id
  LEFT JOIN (   -- purchases from the day before the boost record to the end of the post window
    SELECT e.listing_id AS listing_id, sum(b.v) AS paid, argMin(b.v, b.d) AS first_v FROM ev2 e JOIN bpay b ON b.listing_id = e.listing_id
    WHERE e.product = 'boost' AND b.d >= e.E - 1 AND b.d < e.E + W GROUP BY e.listing_id
  ) bq ON bq.listing_id = t.listing_id
  LEFT JOIN sadb_categories c FINAL ON c.category_id = t.category
  LEFT JOIN sadb_districts dd FINAL ON dd.district_id = t.district_id
  WHERE t.act_pre >= 4 AND t.act_post >= 4
    AND x.e_pre_im > 0 AND x.e_pre_vw > 0 AND x.e_pre_ld > 0
)
SELECT product, dv.1 AS dim, dv.2 AS val, count() n,
  round(sum(t_post_im) / sum(x_post_im), 3) r_im, round(sum(t_post_vw) / sum(x_post_vw), 3) r_vw, round(sum(t_post_ld) / sum(x_post_ld), 3) r_ld,
  round(sum(t_pre_im) / count()) a_pre_im, round(sum(t_post_im) / count()) a_post_im,
  round(sum(t_pre_vw) / count(), 1) a_pre_vw, round(sum(t_post_vw) / count(), 1) a_post_vw,
  round(sum(t_pre_ld) / count(), 2) a_pre_ld, round(sum(t_post_ld) / count(), 2) a_post_ld,
  round(avg(coalesce(sov_sar, 0)), 1) a_sov_sar, round(avg(coalesce(sov_days_post, 0)), 2) a_sov_days,
  round(avg(boost_sar), 1) a_boost_sar, countIf(boost_sar > 0) n_boost_paid
FROM j ARRAY JOIN [('all', 'all'), ('bucket', pre_bucket), ('level', toString(lvl)), ('cat', cat), ('city', city), ('cat_city', concat(cat, ' | ', city)), ('package', boost_pkg)] AS dv
GROUP BY product, dim, val
HAVING n >= multiIf(dim = 'cat_city', 30, 20) OR dim IN ('all', 'bucket', 'level', 'package')
ORDER BY product, dim, n DESC