Meiram runs ads for a small online store for children's goods in Almaty. He uploaded an export of orders and their line items to an AI tool, asked for revenue and got 20,000 ₸. The order register had one order, 101, for 10,000 ₸. After the join with two item rows, the order total repeated on both rows. The program ran without errors, but the store did not get any richer.
The records and amounts in this lesson are made up. The mistake happened before anyone started explaining the result: Meiram summed a money column without finding out what a single row now meant. AI can help write the calculation and point out suspicious spots. But the source data, the metric definitions and a reconciliation that can be repeated are the marketer's job, before recommending anything to the client.
Joining rows does not create revenue
Source register
Order 101: 10,000 ₸
One order
After the join
Two item rows
10,000 ₸ repeats on both rows
Total reconciliation
20,000 ₸ from summing rows: an error
Actual order total: 10,000 ₸
Before summing, establish what each transformed row represents.
Ask a question a calculation can answer
"Find insights" leaves the model to choose the goal and the definitions. For an advertising decision, say what to compare: for example, revenue from completed paid orders over the selected periods, in one currency, with an agreed treatment of refunds. Have the assistant list, before it calculates anything, which conditions it does not know and what the answer depends on.
The question has a unit of counting. One customer can have several orders, one order can hold several items, one payment may cover only part of the amount. These are different tables, and you cannot swap one for another. A column called "amount" does not tell you whose amount it is: the item's, the order's or a money transfer's.
The task includes statuses, currency, time zone and period boundaries. Agree as well which date counts: order creation, payment or fulfilment. If one report counts by payment and another by creation, their totals will differ simply because they answer different questions. Settle these definitions with the report owner before looking for the reason sales fell.
Ask for three things as output: a comparison table, the calculation itself and a list of unknowns. The client then sees where the number came from and which decisions it is too early to make. One confident sentence about why sales fell does not replace that.
Reproduce the join on one order
The register has one record: order 101, total 10,000 ₸. The order contents have two records, product A and product B. Joining on order number gave this table:
| Order | Item | Full order total |
|---|---|---|
| 101 | Product A | 10,000 ₸ |
| 101 | Product B | 10,000 ₸ |
A row now describes an item, while the amount still belongs to the whole order. So 10,000 + 10,000 = 20,000 ₸ counts the same money twice. Revenue for this order is 10,000 ₸, as in the register.
A correct order table is enough for the overall total. Revenue by product needs item amounts, discounts and refunds, or a justified allocation rule. Without them, do not split the order evenly and present the result as actual revenue per product.
Deleting identical amounts is risky too: two different orders can cost the same. Reconciliation goes by order number and by what the record means; an amount is not an identifier. If amounts differ within one order number, work out the order versions or transactions. Picking a convenient row and moving on is the worst option.
Keep controls before and after each transformation
After every join, look at three numbers: rows, unique orders and the money total at the right level. List the keys with no match and the keys with several matches. Lost orders understate the total, repeated ones inflate it.
The pandas guide to merging shows how duplicate keys behave and describes the validate parameter, which checks the expected relationship between tables. It will catch a mismatch between the structure and what you expected. What the column means for the business is still yours to decide before the check.
A one-order-to-many-items relationship is normal; you need it to analyse basket contents. But even when it passes a technical check, an order total cannot stand in for an item amount. Code can run without errors and still give a wrong metric, so check both.
Sometimes the control total is supposed to change: the team removed cancelled orders or deducted agreed refunds. Then record the reason and how much the total changed. If money vanished without explanation, stop the conclusion that depends on it and go back to the transformation.
Give the assistant a working order
Compare revenue for the selected periods using the agreed definitions. Before calculating, describe the row unit and the table keys. Identify unknown conditions and do not draw conclusions that depend on them.
Show row counts, unique orders, unmatched keys and control totals before and after transformations. Explain expected changes in the total. Keep the filters and the executed calculation.
Describe separately the observed difference, possible explanations and the next check. Do not replace missing values with zeros and do not invent data.
The prompt sets the order, but you can only trust code you ran and its output. If the tool really executed the calculation, save the data, the calculation and the validation output. "Everything reconciles" without that material is no reason to change a budget.
The control example should be small enough to check by hand. Ours fits order numbers and amounts into one table. After the fix, run it alongside the full calculation: the assistant can explain the error correctly in words and still leave the old summing in the working code.
Keep the change in sales apart from its explanation
After reconciliation, revenue may really turn out lower. Break the difference into number of orders and average order value, then look at statuses, refunds and product mix. Compare groups over the same observation period, especially while some orders are still unfulfilled.
Say the AI writes confidently: "sales fell because of the new ad". So far there is nothing to back that sentence. Stock, price, tracking setup or audience may have changed on the same days. If different people saw the ads, comparing their sales mixes the ad copy with who the buyers were. So before deciding, check the definitions, filters and calculation against the source data, list the other explanations, keep the cause as a hypothesis and note what data would help test it.
The outcome of an analysis can be the next task: reconcile cancellations after a warehouse failure, restore a missing source or compare like-for-like groups. That helps the client too, even if there is no need to switch off a channel right now. End-to-end analytics will help link ad spend to confirmed order results.
Share only the data you need
For practice, synthetic records like our order are enough. In a live project, agree the fields and the processing environment with whoever is responsible for the data. If the task is about amounts and statuses, the name, phone number and the customer's messages may not be needed at all.
Removing direct contact details exposes less, but a person can still be identified through other attributes and links. Replacing a name with an ID helps join rows but does not settle access questions. Who may receive the data, where it is stored and when temporary files are deleted is decided by the team in advance, and the assistant gets only the dataset the task needs.
Exercise: correct the total and define a check
Orders 101 and 102 are each 10,000 ₸. The first has two items, the second one. The join produced three rows of 10,000 ₸. Work out the correct revenue and explain why neither summing every row nor deleting identical amounts works. Then write down the row unit and the control total for one table in your own project.
The correct total is 20,000 ₸ for two orders. Summing the three rows gives 30,000 ₸, an extra 10,000 ₸. Delete repeated amounts, and you are left with 10,000 ₸ and the second order is gone. A working fix keeps one amount per order number or uses the real item amounts, then reconciles against the source register.
In your own project, the review should end with a result that can be repeated: which data you took, how you transformed it and which total you confirmed. The reasons for a change in sales are discussed after that reconciliation, and the other explanations stay on the list until there is enough data.