A Decade of UPI: Transaction Growth Analytics with SQL and Python

A Decade of UPI is a compact, beginner-level analytics study built on one clean, real, verifiable Indian dataset: the Unified Payments Interface product statistics published every month by the National Payments Corporation of India (NPCI) — monthly transaction volume (in millions) and value (in ₹ crore) since launch.

UPI is the payments story of the decade: from about a lakh transactions a month at launch to billions now, it has overtaken every other retail instrument in India. The raw numbers are public and trustworthy, but the analysis — how fast did it grow, when, and what patterns repeat every year? — is what this project answers.

The workflow is deliberately small enough for a first project yet honest enough to defend in a viva: the monthly tables are saved as CSVs, cleaned with pandas (headers, placeholders, unit columns), loaded into MySQL, and then interrogated two ways. SQL computes the structural metrics — year-over-year and month-over-month growth, compound annual growth rate, records by value and volume, average ticket size. Python (pandas, Matplotlib) does exploratory data analysis: growth curves on a log scale, festival-month seasonality, the volume–value divergence as small-ticket payments exploded. A simple linear-regression trend forecast with a held-out test set closes the study. Every chart in the report traces to a query or notebook cell, and every number can be re-derived from the public source in minutes — that reproducibility is the point of the project. Three members split the work: data and database, SQL metrics, and visualisation with forecast.

UPI's growth is quoted everywhere but analysed almost nowhere: claims float around social media and news tickers with cherry-picked start and end points, mixed units, and no way to verify them. Students and researchers who want to understand the real growth pattern — how fast volumes actually doubled, which months consistently spike, how average ticket size fell as street-vendor payments grew — have no clean, reproducible starting point.

There is a need for a small, rigorous analytics study that takes one authoritative public dataset (NPCI's monthly UPI statistics), stores it in a proper database rather than loose spreadsheets, derives trustworthy growth and seasonality metrics with SQL, visualises them honestly with Python, and reports a trend forecast whose accuracy is measured — so that any claim made in the report can be reproduced from the source data in minutes.

Abstract

This project studies the growth of UPI, India's instant retail payment system, using a single authoritative dataset: NPCI's monthly UPI product statistics. The monthly volume and value series are cleaned, loaded into MySQL, and analysed with SQL queries and Python-based exploratory data analysis. The study quantifies growth (CAGR, YoY, doubling time), identifies seasonal patterns around festivals and financial-year end, examines the divergence between transaction volume and value as small-ticket payments grew, and produces a simple, honestly evaluated trend forecast. The output is a reproducible analytics report: database, notebooks, charts and a findings document that anyone can re-run from the public source.

Introduction

When UPI launched in 2016, cash and cards dominated Indian retail payments. Within a few years, UPI monthly volumes crossed every other instrument, driven by QR codes at street vendors, zero-cost P2P transfers and smartphone growth. NPCI publishes the monthly totals — volume in millions, value in ₹ crore — on its product statistics page. That series is a gift for a first data-analytics project: it is real, national in scale, small enough to understand completely, and rich enough to demonstrate every core skill from the Anna University R2021 labs — SQL querying (CS3481) and pandas-based EDA with Matplotlib (CS3361).

Existing System

• News articles quote one or two headline numbers each month; the underlying series is rarely examined.
• Social-media charts cherry-pick start and end months, ignoring seasonality and scale.
• Common analyses are not reproducible: no database, no queries, no way to check a claim.
• Limitations: no structured storage, no documented cleaning steps, no honest forecast evaluation — just screenshots of charts of unknown origin.

Proposed System

• A small, well-documented database (upi_monthly, plus a derived fy_summary) as the single source of truth for the study.
• A SQL metrics layer: month-over-month and year-over-year growth, CAGR per financial year, records (peak months), average ticket size, best/worst growth spells.
• A Python EDA layer: trend and log-scale growth charts, seasonality plots by month of year, volume-vs-value divergence, annotated events (demonetisation aftermath, COVID dip and surge, festival quarters).
• A forecast module: linear regression on the log-scale volume trend, tested on held-out recent months with reported MAE, and compared against a naive baseline.
• A findings report where every figure cites the query or notebook cell that produced it.

Feasibility Study

• Technical: only Python, pandas, Matplotlib and MySQL — exactly the Anna R2021 toolset (CS3361, CS3481). The dataset is a few hundred rows; any laptop suffices. The bundle includes the extracted CSVs, so the study runs offline.
• Economic: ₹0. All tools are free; the data is public.
• Operational: a single loader script and numbered notebooks make the study repeatable by any junior; updating it for a new month is one file and one rerun.

In scope

• NPCI monthly UPI product statistics: volume (millions) and value (₹ crore), from launch month to the month the study is downloaded.
• Cleaning, database loading, SQL metric layer, Python EDA, one simple forecast model, findings report with reproducible figures.
• Derived metrics: average ticket size, growth spells, festival-quarter comparison.

Out of scope (v1)

• Bank-wise or app-wise UPI market share (published separately by NPCI) — kept as a future extension.
• Other NPCI products (FASTag, AePS, RuPay) except one comparative glance in the conclusion.
• Causal modelling, machine learning beyond linear regression, and any data behind NPCI's login/paywall (there is none — this study is fully public).
• Mobile app or web development; the deliverables are the database, notebooks and report.

The study follows a lightweight iterative analytics lifecycle (plan → acquire → prepare → analyse → communicate), run in three short cycles so a working draft of the findings exists from week 5 onward. The team of three splits the layers but reviews every cycle together.

Validation approach: every derived total is cross-checked against NPCI's own published monthly statements (e.g. peak-month records quoted in press releases); loader sanity checks assert positive volumes, monotone months, and row counts equal to months elapsed; the forecast is never presented without its held-out MAE.

This is an analytics study, so the architecture is a small data flow rather than a software system.

1. Source — NPCI's UPI product statistics page (monthly volume/value tables), saved as CSV extracts so the study is reproducible offline.
2. Storage — a MySQL schema with one fact table (upi_monthly: month, volume_mn, value_cr, derived avg_ticket_size) and one aggregated table (fy_summary: financial-year totals and growth).
3. Analysis — SQL scripts for the metric layer, Jupyter notebooks for EDA and the forecast; figures are generated by code, never drawn by hand.
4. Communication — the findings report and the CS3811 project documentation.

flowchart LR
  A["NPCI UPI product statistics<br/>(monthly volume & value tables)"] --> B["Extraction<br/>save monthly tables as CSV"]
  B --> C["Cleaning & loading<br/>pandas checks → MySQL `upi_monthly`"]
  C --> D["SQL metrics layer<br/>YoY · MoM · CAGR · records · ticket size"]
  C --> E["Python EDA<br/>log growth · seasonality · divergence"]
  D --> F["Findings report<br/>every figure cites its query"]
  E --> F
  E --> G["Trend forecast<br/>held-out MAE vs naive baseline"]

Study database

erDiagram
  UPI_MONTHLY {
    date month PK
    decimal volume_mn
    decimal value_cr
    decimal avg_ticket_size
  }
  FY_SUMMARY {
    int fin_year PK
    decimal annual_volume_mn
    decimal annual_value_cr
    decimal yoy_growth_pct
  }
  UPI_MONTHLY ||--o{ FY_SUMMARY : "aggregates into"

avg_ticket_size is derived in the loader as value × 10 / volume (₹ per transaction), and fy_summary is recomputed from upi_monthly by a view-like SQL script so no number can drift between tables.

• Bank-wise market share — NPCI publishes per-bank UPI volumes; a second table turns this into a concentration study.
• Cross-product comparison — add FASTag and AePS monthly statistics for a payments-landscape dashboard.
• Better forecasting — seasonal-naive and Holt-Winters models compared against the linear baseline.
• Automation — a monthly refresh script that appends the latest NPCI figures and regenerates every figure automatically.
• Public write-up — publish the findings as a short blog post with reproducible notebooks linked.