SDG 16 – Peace, Justice & Strong Institutions

Course: Governance & Public Sector Analytics

Focus: Use data to promote accountability, transparency, and institutional performance across government ministries, departments, agencies (MDAs), and county institutions.

Who this is for

Policy analysts, M&E officers, auditors, data scientists, public administration students, civil society analysts, and civic-tech builders.
Prereqs: Basic Excel/Power BI, beginner SQL/Python.

What you’ll learn (Outcomes)

  • Build a clean, reproducible public-sector data model (budgets, spend, procurement, justice/caseflow, service delivery).

  • Compute governance and integrity indicators (e.g., budget absorption, case clearance rate, procurement red flags).

  • Design performance dashboards (Power BI) aligned to SDG 16 targets.

  • Apply corruption risk analytics (outlier detection, supplier concentration, single-sourcing flags).

  • Model crime/justice flows and backlog risks with SQL/Python.

  • Communicate insights ethically with clear caveats and data provenance.

Tools

Power BI (reporting, DAX, data modeling) • SQL (ETL, KPIs) • Python (pandas, statsmodels/scikit-learn for simple models; matplotlib)

8-Week Outline (lectures + labs)

Week 1 — Foundations of Governance Analytics

  • SDG 16 indicators: data ethics, privacy, and FOI/open data basics.

  • Lab: Data inventory & quality checklist.

Week 2 — Public Finance: Budgets → Expenditure

  • Chart of accounts; vote/department/program structures; absorption & variance.

  • Lab: Build a star schema for PF data in Power BI.

Week 3 — Procurement & Integrity Analytics

  • Methods (open, restricted, direct); red flags & market concentration.

  • Lab: Supplier network/concentration scorecards.

Week 4 — Justice & Caseflow Analytics

  • Crime reports → arrests → charges → trials → outcomes; backlog & time-to-disposition.

  • Lab: Case lifecycle funnel + clearance rate DAX.

Week 5 — Service Delivery & Citizen Feedback

  • Complaints systems, service SLAs, call-center logs, and ombudsman data.

  • Lab: Text-to-categories pipeline (Python) + SLA heatmaps.

Week 6 — Open Government & Fiscal Transparency

  • Budget transparency, program performance info, and publication timeliness.

  • Lab: “Data availability score” index.

Week 7 — Building the Performance Dashboard

  • Multi-page Power BI report; drill-through; role-level security mock.

  • Lab: Governance KPI deck + narrative.

Week 8 — Capstone Sprint

  • Stakeholder mapping, wireframes, QA, executive brief, demo.

Suggested Data Model (Power BI / Warehouse)

Fact tables

  • FactBudget(vote_id, program_id, fy, approved_amount)

  • FactExpenditure(vote_id, program_id, fy, month, paid_amount)

  • FactProcurement(proc_id, fy, method, supplier_id, amount, award_date)

  • FactCases(case_id, court, offense, filed_date, closed_date, outcome)

  • FactCrimeReports(station, report_date, offense, count)

  • FactServiceRequests(request_id, channel, received_at, closed_at, category, status)

Dimensions

  • DimVote(vote_id, vote_name, mda_type, county)

  • DimProgram(program_id, program_name, SDG_target)

  • DimSupplier(supplier_id, name, ownership_type)

  • DimTime(date_key, fy, quarter, month)

Core KPIs (ready to implement)

Public Finance

  • Budget Absorption % = SUM(FactExpenditure.paid_amount) / SUM(FactBudget.approved_amount)

  • Recurrent vs Development Mix = share of dev spend in total.

  • Variance to Budget = (Approved − Actual) / Approved.

Procurement & Integrity

  • Single-Sourcing% % = Direct awards / Total awards.

  • Supplier Concentration (HHI) across each vote/program.

  • Red-Flag Rate (late awards, just-under-threshold amounts, repeat awards).

Justice & Safety

  • Case Clearance Rate (CCR) = Closed cases / New cases (period).

  • Backlog = Cases pending > (policy threshold, e.g., 365 days).

  • Median Time-to-Disposition by offense/court.

Service Delivery

  • On-Time Resolution % within SLA.

  • Complaint Recidivism (repeat within 90 days).

  • Citizen Satisfaction (survey index if available).

DAX Snippets

 
Budget Absorption % :=
DIVIDE( SUM(FactExpenditure[paid_amount]),
SUM(FactBudget[approved_amount]) )

Case Clearance Rate :=
DIVIDE( CALCULATE(COUNTROWS(FactCases), NOT(ISBLANK(FactCases[closed_date]))),
CALCULATE(COUNTROWS(FactCases), YEAR(FactCases[filed_date]) = SELECTEDVALUE(DimTime[fy])) )

Supplier HHI :=
VAR T =
SUMMARIZE(FactProcurement, DimSupplier[supplier_id],
"Share", DIVIDE(SUM(FactProcurement[amount]), CALCULATE(SUM(FactProcurement[amount]), ALL(DimSupplier))))
RETURN SUMX(T, [Share] * [Share])

SQL Patterns

1) Single-sourcing share by vote & FY

 
SELECT b.vote_id, p.fy,
AVG(CASE WHEN p.method IN ('Direct','SingleSource') THEN 1.0 ELSE 0 END) AS single_source_share
FROM FactProcurement p
JOIN DimVote b ON b.vote_id = p.vote_id
GROUP BY b.vote_id, p.fy;

2) Backlog (cases > 365 days pending)

 
SELECT court,
COUNT(*) AS backlog_cases
FROM FactCases
WHERE closed_date IS NULL
AND CURRENT_DATE - filed_date > INTERVAL '365 days'
GROUP BY court;

3) “Just-under-threshold” red flag

 
SELECT proc_id, vote_id, amount
FROM FactProcurement
WHERE amount BETWEEN (threshold*0.95) AND (threshold*1.00);

Python Starters (ETL & Risk Flags)

 
import pandas as pd
import numpy as np

# Load
bud = pd.read_csv("budget.csv")
exp = pd.read_csv("expenditure.csv")
proc = pd.read_csv("procurement.csv", parse_dates=["award_date"])
cases = pd.read_csv("cases.csv", parse_dates=["filed_date","closed_date"])

# Budget absorption
absorption = (exp.groupby(["vote_id","program_id"])["paid_amount"].sum()
.div(bud.groupby(["vote_id","program_id"])["approved_amount"].sum())
.rename("absorption_rate")).reset_index()

# Supplier concentration (HHI)
agg = proc.groupby(["vote_id","supplier_id"])["amount"].sum().reset_index()
tot = agg.groupby("vote_id")["amount"].sum().rename("tot")
agg = agg.join(tot, on="vote_id")
agg["share"] = agg["amount"]/agg["tot"]
hhi = agg.groupby("vote_id")["share"].apply(lambda s: np.sum(s**2)).reset_index(name="supplier_hhi")

# Case time-to-disposition
dur = cases.dropna(subset=["closed_date"]).assign(
ttd=lambda d: (d["closed_date"]-d["filed_date"]).dt.days
).groupby("court")["ttd"].median().reset_index(name="median_ttd_days")

Dashboard Pages (wireframe guide)

  1. Overview: Absorption %, CCR, On-time SLA, Red-Flag Rate, alerts.

  2. Public Finance: by vote/program, dev vs recurrent, variance, trends.

  3. Procurement Integrity: method mix, HHI, red-flag table, supplier profiles.

  4. Justice & Safety: funnel (report→trial→outcome), backlog map, time-to-disposition.

  5. Service Delivery: SLA heatmaps, complaint categories, recurrence.

  6. Open Government: publication timeliness, data completeness scores.

Capstone (what to deliver)

Brief: Build a county-level Governance Performance Dashboard (Power BI) with a short methodology note and a 5–8 slide executive brief.

Minimum requirements

  • Clean data model; documented transformations (SQL/Python).

  • At least 8 KPIs across finance, procurement, justice, and service delivery.

  • 1 integrity analytic (e.g., HHI + red-flag table).

  • Role/department drill-downs and time slicers.

  • Insights page: 5 bullets with policy recommendations + limitations.

Grading rubric (100 pts)

  • Data model & documentation (25)

  • KPI correctness & clarity (25)

  • Integrity analytics depth (15)

  • Visual design & usability (15)

  • Policy insights & communication (20)

Ethics & Governance Guardrails (include in report)

  • Protect personal data; aggregate where possible.

  • Clearly state data gaps/assumptions; version datasets.

  • Distinguish signals from proof of misconduct; refer red flags for audit.

  • Provide reproducible scripts and a data dictionary.

 

Student Ratings & Reviews

No Review Yet
No Review Yet
Price
From

Free

Courses Title
Governance & Public Sector Analytics
Language
Not specified
Course Level
Expert
Reviews
0
Quizzes
75
Duration
Students
0
Certifications
Yes
Start Time
15 Jan, 2026
Instructor
Collins Odhiambo
Free
Free access this course
  • Expert
  • 0 Total Enrolled
  • December 13, 2025 Last Updated
  • Enrollment validity: Lifetime
  • Certificate of completion
Get Certificate

Earn Quality Certificates from DatalytIQs Academy

Get Started Now
img

Want to receive push notifications for all major on-site activities?

✕