Prepaid pack mix: compare packs by validity-normalised revenue

The pack catalogue and the promotion

All prices below are hypothetical MRPs, not any operator's tariff, and the ledger is synthetic. The circle sells four packs. In September a campaign nudged customers from the 28-day pack toward the 84-day and 365-day packs. The same 600 recharges happen in both months; only the mix changes.

-- Hypothetical pack catalogue (MRP, GST-inclusive) and a synthetic recharge ledger
CREATE TABLE packs (pack TEXT PRIMARY KEY, validity_days INTEGER, mrp REAL);
INSERT INTO packs VALUES ('P28', 28, 299), ('P56', 56, 579), ('P84', 84, 859), ('P365', 365, 3599);

CREATE TABLE plan_mix (month TEXT, pack TEXT, recharges INTEGER);
INSERT INTO plan_mix VALUES
 ('2026-08','P28',420),('2026-08','P56',90),('2026-08','P84',60),('2026-08','P365',30),
 ('2026-09','P28',300),('2026-09','P56',80),('2026-09','P84',150),('2026-09','P365',70);

-- expand to one row per recharge, spread across the first 28 days of the month
CREATE TABLE recharges (recharge_id INTEGER PRIMARY KEY, pack TEXT, recharge_date TEXT);
WITH RECURSIVE k(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM k WHERE i<500)
INSERT INTO recharges (pack, recharge_date)
SELECT m.pack, date(m.month || '-01', '+' || ((k.i * 7) % 28) || ' days')
FROM plan_mix m JOIN k ON k.i <= m.recharges;

Prepaid MRPs include GST. Everything below works on MRP so that the arithmetic is easy to follow. Before a finance review, convert to net revenue using the tax rate finance applies, and say in the footnote which basis you used.

What the recharge ledger shows

SELECT substr(r.recharge_date, 1, 7) AS month, r.pack,
       COUNT(*) AS recharges,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY substr(r.recharge_date, 1, 7)), 1) AS share_pct,
       SUM(p.mrp) AS recharge_value,
       ROUND(p.mrp * 30.0 / p.validity_days, 2) AS value_per_30_days
FROM recharges r JOIN packs p USING (pack)
GROUP BY month, r.pack ORDER BY month, p.validity_days;

SELECT substr(r.recharge_date, 1, 7) AS month,
       COUNT(*) AS recharges,
       SUM(p.mrp) AS recharge_value,
       SUM(p.validity_days) AS validity_days_sold,
       ROUND(SUM(p.mrp) * 30.0 / SUM(p.validity_days), 2) AS value_per_30_validity_days
FROM recharges r JOIN packs p USING (pack)
GROUP BY month;

The month totals from the second query:

On the recharge ledger, September looks like a 53.3% jump. On a per-day basis it is a 1.5% price cut: the circle sold 55.6% more days of service, at ₹303.82 per 30 days instead of ₹308.51, because longer packs are cheaper per day. Both statements are true. Only the second tells the pricing team what the promotion did to yield.

Spread each recharge over the days it pays for

A 365-day recharge on 22 September pays for nine days of September and 356 days after it. Assign value to service months in proportion to validity days:

-- spread each recharge evenly over its validity; keep the days that fall in each service month
WITH RECURSIVE months(m_start) AS (
  SELECT '2026-08-01' UNION ALL
  SELECT date(m_start, '+1 month') FROM months WHERE m_start < '2027-09-01'),
spans AS (
  SELECT r.recharge_id, substr(r.recharge_date, 1, 7) AS recharge_month, p.mrp, p.validity_days,
         r.recharge_date AS v_start,
         date(r.recharge_date, '+' || (p.validity_days - 1) || ' days') AS v_end
  FROM recharges r JOIN packs p USING (pack)),
alloc AS (
  SELECT s.recharge_month, substr(mo.m_start, 1, 7) AS service_month,
         s.mrp * (julianday(MIN(s.v_end, date(mo.m_start, '+1 month', '-1 day')))
                  - julianday(MAX(s.v_start, mo.m_start)) + 1) / s.validity_days AS value
  FROM spans s JOIN months mo
    ON s.v_start <= date(mo.m_start, '+1 month', '-1 day') AND s.v_end >= mo.m_start)
SELECT recharge_month,
       ROUND(SUM(value), 0) AS total,
       ROUND(SUM(CASE WHEN service_month = recharge_month THEN value END), 0) AS same_month,
       ROUND(SUM(CASE WHEN service_month > recharge_month THEN value END), 0) AS later_months,
       ROUND(100.0 * SUM(CASE WHEN service_month = recharge_month THEN value END) / SUM(value), 1) AS same_month_pct
FROM alloc GROUP BY recharge_month;

September's recharges earned ₹1,20,368 within September, less than August's recharges earned within August (₹1,26,297). The extra ₹1,79,600 of recharge value is mostly customers prepaying 2027. Straight-line spreading over validity is a simplification that ignores how customers use data within a pack. Your finance team's revenue-recognition policy decides the real treatment, so match their method before your numbers meet theirs.

The synthetic ledger starts in August, so it contains nothing recharged earlier. A real September also earns value from annual packs bought in the previous eleven months. Load at least a year of history before reporting service-period value by month.

Before you call a pack promotion a success

• Compare value per 30 validity days before and after, pack by pack and for the whole mix. A mix shift toward cheaper-per-day packs is a price cut even when no tariff changed.
• Report the same-month earned value next to recharge value in every monthly review, so a prepayment spike is never read as growth.
• Track the next recharge date for customers who moved to long packs. A 28-day customer has 13 chances a year to leave. An annual customer has one, and you should mark it on a calendar now.
• Check churn among customers who took the long pack against a comparable group who did not. If long-pack takers were already your most loyal customers, the promotion may have discounted revenue you would have earned anyway.
• Keep ARPU on the same basis. If ARPU is built from the recharge ledger, a long-pack push inflates this month and deflates the following ones. The base-choice problem is covered in telecom ARPU and churn.

Questions the pricing manager will ask

Did the campaign make money? Not yet measurable. It pulled cash forward and cut the per-day rate by 1.5%. It pays off only if long-pack customers stay longer than they would have otherwise, which you will know at their renewal.

Why not compare packs on price per GB? Data allowances differ in daily caps and speed after the cap, so per-GB comparisons need usage data. Validity days are on every recharge record, which makes them the dependable first normaliser. Add per-GB analysis once you have consumption by pack.

Which single number goes on the dashboard? Value per 30 validity days for the month's mix, with recharge value shown beside it rather than instead of it.

Date arithmetic, proportional allocation and window functions like these are the SQL that our analytics course builds toward. If you want a view of where your current skills stand first, try the skill check.