Hourly Views Buckets for the Project sort_score Pacing Function

Date: 2026-07-30 Period: 2026-04-01 to 2026-07-29 (120 days, post-Ramadan baseline) Data Sources: sadb_user_real_estate_project_views, sadb_stats_projects_stats, sadb_real_estate_projects, sadb_districts

Summary

The pacing term in the project sort_score needs to know how much of a day’s project-view demand has already happened at any given moment. This analysis produces that curve: a 24-row hour-of-day bucket table (share of daily views per hour, plus the cumulative curve) derived from 210,934 project view events over 120 days.

Three results drive the implementation:

1. The curve must be anchored to UTC hour 0, not Riyadh midnight. The daily project view counter (sadb_stats_projects_stats) rolls over at UTC midnight = 03:00 KSA — verified empirically. Anchoring the cumulative curve at KSA midnight would introduce a systematic 3-hour phase error.
2. Demand is only ~2.5x peak-to-trough and heavily evening-weighted. The trough is 06:00–07:00 KSA (2.1–2.2% per hour); a broad plateau runs 17:00–01:00 KSA (5.2–5.5% per hour). A flat (1/24 = 4.17%) assumption is wrong by up to −49% / +30% depending on the hour.
3. Ramadan needs its own curve. During Ramadan 1447 (2026-02-18 → 2026-03-19) the shape inverts: the pre-dawn window (02:00–06:00 KSA) carried 31% of daily views vs ~13.5% normally, and 24% of the day was delivered by 06:00 KSA vs 11% on a normal day. A static curve would throttle projects precisely during peak Ramadan demand.

---

Data Source Decision

Only one table carries project views at sub-daily granularity:

Table	Grain	Rows	Hourly?	Coverage
sadb_user_real_estate_project_views	one row per view event	760K	Yes	Logged-in users only
sadb_stats_projects_stats	one row per (project_id, day)	108K	No	All users (this is the counter)

sadb_user_real_estate_project_views is a genuine append-only event stream, not an upsert-per-(user, project) table: 760,086 rows vs 649,939 distinct (user, project) pairs, and zero rows where updatedAt > createdAt. Repeat views create new rows, so createdAt is a real view timestamp and hour-of-day is meaningful. (Had it been an upsert table, createdAt would capture only first-discovery hours and the curve would be biased.)

Coverage caveat: the event stream covers logged-in users only — 8.5% (July) to 29% (January) of the view volume counted in sadb_stats_projects_stats. The shape is therefore assumed to be the same for logged-out traffic. No anonymous hourly source exists anywhere in the warehouse, so this assumption is untestable with current instrumentation; it is the main known risk in the curve.

Day boundary: verified, not assumed

sadb_stats_projects_stats.day_time is a days-since-epoch integer (no time component), so the rollover boundary had to be established from write timestamps:

day_date	First updated_at (UTC)	First write (KSA)	Last updated_at (UTC)
2026-07-23	00:00:51	03:00:51	23:59:50
2026-07-25	00:00:52	03:00:52	23:59:59
2026-07-27	00:05:36	03:05:36	23:59:58
2026-07-29	00:01:27	03:01:27	23:59:59

Every day’s row starts accumulating just after UTC midnight and stops at 23:59:5x UTC. The counter’s day is [00:00 UTC, 24:00 UTC) = [03:00 KSA, 03:00 KSA).

This is convenient: 03:00 KSA sits near the daily trough (2.9% of views), so daily budgets reset when almost nothing is happening. A KSA-midnight boundary would have reset budgets at a peak hour.

---

The Hourly Views Bucket Table

All days, 2026-04-01 → 2026-07-29. share sums to 1.0 across the counter’s day. weight_vs_flat = share × 24 (1.0 = an average hour).

hour_utc	hour_ksa	views	share	weight_vs_flat	cum_share
0	03	6,979	0.03309	0.794	0.03309
1	04	6,207	0.02943	0.706	0.06252
2	05	6,252	0.02964	0.711	0.09216
3	06	4,620	0.02190	0.526	0.11406
4	07	4,499	0.02133	0.512	0.13539
5	08	5,034	0.02387	0.573	0.15926
6	09	5,713	0.02708	0.650	0.18634
7	10	6,407	0.03037	0.729	0.21671
8	11	7,119	0.03375	0.810	0.25046
9	12	8,223	0.03898	0.936	0.28944
10	13	9,989	0.04736	1.137	0.33680
11	14	10,464	0.04961	1.191	0.38641
12	15	10,171	0.04822	1.157	0.43463
13	16	10,429	0.04944	1.187	0.48407
14	17	10,858	0.05148	1.236	0.53555
15	18	10,608	0.05029	1.207	0.58584
16	19	11,404	0.05406	1.297	0.63990
17	20	10,949	0.05191	1.246	0.69181
18	21	11,215	0.05317	1.276	0.74498
19	22	11,518	0.05460	1.310	0.79958
20	23	11,336	0.05374	1.290	0.85332
21	00	11,090	0.05258	1.262	0.90590
22	01	10,930	0.05182	1.244	0.95772
23	02	8,920	0.04229	1.015	1.00000

Total 210,934 views. Smallest bucket has 4,499 events → relative standard error ≈ 1.5%, so no smoothing is required; the observed hour-to-hour wiggle is real signal, not noise.

Shape: demand falls from the 00:00 KSA peak to a 07:00 KSA trough (2.5x drop), climbs through the working day, and plateaus from 17:00 KSA to 01:00 KSA. Half the day’s views (50%) arrive by 17:19 KSA; a flat curve would put the halfway point at 15:00 KSA — i.e. a flat assumption runs ~2.3 hours ahead of reality at the midpoint.

Weekday / weekend variants

Riyadh weekends (Fri–Sat) shift ~1.2pt of volume out of the late-night hours into 13:00–15:00 KSA:

hour_ksa	weekday share	weekend share	delta
00	0.05648	0.04379	−0.0127
13	0.04361	0.05578	+0.0122
14	0.04791	0.05342	+0.0055
06	0.02286	0.01974	−0.0031

Peak-to-trough is 2.6x on weekdays and 2.8x on weekends. The divergence is small enough that a single all-days curve is adequate for v1; add the weekend variant only if pacing accuracy proves to be the binding constraint.

Ramadan variant (required)

Ramadan 1447 = 2026-02-18 → 2026-03-19. Confirmed from the data, not from a calendar: the share of views in 02:00–06:00 KSA jumps from a 12.8–13.6% weekly baseline to 31.2–31.8% for the weeks of Feb 22 through Mar 15, then decays back to 16.4% by the week of Mar 29.

hour_utc	hour_ksa	ramadan share	ramadan cum	normal cum	cum delta
0	03	0.06049	0.06049	0.03309	+0.027
2	05	0.06828	0.18824	0.09216	+0.096
3	06	0.05524	0.24348	0.11406	+0.129
5	08	0.02407	0.30270	0.15926	+0.143
10	13	0.03762	0.44330	0.33680	+0.107
14	17	0.02571	0.56725	0.53555	+0.032
19	22	0.04498	0.77086	0.79958	−0.029
23	02	0.05803	1.00000	1.00000	0.000

At 06:00 KSA in Ramadan, 24% of the day’s demand has already been served; the normal curve says 11%. A pacer running the normal curve would read every project as 2.1x over-delivered at dawn and throttle it, then under-deliver through the afternoon.

---

Using the Bucket in the Pacing Function

w[h]   = share for UTC hour h          (table above)
C[h]   = cum_share through end of UTC hour h,  C[-1] = 0

Ready-to-paste config — index is UTC hour, matching the daily counter’s day boundary:

# Project view demand by hour of the counter's day (index 0 == 00:00 UTC == 03:00 KSA).
# Source: sadb_user_real_estate_project_views, 2026-04-01..2026-07-29, 210,934 views.
HOURLY_VIEW_SHARE = [
    0.03309, 0.02943, 0.02964, 0.02190, 0.02133, 0.02387,  # 03:00-08:59 KSA
    0.02708, 0.03037, 0.03375, 0.03898, 0.04736, 0.04961,  # 09:00-14:59 KSA
    0.04822, 0.04944, 0.05148, 0.05029, 0.05406, 0.05191,  # 15:00-20:59 KSA
    0.05317, 0.05460, 0.05374, 0.05258, 0.05182, 0.04229,  # 21:00-02:59 KSA
]

# Cumulative fraction of the day's demand delivered through the END of each UTC hour.
HOURLY_VIEW_CUM = [
    0.03309, 0.06252, 0.09216, 0.11406, 0.13539, 0.15926,
    0.18634, 0.21671, 0.25046, 0.28944, 0.33680, 0.38641,
    0.43463, 0.48407, 0.53555, 0.58584, 0.63990, 0.69181,
    0.74498, 0.79958, 0.85332, 0.90590, 0.95772, 1.00000,
]

# Ramadan override (Ramadan 1447 = 2026-02-18..2026-03-19), same UTC-hour indexing.
RAMADAN_VIEW_SHARE = [
    0.06049, 0.05947, 0.06828, 0.05524, 0.03515, 0.02407,
    0.02252, 0.02354, 0.02777, 0.02914, 0.03762, 0.03580,
    0.03305, 0.02939, 0.02571, 0.02903, 0.04481, 0.04156,
    0.04323, 0.04498, 0.04966, 0.05914, 0.06231, 0.05803,
]
RAMADAN_VIEW_CUM = [
    0.06049, 0.11996, 0.18824, 0.24348, 0.27863, 0.30270,
    0.32522, 0.34876, 0.37653, 0.40568, 0.44330, 0.47910,
    0.51215, 0.54154, 0.56725, 0.59627, 0.64109, 0.68265,
    0.72588, 0.77086, 0.82052, 0.87966, 0.94197, 1.00000,
]

The share arrays are rounded to 5dp and sum to 1.00001; normalize on load if the pacer is sensitive to that, or drive everything off the _CUM arrays (they end at exactly 1.0).

Expected fraction of the day delivered at time t (interpolate inside the current hour so the multiplier moves smoothly rather than stepping 24 times a day):

def expected_fraction(now_utc):
    h = now_utc.hour
    return C[h - 1] + w[h] * (now_utc.minute * 60 + now_utc.second) / 3600

Pacing multiplier:

PACING_FLOOR_TARGET = 24    # skip pacing below ~1 view/hour of target
SMOOTHING           = 3.0   # pseudo-views; stops early-day divide-by-tiny
ALPHA               = 0.5   # damping: 0 = off, 1 = aggressive
CLAMP               = (0.5, 2.0)

def pacing_multiplier(views_today, daily_target, now_utc):
    if daily_target < PACING_FLOOR_TARGET:
        return 1.0                      # long tail: not enough volume to pace
    expected = daily_target * expected_fraction(now_utc)
    pace_ratio = (views_today + SMOOTHING) / (expected + SMOOTHING)
    return clamp((1.0 / pace_ratio) ** ALPHA, *CLAMP)

sort_score = base_score * pacing_multiplier(...)

pace_ratio > 1 means the project is ahead of schedule → multiplier < 1 (throttle); pace_ratio < 1 means behind → multiplier > 1 (boost).

Notes on the parameters:

• SMOOTHING matters more than it looks. At 03:30 KSA (30 minutes into the counter’s day) the expected fraction is 0.0165, so a project with a 50-view target expects 0.8 views. Without additive smoothing a single early view produces a pace_ratio above 1 and an immediate throttle.
• ALPHA = 0.5 (square-root damping) keeps a 2x over-delivery at a 0.71x multiplier rather than 0.5x. Pacing should nudge ranking, not dominate the quality signals from the scoring function analysis.
• daily_target is not derivable from this analysis. Either the contracted/package exposure target, or an equal-share target (expected total project views today / eligible projects).

Volume floor: pacing applies to a minority of projects

Per-project daily views, July 2026 (594 projects with stats rows):

Metric	Value
Median daily views	2.7
P90 daily views	59.0
Max daily views	1,633
Projects with ≥20 views/day	160 (27%)

The median project gets 2.7 views/day — roughly one view per 9 hours. Hourly pacing on that volume is pure noise, which is what PACING_FLOOR_TARGET guards against. Per-project hourly curves are not viable at all: median project volume over the full 120-day window is 146 views (≈1.2/day), and only 102 of 450 projects clear 720 views (6/day). The global curve is the only statistically sound option — which is fine, because the curve barely varies by segment (below).

Do not pace on impressions yet

Project impression tracking only began in July 2026 (impressions = 0 for every month before, then 1.14M in July). Rollout is partial: median impressions/day per project is 0.4 while median views/day is 2.7 — impressions currently undercount views for most projects. Revisit once median daily impressions exceed daily views; impressions would be the better pacing denominator because they are ~5x higher volume for the projects that do have them (p90 = 268/day).

---

Segmentation Check

Per project guidelines, the curve was tested by city and by calendar segment. The hourly shape does not vary by city — all major markets are UTC+3 with the same trough and evening plateau:

hour_ksa	Riyadh	Jeddah	Dammam	Al Khobar
00	5.33%	5.05%	4.71%	4.96%
06	2.12%	2.57%	2.36%	2.22%
13	4.72%	4.89%	5.10%	5.05%
20	5.14%	5.04%	4.87%	6.00%

Max deviation from the national curve is ~1pt, and the smaller cities’ buckets hold only 200–500 views each (±5% relative SE), so per-city curves would add noise, not accuracy. Use one national curve. The segmentation that does matter is temporal: Ramadan (large) and weekend (small).

Month-over-month stability

Share of daily views in 02:00–06:00 KSA, by month:

Month	pct 02:00–06:00 KSA	Views
2026-01	13.3%	187,204
2026-02	18.3%	263,071
2026-03	28.5%	97,520
2026-04	14.2%	82,192
2026-05	16.7%	53,581
2026-06	16.4%	38,723
2026-07	17.1%	37,795

Feb–Mar are inflated by Ramadan. The remaining months span 13.3%–17.1% — a 3.8pt drift, not the tight stability a single static curve would ideally want. Part of that is a second, smaller Islamic-calendar bump: the weekly series peaks at 18.2% (week of 2026-05-17) and 22.7% (week of 2026-05-24), which lines up with the first ten days of Dhul Hijjah and Eid al-Adha (≈2026-05-26).

Practical implication: refresh monthly, swap in the Ramadan curve by Hijri date, and expect Islamic-calendar sensitivity around the two Eids as well. A trailing-28-day curve recomputed daily would track these shifts automatically and is the better long-term design.

---

Recommendations

Priority	Action	Complexity
1	Ship the 24-row UTC-anchored table as a static config; anchor at UTC hour 0	Low
2	Add PACING_FLOOR_TARGET + additive smoothing before pacing goes live	Low
3	Swap in the Ramadan curve by Hijri date	Low
4	Refresh the curve monthly from the query below	Low
5	Instrument logged-out project views hourly to remove the coverage assumption	Medium
6	Re-evaluate impressions as the pacing denominator once rollout completes	Medium

---

SQL Queries

Canonical bucket table (refresh monthly)

SELECT
  hour_utc,
  (hour_utc + 3) % 24 AS hour_ksa,
  views,
  round(share, 5) AS share,
  round(24 * share, 4) AS weight_vs_flat,
  round(sum(share) OVER (ORDER BY hour_utc), 5) AS cum_share
FROM
  (
    SELECT
      toHour (createdAt) AS hour_utc, -- counter day = UTC day; do NOT convert here
      count() AS views,
      count() / sum(count()) OVER () AS share
    FROM
      sadb_user_real_estate_project_views FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND createdAt >= '2026-04-01 00:00:00'
      AND createdAt < '2026-07-30 00:00:00'
    GROUP BY
      hour_utc
  )
ORDER BY
  hour_utc

Day-boundary verification (re-run if the stats writer changes)

SELECT
  day_date,
  min(updated_at) AS first_write_utc,
  toTimeZone (min(updated_at), 'Asia/Riyadh') AS first_write_ksa,
  max(updated_at) AS last_write_utc
FROM
  sadb_stats_projects_stats FINAL
WHERE
  _peerdb_is_deleted = 0
  AND day_date >= '2026-07-20'
GROUP BY
  day_date
ORDER BY
  day_date

Ramadan detection (drives which curve to load)

SELECT
  toStartOfWeek (d) AS week,
  round(100 * sum(pre_dawn) / sum(total), 1) AS pct_hours_02_06_ksa,
  sum(total) AS views
FROM
  (
    SELECT
      toDate (toTimeZone (createdAt, 'Asia/Riyadh')) AS d,
      countIf (
        toHour (toTimeZone (createdAt, 'Asia/Riyadh')) BETWEEN 2 AND 6
      ) AS pre_dawn,
      count() AS total
    FROM
      sadb_user_real_estate_project_views FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND createdAt >= '2026-01-01'
    GROUP BY
      d
  )
GROUP BY
  week
ORDER BY
  week

Weekday / weekend / Ramadan variants

SELECT
  hour_utc,
  (hour_utc + 3) % 24 AS hour_ksa,
  round(wd, 5) AS weekday_share,
  round(sum(wd) OVER (ORDER BY hour_utc), 5) AS weekday_cum,
  round(we, 5) AS weekend_share,
  round(sum(we) OVER (ORDER BY hour_utc), 5) AS weekend_cum,
  round(ram, 5) AS ramadan_share,
  round(sum(ram) OVER (ORDER BY hour_utc), 5) AS ramadan_cum
FROM
  (
    SELECT
      hour_utc,
      countIf (seg = 'wd') / sum(countIf (seg = 'wd')) OVER () AS wd,
      countIf (seg = 'we') / sum(countIf (seg = 'we')) OVER () AS we,
      countIf (seg = 'ram') / sum(countIf (seg = 'ram')) OVER () AS ram
    FROM
      (
        SELECT
          toHour (createdAt) AS hour_utc,
          multiIf (
            toDate (toTimeZone (createdAt, 'Asia/Riyadh')) BETWEEN '2026-02-18' AND '2026-03-19',
            'ram',
            createdAt < '2026-04-01'
            OR createdAt >= '2026-07-30',
            'skip',
            toDayOfWeek (toTimeZone (createdAt, 'Asia/Riyadh')) IN (5, 6),
            'we',
            'wd'
          ) AS seg
        FROM
          sadb_user_real_estate_project_views FINAL
        WHERE
          _peerdb_is_deleted = 0
          AND createdAt >= '2026-01-01'
      )
    WHERE
      seg != 'skip'
    GROUP BY
      hour_utc
  )
ORDER BY
  hour_utc

Per-project volume (sets the pacing floor)

WITH
  d AS (
    SELECT
      project_id,
      day_date,
      sum(views) AS views,
      sum(impressions) AS impressions
    FROM
      sadb_stats_projects_stats FINAL
    WHERE
      _peerdb_is_deleted = 0
      AND day_date >= '2026-07-01'
      AND day_date < '2026-07-30'
    GROUP BY
      project_id,
      day_date
  ),
  p AS (
    SELECT
      project_id,
      avg(views) AS avg_daily_views,
      avg(impressions) AS avg_daily_impr
    FROM
      d
    GROUP BY
      project_id
  )
SELECT
  count() AS projects,
  round(quantile (0.5) (avg_daily_views), 1) AS median_daily_views,
  round(quantile (0.9) (avg_daily_views), 1) AS p90_daily_views,
  countIf (avg_daily_views >= 20) AS projects_ge20_views_day,
  round(quantile (0.5) (avg_daily_impr), 1) AS median_daily_impr
FROM
  p

Hourly shape by city (segmentation check)

SELECT
  any(d.city_name) AS city,
  toHour (toTimeZone (v.createdAt, 'Asia/Riyadh')) AS hour_ksa,
  count() AS views,
  round(
    100 * count() / sum(count()) OVER (PARTITION BY p.city_id),
    2
  ) AS pct_of_city_day
FROM
  sadb_user_real_estate_project_views v FINAL
  JOIN sadb_real_estate_projects p FINAL ON v.real_estate_project_id = p.id
  LEFT JOIN sadb_districts d FINAL ON p.district_id = d.district_id
WHERE
  v._peerdb_is_deleted = 0
  AND p._peerdb_is_deleted = 0
  AND v.createdAt >= '2026-04-01'
GROUP BY
  p.city_id,
  hour_ksa
ORDER BY
  city,
  hour_ksa