KYC Analysis Tables — Build Spec & Tracking TODO

00 What this doc is & why

KYC gates every Botim Pay finance feature (transfers, prepaid card, top-up). When KYC goes bad, downstream revenue is blocked. Today we have server-side success on submitted applications (~50% completion, dedup) — but were blind to the top-of-funnel drop where ~40% never even start a verification method. This doc joins the front-end funnel with server-side completion to see the whole picture, and drives three things:

• ① Insight — quantify where and why KYC goes bad, per channel and client combination (country / app version / mini-program version).
• ② TODO — the tracking fixes needed to measure it reliably (the front-end has known gaps).
• ③ Create Table — the table layer that computes it, built around the Bad KYC definition below.

00·b Bad KYC — definition DRAFT · needs eng sign-off

A Bad KYC is an attempt that showed genuine intent but failed to reach a successful server-side completion — excluding pure user cancellation. A session is Bad KYC when all hold:

• Reached KYC Verification Started (intent shown), AND
• Did not reach server-side Completed, AND
• Failure attributable to: system / SDK error, SDK-handoff drop (UAE Pass black box), pending-never-resolved, audit reject, or non-cancel drop-off.

Excluded (not Bad KYC): user Cancel, "just exploring", never-started, "no Emirates ID right now" (will-retry).

Metric: Bad KYC rate = Bad KYC sessions / sessions reaching Verification Started, sliced by channel × client dims. (Session-level once session_id lands; user-level until then.)

00·c KYC order decomposition (MECE)

Every KYC order falls in exactly one bucket — A + B + C + D = 100%:

Variables: Entered = Flow Entered distinct uid (denominator) · Started = Verification Started · KYC_passed = journey_flag=1 OR step IN ('DOWN','PRE_EID_EXPIRY') · Cancel = user-reject / give-up / not-comfortable (excluded from Bad KYC) · Audit_reject = tm_audit_task status=R.

Audit funnel (30d): entered 2,016 → reject 1,120 (55.6%) / pass 886 (44%); 97% triggered by CHANNEL_HINT_REVIEW. Top reject reasons: Portrait Inspection Failed 66%, Document & ICP mismatch — expiry 17% / DOB 9%.

Insight: the two biggest losses are No-intent (~40%) and Bad KYC (~25%); manual audit (C) is a tiny slice (~2k/month). Bad KYC is dominated by UAE Pass job check timeout + UAE KYC "EID not scanned".

01 Table architecture

logs_footprint_mp (raw JSON)  +  kyc.* server tables
        │  flatten / parse
        ▼
  ① vew.kyc_event_flatten_di        DWD flatten table (one row per event)
        │  GROUP BY
        ▼
  ② ads_kyc_channel_funnel_di       per-channel success funnel
  ③ ads_kyc_failure_reason_di       failure reasons
  ④ ads_kyc_leave_reason_di         drop-off reasons
        +
  ⑤ dim_kyc_entry_point             entry-point code → business name (optional)
        +
  ⑥ dwd_kyc_apply_di                server apply mirror (TiDB tm_kyc_apply) — required for Bad KYC

Can we compute Bad KYC today? Bad KYC needs server completion (is_passed) which the front-end flatten table doesn't have — so it comes from ⑥ dwd_kyc_apply_di (mirror of TiDB tm_kyc_apply). Server-only Bad KYC (per channel/day) = started − kyc_passed − cancel, computable once ⑥ is built. The full decomposition (incl. No-intent + client-attr filters) additionally needs the uid ↔ member_id join key (P0).

02 The three channels

Each channel has a different flow / pages, so funnels must be built per channel — the same event cannot be compared across channels.

03 Result tables

② Per-channel success funnel — ads_kyc_channel_funnel_di (tall)

Grain: dt × channel × combination dims × funnel_step. Metric: users (distinct uid; distinct session once available).

③ Failure reasons — ads_kyc_failure_reason_di

Grain: dt × channel × failure_source × failure_reason × dims. Sources: frontend_error (Error Type), server_callback (error code), audit_reject (tm_audit_log.memo), status_page (Page Code). Must split user cancel (Cancel) vs system error.

④ Drop-off reasons — ads_kyc_leave_reason_di

Grain: dt × channel × leave_source × leave_page × leave_reason × dims. Primary signal is Exit Bottom sheet Viewed — not the Leave-click event (most users just close the app).

03·b Schemas & sample data

Full DDL in analysis/schema-ddl.sql. ②③④ sample figures are illustrative (06-01 distribution); error_code / server values pending eng.

① vew.kyc_event_flatten_di — DWD flatten (partition dt · PARQUET)

Business fields: uid, event, entry_point, verification_type, kyc_flow_type, kyc_vendor, error_type, page_code, kyc_status, reason, page_dropped_from, cta_clicked, industry, renewal_flow, uri_path
Client dims: client_version, mp_version, os_version, device_brand, device_model, device_type, net_type, language, region, uid_region, ip_region, ip_isp, event_ts + partition dt (all STRING; event_ts BIGINT)

② vew.ads_kyc_channel_funnel_di — per-channel funnel (tall · partition dt)

Fields: channel, entry_point, kyc_flow_type, verification_type, client_version, mp_version, os_platform, region, net_type, step_order INT, step_name, users BIGINT, events BIGINT, dt

③ vew.ads_kyc_failure_reason_di — failure reasons (partition dt)

Fields: channel, failure_source, failure_reason, error_code, is_user_cancel BOOLEAN, entry_point, kyc_flow_type, verification_type, client_version, mp_version, region, users BIGINT, events BIGINT, dt

④ vew.ads_kyc_leave_reason_di — drop-off reasons (partition dt)

Fields: channel, leave_source, leave_page, leave_reason, entry_point, kyc_flow_type, client_version, mp_version, region, users BIGINT, dt

⑤ vew.dim_kyc_entry_point — entry-point dictionary (no partition · optional)

Fields: entry_point_code, entry_point_name, category, is_tech_source BOOLEAN, note

04 Counting rules

1. Use distinct users (distinct session once available) — front-end has 10–28% double-firing; raw event counts are not usable.
2. Completion & errors from server-side — front-end Completed does not fire on redirect flows.
3. Per-channel funnels differ — never compare the same event across channels.
4. Drop-off uses Exit-sheet — Leave-click count is severely under-reported.
5. Channel is known from step 1 (Verification Started), not at Flow Entered — the top Entered→Started drop is channel-agnostic.

05 Tracking TODO (for eng)

06 Dependencies — need eng sign-off before build

1. Channel rule: kyc_vendor + verification_type → 3 channels.
2. signzy milestone events.
3. Server-side error-callback table (channel / error_code / member_id / time).
4. Server completion source (can tm_kyc_apply carry channel / entry_point?).
5. session_id linkage to member_id / apply on the server.
6. Entry Point code → business-name dictionary (merge mp-ogold/mpogold, MANDATORY_KYC/_V2).
7. UAEPASS drop-off: does the server record it? (tr_leave_record excludes it.)