What this automation does
Every Monday morning your sales team gets the same pipeline report, built from the same definitions, without anyone exporting a CSV on Sunday night.
- Snapshot. Each night a workflow pulls every open and recently closed deal from the CRM (ID, owner, stage, amount, close date, created date, last activity) and writes it to a snapshot table stamped with the date.
- Compare. SQL compares today’s snapshot with the one from seven days ago and labels each deal as new, advanced, moved back, slipped (close date pushed), won, lost or unchanged.
- Compute. The same queries produce the headline numbers: total pipeline by stage, weighted pipeline, coverage against quota, win rate, and average days in stage.
- Narrate. The computed table goes to an LLM with a fixed brief: write five to eight sentences for a sales leader, call out the biggest movements, and use only the numbers provided.
- Verify and post. A code step checks that every figure in the narrative exists in the data. The report goes to a Slack channel and an email list, with a link to the full table in Google Sheets.
When it is worth building
The trigger is usually a sales manager or RevOps person spending two to four hours each week assembling a pipeline view by hand, or a leadership meeting where the first twenty minutes are spent arguing about whose numbers are right. Automating the report fixes both, because the definitions live in code instead of in someone’s head.
It is not worth building if you have three reps and twenty open deals. A saved CRM view is enough. It is also premature if your stages are not used consistently. If half the deals sit in “Qualified” forever, fix stage hygiene first, or the report will just automate bad data. The CRM data entry from calls guide covers one way to keep deal fields current.
Architecture
| Layer | What we use | Why |
|---|---|---|
| Source | HubSpot, Salesforce or Pipedrive API | Deals, owners and stages come straight from the system of record |
| Orchestration | n8n on a nightly and weekly schedule | Built-in CRM, Postgres and Slack nodes, and it can run on your own server |
| Storage | Postgres (or Google Sheets for small teams) | Snapshots need history, and SQL makes week-over-week comparison trivial |
| Metrics | SQL views | Win rate, coverage and stage movement must be deterministic and auditable |
| Narrative | Claude Sonnet 5.5 or OpenAI’s GPT models through the API | Good at concise business writing when given a fixed table and strict rules |
| Delivery | Slack, email and a linked sheet | Leaders read it where they already work, and can click through to detail |
How to build it
- Agree on definitions in writing. What counts as pipeline, which stages are weighted at what probability, what “slipped” means. Get the sales leader to sign off before writing any SQL.
- Build the nightly snapshot. Pull deals with pagination and store every field you might need later. Storage is cheap and you cannot backfill a snapshot you never took.
- Write the comparison views. One view per question: movement by stage, slipped deals, new deals, closed deals. Test them against a week you already reported by hand.
- Assemble the report payload. Produce one compact JSON object with headline numbers, the top ten movements by amount, and any rep with no activity in fourteen days.
- Prompt for the narrative. Give the model the JSON, the audience, a length limit and a rule that it may not introduce any figure that is not in the payload. Ask for plain sentences, not bullet soup.
- Add the number check. Extract every number from the generated text and compare it with the payload. If anything does not match, post the table without commentary and alert the owner.
- Run in parallel for two weeks. Send the automated report to the sales leader only, next to the manual one, and fix every discrepancy before widening the audience.
The downloadable starter template below covers the HubSpot snapshot, the Postgres write and the Slack post.
What breaks in production
- Stage renames. Someone renames “Demo” to “Discovery Call” in the CRM and history no longer lines up. Map stage IDs, not labels, and keep a stage lookup table.
- Close-date pushing. Reps move close dates out a month at a time to keep deals alive. Count pushes per deal; a deal pushed three times is a forecasting problem worth naming.
- Deleted and merged deals. A deal that disappears between snapshots looks like it was lost. Check whether it was merged or deleted before labeling it.
- Multi-currency. Convert at a fixed rate per reporting period, and say which rate you used, or totals drift for reasons unrelated to sales.
- API limits during backfill. Pulling several years of deals at once can hit CRM rate limits. Backfill in batches over a few nights.
- Narrative overreach. Models like to explain why numbers moved. Unless the reason is in the data (for example a lost-reason field), the prompt should forbid guessing causes.
Timeline and cost
A single-CRM build with nightly snapshots, a weekly Slack report and a linked sheet usually takes two to three weeks. Lumeor builds this in the range of $4,000 to $10,000, depending on how many reports and audiences you want, whether forecasting categories are included, and how much stage cleanup the CRM needs first. Running costs are small: one narrative per week is a trivial amount of model usage, and the database fits on the smallest hosted Postgres tier. See the cost of workflow automation for how these projects are scoped.
How to measure success
- Hours per week spent assembling pipeline reports, before and after
- Number of weeks the report shipped on time with no manual fix
- Discrepancies between the automated figures and the CRM, which should trend to zero
- Count of slipped deals surfaced that leadership acted on
- Whether the Monday meeting starts with decisions instead of reconciling numbers