# Query results (generated)

Produced by `python/run_analysis.py`. Synthetic data.

## Validation

### v01_row_counts_match_expected

| check_name | failures |
|---|---|
| disputes = 1200 | 0 |

### v02_orphan_steps

| check_name | failures |
|---|---|
| every step belongs to a dispute | 0 |

### v03_orphan_chargebacks

| check_name | failures |
|---|---|
| every chargeback belongs to a dispute | 0 |

### v04_step_sequence_contiguous

| check_name | failures |
|---|---|
| step_seq runs 1..n with no gaps | 0 |

### v05_case_starts_with_intake_ends_with_notification

| check_name | failures |
|---|---|
| case starts with intake and ends with notification | 0 |

### v06_closed_at_matches_last_step

| check_name | failures |
|---|---|
| closed_at equals the last step completion | 0 |

### v07_steps_in_time_order

| check_name | failures |
|---|---|
| each step starts after the previous one ends | 0 |

### v08_rework_reason_consistent

| check_name | failures |
|---|---|
| rework rows have a reason, others do not | 0 |

### v09_chargeback_iff_merchant_wait

| check_name | failures |
|---|---|
| chargeback row exists exactly when the case waited on the merchant | 0 |

### v10_incomplete_intake_has_document_request

| check_name | failures |
|---|---|
| intake_complete = 0 exactly when a document request exists | 0 |

### v11_wait_type_matches_step

| check_name | failures |
|---|---|
| external waits sit only on external steps | 0 |

## Analysis

### q01_volume_by_type

| dispute_type | cases | pct_of_cases | avg_amount_cad |
|---|---|---|---|
| unauthorized_fraud | 380 | 31.7 | 206.18 |
| not_received | 237 | 19.8 | 111.64 |
| not_as_described | 212 | 17.7 | 138.49 |
| duplicate_charge | 137 | 11.4 | 47.57 |
| incorrect_amount | 121 | 10.1 | 62.34 |
| cancelled_recurring | 113 | 9.4 | 31.69 |

### q02_volume_by_channel

| channel | cases | pct_of_cases |
|---|---|---|
| phone | 534 | 44.5 |
| app | 365 | 30.4 |
| web | 212 | 17.7 |
| branch | 89 | 7.4 |

### q03_lead_time_decomposition

| avg_touch_hours | avg_internal_queue_hours | avg_customer_wait_hours | avg_merchant_wait_hours | avg_total_days | internal_pce_pct | pct_total_time_in_internal_queues |
|---|---|---|---|---|---|---|
| 1.21 | 135.0 | 49.2 | 525.6 | 29.6 | 0.89 | 19.0 |

### q04_internal_lead_time_percentiles

| median_internal_days | p90_internal_days | median_total_days | p90_total_days |
|---|---|---|---|
| 5.4 | 8.6 | 29.8 | 47.3 |

### q05_queue_wait_by_step

| step_name | step_events | avg_wait_hours | median_wait_hours | p90_wait_hours | total_wait_hours |
|---|---|---|---|---|---|
| investigation | 1123 | 50.4 | 40.5 | 93.5 | 56647.0 |
| chargeback_filing | 1123 | 28.5 | 23.7 | 50.1 | 32011.0 |
| triage | 1200 | 23.3 | 19.5 | 41.3 | 27949.0 |
| final_decision_notification | 1200 | 18.7 | 15.7 | 33.9 | 22487.0 |
| provisional_credit_decision | 1123 | 6.9 | 6.0 | 11.5 | 7742.0 |
| document_request | 438 | 9.2 | 8.0 | 15.3 | 4050.0 |

### q06_rework_pareto

| rework_reason | cases | pct_of_rework_cases | cumulative_pct | rework_touch_hours | avg_delay_days_per_case |
|---|---|---|---|---|---|
| wrong_category | 158 | 38.7 | 38.7 | 57.0 | 2.0 |
| incomplete_documents | 130 | 31.9 | 70.6 | 15.0 | 3.8 |
| rekeying_error | 68 | 16.7 | 87.3 | 11.0 | 0.8 |
| customer_unreachable | 52 | 12.7 | 100.0 | 4.0 | 1.0 |

### q07_rework_rate_by_channel

| channel | cases | cases_with_rework | rework_rate_pct |
|---|---|---|---|
| app | 365 | 84 | 23.0 |
| branch | 89 | 25 | 28.1 |
| phone | 534 | 189 | 35.4 |
| web | 212 | 52 | 24.5 |
| all | 1200 | 350 | 29.2 |

### q08_handoffs_per_case

| avg_handoffs_per_case | max_handoffs |
|---|---|
| 6.02 | 11 |

### q09_weekly_rework_p_chart

| week_no | cases | rework_cases | p | p_bar | ucl | lcl |
|---|---|---|---|---|---|---|
| 1 | 61 | 20 | 0.328 | 0.292 | 0.466 | 0.117 |
| 2 | 56 | 17 | 0.304 | 0.292 | 0.474 | 0.109 |
| 3 | 39 | 12 | 0.308 | 0.292 | 0.51 | 0.073 |
| 4 | 43 | 7 | 0.163 | 0.292 | 0.5 | 0.084 |
| 5 | 47 | 6 | 0.128 | 0.292 | 0.491 | 0.093 |
| 6 | 41 | 15 | 0.366 | 0.292 | 0.505 | 0.079 |
| 7 | 41 | 14 | 0.341 | 0.292 | 0.505 | 0.079 |
| 8 | 44 | 13 | 0.295 | 0.292 | 0.497 | 0.086 |
| 9 | 43 | 14 | 0.326 | 0.292 | 0.5 | 0.084 |
| 10 | 56 | 15 | 0.268 | 0.292 | 0.474 | 0.109 |
| 11 | 42 | 11 | 0.262 | 0.292 | 0.502 | 0.081 |
| 12 | 45 | 18 | 0.4 | 0.292 | 0.495 | 0.088 |
| 13 | 41 | 15 | 0.366 | 0.292 | 0.505 | 0.079 |
| 14 | 43 | 16 | 0.372 | 0.292 | 0.5 | 0.084 |
| 15 | 54 | 7 | 0.13 | 0.292 | 0.477 | 0.106 |
| 16 | 46 | 8 | 0.174 | 0.292 | 0.493 | 0.091 |
| 17 | 38 | 14 | 0.368 | 0.292 | 0.513 | 0.07 |
| 18 | 44 | 11 | 0.25 | 0.292 | 0.497 | 0.086 |
| 19 | 61 | 22 | 0.361 | 0.292 | 0.466 | 0.117 |
| 20 | 34 | 9 | 0.265 | 0.292 | 0.526 | 0.058 |
| 21 | 47 | 12 | 0.255 | 0.292 | 0.491 | 0.093 |
| 22 | 37 | 12 | 0.324 | 0.292 | 0.516 | 0.067 |
| 23 | 43 | 8 | 0.186 | 0.292 | 0.5 | 0.084 |
| 24 | 50 | 16 | 0.32 | 0.292 | 0.485 | 0.099 |
| 25 | 42 | 15 | 0.357 | 0.292 | 0.502 | 0.081 |
| 26 | 62 | 23 | 0.371 | 0.292 | 0.465 | 0.118 |

### q10_weekly_investigation_queue

| week_no | investigation_events | avg_investigation_wait_hours |
|---|---|---|
| 1 | 57 | 57.4 |
| 2 | 50 | 47.0 |
| 3 | 37 | 43.3 |
| 4 | 39 | 50.8 |
| 5 | 46 | 58.7 |
| 6 | 36 | 45.8 |
| 7 | 39 | 46.4 |
| 8 | 41 | 47.1 |
| 9 | 42 | 51.3 |
| 10 | 53 | 48.5 |
| 11 | 38 | 42.9 |
| 12 | 40 | 37.7 |
| 13 | 40 | 44.5 |
| 14 | 43 | 100.6 |
| 15 | 46 | 47.1 |
| 16 | 41 | 45.1 |
| 17 | 38 | 43.0 |
| 18 | 42 | 44.8 |
| 19 | 58 | 46.5 |
| 20 | 34 | 43.7 |
| 21 | 45 | 52.0 |
| 22 | 33 | 51.1 |
| 23 | 42 | 55.8 |
| 24 | 43 | 46.1 |
| 25 | 40 | 58.4 |
| 26 | 60 | 49.6 |

### q11_aging_against_56_days

| age_band | cases | pct_of_cases |
|---|---|---|
| 0-14 days | 114 | 9.5 |
| 15-30 days | 497 | 41.4 |
| 31-45 days | 420 | 35.0 |
| 46-56 days | 154 | 12.8 |
| over 56 days | 15 | 1.3 |

### q12_small_value_effort

| value_band | cases | pct_of_cases | total_disputed_cad | specialist_hours | pct_of_specialist_hours |
|---|---|---|---|---|---|
| $50 and over | 933 | 77.8 | 142701.0 | 710.0 | 80.1 |
| under $50 | 267 | 22.3 | 9109.0 | 176.0 | 19.9 |

### q13_miscategorization_by_channel

| channel | cases_reaching_investigation | miscategorized | miscategorized_pct |
|---|---|---|---|
| branch | 77 | 15 | 19.5 |
| phone | 500 | 91 | 18.2 |
| app | 347 | 34 | 9.8 |
| web | 199 | 18 | 9.0 |

### q14_touch_hours_by_team

| team_name | touch_hours | rework_touch_hours | rework_share_pct |
|---|---|---|---|
| Dispute Analysts | 611.0 | 42.0 | 6.8 |
| Chargeback Team | 274.0 | 11.0 | 4.1 |
| Customer Resolution | 186.0 | 4.0 | 2.3 |
| Contact Centre | 183.0 | 0.0 | 0.0 |
| Disputes Triage | 136.0 | 16.0 | 11.6 |
| Disputes Operations | 66.0 | 15.0 | 22.9 |

### q15_outcome_by_type

| dispute_type | cardholder_favour | merchant_favour | withdrawn |
|---|---|---|---|
| cancelled_recurring | 63 | 45 | 5 |
| duplicate_charge | 114 | 13 | 10 |
| incorrect_amount | 89 | 25 | 7 |
| not_as_described | 105 | 96 | 11 |
| not_received | 144 | 75 | 18 |
| unauthorized_fraud | 310 | 44 | 26 |

### q16_touch_by_step

| step_name | first_pass_events | avg_touch_minutes |
|---|---|---|
| intake | 1200 | 9.1 |
| triage | 1200 | 6.0 |
| document_request | 438 | 7.0 |
| final_decision_notification | 1200 | 9.1 |
| investigation | 1123 | 26.4 |
| provisional_credit_decision | 1123 | 4.0 |
| chargeback_filing | 1123 | 14.0 |

### q17_table_counts_and_external_waits

| process_steps | chargebacks | avg_merchant_response_days | avg_first_customer_wait_days |
|---|---|---|---|
| 9664 | 1123 | 23.4 | 4.6 |

### q18_time_per_case_by_step

| step_name | wait_type | avg_wait_hours_per_case | avg_touch_hours_per_case |
|---|---|---|---|
| intake | none | 0.0 | 0.152 |
| triage | internal_queue | 25.22 | 0.114 |
| document_request | internal_queue | 4.34 | 0.055 |
| final_decision_notification | internal_queue | 19.81 | 0.155 |
| investigation | internal_queue | 51.5 | 0.447 |
| await_customer_documents | customer | 49.24 | 0.0 |
| provisional_credit_decision | internal_queue | 6.45 | 0.062 |
| chargeback_filing | internal_queue | 27.7 | 0.228 |
| await_merchant_response | merchant_network | 525.63 | 0.0 |