Car dealership service retention after the free services end

The question from the service manager

"Our workshop loading is flat even though sales grew last year. Are the new owners not coming back?" To answer it you need retention by the month the car was sold (a cohort), by model, and separated from vehicles that simply have not reached their paid-service date yet.

What the DMS export contains

Dealer management systems differ by manufacturer, but two tables are near-universal:

vehicle_sales — vin, sale_date, model, customer_id, customer_pincode.

repair_orders — ro_no, vin, ro_date, ro_type (free_service, paid_periodic, running_repair, bodyshop, accessory_fitment), odometer_km, labour_amount, parts_amount.

The service schedule itself — at which month or kilometre each free and paid service is due — comes from the manufacturer's service booklet and varies by brand and model. Treat it as a parameter. The example below assumes the first paid periodic service should happen within 15 months of sale, which is a hypothetical window you should replace with your own.

Vehicles sold, vehicles due, or vehicles serviced once?

Mixing rows 1 and 3 is common: a car serviced at a sister dealership of the same brand is lost to you but retained by the network. Know which one your target is measured on.

Cohort retention in SQLite (synthetic data)

CREATE TABLE vehicle_sales (vin TEXT, sale_date TEXT, model TEXT);
CREATE TABLE repair_orders (ro_no TEXT, vin TEXT, ro_date TEXT, ro_type TEXT);

INSERT INTO vehicle_sales VALUES
 ('V01','2025-01-10','Hatch'), ('V02','2025-01-22','Hatch'), ('V03','2025-01-28','SUV'),
 ('V04','2025-02-05','SUV'),   ('V05','2025-02-14','Hatch'), ('V06','2025-02-20','SUV'),
 ('V07','2025-03-03','Hatch'), ('V08','2025-03-18','SUV'),   ('V09','2025-08-09','Hatch'),
 ('V10','2025-08-30','SUV');

INSERT INTO repair_orders VALUES
 ('R01','V01','2025-02-12','free_service'), ('R02','V01','2026-01-15','paid_periodic'),
 ('R03','V02','2025-03-01','free_service'), ('R04','V02','2025-11-20','bodyshop'),
 ('R05','V03','2025-03-02','free_service'), ('R06','V03','2026-02-10','paid_periodic'),
 ('R07','V04','2025-03-10','free_service'), ('R08','V05','2025-03-20','free_service'),
 ('R09','V05','2026-02-25','paid_periodic'), ('R10','V05','2026-02-25','accessory_fitment'),
 ('R11','V06','2025-04-01','free_service'), ('R12','V07','2025-04-02','free_service'),
 ('R13','V07','2026-08-20','paid_periodic'), ('R14','V08','2025-04-25','free_service'),
 ('R15','V08','2026-03-01','paid_periodic'), ('R16','V09','2025-09-15','free_service');

WITH params AS (SELECT '2026-09-27' AS as_of, 15 AS window_months),
eligible AS (
  SELECT v.*, strftime('%Y-%m', sale_date) AS cohort,
         date(sale_date, '+' || window_months || ' months') AS window_end
  FROM vehicle_sales v, params
  WHERE date(sale_date, '+' || window_months || ' months') <= as_of),
returned AS (
  SELECT DISTINCT e.vin
  FROM eligible e JOIN repair_orders r ON r.vin = e.vin
  WHERE r.ro_type = 'paid_periodic' AND r.ro_date <= e.window_end)
SELECT cohort, model,
       COUNT(*) AS eligible_vehicles,
       SUM(vin IN (SELECT vin FROM returned)) AS returned_paid,
       ROUND(100.0 * SUM(vin IN (SELECT vin FROM returned)) / COUNT(*), 0) AS retention_pct
FROM eligible
GROUP BY cohort, model
ORDER BY cohort, model;

Across the eight eligible vehicles, four returned for a paid service within the window: 50%. Three details in the data drive that answer:

• V09 and V10, sold in August 2025, are excluded. Their 15-month window ends in November 2026. Counting them as "not returned" would drag retention down with cars that are not yet due.
• V02's bodyshop visit does not count. It appears in the RO table, and a query filtering on "any RO after the free service" would have scored it as retained.
• V07 came back in August 2026, 17 months after sale, outside the window. It is a late return, not a retained service by this definition. Keep a second column for "returned at any time" so late returns stay visible.

Other ways the rate comes out wrong

• Counting ROs instead of vehicles. V05 has two ROs on one day (service plus accessory fitment). Always DISTINCT vin.
• Kilometre-based schedules. Heavy-use vehicles reach the paid service early by kilometres; commercial and taxi-registered cars can reach it months before a private car. If your manufacturer's schedule is "months or km, whichever first", use odometer readings on free-service ROs to estimate each vehicle's due date.
• Relocated owners. A car sold in Noida and now driven in Pune will not come back, and that is not a service-quality failure. Pincode on later ROs at other dealers (if the manufacturer shares network data) tells you how large this group is.
• Tiny cohorts. With one to two vehicles per cell, the percentages above are for illustration only. Real reports need a quarterly cohort or a rolling twelve months to be stable.

Taking it to the dealer principal

The principal wants to know how many customers are being lost and whether anyone is calling them. Give three things: retention for the latest fully eligible quarter against the previous four quarters; the model-level split, if a model is clearly below the others; and a call list of eligible vehicles with no paid service, sorted by how recently their window closed. The call list is the only item that turns into revenue this month, so put it first in the meeting and the trend chart second.

Cohort windows and right-censored denominators come up constantly in the Data Analyst programme. If you already work in a dealership, sales executive to sales analyst covers how to present this kind of workshop analysis as career evidence.