# Binzagr PoV: real data gaps (04_Gaps)

*Version 3 — 22 Oct 2026. Adds a rough head count from employee IDs and the WMS user list (Mehul's pointer) to version 2, which followed Binzagr's mapping files (transaction mapping, WMS user list, conversion factors, pallet-id sample) and the 21 Oct data walkthrough with Badr. Builds on `02_Cleaned` (cleaning only, no estimates) and `03_Simulated` (research and simulation, 40 sources).*

**What this folder is:** the data that neither cleaning nor research can supply. Every item is Binzagr's own money, people, contracts or code meanings, or data destroyed in the export, so only Binzagr can provide it. Each gap already has a labelled stand-in in `03_Simulated`, so the PoV runs today. When the client fills a template, the answer replaces that stand-in one-for-one.

| File | What it is |
|---|---|
| `GAPS.md` | This document: the gap register, why research can't fill each gap, and what each answer unlocks |
| `Binzagr_Data_Request_Templates.xlsx` | Fill-in workbook for Binzagr: a *Read me* sheet plus 11 sheets. 5 are blank with an example row; 6 are pre-filled from Binzagr's own data, so they only confirm or correct. Attendance keyed by payroll number, rate card on Binzagr's revenue codes, badges sheet narrowed to the logins the user list does not explain; version 3 adds the head-count question (sheet 10) and shows cost per case both ways (sheet 11) |
| `scripts/ingest_client_templates.py` | Validates a filled workbook (and any CSVs sent instead) and writes pipeline-ready `client_*` tables plus `VALIDATION_REPORT.md` |
| `scripts/build_templates.py` | Rebuilds the workbook from `02_Cleaned` and `03_Simulated` |
| `MANIFEST.csv` | SHA-256 checksum of every file |

---

## 1. Why research can't close these

Research covered everything that is the same for any Saudi warehouse: wages, GOSI, expat levy and iqama, Labour Law hours and overtime, public holidays, and 3PL rates and margins (see `03_Simulated/sources.csv`). There are five kinds of data it cannot supply:

1. **Binzagr's own money.** The actual P&L, contract rates and what it pays its people. Binzagr is a private group and publishes no warehouse-level accounts or rate cards. Public figures give the industry shape (for example a 6.5% 3PL EBIT margin), not Binzagr's numbers.
2. **Binzagr's own people and time.** Who was on shift when, and who is behind each badge. Scans show work, not attendance. Pseudonyms are enough; we never need names.
3. **The meaning of internal codes.** Client codes, site ids, customer-hierarchy codes and status flags. We inferred most of them from the data, but only the owner can confirm them.
4. **Data lost in the export.** Excel turned pallet ids into `2.10008E+11` and cut 16–18-digit category codes to 15 digits. The lost digits are not in the files, so no algorithm can recover them. Only a re-export can.
5. **History that wasn't sent.** Under 8 months of WMS and sales data contain a single Ramadan. A yearly Hijri pattern can't be learned from one cycle.

---

## 2. Gap register

**Priority:** 1 = needed for the client-facing P&L comparison · 2 = replaces an inference that already works · 3 = re-export, history or future scope.

| # | Gap | Why not researchable | Stand-in now (`03_Simulated`) | Unknown without it | Owner | Template sheet | P |
|---|---|---|---|---|---|---|---|
| G1 | Actual Jeddah P&L, Feb–Sep 2026 | own money | `sim_jeddah_pnl_monthly`: cost-recovery P&L at a 6.5% margin by construction | The simulated-vs-actual P&L comparison; blue-collar % of revenue | Salman / Finance | 1 Jeddah P&L | 1 |
| G2 | Attendance: clock-in/out per person per shift | people & time | `sim_session_attendance` + `sim_person_month_labour`: one paid shift per **person** session under Labour Law, and payroll people are also paid on working days they don't scan | 58.9% of paid hours fall outside any scan (46.0% counting scan days only); who is really on shift: 202 employee IDs scan in an average month, the user list shows ~269 active people, our base pays ~161 FTE; badge sharing. **Now linkable:** the attendance system uses the same payroll numbers as the WMS user list (Badr, 21 Oct) | Badr / HR | 2 Attendance | 1 |
| G3 | Headcount and all-in labour cost by site, role and month | own money | `sim_labour_cost_model` + `inf_jeddah_headcount`: role tiers (tally clerk / storekeeper SAR ~23.9–24.4 per paid hour; manager ~51); head count from employee IDs (~161 paid FTE, range 115–199) | Every SAR figure scales with it (labour cost P10–P90: SAR 6.84M–11.42M a year; who counts as paid now drives most of that range) | Finance / HR | 3 Labour cost | 1 |
| G4 | Contracted rate per client and activity | own money | `sim_rate_card`: cost-recovery rates on Binzagr's 9 revenue codes (e.g. SAR 34.35 per pallet received, SAR 2.17 per case picked). **Badr will share sample client rates**; he quoted SAR 0.5–0.8 per case for the same activity | Real revenue and margin by client; how much revenue storage and shipped-HU fees carry | Finance / Commercial | 4 Rate card | 1 |
| G5 | Site register: name, temperature, size, status, changes | internal codes | `inf_warehouse_profile`: temperature inferred from item mix; changes from volumes | Fair site-to-site benchmarking (cold vs ambient, relocations) | Badr / Operations | 5 Sites | 1 |
| G6 | Client names, type and billing basis (31 codes); which accounts are in-house | internal codes | `inf_client_principal_map`: 26 Binzagr BUs and 5 external 3PL clients, from item codes | Names; which accounts are in-house (Badr: some accounts are not billed) and which are third party | Badr / Finance | 6 Clients | 2 |
| G7 | What the 149 unexplained logins are | people & time | **Mostly closed:** the user list maps 242 of 387 badges (93.9% of scans) to a payroll number and role. Left: 145 logins not in the list (WA.AD.nnn, plain numbers, HM1008, AMR2100, the 3 system logins …; 6.1% of scans) + 4 flagged badges (BHAS210004 and 3 that scanned while marked inactive) | Whether those logins are people, desktops, devices or agency staff | Badr / HR | 7 Badges | 2 |
| G8 | Invoice-level receivables with payments (wholesale first) | own money | `sim_receivables_*`: FIFO allocation of van-sales balances | True ageing; wholesale receivables (the PoV focus) are not in the data at all | Neeraj / Finance | 8 Receivables | 2 |
| G9 | Official names of the 65 customer-hierarchy codes | internal codes | `inf_customer_channel_map`: channel from name keywords (English and Arabic) | Channel of code 10160 (SAR 18.7M of sales, "mixed / unclear") | Neeraj | 9 Channel codes | 2 |
| G10 | Code interpretations to confirm (now 31, incl. 13 from the 22 Oct files) | internal codes | Assumptions stated in the reports | E.g. code 080 listed twice, code 100 "Picked Volume" vs "Item Import", menu level vs job title, 154 items with pallet < case, ~269 active users vs ~138 who scan | Neeraj / Badr | 10 Code meanings | 2 |
| G11 | Engineered standard times: **the time-and-motion study** Badr mentioned | people & time | Data-derived P50 standards by activity and line size; target = top quartile of Binzagr's own shifts | Absolute (not relative) productivity; standards may embed today's slack | Badr / Raza | 11 Re-exports & history | 2 |
| G12 | WMS re-export: pallet id as text, unique transaction id, client on billing rows, **plus January 2026** | lost in export | Rows kept and flagged; 58 sample rows recovered | The 22 Oct sample **proved** the loss: 12-digit ids were kept to their first 6 digits (the site code), so 2.86M rows cannot be repaired. A re-export joins back row for row | Badr | 11 Re-exports & history | 2 |
| G13 | Product hierarchy codes as text | lost in export | Exact codes rebuilt on the product side | L5 / L6 category names for 10,035 / 18,206 products | Neeraj | 11 Re-exports & history | 2 |
| G14 | Sales and returns history, 24+ months | history | Hijri events as features (`ref_calendar_events`) | Learned Ramadan / Eid seasonality for demand forecasts | Neeraj | 11 Re-exports & history | 2 |
| G15 | WMS history, 24 months | history | 8 months with one Ramadan | Workload seasonality for staffing forecasts | Badr | 11 Re-exports & history | 3 |
| G16 | Storage occupancy per client per day | own operations | Not modelled | Storage revenue in the P&L (Badr's case rates suggest it carries a large share) | Badr | 11 Re-exports & history | 3 |
| G17 | Van loads per rep per day | own operations | Not modelled (sales show only what was sold) | Van-load optimisation | Neeraj | 11 Re-exports & history | 3 |
| G18 | Route / journey plans and visit logs | own operations | Rep profile from sales (median 6 drops a day) | Route optimisation; missed visits | Neeraj | 11 Re-exports & history | 3 |
| G19 | Outlet closure confirmations | own operations | Churn proxy: no purchase for max(60 days, 3× the outlet's usual gap) | Labels to train and validate the churn model | Neeraj | 11 Re-exports & history | 3 |
| G20 | Other clusters' customer and product masters | own operations | Riyadh only | Cross-cluster harmonisation | Neeraj | 11 Re-exports & history | 3 |
| G21 | Shelf-life master | own operations | `SHELF_LIFE` is empty for every product | Needed only if the shelf-life use case returns | Neeraj | 11 Re-exports & history | 3 |
| G22 | HR job titles by payroll number | people & time | WMS menu level (tally clerk / storekeeper / manager) used as the role | Wage rates by job (picker vs forklift driver vs loader); Louis's role-rate question | Badr / HR | 11 Re-exports & history | 2 |
| G23 | Billing volumes per client (codes 110–920: Received HU's, Picked UOS, Shipped HU's …) | own money | Billed units counted from the scan log (Binzagr's 9 revenue codes) | What was actually invoiced, per client and month | Finance / Badr | 11 Re-exports & history | 2 |
| G24 | Facility cost per case and per pallet (current costing) | own money | Simulated labour per loose case picked SAR 0.77 on worked time, 1.02 once paid days without a scan are spread in; per pallet picked 5.70 / 7.57; per pallet received 12.10 / 16.06 (per case shipped, loose + full pallets: 0.38 / 0.50) | Validation of the labour model in Binzagr's own unit | Badr / Finance | 11 Re-exports & history | 1 |

**Outside the PoV, no template:** billing, trade spend and rebates (Salman's #1 priority) and logistics / transport. No data has been received for them. They would need promotion calendars, rebate agreements, invoice-level discounts and claims. Also raised on 21 Oct and not modelled: SLA penalties for missed turnaround times (in contracts, not always applied) and outbound orders waiting in staging for late trucks (not tracked today). Say so explicitly rather than implying coverage.

---

## 3. Priority-1 gaps in detail

### G1: Actual Jeddah P&L
- **Need:** monthly revenue and cost lines, Feb–Sep 2026, by site if possible (template line items: handling, storage, VAS and other revenue; own and agency labour, overtime, MHE, rent, utilities, supervision, IT, consumables, other).
- **Stand-in:** revenue = Actual billable units × simulated cost-recovery rates. The baseline margin is 6.5% *by construction* (DHL Supply Chain FY2025), so only the *change* is a result: SAR 639k a year of recoverable labour (P10–P90 SAR 492k–806k) and simulated EBIT 6.5% → 9.7%.
- **When it arrives:** it becomes the baseline. The optimised P&L = actual − recoverable labour, which is the "simulated vs actual Jeddah P&L" Salman described. Blue-collar % of revenue becomes measurable (it is not today; "25% → 18%" stays illustrative).
- **Minimum acceptable:** branch-level monthly totals of revenue, own labour, agency labour and other opex.

### G2: Attendance / roster
- **Need:** one row per person per shift: clock-in, clock-out, role, own or agency, home site, **keyed by payroll number** — the WMS user list already links 242 badges (94% of scans) to payroll numbers. Badr is not sure how reliable the attendance data is, so two sites × two weeks first (V1).
- **Stand-in:** each **person** work session (a person's badges merged; > 6 h without scans starts a new one) counts as one paid shift: 8 h (6 h in Ramadan), a 30-minute break, overtime up to the 12 h legal maximum, and sessions over 12 h counted as two people (368 sessions). Result: 186,380 paid hours on scan days ≈ 115 FTE over Feb–Sep.
- **Head count (version 3):** the payroll-confirmed people (138 scan in an average month, 193 over the 8 months) are treated as salaried, so they are also paid on working days they don't scan. They scan on only 73% of their expected working days (90% of working days assumed worked after leave and holidays), which adds 7,653 paid days: 245,194 paid hours ≈ 161 FTE. The other ~65 logins a month (not in the user list) are paid only on scan days. Range: 115 FTE (scan days only) to 199 FTE (every login a salaried person). The user list shows ~269 people with an active login at the log sites — far more than scan — so it is not a head count on its own.
- **Why it matters most:** **58.9% of paid hours fall outside any scanned work span** (46.0% counting scan days only, 66.6% if every login is a salaried person; 49.5% at 2100-04, 64–88% at the other sites). Scans can't tell unscanned work from waiting, leave or idle time, so no savings are claimed on it — the SAR 639k comes from worked shifts only and does not change with the head count. Attendance turns this into a measured number. It also confirms or clears the 198 high-risk and 427 medium-risk badge-sharing sessions (239 sessions show two badges of one person active at the same time), and shows who is on shift but never scans.
- **Minimum acceptable:** the shift roster (who was scheduled, which shift), or daily headcount per site and shift.

### G3: Labour cost and headcount
- **Need:** monthly headcount and all-in cost by site, role, own/agency and Saudi/non-Saudi (salary, allowances, GOSI, levy, iqama, insurance, agency fees, overtime).
- **Stand-in:** researched market build-up per WMS role: expat operative SAR 2,859 / 4,101 / 5,358 a month (low / base / high), storekeeper slightly higher (SAR 4,234 base), Saudi SAR 4,703 / 5,946 / 7,223, an assumed 20% Saudi share → SAR 23.86 (tally clerk) and 24.43 (storekeeper) per paid hour at base; warehouse managers SAR 9,600 a month loaded. Head count from employee IDs: ~161 paid FTE in the base (115–199 range), so labour is SAR 9.22M a year (6.95M on scan days + 2.27M on paid days without a scan). The Saudi share and the agency/own split are pure assumptions.
- **When it arrives:** cost per paid hour = actual cost ÷ paid hours (from G2), and every SAR result is recalculated. The Monte Carlo range on labour cost (SAR 6.84M–11.42M a year, median 9.21M) collapses to a single actual figure; most of its width is the head-count question (Spearman 0.92, vs 0.24 for the basic salary). Badr's facility cost per case / per pallet (G24) is the quickest check in the meantime.
- **Minimum acceptable:** monthly total labour cost and headcount per site.

### G4: Rate card
- **Need:** rate per client × service × unit, with validity dates and minimum charges, for both external 3PL clients and internal recharges.
- **Stand-in:** cost-recovery rates on the 9 activities Binzagr marks *Revenue Generation* (receipt SAR 34.35 per pallet, case pick SAR 2.17 per case, pallet pick SAR 16.19, consolidation SAR 2.90 per line …). Circular by design: cost plus a benchmark margin. Badr's example — the same case activity billed SAR 0.8 to one client and 0.5 to another — is far below our cost-recovery rate, so storage and shipped-HU fees must carry a large share of revenue. It is also close to our simulated **labour** per loose case picked: SAR 0.77 on worked time, 1.02 once paid days without a scan are spread in. At SAR 0.50 case picking would not cover its labour, unless our simulated wages or head count are too high. Binzagr's own cost per case (G24) settles which.
- **Why research can't:** contract prices are confidential, and 26 of the 31 warehouse clients are Binzagr's own business units, whose internal recharges no market survey prices.
- **Minimum acceptable:** the sample client rates Badr offered, then rates for the 5 external clients (3069, 3030, 3024, 3017, 3099) and the recharge basis for the business units.

### G5: Site register
- **Need:** name, city, ambient / chilled / frozen, area, pallet positions, operating pattern, status, and what changed when.
- **Stand-in:** 2100-14 and 2100-15 look cold-chain (McCain, dairy). The data shows 2100-12 going silent after April and activity moving from 2100-01 to 2100-07 in August. These are visible but unexplained.
- **When it arrives:** sites are benchmarked within like-for-like peer groups (Salman's alternative to absolute standards), and relocation months are handled correctly.

---

## 4. Already solved without the client (no need to ask)

| Looked like a gap | How it was closed | Where |
|---|---|---|
| Duplicate rows at month boundaries | 65,141 second copies proven at raw-text level and removed | 02 §4.1 |
| Product master rows swallowed by a stray quote | Rebuilt: 23,645 products, matching the sales file's own product list exactly | 02 §5.1 |
| Hierarchy codes truncated by Excel | Exact codes recovered with a key rule that is consistent for 100% of keys | 02 §5.2 |
| Client code missing on billing rows | Linked when the same-second scan set has exactly one client: 93.6% / 96.6% / 88.6% coverage | 02 §4.4 |
| Which clients are external 3PL | Item-code match against the product master: 26 BUs, 5 external | 03 §5.10 |
| Labour Law, statutory costs, market wages, holidays | Researched with low / base / high values (38 published sources) | 03 §3–4 |
| Hijri calendar and 2026 events | Computed (Umm al-Qura) and validated against warehouse closures | 03 §5.14 |
| Standard times | Derived from 4.5M scans, now by activity **and line size** (relative standards); G11 upgrades them | 03 §5.3 |
| Which activities are billed vs cost | Binzagr's transaction mapping (22 Oct): 9 revenue codes in the log; loading, LP audit and QC are cost | 02 §4.8a, 03 §5.7 |
| Who is behind each badge | Binzagr's WMS user list (22 Oct): payroll number and role for 242 badges (94% of scans), month by month | 02 §4.8b |
| A rough head count | Employee IDs and the user list: 202 IDs scan in an average month (138 payroll people + ~65 other logins); ~269 people are listed active; payroll people treated as salaried → ~161 paid FTE (range 115–199). Attendance (G2) and HR headcount (G3) make it exact | 02 §4.8, 03 §5.4b |
| Quantities in cases and pallets | Binzagr's conversion factors (22 Oct): 99.6% of item scans | 02 §4.8d |
| Whether pallet ids can be repaired | Binzagr's pallet-id sample: no — 12-digit ids kept to 6 digits; re-export needed (G12) | 02 §4.8e |
| 01/01/1900 end dates | All are shipment-audit events with no end time recorded; left empty, not set to 2026 | 02 §4.8f |
| HU_ID_2, zero-second tasks | Badr: HU_ID_2 is unused (dropped); zero-second tasks are an operator practice (standards use time between scans) | 02 §4.3, 03 §5.2 |

---

## 5. How the answers flow back in

```
python scripts/ingest_client_templates.py <filled workbook>.xlsx [--csv attendance=<roster>.csv] [--csv 8=<open items>.csv]
```

The script:
- skips the example row and empty rows;
- normalises keys the same way as `02_Cleaned` (codes as text, employee ids upper-case) and types dates and numbers;
- checks required fields, formats, dropdown values, logic (clock-out after clock-in, due date after invoice date, open = invoice − paid) and duplicates;
- checks that every id exists: sites, clients and badges against the pre-filled sheets, customers against `02_Cleaned`;
- writes `client_<name>.csv` (and `.parquet` when pyarrow is installed) plus `VALIDATION_REPORT.md`.

As in the cleaning layer, rows are flagged (`row_problems`, `row_notes`) and never dropped, and each keeps its source row. It was tested on the empty workbook, on a copy filled with deliberately good and bad values plus a CSV, and without pyarrow.

| Sheet | Output | Joins on | Replaces | Change in the model |
|---|---|---|---|---|
| 1 Jeddah P&L | `client_jeddah_pnl_actual` | month (+ wh_id) | baseline of `sim_jeddah_pnl_monthly` | Actual baseline; optimised = actual − recoverable labour |
| 2 Attendance | `client_attendance` | `payroll_number` (+ badge) + date | paid shifts in `sim_session_attendance` | Paid hours = clock-out − clock-in per person; unobserved = paid − scanned span |
| 3 Labour cost | `client_labour_cost_actual` | month + wh_id + role | `sim_labour_cost_model` | Cost per paid hour = actual cost ÷ paid hours |
| 4 Rate card | `client_rate_card_actual` | `client_code` + service | `sim_rate_card` | Revenue = Actual billable units × contract rate |
| 5 Sites | `client_sites` | `wh_id` | `inf_warehouse_profile` | Peer groups for benchmarking; relocation dates |
| 6 Clients | `client_clients` | `client_code` | `inf_client_principal_map` | Names; third-party vs internal revenue |
| 7 Badges | `client_badges` | `employee_id_norm` | `person_id` for the unexplained logins, `inf_badge_session_risk` | Person per login; shared, device and system logins handled; aliases merged |
| 8 Receivables | `client_receivables_actual` | `dist_cd` + `cust_cd` + `doc_no` | `sim_receivables_open_items` / `_customer_ageing` | True ageing; payment-behaviour features for the risk model |
| 9 Channel codes | `client_channel_codes` | level + `code` | `inf_customer_channel_map` | Official channel labels |
| 10 Code meanings | `client_code_meanings` | code / field | assumptions in the reports | Confirmed interpretations |
| 11 Re-exports & history | `client_reexport_status` | dataset | (tracking) | Status and dates of the larger extracts |

Replacement happens at table level: the simulation reads `02_Cleaned` and the parameter table today. Pointing a step at its `client_*` table is a small per-table change along the join keys above, not a redesign.

---

## 6. Questions for the next standup

**Salman**
1. Can Finance share the Jeddah P&L by month for Feb–Sep, at least at branch level?
2. How are the 26 business-unit clients charged (internal recharge basis), and which accounts are in-house (not billed)? Who are the 5 external clients (3069, 3030, 3024, 3017, 3099)?

**Badr**

3. Can you export attendance (clock-in / out) by payroll number for two sites × two weeks, so we can check it against the scans?
4. What are the logins that are not in the user list (WA.AD.nnn, plain numbers like 751, HM1008, AMR2100, NB99)? And what is BHAS210004 used for (a storekeeper login posting ~2,140 confirmations a day in batches)?
5. The user list shows ~269 people with an active login at the Jeddah sites each month, but only ~138 payroll people scan in a month, on 73% of their working days. Who are the others — office staff, drivers, people on leave, or accounts never closed? Is there a monthly headcount per site?
6. In your mapping, is code 080 *In House* or *Duplicate*, and is code 100 "Picked Volume" or "Item Import"? Where do codes 110–920 (Received HU's, Picked UOS, Shipped HU's …) come from — a billing table?
7. Can we have the sample client rates, your current cost per case / per pallet by site, and the time-and-motion study results?
8. Can the WMS log be re-exported with the pallet id as text (as in your sample) and a transaction id, including January 2026?
9. What happened to 2100-12 after April, and to 2100-01 → 2100-07 in August? Which sites are chilled or frozen?

**Neeraj**

10. Can we get the wholesale receivables ageing (and invoice-level open items)? What are CUST_HIER codes 10160 and 10161, and DIST_CD 2000?

**Finance / HR, via Salman**

11. Headcount and all-in labour cost by site and job title (payroll number → job title), own vs agency, and the Saudi share.

---

## 7. For the deck
- The gap is no longer "warehouse data", which arrived on 20 Oct. It is now the **P&L, the rate card and labour cost / roster** (the gaps section).
- Present simulated results with their label. The five priority-1 items turn the simulated P&L into Binzagr's actual one.
- The single strongest ask is attendance: ~59% of paid time is invisible to the WMS (~161 paid FTE, while the scans account for ~115) — and it can now be linked by payroll number.