Finding the Payment-Delay Bottleneck in the Tail

Goal

When you get a payment delay report, you become able to split it by segment and percentile rather than one lump of time and pinpoint the place to fix, and to read a defect in the review rules from the denial reason codes.

Why it matters

It is common for the payment delay dashboard to be within target while complaints keep coming in. That is because the people who file complaints are not the people at the mean. The mean is low because there are many short cases, not because there are no long ones.

And to fix the delay, you have to get down to which segment ate the time. Nobody can fix "20 days from intake to payment," and only "18 days in investigation" leads to saying let's fix the investigation assignment rules. If the intake-to-assignment segment is long it is a staffing problem, and if the decision-to-payment segment is long it is a settlement system problem, so a report that does not split by segment throws the work to the wrong team.

The definitions used in this lab are pinned down here.

백분위수   오름차순 정렬 후 ceil(p × n ÷ 100) 번째 값 (최근접 순위법)
평균       합을 건수로 나눈 몫. 소수점은 버린다
지급기일   접수 후 240시간
지연일수   (접수→지급 시간 − 240) ÷ 24 의 몫. 음수면 0
지연이자   지급액 × 54 × 지연일수 ÷ 365000 의 몫 (연 5.4% 일할, 원 단위 내림)

Amounts are handled as integer won from start to finish. If you compute in floating point, reconciliation is off by one won each time, and finding the cause of that one won takes days.

Steps

Save the generator in the example on the right as /root/ins/make_claim.py and run it with python3 make_claim.py. It creates 200 claims, 965 rows of stage history, and 8 appeals.
Write the counts per stage, five lines (stage_received= … stage_paid=), plus ledger_decided= and gap= to /root/ins/funnel.txt, and leave the cases not recorded in the ledger in /root/ins/funnel_gap.csv as claim_no,decided_ts.
Write the per-segment elapsed time of the claims that went through to payment to /root/ins/latency.csv as claim_no,assign_h,investigate_h,review_h,pay_h,total_h.
Write five lines to /root/ins/percentile.txt: n=, mean_h=, p50_h=, p90_h=, p99_h=.
Pick only the cases whose total_h is at or above p90 and write seven lines to /root/ins/bottleneck.txt: tail_n=, tail_total_h=, tail_assign_h=, tail_investigate_h=, tail_review_h=, tail_pay_h=, tail_investigate_pct=.
Write the counts per denial reason code to /root/ins/denial.csv as code,denied,appealed,overturned, and write three lines to /root/ins/denial.txt: wrongful_denied=, wrongful_claimed_amount=, wrongful_code=.
Write the late-payment interest for the cases that passed the payment due date to /root/ins/interest.csv as claim_no,over_days,paid_amount,interest.
Write a report in /root/ins/claim_report.md with five sections: ## 확인한 것, ## 지연은 어디서 생기는가, ## 부당 부지급, ## 권고, ## 반출하지 않은 것 (the five Korean section titles mean: what was checked, where the delay arises, wrongful denials, recommendations, and what was not exported).

Notes

Get the integer-hour difference between two timestamps with CAST(ROUND((julianday(a)-julianday(b))*24) AS INTEGER).
When SQLite divides integers, only the quotient is left. All the calculations in this lab use that property.
Common mistake 1: including denied cases in step 3. A case with no payment time cannot have its elapsed time measured.
Common mistake 2: counting only the cases above p90 in step 5. The definition is p90 "or above."
Common mistake 3: dividing first in the interest calculation. If you compute it as 지급액 ÷ 365000 × 54 × 일수 (the placeholders are the paid amount and the number of days), most come out 0.
Common mistake 4: copying diagnosis codes into the report. What you look at in an investigation and what you send out as a document are different things.

Generate the claim data

Save the generator in the example on the right as /root/ins/make_claim.py and run it with python3 make_claim.py. It creates 200 claims, 965 rows of stage history, and 8 appeals.

Save the generator in the lab instructions as /root/ins/make_claim.py and run it with python3. If you edit it by hand, the numbers in the later steps all go off.

Count the stage table and the ledger separately

Write the counts per stage, five lines (stage_received= … stage_paid=), plus ledger_decided= and gap= to /root/ins/funnel.txt, and leave the cases not recorded in the ledger in /root/ins/funnel_gap.csv as claim_no,decided_ts.

Count the same "number of decisions" in two places, claim_stage and claim.decision. If the two numbers split, the difference is the cases nobody is touching.

Split the elapsed time into four segments

Write the per-segment elapsed time of the claims that went through to payment to /root/ins/latency.csv as claim_no,assign_h,investigate_h,review_h,pay_h,total_h.

All the stage timestamps are on the hour, so multiplying the julianday difference by 24 and rounding gives exact integer hours. Only the claims that went through to payment are targets.

Expose the tail hidden behind the mean

Write five lines to /root/ins/percentile.txt: n=, mean_h=, p50_h=, p90_h=, p99_h=.

Take percentiles by the nearest-rank method — sort in ascending order and take the ceil(p × n ÷ 100)-th value. For the mean, take only the quotient of the sum divided by the count.

Pinpoint which segment the tail's time went to

Pick only the cases whose total_h is at or above p90 and write seven lines to /root/ins/bottleneck.txt: tail_n=, tail_total_h=, tail_assign_h=, tail_investigate_h=, tail_review_h=, tail_pay_h=, tail_investigate_pct=.

Pick only the cases whose total_h is at or above p90 and sum them per segment. The share is the investigation segment's sum times 100 divided by the overall sum, taking the quotient.

Sort out denial reasons and overturned cases

Write the counts per denial reason code to /root/ins/denial.csv as code,denied,appealed,overturned, and write three lines to /root/ins/denial.txt: wrongful_denied=, wrongful_claimed_amount=, wrongful_code=.

Looking only at counts per code, they look evenly spread. If you put the number of appeals and the number overturned on the same line, you can see that they are concentrated in one code.

Compute late-payment interest as integer won

Write the late-payment interest for the cases that passed the payment due date to /root/ins/interest.csv as claim_no,over_days,paid_amount,interest.

The payment due date is 240 hours after intake. The number of overdue days is the quotient of (elapsed time − 240) divided by 24, and the interest is the quotient of paid amount × 54 × overdue days ÷ 365000. Do the division only once, at the end.

Write the delay and wrongful denial report

Write a report in /root/ins/claim_report.md with five sections: ## 확인한 것, ## 지연은 어디서 생기는가, ## 부당 부지급, ## 권고, ## 반출하지 않은 것 (the five Korean section titles mean: what was checked, where the delay arises, wrongful denials, recommendations, and what was not exported).

It needs five sections. The last section is the place to state what you did not put in the report. This data contains diagnosis codes.