Data: from information needs to a clean database

1The information management cycle

1. Information needs: what decisions need to be made, by whom, and what information informs them?
2. Collection: secondary data first (what already exists), then primary data if necessary.
3. Processing: data entry, cleaning, structuring, secure storage.
4. Analysis: trends, comparisons, gaps, severity.
5. Dissemination: dashboards, maps, 4W, briefing notes, to the right audience at the right time.
6. Feedback: are the products used? Adjust accordingly.

The first reflex: do not collect what you will not use. Every question takes up respondents' time and creates a risk for their data.

2Reference data

• P-codes: unique codes for each administrative unit (region, district, municipality) and sometimes for localities. They prevent spelling errors and make it possible to link databases together.
• Common operational datasets (CODs): administrative boundaries and reference population figures validated for the country, published in particular on HDX (Humanitarian Data Exchange, OCHA's platform).
• Data standards: for example HXL hashtags (Humanitarian Exchange Language) added below table headers to make sharing easier.

Always use the same codes and the same version of the boundaries as the cluster and OCHA; otherwise the data cannot be combined.

3Mobile data collection and quality

• Common tools: KoboToolbox and ODK (phone-based forms that work offline), with input constraints (lists, ranges, required fields) that prevent errors at source.
• Test the form before going to the field, train enumerators, plan for translation of the questions.
• Informed consent at the start of the questionnaire, and GPS coordinates only if they are necessary.

4Cleaning data

Before any analysis: a cleaning log that keeps track of every change (never a silent correction in the original database).

• Duplicates (the same household registered twice).
• Outliers (a household of 45 people, an age of 150).
• Inconsistencies (a married 3-year-old child, expenditure higher than declared income without explanation).
• Interviews that are too short or conducted outside the planned hours.
• Place names harmonised with P-codes.

5Start from decisions: the information needs matrix and the secondary data review

Most failures in information management do not stem from a poor tool, but from data collection launched without knowing which decision it is meant to inform. The outcome is familiar: a 180-question questionnaire, a dataset nobody ever analyses in full, and a report published too late to influence planning. The method below, used by cluster information management teams and large NGOs, ties every piece of data back to a decision.

Step 1: list the decisions (day 1, 2 to 3 hours)

The information management officer (IMO) brings together, or interviews separately, the people who make decisions: cluster coordinator or head of mission, programme managers, logistician, security manager, sometimes the donor. Each is asked three questions: what decision do you need to make in the next three months? By what date? What are you missing to make it? Typical answers include: where to preposition stocks, which municipalities to prioritise, what transfer value to set, which modality to choose, where to open a mobile clinic.

Step 2: build the information needs matrix

Each decision is broken down into analysis questions, then into indicators, then into sources. The matrix becomes the contract between the information management team and the users: anything not in it is not collected.

Relevance test: for every question in the future questionnaire, the IMO must be able to point to the matrix row it feeds. A question that matches no row is deleted, however insistent its author.

Step 3: the secondary data review (SDR)

Before any primary data collection, what already exists is gathered and analysed. In a sudden-onset emergency, the IASC Multi-Cluster/Sector Initial Rapid Assessment (MIRA) approach provides for an initial analysis based mostly on secondary data within the first 72 hours, followed by a consolidated report within two weeks. In a protracted crisis, the secondary data review precedes each planning cycle and feeds into the Humanitarian Needs Overview.

1. Define the analytical framework: take the questions from the matrix and organise them by sector and by area. This framework will serve as the classification grid.
2. Search systematically: HDX, ReliefWeb, cluster reports, IPC or Cadre Harmonisé analyses, SMART nutrition surveys, IOM mobility tracking (DTM), multi-sector needs assessments (MSNA), government ministry data, censuses, verified local press reports.
3. Extract and classify: each piece of information is entered into a table with its source, date of collection (not of publication), area, method and a reliability rating.
4. Assess reliability: known source and published methodology, representative sample or not, recent date or not, consistency with other sources.
5. Identify the gaps: the empty cells in the analytical framework justify primary data collection, and only those cells do.

Who does what

• IMO: facilitates the matrix, conducts the secondary data review, proposes sources and methods.
• Sector leads: validate the indicators and technical thresholds (for example 15 litres per person per day as the Sphere benchmark for water in emergencies).
• Decision-makers: validate the matrix and commit to the dates on which the products will be used.
• Data protection officer or focal point: checks that the personal data planned for collection are necessary.

6Reference data in practice: P-codes, CODs and lookup tables

Reference data are what make it possible to combine an NGO's dataset, the cluster 4W, the baseline population and a map. Ignoring them means producing figures that add up with nothing. The IASC Guidelines on Common Operational Datasets (CODs) distinguish two core families and a set of complementary datasets.

Common operational datasets

How a P-code is built

A P-code is a hierarchical identifier: the code of a municipality contains the code of its district, which contains the code of its region. In the fictional country of Valdorie, the Haut-Lisse region has the code VD03, the Morane district VD0302 and the municipality of Kessa VD030205. This structure makes it possible to move up a level by simple truncation, and to check automatically that a municipality really belongs to the district reported.

A place name is never an identifier. The same municipality may be spelt Kessa, Késsa, Kessah or Kesa depending on the enumerator, and two municipalities in different regions may share the same name. Only the P-code links records unambiguously.

Working procedure with reference data

1. Download the current version from HDX and record its version identifier and date in the project data dictionary.
2. Check alignment with the cluster: the information management working group (IMWG) led by OCHA indicates the official version. An NGO using an earlier version will see its municipalities “disappear” during consolidation.
3. Build the choice list for the data collection form directly from the COD file, as a cascade (region, then district filtered by region, then municipality filtered by district), with the P-code as the stored value and the name as the displayed label.
4. Create a lookup table for historical or external data: name as written in the source, P-code assigned, assignment method, person who validated it.
5. Manage changes: when the government creates a new municipality, the lookup table between the old and new boundaries is kept so that time series remain comparable.

Example of a lookup table

HXL hashtags in practice

The Humanitarian Exchange Language (HXL) adds a row of hashtags beneath the column headers of a table. Headers remain free (in French, in Arabic, abbreviated), but the hashtags are standard, which allows tools to recognise columns automatically. Common examples: #adm1+name and #adm1+code for the region, #adm2+code for the district, #org for the organisation, #sector for the sector, #activity for the activity, #targeted and #reached for people targeted and reached, with attributes such as +f, +m, +children or +adults for disaggregation, and #date for the date. An HXL-tagged 4W can be merged with the COD population in a few minutes to calculate coverage.

Baseline population and denominators

The denominator is the most frequent source of error in humanitarian products. The COD-PS gives a projected resident population, often based on an old census; it does not always include displaced people. For a coverage rate, you must state whether the denominator is the total population, the people in need (the response plan figure) or the targeted population. A coverage rate of 120% almost always signals an unsuitable denominator or double counting, rarely a success.

7Designing, testing and deploying a mobile data collection form

KoboToolbox and ODK rely on the same format, XLSForm: a workbook with a survey sheet (the questions), a choices sheet (the answer lists) and a settings sheet (title, form ID, version). Mastering XLSForm directly, rather than the graphical form builder, makes it possible to build robust, version-controlled forms that can be peer-reviewed.

The columns that make the difference in quality

Metadata to enable systematically

• start and end: start and end time, essential for checking interview duration.
• today, deviceid and an enumerator ID chosen from a list (never typed freely).
• audit: a log file that records the time spent on each question; it makes it possible to spot questions skipped in a few seconds or fabricated interviews.
• A consent field at the start, with the form ending if the person refuses.

Sample size: the calculation you must be able to redo

To estimate a proportion in a population, the basic formula is n = z² × p × (1 − p) / e², where z is 1.96 for a 95% confidence level, p is the expected proportion (0.5 if unknown, which maximises n) and e is the margin of error. With 95% confidence and a 5% margin, n = 384. With a 10% margin, n = 96. You then apply:

• the finite population correction if the population is small: corrected n = n / (1 + (n − 1) / N). For a site of 1,200 households, 384 becomes 291;
• the design effect for cluster sampling (often 1.5 to 2): n is multiplied by it;
• a buffer for non-response (often 5 to 10%);
• the calculation by stratum: if you want representative results by municipality and by status (displaced, host), each stratum needs its own sample size. This is what makes budgets explode: you must decide with users which levels of disaggregation are really needed.

Four-stage testing protocol

1. Technical review by a second IMO: variable names, constraints, skip logic, lists, translations. Allow 2 to 4 hours for a 60-question form.
2. Desk test: fill in the form 20 to 30 times with extreme scenarios (one-person household, 18-person household, refusal of consent, person with a disability), then download the data and check that the dataset comes out in the expected structure.
3. Field pilot: 10 to 20 real interviews outside the sample, conducted by the enumerators themselves. Actual duration is measured, and poorly understood questions and missing categories (a frequently recurring “other”) are noted.
4. Version freeze: the form is published with a version number; any later change is logged and communicated, because it changes the structure of the dataset.

Enumerator training: minimum content

Two to three days for a multi-sector survey, one day for simple monitoring. The programme covers the objective of the survey and how the data will be used, a question-by-question reading with the definition of each term (what is a household? who is the head of household?), interviewing technique and consent, protection principles (do not ask about violence without specific training, know how to refer to a service), handling the phone and submitting data, and a day of practice in pairs with feedback. A written test at the end of training makes it possible to retain only the enumerators who have mastered the definitions.

An extra question is never free: over 800 interviews, one more minute represents more than 13 hours of fieldwork, and every minute beyond 45 to 60 minutes degrades the quality of answers.

8Full example: a multi-sector assessment from raw dataset to clean dataset

Fictional context: following clashes, some 9,400 displaced households have settled in three municipalities of the Morane district (Valdorie): Kessa, Lounda and Tarbé, in sites and with host families. The cluster requests a multi-sector assessment for quarterly planning. The fictional NGO “Solidarité Haut-Lisse” is in charge of data collection, with 16 enumerators, 2 supervisors and one IMO.

1. Matrix and sample

The matrix retains 41 indicators (compared with 112 in the first version proposed by the sectors). Users ask for results by municipality and by status (displaced in sites, displaced with host families, host population), i.e. 9 strata. With 95% confidence and a 10% margin, 96 interviews are needed per stratum, rounded up to 100 with the non-response buffer, i.e. 900 interviews. For lack of budget, the host population of Tarbé, little affected according to the secondary data review, is covered by a single stratum shared with Lounda: 8 strata, 800 interviews targeted.

2. Data collection

Average duration measured during the pilot: 38 minutes. Each pair conducts 8 to 10 interviews a day; data collection lasts 6 days. Data are submitted every evening; the IMO runs the daily checks before 9 a.m. and sends the supervisors the list of interviews to verify, which they handle the same day while the teams are still in the area.

3. Results of the checks

4. Extract from the cleaning log

5. Review and decisions

Of the 836 interviews received (the teams slightly exceeded the target), 36 are deleted (24 for duration, 9 duplicate submissions, 3 probable duplicates): the clean dataset contains 800 interviews. The stratum “displaced with host families, Lounda” reaches only 87 interviews after cleaning: its results will be published with a wider margin of error, stated explicitly. The enumerator responsible for the 19 interviews that were too short is removed from the team; his 41 other interviews are all checked through phone call-backs to a sample of 10, of which 8 are confirmed.

The dataset is delivered as three files: the raw dataset (never modified, restricted access), the clean dataset (without names or phone numbers, P-codes verified) and the cleaning log, accompanied by a data dictionary and a one-page methodological note setting out the limitations (inaccessible areas in Tarbé-Est, probable under-reporting of income).

9Structuring and documenting the dataset: rules, data dictionary and daily checks

A clean dataset is not just a dataset without errors: it is one that another person can pick up, understand and reanalyse six months later without calling its author. Evaluators and auditors check precisely this reproducibility.

Structural rules

• One row per unit of observation (household, individual, site, distribution), one column per variable, one value per cell. Individual data on household members are kept in a separate table linked by the household ID.
• Unique and stable identifiers, generated by the tool (UUID), never built from a name.
• No merged cells, no colours carrying information and no totals in the dataset: calculations are done in a separate analysis sheet or script.
• Consistent codes for missing values: distinguish “don't know”, “refused to answer” and “not applicable”, which do not have the same analytical meaning.
• Dates in ISO format (year-month-day), which sort correctly and avoid day and month being swapped between software packages.

The data dictionary

The daily checks table

During data collection, the IMO produces a table by enumerator every morning. It is the most effective tool for detecting fraud or misunderstanding before it contaminates the whole dataset.

The percentage of “no” answers to filter questions is a classic indicator: an enumerator who answers “no” to avoid the follow-up questions shortens the interview. Abnormal proximity between interviews may reveal interviews filled in at the same location.

The validation circuit

1. The IMO detects and lists suspicious cases (morning).
2. The supervisor verifies in the field or by phone (during the day).
3. The IMO applies the decision and records it in the log (evening).
4. At the end of data collection, the MEAL coordinator or assessment manager validates the log and the deletions, and signs off the methodological note.

Separation rule: the raw dataset is never modified. Every transformation is done on a copy or by script, and every transformation is documented. This is the condition for an auditor to be able to retrace the path from the data collected to the figure published.

10Common mistakes, the donor perspective and adapting to the context

The most common mistakes in the field

What donors, evaluators and auditors look at

• Traceability: can each reported figure (people reached, survey results) be linked to a dataset and a method? On-site monitoring visits by donors such as ECHO, or auditors commissioned by BHA, routinely request the source lists and datasets for a sample of indicators.
• Consistency between the narrative report, the indicator tracking table and the data submitted to the cluster.
• The promised disaggregation: if the proposal announces data by sex, age and disability, the form must collect them, for example with the Washington Group Short Set of Questions on Disability.
• Data protection: legal basis or consent, restricted access, retention period. Recent funding agreements include data protection clauses that the organisation must be able to demonstrate compliance with.
• Methodology: justified sample size, stated limitations, number of interviews deleted and the reasons.

Adapting to the context

In an emergency, approximate data available at the right time are worth more than perfect data available too late, provided their level of reliability is clearly stated.

11Interview questions and key references

Interview questions

1. “A coordinator asks you to add 40 questions to an assessment that is already finalised. What do you do?”

A good answer does not refuse on principle: it goes back to the information needs matrix, asks which decision each question is meant to inform, shows the impact on duration and quality (one minute per interview multiplied by the sample size), and proposes alternatives: secondary data, a targeted module on a sub-sample, later data collection. It mentions validation by the person who makes the decision.

2. “How do you calculate the sample size for a household survey?”

The candidate cites the formula, the parameters (confidence level, margin of error, expected proportion), the finite population correction, the design effect for cluster sampling, the non-response buffer and, above all, the question of strata: the size is calculated for each level at which representative results are wanted.

3. “You notice that one enumerator is producing 12-minute interviews when the average is 40. How do you respond?”

A good answer describes a fact-based approach: audit file, comparison of quality indicators, verification of a sample by call-back or visit, discussion with the enumerator, documented decision (training, removal, deletion of interviews), and reflection on the cause (pressure on the number of interviews per day, piece-rate pay).

4. “Why use P-codes rather than place names?”

Uniqueness, hierarchy, interoperability with the 4W, the baseline population and maps; a good answer mentions version management of administrative boundaries and the lookup table.

5. “Explain the difference between a raw dataset, a clean dataset and a cleaning log.”

A good answer explains separation, reproducibility, differentiated access rights (the raw dataset often contains personal data) and the value of the log for auditors and for subsequent analyses.

6. “What personal data would you collect for post-distribution monitoring?”

The minimum necessary: a household ID that links back to the distribution list, possibly a phone number for verification, deleted afterwards; no sensitive data without justification. A good answer mentions consent and the retention period.

Key references

• Operational Guidance for Coordinated Assessments in Humanitarian Crises and the MIRA guidance: IASC.
• IASC Guidelines on Common Operational Datasets in Disaster Preparedness and Response: IASC, with the COD datasets published by OCHA on HDX.
• Documentation of the HXL (Humanitarian Exchange Language) standard and of the HXL Proxy tool.
• XLSForm documentation and the KoboToolbox and ODK user guides.
• Joint Intersectoral Analysis Framework (JIAF): IASC global working group on joint analysis.
• Methodologies and data cleaning guidance for multi-sector needs assessments (MSNA): REACH and IMPACT Initiatives.
• ACAPS guidance on secondary data review and on source assessment.
• Washington Group Short Set of Questions on Disability, from the Washington Group on Disability Statistics.
• Sphere Handbook (2018) for the sector-specific technical thresholds used in indicators.