1d:I[3185,["500","static/chunks/500-70c2ecd352f54656.js","226","static/chunks/226-9d82ff6304c7ef58.js","111","static/chunks/111-9cd8cb69ad90f502.js","217","static/chunks/217-edaf61cdb9547def.js","953","static/chunks/app/blog/%5Bslug%5D/page-adb30015ff5a3ac5.js"],"ReadingProgress"] 1e:I[2736,["500","static/chunks/500-70c2ecd352f54656.js","226","static/chunks/226-9d82ff6304c7ef58.js","111","static/chunks/111-9cd8cb69ad90f502.js","217","static/chunks/217-edaf61cdb9547def.js","953","static/chunks/app/blog/%5Bslug%5D/page-adb30015ff5a3ac5.js"],"PyRuntimeProvider"] 1f:I[1330,["500","static/chunks/500-70c2ecd352f54656.js","226","static/chunks/226-9d82ff6304c7ef58.js","111","static/chunks/111-9cd8cb69ad90f502.js","217","static/chunks/217-edaf61cdb9547def.js","953","static/chunks/app/blog/%5Bslug%5D/page-adb30015ff5a3ac5.js"],"MarkdownContent"] 20:T1c7d,**Real-Time M-Pesa Analytics with Next.js and DuckDB** ====================================================== **Hook & Value Proposition** --------------------------- You're a data analyst tasked with optimizing M-Pesa's mobile money transactions for a major Kenyan operator. Every minute, 50,000+ transactions pour in. Your stakeholders need **instant insights** into transaction volumes, top merchants, and fraud indicators to inform real-time business decisions. Traditional batch-processing analytics are too slow. Here’s how to build a **fast, scalable, and interactive real-time M-Pesa analytics pipeline** using Next.js for the web frontend and DuckDB as the embedded analytics engine. **The Pipeline Overview** ------------------------ | Component | Technology | Role | |-------------------|-------------|-------------------------------------------------------| | **Data Ingestion** | Kafka | Capture M-Pesa Transaction Streams | | **Analytics Engine** | DuckDB (Embedded) | Real-Time Querying & Materialized Views | | **Web Application** | Next.js | Interactive, Scalable Dashboard | ### **Step 1: Ingesting M-Pesa Transactions with Apache Kafka** Assuming M-Pesa transactions are generated in JSON format, we'll use Apache Kafka for streaming. For brevity, we'll focus on the Next.js and DuckDB integration, but here's a high-level Kafka setup: * **Kafka Topic:** `mpesa_transactions` * **Message Format:** ```json { "transaction_id": "string", "amount": float, "merchant_id": "string", "timestamp": "ISO 8601" } ``` **Implemenation Detail: DuckDB Embedded for Real-Time Analytics** --------------------------------------------------------- ### **Why DuckDB?** * **Embedded**: No separate database server needed. * **Columnar Storage**: Efficient for analytical queries. * **Real-Time Capabilities**: Supports streaming data ingestion. #### **Setting Up DuckDB with Next.js** **1. Initialize Next.js Project & Install DuckDB** ```bash npx create-next-app mpesa-analytics cd mpesa-analytics npm install duckdb\n **2. Embed DuckDB in Next.js for Server-Side Rendering (SSR)** ```javascript // pages/_app.js (Excerpt) import type { AppProps } from 'next/app'; import db from '../lib/db'; // Initialize DuckDB connection here function MyApp({ Component, pageProps }: AppProps) { return ; } export default MyApp;\n **3. Ingest Kafka Streams into DuckDB (Simplified Example)** **Note:** For production, consider a more robust Kafka consumer library like `node-kafka`. Here, we simplify for focus on DuckDB integration. ```javascript // lib/db.js import { DuckDB } from 'duckdb'; let db; export function initDB() { if (!db) { db = new DuckDB(); db.query(` CREATE TABLE IF NOT EXISTS mpesa_transactions ( transaction_id TEXT PRIMARY KEY, amount REAL, merchant_id TEXT, timestamp TIMESTAMP ); `); // Simplified Kafka consumer for demo; use node-kafka for production const kafkaConsumer = /* Initialize Kafka Consumer */; kafkaConsumer.on('message', (message) => { const transaction = JSON.parse(message.value.toString()); db.query(` INSERT INTO mpesa_transactions VALUES ($1, $2, $3, $4); `, [transaction.transaction_id, transaction.amount, transaction merchant_id, transaction.timestamp]); }); } return db; } export default { initDB };\n #### **Real-Time Analytics Queries with DuckDB** **Merchant Transaction Volume (Last Hour)** ```sql -- pages/api/merchant-volumes.js (API Endpoint) import db from '../../lib/db'; export default async function handler(req, res) { const results = await db.query(" SELECT merchant_id, SUM(amount) as total_volume FROM mpesa_transactions WHERE timestamp > NOW() - INTERVAL 1 HOUR GROUP BY merchant_id ORDER BY total_volume DESC; "); res.json(results); }\n ### **Step 2: Building Interactive Dashboards with Next.js** #### **Example: Merchant Volumes Dashboard** ```jsx // pages/merchant-volumes.js import { useState, useEffect } from 'react'; import fetch from 'isomorphic-unfetch'; function MerchantVolumes() { const [volumes, setVolumes] = useState([]); useEffect(() => { async function fetchVolumes() { const response = await fetch('/api/merchant-volumes'); const data = await response.json(); setVolumes(data); } fetchVolumes(); // Refresh every 60 seconds for real-time updates const intervalId = setInterval(fetchVolumes, 60000); return () => clearInterval(intervalId); }, []); return (

Top Merchants by Transaction Volume (Last Hour)

{volumes.map((volume) => ( ))}

Merchant ID	Volume (KES)
{volume.merchant_id}	{volume.total_volume.toFixed(2)}

); } export default MerchantVolumes;\n **Why This Matters in Practice** ------------------------------- * **Faster Decision Making**: Stakeholders can react to transaction spikes or declines as they happen. * **Improved Resource Allocation**: Real-time merchant volume insights help in optimizing merchant services. * **Enhanced Fraud Detection**: Near-instant querying capabilities facilitate quicker identification of anomalous transactions. **Debugging Scenario: Handling Late-Arriving Data** ------------------------------------------------ * **Issue:** Transactions from a specific region are delayed, causing underreporting in real-time views. * **Solution:** 1. **Implement Time-Watermarking** in Kafka to handle late arrivals. 2. **Adjust DuckDB Queries** to account for a grace period (e.g., `WHERE timestamp > NOW() - INTERVAL 2 HOURS` for an hour’s grace). **Performance Tradeoff: Scalability vs. Query Latency** ------------------------------------------------------ * **DuckDB Embedded** offers low-latency queries but may face scalability limits with very high transaction volumes. * **Solution for Scalability:** * **Horizontal Scaling** with Next.js behind a load balancer. * **Consider DuckDB Cluster** for extremely large datasets, though it moves away from the embedded simplicity. **Concrete Next Step: Getting Started** -------------------------------------- 1. **Clone the Demo Repo** (Fictitious for this example, create your own based on the guide): ```bash git clone https://github.com/orandi/mpesa-analytics-demo.git ``` 2. **Replace the Simplified Kafka Consumer** with `node-kafka` for a production-ready setup. 3. **Deploy** to Vercel or similar for auto-scaling Next.js deployments. **Code Repository (Hypothetical - Recreate Based on This Guide)**\nmarkdown # mpesa-analytics-demo ## Setup - `npm install` - `npm run dev` ## Components - `/`: Overview Dashboard - `/merchant-volumes`: Top Merchants by Volume (Last Hour) - `/api/...`: Various Analytics Endpoints ## Next Steps - [ ] Implement Robust Kafka Consumer - [ ] Add Authentication for Dashboards 15:[["$","$L1d",null,{}],["$","div",null,{"className":"max-w-6xl mx-auto px-4 py-12","children":["$","div",null,{"className":"grid grid-cols-1 lg:grid-cols-[1fr_260px] gap-12","children":[["$","article",null,{"children":[["$","$L7",null,{"href":"/blog","className":"inline-flex items-center gap-1.5 text-sm text-muted hover:text-accent transition-colors mb-8","children":[["$","svg",null,{"ref":"$undefined","xmlns":"http://www.w3.org/2000/svg","width":14,"height":14,"viewBox":"0 0 24 24","fill":"none","stroke":"currentColor","strokeWidth":2,"strokeLinecap":"round","strokeLinejoin":"round","className":"lucide lucide-arrow-left","aria-hidden":"true","children":[["$","path","1l729n",{"d":"m12 19-7-7 7-7"}],["$","path","x3x0zl",{"d":"M19 12H5"}],"$undefined"]}]," Back to Blog"]}],["$","div",null,{"className":"flex flex-wrap gap-2 mb-4","children":[["$","span","Data Engineering",{"className":"tag","children":"Data Engineering"}],["$","span","Real-Time Analytics",{"className":"tag","children":"Real-Time Analytics"}],["$","span","Next.js",{"className":"tag","children":"Next.js"}],["$","span","DuckDB",{"className":"tag","children":"DuckDB"}]]}],["$","h1",null,{"className":"text-3xl md:text-5xl font-bold leading-tight mb-6","children":"Real-Time M-Pesa Analytics with Next.js and DuckDB"}],null,["$","div",null,{"className":"flex flex-wrap items-center gap-6 text-sm text-muted border-b border-border pb-8 mb-8","children":[["$","span",null,{"className":"flex items-center gap-1.5","children":[["$","svg",null,{"ref":"$undefined","xmlns":"http://www.w3.org/2000/svg","width":13,"height":13,"viewBox":"0 0 24 24","fill":"none","stroke":"currentColor","strokeWidth":2,"strokeLinecap":"round","strokeLinejoin":"round","className":"lucide lucide-calendar","aria-hidden":"true","children":[["$","path","1cmpym",{"d":"M8 2v4"}],["$","path","4m81vk",{"d":"M16 2v4"}],["$","rect","1hopcy",{"width":"18","height":"18","x":"3","y":"4","rx":"2"}],["$","path","8toen8",{"d":"M3 10h18"}],"$undefined"]}],["$","time",null,{"dateTime":"2026-04-14","children":"April 14, 2026"}]]}],["$","span",null,{"className":"flex items-center gap-1.5","children":[["$","svg",null,{"ref":"$undefined","xmlns":"http://www.w3.org/2000/svg","width":13,"height":13,"viewBox":"0 0 24 24","fill":"none","stroke":"currentColor","strokeWidth":2,"strokeLinecap":"round","strokeLinejoin":"round","className":"lucide lucide-clock","aria-hidden":"true","children":[["$","circle","1mglay",{"cx":"12","cy":"12","r":"10"}],["$","polyline","68esgv",{"points":"12 6 12 12 16 14"}],"$undefined"]}],5," min read · ","840"," words"]}],["$","span",null,{"className":"ml-auto font-medium text-foreground/70","children":["By ","Orandi Felix"]}]]}],null,["$","div",null,{"className":"prose max-w-none prose-headings:scroll-mt-24","children":["$","$L1e",null,{"children":["$","$L1f",null,{"markdown":"$20"}]}]}],"$L21"]}],"$L22"]}]}]]