Send personalized birthday and anniversary emails with Google Sheets, Gemini, and Gmail

{"id":"Pl4jC7o8f9EoORmo","meta":{"instanceId":"84598b41d26eb2b7aa8f6258e3e33525d70f194053bb1eca12a4e88b9ad881d9"},"name":"N-0016 Birthday / Milestone Smart Message Generator","tags":[],"nodes":[{"id":"d6b5c3fb-7442-4802-99d1-4a557aae1fdf","name":"Overview — Read This First","type":"n8n-nodes-base.stickyNote","position":[32,-160],"parameters":{"width":520,"height":480,"content":"## Birthday / Milestone Smart Message Generator\n\n## How it works\nRuns daily at 9:01 AM, reads all client rows from Google Sheets and checks if today matches anyone's Birthday or Anniversary. If yes, it picks a personalised investment suggestion based on age, risk profile and occasion, then asks Gemini to write a warm human message. Finally it sends the email directly to the client.\n\n## Setup steps\n1. Connect Google Sheets OAuth2 credential\n2. Add your Google Gemini API key\n3. Connect Gmail OAuth2 credential\n4. Update the Sheet ID in the Read Client List node\n5. Confirm your sheet has columns: Client Name, Email, Advisor Name, Birthday, Anniversary, Relationship Type, Client Age, Risk Profile\n6. Adjust trigger time in the Schedule node if needed"},"typeVersion":1},{"id":"e6bb4cc0-d449-414c-b287-91ca64534fd5","name":"Group — Trigger and Fetch","type":"n8n-nodes-base.stickyNote","position":[80,544],"parameters":{"color":7,"width":436,"height":332,"content":"## Trigger and Data Fetch\nSchedule fires daily at 9:01 AM and reads every client row from the Google Sheet. All client data flows into the next step for date checking."},"typeVersion":1},{"id":"97afe64f-024c-4b54-af73-daeb6ff9d66c","name":"Group — Filter and Prep","type":"n8n-nodes-base.stickyNote","position":[544,464],"parameters":{"color":7,"width":620,"height":524,"content":"## Date Check and Variable Preparation\nFilters only clients whose Birthday or Anniversary matches today. Settings node holds all config. Prepare Variables builds every field needed for AI and email in one place."},"typeVersion":1},{"id":"784ea2a7-d35e-451d-8b76-2f9e11c33c01","name":"Group — AI and Email","type":"n8n-nodes-base.stickyNote","position":[1184,464],"parameters":{"color":7,"width":980,"height":340,"content":"## AI Generation and Email Delivery\nInvestment Suggestion picks the right idea by age, risk and occasion. Gemini writes a warm personalised message. Gmail sends it directly to the client."},"typeVersion":1},{"id":"e08e8bae-3dc0-4c53-a012-33bd0c9de4db","name":"Run Every Day at 9 AM","type":"n8n-nodes-base.scheduleTrigger","position":[160,688],"parameters":{"rule":{"interval":[{"triggerAtHour":9,"triggerAtMinute":1}]}},"typeVersion":1.3},{"id":"cb740280-cb37-4e41-8329-2db45289310e","name":"Read Client List from Google Sheet","type":"n8n-nodes-base.googleSheets","position":[384,688],"parameters":{"options":{},"sheetName":{"__rl":true,"mode":"list","value":"gid=0","cachedResultName":"Sheet1"},"documentId":{"__rl":true,"mode":"list","value":"1ND80A43A8RFUsKo0LqJmkz7C6NCmJg_y0QsHdKucPTc","cachedResultName":"Client_details"}},"credentials":{"googleSheetsOAuth2Api":{"id":"CF3TiZ0bzlFyaSE4","name":"Google Sheets OAuth2 API"}},"typeVersion":4.5},{"id":"2b47c089-5870-4c7d-a258-e995557bd668","name":"Settings — Change These Before Running","type":"n8n-nodes-base.set","position":[592,688],"parameters":{"options":{},"assignments":{"assignments":[{"id":"cfg-001","name":"Trigger Hour","type":"number","value":9},{"id":"cfg-002","name":"Advisor Firm Name","type":"string","value":"Wealth Advisory Services"},{"id":"cfg-003","name":"Premium Tone","type":"string","value":"formal + warm"},{"id":"cfg-004","name":"Normal Tone","type":"string","value":"friendly"},{"id":"cfg-005","name":"Premium Label","type":"string","value":"premium"},{"id":"cfg-006","name":"Birthday Label","type":"string","value":"Birthday"},{"id":"cfg-007","name":"Anniversary Label","type":"string","value":"Anniversary"},{"id":"cfg-008","name":"Default Risk Profile","type":"string","value":"moderate"},{"id":"cfg-009","name":"Default Client Age","type":"number","value":35},{"id":"cfg-010","name":"Young Age Limit","type":"number","value":30},{"id":"cfg-011","name":"Senior Age Limit","type":"number","value":50},{"id":"cfg-012","name":"Email Subject Birthday Prefix","type":"string","value":"Happy Birthday"},{"id":"cfg-013","name":"Email Subject Anniversary Prefix","type":"string","value":"Happy Anniversary"},{"id":"cfg-014","name":"Email Subject Suffix","type":"string","value":"A Special Gift Awaits Inside"},{"id":"cfg-015","name":"AI Message Paragraph Count","type":"string","value":"3-4 short paragraphs"},{"id":"cfg-016","name":"Today Date","type":"string","value":"={{ new Date().toISOString().slice(0,10) }}"},{"id":"cfg-017","name":"Today Month Day","type":"string","value":"={{ new Date().toISOString().slice(5,10) }}"}]}},"typeVersion":3.4},{"id":"98bd5c65-b49e-4f7b-85a5-9bed54cda4f9","name":"Does Today Match Birthday or Anniversary?","type":"n8n-nodes-base.if","position":[800,688],"parameters":{"options":{},"conditions":{"options":{"version":3,"leftValue":"","caseSensitive":true,"typeValidation":"strict"},"combinator":"or","conditions":[{"id":"525112a7-053f-4980-9162-b9c3a60c4557","operator":{"type":"string","operation":"equals"},"leftValue":"={{ new Date($('Read Client List from Google Sheet').item.json.Birthday).toISOString().slice(5,10) }}","rightValue":"={{ new Date().toISOString().slice(5,10) }}"},{"id":"d62f0169-61f0-4785-a627-b60360bdfcf7","operator":{"type":"string","operation":"equals"},"leftValue":"={{ new Date($('Read Client List from Google Sheet').item.json.Anniversary).toISOString().slice(5,10) }}","rightValue":"={{ new Date().toISOString().slice(5,10) }}"}]}},"typeVersion":2.3},{"id":"2951dc2d-3cbb-4dcc-abf2-0e6d6a59495a","name":"No Special Date Today — Stop Here","type":"n8n-nodes-base.noOp","position":[1008,800],"parameters":{},"typeVersion":1},{"id":"6b065c04-fcd0-4849-8a92-6885a75bc334","name":"Prepare All Client and Message Variables","type":"n8n-nodes-base.set","position":[1008,592],"parameters":{"options":{},"assignments":{"assignments":[{"id":"var-001","name":"Client Name","type":"string","value":"={{ $('Read Client List from Google Sheet').item.json['Client Name'] }}"},{"id":"var-002","name":"Client Email","type":"string","value":"={{ $('Read Client List from Google Sheet').item.json['Email'] }}"},{"id":"var-003","name":"Advisor Name","type":"string","value":"={{ $('Read Client List from Google Sheet').item.json['Advisor Name'] }}"},{"id":"var-004","name":"Client Birthday","type":"string","value":"={{ $('Read Client List from Google Sheet').item.json['Birthday'] }}"},{"id":"var-005","name":"Client Anniversary","type":"string","value":"={{ $('Read Client List from Google Sheet').item.json['Anniversary'] }}"},{"id":"var-006","name":"Relationship Type","type":"string","value":"={{ $('Read Client List from Google Sheet').item.json['Relationship Type (Premium / Normal)'] }}"},{"id":"var-007","name":"Client Age","type":"number","value":"={{ $('Read Client List from Google Sheet').item.json['Client_Age'] || $('Settings — Change These Before Running').first().json['Default Client Age'] }}"},{"id":"var-008","name":"Risk Profile","type":"string","value":"={{ $('Read Client List from Google Sheet').item.json['Risk Profile'] || $('Settings — Change These Before Running').first().json['Default Risk Profile'] }}"},{"id":"var-009","name":"Occasion","type":"string","value":"={{ new Date($('Read Client List from Google Sheet').item.json['Birthday']).toISOString().slice(5,10) === new Date().toISOString().slice(5,10) ? $('Settings — Change These Before Running').first().json['Birthday Label'] : $('Settings — Change These Before Running').first().json['Anniversary Label'] }}"},{"id":"var-010","name":"Message Tone","type":"string","value":"={{ $('Read Client List from Google Sheet').item.json['Relationship Type (Premium / Normal)'].toLowerCase() === $('Settings — Change These Before Running').first().json['Premium Label'] ? $('Settings — Change These Before Running').first().json['Premium Tone'] : $('Settings — Change These Before Running').first().json['Normal Tone'] }}"},{"id":"var-011","name":"Is Premium Client","type":"boolean","value":"={{ $('Read Client List from Google Sheet').item.json['Relationship Type (Premium / Normal)'].toLowerCase() === $('Settings — Change These Before Running').first().json['Premium Label'] }}"},{"id":"var-012","name":"Is Birthday Today","type":"boolean","value":"={{ new Date($('Read Client List from Google Sheet').item.json['Birthday']).toISOString().slice(5,10) === new Date().toISOString().slice(5,10) }}"},{"id":"var-013","name":"Today Date","type":"string","value":"={{ $('Settings — Change These Before Running').first().json['Today Date'] }}"},{"id":"var-014","name":"Email Subject","type":"string","value":"={{ (new Date($('Read Client List from Google Sheet').item.json['Birthday']).toISOString().slice(5,10) === new Date().toISOString().slice(5,10) ? $('Settings — Change These Before Running').first().json['Email Subject Birthday Prefix'] : $('Settings — Change These Before Running').first().json['Email Subject Anniversary Prefix']) + ', ' + $('Read Client List from Google Sheet').item.json['Client Name'] + '! ' + $('Settings — Change These Before Running').first().json['Email Subject Suffix'] }}"},{"id":"var-015","name":"AI Prompt","type":"string","value":"={{\n'Write a personalized ' +\n(\n  new Date($('Read Client List from Google Sheet').item.json['Birthday']).toISOString().slice(5,10) ===\n  new Date().toISOString().slice(5,10)\n  ? 'Birthday'\n  : 'Anniversary'\n) +\n' message for a client.\\n\\n' +\n\n'Client Name: ' + $('Read Client List from Google Sheet').item.json['Client Name'] + '\\n' +\n\n'Occasion: ' +\n(\n  new Date($('Read Client List from Google Sheet').item.json['Birthday']).toISOString().slice(5,10) ===\n  new Date().toISOString().slice(5,10)\n  ? 'Birthday'\n  : 'Anniversary'\n) + '\\n' +\n\n'Tone: ' +\n(\n  $('Read Client List from Google Sheet').item.json['Relationship Type (Premium / Normal)']\n  .toLowerCase() === 'premium'\n  ? 'formal + warm'\n  : 'friendly'\n) +\n\n'\\n\\nInstructions:\\n' +\n'- Write in 3-4 short paragraphs\\n' +\n'- Each paragraph should be 1-2 lines only\\n' +\n'- Add proper line breaks between paragraphs\\n' +\n'- Keep it warm and professional\\n' +\n'- Add a subtle financial touch like future planning\\n' +\n'- Do NOT write everything in one paragraph\\n' +\n'- Format properly like a real message\\n' +\n'- End ONLY once with:\\nWarm regards,\\n' +\n$('Read Client List from Google Sheet').item.json['Advisor Name'] + '\\n' +\n'- Do NOT repeat regards or name again\\n' +\n'- Do NOT add any extra signature or closing line'\n}}"},{"id":"var-016","name":"Advisor Firm","type":"string","value":"={{ $('Settings — Change These Before Running').first().json['Advisor Firm Name'] }}"}]}},"typeVersion":3.4},{"id":"04ee1778-163f-45da-b5fd-00dc2530ad1b","name":"Pick Smart Investment Suggestion by Age and Risk","type":"n8n-nodes-base.code","position":[1248,592],"parameters":{"jsCode":"const v = $('Prepare All Client and Message Variables').item.json;\n\nconst today        = new Date();\nconst todayMMDD    = String(today.getMonth()+1).padStart(2,'0') + '-' + String(today.getDate()).padStart(2,'0');\nconst birthdayMMDD = new Date(v['Client Birthday']).toISOString().slice(5,10);\nconst isBirthday   = v['Is Birthday Today'] === true || v['Is Birthday Today'] === 'true';\nconst isPremium    = v['Is Premium Client'] === true || v['Is Premium Client'] === 'true';\nconst age          = parseInt(v['Client Age']) || 35;\nconst risk         = (v['Risk Profile'] || 'moderate').toLowerCase();\n\nconst birthdayByAge = {\n  young: [\n    { suggestion: \"begin a small monthly SIP in an index fund\", instrument: \"Index Fund SIP\", why: \"Starting early gives the power of compounding — even a small amount today becomes a life-changing sum by the time you celebrate your next big milestone.\" },\n    { suggestion: \"open a Digital Gold account and start with even Rs.100\", instrument: \"Digital Gold\", why: \"Gold has been a symbol of wealth and celebration for generations — owning even a little on your birthday is a tradition that holds value forever.\" },\n    { suggestion: \"start a Recurring Deposit in your name\", instrument: \"Recurring Deposit\", why: \"A simple, risk-free way to build a birthday fund — every year this date, you will have something beautiful growing quietly in the background.\" }\n  ],\n  mid: [\n    { suggestion: \"start a Sovereign Gold Bond (SGB) in your name\", instrument: \"Sovereign Gold Bond\", why: \"SGBs give you gold appreciation plus annual interest — a government-backed gift to yourself that grows steadily and safely year after year.\" },\n    { suggestion: \"begin a balanced advantage mutual fund SIP today\", instrument: \"Balanced Advantage Fund\", why: \"On this milestone birthday, let your money grow with the right balance of equity and safety — a truly smart gift you give your future self.\" },\n    { suggestion: \"open a PPF account and make your first contribution\", instrument: \"Public Provident Fund (PPF)\", why: \"PPF is tax-free and guaranteed — a long-term gift to yourself that quietly compounds year after year, a tradition worth starting today.\" }\n  ],\n  senior: [\n    { suggestion: \"start a Senior Citizens Savings Scheme (SCSS) or a debt fund SIP\", instrument: \"SCSS / Debt Fund\", why: \"At this stage, stability and steady returns matter most — this special day is a perfect moment to lock in something secure and reliable for the years ahead.\" },\n    { suggestion: \"begin a monthly RD or fixed deposit in your name\", instrument: \"Fixed Deposit / RD\", why: \"A steady, guaranteed return that marks this birthday permanently — every renewal date will bring back the memory of today.\" },\n    { suggestion: \"invest in a conservative hybrid fund as a birthday tradition\", instrument: \"Conservative Hybrid Fund\", why: \"A gentle balance of growth and safety — ideal to start on your birthday as a tradition that honors this milestone for years to come.\" }\n  ]\n};\n\nconst anniversarySuggestions = [\n  { suggestion: \"start a joint Sovereign Gold Bond together\", instrument: \"Sovereign Gold Bond\", why: \"Gold bonds grow steadily and safely — starting one together on your anniversary means every year you can watch both your bond and your wealth grow side by side.\" },\n  { suggestion: \"begin a joint mutual fund SIP in both your names\", instrument: \"Joint Mutual Fund SIP\", why: \"A shared investment started on your anniversary is a beautiful tradition — a way of saying you are not just celebrating today, but building tomorrow together.\" },\n  { suggestion: \"open a joint Recurring Deposit to mark this milestone\", instrument: \"Joint Recurring Deposit\", why: \"Simple, safe and deeply symbolic — a joint RD started on your anniversary grows quietly year after year, just like your journey together.\" },\n  { suggestion: \"invest in a balanced fund together as your anniversary tradition\", instrument: \"Balanced Advantage Fund\", why: \"Every anniversary, you can look back at how this investment has grown alongside your relationship — a living, growing memory of this very day.\" },\n  { suggestion: \"start a Silver ETF together as a precious memory\", instrument: \"Silver ETF\", why: \"Silver is timeless and precious, just like the bond you share — a joint Silver ETF started today becomes a symbol you can revisit every anniversary.\" }\n];\n\nlet pool;\nif (isBirthday) {\n  const group = age < 30 ? 'young' : age <= 50 ? 'mid' : 'senior';\n  pool = birthdayByAge[group];\n} else {\n  pool = anniversarySuggestions;\n}\n\nconst riskOffset = risk === 'high' ? 0 : risk === 'moderate' ? 1 : 2;\nconst idx = riskOffset % pool.length;\nconst chosen = pool[idx];\n\nconst premiumIntro = `As your trusted advisor, I would love to suggest something meaningful for this special day —`;\nconst normalIntro  = `Here is a little idea to make this day even more memorable —`;\n\nreturn [{\n  json: {\n    'Client Name':            v['Client Name'],\n    'Client Email':           v['Client Email'],\n    'Advisor Name':           v['Advisor Name'],\n    'Client Birthday':        v['Client Birthday'],\n    'Client Anniversary':     v['Client Anniversary'],\n    'Relationship Type':      v['Relationship Type'],\n    'Client Age':             v['Client Age'],\n    'Risk Profile':           v['Risk Profile'],\n    'Occasion':               v['Occasion'],\n    'Message Tone':           v['Message Tone'],\n    'Is Premium Client':      v['Is Premium Client'],\n    'Is Birthday Today':      v['Is Birthday Today'],\n    'Today Date':             v['Today Date'],\n    'Email Subject':          v['Email Subject'],\n    'AI Prompt':              v['AI Prompt'],\n    'Advisor Firm':           v['Advisor Firm'],\n    'Investment Suggestion':  chosen.suggestion,\n    'Investment Instrument':  chosen.instrument,\n    'Investment Why':         chosen.why,\n    'Investment Intro':       isPremium ? premiumIntro : normalIntro,\n    'Age Group':              isBirthday ? (age < 30 ? 'Young (under 30)' : age <= 50 ? 'Mid (30-50)' : 'Senior (50+)') : 'Couple (Anniversary)'\n  }\n}];"},"typeVersion":2},{"id":"9553e448-0539-42a8-a832-d4506d03618a","name":"Ask Gemini to Write Personalised Wish","type":"@n8n/n8n-nodes-langchain.googleGemini","position":[1440,592],"parameters":{"modelId":{"__rl":true,"mode":"list","value":"models/gemini-2.5-flash","cachedResultName":"models/gemini-2.5-flash"},"options":{},"messages":{"values":[{"content":"={{ \n$json['AI Prompt'] +\n\n'\\n\\n--- Special Investment Suggestion Section ---\\n' +\n'After your warm wishes, naturally include this investment idea as a thoughtful suggestion:\\n\\n' +\n'Intro line: ' + $json['Investment Intro'] + '\\n' +\n'Instrument: ' + $json['Investment Instrument'] + '\\n' +\n'Suggestion: ' + $json['Investment Suggestion'] + '\\n' +\n'Why it is meaningful: ' + $json['Investment Why'] + '\\n\\n' +\n'Formatting rules for this section:\\n' +\n'- Write it as 1 short paragraph (2-3 lines max)\\n' +\n'- Frame it as a warm memory idea, not financial advice\\n' +\n'- Use emotional words: memorable, cherish, mark this day, grow with you, special tradition\\n' +\n'- Do NOT use: invest, portfolio, returns, market, profit, financial planning, wealth management\\n' +\n'- It should feel like a heartfelt suggestion from a trusted friend, not a sales pitch\\n' +\n'- Place this paragraph between the wishes and the closing signature\\n' +\n'- Do NOT add a separate heading like Investment Idea or Suggestion\\n'\n}}"}]},"builtInTools":{}},"credentials":{"googlePalmApi":{"id":"n5sKBxpETaEXUSG2","name":"Google Gemini(PaLM) Api account"}},"typeVersion":1.1},{"id":"20e3917e-56c1-4ecd-8edd-4332c78d1efc","name":"Format Email Body and Delivery Fields","type":"n8n-nodes-base.set","position":[1728,592],"parameters":{"options":{},"assignments":{"assignments":[{"id":"email-001","name":"Final Email Body","type":"string","value":"={{ $json['content']['parts'][0]['text'].replace(/\\n\\n/g, '<br><br>').replace(/\\n/g, '<br>') }}"},{"id":"email-002","name":"Send To","type":"string","value":"={{ $('Pick Smart Investment Suggestion by Age and Risk').item.json['Client Email'] }}"},{"id":"email-003","name":"Subject Line","type":"string","value":"={{ $('Pick Smart Investment Suggestion by Age and Risk').item.json['Email Subject'] }}"},{"id":"email-004","name":"Display Sender Name","type":"string","value":"={{ $('Pick Smart Investment Suggestion by Age and Risk').item.json['Advisor Name'] + ' — ' + $('Pick Smart Investment Suggestion by Age and Risk').item.json['Advisor Firm'] }}"},{"id":"email-005","name":"Client Name","type":"string","value":"={{ $('Pick Smart Investment Suggestion by Age and Risk').item.json['Client Name'] }}"},{"id":"email-006","name":"Occasion","type":"string","value":"={{ $('Pick Smart Investment Suggestion by Age and Risk').item.json['Occasion'] }}"},{"id":"email-007","name":"Investment Instrument Used","type":"string","value":"={{ $('Pick Smart Investment Suggestion by Age and Risk').item.json['Investment Instrument'] }}"},{"id":"email-008","name":"Age Group","type":"string","value":"={{ $('Pick Smart Investment Suggestion by Age and Risk').item.json['Age Group'] }}"},{"id":"email-009","name":"Sent On Date","type":"string","value":"={{ $('Pick Smart Investment Suggestion by Age and Risk').item.json['Today Date'] }}"}]}},"typeVersion":3.4},{"id":"dd1ee435-4696-4a00-be20-9d5ffefc3cae","name":"Send Personalised Wish Email to Client","type":"n8n-nodes-base.gmail","position":[1952,592],"webhookId":"d0002302-b347-4aa3-965c-a287c5b595a9","parameters":{"sendTo":"={{ $json['Send To'] }}","message":"={{ $json['Final Email Body'] }}","options":{"senderName":"={{ $json['Display Sender Name'] }}"},"subject":"={{ $json['Subject Line'] }}"},"credentials":{"gmailOAuth2":{"id":"bhTwTAB1KntHO05R","name":"Gmail OAuth2 API"}},"typeVersion":2.2}],"active":false,"pinData":{},"settings":{"binaryMode":"separate","executionOrder":"v1"},"versionId":"3157068e-fcd8-4eb9-937d-2aa8996e0078","connections":{"Run Every Day at 9 AM":{"main":[[{"node":"Read Client List from Google Sheet","type":"main","index":0}]]},"Read Client List from Google Sheet":{"main":[[{"node":"Settings — Change These Before Running","type":"main","index":0}]]},"Ask Gemini to Write Personalised Wish":{"main":[[{"node":"Format Email Body and Delivery Fields","type":"main","index":0}]]},"Format Email Body and Delivery Fields":{"main":[[{"node":"Send Personalised Wish Email to Client","type":"main","index":0}]]},"Prepare All Client and Message Variables":{"main":[[{"node":"Pick Smart Investment Suggestion by Age and Risk","type":"main","index":0}]]},"Settings — Change These Before Running":{"main":[[{"node":"Does Today Match Birthday or Anniversary?","type":"main","index":0}]]},"Does Today Match Birthday or Anniversary?":{"main":[[{"node":"Prepare All Client and Message Variables","type":"main","index":0}],[{"node":"No Special Date Today — Stop Here","type":"main","index":0}]]},"Pick Smart Investment Suggestion by Age and Risk":{"main":[[{"node":"Ask Gemini to Write Personalised Wish","type":"main","index":0}]]}}}