WF-6.4 — Monthly Driver Scorecard Data Acquisition Plan

Value Driver: VD6 Financial Operations (data infrastructure) · VD4 Service Quality (consumer) Version: 1.0 Date: 2026-04-29 Owner: Roger Thompson Stakeholders: BCs (capture), Aaliyah (HR/Fleet capture), Jana (warehouse-side reconciliation), Roger (review)

Purpose

Define exactly how each of the 9 Monthly Driver Scorecard metrics is gathered, parsed, and landed in the Cedarfell data warehouse — and the four-phase build sequence that takes us from the manual May 2026 cycle to a SQL-backed, dashboard-ready system by Q4 2026.

This plan is the first slice of the broader Schema v1.2 warehouse build. The scorecard is the use case forcing the work; the patterns established here (source files → folder convention → parser script → table → gold view) extend directly to the rest of the warehouse later.

Source documents this plan implements:

• WF-4.4 Monthly Driver Scorecard SOP — the consumer
• WF-6.7 Warehouse Engine Decision — DuckDB + MotherDuck on S3 (replaces SQLite reference)
• KPI Taxonomy v1.0 — metric definitions
• Database-Schema-v1.md (v1.3) — target tables and gold view
• WF-6.3 Data Collection Process — weekly capture cadence
• Decision Catalogue VD4 W1, M1, M4 — the operating decisions

1. Metric → Source Map

The full picture for the 9 scorecard metrics. Each metric has a source system, a current-state capture, a target schema location, a transformation rule, and a phase in which it goes live.

Readiness summary: 4 metrics flow from already-captured weekly data (just need monthly aggregation). 3 metrics ride on a single source we don’t yet capture systematically (RYDE portal). 2 metrics require new infrastructure (VEDR table, attendance log).

2. Three Capture Buckets

Grouping the 9 metrics by what work the capture process actually requires — because the work is shared within each bucket.

Bucket A — Already captured, just need monthly aggregation (4 metrics)

Metrics: ILS%, DNA/1000, SPH, Completion%

Sources: WSW (XLS, weekly from MBA) + GroundCloud Weekly Driver Productivity (CSV).

Current state: Weekly capture per WF-6.3 is operating. Files land in /VD6 - Financial Operations/Data/Source-Files/ (or wherever WF-6.3 specifies — see SOP for current folder convention).

What’s missing: A monthly aggregation step that rolls 4–5 weekly files into per-driver per-month metrics.

Phase 0 implementation (May 2026 cycle): BC manually opens each weekly file, sums per driver, computes the 4 formulas, pastes into Monthly-Driver-Scorecard-v1.0.xlsx Driver-Data sheet. ~60 minutes for 11 drivers. Brandon’s quantified ~2 hrs/wk savings is realized when this becomes automated in Phase 2.

Phase 2 implementation: SQL view monthly_driver_scorecard runs the SUM/AVG aggregations directly from wsw_daily_detail and driver_productivity_weekly.

Owner: BC on duty (manual aggregation in Phases 0–1); database parser (Phase 2+).

Bucket B — One source, three metrics, capture design needed (3 metrics)

Metrics: RYDE score, 5-star reviews, 1-star reviews

Source: FedEx RYDE portal.

Current state: Brandon scans RYDE in real time during the day (per VD4 D1, D5 in the Decision Catalogue). No structured monthly export exists. Schema Gap #8 — export format unknown.

What’s missing:

1. Confirm RYDE portal export options (CSV? Per-driver detail? Per-review detail with star rating?).
2. Define a monthly capture SOP — last business day of the month, BC pulls the export.
3. Standardize the file format and storage path: /VD4 - Service Quality/Data/RYDE-Monthly/YYYY-MM/RYDE-export-YYYY-MM.{csv|xlsx}.
4. Schema extension: ryde_scores already has ryde_score, review_count, positive_count, negative_count — needs count_5_star, count_4_star, count_3_star, count_2_star, count_1_star for explicit star-level detail. Brandon’s data-quality caveat (accidental bad reviews can’t be removed) goes into a notes field.

Phase 0 implementation: BC opens the RYDE portal, manually records each driver’s monthly score + 5-star count + 1-star count into the workbook. ~30 minutes for 11 drivers. Crude but unblocks the first cycle.

Phase 1 implementation: Capture SOP drafted (proposed: WF-6.5 RYDE Monthly Capture). Standard export saved to a known folder. Still entered to the workbook by hand.

Phase 2 implementation: Parser reads the saved export → loads into extended ryde_scores table → gold view aggregates per driver per month.

Open question for Roger: is the RYDE portal CSV exportable, or is it screen-only? This decision sets whether Phase 1 is a real capture SOP or a manual transcription.

Owner: BC on duty (capture); BC + Roger (Phase 1 SOP design).

Bucket C — New infrastructure required (2 metrics)

Metrics: VEDR events, Attendance call-outs

Sources: GroundCloud VEDR module (for VEDR); WF-5.2 attendance log (does not yet exist).

C.1 — VEDR events

Current state: BCs filter false positives in real time (per VD4 D4, structural observation in the Decision Catalogue). No persistent log of “confirmed events.” Vincent watches in-cab video; Brandon doesn’t (D4 ask-back pending).

What’s needed:

1. Capture SOP. BC reviews VEDR module daily during the en-route phase, marks each alert as confirmed=TRUE or confirmed=FALSE (false positive). One row per alert in a daily log file.
2. Schema: new vedr_events table (see §3).
3. Folder convention: /VD3 - Fleet Readiness/Data/VEDR-Daily/YYYY-MM/VEDR-events-YYYY-MM-DD.csv OR a single rolling /VD4 - Service Quality/Data/VEDR-Events-Log.xlsx (Phase 0 is xlsx; Phase 2 is the table).
4. Decision still pending (per VD4 D4 ask-back): which BCs monitor VEDR. The capture SOP cannot finalize ownership until that ask-back returns.

Phase 0 implementation: BC manually counts confirmed VEDR events per driver from May 1 to May 31 by scrolling the GroundCloud VEDR module timeline. Enter integer in workbook.

Phase 1 implementation: Build VEDR-Events-Log.xlsx for daily entry. SOP drafted (proposed: WF-4.5 VEDR Event Logging — pairs with WF-4.4 scorecard).

Phase 2 implementation: Parser reads the daily log → vedr_events table → gold view counts per driver per month.

Owner: BC on duty per ask-back resolution.

C.2 — Attendance call-outs

Current state: No log exists. WF-5.2 Call-Out Response Protocol references the response but not the log. Brandon counts patterns by memory (per VD4 M2 in the Decision Catalogue).

What’s needed:

1. Schema: new attendance_log table (see §3).
2. Capture artifact: /VD5 - People Management/Data/Attendance-Log.xlsx — one row per attendance event (call-out, no-show, late, early-out, scheduled-off).
3. Capture SOP — proposed addition to WF-5.2 §X (or standalone WF-5.4 Attendance Logging). Owner to be decided: BC on duty (most likely) or Aaliyah.
4. Backfill option: Slack message history (per VD4 M3 — “Slack is the de facto coaching log”) may be searchable for prior call-outs. Backfilling to 2026-01-01 would give the scorecard a baseline.

Phase 0 implementation: BC counts call-outs from Slack message history for the month, enters integer in workbook.

Phase 1 implementation: Build Attendance-Log.xlsx. WF-5.2 amended (or new SOP) prescribes immediate same-day logging by BC on duty for any call-out, no-show, or late event.

Phase 2 implementation: Parser reads the xlsx → attendance_log table → gold view counts per driver per month.

Owner: BC on duty (recommended); Aaliyah (alternate — already owns HR records). Pick one before Phase 1 starts.

3. Schema Additions and Extensions

Schema v1.2 supports 7 of 9 metrics. Two new tables and one extension complete the picture.

3.1 — New table: vedr_events

vedr_events
├── id            INT PK auto-increment
├── event_date    DATE  — date of the alert
├── event_time    TIME  — HH:MM:SS for clustering analysis
├── driver_id     INT FK → drivers
├── vehicle_id    INT FK → vehicles  — truck the event occurred in
├── event_type    TEXT  — controlled vocabulary: "harsh_braking","harsh_accel","distracted","no_seatbelt","speeding","cornering","other"
├── severity      TEXT  — "low","medium","high" (BC judgment if not from system)
├── confirmed     BOOLEAN — TRUE = real event, FALSE = false positive (BC filter)
├── reviewed_by   TEXT  — BC name who confirmed
├── coached       BOOLEAN — TRUE if 1:1 coaching delivered (per WF-4.2/WF-4.4)
├── coaching_date DATE  — when coaching happened (NULL until done)
├── notes         TEXT  — free text
├── source_file   TEXT  — original capture file path
└── created_at    DATETIME — when row was inserted

Indexes: (driver_id, event_date), (event_date) for monthly rollups.

Driver-month query (gold view):

SELECT driver_id, COUNT(*) AS vedr_events
FROM vedr_events
WHERE confirmed = TRUE
  AND event_date BETWEEN :month_start AND :month_end
GROUP BY driver_id;

3.2 — New table: attendance_log

attendance_log
├── id              INT PK auto-increment
├── event_date      DATE  — date the absence/lateness occurred
├── driver_id       INT FK → drivers
├── event_type      TEXT  — "call_out","no_show","late","early_out","scheduled_off","sick","bereavement","other"
├── reason          TEXT  — driver-stated reason (free text)
├── advance_notice  TEXT  — "day_before","same_day_morning","same_day_after_dispatch","none"
├── coverage_source TEXT  — "internal_bench","mutual_aid","BC_drove","route_dropped","not_needed"
├── coverage_cost_estimate DECIMAL — optional, for OT/borrow tracking
├── logged_by       TEXT  — BC who logged it
├── notes           TEXT
└── created_at      DATETIME

Indexes: (driver_id, event_date), (event_date).

Driver-month query (gold view):

SELECT driver_id, COUNT(*) AS callout_count
FROM attendance_log
WHERE event_type IN ('call_out','no_show')
  AND event_date BETWEEN :month_start AND :month_end
GROUP BY driver_id;

Why log scheduled_off and bereavement, not just call_outs? The scorecard counts unplanned absence (call_out, no_show). The log captures the rest because patterns matter for VD5 OP.VD5.07 (call-out patterns) and for the carelessness-vs-malice operating philosophy from VD4 M3.

3.3 — Extension to existing table: ryde_scores

Current schema:

ryde_scores
├── id            INT PK
├── score_date    DATE
├── driver_id     INT FK
├── ryde_score    DECIMAL
├── review_count  INT
├── positive_count INT
├── negative_count INT
└── notes         TEXT

Proposed extension (additive — doesn’t break existing rows):

+ count_5_star          INT  — explicit 5-star count for the period
+ count_4_star          INT
+ count_3_star          INT
+ count_2_star          INT
+ count_1_star          INT  — explicit 1-star count
+ accidental_flagged    INT  — count where customer self-identifies in comments as accidental (per VD4 open observation)
+ period_start          DATE — adds explicit period (currently implicit in score_date)
+ period_end            DATE
+ source_file           TEXT

Migration note: Existing positive_count ≈ count_5_star + count_4_star; existing negative_count ≈ count_2_star + count_1_star. Once the extension is populated, positive_count and negative_count become derived (or get retired in v2.0).

3.4 — New view: monthly_driver_scorecard

The gold-layer view that the scorecard workbook reads from in Phase 2+. Joins the per-source aggregations into one row per driver per month.

CREATE VIEW monthly_driver_scorecard AS
WITH
month_calendar AS (
  SELECT DISTINCT
    strftime('%Y-%m', activity_date) AS month_key,
    DATE(strftime('%Y-%m', activity_date) || '-01') AS month_start,
    DATE(strftime('%Y-%m', activity_date) || '-01', '+1 month', '-1 day') AS month_end
  FROM wsw_daily_detail
),

wsw_month AS (
  SELECT
    driver_id,
    strftime('%Y-%m', activity_date) AS month_key,
    -- ILS weighted by volume
    ROUND(SUM(ils_pct * actual_del_stops) / NULLIF(SUM(actual_del_stops), 0), 2)        AS ils_pct,
    -- DNA per 1000
    ROUND(SUM(dna_count) * 1000.0 / NULLIF(SUM(actual_del_stops), 0), 2)                AS dna_per_1000,
    -- Completion %
    ROUND(SUM(actual_del_stops) * 100.0 / NULLIF(SUM(planned_del_stops), 0), 2)         AS completion_pct
  FROM wsw_daily_detail
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, month_key
),

sph_month AS (
  -- Aggregate weekly SPH to month, weighted by stops
  SELECT
    driver_id,
    strftime('%Y-%m', period_end) AS month_key,
    ROUND(SUM(sph_duty * stops) / NULLIF(SUM(stops), 0), 2) AS sph
  FROM driver_productivity_weekly
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, month_key
),

ryde_month AS (
  SELECT
    driver_id,
    strftime('%Y-%m', period_end) AS month_key,
    AVG(ryde_score)               AS ryde_score,
    SUM(count_5_star)             AS five_star,
    SUM(count_1_star)             AS one_star
  FROM ryde_scores
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, month_key
),

vedr_month AS (
  SELECT
    driver_id,
    strftime('%Y-%m', event_date) AS month_key,
    COUNT(*)                      AS vedr_events
  FROM vedr_events
  WHERE confirmed = TRUE
  GROUP BY driver_id, month_key
),

attendance_month AS (
  SELECT
    driver_id,
    strftime('%Y-%m', event_date) AS month_key,
    SUM(CASE WHEN event_type IN ('call_out','no_show') THEN 1 ELSE 0 END) AS callouts
  FROM attendance_log
  GROUP BY driver_id, month_key
)

SELECT
  d.driver_id,
  d.first_name || ' ' || d.last_name AS driver_name,
  w.month_key,
  w.ils_pct,
  w.dna_per_1000,
  r.ryde_score,
  r.five_star,
  r.one_star,
  s.sph,
  w.completion_pct,
  COALESCE(v.vedr_events, 0)        AS vedr_events,
  COALESCE(a.callouts, 0)           AS callouts
FROM drivers d
JOIN wsw_month w   ON w.driver_id = d.driver_id
LEFT JOIN sph_month  s ON s.driver_id = d.driver_id AND s.month_key = w.month_key
LEFT JOIN ryde_month r ON r.driver_id = d.driver_id AND r.month_key = w.month_key
LEFT JOIN vedr_month v ON v.driver_id = d.driver_id AND v.month_key = w.month_key
LEFT JOIN attendance_month a ON a.driver_id = d.driver_id AND a.month_key = w.month_key
WHERE d.status = 'active'
ORDER BY d.driver_id, w.month_key;

Edge cases handled:

• No WSW activity in the month → driver excluded (didn’t drive). Acceptable.
• VEDR / Attendance / RYDE absent → COALESCE to 0 (not driving means no events; missing capture means we don’t penalize the driver — banding logic on the workbook side already skips empty cells).
• Active driver list pulled from drivers.status = 'active' so terminated drivers fall out automatically.
• ILS/Completion weighted by volume (not simple average) — heavier-load drivers’ percentages count more, which matches operational reality.

4. Build Sequence

Four phases with explicit milestones, owners, and validation criteria.

Phase 0 — Manual first cycle (target: 2026-06-01 to 2026-06-12)

Goal: Run the May 2026 month-close cycle entirely by hand and prove the SOP + workbook in real ops.

Activities:

• Day 1 (Jun 1): BC on duty pulls the four WSW files (May 2–8, May 9–15, May 16–22, May 23–29) and the four Weekly Driver Productivity files. Computes the 4 R-state metrics for each driver and pastes into Monthly-Driver-Scorecard-v1.0.xlsx.
• Day 1: BC also opens the RYDE portal, scans each driver’s score / 5-star / 1-star count for May, enters into workbook.
• Day 1: BC opens GroundCloud VEDR module, counts confirmed events per driver for May.
• Day 1: BC scans Slack history for call-out / no-show events for May, counts per driver.
• Day 3: Roger reviews aggregate (Top Driver, CSA Average, anomalies, DQ flags). Sign-off.
• Day 5–10: BC distributes individual scorecards during daily 1:1 belt windows. 100% by Day 10.

Deliverables: First populated workbook archived to /VD4 - Service Quality/Data/Monthly-Scorecards/2026-05/.

Validation: Did the workflow take the SOP-budgeted time? Where did BCs lose time? Are any cells reliably blank or wrong? Capture friction → drives Phase 1 SOP design.

Owner: BC on duty (Vincent or Brandon). Roger reviews.

Phase 1 — Capture infrastructure for P/B-state metrics (target: 2026-05-01 through 2026-06-30)

Goal: Eliminate manual scanning by Phase 0’s second cycle (Jun close → first week of Jul).

Activities:

1. VEDR daily log. Build /VD4 - Service Quality/Data/VEDR-Events-Log.xlsx (or /VD3 - Fleet Readiness/Data/ — pick one). Draft WF-4.5 VEDR Event Logging SOP. BC enters confirmed/false-positive flag once daily during en-route phase. Live by 2026-05-15 so May data is captured the right way.
2. Attendance log. Build /VD5 - People Management/Data/Attendance-Log.xlsx. Amend WF-5.2 to require same-day logging. Live by 2026-05-15.
3. RYDE export check. BC (or Roger) verifies whether RYDE portal exports CSV. Document format.
4. RYDE monthly capture SOP. Draft (proposed: WF-6.5 RYDE Monthly Capture). Last business day of the month, BC pulls export, saves to /VD4 - Service Quality/Data/RYDE-Monthly/YYYY-MM/.
5. D4 ask-back resolution. VD4 D4 (en-route monitoring tools) ask-back returns from BCs — finalizes who reviews VEDR daily.

Deliverables:

• VEDR-Events-Log.xlsx + WF-4.5
• Attendance-Log.xlsx + WF-5.2 amendment (or WF-5.4)
• WF-6.5 RYDE Monthly Capture
• D4 ask-back resolution → updates WF-4.0a and Decision Catalogue

Validation: Run the June 2026 month-close cycle (target: 2026-07-01) using captured data instead of manual scanning. Compare BC time vs Phase 0 — should drop sharply.

Owner: Roger drives Phase 1. BCs own VEDR + attendance capture once SOPs land. Aaliyah may own attendance if reassigned.

Phase 2 — DuckDB + MotherDuck warehouse + parsers (target: 2026-07-01 through 2026-09-30)

Engine decision: DuckDB + MotherDuck on AWS S3. Documented in WF-6.7 Warehouse Engine Decision (replaces the original SQLite reference). See WF-6.7 for full TCO and rationale.

Goal: Move from xlsx-as-data-store to xlsx-as-view. The workbook becomes a printable wrapper around the monthly_driver_scorecard gold view.

Activities:

1. Provision AWS S3 buckets. cedarfell-raw/ (immutable raw source files, partitioned /<source>/<YYYY-MM>/) and cedarfell-staging/ (Parquet, partitioned by table and month). One AWS account, IAM user for parser writes, IAM user for read-only consumer access.
2. Initialize MotherDuck workspace. Free tier covers Cedarfell scale. Create database cedarfell_dev (Roger’s local DuckDB attached) and cedarfell_prod (cloud, BC + Jana read access).
3. Apply Schema v1.3 DDL to both DuckDB local and MotherDuck cloud.
4. Build parsers (Python) — sequence by leverage:
   • Parser 1: WSW XLS → S3 raw → Parquet wsw_daily_detail (4 metrics depend on this).
   • Parser 2: GroundCloud Weekly Driver Productivity CSV → S3 raw → Parquet driver_productivity_weekly (1 metric).
   • Parser 3: VEDR-Events-Log.xlsx → S3 raw → Parquet vedr_events (1 metric).
   • Parser 4: Attendance-Log.xlsx → S3 raw → Parquet attendance_log (1 metric).
   • Parser 5: RYDE export → S3 raw → Parquet ryde_scores (3 metrics — depends on Phase 1 RYDE format confirmation).
   • Each parser: pure Python, reads source file, writes to S3 raw (immutable copy), emits Parquet to S3 staging via DuckDB COPY. Locally testable with a sample file.
5. dbt-duckdb project setup. Bronze → silver → gold model layers. Profile points at MotherDuck for prod, local DuckDB for dev. Same dbt project parameterizable per CSA in the future.
6. Build gold view monthly_driver_scorecard as a dbt model (DDL in §3.4 above).
7. Connect workbook to gold view. Python script populate_scorecard.py runs the gold view query against MotherDuck and pastes per-driver results into the Driver-Data sheet. Scheduled via cron or run on demand by BC. Avoids ODBC driver install on BC machines.
8. Validation cycle: September 2026 month-close (target: 2026-10-01) runs from gold view. Compare to Phase 1 manual numbers — must match within ±0.5% on numeric metrics, 100% on counts.

Deliverables:

• AWS S3 buckets provisioned + IAM roles
• MotherDuck workspace + Schema v1.3 applied
• 5 parser scripts + tests committed to a Cedarfell dbt repo
• dbt-duckdb project with bronze/silver/gold layers
• gold view monthly_driver_scorecard
• populate_scorecard.py workbook auto-populate script
• Validation report against Sep-2026 manual cycle

Owner: Roger (with Claude support for code). Jana for warehouse-side reconciliation against Tensor when applicable.

Phase 3 — Dashboard layer (target: 2026-10-01 through 2026-12-31)

Goal: Live dashboard alongside the printed scorecards, supporting Roger’s Day-3 aggregate review and surfacing trends across months.

Activities:

1. Choose dashboard tool: Metabase (open source, self-host) vs Google Sheets reads (low overhead) vs custom Python/HTML (most control, most work). Decision documented in WF-6.6.
2. Build Driver-Scorecard dashboard: per-driver trend per metric, CSA average, Top Driver winners over time, DQ history.
3. Build Monthly Roll-up dashboard for Roger: aggregate distribution, outliers, banding %.
4. Connect to gold view monthly_driver_scorecard (and any new gold views built for cross-cutting metrics).

Deliverables:

• Dashboard tool decision + setup
• 2 dashboards (driver-level + Roger-level)
• WF-6.6 Dashboard Operations SOP

Owner: Roger (with Claude). Brandon + Vincent test for friction.

5. Milestone Calendar

6. Open Dependencies and Questions

These need resolution before the noted phase can complete.

7. Cross-References

• WF-4.4 Monthly Driver Scorecard SOP — /VD4 - Service Quality/Workflows/
• Monthly-Driver-Scorecard-v1.0.xlsx — /VD4 - Service Quality/Data/
• Database-Schema-v1.md (v1.2 → v1.3) — /VD6 - Financial Operations/Data/
• WF-6.3 Data Collection Process — /VD6 - Financial Operations/Workflows/
• KPI Taxonomy v1.0 — /Strategic/
• Decision Catalogue — /VD6 - Financial Operations/Data/
• Scorecard-Data-Sources.xlsx (operational tracker) — /VD6 - Financial Operations/Data/

8. Change Log