Daily sales by store and salesperson from the ERP

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.

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).

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.

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.

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.

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.

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.

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.

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.