--- id: agencias/relatorio-recorrente-automatizado industry: Marketing agencies usage: widely used effort: medium grain: Client x period (ISO week or closed month) --- # Automated recurring report Each client's weekly or monthly report goes out on its own, in the same format, with the numbers calculated in the warehouse and the readout written by AI. ## Executive summary **For:** Agencies that deliver a weekly or monthly report to each client. **What it solves** - The report goes out on the right day and time, with no manual assembly. - Every client gets the same format, comparable across clients. - The period's readout comes written, with no made-up numbers. **Why it matters:** The report is the part of the work the client sees. Automated, it frees up days of the team's time every month and removes quality differences between analysts. With the right rules, AI writes the readout without the risk of making up numbers in front of the client. **Business questions it answers** - How did client X's week compare with the previous week and with the month's average? - Generate client Y's monthly report for September. - What is the executive readout of client Z's LinkedIn for the closed month? - Which clients had the biggest drop in results last week? - Build the renewal presentation with client X's journey over the cycle. **What you need** - Final media and funnel tables per client - The report structure approved with each client - An LLM API key stored as a secret in Nekt **What you get** - A scorecard per client and period, with previous, average and change - An AI-written executive readout - Automatic delivery on each client's channel - Send log per client and period ## Detailed ### The problem Building each client's report by hand eats days of the team's time at the start of every month, and every analyst does it differently. When AI is brought in to write it, it makes up numbers, compares a partial month with a full one, or fills in zeros for a channel the client never even hired. ### Business rules 1. **Numbers come from SQL, the AI only writes.** The scorecard, changes, ranking and priority are calculated in a table before the model runs. The LLM receives the finished scorecard for the client and period and writes the summary, highlights, alerts and recommendations. Priority is calculated in the transformation, not by the model. (Avoid: Sending raw tables to the LLM to calculate and write.) 2. **Closed, explicit period.** Monday to Sunday week (ISO) or a closed calendar month, in the client's time zone. The report runs after the data settles: Monday morning for the week, a few days after the month turns over for the month (e.g. the 4th). The current month only appears labeled as partial. (Avoid: Monthly report running on the 1st while conversions are still coming in.) 3. **Scorecard ready to read.** One row per client, period and platform. For each metric: current value, previous period (_ant), average of the last 4 weeks including the current one (_med4) and change against the previous period (_var). The same scorecard feeds the dashboard and the message: the consumer only filters the period. (Avoid: Each destination recalculates the change its own way.) 4. **Compare windows of the same size.** Week against the previous week, month against the previous month. For rolling windows, the last 30, 90, 180 and 365 days against the immediately preceding window of the same size. Cost per result for the period is total spend over total results, never an average of daily costs. (Avoid: Partial month against a full month.) 5. **Scope without a connector stays out of the report.** If the client does not have the source connected (SEO, CRM, organic), the section is left out of the report; it does not show up with zeros. If the pipeline did not run in the period, the report says so instead of filling it in. Manual data (e.g. LinkedIn from a personal profile) is flagged as manual. (Avoid: An SEO section with zero clicks for a client without Search Console.) 6. **Fixed structure approved by the client.** Section order, charts, colors and typography live in a client context document, based on an approved reference report. Every report follows that structure. For presentations, use native, editable elements, never text baked into an image. (Avoid: A different layout in every send.) 7. **Never estimate, ask.** If next month's target, the period's deliverables or the client's assumptions are missing, the agent asks before building. A screenshot or value provided by the team only goes in when the table does not exist; never to fill in a number the warehouse has. (Avoid: Filling in the target with the average of recent months.) 8. **Late data is flagged.** Before sending, check that the source table is from today. If it is not, wait a short window (e.g. 25 minutes) and, if it still does not arrive, send with a note that the data is from the previous day. (Avoid: Sending on time with two-day-old data, with no warning.) 9. **One send per client and period.** Persistent log per client and period: rerunning does not resend. A failure for one client does not stop the others. Long messages are split at the channel's limit (e.g. 1,900 characters on Discord). A client who has left the portfolio does not get a report; churn is flagged. (Avoid: A single loop that stops at the first error and resends everything on retry.) ### Definitions | Metric | Definition | |---|---| | Report week | closed Monday to Sunday, in the America/Sao_Paulo time zone, ending on the Sunday before the send | | Report month | closed calendar month, generated after the conversion sources settle | | _ant | the metric's value in the previous period of the same size | | _med4 | average of the last 4 weeks, including the report week | | _var | current / previous - 1; empty when the previous value is zero | | Cost per result for the period | total spend / total results in the period, dividing only once | | Readout | LLM text about the scorecard: summary, highlights, alerts and recommendations, with no number that is not in the scorecard | ### Pitfalls - **Numbers made up by the AI.** Given raw data, the model miscalculated and filled gaps with plausible values. How to avoid: Hand over the finished scorecard and check every number in the text against it. - **A drop that was a partial month.** The current month compared with a full month showed a steep drop that did not exist. How to avoid: Compare closed periods or label the partial one. - **Zeros for a channel the client does not have.** Sections without a connector showed up with zeros and the client thought the channel had died. How to avoid: Scope per client: no connector, no section. - **Generic readout.** Note fields cut at 600 characters chopped off the client's exact wording, and the text came out generic. How to avoid: Do not truncate the context that sets the tone; use high limits and only the fields needed. - **Report with stale data.** The chain ran late and the message went out on time with data from two days earlier, without anyone noticing. How to avoid: Check the table's date before sending and warn when it is stale. - **Duplicate or missing sends.** An error for one client stopped all the others, and the retry resent to those who had already received it. How to avoid: Log per client and period, and failure isolation. ### Acceptance test - [ ] For one client and one closed week, every number in the scorecard matches a direct query on the source table (zero difference). - [ ] Every number cited in the AI-written readout exists in that client's scorecard for that period. - [ ] A client without an SEO connector has no SEO section in the report, not even with zeros. - [ ] Rerunning the send for the same period does not produce a duplicate message, and a client with an error does not block the send for the others. - [ ] The current month, when it appears, is labeled as partial. ### Context document ```text Recurring client report - The report covers one client and one closed period: a Monday to Sunday week or a calendar month, in the client's time zone. The current month only as partial, with a label. - Every number comes from the scorecard calculated in the warehouse. The written readout contains no number that is not in the scorecard. - Comparisons: previous period of the same size (_ant), average of the last 4 weeks (_med4) and change (_var). Never partial against full. - Cost per result = total spend / total results for the period. - A section without a connector enabled for the client is left out of the report; it does not show up with zeros. If the pipeline did not run, say so. - Follow the client's approved structure: section order, charts and colors from their template document. - If a target, assumption or deliverable for the period is missing, ask before building. Never estimate. - Screenshots or provided values only when the table does not exist; never to fill in a number the warehouse has. - A client with churn status does not get a report; just flag it. Per-client exceptions: - : weekly report, sections in the order , template approved on . - : is manual data, provided by the team by day . - : report result = , cost including % tax. ``` ### Variations - **Daily summary:** Yesterday per client, short, for the media team, with Meta and Google side by side. - **Renewal:** The journey across the whole cycle and a continuation proposal, instead of month over month. - **Rolling windows:** Last 30, 90, 180 and 365 days against the previous window of the same size.