⚠️ Mock / prototype — numbers are a real server snapshot (last 7–14d, hardcoded, not live). Each metric shows its definition + SQL. Filters illustrative.
KYC — Bad-KYC Analysis
Bad KYC = reached Verification but no server-side completion, excl. user cancel · flow_type = channel
Server · last 7–14d
Filters Country All ▾ Botim ver. All ▾ MP ver. All ▾ Platform All ▾ Channel All ▾ apply across all 3 tabs · grouping & filtering
1 · Basic data
2 · Funnels
3 · Reasons
Bad KYC definition: an attempt that reached Verification Started (intent shown) but did not reach server-side completion, and was not a user cancel. Server completion = journey_flag = 1 (KYC_FLOW) or step IN ('DOWN','PRE_EID_EXPIRY'). Bad KYC rate = (initiated − completed) / initiated (distinct member; session-level once session_id lands).
Bad KYC rate (avg)
~41%
last 14d · daily 39–46%
Initiated / day
~15.3k
distinct members
Completion (all)
~59%
passed / initiated
Worst channel
SIGNZY
53.7% bad vs UAE_KYC 34.5%
1.0 Order decomposition (MECE)A+B+C+D = 100%
Order outcome splitall orders
Definition / SQL
Definition: every KYC order falls in exactly one bucket. A = no-intent (front-end: Entered & not Started). B = Bad KYC (server: applied, not completed, not cancel). C = risk/audit reject (audit status=R). D = final success (completed & passed audit). A+B+C+D = 100%.
-- B/C/D (server). A needs front-end + uid↔member_id join.
SELECT
  COUNT(DISTINCT member_id) AS has_intent,
  COUNT(DISTINCT CASE WHEN step IN ('DOWN','PRE_EID_EXPIRY') OR journey_flag=1
                       THEN member_id END) AS kyc_passed   -- C+D
FROM kyc.tm_kyc_apply
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);
-- B = has_intent - kyc_passed ; C = audit reject ; D = kyc_passed - C
Decomposition detailillustrative
A · No-intent~40%
B · Bad KYC (pre-audit)~25%
C · Risk/audit reject~0.4%
D · Final success~35%
Biggest losses = No-intent (~40%) + Bad KYC (~25%). Manual audit (C) is tiny (~2k/month). A & intent split are front-end; B/C/D server — precise merge needs uid↔member_id.
1.1 Daily Bad-KYC ratetm_kyc_apply
Definition / SQL
Definition: daily Bad-KYC rate = (initiated − completed) / initiated. initiated = distinct members with an apply that day; completed = journey_flag=1 or step∈(DOWN,PRE_EID_EXPIRY).
SELECT DATE(create_time) d,
       COUNT(DISTINCT member_id) AS initiated,
       COUNT(DISTINCT CASE WHEN step IN ('DOWN','PRE_EID_EXPIRY')
                            OR journey_flag=1 THEN member_id END) AS completed
FROM   kyc.tm_kyc_apply
WHERE  create_time >= DATE_SUB(NOW(), INTERVAL 14 DAY)
GROUP  BY DATE(create_time);
-- bad_rate = (initiated - completed) / initiated
1.2 Bad-KYC by channelflow_type · 7d
Definition / SQL
Definition: per-channel (flow_type) Bad% = 1 − completed/initiated.
SELECT flow_type,
       COUNT(DISTINCT member_id) AS initiated,
       COUNT(DISTINCT CASE WHEN step IN ('DOWN','PRE_EID_EXPIRY')
                            OR journey_flag=1 THEN member_id END) AS completed
FROM   kyc.tm_kyc_apply
WHERE  create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP  BY flow_type;
-- bad% = 1 - completed/initiated
1.3 Daily initiated vs completeddistinct members
Definition / SQL
Definition: distinct members initiating KYC per day (initiated) vs those completing (completed) — same two columns as 1.1.
SELECT DATE(create_time) d,
       COUNT(DISTINCT member_id) AS initiated,
       COUNT(DISTINCT CASE WHEN step IN ('DOWN','PRE_EID_EXPIRY')
                            OR journey_flag=1 THEN member_id END) AS completed
FROM   kyc.tm_kyc_apply
WHERE  create_time >= DATE_SUB(NOW(), INTERVAL 14 DAY)
GROUP  BY DATE(create_time);
1.4 New vs Renew shareapply_type
Definition / SQL
Definition: distinct members split by apply_type: KYC_FLOW = new user, EID_RENEW_FLOW = renewal.
SELECT apply_type,
       COUNT(DISTINCT member_id) AS users
FROM   kyc.tm_kyc_apply
WHERE  create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP  BY apply_type;
2.1 UAE Pass funnelUAEPASS · 7d
Initiated19,353
▼ out-of-app DV (UAE Pass)
DV success10,955 56.6%
DV fail9,385 48.5%
▼
✓ Passed (server)10,622 54.9%
Definition / SQL
Definition: Initiated/Passed from the apply table (flow_type=UAEPASS); DV success/fail from the UAE Pass monitor table (status S/F). passed = step∈(DOWN,PRE_EID_EXPIRY).
-- initiated & passed
SELECT COUNT(DISTINCT member_id) AS initiated,
       COUNT(DISTINCT CASE WHEN step IN ('DOWN','PRE_EID_EXPIRY')
                            THEN member_id END) AS passed
FROM   kyc.tm_kyc_apply
WHERE  flow_type='UAEPASS'
  AND  create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- DV success / fail (out-of-app monitor)
SELECT status, COUNT(DISTINCT member_id) AS users
FROM   outman.t_uaepass_monitor_event
WHERE  main_type='UAEPASS_DV_MONITOR'
  AND  create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP  BY status;   -- S=success, F=fail, I=init
UAE Pass DV failure breakdownmonitor · message
Definition / SQL
Definition: UAE Pass DV failures (status='F') split by message, distinct members. 94% = job check timeout.
SELECT message, COUNT(DISTINCT member_id) AS users
FROM   outman.t_uaepass_monitor_event
WHERE  status='F'
  AND  create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP  BY message
ORDER  BY users DESC;
2.2 UAE KYC funnelUAE_KYC_FULL · 7d
Users by current stepstep · in-app
Definition / SQL
Definition: UAE_KYC_FULL users by current step, distinct members (OCR=scan, ICA=gov check, LIVEPIC=liveness, DOWN=done). Step-level failures live in tr_biz_record_*.
SELECT step, COUNT(DISTINCT member_id) AS users
FROM   kyc.tm_kyc_apply
WHERE  flow_type='UAE_KYC_FULL'
  AND  create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP  BY step
ORDER  BY users DESC;
Top UAE KYC failuresresult_msg
Definition / SQL
Definition: UAE_KYC_FULL failures (status=-1) split by result_msg, distinct members.
SELECT result_msg, COUNT(DISTINCT member_id) AS users
FROM   kyc.tm_kyc_apply
WHERE  flow_type='UAE_KYC_FULL' AND status=-1
  AND  create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP  BY result_msg
ORDER  BY users DESC LIMIT 10;
2.3 Audit funneltm_audit_task · 30d
Entered audit2,016
▼
✓ Pass (S)886 44.0%
✗ Reject (R)1,120 55.6%
In progress (I)10
Definition / SQL
Definition: tasks entering manual audit by status (S=pass, R=reject, I=in-progress); Entered = S+R+I. Reject reasons in reason_msg.
SELECT status, COUNT(DISTINCT member_id) AS users
FROM   kyc.tm_audit_task
WHERE  create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP  BY status;   -- S=pass, R=reject, I=in-progress
Audit reject reasonsreason_msg · status=R
Definition / SQL
Definition: rejected audit tasks (status=R) split by reason_msg, distinct members. Portrait Inspection Failed dominates (~66%).
SELECT reason_msg, COUNT(DISTINCT member_id) AS users
FROM   kyc.tm_audit_task
WHERE  status='R'
  AND  create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP  BY reason_msg
ORDER  BY users DESC;
3.1 Failure reason distributionresult_msg · 7d
KYC failure reasons (status = -1)distinct users
Definition / SQL
Definition: all failed applies (status=-1) split by result_msg, distinct members (across channels).
SELECT result_msg, COUNT(DISTINCT member_id) AS users
FROM   kyc.tm_kyc_apply
WHERE  status = -1
  AND  create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP  BY result_msg
ORDER  BY users DESC;
3.2 User cancel / leave intenttr_leave_record · 30d
Leave reasonsdistinct users
Definition / SQL
Definition: distribution of the reason users pick when leaving/cancelling (distinct members); split further by leave_type (main/renew).
SELECT reason, leave_type,
       COUNT(DISTINCT member_id) AS users
FROM   kyc.tr_leave_record
WHERE  create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP  BY reason, leave_type
ORDER  BY users DESC;
Explicit cancel signalsgive-up / reject
"User give up journey"2,554
UAE Pass user-reject739
Not comfortable: ID3,660
Not comfortable: selfie1,173
Definition / SQL
Definition: explicit cancel signals — apply "User give up" (result_msg), UAE Pass user-reject (monitor message), not comfortable sharing ID/selfie (leave reason). These are excluded from Bad KYC (genuine user cancel, not a system failure).
-- give up (apply)
SELECT COUNT(DISTINCT member_id) FROM kyc.tm_kyc_apply
WHERE result_msg LIKE 'User give up%' AND create_time>=DATE_SUB(NOW(),INTERVAL 7 DAY);
-- UAE Pass user reject (monitor)
SELECT COUNT(DISTINCT member_id) FROM outman.t_uaepass_monitor_event
WHERE status='F' AND message='user reject' AND create_time>=DATE_SUB(NOW(),INTERVAL 7 DAY);
-- not comfortable (leave)
SELECT reason, COUNT(DISTINCT member_id) FROM kyc.tr_leave_record
WHERE reason LIKE 'I''m not comfortable%' GROUP BY reason;
Server tables: kyc.tm_kyc_apply · outman.t_uaepass_monitor_event · tm_audit_task · tr_leave_record ·Build spec ↗