Underperforming Listings and Promotion Impact
Which property listings underperform the listings around them, and do the paid promotions SOV, boosts and notification messages lift listing performance? Each product is measured separately. Golden campaigns are left out of the comparison; they are only used to exclude overlapping events.
Scope: property listings (rega_licensed = 1) created 2024+. Leads = calls + WhatsApp + msgs from sadb_stats_listings_stats, whose calendar date is toDate(day_time) - 1. The event-data version repeats this on analytics.events and documents the Metabase table and target list (Q1177).
Peers are defined by distance, not district. Every listing is compared with nearby never-promoted listings of the same category and rent period: within 2 km, widening to 5 km where 2 km has too few. See Proximity peers.
Revised 2026-10-08: replaced category × district peer clusters with 2 km / 5 km proximity peers (≥50 peers, ≥20 for land and buildings), dropped golden from the comparison, reported SOV, boosts and notification messages separately, and added boost prices from sadb_transactions.
Executive Summary
Section titled “Executive Summary”-
Half of the listings with a local peer group underperform it. Of 101,451 active listings with a local peer group (≥50 nearby peers, ≥20 for land and buildings; September, stats data):
- 17.4% have low visibility (<50% of the peer median impressions)
- 20.0% are shown but not clicked (low CTR)
- 10.5% are clicked but not contacted (low conversion)
- 52.0% are healthy
Another 18,633 listings (16%) have too few comparable listings within 5 km. Land (2,897) and villas for sale (2,149) are the largest groups.
-
SOV, boosts and notification messages all lift leads, at very different scales. The 14-day event study below is adjusted for nearby peers on the same calendar days and for a placebo group:
Product Promotions analysed Impressions Views Leads Leads/14d before → after Incremental leads/14d Cost (SAR) SAR per incremental lead SOV 2,984 +45% +65% +45% 6.96 → 9.97 +3.1 124 ~40 Boost 336 +562% +215% +140% 8.48 → 21.50 +12.5 117 ~9 Notification msgs 141 −8% +46% +28% 9.09 → 13.85 +3.0 259 ~86 -
Boost is both the biggest effect and the cheapest lead. Leads rise ×2.4 (+12.5 per listing in 14 days) for an average 117 SAR paid, about 9 SAR per incremental lead. The 84.99 SAR package (150 guaranteed views) is the most common and the most efficient (~6 SAR/lead).
-
SOV costs ~40 SAR per incremental lead. It works in every category, and best for apartments (Apartment for Rent ~25 SAR/lead, Apartment for Sale ~42).
-
Notification messages lift views and leads without search exposure, but they cost twice as much per lead as SOV. Their lead lift is also the least stable estimate: +28% with proximity peers against +52% with district peers.
-
Underperformers gain the most from all three. For listings below half their peer expectation:
- SOV adds +71% leads (4.41 → 7.63 per 14 days) at the same ~38 SAR per lead.
- Boost adds +383% (~6.5 SAR per lead).
- Notification messages add +91%.
For listings that were already strong, notification messages show no lead lift (−6%).
-
Promotions still almost never reach underperformers. In September, 0.1% of low-visibility listings were promoted, against 1.4% of healthy ones.
Data Sources and Product Mapping
Section titled “Data Sources and Product Mapping”| Product | What it does | Where the data lives | Event used |
|---|---|---|---|
| SOV | Daily paid bidding for top search positions | ClickHouse sadb_sov_daily_biddings (budget in points, 100 points = 1 SAR, avg 39 SAR/day) |
First chargeable bidding day, with no SOV in the prior 28 days |
| Boost | Paid view package: lifts the listing until a guaranteed number of views is reached (84.99 SAR = 150 views, 164.99 = 250, 299.99 = 500) | MySQL only: boosted_listings (start/target/current are cumulative views). Price paid: ClickHouse sadb_transactions (transaction_category = 4, listing id parsed from the note meta value) |
create_time |
| Notification msgs | Push to ~470 interested users (~259 SAR) | MySQL only: listing_notification_campaigns (status = 'paid') + notification_campaigns.start_sending_on |
Send start |
| Golden (exclusion only) | 30-day gold highlight in app | ClickHouse sadb_golden_campaigns (status IN ('paid','done')) |
Overlapping events are dropped; golden is not reported |
Boosts are bought separately, not included in subscriptions.
- They are purchased as an iOS in-app purchase. The Android button was removed in February 2024, and the web option in September 2025.
- Admins can also grant a “super boost”, which has no purchase. 10% of the boosts in the study windows have no matching transaction.
- The price comes from
sadb_transactions(transaction_category = 4), whose note readsدفع عن طريق ابل لترقية الاعلان #<listing_id>. Each boost is matched to purchases for the same listing from the day before theboosted_listingsrecord to the end of the post window. Repeat purchases in that window add to the cost.
listing_notification_campaigns and boosted_listings are not synced to ClickHouse (boost purchases are, through sadb_transactions). Their events were pulled from MySQL through Metabase (db 2) and passed into the ClickHouse query as literal arrays.
Proximity Peers
Section titled “Proximity Peers”Peers are never-promoted listings (no SOV or golden in the window) that share the listing’s category and rent period (sale, or for rent categories yearly = rent_period 0/3, monthly = 2, other = 1). Distance is the great-circle distance between location points (POINT (lon lat)). The listing itself is never its own peer.
Each listing uses the first level that has enough peers:
| Level | Radius | Also matches | Share of active listings (28 days to 2026-10-07) |
|---|---|---|---|
| 1 | 2 km | bedrooms (capped at 6) + area bin (floor(log2(area)): 64–127, 128–255, 256–511 m², …) |
33% |
| 2 | 2 km | bedrooms | 17% |
| 3 | 2 km | — | 19% |
| 4 | 5 km | bedrooms + area bin | 4% |
| 5 | 5 km | bedrooms | 5% |
| 6 | 5 km | — | 6% |
| 0 | — | too few peers within 5 km at every level | 15% |
- Part A (classes) needs ≥50 peers, or ≥20 for land and buildings (Land/Building for Sale/Rent, categories 2, 7, 15, 16). This matches Q1177.
- Land and buildings are too sparse for 50: at that threshold 29% of Land for Sale and 46% of Building for Sale had no group. At 20 they are 15% and 21%.
- Overall, 15% of listings have no group (18% if every category needed 50, 7% at 10).
- Land for Rent stays at 83% with no group even at 20.
- Part B (event study) needs ≥10 candidates at a level. It then uses the 50 nearest of them, averaged per calendar day. A promoted listing is compared with what its neighbours did on the same days.
- Nearby pairs are found on a 0.06° grid (±1 cell covers 5 km) before computing the exact distance.
Why distance and not districts. District polygons are uneven: some are a few blocks, others span several kilometres of mixed housing. A fixed radius gives every listing a comparably sized local market. With size matching, the overall results are close to the district version (the SOV lead lift was +44% with district peers and +45% here), so the earlier conclusions hold. Notification messages are the exception (see Notification messages).
Part A — Underperforming Listings Landscape
Section titled “Part A — Underperforming Listings Landscape”Window: 2026-09-01 to 2026-09-28 (28 days), stats data. The universe is 120,084 active listings created before 2026-09-01 with valid coordinates. Each listing is compared with the median of its proximity peers and gets the first funnel stage where it falls below 50% of that median:
- Low visibility: impressions < 0.5 × peer median impressions
- Low CTR: views/impressions < 0.5 × peer median CTR
- Low conversion: leads/views < 0.5 × peer median
- Healthy
Overall profile
Section titled “Overall profile”| 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) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Low visibility | 17,696 | 17.4% | 583 | 48.9 | 1.74 | 62% | 1.01 | 20% | 5 | 25% | 0.1% | 72 |
| Low CTR | 20,300 | 20.0% | 10,108 | 109.6 | 2.80 | 48% | 1.04 | 28% | 8 | 49% | 1.2% | 67 |
| Low conversion | 10,693 | 10.5% | 5,145 | 199.4 | 1.85 | 52% | 1.04 | 30% | 6 | 31% | 1.0% | 65 |
| Healthy | 52,762 | 52.0% | 4,723 | 241.8 | 12.46 | 20% | 0.98 | 19% | 6 | 35% | 1.4% | 61 |
| No peer group | 18,633 | (15.5% of all) | 6,500 | 220.6 | 9.43 | 23% | 4 | 27% | 0.9% | 75 |
- Low visibility
- These listings aren’t overpriced (median 1.01× their nearby peers).
- They are older and have fewer photos and less video.
- This is a ranking/freshness problem, which is what SOV and boosts address. Yet almost none of them are promoted.
- Low CTR
- These listings get twice the peer impressions but aren’t clicked.
- 28% are priced more than 20% above their nearby peers (49% for Apartment for Rent, median 1.20×).
- They also have more media (8 photos, 49% video), so more exposure won’t fix this.
- Low conversion: viewed but not contacted (52% zero leads).
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 | 32,676 | 3.1% | 21.6% | 25.0% | 4.1% | 46.2% | 1.01 (19%) |
| Apartment for Rent | 22,080 | 6.0% | 7.4% | 14.9% | 13.4% | 58.3% | 1.20 (49%) |
| Villa for Sale | 19,022 | 11.3% | 16.6% | 15.7% | 12.0% | 44.4% | 1.04 (29%) |
| Land for Sale | 18,613 | 15.6% | 15.8% | 15.7% | 11.7% | 41.3% | 1.06 (30%) |
| Big Flat for Sale | 7,719 | 12.5% | 25.6% | 19.8% | 5.7% | 36.4% | 1.03 (16%) |
| Building for Sale | 3,303 | 23.0% | 6.8% | 8.7% | 15.2% | 46.3% | 1.30 (56%) |
| Villa for Rent | 3,114 | 27.7% | 3.0% | 9.9% | 11.9% | 47.5% | 1.14 (42%) |
- Sale apartments and big flats are the most underperforming. Over 45% are low visibility or low CTR, and their low CTR isn’t a price problem (1.01–1.03×), which points to photos, titles or listing quality.
- Rentals with low CTR are overpriced. Half of low-CTR Apartment for Rent listings ask more than 20% above nearby same-size units.
- Land and buildings use a 20-peer threshold. Even so, 16% of land and 23% of buildings have no group (31% and 49% at 50 peers). Low-CTR buildings ask 1.30× nearby buildings.
By city
Section titled “By city”| City | Listings | No peer group | Low visibility | Low CTR | Low conversion | Healthy |
|---|---|---|---|---|---|---|
| Riyadh | 55,376 | 7.0% | 14.6% | 17.2% | 11.4% | 49.8% |
| Jeddah | 29,478 | 7.1% | 18.5% | 21.8% | 5.0% | 47.6% |
| Al Khobar | 6,034 | 7.3% | 19.3% | 19.2% | 8.1% | 46.2% |
| Dammam | 5,861 | 10.3% | 16.4% | 17.0% | 12.9% | 43.4% |
Part B — Do Promotions Lift Performance?
Section titled “Part B — Do Promotions Lift Performance?”Method
Section titled “Method”Promotions are not randomly assigned. Brokers buy them for new listings (which decay naturally) or for listings that already do well, so a raw promoted-vs-not comparison mostly measures selection. This analysis uses an event study with difference-in-differences:
- Events. First promotion per listing between 2026-03-01 and 2026-09-15 (boosts: 2026-06-01 onward). Each listing is compared on its own 14 days before vs. the 14 days from the event.
- Expected post-period from the 50 nearest peers.
- For each event, candidate peers are never-promoted listings of the same category and rent period that were live for the whole 28-day window. They must be created at least 14 days before the event and not closed before the window ends.
- The first proximity level with ≥10 candidates is used, keeping its 50 nearest listings. For SOV, 1,786 events used level 1, 558 level 2, 438 level 3 and 202 levels 4–6.
- Expected post = the listing’s own pre-period × (peers’ daily-mean post ÷ peers’ daily-mean pre). At least 5 peers must have data on every day.
- Lift = Σ actual post ÷ Σ expected post − 1, so large listings don’t dominate the way they would in an average of ratios.
- Placebo.
- 1,210 randomly sampled never-promoted listings with random pseudo-event dates go through the identical pipeline.
- Raw placebo ratios are 0.99 (impressions), 0.99 (views) and 0.95 (leads), which confirms the method is calibrated.
- All lifts below are divided by the matching placebo: (1 + treated) / (1 + placebo) − 1. The segment’s own placebo is used when it has ≥40 listings, otherwise the overall one.
- 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 the ±14-day window.
- Impressions on fewer than 10 of the 14 days in either window. This drops listings that were reactivated right before the promotion.
- Cost.
- SOV is the sum of daily budgets (points ÷ 100) on chargeable bidding days within the 14-day post window: 124 SAR over 3.4 days on average. It is an upper bound on actual spend.
- Notification messages use the average paid price (259 SAR).
- Boosts use the purchases matched in
sadb_transactions(avg 117 SAR per analysed boost, counting the 32 with no purchase as 0).
Overall effect (14 days from event, placebo-adjusted)
Section titled “Overall effect (14 days from event, placebo-adjusted)”| Product | n | Impressions | Views | Leads | Impressions/14d before → after | Views/14d before → after | Leads/14d before → after | Incremental leads | SAR per incremental lead |
|---|---|---|---|---|---|---|---|---|---|
| SOV | 2,984 | +45% | +65% | +45% | 5,004 → 7,709 | 172 → 274 | 6.96 → 9.97 | +3.1 | ~40 |
| Boost | 336 | +562% | +215% | +140% | 10,958 → 81,009 | 222 → 681 | 8.48 → 21.50 | +12.5 | ~9 |
| Notification msgs | 141 | −8% | +46% | +28% | 22,877 → 18,945 | 241 → 352 | 9.09 → 13.85 | +3.0 | ~86 |
| Placebo (raw) | 1,210 | −1% | −2% | −5% | 2,274 → 2,316 | 119 → 115 | 4.67 → 4.70 |
The before → after columns are raw averages. Incremental leads = actual post − placebo-adjusted expected post.
SOV lifts every stage of the funnel, even though campaigns are short: only 3.4 active bidding days on average in the 14-day window.
By pre-period performance. “Under” = pre-period impressions below 0.5× the peer expectation.
| Pre-period vs peers | n | Impressions | Views | Leads | Leads/14d before → after | Incremental leads | SAR per incremental lead |
|---|---|---|---|---|---|---|---|
| Under (<0.5×) | 1,012 | +266% | +119% | +71% | 4.41 → 7.63 | +3.2 | ~38 |
| Typical (0.5–1.5×) | 1,091 | +116% | +66% | +43% | 7.38 → 10.30 | +3.1 | ~40 |
| Strong (>1.5×) | 881 | +15% | +43% | +33% | 9.36 → 12.25 | +3.1 | ~42 |
Placebo (raw) by bucket: under +29% impressions / −3% leads, typical −2% / −5%, strong −5% / −7%. The “under” impressions rise is regression to the mean, and dividing by the placebo removes it.
By category (n ≥ 40).
| Category | n | Impressions | Views | Leads | Leads/14d before → after | Incremental leads | SAR per incremental lead |
|---|---|---|---|---|---|---|---|
| Apartment for Rent | 887 | +22% | +62% | +42% | 11.36 → 15.99 | +4.8 | ~25 |
| Villa for Sale | 504 | +71% | +67% | +34% | 4.62 → 6.42 | +1.6 | ~76 |
| Land for Sale | 437 | +63% | +45% | +45% | 3.20 → 4.19 | +1.3 | ~88 |
| Apartment for Sale | 436 | +57% | +96% | +72% | 3.92 → 6.69 | +2.8 | ~42 |
| Big Flat for Sale | 196 | +28% | +109% | +53% | 5.14 → 8.33 | +2.9 | ~52 |
| Building for Sale | 112 | +34% | +34% | +26% | 3.52 → 4.20 | +0.9 | ~128 |
| Office for Rent | 81 | +66% | +50% | +26% | 8.17 → 10.68 | +2.2 | ~97 |
| Villa for Rent | 81 | +41% | +37% | +26% | 10.25 → 13.56 | +2.8 | ~41 |
| Studio for rent | 64 | +44% | +85% | +102% | 12.05 → 22.83 | +11.6 | ~11 |
| Store for Rent | 54 | +72% | +41% | +29% | 10.65 → 13.80 | +3.1 | ~47 |
| Big Flat for Rent | 44 | +32% | +38% | +42% | 11.61 → 15.93 | +4.7 | ~25 |
By city (n ≥ 40).
| City | n | Impressions | Views | Leads | Leads/14d before → after | SAR per incremental lead |
|---|---|---|---|---|---|---|
| Riyadh | 1,785 | +55% | +68% | +47% | 8.07 → 11.84 | ~35 |
| Jeddah | 449 | +75% | +77% | +50% | 5.88 → 8.70 | ~40 |
| Al Khobar | 213 | −36% | +54% | +32% | 6.75 → 8.48 | ~52 |
| Dammam | 206 | +37% | +45% | +61% | 4.60 → 5.64 | ~50 |
| Medina | 75 | −13% | +56% | −8% | 5.04 → 6.47 | n/a |
| Mecca | 69 | +94% | +58% | +44% | 2.74 → 3.78 | ~81 |
By category × city (n ≥ 50).
| Category / city | n | Impressions | Views | Leads | Leads/14d before → after | SAR per incremental lead |
|---|---|---|---|---|---|---|
| Apartment for Rent / Riyadh | 611 | +43% | +67% | +39% | 11.34 → 16.25 | ~28 |
| Villa for Sale / Riyadh | 305 | +67% | +74% | +46% | 5.40 → 7.78 | ~53 |
| Big Flat for Sale / Riyadh | 192 | +27% | +112% | +67% | 5.14 → 8.41 | ~44 |
| Apartment for Sale / Riyadh | 157 | +42% | +79% | +77% | 5.55 → 9.20 | ~32 |
| Land for Sale / Riyadh | 144 | +66% | +46% | +32% | 4.47 → 5.72 | ~82 |
| Apartment for Sale / Jeddah | 122 | +120% | +207% | +200% | 1.80 → 4.22 | ~42 |
| Apartment for Rent / Jeddah | 108 | +64% | +57% | +56% | 13.31 → 18.85 | ~18 |
| Land for Sale / Jeddah | 84 | +75% | +45% | +35% | 3.80 → 5.37 | ~79 |
| Apartment for Rent / Al Khobar | 82 | −49% | +57% | +20% | 9.38 → 12.09 | ~50 |
| Villa for Sale / Jeddah | 59 | +96% | +67% | +73% | 3.36 → 5.42 | ~47 |
| Apartment for Rent / Dammam | 55 | +42% | +32% | +19% | 9.31 → 10.55 | ~40 |
| Apartment for Sale / Dammam | 51 | +181% | +126% | +89% | 3.65 → 6.69 | ~34 |
| Villa for Sale / Dammam | 50 | +76% | +77% | +5% | 2.12 → 2.18 | n/a |
By peer level. Events compared within 2 km (levels 1–3, 2,782 events) show leads +30% to +54%. The 202 events that needed a 5 km ring show +15% to +25%. Wider rings mix in less comparable areas, so those are less precise.
Boost is the largest visibility effect: impressions ×6.6, views ×3.2, leads ×2.4. A boost guarantees a number of views, and the listing is pushed until it gets them.
| Segment | n | Impressions | Views | Leads | Leads/14d before → after | Incremental leads | Avg paid (SAR) | SAR per incremental lead |
|---|---|---|---|---|---|---|---|---|
| All | 336 | +562% | +215% | +140% | 8.48 → 21.50 | +12.5 | 117 | ~9 |
| Under (<0.5× peers) | 82 | +3081% | +496% | +383% | 4.90 → 23.07 | +18.3 | 119 | ~6.5 |
| Typical (0.5–1.5×) | 131 | +2559% | +311% | +160% | 7.09 → 18.59 | +11.5 | 101 | ~9 |
| Strong (>1.5×) | 123 | +285% | +129% | +74% | 12.34 → 23.54 | +10.0 | 133 | ~13 |
| Apartment for Rent | 161 | +629% | +253% | +158% | 11.56 → 32.74 | +20.0 | 112 | ~6 |
| Apartment for Sale | 53 | +159% | +169% | +151% | 3.94 → 10.23 | +6.2 | 119 | ~19 |
| Land for Sale | 33 | +487% | +118% | +125% | 4.61 → 8.42 | +4.7 | 149 | ~32 |
| Villa for Sale | 25 | +730% | +106% | +93% | 6.24 → 11.64 | +5.6 | 140 | ~25 |
| Riyadh | 228 | +619% | +239% | +156% | 9.20 → 25.02 | +15.3 | 115 | ~7.5 |
| Jeddah | 55 | +413% | +162% | +86% | 6.35 → 13.93 | +6.5 | 125 | ~19 |
| Apartment for Rent / Riyadh | 123 | +698% | +286% | +175% | 11.23 → 34.69 | +22.1 | 111 | ~5 |
By package (the first purchase matched to the boost):
| Package | n | Impressions | Views | Leads | Leads/14d before → after | Incremental leads | Avg paid (SAR) | SAR per incremental lead |
|---|---|---|---|---|---|---|---|---|
| 150 views (84.99 SAR) | 223 | +548% | +248% | +176% | 7.97 → 23.05 | +14.7 | 93 | ~6 |
| 250 views (164.99 SAR) | 44 | +526% | +135% | +52% | 9.86 → 16.66 | +5.7 | 165 | ~29 |
| 500 views (299.99 SAR) | 37 | +612% | +192% | +104% | 10.16 → 21.68 | +11.0 | 304 | ~28 |
| No purchase found (admin / unmatched) | 32 | +618% | +145% | +100% | 8.19 → 17.09 | +8.5 | 0 |
- A boost delivers about 4× the incremental leads of an SOV campaign (+12.5 vs +3.1 per 14 days) at a similar outlay (117 vs 124 SAR). It is the cheapest paid lead in the data, at ~9 SAR against ~40 for SOV.
- The smallest package is the most efficient. 150-view boosts deliver views far beyond the guarantee (+500 views per 14 days) and the most incremental leads.
- The larger packages don’t add more leads in 14 days. That could be because their view target is still being worked through after the window ends.
- The 250- and 500-view samples (44 and 37) are small, so treat the comparison as directional.
- Avg paid includes repeat purchases for the same listing within the window (avg paid for the 150-view package is 93, not 84.99).
Notification messages
Section titled “Notification messages”Notification messages push the listing to ~470 interested users. They drive direct traffic (views +46%, leads +28%) without search exposure, and impressions don’t rise (−8%).
| Segment | n | Impressions | Views | Leads | Leads/14d before → after | Incremental leads | SAR per incremental lead |
|---|---|---|---|---|---|---|---|
| All | 141 | −8% | +46% | +28% | 9.09 → 13.85 | +3.0 | ~86 |
| Under (<0.5× peers) | 43 | +604% | +126% | +91% | 7.42 → 14.44 | +6.9 | ~38 |
| Typical (0.5–1.5×) | 58 | +70% | +64% | +39% | 7.10 → 11.12 | +3.1 | ~83 |
| Strong (>1.5×) | 40 | −24% | +10% | −6% | 13.78 → 17.18 | none | n/a |
| Apartment for Rent | 96 | −35% | +43% | +17% | 10.40 → 14.58 | +2.1 | ~123 |
| Riyadh | 103 | −9% | +43% | +20% | 9.17 → 14.03 | +2.3 | ~111 |
- Notification messages pay off only for listings below their local expectation. There they deliver ~6.9 incremental leads at ~38 SAR each, on par with SOV. For typical listings the cost is ~83 SAR per lead, and strong listings gain nothing.
- This is the least stable estimate.
- The sample is small (141 of 723 paid campaigns survive the exclusions, mostly because of SOV overlap and age).
- The lead lift halves between the district-peer study (+52%) and this one (+28%).
- The pre-period impressions of these listings are unusually high and concentrated in a few listings (avg 22.9k/14d vs 2.3k for placebo), so the impressions ratio is dominated by them.
Comparison with the district-peer version
Section titled “Comparison with the district-peer version”| Product | District peers (Sep 30): impressions / views / leads | Proximity peers: impressions / views / leads | SAR per incremental lead (district → proximity) |
|---|---|---|---|
| SOV | +35% / +63% / +44% | +45% / +65% / +45% | 41 → 40 |
| Boost | +574% / +207% / +134% | +562% / +215% / +140% | not measured → ~9 |
| Notification msgs | −69% / +50% / +52% | −8% / +46% / +28% | 55 → 86 |
SOV and boost estimates don’t depend on the peer definition. The notification messages estimate does, and should be confirmed with a holdout test before it drives decisions.
Recommendations
Section titled “Recommendations”- Aim SOV at low-visibility listings.
- They are 17.4% of listings with a peer group, respond with +71% leads at ~38 SAR per incremental lead, and only 0.1% of them are promoted today.
- Q1177 lists them, with a 50/50 test/holdout split.
- Promote boosts, starting with the 150-view package. At ~9 SAR per incremental lead (~6 SAR for the 84.99 package), boosts are the most cost-efficient promotion, and they are only sold on iOS today. Bringing the purchase back to Android and web would extend it to most brokers.
- Sell notification messages to underperforming listings only. For listings already at or above their local expectation, they add few or no leads at ~259 SAR. Confirm the effect with a holdout before scaling.
- For low-CTR listings, don’t buy visibility.
- Rentals: nudge price, since they ask 1.20× nearby same-size units.
- Sale apartments and big flats (not overpriced): prompt for better photos and titles.
- Use ~40 SAR per incremental lead as the SOV benchmark on stats leads (~60 SAR on event-tracked leads, see the event-data finding).
- Sync
listing_notification_campaignsandboosted_listingsto ClickHouse (PeerDB). Two of the three promotion products are currently invisible to warehouse analysis.
Caveats
Section titled “Caveats”- Peer coverage. 16% of listings have too few comparable listings within 5 km (50, or 20 for land and buildings) and get no class. Villas for rent (28%), buildings (23%) and land (16%) are the most affected. Land and building medians rest on 20–40 peers, so they are noisier.
- SOV cost uses the daily budget as the spend, which may overstate the actual charge.
- Boost (336) and notification (141) samples are small. Treat their segment and package cuts as directional.
- Boost costs come from purchases matched by listing and date. 10% of boosts have no purchase (likely admin super boosts) and count as free in the average.
- Listings younger than 14 days at promotion time are excluded, and those are a large share of notification and boost usage. Results describe promoting an established listing.
- Bedrooms and area are broker-entered. Missing or zero area falls into its own bin, and bedrooms are capped at 6. Some
rent_period = 3listings look like monthly prices entered as yearly, soprice_vs_peersfar below 1 is usually an entry error. - The event-study peers must have been live for the whole 28-day window. That excludes peers that closed mid-window, but it also means peers are slightly more established than the average listing.
SQL Queries
Section titled “SQL Queries”Event export from MySQL (Metabase db 2, Aqar Production)
Section titled “Event export from MySQL (Metabase db 2, Aqar Production)”Encoded as listing_id * 1000 + (day_number - 20500) to fit in a ClickHouse array literal.
SELECT 'notif' k, COUNT(*) n, JSON_ARRAYAGG (listing_id * 1000 + d) jFROM ( 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-03-01' AND lnc.createdAt < '2026-09-16' GROUP BY lnc.listing_id ) xUNION ALLSELECT '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-06-01') AND create_time < UNIX_TIMESTAMP ('2026-09-16') GROUP BY listing_id ) yPromotion event study with proximity peers (ClickHouse, Metabase db 8)
Section titled “Promotion event study with proximity peers (ClickHouse, Metabase db 8)”Replace [...] with the arrays from the export above. The bpay CTE matches boosts to their purchase price. The query was run once per product (ev1.product IN ('sov'), ('notif', 'boost'), ('placebo')) to stay under the 60-second Metabase proxy timeout. Each run took 35–55 s.
WITHtoDate('2026-03-01') AS w0, toDate('2026-09-15') AS w1, 14 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) % 199) AS E, 0 AS prior_promo FROM base_l WHERE cityHash64(id) % 60 = 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 windowcand 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(id) AS id, toDate(day_time) - 1 AS d, toInt64(impressions) AS im, toInt64(views) AS vw, toInt64(calls + whatsapp + msgs) AS ld FROM sadb_stats_listings_stats FINAL WHERE day_time BETWEEN toUInt16(toDate('2026-02-10')) AND toUInt16(toDate('2026-09-29')) AND id IN (SELECT id FROM need_ids)),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-02-10' 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-02-10'),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.im > 0 AND s.d >= e.E - W AND s.d < e.E) AS act_pre, countIf(s.im > 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 >= 10 AND t.act_post >= 10 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_paidFROM j ARRAY JOIN [('all', 'all'), ('bucket', pre_bucket), ('level', toString(lvl)), ('cat', cat), ('city', city), ('cat_city', concat(cat, ' | ', city)), ('package', boost_pkg)] AS dvGROUP BY product, dim, valHAVING n >= multiIf(dim = 'cat_city', 30, 20) OR dim IN ('all', 'bucket', 'level', 'package')ORDER BY product, dim, n DESCPart A: underperformance classes with proximity peers (ClickHouse, Metabase db 8, ~45 s)
Section titled “Part A: underperformance classes with proximity peers (ClickHouse, Metabase db 8, ~45 s)”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_mFROM ( 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 ( WITH toDate('2026-09-01') AS d0, toDate('2026-09-28') 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(id) AS id, sum(impressions) AS im, sum(views) AS vw, sum(calls + whatsapp + msgs) AS ld FROM sadb_stats_listings_stats FINAL WHERE day_time BETWEEN toUInt16(d0 + 1) AND toUInt16(d1 + 1) AND id IN ( SELECT id FROM L ) -- stats day_time = calendar date + 1 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, [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 ) 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, clsBoost purchases (ClickHouse)
Section titled “Boost purchases (ClickHouse)”-- Boost purchases for a listing. The listing ID is parsed from the transaction note-- ('دفع عن طريق ابل لترقية الاعلان #<listing_id>'); boost_value is the price paid (SAR).SELECT toInt64OrNull(extract(tm.value, '#(\\d+)')) AS listing_id, toInt64(t.transaction_id) AS transaction_id, toInt64(t.user_id) AS user_id, round(toFloat64(t.value), 2) AS boost_value, multiIf( boost_value = 84.99, 1, boost_value = 164.99, 2, boost_value = 299.99, 3, 0 ) AS boost_package, -- 150 / 250 / 500 views toTimeZone(t.createdAt, 'Asia/Riyadh') AS purchased_atFROM 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_idWHERE t._peerdb_is_deleted = 0 AND tm._peerdb_is_deleted = 0 AND t.transaction_category = 4 AND mk.name = 'note'ORDER BY purchased_at DESC