"""README.md for 05_Predictions, with every number read from the output files.""" import pickle, json, pandas as pd from pcommon import * K = pd.read_csv(f'{OUT}/00_KPI_TILES.csv').set_index('tile') FM = pd.read_csv(f'{OUT}/03_forecast/forecast_metrics.csv'); fm = {(r.level, r.metric): r.value for r in FM.itertuples()} VM = pd.read_csv(f'{OUT}/05_van_rep_day/van_metrics.csv') def _num(v): try: return float(v) except (TypeError, ValueError): return v vm = {r.metric: _num(r.value) for r in VM.itertuples()} CM = pd.read_csv(f'{OUT}/06_outlets/churn_model_metrics.csv'); cm = {r.metric: r.value for r in CM.itertuples()} LV = pd.read_csv(f'{OUT}/08_value_rollup/value_levers.csv').set_index('lever_id') WF = pickle.load(open(f'{WORK}/warehouse.pkl', 'rb'))['wf_metrics'] RC = pickle.load(open(f'{WORK}/receivables.pkl', 'rb'))['kpi']; OK = pickle.load(open(f'{WORK}/outlets_kpi.pkl', 'rb'))['kpi'] f = lambda lv, m: float(fm[(lv, m)]) pc = lambda x, d=1: f'{100 * float(x):.{d}f}%' d = lambda t: K.loc[t, 'display'] tot = LV.loc['TOTAL'] FS = pd.read_csv(f'{OUT}/03_forecast/forecast_series.csv', dtype={'series_id': str}) sk = FS[FS.level == 'sku'].selling_status.value_counts(); vs = FS[FS.level == 'van'].selling_status.value_counts() FW = pd.read_csv(f'{OUT}/03_forecast/forecast_next_12_weeks.csv', dtype={'series_id': str}); w1 = FW[(FW.level == 'sku') & (FW.horizon_weeks == 1)] CL = pd.read_csv(f'{OUT}/07_receivables/receivables_collection_list.csv', dtype={'outlet_id': str}); P1 = CL[CL.priority == 'P1'] p1r = P1[P1.collect_via == 'Van rep on route']; p1c = P1[P1.collect_via != 'Van rep on route']; nsm = CL[CL.collect_via == 'Credit control: no purchase since March'] VL = pd.read_csv(f'{OUT}/05_van_rep_day/van_list.csv'); SL = pd.read_parquet(f'{WORK}/sales.parquet'); SL = SL[SL.sellable] fv = set(FS[FS.level == 'van'].series_id); LD = pd.read_csv(f'{OUT}/05_van_rep_day/van_load_suggestion_next_week.csv'); VP = pd.read_csv(f'{OUT}/05_van_rep_day/van_visit_plan_next_week.csv') new_vans = [] for v in VL[(VL.active_now == True) & ~VL.van.isin(fv)].itertuples(): ou = set(SL[SL.van == v.van].outlet_id); prev = SL[SL.outlet_id.isin(ou) & (SL.van != v.van) & (SL.date >= DATA_END - pd.Timedelta(weeks=12))].outlet_id.nunique() new_vans.append(f"{v.van} started on {pd.Timestamp(v.first_day):%d %b %Y} and already serves {len(ou)} outlets; {prev} of them were served by other vans in the 12 weeks before. " f"With one week of history it has a load suggestion ({(LD.van == v.van).sum()} SKUs) but no van forecast{' or visit plan' if not (VP.van == v.van).any() else ''} yet.") md = f"""# Binzagr PoV: predictions for the demo dashboard (05_Predictions) *Version 1, 23 Oct 2026. Riyadh van sales to 20 Oct 2026; Jeddah WMS to 30 Sep 2026. Builds on `02_Cleaned` and `03_Simulated`.* This folder holds the predictions behind the wireframe screens (WF_1), as plain CSV files for the dashboard. There is one folder per screen. **No model is deployed.** Each prediction was made once, offline, and judged on weeks or outlets the model never saw. | Start here | What it is | |---|---| | `00_KPI_TILES.csv` | Every headline tile, by screen. Each tile has the value, the text to display, a unit, a label and a note | | `00_INDEX.csv` | Each file mapped to its screen, widget, grain (what one row is), row count and label | | `00_COLUMNS.csv` | A description of every column in every file | | `scripts/` | The code that produced the files (run order below) | **Conventions** - **Format:** UTF-8 with BOM, so Arabic names open correctly in Excel. Dates are YYYY-MM-DD. Amounts are SAR excluding VAT. - **Weeks:** a week runs Wednesday to Tuesday and is named by its closing Tuesday (`week_end`), so the last full week ends on the data end, 20 Oct 2026. "Next week" means 21–27 Oct 2026. - **IDs are text:** - Outlets: `outlet_id` = `dist_cd-cust_cd`, the same key in every outlet file. - Products: `sku` is the product code. - Vans: `van` is the rep code; rep names are never included. - Warehouse staff: `payroll_number` or `person_id` only; no names. - **Labels** (wireframe legend): - **Actual:** measured in Binzagr data, or a test result measured on unseen data. - **Corrected:** rebuilt from related data, such as the receivables ageing. - **Simulated:** uses researched or assumed parameters, such as wages or margins. - **Forecast:** a model's prediction for weeks that were held out or have not happened yet. --- ## Screen by screen ### 1 Reconciliation (`01_reconciliation`) This screen is not a prediction. It compares each Binzagr file total with the total we loaded: - Sales and returns match to the riyal. - Two differences are explained in the file: 174 extra customers came from the Lipton customer file, and 65,141 WMS rows were exact copies of the first day of the next month. ### 3 Forecast proof (`03_forecast`) - **What:** weekly demand for every SKU (in cases), every van (in SAR) and every principal (in SAR). - The back-test covers 983 SKUs, 114 vans and 19 principals. - The forward forecast runs 12 weeks, to 12 Jan 2027, with a p10–p90 band and a suggested order quantity per SKU. - **Selling status and orders:** each series is marked active, slowing or dormant from the weeks since its last sale, scaled to its own rhythm (the same idea as the outlet rule). - SKUs: {sk.get('active', 0)} active, {sk.get('slowing', 0)} slowing, {sk.get('dormant', 0)} dormant. - Active SKUs get p50 + safety stock. Slowing SKUs get p50 only. Dormant SKUs get no order and go to review (delist, or a promotion to clear?). Without this rule, safety stock built while an item still sold would keep ordering it after it stopped. - Vans: {vs.get('active', 0)} active, {vs.get('slowing', 0)} slowing, {vs.get('dormant', 0)} dormant. Filter on `selling_status` to hide vans that are off the road. - **How:** 10 models raced on every series: - Moving average, SES, ETS and Theta. - Croston, SBA, TSB, ADIDA and IMAPA, for products that sell intermittently. - A global LightGBM model with lag, trend and Hijri-calendar features. - **Choosing the strategy:** each level's strategy was chosen on the validation weeks (1–22 Sep) only: - SKU and van levels use LightGBM. - The principal level uses a 4-week moving average. - Picking a different winning model for each SKU was tested; it was not better than one global model. - Repeat-last-week is the benchmark, not a candidate. - **Test:** the hold-out is 23 Sep–20 Oct 2026 (4 weeks). Each week was forecast from the data up to the week before, and those weeks were never used for fitting or for choosing a model. - **Results:** | Level | Weekly error (WAPE) | Repeat-last-week | |---|---|---| | SKU | {pc(f('sku', 'holdout_wape_model'))} | {pc(f('sku', 'holdout_wape_naive'))} ({pc(f('sku', 'forecast_value_added'), 0)} less error) | | Van | {pc(f('van', 'holdout_wape_model'))} | {pc(f('van', 'holdout_wape_naive'))} | | Principal | {pc(f('principal', 'holdout_wape_model'))} | {pc(f('principal', 'holdout_wape_naive'))} | - The 80% band held {pc(f('sku', 'band_80_coverage_holdout'), 0)} of hold-out weeks. - Safety stock is {abs(f('sku', 'safety_stock_change')):.0%} lower at the same 95% service level. - **Be open about:** - In September (validation), repeat-last-week was slightly better at SKU level ({pc(f('sku', 'validation_wape_naive'))} vs {pc(f('sku', 'validation_wape_model'))}), because demand was flat. The model's advantage shows when demand moves. - At van and principal level the model wins in both periods. - Mid-October had a promotion-like burst, for example Tiffany Break mini bars at about 24,000 cases in one week. No model sees a promotion coming without a promotion calendar. - The p50 is a median, so SKU totals should come from the van or principal forecasts. ### 4 Warehouse labour (`04_warehouse_labour`) - **Cost per activity:** the six activities on deck slide 6, plus pallet picking. Each shows SAR per 100 operations and how far the average is from the median and best-quartile person-month rate. - **Badge-sharing fix:** - {K.loc['Shared-badge shifts', 'value']:.0f} high or medium-risk shifts. - Shifts over 12 hours are split into two people's hours: recorded hours vs corrected hours per person. - Each shift links to its scans by `person_session_id`. - **Heads needed vs on shift:** - Workload is measured in standard hours, from Binzagr's own median standard times. - It is turned into heads at each role's median rate and at the top-quartile rate. - Excess head-hours in Aug–Sep: {d('Excess head-hours')} at median rates. - **Workload forecast for October 2026:** the October numbers can be checked against Binzagr's actual October data. - Method: the median of the last 4 same weekdays per site, chosen on Jun–Aug. - September back-test: {pc(WF['test_wape_sep::median_last4_same_weekday'])} daily error vs {pc(WF['test_wape_sep::last_week'])} for repeat-last-week. - The October forecast is spread over each site's hour profile to give heads needed by hour. - **Role x activity:** hours by WMS menu level and activity. There are no job titles in the data (gap G22), so "forklift / picker / loader" rows are not possible yet. - **Tiles:** - Paid FTE: {d('Paid FTE (head-count base)')}. - Labour cost: {d('Labour cost a year')}. - Gap to median: {d('Gap to median')}. - Gap to top quartile: {d('Gap to top quartile')}. The base case claims 70% of the top-quartile gap, which is SAR 639K. ### 5 Van rep day (`05_van_rep_day`) - **Next-best SKU:** up to 5 SKUs per outlet that it has not bought yet. Each comes with the chance it buys within 8 weeks, its typical spend, the expected value and a plain reason. - **Method:** the score blends what similar outlets buy, what the outlet's own van sells to its other outlets, and overall popularity. The weights were tuned on a 30 Jun back-test. Only SKUs sold somewhere in the last 4 weeks can be picked, so a rep is never sent to sell an item that has stopped flowing; in the 25 Aug back-test this changed no pick. - **Test:** picks made on 25 Aug 2026, judged on the 8 unseen weeks after it (to 20 Oct). {d('Picks bought unprompted (next-best SKU)')} of picks were bought unprompted, against {pc(pd.read_csv(f'{OUT}/05_van_rep_day/next_best_sku_backtest.csv').precision_at_5.iloc[1])} for pushing best-sellers and {pc(pd.read_csv(f'{OUT}/05_van_rep_day/next_best_sku_backtest.csv').precision_at_5.iloc[3], 2)} for random picks. - {d('Outlets that bought a pick')} of outlets bought at least one pick. - **Visit plan for 21–27 Oct:** which outlets each van is likely to visit each day, with at-risk outlets first. - The model scored AUC {float(vm['auc_model']):.2f} vs {float(vm['auc_same_weekday_rule']):.2f} for the same-weekday rule, tested on 14–20 Oct. - Routes are not fixed by weekday. At probability 0.3 or higher the list catches {pc(vm['recall_at_0.3'], 0)} of visits, and {pc(vm['precision_at_0.3'], 0)} of listed outlets are visited. Use it as a guide; `expected_visits_van_day` shows how many visits a day to expect. - **Suggested load:** - p50 and p90 cases per van and SKU for next week, from a LightGBM quantile model across 4,971 van x SKU series. - On the hold-out, a p90 load covered {pc(vm['holdout_share_weeks_covered_by_p90_load'], 0)} of van-SKU weeks and {pc(vm['holdout_share_cases_covered_by_p90_load'], 0)} of cases sold. The promotion burst explains the cases figure. Loading last week's quantity instead covered {pc(vm['holdout_share_cases_covered_by_naive_load'], 0)} of cases. - The load follows the same selling-status rule as the SKU order on screen 3: p90 for active SKUs, p50 for slowing ones, and no load for SKUs that have not sold anywhere for 8+ weeks (`sku_selling_status`, `load_rule`). - This stays Simulated until van load data arrives. - **New van:** {' '.join(new_vans) if new_vans else 'none.'} - **Productivity:** {d('Outlets per van-day')} outlets per van-day (best quartile {vm['best_quartile_outlets_per_van_day_8w']:.0f}). {d('Outlets served by 2+ vans')} of outlets are served by 2 or more vans. ### 6 Outlets (`06_outlets`) - **Churn risk:** the chance an active outlet makes no purchase in the next 8 weeks. - **Model:** LightGBM on recency, frequency, spend trend, purchase rhythm, returns, free goods, and whether the outlet's van is still active. It was chosen over logistic regression on a 30 Jun validation. - **Test:** at 25 Aug, on {int(float(cm['test_outlets'])):,} outlets the model never saw. AUC {float(cm['auc_lightgbm']):.2f} vs {float(cm['auc_recency_rule']):.2f} for the recency rule. The riskiest 20% caught {pc(cm['top20_capture_lightgbm'], 0)} of the outlets that went quiet. - **Be open about:** sorting by days since the last purchase catches about the same share in its top 20% ({pc(cm['top20_capture_recency_rule'], 0)}). The model's edge is in ranking all outlets (AUC) and in giving each one a calibrated probability, not in a sharper top-20% list. Logistic regression scored slightly higher on the test (AUC {float(cm['auc_logistic']):.2f}), but it lost on validation, where the choice was made, so it was not used. - **Final scores:** isotonic-calibrated, as of 20 Oct. - **Lapsed outlets:** {OK['lapsed']} outlets, worth SAR {OK['lapsed_annual_value_sar'] / 1e6:.2f}M a year. Value is their average per year since their first purchase, which is conservative; their run-rate while active was SAR {OK['lapsed_run_rate_value_sar'] / 1e6:.2f}M. The win-back list adds the last van, the nearest active van (straight-line km) and the next-best SKUs. - **Map:** all {OK['master_outlets_with_gps']:,} customers with GPS, coloured by status, risk, spend band or van. ### 7 Receivables (`07_receivables`) - **Ageing:** rebuilt FIFO from the 20 Oct balance snapshot and the van-sales invoices (Corrected). - Credit book: {d('Credit book')}. - Past due: {d('Past due')}, plus SAR {RC['unmatched_sar'] / 1e3:,.0f}K that matches no invoice since March. - DSO: {d('DSO')}. - **Collection list:** P1–P4 priority from rule points and exposure, with plain reasons: - balance above 2x monthly buying while orders slow - no purchase in 45+ days, or none since March, while a balance is open - more than 60 days past due - unmatched balance - high returns - over a real limit - outlet likely to go quiet - **Who collects (`collect_via`):** the van rep when the account's van is still on the road; otherwise credit control. - **Result:** {RC['p1_accounts']} P1 accounts hold SAR {RC['p1_exposure_sar'] / 1e3:,.0f}K of exposure: - {len(p1r)} are on active van routes (SAR {p1r.exposure_sar.sum() / 1e3:,.0f}K). - {len(p1c)} go to credit control (SAR {p1c.exposure_sar.sum() / 1e3:,.0f}K), mostly accounts with no purchase since March. - **Anomaly flag:** {len(nsm)} credit accounts carry SAR {nsm.balance_sar.sum() / 1e3:,.0f}K but have bought nothing since March, so none of it matches an invoice in the data. It may be older debt or another channel's invoices; the customer ledger would settle it. - **Why rules, not a model:** there is no payment history to train one on. ### 2 Impact tree and 8 Value roll-up (`08_value_rollup`) - **Levers:** ten levers, each with its driver (taken from the predictions above), formula, low / base / high SAR a year, one-off cash, labels and its proof screen. - **Total:** SAR {tot.base_sar_yr / 1e6:.2f}M a year (SAR {tot.low_sar_yr / 1e6:.2f}M–{tot.high_sar_yr / 1e6:.2f}M), plus SAR {tot.one_off_cash_base_sar / 1e6:.2f}M one-off cash. - **Not added to the total:** the van-rep productivity view (it overlaps with whitespace and win-back) and the price-spread proxy (it needs trade-spend data). Both are still shown. - **Sliders:** `value_assumptions.csv` holds the slider values (gross margin, conversion uplift, win-back rate, cost of capital, carrying cost, van-day cost and others). `formula` names the assumption ids, so totals can recompute live. - **Biggest assumption:** route productivity (fleet right-sizing) depends most on assumptions (van-day cost, share captured). Present it as a range. --- ## Wireframe placeholders vs these files Use these numbers. The wireframe text came from an earlier analysis. | Screen | Wireframe text | These files | |---|---|---| | 1 | Invoice lines 325,456 · WMS scans 4,495,846 (112,973 duplicates) | {d('Invoice lines')} · {d('WMS scans')} ({K.loc['WMS scans', 'note']}) | | 2 | SAR 2.81M (1.31M–5.30M) + 0.78M one-off | {d('Estimated annual saving (base)')} ({K.loc['Estimated annual saving (base)', 'note'].split(';')[0]}) + {d('One-off cash')} one-off | | 3 | SKU error 63.8% vs 82.2%; 22% less error; van 38.6% vs 47.2%; safety stock −12% | {d('SKU weekly error')} vs {pc(f('sku', 'holdout_wape_naive'))}; {d('Less error (forecast value added)')} less error; van {d('Van weekly error')} vs {pc(f('van', 'holdout_wape_naive'))}; safety stock {abs(f('sku', 'safety_stock_change')):.0%} lower | | 4 | Logged FTE 68 (SAR 3.46M); gap to median 233K (6.7%); top quartile 449K (13%); 485 shared-badge shifts; 14% excess head-hours | {d('Paid FTE (head-count base)')} paid FTE ({d('Labour cost a year')}); {d('Gap to median')}; {d('Gap to top quartile')}; {d('Shared-badge shifts')} shifts; {d('Excess head-hours')} | | 5 | 12.0% vs 4.0% vs 0.66%; 58% of outlets; 6 a day (best quartile 9); 65% on 2+ vans; re-zoning −16% km | {d('Picks bought unprompted (next-best SKU)')} vs {pc(pd.read_csv(f'{OUT}/05_van_rep_day/next_best_sku_backtest.csv').precision_at_5.iloc[1])} vs {pc(pd.read_csv(f'{OUT}/05_van_rep_day/next_best_sku_backtest.csv').precision_at_5.iloc[3], 2)}; {d('Outlets that bought a pick')}; {d('Outlets per van-day')} (best quartile {vm['best_quartile_outlets_per_van_day_8w']:.0f}); {d('Outlets served by 2+ vans')}; re-zoning not produced | | 6 | AUC 0.77 vs 0.68; riskiest 20% = 48%; 597 lapsed worth SAR 926K | AUC {d('Churn model AUC')} vs {float(cm['auc_recency_rule']):.2f}; {d('Riskiest 20% catch')}; {OK['lapsed']} lapsed worth SAR {OK['lapsed_annual_value_sar'] / 1e3:,.0f}K | | 7 | Credit book 3.58M; past due 684K (19%); DSO 22; 77 P1 = SAR 502K | {d('Credit book')}; {d('Past due')} ({pc(RC['past_due_share_of_balance'], 0)}) + SAR {RC['unmatched_sar'] / 1e3:,.0f}K unmatched; {d('DSO')}; {RC['p1_accounts']} P1 = SAR {RC['p1_exposure_sar'] / 1e3:,.0f}K | ## Not produced, and limits - **Foundation models:** Chronos-2, TimesFM 2.5 and TiRex were not run, because their weights could not be downloaded in the build environment. They appear in the model race as "not run" and can be added on a machine that has access. - **Re-zoning (−16% km):** not produced; it needs road distances or route plans. The nearest active van is straight-line only. - **Ramadan / Eid seasonality:** cannot be learned from one Ramadan. The forecasts carry the Hijri events as features. The 12-week horizon to 12 Jan 2027 holds no major Islamic holiday. - **No stock on hand:** the suggested order is an order-up-to quantity, and the van load is a recommendation until van load / unload data arrives. - **Churn means "went quiet":** an outlet with no purchase in 8 weeks. True closures need visit logs or geo-fence flags. - **Warehouse heads:** these count time inside scanned sessions, so the 59% of paid time with no scan is not in them. Attendance by payroll number would close that gap. - **Personal data:** outlet names come from Binzagr's customer master. Reps and warehouse staff appear only as codes or payroll numbers. ## Reproduce From `scripts/`, with `BZ_ROOT` pointing to `Binzagr_Data`, run these in order: `p00_prepare` → `p01_forecast_series` (`sku`, `van` and `principal`) → `p02_forecast_outputs` → `p03_reco` → `p03b_reco_select` → `p03c_reco_tune` → `p03d_reco_final` → `p04_churn` → `p05_van` → `p06_receivables` → `p07_warehouse` → `p08_outlets` → `p09_write` → `p10_docs` → `p11_readme` Requirements: pandas, numpy, scikit-learn, lightgbm, statsforecast, scipy, pyarrow. Seeds are fixed, so a re-run on the same machine gives the same files. Confidential: contains Binzagr data. """ open(f'{OUT}/README.md', 'w', encoding='utf-8').write(md) print(len(md))