Rahi Uppal
AI and Automation Consultant with over 1000 hours of experience building, deploying, and solving marketing and sales automation problems. I ship AI products faster by combining business acumen with AI & automation expertise and an engineering mindset. Connect with me on LinkedIn to chat.
Templates by Rahi Uppal
Personalized cold email generator with Supabase, Smartlead & Google Gemini AI
n8n Workflow: AI-Personalized Email Outreach (Smartlead) 🔄 Purpose This workflow automates cold email campaigns by: Fetching leads Generating hyper-personalized email content using AI Sending emails via Smartlead API Logging campaign activity into Google Sheets --- 🧩 Workflow Structure Schedule Trigger Starts the workflow automatically at scheduled intervals. Ensures continuous campaign execution. Get Leads Fetches lead data (name, email, company, role, industry). Serves as the input for personalization. Loop Over Leads Processes each lead one by one. Maintains individualized email generation. Aggregate Lead Data Collects and formats lead attributes. Prepares structured input for the AI model. Basic LLM Chain 1 Generates personalized snippets/openers using AI. Tailored based on company, role, and industry. Update Row (Google Sheets) Saves AI outputs (snippets) for tracking and QA. Basic LLM Chain 2 Expands snippet into a full personalized email draft. Includes subject line + email body. Information Extractor Extracts structured fields from AI output: Subject Greeting Call-to-Action (CTA) Closing Update Row (Google Sheets) Stores finalized draft in Google Sheets. Provides visibility and audit trail. Code Formats email into Smartlead-compatible payload. Maps fields like subject, body, and recipient details. Smartlead API Request Sends the personalized email through Smartlead. Returns message ID and delivery status. Basic LLM Chain 3 (Optional) Generates follow-up versions for multi-step campaigns. Ensures varied engagement over time. Information Extractor (Follow-ups) Structures follow-up emails into ready-to-send format. Update Row (Google Sheets) Updates campaign logs with: Smartlead send status Message IDs AI personalization notes --- ⚙️ Data Flow Summary Trigger → Runs workflow Get Leads → Fetch lead records LLM Personalization → Create openers + full emails Google Sheets → Save drafts & logs Smartlead API → Send personalized email Follow-ups → Generate and log structured follow-up messages --- 📊 Use Case Automates hyper-personalized cold email outreach at scale. Uses AI to improve response rates with contextual personalization. Provides full visibility by saving drafts and send logs in Google Sheets. Integrates seamlessly with Smartlead for sending and tracking.
By Rahi UppalAI-powered multi-channel lead outreach with JotForm, Gemini AI & HeyReach
🧠 AI-Powered Outreach Engine (Jotform + Gemini AI + HeyReach + Data Tables) 🚀 Overview This workflow turns every Jotform submission — whether from your website or a live marketing campaign — into a fully automated, AI-personalized outreach sequence. Using Google Gemini AI, it instantly generates LinkedIn messages and email content tailored to each lead’s company and context. Depending on the data provided (like LinkedIn profile or email), it automatically routes the message to the right platform — HeyReach for LinkedIn outreach or Gmail for email follow-up — and logs all activity into n8n Data Tables for tracking and visibility. The result? Zero manual copywriting, zero campaign setup, and instant activation of every inbound lead through personalized, human-sounding communication. --- ⚙️ What It Does Trigger: Starts when a new submission is received from a Jotform attached to your website or ongoing campaign. Lead Enrichment (Optional): You can add an enrichment node (like Clearbit) to pull company, role, and domain details. AI Message Generation: Uses Google Gemini AI (LangChain) to write both a LinkedIn outreach message and a short, conversational email. Each message is context-aware — referencing the company, role, or query captured from the Jotform submission. Conditional Routing Logic: If the form includes a LinkedIn URL, the workflow sends the lead and message to HeyReach, adding them to the correct campaign automatically. If not, it sends the email variant via Gmail, ensuring no lead is missed. Data Tracking: All lead details, messages, and campaign statuses are automatically saved into n8n Data Tables, creating a single source of truth for your outreach history. Result: Personalized, multi-channel follow-up happens in seconds — turning inbound form leads into active, engaged prospects instantly. --- 🧠 Example Use Case Imagine a SaaS or marketing agency running a “Request a Demo” or “Get Pricing” campaign via a Jotform on their website. With this workflow, every time someone fills the form: Their details are captured and stored in n8n Data Tables. Gemini AI instantly writes a relevant, human-sounding LinkedIn and email message. The lead is automatically added to a HeyReach campaign or receives a Gmail follow-up if LinkedIn isn’t available. All statuses (message type, delivery, and timing) are tracked in real time. This workflow replaces hours of manual effort with automated, AI-personalized communication that feels natural — not robotic. --- 🧩 Ideal For B2B SaaS teams generating inbound leads through website forms Agencies managing multi-channel LinkedIn + email campaigns Sales and growth teams using form submissions for lead capture Marketers running Jotform-based campaigns looking to instantly activate leads --- 💡 Key Integrations JotForm → Capture and trigger workflow from form submissions Google Gemini AI (LangChain) → Generate personalized LinkedIn & Email messages HeyReach → Add AI-written messages directly into your LinkedIn campaign Gmail → Send AI-personalized cold emails automatically n8n Data Tables → Store, monitor, and analyze every lead and message generated --- 🧠 Workflow Logic | Step | Node | Description | |------|------|-------------| | 1 | JotForm Trigger | Captures lead data submitted through your form | | 2 | HTTP Request (Optional Enrichment) | Pulls company details from Clearbit or any enrichment API | | 3 | Insert Row (n8n Data Table) | Stores raw lead submission for tracking | | 4 | If Node | Determines if LinkedIn profile URL is available | | 5 | Google Gemini AI (LinkedIn Agent) | Creates personalized LinkedIn message | | 6 | HeyReach Node | Adds lead + message to selected campaign | | 7 | Google Gemini AI (Email Agent) | Generates personalized HTML email | | 8 | Gmail Node | Sends the AI-generated email if no LinkedIn is found | | 9 | Insert Row (n8n Data Table) | Logs campaign status, sent message, and timestamps | --- 🧾 Example Output (AI Node) json { "To": "alex@company.com", "Subject": "AI automation ideas for CompanyX 🚀", "Email Type": "LinkedIn or Cold Email", "HTML": "<p>Hey Alex, noticed CompanyX is scaling fast — congrats! 👏</p><p>At Amply, we help growing businesses use <strong>AI-driven automations</strong> to eliminate repetitive ops work and boost team productivity by 30–50%.</p><p>Would it make sense to explore if this could free up your ops bandwidth too?</p>", "Message": "Hey Alex, noticed CompanyX is scaling fast — congrats! 👏 At Amply, we help growing businesses use AI-driven automations to eliminate repetitive ops work and boost team productivity by 30–50%. Would it make sense to explore if this could free up your ops bandwidth too?" }
By Rahi UppalSmartlead email campaign analytics dashboard with Google Sheets integration
Workflow 1: Domain and Email Health 🩺 This part of the workflow is triggered every 5 hours by the Schedule Trigger1 node. Its purpose is to pull health metrics for both email domains and individual email addresses. How it Works: ++Schedule Trigger:++ The Schedule Trigger1 node initiates the workflow every 5 hours. ++API Requests:++ Two separate HTTP Request nodes, HTTP Request5 and HTTP Request6, make API calls to Smartlead. ++HTTP Request5 calls++ the endpoint for domain-wise health metrics. ++HTTP Request6 calls++ the endpoint for email-wise health metrics. Both requests use the same api_key and a date range from 2025-07-04 to the current day. ++Data Splitting:++ The Split Out5 and Split Out6 nodes take the JSON response from the API calls and split the data into individual items. This is necessary so each row of data can be processed and added to Google Sheets separately. ++Google Sheets Integration:++ Finally, the Append or update row in sheet5 and Append or update row in sheet6 nodes update two different Google Sheets: ++Append or update row in sheet5 adds++ or updates rows in the DomainHealth sheet, matching on the domain column. ++Append or update row in sheet6 adds++ or updates rows in the EmailHealth sheet, matching on the from_email column. Workflow 2: Global and Campaign-Specific Analytics 📊 This second part of the workflow is triggered every 2 hours by the Schedule Trigger node. Its goal is to get a day-by-day overview of email engagement and campaign-specific performance. How it Works: Schedule Trigger: The Schedule Trigger node starts this workflow every 2 hours. ++API Requests:++ Two HTTP Request nodes, HTTP Request and HTTP Request1, call different Smartlead API endpoints. ++HTTP Request++ retrieves day-wise overall stats for email engagement. ++HTTP Request1 ++retrieves overall stats for each campaign. ++Data Splitting:++ The Split Out and Split Out1 nodes separate the JSON responses into individual data items for processing. ++Google Sheets Integration:++ The Append or update row in sheet and Append or update row in sheet1 nodes then write the data to Google Sheets. ++Append or update row in sheet++ updates the Sheet1 sheet with day-wise metrics, using the date as a matching column. ++Append or update row in sheet1++ updates the CampaignWise sheet with campaign performance metrics, using the campaign id to match rows.
By Rahi UppalAutomated WhatsApp messaging & lead nurturing with Gallabox & Supabase
WABA Message Journey Flow Documentation This document outlines the automated workflow for sending WhatsApp messages to contacts, triggered hourly and managed through disposition and message count logic. The workflow is designed to ensure contacts receive messages based on their status and the frequency of previous interactions. Trigger and Data Retrieval The journey begins with a time-based trigger and data retrieval from the Supabase contacts table. Trigger: A "Schedule Trigger3" node initiates the workflow every hour. This ensures that the system regularly checks for contacts requiring messages. Get Contacts: The "Get many rows1" node (Supabase) then retrieves all relevant contact data from the contactsampere table in Supabase. This brings in contact details such as name, phone, Disposition, Count, and lastmessage_sent. Disposition-Based Segregation After retrieving the contacts, the workflow segregates them based on their Disposition status. Disposition Switch: The "Disposition Switch" node acts as the primary routing mechanism. It evaluates the Disposition field of each contact and directs them to different branches of the workflow based on predefined categories. Case 0: newlead: Contacts with the disposition newlead are routed to the "Count Switch" for further processing. Cases 1-4: The workflow also includes branches for testride, Booking, walkin, and Sale dispositions, though the detailed logic for these branches is not fully laid out in the provided JSON beyond the switch nodes ("Switch2", "Switch3", "Switch4", "Switch5"). The documentation focuses on the new_lead disposition's detailed flow, which can be replicated for others. Message Count Logic (for new_lead Disposition) For contacts identified as new_lead, the workflow uses a "Count Switch" to determine which message in the sequence should be sent. Count Switch: This node evaluates the Count field for each new_lead contact. This Count likely represents the number of messages already sent to the contact within this specific journey. Count = 0: Directs to "Loop Over Items1" (first message in sequence). Count = 1: Directs to "Loop Over Items2" (second message in sequence). Count = 2: Directs to "Loop Over Items3" (third message in sequence). Count = 3: Directs to "Loop Over Items4" (fourth message in sequence). Looping and Interval Check Each "Loop Over Items" node processes contacts in batches and incorporates an "If Interval" check (except for Loop Over Items1). Loop Over Items (e.g., "Loop Over Items1", "Loop Over Items2", "Loop Over Items3", "Loop Over Items4"): These nodes iterate through the contacts received from the "Count Switch" output. Interval Logic: "If Interval" (for Count = 1 from "Loop Over Items2"): Checks if the interval is greater than or equal to 4. This interval value is handled by a separate Supabase cron job, which updates it every minute based on Current time - last api hit time in hours. "If Interval1" (for Count = 2 from "Loop Over Items3"): Checks if the interval is exactly 24 hours. "If2" (for Count = 3 from "Loop Over Items4"): Checks if the interval is exactly 24 hours. Sending WhatsApp Messages If a contact passes the interval check (or immediately for Count = 0), a WhatsApp message is sent using the Gallabox API. HTTP Request Nodes (e.g., "newlead0", "newlead", "newlead3", "newlead2"): These nodes are responsible for sending the actual WhatsApp messages via the Gallabox API. They are configured with: Method: POST URL: https://server.gallabox.com/devapi/messages/whatsapp Authentication: apiKey and apiSecret are used in the headers. Body: Contains channelId, channelType (whatsapp), and recipient (including name and phone). WhatsApp Message Content: Includes type: "template" and templateName (e.g., testingrahi, wu2, testingrahi1). The bodyValues dynamically insert the contact's name and other details. Some messages also include buttonValues for quick replies (e.g., "Show me Brochure"). Logging and Updating Contact Status After a message is sent (or attempted), the workflow logs the interaction and updates the contact's record. Create Logs (e.g., "Create Logs", "Create Logs1", "Create Logs2", "Create Logs3"): These Supabase nodes record details of the message send attempt into the logsnurtureampere table. This includes: message_id (from the Gallabox API response body) phone and name of the contact disposition and mes_count (which is Count + 1 from the contacts table) last_sent (timestamp from Gallabox API response headers) statuscode and statusmessage (from Gallabox API response or error). These nodes are configured to "continueRegularOutput" on error, meaning the workflow will attempt to proceed even if logging fails. Status Code Check (e.g., "If StatusCode", "If StatusCode 202", "If StatusCode 203", "If StatusCode 204"): Immediately after attempting to create a log, an "If" node checks if the status_code from the message send attempt is "202" (indicating acceptance by the messaging service). Update Contact Row (e.g., "Update a row1", "Update a row2", "Update a row3", "Update a row4"): If the status code is 202, these Supabase nodes update the contacts_ampere table for the specific contact. The Count for the contact is incremented by 1 (Count + 1). The lastmessagesent field is updated with the date from the Gallabox API response headers. These nodes are also configured to "continueRegularOutput" on error. This structured flow ensures that contacts are nurtured through a sequence of WhatsApp messages, with each interaction logged and the contact's status updated for future reference and continuation of the journey.
By Rahi UppalCreate HubSpot companies & tasks from Jotform submissions with Google Sheets
🛠️ Workflow: Jotform → HubSpot Company + Task Automation Automatically create or update HubSpot companies and generate follow-up tasks whenever a Jotform is submitted. All logs are stored to Google Sheets for traceability, transparency, and debugging. --- ✅ Use Cases Capture marketing queries from your website’s Jotform form and immediately create tasks for your sales or SDR team. Enrich HubSpot companies with submitted domains, company names, and contact data. Automatically assign tasks to owners and keep all form submissions logged and auditable. Avoid manual handoffs — full automation from form submission → CRM. --- 🔍 How It Works (Step-by-Step) Jotform Trigger The workflow starts when a new submission is received via the Jotform webhook. Captured fields include: name, email, LinkedIn profile, company name, marketing budget, domain, and any specific query. --- Create or Update Company in HubSpot + Format Data The “Create Company” node ensures the submitted company is either created in HubSpot or updated if it already exists. A Formatter (Function) node standardizes the data — names, email, LinkedIn URL, domain, marketing budget, and query text. It composes a task title, generates a follow-up timestamp, and dynamically assigns an owner. --- Loop & HTTP Request – Create HubSpot Task The workflow loops through each formatted item. A Wait node prevents rate limit issues. It then sends an HTTP POST request to HubSpot’s Tasks API, creating a task with: Subject and body including the submission details Task status, priority, and type Assigned owner and associated company --- Loop & HTTP Request – Set Company Domain After tasks are created, another loop updates each HubSpot company record with the submitted domain. This ensures all HubSpot companies have proper website data for future enrichment. --- Storing Logs (Google Sheets) All processed submissions, responses, errors, and metadata are appended or updated in a Google Sheets document. This provides a complete audit trail — ideal for debugging, reporting, and performance monitoring. --- 🧩 Node Structure Overview | Step | Node | Description | |------|------|--------------| | 1️⃣ | Jotform Trigger | Receives form submission data | | 2️⃣ | HubSpot Create Company | Ensures company record exists | | 3️⃣ | Formatter / Function Node | Cleans & structures data, assigns owner, generates task fields | | 4️⃣ | Wait / Delay Node | Controls API call frequency | | 5️⃣ | HTTP Request (Create Task) | Pushes task to HubSpot | | 6️⃣ | HTTP Request (Update Domain) | Updates company domain in HubSpot | | 7️⃣ | Google Sheets Node | Logs inputs, outputs, and status | --- 📋 Requirements & Setup 🔑 HubSpot Private App Token with permissions to create companies, tasks, and update records 🌐 Jotform Webhook URL pointing to this workflow 📗 Google Sheets Credentials (OAuth or service account) with write access ✅ HubSpot app must have crm.objects.companies.write and crm.objects.tasks.write scopes ⚠️ Add retry or error-handling branches for failed API calls --- ⚙️ Customization Tips & Variations Add contact association: Modify the payload to also link the task with a HubSpot Contact (via email) so it appears in both company and contact timelines. Use fallback values: In the Formatter node, provide defaults like “Unknown Company” or “No query provided.” Dynamic owner assignment: Replace hash-based assignment with round-robin or territory logic. Conditional task creation: Add logic to only create tasks when certain conditions are met (e.g., budget > 0). Error branches: Capture failed HTTP responses and send Slack/Email alerts. Extended logs: Add response codes, errors, and retry counts to your Google Sheet for more transparency. --- 🎯 Benefits & Why You’d Use This ⚡ Speed & Automation — eliminate manual data entry into HubSpot 📊 Data Consistency — submissions are clean, enriched, and traceable 👀 Transparency — every action logged for full visibility 🌍 Scalability — handle hundreds of submissions effortlessly 🔄 Flexibility — adaptable for other use cases (support tickets, surveys, partnerships, etc.) --- ✨ Example Use Case A marketing form on your website captures partnership or franchise inquiries. This workflow instantly creates a HubSpot company, logs the inquiry as a task, assigns it to a regional manager, and saves a record in Google Sheets — all within seconds. --- Tags: HubSpot Jotform CRM GoogleSheets Automation LeadManagement
By Rahi UppalAutomated LinkedIn lead enrichment pipeline using Apollo.io & Google Sheets
LinkedIn to Apollo.io Lead Enrichment System with Google Sheets Automatically capture company and store details from LinkedIn posts, enrich them with domain names and key decision-maker (KDM) data from Apollo.io, and store everything neatly in Google Sheets. This workflow turns LinkedIn post data into a complete, structured lead database — fully automated from detection to enrichment. --- 🧾 Summary This workflow reads LinkedIn post data (in JSON format) received via a webhook, extracts company and location information using an AI agent, and progressively enriches the data in three stages: Find Company Domain using Apollo.io Fetch Key Decision Makers (KDMs) using the company domain Find Corporate Emails for those KDMs All results are automatically updated in Google Sheets, ensuring your lead list remains fresh, structured, and actionable. --- ⚙️ Prerequisites Before running the workflow, make sure you have: An Apollo.io API Key with company and people search access A connected Google Sheets account (OAuth2) A Webhook URL or LinkedIn scraper that feeds JSON-formatted post data (Optional) An OpenAI / AI Agent node to extract company names and store locations from post text --- 🧩 Example Input (Webhook JSON) Each LinkedIn post is received as a JSON object containing key clues: json { "index": 1, "text": "Excited to announce the opening of our new KFC store at Sky City Mall, Borivali East 🎉🍗\n\nLooking forward to welcoming you all to experience the Finger Lickin’ Good taste at our newest location!\n\nKFCIndia NewStoreOpening Borivali SkyCityMall Sapphirefoods", "CompanyName": "MockCompany", "NewStoreLocation": "optional", "PersonPosted": "https://linkedin.com/in/mockperson", "PostImage": null, "PostLink": "https://www.linkedin.com/search/results/all/?keywords=%23kfcindia&origin=HASHTAGFROM_FEED", "extracted_at": "2025-09-08T12:05:40.044101" } 🧠 Workflow Overview This workflow runs in three main stages, moving from LinkedIn data → company domain → people → emails. --- Stage 1: Company Name to Company Domain 🌐 Goal: Convert each company name into its website domain using Apollo.io. Nodes & Flow Schedule Trigger — Runs automatically (e.g., once a week) to check your Google Sheet for companies missing a domain. IF Node (Check for Missing Domain) — Filters only companies without a website. Loop Over Items — Iterates through each company record. HTTP Request – Find Domain — Calls the Apollo.io Company API to find the company’s domain (e.g., kfc.com). Update Row in Google Sheets — Writes the found domain back into the corresponding company row. Wait Node – Delay Between API Calls — Adds a small pause to avoid hitting rate limits. --- Stage 2: Domain to Top 10 KDMs 👥 Goal: Fetch key decision-makers (KDMs) from each company using Apollo.io. Nodes & Flow Schedule Trigger — Runs on a weekly cadence to check for companies with a domain but no KDMs. Get Rows from Google Sheets — Pulls company records ready for enrichment. IF Node (Check for Missing KDMs) — Ensures only companies without people data are processed. Loop Over Items — Processes one company at a time. HTTP Request – Find KDMs — Searches Apollo.io People API for top roles such as Founder, CEO, Head of Retail, etc. Update Row in Google Sheets — Saves the top 10 names and their LinkedIn profiles into your sheet. Wait Node — Adds a delay to manage rate limits safely. --- Stage 3: KDM Profile to Email Enricher 📧 Goal: Find and store verified corporate email addresses for each KDM. Nodes & Flow Schedule Trigger — Weekly automation trigger. Get Rows from Google Sheets — Pulls KDMs that have LinkedIn profiles but no email yet. IF Node (Check for Missing Emails) — Ensures only valid records are processed. Code Node – Prepare Data — Organizes LinkedIn profile and domain information. HTTP Request – Find Email — Queries Apollo.io’s email enrichment endpoint using the LinkedIn URL and domain. Code Node – Format Response — Cleans and formats the email result. Wait Node — Adds delay to avoid request bursts. Update Row in Google Sheets — Writes the verified email address back to the corresponding KDM entry and marks it as “Updated – email search.” 🎉 --- 🧰 Setup Instructions Connect APIs & Credentials Add your Apollo.io API Key under HTTP Request credentials. Connect your Google Sheets account (OAuth2). Customize Sheet Structure Add columns for: CompanyName, Domain, KDMs, LinkedInProfile, Email, Status Set Schedule Frequency Each Schedule Trigger can be configured separately (e.g., Stage 1 every Monday, Stage 2 every Wednesday). Optional: AI Extraction Node Use an AI model (like OpenAI or Gemini) to extract company name and location from LinkedIn post text before enrichment. --- 🧰 Custom Node Names (Recommended for Clarity) | Node Type | Recommended Name | Description | |------------|------------------|-------------| | HTTP Request (Company) | Find Company Domain (Apollo.io) | Searches for the company’s official domain | | HTTP Request (People) | Find Key Decision Makers (Apollo.io) | Retrieves top company contacts | | HTTP Request (Emails) | Find Corporate Emails (Apollo.io) | Gets verified email addresses for each contact | | Wait Node | API Delay (Rate Limit Buffer) | Adds delay to avoid hitting API rate limits | | Code Node | Prepare KDM Data | Organizes input data for API calls | --- ⚙️ Customization Tips Multiple Campaigns: Duplicate the workflow for different industries or store categories, updating parameters as needed. Batch Size: Adjust API request limits (e.g., 100 per batch) based on your Apollo.io plan. Filtering: Add IF conditions to skip records already marked as “Completed.” Dashboards: Build visual analytics directly in Google Sheets or connect to Looker Studio. Enrichment: Combine with CRM systems (like HubSpot or Close) using company domain or lead email as the linking key. --- 🔐 Security and Publishing Notes 🔒 Never hardcode API keys in workflow exports. Use n8n credentials or environment variables instead. 🚫 Replace sensitive values (like API keys or Sheet IDs) with placeholders before sharing. 🔐 Keep your Google Sheet private unless intentionally shared. --- 🧩 Troubleshooting No Data in Sheets: Check API response for data[] and verify Split Out configuration. Duplicate Rows: Ensure the “Matching Column” in Google Sheets is correctly set (e.g., CompanyName or LinkedInProfile). Rate Limits: Add Wait Nodes or reduce batch size. Mapping Errors: Confirm Google Sheet headers exactly match node field mappings. Timezone Adjustments: Apollo.io timestamps are in UTC — convert to local time if needed. --- 🎯 Example Use Case Every week, this workflow scans new LinkedIn store-opening posts. It extracts company names (like KFC India), finds their domains and top executives through Apollo.io, retrieves their emails, and logs everything in a Google Sheet — ready for your sales team to reach out. --- Tags: LinkedIn Apollo Automation LeadGeneration GoogleSheets MarketingOps DataEnrichment
By Rahi UppalTracking cold email engagement metrics using Smartlead and Google Sheets
Workflow: Track Email Campaign Engagement Analytics with Smartlead and Google Sheets Automatically fetch lead-level email engagement analytics (opens, clicks, replies, unsubscribes, bounces) from Smartlead and update them in Google Sheets. Use this to keep a single, always-fresh source of truth for campaign performance and sequence effectiveness. --- Summary Pull Smartlead campaign analytics on a schedule and write them to a Google Sheet (append or update). Works with pagination, avoids duplicates via a stable key, and is ready for dashboards, pivots, or BI tools. --- What This Workflow Does Collects campaign stats from Smartlead (per-lead, per-sequence). Handles pagination safely (offset/limit). Writes to Google Sheets using appendOrUpdate with a matching column to prevent duplicates. Can run on a schedule for near real-time analytics. --- Node Structure Overview | Step | Node | Purpose | |---|---|---| | 1️⃣ | Schedule Trigger | Starts the workflow on a cadence (e.g., hourly) | | 2️⃣ | Code (Pagination Generator) | Emits {offset, limit} pairs (e.g., 0..9900, step 100) | | 3️⃣ | Split in Batches | Sends each pagination pair to the API sequentially | | 4️⃣ | HTTP Request (Smartlead) | GET /campaigns/{campaign_id}/statistics with offset/limit | | 5️⃣ | Split Out | Turns the API data[] array into one item per lead record | | 6️⃣ | Google Sheets (appendOrUpdate) | Upserts rows by stats_id into EngagedLeads tab | | 7️⃣ | Loop Back | Continues until all batches have been processed | --- Step-by-Step Setup Prerequisites Smartlead account + API key with access to campaign statistics. Google account + Google Sheets OAuth connected in n8n. Create the Google Sheet Spreadsheet name: Email Analytics (can be anything). Tab name: EngagedLeads. Add these exact headers (first row): leadname, leademail, lead_category, sequencenumber, statsid, emailsubject, senttime, opentime, clicktime, reply_time, opencount, clickcount, isunsubscribed, isbounced Configure the Schedule Trigger Choose a frequency (e.g., every 2 hours). If you’re testing, set a single run or a short cadence. Configure the Code Node (Pagination) Emit N items like: json { "offset": 0, "limit": 100 } { "offset": 100, "limit": 100 } ... 100 is a good default limit. For up to 10,000 records, generate 100 offsets. Configure the Smartlead API Node Method: GET URL: https://server.smartlead.ai/api/v1/campaigns/{campaign_id}/statistics Query parameters: apikey = <YOURSMARTLEADAPIKEY> offset = {{ $json.offset }} limit = {{ $json.limit }} Map response to JSON. Split Out the Response Use a Split Out (or similar) to iterate over data[] so each lead record is one item. Google Sheets Node (Append or Update) Operation: appendOrUpdate. Document: Your Email Analytics sheet. Sheet/Tab: EngagedLeads. Matching Column: stats_id. Map fields from Smartlead response to sheet columns: lead_name ← lead name (or composed from first/last if provided) lead_email ← email lead_category ← category/type if available sequence_number ← sequence step number statsid ← stable identifier (e.g., Smartlead statsid or message id) email_subject ← subject senttime, opentime, clicktime, replytime ← timestamps opencount, clickcount ← integers isunsubscribed, isbounced ← booleans If the same stats_id arrives again, the row is updated, not appended. Test and Activate Run once manually to verify API and sheet mapping. Check the sheet for new/updated rows. Activate the workflow to run automatically. --- Smartlead API Reference (Used by This Workflow) Endpoint GET https://server.smartlead.ai/api/v1/campaigns/{campaign_id}/statistics Required query parameters api_key (string) offset (number) limit (number) Typical response (trimmed example) json { "data": [ { "lead_name": "Jane Doe", "lead_email": "jane@example.com", "sequence_number": 2, "stats_id": "15b6ff3a-...-b2b9f343c2e1", "email_subject": "Quick intro", "sent_time": "2025-10-08T10:18:55.496Z", "open_time": "2025-10-08T10:20:10.000Z", "click_time": null, "reply_time": null, "open_count": 1, "click_count": 0, "is_unsubscribed": false, "is_bounced": false } ], "total": 1234 } Google Sheets Structure (Recommended) Spreadsheet: Email Analytics Tab: EngagedLeads Columns:leadname, leademail, lead_category, sequencenumber, statsid, emailsubject, senttime, opentime, clicktime, reply_time, opencount, clickcount, isunsubscribed, isbounced Matching Column: stats_id (prevents duplicates and allows updates) Customization Tips Multiple Campaigns Duplicate the workflow and set a different {campaign_id} and/or write results to a separate tab in your Google Sheet. Batch Size Increase or decrease the limit value (e.g., 200) in your Code node if you want fewer or more API calls. Filtering Add a Code or IF node to skip rows where isbounced = true or isunsubscribed = true. Dashboards Create a new tab named Dashboard in Google Sheets and visualize your data using built-in charts or connect it to Looker Studio for advanced visualization. Enrichment Join this dataset with your CRM data (e.g., HubSpot or Salesforce) using lead_email as a key to gain deeper customer insights. --- Security and Publishing Notes Do not hardcode your Smartlead API key in the workflow export. Use n8n credentials or environment variables instead. When sharing the template publicly, replace sensitive values with placeholders like: <YOURSMARTLEADAPIKEY> and <YOURGOOGLESHEETID>. Keep your Google Sheet private unless you intentionally want to share it publicly. --- Troubleshooting No rows in Sheets Verify that the API response includes data[], confirm that the Split Out node is configured correctly, and check field mappings. Duplicates Ensure the Google Sheets node has its matching column set to stats_id. Rate Limits Increase the schedule interval, add a short Wait node between batches, or reduce the limit size. Mapping Errors Ensure that column names in Sheets exactly match your field mappings — they are case-sensitive. Timezone Differences Smartlead timestamps are in UTC. Convert them downstream if your local timezone is different. --- Example Use Case Run this workflow hourly to maintain a live, company-wide Email Engagement Sheet. Sales teams can monitor replies and active leads. Marketing teams can track open and click rates by sequence. Operations can export monthly summaries — no Smartlead login required. --- Tags Smartlead EmailMarketing Automation GoogleSheets Analytics CRM MarketingOps
By Rahi Uppal