Segment donors and draft personalized outreach with Google Sheets and Ollama

Description

Quick overview
This workflow pulls donor records from Google Sheets, validates and segments donors using RFM and engagement signals, generates personalized outreach drafts with a local Ollama Llama 3.1 model, and writes the drafted “Personalized Message” back to the same spreadsheet while logging invalid rows.

How it works
Runs manually or on a monthly schedule and reads donor rows from a Google Sheets spreadsheet.
Validates required fields (like Name and Email), normalizes donation amount/date, deduplicates identical rows, and tags each row as valid or invalid.
Appends or updates invalid rows into a separate Google Sheets tab named “Invalid Rows.”
Calculates RFM scores (recency, frequency, monetary) per donor, assigns an RFM segment, and adds latest donation details.
Adds behavioral segmentation based on event attendance and pre-donation communications, then maps each combined segment to a tone and ask strategy.
Sends a per-donor prompt to Ollama (Llama 3.1) to draft a short message, validates the output, and falls back to a predefined template if the draft is empty, malformed, or too long.
Updates the original Google Sheets donor records with the final personalized message and review flags.

Setup
Connect your Google Sheets OAuth credentials and set the correct spreadsheet and sheet/tab names for “Donor Data” and “Invalid Rows.”
Ensure your “Donor Data” sheet includes columns for Name, Email, Donation amount, Donation date, and a “Personalized Message” column for write-back.
Set up Ollama credentials in n8n and make sure the llama3.1:latest model is available and running where n8n can reach it.
Review and customize the segment-to-treatment mapping table (tone and ask_type) and the fallback templates to match your organization’s voice.