The reference sheet, field by field — Indicator Design and the LogFrame — Data Analysis

The test the sheet has to pass

An indicator reference sheet is finished when a competent analyst who has never met you can compute your number from it, from the raw data, without asking you a question.

That is the only test worth applying, and it is brutal. Almost every reference sheet in circulation fails it at the same three points: the denominator source is named but not specified, the exclusions are not listed, and nobody wrote down what to do when a reporting unit is silent.

The fields

Field	What it holds
Name	Including the unit of measure
Definition	One sentence a non-analyst can read
Numerator	The exact condition, in words
Denominator	The exact population, and where it comes from
Exclusions	What is deliberately left out, and why
Unit of measure	Percent, count, per 1,000, litres per person per day
Direction	Higher is better, lower is better, or neither
Disaggregation	The cuts that will be reported, and the minimum cell size
Frequency	How often it is computed, and the reporting lag
Source	System, table, field. Not “programme records”
Method of computation	Enough that the arithmetic is unambiguous
Decision informed	What changes depending on the value
Known limitations	What the number cannot support

Thirteen fields, most of them a line. A sheet takes twenty minutes and it is never written again.

Worked: penta3 coverage

Name              penta3_coverage_percent_monthly
Definition        The share of the target infant population that received a third
                  dose of pentavalent vaccine in the month.
Numerator         Third doses of pentavalent vaccine administered, as reported by
                  facilities that submitted a monthly report for that month.
Denominator       Surviving infants in the catchment, from the MoH population
                  estimates 2024, projected from the 2015 census at 2.4% annual
                  growth, summed across facilities that reported.
Exclusions        Facility-months with report_submitted = false are excluded from
                  both numerator and denominator. Doses given to children outside
                  the catchment are not separable and remain in the numerator.
Unit              Percent
Direction         Higher is better
Disaggregation    Month, facility type, district. Minimum cell 30 in denominator.
Frequency         Monthly, reported 6 weeks after month end
Source            DHIS2 data element PENTA3_DOSES, org unit level 4, monthly
Computation       100 * sum(doses) / sum(target_population), over reporting
                  facility-months only. Not the mean of facility-level rates.
Decision          Whether a district is prioritised for outreach in the next
                  quarterly microplan.
Limitations       Reporting completeness was 76.5% in 2024 and fell to 29% in
                  August; the figure is a lower bound in any month where
                  completeness is below 90%. Catchments overlap, so facility-level
                  values are unreliable; use district level or above.

Read the exclusions and the computation rows together, because they are where the two-analyst test is usually failed.

“Over reporting facility-months only. Not the mean of facility-level rates.” Those two sentences settle a difference of several points and neither is implied by the definition. A ratio of sums and a mean of ratios are different numbers, and both are reasonable readings of “coverage”.

The field everyone omits

Decision informed is missing from most templates in use, and it is the field that does the most work.

Filling it in has three effects, and all three are uncomfortable in a useful way.

It exposes indicators that inform nothing. Some of those are still required — a donor asks for them — and that is a legitimate answer. Write “donor reporting requirement, no operational decision” and stop spending analytical effort on it.
It exposes indicators that inform two decisions. Coverage used both to prioritise outreach and to trigger a supply order needs two definitions, or one of the two decisions is being made on the wrong number.
It sets the precision you need. An indicator that prioritises a district needs to be right about the ranking. One that triggers a supply order needs to be right about the level. Those are different requirements and they cost different amounts.

Keep it beside the code

A reference sheet in a Word file in somebody’s mailbox is a reference sheet that drifts. Store it as data next to the script that computes the indicator.

import json
from pathlib import Path

sheet = json.loads(Path("indicators/penta3_coverage.json").read_text())

assert sheet["denominator_source"], "denominator source is required"
assert sheet["decision_informed"], "an indicator with no decision needs saying so"

numerator = vax.loc[vax["reported"] & (vax["antigen"] == "penta3"),
                    "doses_administered"].sum()
denominator = vax.loc[vax["reported"] & (vax["antigen"] == "penta3"),
                      "target_population"].sum()

print(f"{sheet['name']}: {100 * numerator / denominator:.1f}")

sheet <- jsonlite::read_json(here::here("indicators", "penta3_coverage.json"),
                             simplifyVector = TRUE)

stopifnot(nzchar(sheet$denominator_source), nzchar(sheet$decision_informed))

vax |>
  filter(report_submitted, antigen == "penta3") |>
  summarise(value = 100 * sum(doses_administered) / sum(target_population))

Two gains. The assertions fail the build when a sheet is incomplete, which is the same move the platform’s own content schema makes. And the sheet ships with the result, so a table and its definitions travel together — the habit the foundations course introduced with a definitions.csv beside every output.

Write the exclusions as code, not as prose

The exclusions field is the one most likely to be true in the document and false in the script. Close the gap by generating one from the other.

EXCLUSIONS = [
    ("non-reporting facility-months", lambda d: ~d["reported"]),
    ("other antigens", lambda d: d["antigen"] != "penta3"),
]

remaining = vax.copy()
for label, rule in EXCLUSIONS:
    dropped = rule(remaining).sum()
    remaining = remaining[~rule(remaining)]
    print(f"excluded {dropped:>5} rows: {label}")
print(f"{len(remaining)} rows in the indicator")

EXCLUSIONS <- list(
  "non-reporting facility-months" = function(d) !d$report_submitted,
  "other antigens"                = function(d) d$antigen != "penta3"
)

remaining <- vax
for (label in names(EXCLUSIONS)) {
  rule <- EXCLUSIONS[[label]]
  cat(sprintf("excluded %5d rows: %s\n", sum(rule(remaining)), label))
  remaining <- remaining[!rule(remaining), ]
}

The printout is the exclusions field, with counts. Paste it into the sheet and the two cannot disagree.

Version it

An indicator definition changes. When it does, the series breaks, and the break has to be visible.

version   2.1
changed   2026-07-28
change    Denominator restricted to reporting facilities. Previously all
          facilities, with non-reporters counted at their target population,
          which understated coverage by about 23% in August 2024.
effect    Series revised from 2024-01. Values before v2.1 are not comparable.

Never silently improve a definition. A coverage figure that rises eight points because the definition changed, presented in the same chart as previous quarters, is the most convincing wrong finding an M&E system can produce.

The two-analyst test, in practice

Once a quarter, take an indicator, hand the sheet and the raw extract to a colleague who did not write it, and compare. It takes an hour and it finds things no review of the document does.

What it typically surfaces:

The colleague used the calendar month; you used the reporting month.
The colleague computed a mean of facility rates; you computed a ratio of sums.
The colleague included the district hospital; your extract excluded it because of an org-unit level filter nobody documented.

Every one of those is a sheet that needed one more line. The purpose of the exercise is to find the line, not to find out who was right.

What comes next

The field that generates more disagreement than the other twelve combined is the denominator. The next lesson is entirely about choosing one, defending it, and saying honestly what it excludes.