KoboToolbox export of a post-distribution monitoring survey: checking, transformation and indicators

• (a) K2: =AND(C2="yes",D2>=10)
• (b) L2: =IF(ISNUMBER(SEARCH(" rent "," "&H2&" ")),1,0). The spaces added around the text and the search term prevent finding a choice contained within another (the problem would arise, for example, between "health" and a choice "mental_health"). Better still: request the KoboToolbox export with one 0/1 column per choice.
• (c) M2: =IF(AND(K2,G2<135),"Incomplete amount","") (ideally, 135 in a parameter cell rather than written into the formula).
• (d) =COUNTIFS(L2:L17,1,K2:K17,TRUE)/COUNTIF(K2:K17,TRUE)
• (e) =AVERAGEIFS(I2:I17,K2:K17,TRUE)
• (f) =COUNTIFS(I2:I17,">=4",K2:K17,TRUE)/COUNTIF(K2:K17,TRUE)

Exclusions: PDM-004 (no consent, kept in the raw database); PDM-005 to PDM-008 (durations of 4 to 7 minutes, under 10 minutes). That leaves 11 valid interviews.

E03: average duration of 5.5 minutes compared with 20 to 22 minutes for the other enumerators (E01: 21.0; E02: 21.7; E04: 20.3), and four identical answers (food only, satisfaction 5, knows the mechanism). This is the profile of rushed or fabricated interviews.

Effect: with the 15 consenting interviews, average satisfaction 3.87 and 66.7% satisfied (10/15); on the 11 valid ones, 3.45 and 54.5% (6/11). Keeping E03 would inflate satisfaction by 12 points.

Reply to the supervisor: no. An invalid interview does not become valid because it is favourable: it skews the result in the direction that suits us. Actions: check by phone call-back (with consent) a sample of the households surveyed by E03, and the geolocation and timestamp data; if falsification is confirmed, exclude all their interviews, redo the missing interviews, and deal with the matter with human resources; strengthen automatic checks (minimum duration, daily checks by enumerator).

Uses (n = 11): food 7 (63.6%); rent 7 (63.6%); health 3 (27.3%); debt 3 (27.3%); education 1 (9.1%). The total exceeds 100% because a household can mention several uses.

Satisfaction: average 3.45; satisfied 6/11 = 54.5%. Refugees: 3/7 = 42.9%, average 3.14; host community: 3/4 = 75.0%, average 4.00.

Complaints mechanism: 6/11 = 54.5% in total; female heads of household 4/6 = 66.7%; male 2/5 = 40.0%.

Full amount: 9/11 = 81.8%; two households (PDM-009 and PDM-015) received USD 100 instead of 135.

Interpretation: rent is mentioned by all 7 refugee households and by no host household: the burden of rent falls on refugees and probably explains their lower satisfaction (insufficient amount). Only half of households know the complaints mechanism, which is low. However, these figures relate to an extract of 11 interviews: the differences between subgroups (4 to 7 households) cannot be interpreted statistically; they point to leads to be checked on the full database.

Hypotheses: data entry error or the household misremembering; partial withdrawal (the household did not withdraw everything); fees taken by an agent or trader; deduction by an intermediary (landlord, site leader, staff member); amount reduced for a legitimate reason unknown to the enumerator.

Actions: (1) compare with the payment provider's data (amount loaded on the card, withdrawals); (2) if the discrepancy is confirmed, refer the case, without sharing it more widely, to the focal point of the complaints or anti-fraud mechanism, according to the procedure, with only the identifiers needed; (3) do not call the household back in public or in front of the suspected intermediary, so as not to expose it to retaliation; (4) look for other cases in the full database (same area, same agent); (5) report in aggregated form in the PDM report. Two cases out of 11 (18%) is a strong signal to be checked quickly.

Flow: (1) automatic import of the KoboToolbox export (connection or file) into Power Query, without manual editing of the raw database; (2) recorded and documented cleaning steps: exclusion of non-consent and durations under 10 minutes, normalisation, splitting of multiple choices, flagging of incomplete amounts; (3) a cleaning log (row, problem, decision, date, author); (4) a single clean table in table format, with a data dictionary; (5) an indicator calculation sheet (formulas or pivot table) with the parameters (planned amount, duration threshold) in named cells; (6) a presentation sheet (charts, slicers by household type, sex and area) with the sample size displayed. Monthly update with "Refresh All" and cross-check of a few figures.

Protection: names, phone numbers and GPS coordinates are kept separately in a protected file with restricted access, linked by the identifier; the dashboard contains only aggregated data; no sharing via instant messaging; a defined retention period.

To the donor: the aggregated indicators and, if needed, an anonymised database (without names, phone numbers or precise GPS; location at district level; small cells grouped to avoid re-identification), under a data sharing agreement. Not the raw database with personal data.