Merchant Activation Funnel — Plan

Status: Draft (2026-04-23) — awaiting Ops + Partner Lead review before ship. Owner: Dashboard team. Scope: A new report under Reports → Merchant & Partner → Merchant Activation Funnel. Extends the brief spec in reports-plan.md §4.5.1 (merchant_docs/docs/architecture/reports-plan.md) into a full design (data model, endpoint, UI, test). Parent plan: reports-plan.md (merchant_docs/docs/architecture/reports-plan.md) (umbrella for the entire Reports menu).

DB topology note (2026-06-03): beberapa tabel yang dipakai funnel ini sekarang tersebar di 3 database.

• Stages 1–5: db_kesles_merchant — merchant.merchant_registration_requests, merchant.merchants
• Stage 6: db_kesles_merchant_order — orders.sales_orders (order_service, port 8083)
• Stage 7: db_kesles_merchant_inventory — inventory.product_inventory_items.activated_at (inventory_service, port 8084)
• Stage 8: db_kesles_merchant_payment — payment.transactions (Flow A, payment_service)

Backend endpoint harus mengagregasi dari 4 DB via service-to-service HTTP call, atau menggunakan read replica / denormalized summary table. SQL sketch di §4.2 diupdate untuk mencerminkan ini.

1. Goal

Answer a single business question: of all merchant registrants, how many pass each activation stage and how long does drop-off take?

This funnel is consumed by:

Role	Decision supported
Ops lead	Which stage is most "stuck" → priority intervention (SOP, reviewer training, merchant nudge).
Partner admin	Which partner / sales rep has the smoothest / worst onboarding.
Finance	Project when today's registration becomes revenue (time-to-first-transaction).
Super admin	Monitor the platform's activation pipeline health overall.

Non-goals (see §10): not a churn dashboard, not an activation forecast, not a cross-month cohort retention.

---

2. Funnel Stage Definitions

Eight stages; the order is strict, step N is only counted if the merchant also passes step N-1.

#	Stage	Data source	DB	When considered "entered the stage"
1	Lead	merchant.merchant_registration_requests	db_kesles_merchant	Row created (status pending / draft).
2	KYC Submitted	mrr.status IN ('pending_review','approved','rejected')	db_kesles_merchant	KYC form submitted (status leaves pending/draft).
3	KYC Reviewed	mrr.reviewed_at IS NOT NULL	db_kesles_merchant	Ops starts the review (start-review action sets reviewed_at).
4	Approved	mrr.status = 'approved'	db_kesles_merchant	Review complete, merchant approved.
5	Merchant Live	merchants.status = 'active'	db_kesles_merchant	The merchant.merchants row is promoted to active.
6	Device Shipped	orders.sales_orders.status IN ('processing','completed')	db_kesles_merchant_order	Terminal leaves the warehouse / handoff to courier.
7	Device Activated	inventory.product_inventory_items.activated_at IS NOT NULL	db_kesles_merchant_inventory	Terminal has been bound & first online.
8	First Transaction	MIN(payment.transactions.transaction_at)	db_kesles_merchant_payment	The first QRIS transaction (Flow A) is recorded.

Additional health indicator (not a stage, presented as a metric badge): Active 30d — merchants with ≥1 transaction in the last 30 days. Calculated relative to NOW(), not relative to stage 8. Data source: payment.transactions di db_kesles_merchant_payment.

2.1 Per-stage metrics

For every stage, the report returns:

• count — number of merchants in the stage (unique per merchant).
• conversion_from_previous — count / count_stage_prev (%). Stage 1 stays 100%.
• conversion_from_lead — count / count_stage_1 (%).
• median_hours_from_prev — median duration from the previous stage to this stage (integer, rounded to hours).
• median_hours_from_lead — median duration from stage 1 (lead) to this stage.

Drop-off = count_stage_prev - count. Shown explicitly in the chart so Ops can click & drill down.

2.2 Cohort & attribution

Each stage is enriched with optional breakdown:

• By partner (referral_partner_id).
• By partner rep (referral_partner_rep_id — output of migration 056).
• By status (specifically for stage 4: approved vs rejected).

Breakdowns are computed in the same query (subquery GROUP BY), not separately — so the ?partner_id=... filter is consistent between total & breakdown.

---

3. Data Model Check

3.1 Columns already available

Need	Table.column	DB	Status
Lead timestamp	mrr.created_at	db_kesles_merchant	✅
KYC submitted timestamp	Proxy: mrr.updated_at when status changes	db_kesles_merchant	⚠ see §3.2
KYC reviewed timestamp	mrr.reviewed_at	db_kesles_merchant	✅ (migration 012)
Registration status	mrr.status enum: draft / pending / pending_review / approved / rejected / cancelled	db_kesles_merchant	✅ (migration 013)
Approval timestamp	mrr.reviewed_at when status=approved	db_kesles_merchant	✅
Merchant activation timestamp	merchant.merchants.reviewed_at	db_kesles_merchant	✅ (migration 019)
Shipping event	orders.sales_orders.status + orders.sales_orders.updated_at	db_kesles_merchant_order	⚠ see §3.2
Device activation	inventory.product_inventory_items.activated_at	db_kesles_merchant_inventory	✅
First transaction	MIN(payment.transactions.transaction_at) GROUP BY merchant_id	db_kesles_merchant_payment	✅
Partner attribution	mrr.referral_partner_id / merchants.referral_partner_id	db_kesles_merchant	✅
Partner rep attribution	mrr.referral_partner_rep_id / merchants.referral_partner_rep_id	db_kesles_merchant	✅ (migration 056)

3.2 Gaps & mitigations

Gap 1 — Status transition timestamp pending→pending_review. There is no dedicated column; mrr.updated_at is overwritten on every field change. Interim mitigation: treat stage_2 = stage_3 - epsilon (use the same timestamp as reviewed_at). This means in v1, stage 2 and stage 3 effectively overlap. Permanent fix: add a kyc_submitted_at column via a new migration (estimated 1 pt, deferred to v1.1).

Gap 2 — Device shipped timestamp. orders.sales_orders.updated_at does not guarantee the moment the goods leave the warehouse (it can be updated for any reason). Mitigation: use orders.shipping_orders.shipped_at if available (via db_kesles_merchant_order); otherwise use orders.sales_orders.updated_at when status='processing' with a CTE MIN(updated_at) FILTER (WHERE status='processing'). Permanent fix: ensure shipping_orders.shipped_at is consistently populated (check the ship tracker worker).

Gap 3 — Rejected merchants must not enter stage 5+. Explicit filter in the query: stage ≥5 requires merchants.id IS NOT NULL AND merchants.status NOT IN ('deleted', 'terminated'). Stage 4 still records approved & rejected as a breakdown — the drop-off from stage 3 → 4 is split into approved vs rejected in the UI.

Gap 4 — Migration 056 backfill data. A default "Primary PIC" rep has been created for active partners with a referral_code. But if an old merchant registered before migration 056 ran and the referral_code does not match any rep, referral_partner_rep_id stays NULL. The UI must handle this: the "(no rep)" group must appear as its own row in the breakdown, not skipped.

Gap 5 — Cross-DB query (NEW 2026-06-03). Stages 6–8 require data dari DB yang berbeda (order, inventory, payment). Dashboard_api tidak boleh query langsung ke DB domain lain. Opsi implementasi:

• Opsi A (preferred): Endpoint /api/dashboard/reports/merchant-activation-funnel di dashboard_api memanggil summary endpoint di order_service, inventory_service, dan payment_service via HTTP untuk mendapatkan count + timestamp per merchant_id range. Services masing-masing expose endpoint internal seperti GET /internal/reports/activation-stage?merchant_ids=....
• Opsi B (simpler, V1): Buat summary table merchant.activation_funnel_snapshots di db_kesles_merchant yang di-refresh oleh worker harian, menggabungkan data lintas DB. Staleness ≤ 24 jam acceptable untuk report ini.
• Pilihan antara Opsi A vs B perlu keputusan tim sebelum implementasi dimulai.

---

4. Backend Design

4.1 Endpoint

GET /api/dashboard/reports/merchant-activation-funnel
    ?from=<YYYY-MM-DD>                     # required, lead-range bucket
    &to=<YYYY-MM-DD>                       # required, inclusive
    &partner_id=<uuid>                     # optional
    &partner_rep_id=<uuid>                 # optional
    &breakdown=none|partner|rep            # optional, default none

Response:

{
  "meta": {
    "from": "2026-01-01T00:00:00+07:00",
    "to":   "2026-04-30T23:59:59+07:00",
    "total_leads": 450,
    "active_30d": 145,
    "generated_at": "2026-04-23T11:04:00+07:00",
    "filters": {"partner_id": null, "partner_rep_id": null, "breakdown": "rep"}
  },
  "stages": [
    {
      "stage": 1,
      "code": "lead",
      "label": "Lead",
      "count": 450,
      "conversion_from_previous_pct": 100.0,
      "conversion_from_lead_pct": 100.0,
      "median_hours_from_previous": null,
      "median_hours_from_lead": null,
      "breakdown": [
        {"key": "rep:abcd-...", "label": "Budi P.", "count": 120},
        {"key": "rep:ef01-...", "label": "Citra",   "count":  95},
        {"key": "rep:null",    "label": "(no rep)","count":  40}
      ]
    },
    { "stage": 2, "code": "kyc_submitted", ... },
    { "stage": 3, "code": "kyc_reviewed",  ... },
    { "stage": 4, "code": "approved",
      "breakdown_split": {"approved": 300, "rejected": 50} },
    { "stage": 5, "code": "merchant_live", ... },
    { "stage": 6, "code": "device_shipped", ... },
    { "stage": 7, "code": "device_activated", ... },
    { "stage": 8, "code": "first_transaction", ... }
  ]
}

4.2 SQL sketch (Postgres, not final)

PENTING (2026-06-03): query di bawah adalah sketch untuk Stages 1–5 yang datanya masih di db_kesles_merchant. Stages 6–8 memerlukan data dari DB terpisah — lihat Gap 5 di §3.2. Implementer harus memilih Opsi A (HTTP aggregation) atau Opsi B (worker snapshot table) sebelum menulis query final.

-- Database: db_kesles_merchant
-- Stages 1–5 only. Stages 6–8 dari service lain (lihat §3.2 Gap 5).

-- Reusable CTE: registration scope being measured
WITH scope AS (
  SELECT mrr.id AS reg_id, mrr.created_at, mrr.reviewed_at, mrr.status,
         mrr.referral_partner_id, mrr.referral_partner_rep_id,
         m.id AS merchant_id, m.status AS merchant_status, m.reviewed_at AS merchant_activated_at
  FROM merchant.merchant_registration_requests mrr
  LEFT JOIN merchant.merchants m ON m.created_by_user_id = mrr.created_by_user_id
  WHERE mrr.created_at BETWEEN $1 AND $2
    AND ($3::uuid IS NULL OR mrr.referral_partner_id     = $3)
    AND ($4::uuid IS NULL OR mrr.referral_partner_rep_id = $4)
)
-- Stage counts 1–5 (all in db_kesles_merchant)
SELECT
  (SELECT COUNT(*) FROM scope)                                                  AS stage_1,
  (SELECT COUNT(*) FROM scope WHERE status <> 'pending' AND status <> 'draft')  AS stage_2,
  (SELECT COUNT(*) FROM scope WHERE reviewed_at IS NOT NULL)                    AS stage_3,
  (SELECT COUNT(*) FROM scope WHERE status = 'approved')                        AS stage_4,
  (SELECT COUNT(*) FROM scope WHERE merchant_status = 'active')                 AS stage_5;

-- Stages 6–8: aggregate via HTTP dari service masing-masing
-- Stage 6: order_service  → GET /internal/reports/shipped-merchants?merchant_ids=...
--   Source: db_kesles_merchant_order.orders.sales_orders
-- Stage 7: inventory_service → GET /internal/reports/activated-terminals?merchant_ids=...
--   Source: db_kesles_merchant_inventory.inventory.product_inventory_items
-- Stage 8: payment_service → GET /internal/reports/first-transactions?merchant_ids=...
--   Source: db_kesles_merchant_payment.payment.transactions (Flow A only)

Median hours per stage-to-stage are computed via PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM ...)/3600) in a separate sub-query so the main query stays scannable.

4.3 Handler code layout

• New file: services/dashboard_api/internal/app/dashboard_reports_merchant_activation_funnel.go (consistent with dashboard_reports_growth_projection.go).
• Function: handleMerchantActivationFunnelReport(w, r).
• Route: register in server.go after existing report routes.
• Response struct: MerchantActivationFunnelReport, MerchantActivationFunnelStage, MerchantActivationFunnelBreakdown.
• DB helper: loadMerchantActivationFunnel(ctx, db, filter) in db.go or dashboard_reports_merchant_activation.go.

4.4 Permissions & cache

• RBAC: requires reportRead + merchantRead. Export gated by reportExport.
• Cache: 5 minutes (dashboard_reports:funnel:<hash>), per the general rule in reports-plan.md §2.6 (merchant_docs/docs/architecture/reports-plan.md). Key hashed from (from, to, partner_id, partner_rep_id, breakdown).
• Timezone: all input & output assumed Asia/Jakarta (WIB). Workers consuming the report must serialize ISO8601 with offset.

---

5. Frontend UI Design

5.1 Panel layout

New file: apps/merchant_dashboard/lib/dashboard/features/home/presentation/widgets/panels/report_merchant_activation_funnel_panel.dart.

┌─────────────────────────────────────────────────────────┐
│ [icon] Merchant Activation Funnel                       │
│        Merchant registration conversion per stage       │
│                                         [Export CSV] [PDF]│
├─────────────────────────────────────────────────────────┤
│ Filter bar                                              │
│  [Date range: Last 30 days ▼] [Partner: All ▼]          │
│  [Rep: All ▼] [Breakdown: (none) ▼] [Apply]             │
├─────────────────────────────────────────────────────────┤
│ Summary cards (5)                                       │
│  [Total Leads 450] [Approved 300] [Live 270] [Tx 180]   │
│  [Active 30d 145]                                       │
├─────────────────────────────────────────────────────────┤
│ Funnel chart (custom painter, horizontal bars)          │
│  ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓ 450 Lead (100%)                     │
│  ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓   420 KYC Submitted (93%, -30)        │
│  ▓▓▓▓▓▓▓▓▓▓▓▓▓▓    380 KYC Reviewed   (84%, -40, Ø 18h)│
│  ▓▓▓▓▓▓▓▓▓▓▓▓      350 Approved        (78%, -30)      │
│  ... etc                                                │
├─────────────────────────────────────────────────────────┤
│ Breakdown table (when breakdown ≠ none)                 │
│  Partner Rep   │ Lead │ Approved │ Live │ Tx │ Conv%   │
│  Budi P.       │  120 │    95    │  90  │ 60 │ 50%     │
│  Citra         │   95 │    80    │  72  │ 45 │ 47%     │
└─────────────────────────────────────────────────────────┘

5.2 Reused components

• MasterPanelShell (header + footer + loading state).
• DashboardSectionCard for each section.
• MasterTableShell for the breakdown table.
• Filter bar reuses DashboardDateRangePicker + DashboardDropdownFilter (add if absent — check first).
• Funnel chart: a new CustomPaint (_FunnelPainter) — no external lib, consistent with _StackedBar in growth-projection.
• Export button reuses DashboardExportMenu (CSV + PDF generator).

5.3 State management

• StatefulWidget + Future<MerchantActivationFunnelReport>.
• Re-fetch only when the Apply button is pressed (not on-change) — avoid spam.
• Loading state: skeleton + disabled filter.
• Error state: DashboardFormErrorBanner + Retry button.

5.4 i18n labels

The entry 'Report Merchant Activation': ('Merchant Activation Funnel', 'Funnel Aktivasi Merchant') is already registered in dashboard_language.dart. Additional stage labels (e.g. "Lead", "KYC Reviewed", etc.) live in a local map inside the new panel file.

5.5 Export format

CSV:

from,to,generated_at,partner_filter,rep_filter
2026-01-01,2026-04-30,2026-04-23T11:04:00+07:00,all,all

stage,count,conversion_from_previous,conversion_from_lead,median_hours_from_previous,median_hours_from_lead
lead,450,100.00,100.00,,
kyc_submitted,420,93.33,93.33,0.5,0.5
...

PDF: CompanyProfile letterhead + stages table + funnel chart rendered to PNG (reuse the existing growth-projection export pipeline if available; if not, defer to v1.1).

---

6. Test Plan

6.1 Backend unit tests

• TestMerchantActivationFunnel_EmptyRange — no registrations → all stages = 0.
• TestMerchantActivationFunnel_AllStages — seed 1 merchant with a complete timeline → all stages = 1, median = 0.
• TestMerchantActivationFunnel_Rejected — 5 pending, 3 approved, 2 rejected → stage 4 count = 3 (approved), breakdown_split.rejected = 2.
• TestMerchantActivationFunnel_FilterPartner — 10 merchants, 4 with partner A → filter partner A returns only 4.
• TestMerchantActivationFunnel_FilterRep — test 056-backfill rep + explicit rep + null rep.
• TestMerchantActivationFunnel_BreakdownByRep — verify breakdown structure & total match.
• TestMerchantActivationFunnel_MedianCalc — fixture of 5 merchants with durations 1h/4h/7h/10h/13h → median = 7h.

6.2 Integration tests

• Seed test DB: 20 registrations with a realistic stage distribution (see new fixture test_merchant_funnel_fixture.sql).
• Hit the endpoint → assert response shape + value checksum.

6.3 Manual QA checklist

• Menu appears under Reports → Merchant & Partner → Merchant Activation Funnel.
• Default filter = last 30 days; chart + table populate.
• Change filter → Apply button enabled → re-fetch.
• Partner filter + rep filter independent & combined work.
• Breakdown by rep shows active reps; (no rep) is a separate row.
• Rejected count appears at stage 4 as a sub-chip.
• Active 30d badge updates together with the filter.
• Export CSV: header correct, per-stage rows correct, conversion% consistent.
• Empty state: range without registrations → "No data yet" placeholder, not an error.
• Error state: backend 500 → banner + retry; does not hang.
• Permission: a user with reportRead but without merchantRead → 403.

---

7. Implementation Steps

Estimated ~4–6 engineering days, broken up so chunks can be merged incrementally.

Step 1 — Backend endpoint + SQL (1.5 days)

1.1 Putuskan Cross-DB strategy: Opsi A (HTTP aggregation) vs Opsi B (worker snapshot). Buat ADR singkat. 1.2 Turn the §4.2 SQL sketch into the final query + wrap in loadMerchantActivationFunnel(). 1.3 Build the response struct + JSON tags. 1.4 New handler dashboard_reports_merchant_activation_funnel.go + register the route. 1.5 Permission check (reportRead + merchantRead) + timezone normalization. 1.6 §6.1 unit tests using sqlmock / testcontainers.

Step 2 — Data fixture & sanity check (0.5 day)

2.1 Build the fixture test_merchant_funnel_fixture.sql (20 merchants, 8 stages). 2.2 Integration test hitting the endpoint + compare expected JSON. 2.3 Manual check via curl + superadmin token, verify shape.

Step 3 — Frontend API client + entity (0.5 day)

3.1 DashboardHomeApi.getMerchantActivationFunnel(...) in dashboard_home_api.dart. 3.2 Entities MerchantActivationFunnelReport, ...Stage, ...Breakdown in home_dashboard_entities.dart. 3.3 Mapper in home_dashboard_mapper.dart. 3.4 Datasource, repository contract + impl, usecase (4-layer standard).

Step 4 — Frontend panel + chart (2 days)

4.1 report_merchant_activation_funnel_panel.dart skeleton: shell + filter bar + loading state. 4.2 _FunnelPainter custom paint (horizontal bar with count label + conversion%). 4.3 Summary cards (5 metrics). 4.4 Breakdown table (reuse MasterTableShell). 4.5 Error handling + retry. 4.6 Integrate into dashboard_sections.dart → switch case for the Report Merchant Activation submenu.

Step 5 — Export (0.5 day)

5.1 Local CSV generator (use package:csv or a manual StringBuilder). 5.2 PDF export — v1 skip if the export pipeline doesn't exist yet; write a TODO + scope to v1.1.

Step 6 — QA + polish (0.5 day)

6.1 §6.3 manual QA checklist. 6.2 Polish spacing / labels / tooltips. 6.3 Review dark mode contrast (check the palette). 6.4 Update changelog + demo to Ops + Partner Lead.

Step 7 — Docs & enablement (0.5 day)

7.1 Update reports-plan.md §4.5.1 (merchant_docs/docs/architecture/reports-plan.md) with a link to this plan. 7.2 Write a short "how to read the funnel" runbook in merchant_docs/api_docs/internal/docs/runbooks/ for Ops. 7.3 Update the role-access SOP if there is a new permission (none — reuse existing).

---

8. Open Questions

1. Stage 6 shipped-at — should we wait for a consistent shipping_orders.shipped_at migration, or accept the sales_orders.updated_at proxy in v1? (Default: accept the proxy, flag in the UI "proxy data, accuracy ±1h".)
2. Active 30d window — 30 days rolling from today, or 30 days from each merchant's stage 8? (Default: rolling from today.)
3. Rejected visualization — show as a "red drop-off" in the funnel, or as a separate branch under stage 4? (Default: sub-chip on stage 4 + drop-off stays cumulative.)
4. Export PDF — v1 ship CSV only or hold until PDF is done? (Default: ship CSV, TODO PDF v1.1.)
5. Public dashboard board — need an iframe embed / shareable link for external Partners? (Non-goal for v1.)
6. Cross-DB strategy (NEW) — Opsi A (HTTP aggregation per service) vs Opsi B (worker snapshot table di db_kesles_merchant)? Opsi B lebih simpel untuk V1 tapi menambah coupling. Perlu keputusan sebelum coding dimulai.

---

9. Risks

Risk	Impact	Mitigation
Heavy SQL because of 4 CTEs + 3 medians	Slow response (>2s) for 1-year ranges	Cap default range to 90 days in the UI; cache 5 minutes; index review on mrr.created_at, payment_terminals.activated_at, transactions.merchant_id+transaction_at.
partner_rep data null for old registrations	Breakdown by rep is misleading	UI groups "(no rep)" explicitly + tooltip "registrations before 2026-04-23 are not yet attributed to a rep".
Gap 1 stage_2 = stage_3 makes median_2 = 0	The metric is uninformative	Mark the column "n/a" in the UI until kyc_submitted_at is available.
Combined partner+rep filter inconsistent	User confused why a rep appears even though it does not belong to that partner	UI validates: pick partner → rep list is filtered to reps belonging to that partner only.
Cross-DB data skew	Stage 6/7/8 counts mismatch stage 1–5 due to eventual consistency	Add data_freshness_at per stage in response; UI shows tooltip "Data stage 6–8 diperbarui setiap 24 jam" (Opsi B) atau "real-time" (Opsi A).

---

10. Non-goals (v1)

• Activation prediction / forecast for next month.
• Cross-period benchmarking (MoM, YoY) — see future plan "Activation Trend Chart".
• "Stuck merchant" auto-alerts via email/WA.
• Editable per-stage SLA.
• PDF export (deferred to v1.1 if the pipeline isn't ready).

---

11. References

• Parent plan: reports-plan.md — moved to merchant_docs/docs/architecture/reports-plan.md
• Partner rep attribution: migration 056_create_partner_reps.sql + SOP dashboard-role-access-sop.md §6.4
• Purchasing/Sales refactor: purchasing-sales-refactor-plan.md (shipping flow)
• Existing report pattern: apps/merchant_dashboard/lib/.../panels/report_growth_projection_panel.dart + services/dashboard_api/internal/app/dashboard_reports_growth_projection.go
• Menu & i18n: apps/merchant_dashboard/lib/dashboard/core/i18n/dashboard_language.dart (key already registered)
• DB topology: merchant_docs/api_docs/internal/docs/dev/database/schema/schema.md (diupdate 2026-06-03)

---

12. Change Log

• 2026-04-23 — v0.1 draft written by the Dashboard team. Awaiting review.
• 2026-06-03 — Updated DB sources for stages 6–8 (post multi-DB extraction complete): order_service, inventory_service, payment_service. Added Gap 5 (cross-DB query), updated SQL sketch, added Open Question 6, added terminated status to stage 3 rejection filter.