Merchant Financial Analytics — Panel Spec
Spec document for 2 new panels in the kesles_merchant dashboard:
- "Actual Revenue" — actual revenue from real transactions (companion to the Growth Projection panel which is forward-looking)
- "Profit Analysis" — revenue vs cost per merchant per period, with per-merchant drill-down
These panels read from tables created in migration 063 (merchant.transactions, merchant.transactions_monthly_agg, merchant.merchant_financials).
Status (2026-06-23): not shipped — design proposal only. The Actual Revenue / Profit Analysis panels, their
/api/dashboard/reports/...endpoints, and the ETL job described below were never built. The analytics tables have since moved out ofdb_kesles_merchant(merchant.transactions_monthly_aggandmerchant.merchant_financialswere dropped by migration v1/085 on 2026-06-09 and now live indb_kesles_merchant_payment.payment, owned by payment_service). The SQL andmerchant.*table references below reflect the original design and are kept as-is.
1. Context
Why two new panels?
| Existing panel | Scope | Data source |
|---|---|---|
| Report Growth Projection | 5-year forward-looking projection | Manual parameter input (no DB) |
| Report Deployment Ramp | Monthly deployment projection | Manual parameter input |
| Report Overview | High-level KPI | merchant.* aggregate |
What is missing:
- Actual revenue — "what is the actual revenue from real transactions this month?"
- Profit per merchant — "which merchant is profitable, which is still losing (because the device hasn't paid back yet)?"
These two panels become the actual ground-truth — compare with Growth Projection to measure planning accuracy.
Who uses these panels?
| Role | Use case |
|---|---|
| Super admin / executives | View total monthly revenue, compare with target |
| Finance | Calculate net income, COGS amortization, tax |
| Ops | Detect merchants with dropping transactions (churn warning) |
| Sales | Calculate eligible partner_rep commissions |
Access control
Super admin only (same as Growth Projection) — gating in dashboard_sections.dart.
2. "Actual Revenue" Panel
2.1 Purpose
Display Kesles actual revenue based on real transactions already forwarded from payment.kesles.com to merchant.transactions. Broken down per stream:
- Onboarding fee — from new merchants (table
merchant.merchants+ join date) - Service fee (Rp 2,000/day) — daily accumulation × active merchant count
- MDR share (0.3%) — from
merchant.transactions.mdr_fee_kesles - Shipping revenue — from onboarding (if tracked)
2.2 UI Layout
┌────────────────────────────────────────────────────────────────────┐
│ Report → Actual Revenue │
│ Revenue aktual bulan ini (May 2026): Rp XX.XXX.XXX │
└────────────────────────────────────────────────────────────────────┘
┌─────────────────┬─────────────────┬─────────────────┬──────────────┐
│ Onboarding Fee │ Service Fee │ MDR Share │ Total Revenue │
│ Rp 9.460.000 │ Rp 69.197.000 │ Rp 97.920.000 │ Rp 176.577.000│
│ +12% vs prev │ +8% vs prev │ +15% vs prev │ +11% vs prev │
└─────────────────┴─────────────────┴─────────────────┴──────────────┘
┌────────────────────────────────────────────────────────────────────┐
│ Monthly Revenue Trend (12 bulan terakhir) │
│ │
│ Stacked bar chart: onboarding + service fee + MDR │
│ ▃▄▅▆▇█▆▇▇█▇█ │
│ May'25 Jun Jul Aug Sep Oct Nov Dec Jan'26 Feb Mar Apr May │
└────────────────────────────────────────────────────────────────────┘
┌────────────────────────────────────────────────────────────────────┐
│ Actual vs Projection (5 tahun) │
│ │
│ Line overlay: Actual revenue (solid) vs Projected (dashed) │
│ Shows drift — kalau actual < projection: signal adjust target │
└────────────────────────────────────────────────────────────────────┘
┌────────────────────────────────────────────────────────────────────┐
│ Top 10 Revenue Contributors (merchant-wise) │
│ │
│ 1. Warung Pak Budi Rp 2.140.000 [detail→] │
│ 2. Toko Harapan Rp 1.890.000 [detail→] │
│ ... │
└────────────────────────────────────────────────────────────────────┘
2.3 Data Source
Main query uses merchant.transactions_monthly_agg + merchant.merchants:
-- Summary revenue bulan ini
SELECT
sum(gross_amount) as gross,
sum(mdr_fee_kesles) as mdr_revenue,
sum(transaction_count) as txn_count,
count(distinct merchant_id) as active_merchants
FROM merchant.transactions_monthly_agg
WHERE period_month = date_trunc('month', now());
-- Revenue per stream (onboarding, service fee, MDR)
-- Onboarding: merchant count joined this month × onboarding fee
SELECT count(*) * 315315 as onboarding_fee_revenue
FROM merchant.merchants
WHERE date_trunc('month', created_at) = date_trunc('month', now())
AND status = 'active';
-- Service fee: per-merchant-active-day × Rp 1802
-- Requires joining transactions to estimate active days
SELECT
merchant_id,
count(distinct date_trunc('day', transaction_at)) as active_days
FROM merchant.transactions
WHERE transaction_at >= date_trunc('month', now())
GROUP BY merchant_id;
For the actual vs projection chart:
- Actual = the query above per-month for the last 12 months
- Projection = from the Growth Projection panel (the loaded scenario)
2.4 New API Endpoints
Added under /api/dashboard/reports/actual-revenue/* (super admin only):
GET /api/dashboard/reports/actual-revenue/summary
Query: ?period=today|this_month|this_quarter|ytd&compare=true
Response:
{
"period": { "from": "2026-05-01", "to": "2026-05-31" },
"summary": {
"currency": "IDR",
"gross_revenue": 176577000,
"onboarding_fee": 9460000,
"service_fee": 69197000,
"mdr_share": 97920000,
"shipping_revenue": 0,
"active_merchant_count": 128,
"transaction_count": 7680
},
"comparison": {
"period_label": "previous_month",
"delta_pct": { "gross": 11.2, "onboarding": 12.0, "service_fee": 8.0, "mdr": 15.3 }
}
}
GET /api/dashboard/reports/actual-revenue/monthly-trend?periods=12
Returns array of 12 months with breakdown per stream.
GET /api/dashboard/reports/actual-revenue/top-contributors?limit=10&period=this_month
Returns top N merchants by total revenue contribution.
GET /api/dashboard/reports/actual-revenue/actual-vs-projection?scenario_id={id}
Returns side-by-side actual + projection (from scenario_id in report.growth_projection_scenarios).
2.5 Files to Be Created
Backend:
services/dashboard_api/internal/app/dashboard_reports_actual_revenue.go— 4 handlers- Register routes in
server.go
Flutter:
apps/merchant_dashboard/lib/dashboard/features/home/presentation/widgets/panels/report_actual_revenue_panel.dart- Extend
dashboard_sections.dartrouting for the "Report Actual Revenue" submenu - Add to
dashboard_language.dartkeys
3. "Profit Analysis" Panel
3.1 Purpose
Display net profit per merchant per month (revenue − amortized device COGS − OpEx allocation − sales rep commission − CAC). Identify:
- Merchants that have already paid back the Rp 732,978 device COGS
- Merchants still in negative cashflow (temporary loss — device hasn't paid back yet)
- Average actual payback period vs the projected 8–13 months
3.2 Formula
Per merchant per month:
Revenue = (onboarding_fee one-time, if onboarding month)
+ (service_fee × active_days × Rp 1.802)
+ (MDR × transaction_count)
+ (shipping_revenue one-time, if onboarding month)
Cost (allocated):
device_hpp_amortized = HPP / expected_lifetime_months (e.g., 60 bulan = 5 tahun)
= Rp 732.978 / 60 = Rp 12.216/bulan
shipping_cost_actual = dari merchant.merchants.shipping_paid (atau estimate dari tier; currency dari kolom merchant.merchants.shipping_currency, default IDR)
opex_allocated = total_monthly_opex / active_merchant_count
= Rp 200jt / 128 = Rp 1.562.500 per merchant per bulan (Year 1)
sales_rep_commission = dari tabel partner.partner_rep_commissions (kalau ada)
cac_allocated = total_marketing / acquired_this_period
= Rp 70jt / 300 = Rp 233.333 (dialokasi sekali, bulan onboarding)
Net Profit = Revenue − Cost
3.3 UI Layout
┌────────────────────────────────────────────────────────────────────┐
│ Report → Profit Analysis │
│ Period: [May 2026 ▼] Merchant Filter: [All ▼] │
└────────────────────────────────────────────────────────────────────┘
┌─────────────────┬─────────────────┬─────────────────┬──────────────┐
│ Total Revenue │ Total Cost │ Net Profit │ Margin % │
│ Rp 176.577.000 │ Rp 220.500.000 │ (Rp 43.923.000) │ -24.9% │
│ │ │ ⚠️ Loss │ (Year 1 phase)│
└─────────────────┴─────────────────┴─────────────────┴──────────────┘
┌────────────────────────────────────────────────────────────────────┐
│ Cost Breakdown │
│ │
│ Device HPP Amortized Rp 1.563.648 (128 × Rp 12.216) │
│ OpEx Allocated Rp 200.000.000 │
│ Sales Rep Commission Rp 12.000.000 │
│ CAC Allocated (new) Rp 7.000.000 │
│ ──────────────────────────────────────── │
│ Total Cost Rp 220.563.648 │
└────────────────────────────────────────────────────────────────────┘
┌────────────────────────────────────────────────────────────────────┐
│ Payback Status (300 merchant Year 1) │
│ │
│ ✅ Payback done (cumulative revenue > HPP+shipping): 45 merchant │
│ 🟡 Near payback (80-99% HPP recovered): 67 merchant │
│ 🔴 Still negative (<80% HPP recovered): 188 merchant │
│ │
│ [Detail table→] │
└────────────────────────────────────────────────────────────────────┘
┌────────────────────────────────────────────────────────────────────┐
│ Per-Merchant Profit Table │
│ │
│ Merchant | Revenue | Cost | Profit | Payback │
│ Warung Pak Budi | 2.140.000 | 850.000 | 1.290.000 | ✅ 3 bulan │
│ Toko Harapan | 1.890.000 | 850.000 | 1.040.000 | ✅ 4 bulan │
│ UD Sejahtera | 320.000 | 850.000 | (530.000) | 🔴 negative │
│ ... │
└────────────────────────────────────────────────────────────────────┘
┌────────────────────────────────────────────────────────────────────┐
│ Avg Payback Period (actual vs target) │
│ │
│ Target: 8 bulan (rekomendasi dari qrisplus-selling- │
│ strategy-analysis.md §2.3) │
│ Actual average: 11.2 bulan │
│ Delta: +3.2 bulan (merchant onboarding lebih slow ramp) │
└────────────────────────────────────────────────────────────────────┘
3.4 Data Source
Main query uses merchant.merchant_financials (monthly materialized):
-- Summary per period
SELECT
sum(total_revenue) as revenue,
sum(total_cost) as cost,
sum(net_profit) as net_profit,
count(distinct merchant_id) as merchant_count
FROM merchant.merchant_financials
WHERE period_month = '2026-05-01';
-- Payback status
WITH cumulative AS (
SELECT
merchant_id,
sum(total_revenue) as revenue_to_date,
sum(device_hpp_depreciated + shipping_cost_actual) as recovery_target
FROM merchant.merchant_financials
GROUP BY merchant_id
)
SELECT
count(*) filter (where revenue_to_date >= recovery_target) as paid_back,
count(*) filter (where revenue_to_date >= 0.8 * recovery_target AND revenue_to_date < recovery_target) as near_payback,
count(*) filter (where revenue_to_date < 0.8 * recovery_target) as still_negative
FROM cumulative;
-- Per-merchant detail with payback months
SELECT
m.id,
m.merchant_name,
f.total_revenue,
f.total_cost,
f.net_profit,
(SELECT count(*) FROM merchant.merchant_financials f2
WHERE f2.merchant_id = m.id AND f2.period_month <= now()) as months_active
FROM merchant.merchants m
JOIN merchant.merchant_financials f ON f.merchant_id = m.id
WHERE f.period_month = '2026-05-01'
ORDER BY f.net_profit DESC
LIMIT 100;
3.5 New API Endpoints
/api/dashboard/reports/profit-analysis/*:
GET /api/dashboard/reports/profit-analysis/summary?period=2026-05
{
"period": "2026-05",
"summary": {
"total_revenue": 176577000,
"total_cost": 220500000,
"net_profit": -43923000,
"margin_pct": -24.9,
"status": "loss_expected_year_1"
},
"cost_breakdown": {
"currency": "IDR",
"device_hpp_depreciated": 1563648,
"opex_allocated": 200000000,
"sales_rep_commission": 12000000,
"cac_allocated": 7000000,
"shipping_cost_actual": 0
}
}
GET /api/dashboard/reports/profit-analysis/payback-status
Returns bucket count (paid_back / near_payback / still_negative).
GET /api/dashboard/reports/profit-analysis/per-merchant?period=2026-05&limit=100&cursor=...&sort=profit_desc
Paginated per-merchant profit table.
GET /api/dashboard/reports/profit-analysis/avg-payback-months
Returns avg payback duration (actual vs target).
3.6 Materialization Job
merchant.merchant_financials must be populated via a monthly batch job (ETL):
Job name: refresh_merchant_financials
Trigger:
- Hourly for the current month (ad-hoc update)
- Daily 03:00 for full historical rebuild if late-arrival transactions exist
Logic pseudo-code:
FOR each merchant WHERE status = 'active':
FOR each period_month IN range(merchant.created_at, now()):
# Revenue calculation
onboarding = 315315 IF merchant.created_at in period_month ELSE 0
service_fee = count(active_days in period_month) × 1802
mdr = sum(transactions.mdr_fee_kesles WHERE period = period_month)
revenue = onboarding + service_fee + mdr
# Cost allocation
device_hpp = 732978 / 60
shipping = 100000 IF merchant.created_at in period_month ELSE 0
opex = total_monthly_opex(period_month) / active_merchant_count(period_month)
commission = sum(partner_rep_commissions WHERE period = period_month)
cac = 233333 IF merchant.created_at in period_month ELSE 0
cost = device_hpp + shipping + opex + commission + cac
# Upsert
INSERT INTO merchant.merchant_financials (...) VALUES (...)
ON CONFLICT (merchant_id, period_month) DO UPDATE SET ...
New files:
services/dashboard_api/internal/job/refresh_financials.go- Cron registration in
cmd/server/main.goor a separate worker
3.7 Files to Be Created
Backend:
services/dashboard_api/internal/app/dashboard_reports_profit_analysis.go— 4 handlersservices/dashboard_api/internal/job/refresh_financials.go— ETL job- Register routes in
server.go
Flutter:
apps/merchant_dashboard/lib/dashboard/features/home/presentation/widgets/panels/report_profit_analysis_panel.dart- Extend
dashboard_sections.dartrouting for the "Report Profit Analysis" submenu
4. Integration with Existing Panels
4.1 Report Growth Projection ← compare → Actual Revenue
Add a new tab in the Growth Projection panel: "Compare with Actual".
- Load the saved scenario
- Overlay the actual revenue chart from
GET /api/dashboard/reports/actual-revenue/actual-vs-projection?scenario_id=... - Highlight months where actual < projection or actual > projection
4.2 Report Deployment Ramp ← actual active base
The Deployment Ramp panel currently only shows projected active units. It can be enhanced with an overlay of actual active merchant count per month (from merchant.transactions_monthly_agg).
5. Implementation Timeline
| Week | Task |
|---|---|
| Month 2 Week 1 | Apply migration 063 (transaction store + analytics tables) |
| Month 2 Week 2 | ETL job refresh_financials — populate merchant_financials |
| Month 2 Week 3 | Backend handler Actual Revenue (4 endpoints) + unit tests |
| Month 2 Week 4 | Backend handler Profit Analysis (4 endpoints) + unit tests |
| Month 3 Week 1 | Flutter panel Actual Revenue + API integration |
| Month 3 Week 2 | Flutter panel Profit Analysis + per-merchant table |
| Month 3 Week 3 | Integration to Growth Projection "Compare with Actual" tab |
| Month 3 Week 4 | QA + UI polish + deploy |
In parallel with mobile app Month 2-3. Analytics panels do not gate launch because they are used by internal admins, not merchant-facing.
6. Open Questions
Before development starts:
- Device HPP amortization period — 60 months (5-year lifetime) or shorter (30 months)?
- Shorter → cost per month higher, profit appears lower early
- Recommendation: 60 months — aligned with the assumption of 5-year device lifetime
- OpEx allocation method — flat per merchant, or proportional to revenue?
- Flat is simpler, proportional is fairer (large merchants contribute proportionally to OpEx)
- Recommendation: flat for MVP, upgrade to proportional if needed
- CAC allocation — onboarding month only, or spread over the first 12 months?
- Spread is smoother but more complex
- Recommendation: lump sum at onboarding month (simple, matches SaaS industry convention)
- Commission scheme — already in the migration or not yet?
- Not yet. Needs a new migration
partner.partner_rep_commissions(future) - For MVP: hardcode Rp 100,000/merchant if from a referral code, else 0
- Not yet. Needs a new migration
7. References
- Existing Growth Projection panel:
planning_growth_projection_panel.dart - Selling strategy that defines the cost structure:
qrisplus-selling-strategy-analysis.md - Feasibility analysis (unit economics):
qrisplus-feasibility-analysis.html - Transaction store migration:
063_transaction_store_and_analytics.sql - PSP integration (transaction source data):
psp-integration-api-contract.md