--- id: casos/relatorios-por-email area: Operations usage: common effort: low grain: Audience x period (business day, week or closed month) --- # Daily email reports Each audience gets, by email or chat, at the agreed time, only the numbers it needs to read, for a closed period, with no duplicate sends and a warning when the data is late. ## Executive summary **For:** Managers who start the day opening several tools to build the same summary, and teams that need the information to come to them. **What it solves** - The summary arrives ready, on time, for each audience, with nobody building it. - Each person gets only what is theirs: leadership the consolidated view, a manager their branch. - When the data is late, the message says so instead of sending a stale number. **Why it matters:** The routine changes behavior: information goes to people and deviations show up before the meeting. But an email with a wrong, duplicated or two-day-old number loses trust faster than a dashboard, because it lands in everyone's inbox at once. Period rules, freshness checks and a send log are what make the routine last. **Business questions it answers** - How much did we bill yesterday, and how is the month to date tracking against target? - How many new deals came in yesterday in each pipeline? - Which contracts have already used more than 80% of what was sold? - What changed this week compared with last week? - Who on the team logged no activity on the last business day? **What you need** - The summary metrics in gold tables, with closed rules - The list of audiences, what each receives, through which channel and at what time - The sending channel credential stored as a secret in Nekt **What you get** - A scoreboard per audience and period, computed in the warehouse - The message built from the scoreboard, with comparisons and highlights - A send log that prevents resends and surfaces failures - An automatic warning when the source did not load ## Detailed ### The problem The daily summary is a clock-scheduled query that sends whatever is in the table. On Monday it compares with Sunday; on the day the load was late, it sends the day-before-yesterday number as if it were yesterday's; on a rerun, it sends twice; when one recipient fails, the others get nothing. Months later, the routine is still running and spending credits for a channel nobody reads. ### Business rules 1. **The number comes from the scoreboard; the message only writes.** Compute in a table, per audience and period, each metric with the current value, the previous period and the change. The send routine only formats. If there is AI-written text, the model gets the ready scoreboard and never adds a number that is not in it; the priority of highlights is also computed in the table, not by the model. (Avoid: Computing inside the send script or letting the AI do the sums.) 2. **Closed business day, in local time.** The daily summary covers the previous day in the business time zone. In a business-day routine, Monday covers Friday and national holidays are skipped. The week is defined once (Monday to Sunday or Sunday to Saturday) and written down. The current month comes as a partial month to date, never compared with a full month. (Avoid: Current date minus one, in UTC, every day, including Monday.) 3. **Compare windows of the same length.** Day against the same weekday last week, week against week, month to date against the same days of the previous month. Rate changes are in percentage points; a very small base becomes a change in reais. (Avoid: Monday against Sunday, partial month against full month.) 4. **Each audience gets its own cut.** The recipient list lives in a table (audience, channel, destination, cut, time), not in code. Leadership gets the consolidated view; a manager, their branch; a seller or advisor, their book. A row from another branch never goes to someone who should not see it. A client who left the portfolio stops receiving. (Avoid: The same email for everyone, with everything.) 5. **Freshness before sending.** Before sending, check that the source table has the reference day. If not, wait a short window and, if it still has not arrived, send with a note on how current the data is, or do not send. A heartbeat block (how many records came in over the last 24 hours) exposes stalled ingestion. (Avoid: Sending on schedule with whatever is in the table.) 6. **One send per audience and period.** Log every send (audience, period, channel, status, time). A rerun does not resend what already went out. One recipient's failure does not stop the others, and a send failure makes the run fail visibly. Long messages are split at the channel limit. (Avoid: Rerunning and sending again to everyone.) 7. **Dry run, pilot, then the list.** Every new routine first runs without sending (dry run), then to one internal recipient and only then to the real list. A client-facing message can start as a draft reviewed by someone before sending. (Avoid: Switching sending on straight to the full list.) 8. **Few numbers, fixed order.** Open with a readout of up to five lines before any table. Show what calls for action: what went out of range, what overran, what is at risk. Block order is fixed (current month before the previous one, consolidated before detail) so readers find the same place every day. Values in Brazilian format, with a footer giving source and load date. (Avoid: Pasting the whole table into the email body.) 9. **Switch off what nobody reads.** A disabled destination, an unread channel or a replaced report keeps consuming credits. Review routines periodically and pause the ones with no reader, recording the reason and the date. (Avoid: Leaving the query running after the channel died.) ### Cadence and cut per audience A common starting point across workspaces. Adjust the time to each team's routine. - **Leadership** → Consolidated day and month to date against target (`business day, early`) - **Branch manager** → Own branch only, with internal ranking (`business day, early`) - **Sales or operations team** → Activity per person on the previous business day (`business day`) - **Contract management or CS** → Overruns, attention zone and no movement (`weekly, Friday`) - **Agency or consultancy client** → Period scoreboard with approved structure (`Monday or closed month`) ### Definitions | Metric | Definition | |---|---| | Reference day | business day before the run in local time; on Monday, Friday; national holidays skipped | | Week | Monday to Sunday (ISO) or another written rule, always closed | | Month to date | from day 1 to the reference day, compared with the same days of the previous month | | Change | value for the period - value for the previous period of the same length; for rates, in percentage points | | Send | one row in the log per audience, period and channel with status sent; a rerun with the same trio does not send | | Source freshness | latest business date in the source table compared with the reference day | | Attention zone | item at or above the stated consumption or deviation limit, for example 80% of the contracted amount | ### Pitfalls - **A two-day-old number.** The load chain was late and the summary went out on time with the day-before-yesterday number, with no warning. How to avoid: Check the source date before sending and warn. - **Ingestion stopped for weeks.** A source went more than a month without loading and nobody noticed, because nothing warned. The month showed a fraction of the real entries. How to avoid: A heartbeat block with volume over the last 24 hours. - **Duplicate or missing sends.** An error with one recipient stopped all of them, and the retry resent to people who had already received it. How to avoid: Log per audience and period and failures isolated per recipient. - **Credits spent with no reader.** The chat destination was switched off and the query kept running every day for more than a month. How to avoid: Pause routines with no active destination and record the reason. - **A heavy routine that stopped.** A daily summary written in Spark started failing repeatedly, with no useful error message. Rewritten in plain Python, it ran again. How to avoid: A send routine is light: a Python notebook reading the ready scoreboard. - **Monday always looks like a drop.** Comparing yesterday with the day before made every Monday look like a drop, because Sunday does not have the same operation. How to avoid: Previous business day and comparison with the same weekday last week. ### Acceptance test - [ ] For a reference day, every number in the message matches the query on the gold table for the same day. - [ ] Running the routine twice on the same day produces a single send row per audience in the log and a single message. - [ ] With the source table missing the reference day, the routine sends the delay warning or does not send, and never sends the previous day's number as if it were the reference day's. - [ ] On Monday, the business-day summary covers Friday; after a national holiday, it covers the last business day. - [ ] A manager receives zero rows from another branch. ### Context document ```text Scheduled summary per audience - Each summary covers a closed period in the America/Sao_Paulo time zone: daily = previous business day (Monday covers Friday, national holidays skipped); weekly = ; monthly = closed calendar month. - Every number comes from the scoreboard . The message carries no number that is not in the scoreboard. - Comparisons over same-length windows: day against the same weekday last week, week against week, month to date against the same days of the previous month. Rates change in percentage points. - Before sending, check that the source has the reference day. If not, send with the note 'data up to ' or do not send. - Recipients, channel, time and cut come from the table . Nobody receives rows outside their cut. - One send per audience and period: check the log before sending. - Structure: a readout of up to five lines, then the blocks in the order . - Values in pt-BR format, no emoji, with a footer giving source and last load date. Client exceptions: - : daily summary at