Google Sheets + Gmail: CLV segments that convert
You’re trying to run “smart” promos, but the segmenting is a mess. CLV lives in someone’s spreadsheet, customer history sits behind a dashboard, and by the time you’ve pulled it together, the campaign window is already gone.
This hits growth marketers hardest. But ecommerce owners and CRM consultants feel the same pain, just with different tools. With this CLV segment automation, you stop guessing who deserves an offer and you stop blasting discounts at low-value buyers.
This workflow pulls customer order history, calculates and analyzes lifetime value, writes clean rows into Google Sheets, then uses Gmail to email your high-value segment automatically. You’ll see how it works, what you need, and where teams usually trip up.
How This Automation Works
The full n8n workflow, from trigger to final output:
n8n Workflow Template: Google Sheets + Gmail: CLV segments that convert
flowchart LR
subgraph sg0["Run Offer Campaign Monthly Flow"]
direction LR
n0@{ icon: "mdi:play-circle", form: "rounded", label: "Run Offer Campaign Monthly", pos: "b", h: 48 }
n1@{ icon: "mdi:swap-vertical", form: "rounded", label: "Set customer history url", pos: "b", h: 48 }
n2@{ icon: "mdi:robot", form: "rounded", label: "Scrape Customer Profiles & O..", pos: "b", h: 48 }
n3@{ icon: "mdi:cog", form: "rounded", label: "Bright Data MCP Scraper", pos: "b", h: 48 }
n4@{ icon: "mdi:brain", form: "rounded", label: "AI Assistant", pos: "b", h: 48 }
n5["<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/>Format Customer Info"]
n6["<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/>Get Customer Order History"]
n7@{ icon: "mdi:swap-horizontal", form: "rounded", label: "Is Customer High-Value?", pos: "b", h: 48 }
n8@{ icon: "mdi:message-outline", form: "rounded", label: "Send Special Offer Email", pos: "b", h: 48 }
n9@{ icon: "mdi:cog", form: "rounded", label: "Ignore Low-Value Customers", pos: "b", h: 48 }
n10@{ icon: "mdi:robot", form: "rounded", label: "Auto-fixing Output Parser", pos: "b", h: 48 }
n11@{ icon: "mdi:brain", form: "rounded", label: "OpenAI Chat Model", pos: "b", h: 48 }
n12@{ icon: "mdi:robot", form: "rounded", label: "Structured Output Parser", pos: "b", h: 48 }
n4 -.-> n2
n11 -.-> n10
n5 --> n6
n3 -.-> n2
n7 --> n8
n7 --> n9
n1 --> n2
n12 -.-> n10
n10 -.-> n2
n6 --> n7
n0 --> n1
n2 --> 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 n2,n10,n12 ai
class n4,n11 aiModel
class n7 decision
class n5,n6 code
classDef customIcon fill:none,stroke:none
class n5,n6 customIcon
The Problem: CLV Segments Are Usually Guesswork
Most “VIP” segments are built from whatever data is easiest, not what’s most predictive. Somebody exports a report, filters by last purchase date, maybe adds total spend, and calls it a day. Then you email a discount to customers who would have bought anyway, while missing the high-value customers who are about to churn. Worse, the manual process quietly introduces errors: duplicate customers, mismatched IDs, and out-of-date totals. That’s how you end up with campaigns that look busy, but don’t move revenue.
It adds up fast. Here’s where it breaks down.
- Pulling order history from a locked-down analytics platform turns into a monthly copy-paste ritual that burns about 2 hours.
- CLV calculations drift because the “latest” spreadsheet is never the same file two weeks later.
- Segments get built on blunt rules, so your best customers receive generic offers that feel cheap.
- Campaign timing suffers, because the list is ready after the promo should have already launched.
The Solution: Automated CLV Analysis That Feeds Sheets and Gmail
This workflow runs on a schedule (think “monthly promo day”) and collects customer order history from a defined source URL. It uses Bright Data to scrape and retrieve the data reliably, even when platforms try to block automated access. Then an AI agent (powered by OpenAI chat models) helps interpret and structure what comes back, which is useful when the raw data is messy or inconsistent. Next, the workflow normalizes customer records, expands the order timeline, and evaluates each customer against your “high-value” criteria. Finally, high-value customers get an email via Gmail, while lower-value segments are skipped automatically so you don’t waste promos.
The workflow starts with a scheduled trigger and a “client history” URL that points to the right dataset. From there, it gathers orders, cleans the data, calculates CLV patterns, and makes a simple decision: email the offer or do nothing. Results are stored in Google Sheets so you can review, audit, and refine future campaigns.
What You Get: Automation vs. Results
| What This Workflow Automates | Results You’ll Get |
|---|---|
|
|
Example: What This Looks Like
Say you run one “VIP push” per month and you target 500 customers. Manually, you might spend about 2 hours exporting data, another hour cleaning it, then 30 minutes building a Gmail list and sending. That’s roughly 4 hours per campaign, assuming nothing breaks. With this workflow, you spend about 15 minutes updating the source URL or offer text, then the scheduled run does the collection, CLV analysis, and Gmail dispatch while you’re doing other work.
What You’ll Need
- n8n instance (try n8n Cloud free)
- Self-hosting option if you prefer (Hostinger works well)
- Google Sheets for storing CLV rows and segments.
- Gmail to send high-value customer offers.
- Bright Data credentials (get them from your Bright Data dashboard).
- OpenAI API key (get it from the OpenAI API settings page).
Skill level: Intermediate. You’ll connect accounts, set credentials, and be comfortable editing a couple of data fields and rules.
Don’t want to set this up yourself? Talk to an automation expert (free 15-minute consultation).
How It Works
A monthly promo trigger kicks things off. The workflow runs on a schedule, so your segmentation happens before the campaign, not after.
A client history URL defines the data source. You set the page or endpoint that contains customer order history, which keeps the workflow pointed at the right dataset without hunting through dashboards.
Bright Data + AI collect and interpret orders. Bright Data pulls the raw information, then the AI agent helps structure it so downstream steps can calculate patterns reliably (even if the source format changes a bit).
Records get normalized, expanded, and scored. The workflow cleans customer identifiers, expands the purchase timeline, then checks a “high-value” rule. If the customer qualifies, Gmail sends the offer; if not, the workflow does nothing.
You can easily modify the high-value rules to match your business model based on your needs. See the full implementation guide below for customization options.
Step-by-Step Implementation Guide
Step 1: Configure the Schedule Trigger
Set the monthly schedule that initiates the customer value analysis workflow.
- Add the Monthly Promo Trigger node as the workflow trigger.
- Open Monthly Promo Trigger and set the schedule rule to run monthly at Hour
9. - Confirm the trigger is connected to Define Client History URL.
Step 2: Connect Client History Source
Define where the workflow pulls customer history data for scraping.
- Open Define Client History URL.
- Set the assignment customer data url to
example.com(replace with your real customer history URL). - Ensure Define Client History URL connects directly to Agent: Collect Client Orders.
Step 3: Set Up AI Collection and Parsing
Configure the AI agent, scraping tool, and parsers to extract structured customer order data.
- Open Agent: Collect Client Orders and set Text to
=scrape the customer history url below and extract the key information: {{ $json['customer data url'] }}. - Verify Agent: Collect Client Orders has Prompt Type set to
defineand Has Output Parser enabled. - Open BrightData Scrape Tool and keep Tool Name as
scrape_as_markdownwith Tool Parameters set to{{ /*n8n-auto-generated-fromAI-override*/ $fromAI('Tool_Parameters', ``, 'json') }}. - Credential Required: Connect your mcpClientApi credentials to BrightData Scrape Tool.
- Open OpenAI Helper Model and set the model to
gpt-4o-mini. - Credential Required: Connect your openAiApi credentials to OpenAI Helper Model.
- Open Structured Parser and keep the JSON Schema Example as provided to define the expected output structure.
- Open Chat Model Engine and set the model to
gpt-4o-mini. - Credential Required: Connect your openAiApi credentials to Chat Model Engine.
Step 4: Normalize and Expand Order Data
Transform the AI output into individual customer order rows for downstream filtering.
- Open Normalize Client Records and keep the JavaScript code that maps
items[0].json.outputinto individual customer items. - Confirm Normalize Client Records connects to Expand Order Timeline.
- Open Expand Order Timeline and keep the JavaScript that splits each customer into order rows, including customer_name, customer_email, amount, and date.
- Verify Expand Order Timeline outputs to High-Value Decision.
customer_email and orders; missing fields will cause Expand Order Timeline to produce empty or invalid rows.Step 5: Configure Value Routing and Email Output
Route high-value customers to a promotional email and skip lower-value segments.
- Open High-Value Decision and set the condition to Amount
={{ $json.amount }}gte200. - Confirm High-Value Decision routes true results to Dispatch Offer Email and false results to Skip Low-Value Segment.
- Open Dispatch Offer Email and set Send To to
={{ $json.customer_email }}. - Set Subject to
Offer for being out ideal customerand Message towrite any offer(customize as needed). - Credential Required: Connect your gmailOAuth2 credentials to Dispatch Offer Email.
Step 6: Test and Activate Your Workflow
Validate the full execution path and then enable the workflow for monthly runs.
- Click Execute Workflow to run a manual test from Monthly Promo Trigger.
- Check that Agent: Collect Client Orders produces structured output and that Normalize Client Records and Expand Order Timeline generate individual order rows.
- Confirm High-Value Decision routes orders ≥
200to Dispatch Offer Email and others to Skip Low-Value Segment. - Verify a successful Gmail send in Dispatch Offer Email with the correct recipient.
- Toggle the workflow to Active so Monthly Promo Trigger runs automatically each month.
Common Gotchas
- Bright Data credentials can expire or need specific permissions. If things break, check your Bright Data zone and authentication settings first.
- If you’re using Wait nodes or external scraping, processing times vary. Bump up the wait duration if downstream steps fail because the scrape returned late or incomplete data.
- Default AI prompts are generic. Add your segmentation definitions and brand voice early, or you will be second-guessing the outputs every month.
Frequently Asked Questions
About 45 minutes if you already have credentials ready.
No. You’ll mostly connect accounts and adjust a few fields and rules. The “code” steps are included in the workflow already, so you’re configuring, not programming from scratch.
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 OpenAI API costs (often just a few dollars a month at modest volume) and Bright Data usage.
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, and it’s a common tweak. Swap the Monthly Promo Trigger schedule to weekly, then tighten your “High-Value Decision” rules so you don’t email the same customer too often. Many teams also add a simple “last emailed date” column in Google Sheets and check it before Gmail sends. If you want a softer approach, change the email content to a non-discount value message (early access, concierge support, reorder reminders).
Usually it’s permissions or an expired OAuth connection. Reconnect Google Sheets in n8n credentials, then confirm the spreadsheet is shared with the same Google account you authenticated. Also check that the target sheet tab name matches what the workflow expects, because a renamed tab looks like “missing file” to the automation.
On n8n Cloud Starter, you can typically run a few thousand workflow executions per month, which is plenty for many small lists. If you self-host, there’s no execution cap, so scale depends on your server and how heavy the scraping and AI steps are. For practical planning, most teams run this in batches (like 500 to 2,000 customers at a time) to avoid rate limits and keep Sheets clean.
Often, yes, because this workflow isn’t just “send row to email.” You’re scraping data, cleaning it, expanding timelines, and making conditional decisions, which is where n8n tends to feel more flexible and less expensive at scale. Another big difference is self-hosting, which lets you run as much as your server can handle. Zapier or Make can still win for very simple two-step flows, or when you need a teammate to edit automations without thinking about data structure at all. If you’re on the fence, Talk to an automation expert and we’ll sanity-check your use case.
Once this is running, your promos stop depending on someone “finding time” to build a list. The workflow handles the repeatable work, and you get to focus on the offer and the relationship.
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.
