The current state in numbers

01The problem

Everyone at Shieldline has a theory. The agency manager blames the claims desk; the claims desk blames agents; IT blames the old system. Before you test any theory, measure the problem itself. Where is it worst, and for whom? A number that varies a lot between groups is a clue. A number that's the same everywhere points to something shared, such as the process itself.

02The concept

Cut the headline by every dimension you have

Average days to settle is 25.2. Break it down by channel (how the claim came in), region and claim type. Look for the groups that stand out and the ones that don't.

Look at every outcome, not just the happy one

Paid claims are only part of the story. Claims that are withdrawn (closed because the customer stopped responding) are a failure the average hides.

Customers' own words

Complaints tell you what customers experience, which isn't always what the business measures. A claim "in progress" in the system can feel like silence to the customer.

03Example

Days to settle by channel:

SELECT channel,
       COUNT(*) AS paid_claims,
       ROUND(AVG(julianday(closed_at) - julianday(submitted_at)), 1) AS avg_days,
       ROUND(100.0 * AVG(julianday(closed_at) - julianday(submitted_at) > 30), 1) AS pct_over_30
FROM claims
WHERE outcome = 'Paid' AND submitted_at < '2026-04-01'
GROUP BY channel
ORDER BY avg_days DESC;

channel  paid_claims  avg_days  pct_over_30
Agent            688      28.7         37.2
Phone            304      25.4         20.1
Branch           551      22.8         13.8
Web              361      22.3         11.9

Agent claims are the slowest, by about six days compared with the web. By region and claim type:

SELECT region,
       ROUND(AVG(julianday(closed_at) - julianday(submitted_at)), 1) AS avg_days
FROM claims
WHERE outcome = 'Paid' AND submitted_at < '2026-04-01'
GROUP BY region
ORDER BY avg_days DESC;

region         avg_days
Port Harcourt      27.7
Lagos              24.9
Kano               24.9
Ibadan             24.5
Abuja              24.5

SELECT claim_type,
       COUNT(*) AS paid_claims,
       ROUND(AVG(claim_amount_ngn)) AS avg_amount,
       ROUND(AVG(julianday(closed_at) - julianday(submitted_at)), 1) AS avg_days
FROM claims
WHERE outcome = 'Paid' AND submitted_at < '2026-04-01'
GROUP BY claim_type
ORDER BY avg_days DESC;

claim_type       paid_claims  avg_amount  avg_days
Theft                    177     6044661      31.1
Third party              303     1256865      24.8
Windscreen               578      282673      24.6
Accident damage          846      803901      24.6

Port Harcourt is about three days slower than everywhere else. Theft claims are slower, which is reasonable: they're large and need checks. But look at windscreens. A ₦280,000 windscreen claim takes as long as a ₦800,000 accident claim. Something in the process treats every claim the same, whatever its size.

What customers complain about:

SELECT reason,
       COUNT(*) AS complaints,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM complaints), 1) AS pct
FROM complaints
GROUP BY reason
ORDER BY complaints DESC;

reason                 complaints   pct
Delay                         305  50.7
No update on my claim         176  29.3
Settlement amount              78    13
Staff attitude                 42     7

Half the complaints are about delay. Another 29% are about not knowing what's happening, which is a separate problem with a separate fix: even a slow claim feels better when the customer is kept informed.

04Walkthrough

1. Run the queries, or build the same in a pivot table or Power BI.
2. Break down the outcomes (paid, rejected, withdrawn) by channel. Which channel has the most withdrawals?
3. Calculate the share of baseline claims that received at least one complaint.
4. Plot average days to settle by month of submission. Is the problem getting better or worse?
5. Write the current state summary (the task below).

05Practice

06Check your understanding