How the dashboard was built

The data

• National coverage — WHO/UNICEF estimates of immunization coverage (DTP3 and measles, by country and year, 1980 onward). Licence CC BY 3.0 IGO.
• Child survival — UN IGME neonatal mortality rate, by country and year. Used to set coverage against an outcome.
• Subnational coverage — WHO/UNICEF subnational (state and district) coverage, 2018, with doses administered and eligible population per district. The basis for the state map and the burden ranking.

Everything here is aggregate, public, and openly licensed. No personal or facility-identifying data is involved.

The tools

The raw files (two CSVs and an Excel workbook) were loaded into DuckDB and queried with SQL. SQL was chosen deliberately: every figure on the dashboard traces back to a query that anyone can read and re-run, rather than to a spreadsheet step that leaves no record.

Step 1 · Build the national trend

Joining coverage to mortality

Three separate series (DTP3, measles, neonatal mortality) are filtered to Nigeria and lined up by year. One detail mattered: the mortality file carried two columns that differed only by capitalisation, so the filter has to use the unambiguous code columns (INDICATOR, SEX) rather than the text labels, otherwise the engine silently matches the wrong column and returns nothing.

WITH d AS (SELECT TIME_PERIOD yr, OBS_VALUE v FROM dtp3_raw WHERE "Geographic area"='Nigeria'),
     m AS (SELECT TIME_PERIOD yr, OBS_VALUE v FROM mcv1_raw WHERE "Geographic area"='Nigeria'),
     n AS (SELECT TIME_PERIOD yr, OBS_VALUE v FROM cme_raw WHERE "Geographic area"='Nigeria' AND INDICATOR='CME_MRM0' AND SEX='_T')
SELECT yr, MAX(dtp3) dtp3, MAX(mcv1) mcv1, ROUND(MAX(nmr),1) nmr FROM (
  SELECT yr,v dtp3,NULL mcv1,NULL nmr FROM d
  UNION ALL SELECT yr,NULL,v,NULL FROM m
  UNION ALL SELECT yr,NULL,NULL,v FROM n)
WHERE yr BETWEEN 1984 AND 2024 GROUP BY yr ORDER BY yr;

Step 2 · Validate before trusting

Catching impossible records

Subnational administrative data is noisy. The first check is the simplest one that matters: a district cannot give more doses than it has eligible children. Roughly a third of the 2018 measles records fail it. They are counted, then excluded from everything downstream.

SELECT COUNT(*) total, SUM(CASE WHEN Numerator>Denominator THEN 1 ELSE 0 END) impossible
FROM sub_raw WHERE CountryName='Nigeria' AND Vaccine='MCV1';

Step 3 · Rebuild state coverage

Aggregating districts to states

State coverage is not the average of its districts, that would weight a tiny district the same as a huge one. Instead the valid districts' doses and eligible populations are summed per state, then divided. The same query also produces the absolute number of children missed (eligible minus vaccinated), which is what drives the burden ranking.

WITH clean AS (
  SELECT Admin1 state, Numerator num, Denominator denom
  FROM sub_raw
  WHERE CountryName='Nigeria' AND Vaccine='MCV1'
    AND Denominator>0 AND Numerator>=0 AND Numerator<=Denominator)
SELECT state,
       ROUND(100.0*SUM(num)/SUM(denom),1) coverage,
       CAST(SUM(denom-num) AS BIGINT) children_missed,
       COUNT(*) districts_used
FROM clean GROUP BY state ORDER BY children_missed DESC;

What was left out, and why

A first-to-third-dose drop-off rate for polio was attempted and abandoned. In most states the third-dose count exceeded the first-dose count, which cannot happen in a real cohort and points to reporting problems rather than a real pattern. Showing a drop-off number built on that would have been misleading, so it was dropped rather than published with a caveat no one would read.

Two honest limits remain on what is shown. The national trend is country-wide while the state views are a single year (2018), so they are different lenses, not one continuous story. And coverage moving together with mortality is an association; many things drive child survival, and this analysis does not isolate the effect of vaccination.