Retail Sales Analysis

A young shop assistant in a cyan apron stands in a store stockroom holding a cardboard box and looking at its barcode label. Three more boxes sit on the bottom shelf of a metal rack beside him, with magenta, cyan and yellow bands behind.

Analyzing product sales with AI works in three steps: put numbers in a prompt, get a draft, check the arithmetic. AI drafts the explanation and a check plan and helps phrase a question for the analyst. A spreadsheet or script checks the math. In this training example, one product's monthly total nearly tripled, while sale invoices rose 59.2 percent.

Before reading the table: positive entries and C-corrections are summed with sign, so the total is less than the sum of sales. Physical return of goods is verified separately against accounting documents. The average positive document amount is the sum of positive entries divided by the number of positive documents; a typical document requires a distribution.

What the Data Shows for Product 85123A

The total for product 85123A, after cancellations, grew from 4756.05 to 14119.80 GBP - growth of 196.9 percent. The number of sale invoices (the table calls them positive documents) increased from 157 to 250, which is 59.2 percent. The average invoice became larger: from 30.33 GBP in October to 56.55 GBP in November. Money grew faster than the number of documents, so something beyond volume alone is at work.

The data comes from an export of a historical British online gift shop covering December 1, 2010 to December 9, 2011. This is a training example, not real client data. Source: Chen, D. (2015). Online Retail [Dataset]. UCI Machine Learning Repository, license CC BY 4.0.

Each row in the file describes one product line in one document: product code StockCode, description, quantity, date, price in pounds, invoice number InvoiceNo, customer identifier CustomerID, country. So one row is one line of an invoice, not a whole order or a customer.

A C-row is an accounting adjustment: it carries a negative quantity and a negative amount. It does not mean a confirmed physical return and may not cancel the original sale in the same month. An invoice number alone does not prove that goods were handed over.

The table includes only valid rows: positive price, non-zero quantity, filled-in invoice number and date. Duplicate rows are retained because no confirmed unique key exists.

Three terms used in the table: positive document - a sale invoice: its number has no letter C; signed total - the sum of positive documents plus negative cancellation rows; signed quantity - the same measure applied to units.

Metric October November
Rows 168 263
Distinct invoice numbers, including cancellations 159 254
Positive documents without cancellations 157 250
Known customer identifiers 127 191
Rows without customer identifier 16 21
Of which distinct documents 15 21
Units, positive documents 1689 4910
Cancelled quantity -2 -6
Signed quantity 1687 4904
Positive document total, GBP 4761.95 14136.70
Cancellation total, GBP -5.90 -16.90
Signed total, GBP 4756.05 14119.80

Without a distribution of document sizes, you cannot describe a typical purchase.

Rows without a customer identifier are a separate group: 16 in October (15 documents), 21 in November (21 documents). A missing identifier must not become one customer and cannot be excluded from the monetary total without an explicit note about the decision. Excluding these rows does not necessarily reduce the signed total because some may be negative adjustments.

How to Query AI to Analyze Product Sales

The query below contains all the numbers from the table, the constraints, and the format requirements. Copy it in full and paste it into the AI.

Product 85123A, retail online store, October and November 2011.
All countries, valid rows: positive price, non-zero quantity,
filled-in invoice number and date. Full duplicates preserved.

October:
- Rows: 168
- Positive documents without cancellations: 157
- Known customer identifiers in them: 127
- Rows without customer identifier: 16 (15 distinct documents)
- Units: 1689
- Cancelled quantity: minus 2, signed: 1687
- Positive total: 4761.95 GBP
- Cancellation total: minus 5.90, signed: 4756.05

November:
- Rows: 263
- Positive documents without cancellations: 250
- Known customer identifiers in them: 191
- Rows without customer identifier: 21 (21 distinct documents)
- Units: 4910
- Cancelled quantity: minus 6, signed: 4904
- Positive total: 14136.70 GBP
- Cancellation total: minus 16.90, signed: 14119.80

Constraints:
- Cancellations (C) are not matched to original documents.
- A missing customer identifier must not be treated as one customer.
- Data is for training, not current demand.
- The cause of growth is unknown.

Task:
1. Observations confirmed by the numbers above.
2. Testable explanations of the growth (at least three alternatives).
3. Priority next check: what to request, from whom, which fields.
4. Conclusions prohibited by these data.

Separate causes from observations. Do not assert causality without
distribution by documents, days, or customers. You may independently
recalculate percentages and averages from the numbers provided, with a check.
Do not introduce new quantities not present in the source data.
A hand holds a photo of the earlier shelf layout in front of a store shelf. The photo and the shelf show the same jam jars, leaf-patterned tea boxes and bottles, but in a different order. Price strips are plain coloured tabs.

How to Check the Draft and Verify Arithmetic

The sample below was written by hand as an illustration. This is not the result of an actual model run.

Observations:
- Signed total grew by 9363.75 GBP (196.9%).
- Signed quantity grew by 3217 units (190.7%).
- Positive documents: 250 versus 157 (growth 59.2%).
- Known customer identifiers: 191 versus 127.
- Total per positive document: 30.33 GBP in October, 56.55 in November.
- Units per document: 10.76 versus 19.64.
- Rows without customer identifier: 16 in October (15 documents),
  21 in November (21 documents). Separate group, not reducible to customers.

Alternative explanations:
1. Growth concentrated in a few large documents (wholesale purchases).
   Check: distribution of quantity by document, share of documents
   above 50 units.
2. Product was unavailable in October and became available in November.
   Check: daily stock levels, receipt dates, reservations.
   Availability at the month level does not rule out day-by-day differences.
3. A promotion may have run with exact dates and mechanics.
   Hypothesis, not confirmation. Check: promo calendar, channels,
   comparable products without the promotion.
4. Unit price changed.
   Check: UnitPrice distribution by day in both months.

Priority next check:
Document size and composition (distribution of units by invoice) and
daily product availability. Promo calendar requested if available.
Fields: date, end-of-day stock, receipts, reservations; for promo:
start/end date, channel, price, mechanics.

Prohibited conclusions:
- One specific buyer is responsible for the growth (need intersection
  of identifiers and distribution of amounts by them).
- The typical purchase became larger (need distribution by documents).
- Growth is the result of a promotion (need comparable days without promo).
- Product growth can be extrapolated to the entire store assortment.

Verify the arithmetic step by step:

1. Total difference: 14119.80 minus 4756.05 equals 9363.75. Divide by 4756.05 and you get 196.9%.
2. Quantity difference: 4904 minus 1687 equals 3217. Divide by 1687 and you get 190.7%.
3. Average document in October: 4761.95 divided by 157 equals 30.33 GBP.
4. Average document in November: 14136.70 divided by 250 equals 56.55 GBP.
5. Units per document in October: 1689 divided by 157 equals 10.76. In November: 4910 divided by 250 equals 19.64.

All numbers match the table.

The first check runs in two directions: document size and composition, and daily product availability. Request the promo calendar if it is available. Do not adjust procurement or the forecast until those data arrive.

The finished short memo for the manager reads:

The total for 85123A grew from 4756.05 to 14119.80 GBP, growth 196.9%. The average document grew from 30.33 to 56.55 pounds. The cause is unknown. First check: distribution of units by document and daily stock levels. I will request the promo calendar if it is available. Procurement and forecast stay untouched for now.

What the Analyst Checks Next

Stock and product availability. You need daily stock levels, receipts, and reservations. Zero sales on a specific day do not prove the absence of demand: the buyer may have gone elsewhere or postponed the purchase. Availability at the month level says nothing about specific days. If the product was available every day in both months, the availability hypothesis drops out and attention shifts to promotion or purchase composition. Comparing availability days provides a basis for analysis but does not control all factors simultaneously.

Promotion. Analysis requires exact dates, channels, price, and mechanics. Growth before and after a promotion does not isolate its effect: seasonality, competition, and customer composition also change in the same interval. Comparison with products without a promotion in the same period gives a basis for comparison. If no promotion ran, this factor is excluded.

Unit price. The price on a row is what the buyer actually paid. It does not show the list price or why a discount was given. If more large discounted packs were sold in November, the average price drops while total quantity grows. Revenue growth and demand growth are different things.

Cancellations (C-rows). C-rows are not matched to original sales in this data, so you cannot calculate a return rate from them. For accurate analysis you need to ask the accounting team for the linkage to the original document, the operation type, and the correction date. Until that data arrives, cancellations are included in the signed total but are not interpreted as confirmed returns.

Cost of goods. The export does not contain cost of goods sold. A supplier price list does not give cost of goods: it depends on the inventory valuation method and the accounting period. Standard IAS 2 distinguishes inventory cost from acquisition costs and costs of bringing goods to their present location and condition, and describes allocation methods - FIFO (first in, first out) and weighted average cost. The finance team must agree which cost applies to the units sold, which revenue scope is comparable to those costs, and how discounts and cancellations are treated. Margin cannot be recovered from this data.

Forecast. Forecasts are adjusted when a confirmed material event exists: a promotion, a holiday, or an assortment change. The justification is recorded. Month-over-month growth alone is not a reason for a manual upward revision - especially when the cause of growth is not established. For principled forecasting adjustments, see Forecasting: Principles and Practice.

Procurement. A review requires stock level, product route, lead time, minimum order quantity, reservations, and budget. None of these fields are in the export. The memo prepares an analytical question, not an auto-order.

Over a wooden shop counter, a hand in a yellow sleeve tears a long strip from a small white receipt printer while a hand in a magenta sleeve waits on the other side with an open palm. The strip carries only unreadable marks.

The Full Dataset: Thirteen Months and Four Row Classes

The UCI Online Retail file contains 541,909 rows covering December 1, 2010 to December 9, 2011. Processing was performed on October 4, 2026; a repeat independent calculation reconciled classes, months, the file checksum, and missing values. Four classes cover all rows with no remainder:

Class Rows Total, GBP
Positive documents without cancellations 530104 10666684.544
Cancellations (C) 9288 -896812.490
Negative without C 0 0
Excluded (non-positive price) 2517 0
Coverage check: 530104 + 9288 + 0 + 2517 = 541909.
Signed file total: 10666684.544 - 896812.490 = 9769872.054 GBP.

Monthly signed totals:

Month Rows Total, GBP
2010-12 42208 748957.020
2011-01 35007 560000.260
2011-02 27580 498062.650
2011-03 36502 683267.080
2011-04 29655 493207.121
2011-05 36785 723333.510
2011-06 36688 691123.120
2011-07 39330 681300.111
2011-08 35151 704804.630
2011-09 50066 1019687.622
2011-10 60504 1070704.670
2011-11 84445 1461756.250
2011-12 25471 433668.010

All thirteen months sum to 9769872.054 GBP - exactly the signed total. December 2011 is partial (through the ninth) and cannot be compared with full months. Even the full October and November are limited by their own calendar and seasonality.

135,080 rows have no customer identifier CustomerID, and 1,454 have no description. The UCI catalogue states there are no missing values; the cause of the discrepancy is unknown. For the monetary product total, the identifier is not required, so rows are retained. For counting unique customers, a missing value must not become one customer; the direction of bias in customer metrics is unknown. The file contains 5,268 full duplicates across eight fields - these are preserved. The combination of InvoiceNo, StockCode, and date cannot be treated as a unique key; the original row sequence number is useful for reconciliation.

Data: Chen, D. (2015). Online Retail [Dataset]. UCI Machine Learning Repository, https://doi.org/10.24432/C5BW33, license CC BY 4.0. Historical British online gift shop, many buyers are wholesale. Processing, filtering, and calculations performed by Majento; the data author does not confirm the conclusions.

Technical process: obtain the export, check the schema and period, tag the classes; multiply each price by 100,000 to work in whole numbers (fractions of a penny are kept, rounding happens only on output), multiply by signed quantity, aggregate and reconcile by classes, months, and total. The input file checksum confirms the same document was read but does not prove the calculation is correct.

How Teams Are Trained on This Task

Training is built around one skill: take a working product question and turn it into a verifiable memo for the manager.

The task is structured as follows:

  1. The manager poses a question about a specific product and names the acceptable options.
  2. The analyst calculates the data and prepares a table for reconciliation.
  3. Each participant reads the table independently without AI, then formulates a query, edits the draft, and verifies the arithmetic.
  4. The participant defends the memo before a colleague.

We judge the result by the quality of the decision, not by whether people liked the session.

A verification question for participants: the store's October total was 1,070,704.670 GBP on 60,504 rows; November was 1,461,756.250 GBP on 84,445 rows. The total grew 36.5 percent, rows grew 39.6 percent. The participant explains why product 85123A's growth cannot be extrapolated to the full assortment and names the first check. If growth is concentrated in large documents, the next step looks for wholesale buyers. If it is spread evenly, it looks for a general demand shift. Until the distribution is available, both possibilities remain open.

An indication for assessing skill: the participant independently transfers the process to a new product and a new period, goes through the full process without prompts, and either confirms a correct draft or finds and fixes an error or missing information. One completed task does not establish mastery.

Questions and answers

Can growth in the total prove that demand grew?

No. The total depends on quantity, unit price, and purchase composition. If more large packs or packs at a different price were sold in November, the total rises even with the same number of customers. To speak about demand growth you need the distribution of documents by size and a price comparison by day. Until that data is available, the correct observation is: the total grew and the average document became larger. The cause remains unknown.

What to do with documents that have no customer identifier?

Keep them as a separate group. A missing CustomerID must not become one customer, and these rows cannot be excluded from the monetary total without a note about the decision. For counting customers, use only rows with known identifiers - that is a lower bound, not the full audience. In October there were 16 rows without CustomerID (15 documents), in November 21 (21 documents). Excluding these rows does not necessarily reduce the signed total because some may be negative adjustments.

Why can't one product's sales growth be applied to the whole store?

A single product has its own dynamics: stock availability, price, promotion, and customer mix. In November 2011 the store as a whole grew 36.5 percent by total, while product 85123A grew 196.9 percent. These are different phenomena with different explanations. To extend a conclusion to the full assortment, you need a picture across all product groups, weighted by their different shares and dynamics.

How to work with cancelled records - C-rows?

A C-row is an adjustment: it carries a negative quantity and a negative amount. It does not mean a confirmed physical return and does not necessarily cancel the original sale in the same month. In this data, C-rows are not matched to original sales, so you cannot calculate a return rate from them. For accurate analysis, request from the accounting team: linkage to the original document, operation type, and correction date. Until then, cancellations are included in the signed total but are not interpreted as confirmed returns.

Why write constraints and prohibited conclusions in the AI query?

When you write "the cause of growth is unknown" and list prohibited conclusions, the draft comes out more precise and is easier to check. Without an explicit constraint, the model may write that a promotion caused the growth - even though there are no data on its dates or mechanics. Or that the typical purchase became larger - even though there is no distribution of documents by size. Explicit constraints make the draft cleaner and save verification time.

How to verify the model did not introduce numbers not in the query?

The query explicitly prohibits introducing new quantities. Check every number in the draft against the table: if you find a percentage with no corresponding source numbers, verify the arithmetic by hand - divide the difference by the base value. If the result does not match your calculation, tighten the query and try again. A good habit is to check at least two or three numbers in the draft, not just the final totals.

How to know the skill of analyzing product sales with AI is mastered?

The participant takes a new product, a new period, independently formulates a query with full data, receives a draft, checks the result, and defends the memo before a colleague. A correct answer is confirmed; an incorrect one is corrected - acceptance of a specific result. Independent transfer to a new question without a template gives grounds to assess the skill. One task does not prove mastery.

Where to find similar tasks for practice?

The AI training section on the Majento site collects materials on working with data and formulating analytical questions. The approach in this article applies to any product in any export: take two adjacent months, prepare a table with the same metrics, formulate a query using the same structure, and verify the arithmetic by hand. The same UCI Online Retail dataset works well for practice - hundreds of products with different dynamics.

How to Start Analyzing Your Own Product's Sales

Analyzing product sales with AI is a structured process. AI provides a draft explanation and a hypothesis structure; the human makes the decision based on verified numbers. The key skill is separating an observation from a conclusion.

Take your own product and run the analysis using the query above.

Contact us: Telegram or email.

Continue with

Sources

  1. doi.org publication
  2. creativecommons.org publication
  3. ifrs.org publication
  4. otexts.com publication

View as Markdown