Automate Weekly Pipeline Health Report → Sheets + Manager Email in n8n — Step by Step
Every Monday morning, a sales manager asks the same question: "How's the pipeline looking?" And every Monday, someone opens the CRM, eyeballs a few deals, and gives an answer that's mostly vibes. Ther
Every Monday morning, a sales manager asks the same question: "How's the pipeline looking?" And every Monday, someone opens the CRM, eyeballs a few deals, and gives an answer that's mostly vibes. There's no baseline, no trend, and no early warning when the pipeline is quietly rotting. By the time a bad quarter is obvious, it's already a bad quarter. This article shows you how to replace that ritual with an n8n workflow that scores your pipeline health automatically every Monday, logs the score to Google Sheets so you build a trend line over time, and emails a clean summary straight to your manager — before anyone has had their first coffee.
The problem: pipeline reviews are manual, subjective, and too late
Pipeline health isn't a single number your CRM hands you. It's a composite: how much value is in each stage, how many deals are stalled past their expected close date, how lopsided your coverage is versus quota, and whether new opportunities are actually entering the top of the funnel. Answering that by hand means exporting deals, building a pivot table, and interpreting it — a 30-to-45 minute job that nobody does consistently.
Because it's manual, three things go wrong. First, it's inconsistent: different people measure it differently week to week, so you can't compare. Second, there's no history: without a logged score every week, you can't see the trajectory that matters far more than any single snapshot. Third, it's reactive: stalled deals and coverage gaps surface only when someone happens to look, which is usually after the damage is done. What busy founders and ops teams need is a scheduled, deterministic score that lands in the same place, in the same format, every single week — automatically.
The solution: a scored, logged, emailed report on a schedule
The workflow does four things in sequence. It pulls your open deals from the CRM every Monday morning. It scores pipeline health from that data using a simple, transparent formula you control. It appends that score plus the supporting metrics to a Google Sheet so each run adds a new row and the trend builds itself. And it emails a formatted summary to your manager, so the review is a two-minute read instead of a 45-minute chore.
The health score is a weighted blend of factors you can tune: total open pipeline value versus quota (coverage ratio), the percentage of deals stalled beyond their expected close date, the count of net-new deals created in the last 7 days, and the distribution of value across stages. Roll those into a 0–100 score with a color band — green above 75, amber 50–75, red below 50 — and your manager gets an instant read on whether to relax or intervene. Because it's the same formula every week, the number is comparable across time, which is the entire point.
Step-by-step: building it in n8n
Here's the node-by-node build. The whole thing is seven nodes and runs in under 30 seconds per execution.
1. Schedule Trigger. Add a Schedule Trigger node. Set it to a Cron expression of 0 8 * * 1 — that fires at 08:00 every Monday. Set the workflow timezone in n8n's settings so the trigger fires in your local time, not UTC.
2. Fetch open deals. Add your CRM node — HubSpot, Pipedrive, or a generic HTTP Request node hitting your CRM's REST API. For HubSpot, use the "Search" operation on Deals, filtering dealstage to exclude closed-won and closed-lost. Request the properties you need for scoring: amount, dealstage, closedate, and createdate. If your CRM paginates, enable "Return All" so you capture the full pipeline, not just the first page.
3. Compute the score. Add a Code node (JavaScript). This is the brain. Loop over the incoming deals and calculate: totalValue (sum of amount), stalledCount (deals whose closedate is in the past), newDeals (deals with createdate within the last 7 days), and coverageRatio (totalValue divided by your quota constant). Then combine them into a score:
const score = Math.round( Math.min(coverageRatio/3,1)*40 + (1 - stalledCount/dealCount)*40 + Math.min(newDeals/5,1)*20 );
Return a single item with score, totalValue, dealCount, stalledCount, newDeals, and a band string ("Green"/"Amber"/"Red"). Keep the weights in named variables at the top of the node so you can retune without hunting through logic.
4. Add the run date. Use a Set (Edit Fields) node, or the built-in $now expression, to stamp the row with the ISO date. This becomes the first column in your sheet and the x-axis of every future chart.
5. Log to Google Sheets. Add a Google Sheets node, operation Append Row. Authenticate with a Google OAuth2 or service-account credential. Point it at a sheet with columns: Date, Score, Band, Total Value, Deal Count, Stalled, New Deals. Map each field from the Code node's output. Append (not update) is deliberate — every Monday adds one row, and the accumulating rows are your trend line. Drop a sparkline or line chart on top of the Score column and you have a free pipeline-health dashboard.
6. Format the email. Add a second Code or Set node to build an HTML email body from the score fields — a headline like "Pipeline Health: 82 (Green)", followed by the supporting metrics as a short list, and a one-line callout if stalledCount is high. Templating the body here keeps your send node clean.
7. Email the manager. Add a Gmail or Send Email (SMTP) node. Set the recipient to your manager, a subject like Weekly Pipeline Health — {{$json.score}} ({{$json.band}}), and the HTML body from the previous node. Send. That's the full chain: Trigger → CRM → Score → Set date → Sheets → Format → Email.
Why this pays off
The immediate win is time: you reclaim the 30–45 minutes a week someone spent assembling the review, times 52 weeks a year. But the compounding win is the trend line. After a month you can see whether health is climbing or sliding; after a quarter you can correlate score dips with rep changes, seasonality, or marketing spend. A single weekly number, logged consistently, turns pipeline management from anecdote into a time series you can actually reason about.
It also changes the manager relationship. Instead of pulling your manager into the weeds every Monday, you hand them a scored, color-coded summary they can absorb in two minutes and drill into the Sheet only when the number moves. Green weeks need no meeting. Red weeks come with the exact metrics — stalled count, coverage gap — that explain why, so the conversation starts at "what do we do" instead of "what's going on." And because the whole thing is deterministic, it runs whether you're on vacation, slammed, or asleep.
Common pitfalls to avoid
Timezone drift. The most common failure is the report firing at the wrong hour or wrong day because the workflow ran in UTC. Set your n8n instance timezone explicitly and confirm the first live run lands when you expect.
Pagination truncation. If your CRM returns only the first 100 deals, your score is computed on a partial pipeline and will be silently wrong. Always enable "Return All" or loop through pages, and sanity-check dealCount against your CRM's own total on the first run.
Update vs. append. Using the Sheets "Update" operation instead of "Append" overwrites the same row every week and destroys your history — the one asset that makes this valuable. Confirm it's set to Append and watch a new row appear each Monday.
Opaque scoring. Don't bury the formula in one dense line with magic numbers. Keep the weights and thresholds as named constants at the top of the Code node so anyone — including future you — can see why a score is what it is and retune it when your sales motion changes.
Silent failures. If the CRM API rate-limits or a credential expires, the workflow can fail quietly and your manager just… gets no email. Add an Error Trigger workflow that pings you on Slack or email when the run fails, so a missing report is never mistaken for a healthy pipeline.
Currency and stage mismatches. If deals use multiple currencies or your stage names differ from what the Code node expects, totals go sideways. Normalize currency before summing and match stage names to your actual CRM configuration, not a template's defaults.
Build it once and it runs forever. Every Monday your pipeline scores itself, the trend line grows by one row, and your manager gets the read before the week begins — no exports, no pivot tables, no vibes.
Ja construimos isso pra voce
Nao comece do zero. O Weekly Pipeline Health Report → Sheets + Manager Email e um workflow n8n pronto para instalar — conecta suas ferramentas em minutos, sem codigo.
Instalar por $29 →
