Quick-commerce dark store ops to data analyst: pick and ETA data

The shift handover report as a checklist

Most dark stores have some version of a handover sheet or daily ops summary. Go through yours line by line. For each item, tick whether you could compute it yourself from raw orders, and write the question behind it.

• Orders handled this shift. Can you count them from an order table and split them by hour? Question: when does demand actually peak, and does the staffing roster follow it?
• Average pick time. Can you compute it from pick-start and pick-end timestamps, and would you report the average or the median? Question: is a slow shift slow for every order, or dragged by a few very long picks?
• Orders dispatched late against the promised window. Do you know which timestamp the promise is measured from? Question: how much of the delay is pick and pack, and how much is waiting for a rider?
• Items marked out of stock or substituted. Can you list the SKUs? Question: are the same SKUs going out of stock at the same hour, which points to a replenishment timing problem rather than bad luck?
• Rider wait at the store. Can you separate time waiting for the order from time waiting for a rider? Question: which side of the handoff is the bottleneck in each hour?
• Picker attendance and productivity. Items per picker-hour. Question: does productivity drop late in the shift, and is that fatigue or a harder order mix?
• Remarks column. The free-text notes ("chiller door issue", "cold chain delay"). Question: can these be tagged into categories so they can be counted?

Every unticked box is a skill you need. Every question is a potential portfolio project.

A worked example: is the evening slowdown about pick time or stock-outs?

The query below uses synthetic orders for one hypothetical store. It computes, by hour, the order count, the median pick time in seconds, and how many orders had a stock-out. SQLite has no built-in median, so the query ranks pick times within each hour and takes the middle value (the average of the two middle values when the count is even). It runs as written.

-- Synthetic dark store orders (hypothetical data)
CREATE TABLE orders (order_id TEXT, placed_hour INTEGER, pick_seconds INTEGER, had_stockout INTEGER);
INSERT INTO orders VALUES
 ('O1', 18, 95, 0), ('O2', 18, 110, 0), ('O3', 18, 102, 0), ('O4', 18, 130, 1),
 ('O5', 19, 140, 0), ('O6', 19, 125, 0), ('O7', 19, 310, 1), ('O8', 19, 135, 0), ('O9', 19, 290, 1),
 ('O10', 20, 150, 0), ('O11', 20, 145, 0), ('O12', 20, 160, 0), ('O13', 20, 155, 0);

WITH ranked AS (
  SELECT placed_hour, pick_seconds, had_stockout,
         ROW_NUMBER() OVER (PARTITION BY placed_hour ORDER BY pick_seconds) AS rn,
         COUNT(*) OVER (PARTITION BY placed_hour) AS n
  FROM orders
)
SELECT placed_hour,
       MAX(n) AS orders,
       AVG(CASE WHEN rn IN ((n + 1) / 2, (n + 2) / 2) THEN pick_seconds END) AS median_pick_s,
       ROUND(AVG(pick_seconds), 1) AS mean_pick_s,
       SUM(had_stockout) AS stockout_orders
FROM ranked
GROUP BY placed_hour
ORDER BY placed_hour;

The output tells a more careful story than an average would:

At 7 pm the mean jumps to 200 seconds, but the median is 140; two orders with stock-outs took around five minutes each and pulled the average up. At 8 pm there are no stock-outs, yet the median is the highest of the evening. So there are two separate problems: stock-outs at 7 pm, and a general slowdown at 8 pm that stock-outs do not explain. That might be fewer pickers, tired pickers or larger baskets, and your next query would test which. Being able to say "these are two different problems" is exactly what an analyst is for.

The gap between reading dashboards and building them

Ops roles in quick commerce are data-heavy but often consume numbers rather than produce them. What to add:

• SQL beyond single tables: joining orders to inventory and rider logs, window functions like the one above.
• A BI tool such as Power BI, with a proper model instead of one flat export.
• Enough statistics to know when a one-evening difference is noise.
• Writing up a finding in three sentences for a city manager, with the chart that supports it.

The Data Analyst programme at /courses/da covers these over three to four months, live online with optional in-person labs at our Noida centre.

Ops analyst inside the same kind of company

Aim first for analyst roles close to operations: operations analyst, supply chain analyst, or business analyst on a fulfilment or category team in e-commerce or retail. Your knowledge of how a pick timestamp gets created, and when it lies, is worth more there than in a general analytics role. For a related path, see supply chain executive to supply chain analyst.

Your answer to "why move from ops to analytics?"

"I have managed evening shifts at a dark store for two years, and every handover we argued about why 7 to 9 pm was slow. I started pulling the order data myself and learned SQL to answer it properly. On a synthetic version of our data I showed that the 7 pm slowdown was driven by a few stock-out orders, while 8 pm was slow across the board, which are two different fixes. I want to do that analysis full time. I know the floor, so I know which numbers to trust and which ones are artefacts of how pickers scan items."

Use no real company data in any public portfolio; build synthetic tables with the same shape, and say so in the README.