Sample E-commerce Orders Dataset

What’s in the file

20 columns over 50,000 rows. Distinct counts and null rates are measured from the file itself, not estimated.

Things worth noticing

• Q4 carries 37.7% of the year: 18,864 of the 50,000 orders land in October, November and December. A forecast fitted on Q1 will under-call November badly, which is the point of testing against it.
• 12,854 customers placed the 50,000 orders, and the 7,408 who came back three or more times account for $7.76M of the $9.36M in order value — 82.9% of revenue from 57.6% of buyers.
• Average order value runs from $32.83 in grocery to $507.78 in electronics. The blended figure, $187.20, describes none of the six categories, which is exactly the trap a category breakdown is meant to catch.
• 4.0% of orders are refunded and refund_amount is populated only on those. Every other row is a true zero rather than a missing value in disguise — so a naive AVG and a considered one give different answers, on purpose.

Put an agent on it

Anything you were about to do with this file by hand is a job that runs every week forever. Each of these is a working prompt — open it and it is already loaded.

Every Monday at 8am, read the orders data for the last complete week.

Compare it against the four weeks before it and report:
- Total order value, order count and average order value, each with the week-over-week change
- The breakdown by channel, and any channel whose share moved more than 3 percentage points
- The breakdown by product_category, flagging any category down more than 10% on its four-week average
- Refund rate for the week, and whether it is outside the normal range

Write it as five sentences a founder can read on their phone. Lead with whatever actually changed — if nothing did, say so in one line instead of padding. Post it to #revenue.

Every day, calculate the refund rate (refunded orders / total orders) for the trailing 7 days, overall and split by product_category and product_sku.

Compare each against its own trailing 30-day rate. Alert only when:
- A category's 7-day refund rate is more than 1.5x its 30-day rate AND has at least 20 orders behind it
- Or any single SKU has 3+ refunds in 7 days

For each alert include the SKU or category, both rates, the order counts, and the total dollars refunded. If nothing breaches, post nothing at all — do not send a daily "all clear".

Build a monthly cohort analysis from the orders data.

Group customers by the month of their first order. For each cohort report:
- Number of customers
- Total and average order value in month 0, 1, 2, 3, 6
- Percentage of the cohort that placed a second order, and the median days between first and second
- Which acquisition channel the cohort came from, and whether channel predicts repeat rate

Call out the two cohorts with the strongest and weakest repeat behaviour and what distinguishes them. Deliver it as a table plus three sentences of interpretation, on the first of every month.

Load it yourself

The file is a plain parquet. Nothing here is specific to us.

-- No download step: DuckDB reads the parquet over HTTP range requests.
INSTALL httpfs;
LOAD httpfs;

CREATE VIEW sample_ecommerce_orders AS
SELECT * FROM read_parquet('https://cotera.co/datasets/sample-ecommerce-orders.parquet');

SELECT * FROM sample_ecommerce_orders ORDER BY order_id LIMIT 20;

More datasets