Adjust claims desk · Quillmark Mutual
World clock Mon 17:00:00 CET paused · 60×

Definitions

The claims terms the operator, its detectors and Ask all run on. Each has an owner, a version, SQL and tests. Edits make a new version.

Stalled claim

stalled_claim unit: claims operator detector no canonical variant: Ask returns all 2

An open claim nobody has moved for too long. Claims Operations and Customer Contact count it differently: one looks at the file (nothing done on it), the other at the customer (nobody has spoken to the claimant).

claimsHead of Claims Operations · claims v2

An open, assigned claim with nothing done on the file for stalled_days or more: no claim event (any event except the first report) and no outbound call, email or text. A file waiting for documents from the claimant (an awaiting_claimant event with no reply since) is not stalled. The operator's detector uses this meaning.

latest change: Files waiting for the claimant no longer count; the clock restarts when the claimant replies.

  • pass count_betweenobserved 0, expected between 0.0 and 400.0
  • pass uniqueobserved {'duplicates': 0}, expected claim_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH activity AS (
  SELECT claim_id, max("at") AS last_at, arg_max(type, "at") AS last_type FROM claim_events
  WHERE "at" <= TIMESTAMP 'Mon 17:00:00' AND type <> 'reported' GROUP BY claim_id
  UNION ALL
  SELECT claim_id, max("at"), 'outbound ' || arg_max(channel, "at") FROM contacts
  WHERE "at" <= TIMESTAMP 'Mon 17:00:00' AND direction = 'outbound' GROUP BY claim_id
),
last AS (SELECT claim_id, max(last_at) AS last_at, arg_max(last_type, last_at) AS last_type FROM activity GROUP BY claim_id),
waiting AS (
  SELECT claim_id, arg_max(type, "at") AS state FROM claim_events
  WHERE type IN ('awaiting_claimant', 'claimant_replied') AND "at" <= TIMESTAMP 'Mon 17:00:00' GROUP BY claim_id
),
caseload AS (
  SELECT adjuster_id, count(*) AS open_claims FROM claims
  WHERE assigned_at <= TIMESTAMP 'Mon 17:00:00' AND (closed_at IS NULL OR closed_at > TIMESTAMP 'Mon 17:00:00') GROUP BY adjuster_id
)
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, c.policy_id, c.adjuster_id,
       l.last_at + INTERVAL (10) DAY AS started_at,
       CASE WHEN a.left_at <= TIMESTAMP 'Mon 17:00:00' THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'claim_no', c.claim_no, 'line', c.line, 'peril', c.peril, 'last_activity_at', l.last_at,
         'last_activity', l.last_type, 'days_quiet', date_diff('day', l.last_at, TIMESTAMP 'Mon 17:00:00'),
         'stalled_days', 10, 'adjuster', a.name, 'team_id', c.team_id,
         'adjuster_status', CASE WHEN a.left_at <= TIMESTAMP 'Mon 17:00:00' THEN 'left the company'
                                 WHEN a.leave_from <= TIMESTAMP 'Mon 17:00:00' AND a.leave_to > TIMESTAMP 'Mon 17:00:00' THEN 'on leave'
                                 ELSE 'working' END,
         'adjuster_open_claims', coalesce(k.open_claims, 0), 'adjuster_caseload_cap', a.caseload_cap
       ) AS evidence
FROM claims c
JOIN last l ON l.claim_id = c.id
LEFT JOIN waiting w ON w.claim_id = c.id
LEFT JOIN adjusters a ON a.id = c.adjuster_id
LEFT JOIN caseload k ON k.adjuster_id = c.adjuster_id
WHERE c.assigned_at <= TIMESTAMP 'Mon 17:00:00' AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
  AND l.last_at <= TIMESTAMP 'Mon 17:00:00' - INTERVAL (10) DAY
  AND coalesce(w.state, '') <> 'awaiting_claimant'
ORDER BY started_at, c.id
serviceHead of Customer Contact · service v1

An open, assigned claim whose claimant nobody has spoken to (a call, email or text either way) for stalled_days or more, whatever is happening on the file. Files waiting for the claimant count too: that is when customers feel forgotten.

latest change: First version.

  • pass count_betweenobserved 1550, expected between 100.0 and 6000.0
  • pass uniqueobserved {'duplicates': 0}, expected claim_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 1550}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH spoken AS (
  SELECT claim_id, max("at") AS last_at FROM contacts
  WHERE "at" <= TIMESTAMP 'Mon 17:00:00' AND party = 'claimant' GROUP BY claim_id
)
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, c.adjuster_id,
       s.last_at + INTERVAL (10) DAY AS started_at, 'low' AS severity,
       json_object('claim_no', c.claim_no, 'last_claimant_contact_at', s.last_at,
                   'days_without_contact', date_diff('day', s.last_at, TIMESTAMP 'Mon 17:00:00')) AS evidence
FROM claims c
JOIN spoken s ON s.claim_id = c.id
WHERE c.assigned_at <= TIMESTAMP 'Mon 17:00:00' AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
  AND s.last_at <= TIMESTAMP 'Mon 17:00:00' - INTERVAL (10) DAY
ORDER BY started_at, c.id

Parameters

change one, see what changes today, then save it as a new version with a note
now 10 · used by claims, service

History

append-only; nothing is edited in place
  1. definitionHead of Claims Operations claims v2 Files waiting for the claimant no longer count; the clock restarts when the claimant replies.
  2. definitionHead of Claims Operations claims v1 First version: 14 days.
  3. definitionHead of Customer Contact service v1 First version.