A marketer uploaded orders and their contents to an AI tool, asked for revenue and received 20,000 ₸. The source register held only order 101 for 10,000 ₸. Joining it to two item rows repeated the total on both. The program ran, but the business had not received more money.
All records and amounts in this lesson are illustrative. The mistake happened before anyone explained the result: the team summed a money column without establishing what each transformed row represented. AI can help write calculations and locate suspicious areas. The marketer still needs source data, metric definitions and a reproducible reconciliation before recommending action to a client.
Ask a question a calculation can answer
“Find insights” lets the model choose the goal and definitions. For an advertising decision, specify what to compare: for example, revenue from completed paid orders over selected periods, in one currency and with agreed refund treatment. Before calculating, the assistant should list unknown conditions that affect the answer.
The question has a unit of measurement. One buyer may place several orders, one order may contain several items, and one payment may cover only part of the amount. Tables describing these records are not interchangeable. A column named “amount” does not tell you whether it refers to an item, order or transfer of funds.
The task description includes statuses, currency, time zone and period boundaries. Specify whether the date means creation, payment or fulfilment. If one report uses payment and another order creation, their difference may follow from the questions chosen. Agree those definitions with the report owner before seeking a reason for falling sales.
Ask for three things as output: a comparison table, the calculation and a list of unknowns. The client can then see where the number came from and which decisions remain premature. One persuasive sentence about why sales fell does not replace that deliverable.
Reproduce the join with one order
The order register has one record: number 101, total 10,000 ₸. Its contents have two records, product A and product B. Joining by order number produces this table:
| Order | Item | Full order total |
|---|---|---|
| 101 | Product A | 10,000 ₸ |
| 101 | Product B | 10,000 ₸ |
A row now represents an item, while the amount still belongs to the whole order. Adding 10,000 + 10,000 = 20,000 ₸ therefore counts the same money twice. The correct revenue in this order's register is 10,000 ₸.
A correct order table is enough for the overall total. Revenue by product requires item amounts, discounts and refunds, or a justified allocation rule. Without that information, do not divide the order equally and present the result as actual revenue for each product.
Removing identical amounts is dangerous too. Different orders may have the same value. Reconciliation depends on the order identifier and the meaning of the record; an amount cannot replace an identifier. If one identifier has different totals, investigate versions or transactions instead of choosing a convenient row.
Keep controls before and after transformations
For each join, record row count, unique order count and a monetary total at the correct level. Show unmatched keys and keys with several matches separately. Lost orders may understate the result; repeated orders may inflate it.
The pandas guide to merging explains duplicate keys and the validate parameter for checking an expected relationship. This control detects a mismatch between the structure and the stated expectation. The business meaning of the column still needs to be established first.
One order connected to many items is a valid relationship needed for basket analysis. Even when it passes a technical check, an order total cannot be used as an item amount. Successful code execution and a correct business metric therefore need separate checks.
A changed control total is sometimes expected: the team excluded cancelled orders or deducted agreed refunds. Retain the reason and amount of that change. Unexplained missing money requires pausing the dependent conclusion and returning to the transformation.
Give the assistant a working sequence
Compare revenue for the selected periods using the agreed definitions. Before calculating, describe what a row represents and the table keys. Identify unknown conditions and do not make 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 executed calculation.
Separate the observed difference, possible explanations and next check. Do not replace missing values with zero or invent data.
The prompt sets the order, but you can only trust code you have run and its result. If the tool executed calculations, retain the data used, the calculation and validation output. “Everything reconciles” without those materials is no basis for changing a budget.
The control example should be small enough to reconcile manually. Our identifiers and amounts fit in a table. After fixing it, run it alongside the full calculation: an assistant can explain the error correctly while leaving the old summation method in the working code.
Separate changes in sales from explanations
After reconciliation, revenue may indeed be lower. Break the difference into order count and average order value, then examine statuses, refunds and product mix. Groups need equal observation periods, especially while some orders remain unfulfilled.
“The new ad caused it” requires additional evidence. Stock, price, tracking or audience may have changed at the same time. If advertisements reached different people, comparing sales combines the creative with buyer composition. Retain a possible cause as a hypothesis and name the evidence needed to assess it.
Analysis may lead to a task for the next step: reconcile cancellations after a warehouse failure, restore a missing source or compare equivalent groups. That is useful to the client even when switching off a channel immediately is unwarranted. End-to-end analytics connects advertising costs to confirmed order outcomes.
Share only the data needed
A set of synthetic records like our order works for practice. In a live project, agree the fields and processing environment with the data owner. A task about amounts and statuses may not need names, phone numbers or correspondence at all.
Removing direct contact details reduces exposed information, but other attributes and links may still identify someone. Replacing a name with an identifier helps join rows without settling every access question. The team separately defines permitted recipients, storage and deletion of temporary files; the assistant receives only the dataset required for the task.
Exercise: correct the total and define a check
Orders 101 and 102 are each worth 10,000 ₸. The first has two items and the second one. Joining produces three rows of 10,000 ₸. Calculate the correct revenue and explain why neither summing every row nor removing identical amounts works. Then record the row meaning and control total for one table in your project.
The correct result is 20,000 ₸ for two orders. Summing three rows produces 30,000 ₸, an extra 10,000 ₸. Removing repeated amounts leaves 10,000 ₸ and loses the second order. A working correction restores one correct total per order number or uses correct item amounts, then reconciles against the original register.
For your project, the review ends with a reproducible result: which data was used, how it was transformed and which total was confirmed. Discuss the reason for a sales change after that reconciliation, retaining alternative explanations until there is sufficient evidence.