Maji Ndogo: water access, queues and safety

A multi-page Power BI report on the water survey of Maji Ndogo, built for ALX. It joins several tables into one data model, then looks at water sources, queue times, pollution, crime around water points, and where the improvement budget is going.

Decision-makers in a water programme need to know where to send limited money and effort. That means knowing who relies on poor sources, where people queue longest, and where collecting water puts people at risk.

• Combine the survey tables into one reliable data model.
• Show the national picture: population, water sources and who they serve.
• Measure queue times for shared taps by day, hour and province.
• Show how crime near water points affects women, men and children.
• Compare the improvement plan and budget of each province.

An Excel workbook of survey tables: water sources and visits, locations, pollution results, queue data, water-source-related crime, and project progress with budgets. The data was cleaned earlier in SQL, then modelled in Power BI.

• Modelling: I linked the crime table to the visits through the location table, which holds one row per location. Linking it to visits directly gave a many-to-many relationship, which makes filters unreliable.
• I kept filters single-direction on one-to-many relationships, and checked column types in the data model before drawing anything.
• National page: provinces, the urban and rural split, people served by source type, and the count of sources by town. Rural is a town in this data, so I removed it where it would distort a town comparison.
• Queue page: shared taps only, so queue time compares like with like. Average queue time by hour and day, queue composition, and total time queued by province.
• Crime page: victims by gender, by hour and weekday, and by province, to see who is most exposed and when.
• Pollution page: the count of test results on a map by province, and a composition chart of clean, biologically contaminated and chemically contaminated results.
• Province pages: one page per province with its budget split by improvement type and by rural and urban spending.

A ten-page interactive Power BI report: a summary page, the national, queue, pollution and crime pages, and a page for each of the five provinces (Akatsi, Amanzi, Hawassa, Kilimani and Sokoto). Province buttons on the summary page let you switch the whole view.

• About 64% of the 28 million people in the survey live in rural areas (18 million, against 10 million in urban areas). Plans built around towns miss most of the population.
• Shared taps serve the largest number of people of any source type, and wells are the most numerous source (10,900 of them). Both depend on the water point staying open, working and safe to reach.
• Only about 28% of the 17,383 well test results are clean (4,916). The rest are contaminated: 7,093 chemically (40.8%) and 5,374 biologically (30.9%). Chemical contamination is the larger problem, about a third more common than biological.
• Testing is uneven across provinces, and the water differs a lot between them. Akatsi has the most test results (4,921): about 44% are clean (about 2,160), about 42% are chemically contaminated (about 2,060) and only about 14% are biologically contaminated (about 700). Sokoto has the fewest results (3,096) and the worst mix: only about 23% clean, about 42% chemical and about 35% biological.
• Akatsi's pollution is mostly chemical, and its plan matches it. It has about 2,060 chemically contaminated results and plans 2,056 RO filter installations, the treatment suited to chemical contamination.
• Queues are long and uneven. Saturday has by far the longest average queue, at around 250 minutes, against about 80 on Sunday. Kilimani has the most total time lost to queuing and Hawassa the least.
• Women and girls carry most of the burden. They make up about two thirds (65.8%) of the people queuing, and 71.7% of crime victims in the gender split are women, roughly two and a half times as many as men.
• Crime against women peaks on Fridays and rises through the evening to a high near 10pm, with a second smaller rise before dawn. Early morning and night are the risky hours, and they are also when many people collect water.
• Kilimani and Akatsi have the most crimes against women. Amanzi is the exception, where men are a much larger share of victims. Amanzi is also the most urban province (about 39% of its people are rural, against about 77% in Akatsi), which may mean shorter and safer trips to a water point. That is a lead to check, not a proven cause.
• The improvement budget totals about $146.7 million for 25,398 improvements. Budget per person varies a lot: about $6.95 in Sokoto, $5.96 in Kilimani, $5.87 in Hawassa, $5.23 in Akatsi and $2.47 in Amanzi.
• Plans differ by province. Akatsi puts the most into RO filters (2,056), consistent with the chemical contamination the project brief reports there. Amanzi's plan is mostly infrastructure repair (2,048), the cheapest improvement type. Kilimani spends 72% of its $39.2 million in rural areas.
• Sokoto and Hawassa take opposite routes. Sokoto, with the highest budget ($40.2 million, 80% of it rural), puts almost half (49.3%, $19.8 million) into drilling 1,709 wells, and adds 2,383 filter installations. Hawassa ($22.6 million, also about 80% rural) drills only 210 wells and puts most of its money into filters: 2,774 installations, about 63% of its improvements.

• Start with safety around water points in Kilimani and Akatsi: lighting, patrols and community watch at dawn and in the evening, and especially on Fridays.
• Bring water closer to rural homes. More taps and wells within a short walk cuts both queue time and exposure to danger, and about 64% of people are rural.
• Target shared-tap queues at the worst times. Add capacity or opening hours on Saturdays, and start in Kilimani, where the total queue time is largest.
• Review how budget is shared. Sokoto gets the most per person and Amanzi the least. Check whether the gap reflects real need before the next round.
• Match filtration to the contamination. Chemical contamination is the bigger problem (40.8% of results), and RO filters are the improvement used most often (about 7,100). Make sure the filter type matches what each source is actually contaminated with, and retest sources after treatment.
• In Sokoto, test the water from new wells. Only about 23% of its results are clean, so a new well helps only if the water is safe. Pair the drilling with testing and treatment.
• Fix what is broken first. Repairs are the cheapest improvement type, so they are a fast way to restore access.
• Study Amanzi. If its lower risk for women comes from how water is collected there, other provinces could copy it.

Modelling comes first. Choosing which table to connect to, so that relationships stay one-to-many, is what made the later visuals trustworthy. I also learned to say what a chart does not prove: a pattern points to a question worth asking, not an answer.