Project Agent Document (Client-Alignment First)

# Project Agent Document (Client-Alignment First)

This document prioritizes aligning goals and expectations, understanding business pain points, identifying gaps, and laying out a practical plan. The near‑term objective is not to present a finished model, but to get the problem and the path right with clear decisions and next steps.

## Executive Summary

We completed the first briefing with UNOPS and received initial workbooks and background materials. The key challenges are cost definition and measurement scope, forecast granularity and horizon, currency and inflation treatment, and data quality/compliance alignment. Before the second meeting, we propose to confirm task definition and success criteria, using Data Dictionary v0 and the Validation Summary to agree on scope and priorities. Within 24 hours after the meeting, we will deliver Task Definition v1, Questions v2, and Roadmap v1.

## 1. Purpose & Usage

- Alignment: Document shared agreements on objectives, scope, and success criteria.
- Inventory: Summarize resources and constraints; surface critical gaps.
- Plan: Stage milestones and an actionable checklist with clear ownership.
- Communication: Maintain questions, decisions, and glossary to reduce churn.

## Glossary (Initial)

- Victim assistance cost: All cost components for survivor support (to be confirmed with UNOPS).
- Direct cost: Costs paid for beneficiary services (medical/rehab/assistive devices).
- Indirect cost: Overheads such as management, logistics, and program ops.
- Forecast granularity: Target level (country/region/program line).
- Time granularity: Frequency (annual/quarterly/monthly).
- Inflation adjustment: Transform nominal into real values (CPI/PPP, base year tbc).
- Success criteria: Acceptable error thresholds and adoption targets.
- Data dictionary: Fields, types, time ranges, missingness, primary/foreign keys.

## 2. Stakeholders & Cadence (R&R)

- UNOPS
  - Sponsor (TBC): Business owner; approves scope and success criteria.
  - Data Owner (TBC): Field semantics, update cadence, and access.
  - Finance/Budget (TBC): Currency, inflation/discounting, and reporting use.
  - IT/Security/Compliance (TBC): Data privacy, sharing, and storage boundaries.
- CMU Team
  - PM: Alignment, milestones, minutes, action tracking.
  - DS: Problem modeling, evaluation framework, interpretability.
  - DE/DA: Data survey, cleaning, dictionaries, reproducible pipelines.
- Cadence
  - Weekly/bi‑weekly meeting + async questions via shared doc/email.
  - Decisions and scope changes recorded and countersigned by Sponsor.

## 3. Success Criteria & Business Value

- Value: Improve planning for a 3–5 year horizon; support budgeting and advocacy.
- Candidate criteria (to confirm):
  - Accuracy: e.g., annual/quarter budget MAPE ≤ X% or absolute error ≤ Y million.
  - Usability: Drivers/interpretability; update cadence and run‑cost acceptable.
  - Adoption: Incorporated in a defined decision process/SOP with owners.
- Non‑goals: No individual‑level causal impact; no commitment to long‑term ops.

## 4. Current Resources & Constraints

- Data: Accident/Victim/Assistance workbooks (XLSX) with sub‑tables.
- Background: all_documents_merged (TXT/MD).
- Environment: Local analysis OK; external data (CPI/FX/incidents) requires source and permission confirmation.
- Compliance: Follow UNOPS rules for data use and sharing.

## Data Status & Validation (v0)

- Key uniqueness
  - accident.Accident ID unique rate ≈ 88.6% (duplicates exist).
  - assistance.Assistance ID unique rate ≈ 99.7% (15 duplicates).
  - victims.Victim ID unique rate ≈ 97.7% (322 duplicates; 7,271 nulls).
  - All GlobalID columns are 100% unique/non‑null (good candidates for stable keys).
- FK hit rates
  - victims→accident: ParentRowID = accident.GlobalID hits ≈ 100%; using RowID hits ≈ 9.5%.
  - assistance→victims (Victim ID): direct ≈ 21.1%; normalized ≈ 21.2%.
  - identification/service → assistance (ParentRowID): ≈ 85.4% / ≈ 99.9%.
- Time consistency
  - Assistance start earlier than accident date: 0 samples (after chain mapping victim→accident).
  - Service start earlier than assistance start: 129 samples (likely back‑filled/retroactive).
- Duplicate consistency
  - Accident ID duplicate groups 1,486 | identical 1,407 | conflicting 79.
  - Assistance ID duplicate groups 15 | identical 2 | conflicting 13.
  - Victim ID duplicate groups 158 | identical 26 | conflicting 132.

## FK Consistency vs Proposal/Data Dictionary (Needs confirmation)

- accident ↔ victims
  - Observed: victims.ParentRowID → accident.GlobalID (≈100% hit).
  - Proposal/Docx: explicitly states victims.parentrowid should reference accident.uniquerowid (RowID).
  - Conclusion: Not consistent; choose one standard (GlobalID vs RowID) and update dictionary.
- assistance ↔ identification/service
  - Observed: identification/service.ParentRowID → assistance.RowID (≈85.4% / ≈99.9%); → GlobalID ≈ 0%.
  - Proposal/Docx: shows ParentRowID on sub‑tables and uniquerowid (RowID) on main; no reference to GlobalID.
  - Conclusion: Consistent with RowID; investigate the <100% cases (orphans/missing ParentRowID).
- assistance ↔ victims (Victim ID)
  - Observed: assistance."Please provide Victim ID (from Victim form):" ↔ victims."Vicitm ID" hits ≈ 21%.
  - Proposal/Docx: indicates the field comes from Victim form but no canonical format rule.
  - Conclusion: Intended path consistent; execution inconsistent. Confirm canonical Victim ID and mapping process.

## Requirements Clarification Questions (Based on Proposal/Data Dictionary)

- Cost definition & scope:
  - Proposal: emphasizes "victim rehabilitation and assistive technology" long-term costs, mentions direct vs indirect cost; no itemized list or measurement standards.
  - Data dictionary: service category enumeration (Emergency care / Rehabilitation / Assistive products / Psychosocial / Inclusive education / Socio‑economic insertion / Transportation / Multi‑purpose in‑kind, etc.), no amount fields.
  - To confirm: Which categories to include? Separate direct/indirect? Cost source (unit prices) and conversion methods?

- Forecast period & granularity:
  - Proposal: 3–5 year forecast period specified; no constraints on time granularity (month/quarter/year) or spatial granularity.
  - Data dictionary: contains `Operational start/end` dates and multi-level administrative areas (Governorate/District/Sub‑district/Community).
  - To confirm: Output by year/quarter/month? By country/province/district/community? Need scenario analysis and rolling update rhythm?

- Currency & inflation:
  - Proposal: notes "Inflation – NB" (needs attention).
  - Data dictionary: no cost amount or currency fields provided.
  - To confirm: Budget standard currency (USD/local currency), inflation index (CPI/PPP/endogenous index), base period and discounting rules.

- External data dependencies:
  - Proposal: accident/injury occurrence can use UNMAS IMS, Fenix Insight and other historical event data; medical costs from ATscale/providers.
  - Data dictionary: does not contain external event or cost data.
  - To confirm: Availability, authorization, and time coverage of external data; alignment and update frequency.

- Keys & uniqueness:
  - Proposal/data dictionary: no uniqueness constraints defined for `Accident ID`/`Assistance ID`.
  - Observed: `Accident ID` has duplicates (1486 duplicate groups, 79 conflicts), `Assistance ID` 15 duplicate groups (13 conflicts).
  - To confirm: Are duplicates business-allowed (revisions/multilingual entry)? Cleaning and master record selection rules?

## 5. Requirements Clarification Questions (Second Meeting Focus)

- Cost definition & scope:
  - Which categories/items does cost include? Are direct cost vs management/indirect costs included?
  - Have accounting standards changed across years? Need comparable standard adjustments?
- Granularity & time:
  - What are business needs for prediction granularity (country/region/project line) and time granularity (year/quarter/month)?
  - What is the "lead time" requirement for 3–5 year forecasts (how early do usable predictions need to be available)?
- Currency & inflation:
  - Should currency be unified to USD? Need inflation adjustment/discounting (CPI/PPP/endogenous index)? What base period?
- Data & quality:
  - What are primary/foreign keys, time coverage, missing/anomaly situations, duplicates and standard differences for each table?
  - Data update frequency and sustainable supply plan? Will historical data be retroactively revised?
- Output & delivery:
  - Expected delivery format (briefings/reports/dashboards/reproducible scripts) and update cycle?
  - Priority of explainability requirements (key drivers, scenario analysis, sensitivity analysis)?
- Constraints & risks:
  - Data compliance and privacy boundaries (PII/sensitive fields)? Sharing and storage requirements?
  - Operations constraints (need delivery to specific platform/intranet)?
- External variables:
  - Any recommended/authorized external data sources (CPI, exchange rates, conflict intensity, etc.)?

(New confirmation points based on current validation)
- Keys & joining:
  - Officially confirm victims→accident using `ParentRowID = accident.GlobalID`? Is `RowID` deprecated or historical reference only?
  - What is the "authoritative Victim ID" for Assistance→Victims? Can official lookup table or generation rules be provided to achieve ≥90% hit rate?
- Uniqueness & duplicates:
  - Are `accident.Accident ID` duplicates allowed (same accident multiple revisions/multilingual entries)? If not allowed, what is cleaning strategy?
  - Are the 15 `assistance.Assistance ID` duplicates business-allowed?
  - For "conflicting" duplicates (see report conflict example tables), which field should be arbitration priority? Need latest by source/time, or manual determination by Data Owner?
  - Accept current "mixed RowID/GlobalID foreign keys" status? Need unified foreign key target column annotation in data dictionary (e.g., consistently use GlobalID) and arrange migration?
- Time standards:
  - Are the 129 cases of "service start earlier than assistance start" retroactive registration/advance services? Need tolerance threshold (e.g., ≤7 days)?
- Geography & coding:
  - Can authoritative mapping table for `adminX` codes and place names be provided? Do latitude/longitude fields need digitization and projection standards?
- Adoption & implementation:
  - Can Victim ID standardization rules be incorporated into data entry guidelines to avoid new ambiguities?

## 6. Gap Assessment (As‑is → To‑be → Actions)

- Data availability
  - As‑is: v1 dictionary and validation done; FK inconsistency (doc vs data), low assistance↔victims hit, duplicated/conflicting IDs.
  - To‑be: Freeze FK standard (GlobalID or RowID), publish conflict lists + resolution rules, define "authoritative ID + lookup" in ETL.
  - Actions: Submit FK consistency sheet and conflict CSVs; table and approve ID cleaning rules and mapping file; implement cleaning script v1.
- Task definition
  - As‑is: Proposal stresses 3–5y, direct vs indirect, and inflation; no amount fields in current workbooks.
  - To‑be: Fix target metric (annual/quarter total cost), currency (USD/local), inflation/discounting, spatial granularity, error thresholds.
  - Actions: Deliver Task Definition v1 + Success Criteria v1 with example report mockups.
- Evaluation framework
  - As‑is: No unified metrics or time split; no weighting convention.
  - To‑be: MAPE/Weighted‑MAPE/MAE or budget‑deviation thresholds; rolling‑origin backtests; regional weighting by budget/volume.
  - Actions: Share evaluation draft with splits/weights diagram; confirm and lock scripts.
- External data
  - As‑is: No cost/currency/inflation amounts in current files; proposal suggests ATscale cost and UNMAS IMS/Fenix incidents.
  - To‑be: Acquire cost tables (unit price/unit/currency/time, source/version), CPI/PPP, FX, incident intensity.
  - Actions: Submit external‑data request list with mapping plan and SLAs.
- Delivery & adoption
  - As‑is: Format and cadence not fixed; proposal expects explainable outputs and sensitivity analysis.
  - To‑be: Define update cadence (monthly/quarterly/annual), report templates (with scenarios), reproducible scripts and hand‑off pack.
  - Actions: Provide one‑pager template; post‑meeting fill charts/numbers per agreed rules.

### Data Sufficiency & Sourcing Suggestions

- Conclusion (v0): Current workbooks alone cannot support cost forecasting without external cost/macros. They describe service types and timing but not amounts.
- Minimal additions:
  - Cost: unit prices by service type, unit definitions, currency, time coverage, provenance/versioning.
  - Macro: CPI/PPP, FX, incident intensity (monthly/annual), population or caseload baselines.
  - Linking: Canonical Victim ID + lookup; unified FK (RowID/GlobalID) per relation.
- Sourcing: ATscale/provider cost tables; CPI/FX from UN/IMF/WB; incidents from UNMAS IMS/Fenix/ACLED. After approval and access, land to `external/` and align via scripts.

## 7. Pre‑Meeting Deliverables

- [ ] Discovery Brief v1 (1–2 pages): problem, scope, candidate success criteria, non‑goals.
- [ ] Data Dictionary v0: table/field overview (types, time ranges, missingness, primary keys).
- [ ] Questions v1: prioritized list (must‑answer vs later).
- [ ] Evaluation draft: metrics + time splits (with diagram).
- [ ] Comms & sign‑off: cadence, async channel, decision log & versioning.

## 8. Meeting Agenda (45–60 min)

1) Goal Alignment (10'): restate problem/value; confirm scope and non‑goals.
2) Data Status (10'): present v0 dictionary; highlight quality and FK issues.
3) Success Criteria (10'): metrics, time splits, acceptable thresholds.
4) External Data & Constraints (10'): sources, compliance, ops limits.
5) Roadmap & Roles (10'): milestones, outputs, ownership, cadence.
6) Decisions & Actions (5'): record conclusions, owners, and due dates.

## Meeting Materials (Simplified, One Page)

- Title: Data Status and Key Alignment Points (v0 Validation Summary)

- Connection Hit Rates (visual):
  - victims→accident (GlobalID): 100% [██████████]
  - victims→accident (RowID): 9.47% [█.........]
  - assistance→victims (normalized Victim ID): 21.24% [██........]
  - identification→assistance: 85.44% [█████████.]
  - service→assistance: 99.91% [██████████]

- Anomalies & Concerns:
  - Assistance ID duplicates: 15; Accident ID duplicates: 2413.
  - Service starts earlier than assistance start: 129 (confirm if allowed/set threshold).
  - Victim ID multiple formats coexist, current hit ≈21%, need "authoritative ID + lookup table".
  - Duplicate consistency: Accident duplicate groups 1486 (conflicts 79); Assistance duplicate groups 15 (conflicts 13); Victim duplicate groups 158 (conflicts 132).

- Recommended Decisions (meeting approval):
  - Confirm primary key path: victims→accident use GlobalID as standard; RowID reference only.
  - Confirm Victim ID standard and lookup table provision path (target hit ≥90%).
  - Define time standard and tolerance rules (service vs assistance start).
  - Define duplicate ID handling and write-back strategy (modify source system or analytical layer deduplication only).

- Attachment: Related Files
  - `validation_report.md` (detailed tables and examples)
  - `data_dictionary_v1.md` (field definitions and validation summary)
  - `id_cleaning_rules.md` + `victim_id_mapping.csv` (ID standards and mapping)

## 9. Roadmap & Milestones (alignment‑oriented)

- Phase 0: Discovery & Alignment (this/next week)
  - Deliverables: Discovery v1, Data Dictionary v0, Evaluation draft, minutes & actions.
- Phase 1: Lock Scope & Baselines (+1 week)
  - Deliverables: Task Definition v1, Success Criteria v1, Data Dictionary v1, baseline methods.
- Phase 2: Features & External Data (+1–2 weeks)
  - Deliverables: external data list and acquisition, join strategies, updated risks/dependencies.
- Phase 3: Validation & Comms (+1–2 weeks)
  - Deliverables: explainable drivers, scenario/sensitivity examples, adoption path.
- Phase 4: Handover & Adoption (final)
  - Deliverables: report & slides, reproducible scripts, maintenance & next‑step plan.

## 10. RAID (Risks, Assumptions, Issues, Dependencies)

- Risks: FK inconsistency, time gaps, external data unavailable, scope creep.
- Assumptions: Workbooks cover key years; CPI/FX available; process supports adoption.
- Dependencies: Data Owner clarifications and access; Sponsor sign‑off on criteria; external data authorization.
- Mitigation: Iterate v0→v1 quickly; visual mocks to accelerate agreement; track changes in decision log.

## 11. Ways of Working & Reproducibility

- Docs in repo with versioning (v0→v1).
- Data: only necessary subsets and de‑identified samples in repo.
- Code: clear script naming; `requirements` when needed; charts reused in report and slides.
- Records: minutes, decision log, glossary continuously updated.

## 12. Appendix: Resource Paths

- Dataset: `Accident-Victim-Assistance Data/`
- Background: `all_documents_merged.txt`, `all_documents_merged.md`
- Data Dictionary: `data_dictionary_v0.md` (initial version for alignment)
- Data Dictionary (Enhanced): `data_dictionary_v1.md` (with field definitions and validation summary)
- Validation Report: `validation_report.md` (key/foreign key/temporal consistency and examples)
- ID Cleaning: `id_cleaning_rules.md` (standards and processes); `victim_id_mapping.csv` (mapping table)

---

Maintainer: Project Team (CMU) | Last Updated: TBD (to be updated after second meeting)