--- id: varejo/vendas-diarias-loja-erp industry: Retail and e-commerce usage: widely used effort: medium grain: Store x day (plus invoice or receipt line item to break down by salesperson and product) --- # Daily sales by store and salesperson from the ERP Net revenue, average ticket, cancellations and returns by store, day and salesperson, on the right business day and matching the ERP close. ## Executive summary **For:** Retail chains with several stores or branches that sell through an ERP or POS. **What it solves** - Each store's revenue lands on the right business day, with no day zeroed out or shifted by time zone. - Gross sales, net sales, returns and future-delivery sales follow a single rule and match the ERP. - Each sale stays with the store and salesperson from when it happened. **Why it matters:** It is the number leadership looks at every day. One day zeroed out by a time zone error, or an invoice matched to the wrong store, destroys trust in the whole dashboard. It is the foundation for targets, salesperson commissions and any AI assistant that answers how much a store sold. **Business questions it answers** - How much did each store sell yesterday, net? - What is the average ticket per store this week, and how does it compare with last week? - How much were returns and cancellations this month, by store? - What is revenue by store and salesperson over the last 14 months? - Which store is at risk of missing this month's target, and how much does it need to sell per day? **What you need** - The ERP or POS connected to Nekt (Oracle or SAP database, or a cloud ERP) - The ERP operation codes: sale, future-delivery sale, return, shipment - Targets by store or product, if you want target vs. actual **What you get** - A table by store and day with gross, net, tickets and cancellations - A table by invoice line item with salesperson and product - Chain dashboard with target, actual and projection by store - Daily report of yesterday by store, via WhatsApp or email ## Detailed ### The problem The ERP stores the sale date as midnight UTC, and whoever converts the time zone pushes the sale to the previous day or zeroes out the day. Invoice numbers repeat across stores, and joining on the number alone mixes sales from different stores and salespeople. Sales, future-delivery sales, negative returns, cancellations and shipments all live in the same table, and every report adds them up differently. ### Business rules 1. **The ERP date is the business day.** In many ERPs and POS systems the sale date field is stored as midnight UTC representing the business date, and the real time sits in another field. Compute today and yesterday in the America/Sao_Paulo time zone and compare directly with that date, without converting the field. Converting to BRT moves midnight UTC to 9 p.m. on the previous day. (Avoid: Applying AT TIME ZONE 'America/Sao_Paulo' to the sale date field.) 2. **Write DATE, not TIMESTAMP, to the destination.** Destination databases and BI tools reinterpret a midnight UTC TIMESTAMP in the local time zone and show the previous day. Convert to DATE before sending. Check source by source: some bring a plain date, others bring the real time in UTC (e-commerce usually has the real time, and then you do convert to BRT). (Avoid: Sending the ERP TIMESTAMP to the BI Postgres and trusting the server time zone.) 3. **An invoice is store + number.** Invoice numbers are reused across locations: the same number in two stores is two different documents. Every join of invoices with line items, carts, returns or commissions uses the composite key (store, invoice number). Count invoices with a distinct count of the pair, not with a count of line item rows. (Avoid: JOIN on invoice number only.) 4. **Say whether it is gross or net.** Gross sales is the value of receipts before discounts and cancellations. Net sales = gross minus discounts minus cancellations. The two can differ by six figures in a single day at one store. Every published number states which one it is, and the question "how much did we sell" is answered with net. (Avoid: A "sales" column that does not say whether it is gross or net.) 5. **Each operation type has a sign.** Direct sales add. Future-delivery sales (invoiced for later delivery) are valid revenue and add. Returns come in with a negative value and are added, reducing the total on the return date. Shipments, transfers and bonus goods stay out: filter by the sales CFOPs (fiscal operation codes). Confirm the list of operation codes with finance. (Avoid: Filtering only direct sales and losing future-delivery sales, or excluding returns from net.) 6. **Average ticket uses valid receipts.** Average ticket = net sales / valid tickets. A valid ticket is a distinct receipt (store, POS, number) that was not cancelled. Average ticket is not additive: the chain's and the month's are recomputed from the sums, never as an average of daily tickets. (Avoid: Dividing by the count of line item rows, or averaging each store's ticket.) 7. **Salesperson with the store at the time.** Salespeople move between stores or help out at other locations. The store of the sale is the one on the document, and the salesperson x store relationship has a validity period; the salesperson's current store is never applied retroactively. Normalize the name (the ERP may add a " - 123" suffix with the code) and keep a null salesperson as "no salesperson", without dropping the sale. (Avoid: Joining salesperson to store through the current record and rewriting history.) 8. **The grain is in the name and in the upsert key.** The daily table has one row per store per day, and its name says so. The upsert key is exactly the grain (store + day). Each run reprocesses today and yesterday to catch late adjustments. A monthly table does not receive daily data. (Avoid: Merging by store and month in a table that receives daily rows.) 9. **Target vs. actual with projection.** Targets come from a spreadsheet by store or product and reference month. The load replaces only the items present in the spreadsheet for that month and keeps the rest. Actual is the month's net sales. Gap = target minus actual; required daily average = gap / remaining days; projection = actual / elapsed days x days in the month. (Avoid: Reloading the whole spreadsheet and wiping out targets from previous months.) ### How each operation enters revenue Confirm your ERP codes with finance. - **Direct sale** → Adds (`positive value`) - **Future-delivery sale (later delivery)** → Adds, it is valid revenue (`positive value`) - **Return** → Adds the negative value (`negative value, return date`) - **Discount** → Subtracts from gross (`gross - discounts`) - **Cancelled receipt** → Subtracts from gross and leaves valid tickets (`gross - cancellations`) - **Shipment, transfer, bonus goods** → Outside revenue (`CFOP not in the sales list`) - **Head office or DC without direct sales** → No sales is expected (`do not alert`) ### Definitions | Metric | Definition | |---|---| | Gross sales | sum of the gross value of sales receipts and invoices, by store and business day | | Net sales | gross sales minus discounts minus cancellations, with future-delivery sales added and returns negative, by store and business day | | Valid tickets | distinct count of non-cancelled receipts (store, POS, number), by store and day | | Average ticket | net sales / valid tickets in the requested cut; not additive, recomputed at every aggregation | | Returns | sum of the value of return operations (negative), by return date, by store and month | | Cancellation rate | cancelled receipts / total receipts, by store and period | | Invoices issued | distinct count of the pair (store, invoice number) in the period | | % attainment | month's net sales / month's target, by store or product | | Required daily average | (target minus actual) / remaining days in the month | ### Pitfalls - **High-sales day zeroed out.** A query converted the date field stored as midnight UTC to BRT and answered zero sales for a day that had hundreds of thousands of reais. How to avoid: Do not convert the business date field; compute only today in BRT. - **Day shifted in BI.** The midnight UTC TIMESTAMP sent to the BI Postgres was read in the São Paulo time zone, and every sale showed up on the previous day. How to avoid: CAST to DATE before the destination. - **Negative margin from a repeated invoice.** Joining on invoice number alone matched a cart from one store with an invoice from another store, with a different salesperson and customer. The result was negative margins and margins above 100%. How to avoid: Composite key (store, invoice number) in every JOIN. - **Head office without sales triggers an alert.** The head office or DC has no direct sales and showed up as a zeroed store, raising suspicion of a sync failure. How to avoid: Flag stores without direct sales in the store dimension and exclude them from the zero alert. - **Silent duplication.** A table with a monthly grain in its name started receiving daily rows, and a merge key different from the grain duplicated records with no error. How to avoid: Name, grain and merge key aligned; uniqueness test by store and day. - **Month-to-date report.** Some sources export the month-to-date total every day. Adding up the files multiplied sales. How to avoid: Deduplicate by whole day and keep the latest file for each date. ### Acceptance test - [ ] For three stores and one closed day, gross and net sales match the ERP cash close (zero difference). - [ ] The sum of stores is the chain total for the day, and the sum of days for each store equals the value in the monthly table. - [ ] The daily table has exactly one row per store and day. - [ ] "Yesterday" queried at 10 p.m. and at 11:59 p.m. in São Paulo returns the same business day and the same value. - [ ] The count of distinct invoices by (store, number) in the month equals the total sales invoices issued in the ERP. ### Context document ```text Daily sales by store (ERP) - Grain: one row per store, per business day. Detail by salesperson and product in the invoice line item table. - The sale date is stored as midnight UTC and represents the business day. Do not apply a time zone conversion to this field. Compute today and yesterday in America/Sao_Paulo and compare directly. - The invoice (nota fiscal) is identified by (store, invoice number). Never JOIN on the number alone. - Net sales = gross minus discounts minus cancellations. When asked "how much did we sell", answer with net and say it is net. - Direct sales and future-delivery sales add. Returns come in negative and add. Shipments and transfers stay out (sales CFOP, the fiscal operation code). - Average ticket = net sales / valid (non-cancelled) receipts. Do not sum or average average tickets. - The store of the sale is the one on the document. The salesperson's current store is not applied to history. - The head office and the DC have no direct sales: no sales there is expected. - Target: % attainment = month's net / target; required daily average = gap / remaining days. Client exceptions: - : sales operation codes ; future-delivery sale = ; return = . - : stores without direct sales . - : source brings the real time in UTC and needs conversion to the local time zone. ``` ### Variations - **One ERP instance per store:** Same rule in each instance, unified with a store column before aggregating. - **Chain in several countries:** Daily FX rate carrying the last value forward on weekends, and a time zone rule per country. - **Adjustment history:** Keep versions of the daily table (SCD type 2) to know when a closed day was changed.