JoMatata/CAPSTONE-PROJECT

Monthly CHW Activity Aggregation Model - This dbt project transforms Community Health Worker (CHW) activity data into monthly performance metrics for healthcare analytics dashboards in Kenya. # CAPSTONE-PROJECT Monthly CHW Activity Aggregation Model # CHW Monthly Activity Aggregation Project **JOAN WAVINYA** ## Project Overview This dbt project transforms Community Health Worker (CHW) activity data into monthly performance metrics for healthcare analytics dashboards in Kenya. ## Problem: Healthcare analytics teams needed a performant way to track CHW productivity across regions. The source data contains individual visit records, but dashboards require monthly aggregations with special business rules for delayed field reporting. ## Solution: ### 1. Month Assignment Macro (`macros/month_assignment.sql`) **Purpose:** Implements the 26th-of-month cutoff rule for field reporting delays. **Logic:** - Activities before day 26 → Assigned to current month - Activities on/after day 26 → Assigned to next month - Correctly handles year boundaries (Dec 26 → Jan next year) **Why:** Remote field workers often submit late reports. Activities on Jan 26-31 are actually "closing out" January work, so they count toward February's reporting period. ### 2. Aggregation Model (`models/metrics/chw_activity_monthly.sql`) **Configuration:** - **Materialization:** Incremental table (performance optimization) - **Strategy:** delete+insert (handles late-arriving data) - **Unique Key:** (chv_id, report_month) - **Incremental Logic:** Reprocesses last 2 months on each run **Metrics Calculated:** 1. `total_activities` - All activities count 2. `unique_households_visited` - Distinct households (deduplication) 3. `unique_patients_served` - Distinct patients (handles NULLs) 4. `pregnancy_visits` - Conditional aggregation 5. `child_assessments` - Conditional aggregation 6. `family_planning_visits` - Conditional aggregation **Data Quality Filters:** - Excludes NULL chv_id (data entry errors) - Excludes NULL activity_date (invalid records) - Excludes soft-deleted records (is_deleted = TRUE) ### 3. Data Quality Tests (`models/metrics/schema.yml`) **Tests Implemented:** - `not_null …