How ibrahim-sani/irembo_voice_ai_analysis works
What this project is (type + purpose)
This repository is a SQL-first analytics project (not an app/runtime service) designed to evaluate Voice AI impact on public service journeys: accessibility, efficiency, adoption, friction, and outcome uplift.
Architecturally, it’s a classic analytics workflow:
• Source tables in PostgreSQL/Supabase (raw operational data: voice sessions, turns, users, applications, AI metrics)
• Staging models (cleaning, shaping, deduping)
• A single integrated fact model (fact_voice_ai_sessions) that becomes the analysis “source of truth”
• Analysis queries that compute KPIs, segmentation, friction indices, and simulations
• BI outputs in Metabase (PDF dashboard/report artifacts included)
How to read the file tree (what maps to architecture)
From the tree + README:
Modeling layer (dbt-like pattern but executed manually)
• models/
• stg_voice_sessions.sql
• stg_users.sql
• stg_turn_agg.sql
• stg_voice_ai_metrics.sql
• stg_applications.sql
• stg_applications_dedup.sql
• fact_voice_ai_sessions.sql ← the core “semantic” dataset
This is your transformation layer inside PostgreSQL (Supabase).
Analytics layer (downstream, read-only queries)
• analysis/*.sql
• health checks
• completion by channel
• first time vs returning
• misunderstanding / error impact
• time-to-submit by channel
• friction severity index
• error reduction simulation, etc.
These are consumer queries that depend primarily on the fact model (and sometimes staging).
BI/Reporting artifacts
• metabase_report/*.pdf
These indicate the terminal consumption layer is Metabase dashboards.
Data Sources (raw tables in Supabase/Postgres)
• Voice sessions (raw events)
• Turn-level logs (raw conversational turns)
• Users (attributes, cohorts)
• Applications (submission/outcome)
• Voice AI metrics (ASR confidence, misunderstanding rates, etc.)
Staging Layer (models/stg_*.sql)
• stg_voice_sessions (session normalization)
• stg_users (user normalization)
• stg_turn_agg (turn → session aggregation)
• stg_voice_ai_metrics (AI metrics shaping)
• stg_applications (application shaping)
• stg_applications_dedup (explicit dedup step; call this out as data quality control)
Core Fact / Semantic Model
• fact_voice_ai_sessions (integration point)
• This is the “gold” dataset: session grain + outcome flags + AI metrics + user/app join + turn aggregates
Analysis / Metrics Computation
• Each analysis/*.sql as a group: KPIs, segmentation, causal-ish impact analysis, simulations
• Call out the special category:
• Simulation/Projection (e.g., 08_error_reduction_simulation.sql) as it implies “what-if modeling” rather than descriptive analytics
BI / Consumption
• Metabase dashboard(s)
• PDF exports / executive report outputs
Execution Environment
• Supabase SQL Editor (manual run order in README)
• PostgreSQL engine (where transformations live)
Data flow (primary)
Raw Source Tables
→ Staging Models
→ Fact Model
→ Analysis Queries
→ Metabase Dashboards/Reports
Metric definitions as “contract”
The README defines:
• misunderstanding threshold = 0.33
• error = total_error_turns > 0
• completion = session_completed_flag = TRUE
• application success = application_success_flag = TRUE
• fact_voice_ai_sessions (where flags/fields likely live)
• the analysis layer (where they’re used)
Single source of truth fact table
Everything downstream should hang off fact_voice_ai_sessions. Make it the central hub.
Deterministic transformations in SQL
No application code; the “system” is the database + SQL scripts + BI.
Data quality / dedup as explicit step
stg_applications_dedup.sql is a great indicator of pipeline hygiene; show it as a distinct node (not merged into staging).
What-if simulation as separate analytic mode
Use the file names directly in labels to remove ambiguity:
Transform nodes
• models.stg_voice_sessions
• models.stg_users
• models.stg_turn_agg
• models.stg_voice_ai_metrics
• models.stg_applications
• models.stg_applications_dedup
• models.fact_voice_ai_sessions
Analysis nodes (grouped)
• Health: 01_basic_health_check.sql
• Completion/channel/time: 02_completion_by_channel.sql, 05_time_to_submit_by_channel.sql
• Cohorts: 03_first_time_vs_others.sql
• Friction/impact: 04_high_vs_low_misunderstanding.sql, 06_friction_efficiency_impact.sql, 07_friction_severity_index.sql
• Simulation: 08_error_reduction_simulation.sql
Consumption nodes
• Metabase Dashboard(s) → PDFs in metabase_report/
Does the fact table join applications directly or via sessions?
3. Where are flags computed (completion, success, error, misunderstanding)? Staging vs fact vs analysis.
4. Is Metabase querying the fact table directly or saved questions based on analysis queries?
• This is a database-centric analytics pipeline.
• The fact table is the core contract.
• Most insights are downstream read-only (analysis queries + Metabase).
• There’s an explicit quality gate (dedup) and a grain transformation (turn → session).
• There are both descriptive analytics and predictive/what-if simulation outputs.