Insurance operations to claims analyst: turnaround and leakage data

Pick your first project from the desk you sit at

Choose one row. Trying to cover claims, renewals and underwriting in one portfolio dilutes the thing that makes you credible, which is depth in one process.

The motor claims version, worked end to end

This synthetic claims register has the columns a motor claims desk would recognise. The query answers two questions at once: how long each stage takes, and whether any garage has an unusual pattern of estimates.

CREATE TABLE claims (
  claim_no TEXT, garage TEXT, intimated TEXT, surveyed TEXT,
  approved TEXT, settled TEXT, estimate REAL, approved_amt REAL);

INSERT INTO claims VALUES
 ('MC-001','Sharma Motors','2026-08-01','2026-08-02','2026-08-05','2026-08-09',42000,36000),
 ('MC-002','Sharma Motors','2026-08-03','2026-08-06','2026-08-12','2026-08-20',65000,52000),
 ('MC-003','City Auto Works','2026-08-04','2026-08-05','2026-08-07','2026-08-10',28000,26500),
 ('MC-004','Sharma Motors','2026-08-10','2026-08-14','2026-08-22','2026-09-01',88000,61000),
 ('MC-005','City Auto Works','2026-08-11','2026-08-12','2026-08-14','2026-08-18',31000,29000),
 ('MC-006','Rana Garage','2026-08-12','2026-08-13','2026-08-16','2026-08-21',47000,44000),
 ('MC-007','Sharma Motors','2026-08-18','2026-08-21','2026-08-29','2026-09-08',73000,50000);

-- Average days per stage
SELECT ROUND(AVG(julianday(surveyed) - julianday(intimated)), 1) AS to_survey,
       ROUND(AVG(julianday(approved) - julianday(surveyed)), 1)  AS to_approval,
       ROUND(AVG(julianday(settled)  - julianday(approved)), 1)  AS to_settlement
FROM claims;

-- Garage-level pattern: how far approvals fall below estimates
SELECT garage,
       COUNT(*) AS claims,
       ROUND(AVG(estimate), 0) AS avg_estimate,
       ROUND(100.0 * SUM(estimate - approved_amt) / SUM(estimate), 1) AS pct_cut_by_surveyor
FROM claims
GROUP BY garage
ORDER BY pct_cut_by_surveyor DESC;

On this invented data the approval-to-settlement stage takes longest on average (about 6 days), survey-to-approval is next, and Sharma Motors has the highest average estimate and the largest share of its estimates cut by surveyors, roughly a quarter. City Auto Works and Rana Garage sit well under 10%.

Read the result carefully before calling it leakage

This is where your operations experience earns its place. A high cut percentage does not prove anything improper. You know the ordinary explanations:

• The garage may handle more severe accidents, so estimates include parts that are later found repairable.
• Depreciation on parts is applied at approval, which reduces the approved amount regardless of the garage.
• A few large claims can dominate a small sample; four claims is not a pattern.

So the analyst's finding is not "Sharma Motors is inflating estimates". It is "Sharma Motors shows larger estimate reductions and slower movement through approval and settlement on this sample; check whether that holds on a bigger period and after adjusting for depreciation and claim severity". Writing that sentence is what separates analysis from accusation, and interviewers for claims roles will test whether you can.

The gaps between processing and analysis

• Thinking in populations. Processing trains you to judge one file at a time. Analysis asks what is true across thousands, and when a difference is too small to trust.
• Joining the right tables. Real claims data sits across policy, claim, payment and survey tables. You will need joins, and you will need to spot when a join duplicates rows.
• Dates done properly. Working days, holidays and reopened claims all change turnaround figures. Decide your rule, write it down and apply it consistently.
• One BI tool. A Power BI page with stage-wise turnaround and a garage filter is a stronger deliverable than a static table.

What to say when asked why you are leaving operations

"I have processed motor claims for three years, so I know each stage from intimation to settlement and where files usually get stuck. I kept noticing patterns across files that nobody measured, like payment release after approval taking longer than the survey itself. I learned SQL and built a claims turnaround and garage-pattern analysis on synthetic data. The most useful part was writing down what the numbers do not prove, for example that a high estimate reduction can come from depreciation or severity rather than anything improper. I want a claims analyst role where I can do that across the whole book, not one file at a time."

Your next move

Pick one row from the table and build that project first. Check how your resume reads to a screener with the free skill check, and see the Data Analyst programme if you want structured SQL, Power BI and AI-verification practice; a counsellor can share current fees and batch dates. For another operations-to-analyst route using queue data, compare the bank operations guide.