Google Sheets + Gmail: retention digests that get read
Your retention data is probably “somewhere in a sheet,” and the weekly update is probably “someone’s Friday problem.” Then it turns into copy-paste reporting, vague takeaways, and a hiring team that skims (or ignores) the one email that actually matters.
HR managers feel it when leaders ask, “So what changed?” and there’s no clean answer. A recruiting lead gets stuck translating messy trait notes into something actionable. And founders end up making job description changes based on gut feel. This retention digest automation turns your Google Sheets data into an email people will actually read.
Below you’ll see exactly how the workflow pulls candidate records, merges them with trait-level retention stats, scores candidates, then generates a polished HTML digest via Azure OpenAI and sends it through Gmail.
How This Automation Works
The full n8n workflow, from trigger to final output:
n8n Workflow Template: Google Sheets + Gmail: retention digests that get read
flowchart LR
subgraph sg0["When clicking ‘Execute workflow’ Flow"]
direction LR
n0@{ icon: "mdi:play-circle", form: "rounded", label: "When clicking ‘Execute workf..", pos: "b", h: 48 }
n1@{ icon: "mdi:database", form: "rounded", label: "Candidate Data Fetch", pos: "b", h: 48 }
n2@{ icon: "mdi:database", form: "rounded", label: " Trait Summary Fetch ", pos: "b", h: 48 }
n3["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/merge.svg' width='40' height='40' /></div><br/>Merge Candidate + Trait Data"]
n4["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Candidate Scoring & Data Nor.."]
n5@{ icon: "mdi:swap-horizontal", form: "rounded", label: " Data Validation", pos: "b", h: 48 }
n6@{ icon: "mdi:database", form: "rounded", label: " Error Handling Logic", pos: "b", h: 48 }
n7@{ icon: "mdi:robot", form: "rounded", label: " Retention Digest Generator", pos: "b", h: 48 }
n8@{ icon: "mdi:brain", form: "rounded", label: " AI Processing Backend ", pos: "b", h: 48 }
n9@{ icon: "mdi:message-outline", form: "rounded", label: "Email Delivery", pos: "b", h: 48 }
n5 --> n7
n5 --> n6
n1 --> n3
n2 --> n3
n8 -.-> n7
n7 --> n9
n3 --> n4
n0 --> n1
n0 --> n2
n4 --> n5
end
%% Styling
classDef trigger fill:#e8f5e9,stroke:#388e3c,stroke-width:2px
classDef ai fill:#e3f2fd,stroke:#1976d2,stroke-width:2px
classDef aiModel fill:#e8eaf6,stroke:#3f51b5,stroke-width:2px
classDef decision fill:#fff8e1,stroke:#f9a825,stroke-width:2px
classDef database fill:#fce4ec,stroke:#c2185b,stroke-width:2px
classDef api fill:#fff3e0,stroke:#e65100,stroke-width:2px
classDef code fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
classDef disabled stroke-dasharray: 5 5,opacity: 0.5
class n0 trigger
class n7 ai
class n8 aiModel
class n5 decision
class n1,n2,n6 database
class n4 code
classDef customIcon fill:none,stroke:none
class n3,n4 customIcon
The Problem: Retention insights get lost in the spreadsheet
Most retention reporting fails in a quiet way. The data exists, but it’s split across tabs, updated inconsistently, and summarized differently depending on who’s writing the email that week. Candidate notes live in one sheet. Trait performance lives in another. The “so what” lives in someone’s head. Then a hiring manager gets a wall of numbers, or a vague paragraph, and the decision-making goes right back to instincts. Meanwhile, you’re doing the same cleanup every week and still worrying you missed something important.
It adds up fast. Here’s where it breaks down in real teams.
- Weekly retention updates take about 2 hours because the story has to be rebuilt from scratch each time.
- Trait-level retention stats don’t naturally connect to individual candidate outcomes, so “patterns” are mostly guesswork.
- Manual summaries drift in quality, which means leaders stop trusting them and stop reading them.
- When something is missing in the data, you find out late, after the email goes out or never goes out at all.
The Solution: A scored, AI-written retention digest sent weekly
This workflow turns two Google Sheets into one consistent retention narrative, delivered as a formatted Gmail HTML email. You start with candidate-level records in a “Hires Tracking” sheet (name, role, traits, start date, retention status). In a second sheet, you keep a trait-level “Retention Summary” (hires, stayed, left, retention rate, and any weight adjustments). When you run the workflow, n8n pulls both datasets, merges them, and normalizes the messy parts so the scoring is fair and repeatable. Then Azure OpenAI (gpt-4o-mini) generates a structured Retention Digest: a TL;DR, top traits, weak traits, candidate highlights with scores, plus job description refinement tips. Finally, Gmail sends it to your hiring managers with a clear subject line, so it doesn’t get buried.
The workflow begins with a manual trigger (so you control when the digest runs). It fetches candidates and trait stats from Google Sheets, calculates a Candidate_Score, validates that both datasets exist, and only then generates the HTML digest through Azure OpenAI. If something’s missing, it logs the issue to an Error Log sheet instead of quietly sending a broken email.
What You Get: Automation vs. Results
| What This Workflow Automates | Results You’ll Get |
|---|---|
|
|
Example: What This Looks Like
Say you review 25 hires each week and you maintain 15 trait metrics in a separate summary tab. Manually, it’s common to spend about 3 minutes per hire to clean traits and status, plus another 30 minutes building a readable email. That’s roughly 2 hours, and the “insight” still depends on whoever wrote it. With this workflow, you trigger the run in under a minute, the scoring and AI generation usually finishes in a few minutes, and the HTML digest lands in Gmail ready to forward. You get the same output, every week, without rebuilding it.
What You’ll Need
- n8n instance (try n8n Cloud free)
- Self-hosting option if you prefer (Hostinger works well)
- Google Sheets to store hires, traits, and error logs.
- Gmail to deliver the retention digest to managers.
- Azure OpenAI API access (get it from Azure AI Foundry / Azure Portal)
Skill level: Intermediate. You’ll connect credentials, confirm sheet columns match expectations, and adjust one or two prompts/fields for your org.
Don’t want to set this up yourself? Talk to an automation expert (free 15-minute consultation).
How It Works
Manual run starts the week’s digest. You trigger the workflow when you’re ready, which is useful if your sheets are updated at different times on different days.
Google Sheets data is pulled from two sources. n8n fetches candidate-level records from your Hires Tracking sheet and trait-level retention stats from your Retention Summary sheet.
Scoring and cleanup happens before AI touches anything. A code step standardizes fields, builds a trait-to-weight map, and calculates Candidate_Score so the digest is based on the same logic every run.
Validation protects your inbox reputation. If required datasets are missing, the workflow writes a record to an Error Log sheet instead of sending a half-empty email. If the data checks out, Azure OpenAI generates a structured HTML digest and Gmail delivers it with the weekly subject line.
You can easily modify the scoring weights to reflect your hiring priorities, or adjust the digest sections to match how your managers prefer to review retention.
Step-by-Step Implementation Guide
Step 1: Configure the Manual Trigger
This workflow starts manually and immediately fans out to pull two datasets in parallel.
- Add and open Manual Run Trigger to confirm it is the workflow entry point.
- Connect Manual Run Trigger so it outputs to both Fetch Candidate Records and Retrieve Trait Summary in parallel.
Step 2: Connect Google Sheets
Both data inputs and the error log rely on Google Sheets. Configure each sheet source with the exact document and sheet IDs shown.
- Open Fetch Candidate Records and set Document to
[YOUR_ID]and Sheet toRetention Summary). - In Fetch Candidate Records, set Credential Required: Connect your googleSheetsOAuth2Api credentials.
- Open Retrieve Trait Summary and set Document to
[YOUR_ID]and Sheet toHires Tracking. - In Retrieve Trait Summary, set Credential Required: Connect your googleSheetsOAuth2Api credentials.
- Open Log Processing Errors and set Operation to
append, Document to[YOUR_ID], and Sheet toerror log sheet. - In Log Processing Errors, set Credential Required: Connect your googleSheetsOAuth2Api credentials.
Trait, Retention_%, and Candidate (including spacing variants).Step 3: Set Up Data Merge and Scoring
Merge both sheets into one stream, then normalize and calculate candidate scores in code.
- Connect Fetch Candidate Records and Retrieve Trait Summary into Combine Candidate Traits so both datasets are merged.
- Open Normalize & Score Candidates and keep the provided JavaScript Code as-is to normalize data and compute
Candidate_Score. - Confirm Normalize & Score Candidates outputs a single item with
candidatesandtraitsarrays.
Step 4: Configure Validation and AI Digest Generation
The dataset is validated before the AI generates the HTML retention digest.
- Open Validate Dataset Contents and ensure both conditions are set to greater-than checks:
{{ $json.candidates.length }}>0and{{ $json.traits.length }}>0. - Open Generate Retention Digest and set Text to the prompt that includes
{{ JSON.stringify($json, null, 2) }}and the HTML formatting requirements. - In Azure AI Chat Engine, set Model to
gpt-4o-mini. - Credential Required: Connect your azureOpenAiApi credentials in Azure AI Chat Engine.
Step 5: Configure Output Email Delivery
The HTML digest is sent via Gmail using the AI output.
- Open Send Digest Email and set To to
[YOUR_EMAIL]. - Set Message to
Weekly Update. - Set Subject to
=Retention Analysis Digest - Weekly Update. - Set HTML Message to
={{ $json.text }}and enable Include HTML. - Credential Required: Connect your gmailOAuth2 credentials.
Step 6: Add Error Handling
When validation fails, the workflow logs the error to a Google Sheet.
- Confirm the false output of Validate Dataset Contents connects to Log Processing Errors.
- In Log Processing Errors, ensure columns include
error_idanderrorfor the append operation.
Step 7: Test and Activate Your Workflow
Run a manual test to confirm the parallel data pull, AI digest generation, and email delivery all succeed.
- Click Execute Workflow and verify Manual Run Trigger launches both Fetch Candidate Records and Retrieve Trait Summary in parallel.
- Check that Normalize & Score Candidates outputs a single item with populated
candidatesandtraitsarrays. - Confirm Generate Retention Digest returns HTML text, and Send Digest Email delivers the message.
- If validation fails, confirm Log Processing Errors receives a new row.
- When ready for production, toggle the workflow to Active.
Common Gotchas
- Google Sheets credentials can expire or lack access to a specific spreadsheet. If runs suddenly fail, check the Google connection in n8n credentials and confirm the sheet is shared with the right Google account.
- Azure OpenAI deployments are picky about model/deployment names. If the AI step errors, verify the deployment name in your Azure OpenAI credential settings and confirm the region supports your chosen model.
- Gmail can reject HTML that looks fine in a browser but breaks in email clients. If formatting looks off, simplify the HTML styles in the prompt and avoid heavy CSS, because Gmail strips a lot of it.
Frequently Asked Questions
About an hour if your Google Sheets are already organized.
No. You will connect accounts and map a few fields in Google Sheets. The scoring logic is already packaged inside the workflow.
Yes. n8n has a free self-hosted option and a free trial on n8n Cloud. Cloud plans start at $20/month for higher volume. You’ll also need to factor in Azure OpenAI usage, which is usually low for one weekly digest.
Two options: n8n Cloud (managed, easiest setup) or self-hosting on a VPS. For self-hosting, Hostinger VPS is affordable and handles n8n well. Self-hosting gives you unlimited executions but requires basic server management.
Yes, but keep it simple at first. You can adjust the trait weights and Candidate_Score rules inside the “Normalize & Score Candidates” code step, then update the prompt used by the “Generate Retention Digest” step so the email explains the score the way your team expects. Common tweaks include changing which traits are considered “top” vs “weak,” adding a section per role, and inserting your own JD language guidelines so the tips match your hiring style.
Usually it’s an expired Google authorization or the sheet isn’t shared with the connected account. Reconnect Google Sheets in n8n, then confirm the spreadsheet ID and tab names still match. Also check that the workflow can read all required columns, because missing headers can look like a credential issue.
Hundreds per run is fine for most setups.
Often, yes, if you care about scoring, validation, and reliable error handling. This workflow has multiple branches (validate vs log errors), a real scoring step, and a structured AI output, and n8n handles that complexity without turning it into a pricey chain of “tasks.” Zapier or Make can be quicker for a basic “sheet to email” flow, but once you want merging, normalization, and a repeatable scoring model, they get fiddly. n8n also gives you a self-host option, which matters if you run a lot of internal reporting. If you’re unsure, Talk to an automation expert and we’ll sanity-check your use case.
Once this is running, retention reporting stops being a weekly scramble and becomes a reliable signal. Honestly, that consistency is what gets the email read.
Need Help Setting This Up?
Our automation experts can build and customize this workflow for your specific needs. Free 15-minute consultation—no commitment required.
