Score and assign wealth advisory leads with Google Sheets, Gmail, and Google Gemini

Quick overview This workflow captures advisor and lead submissions via webhooks, scores and enriches them with Google Gemini, and stores them in Google Sheets as a lightweight CRM. It auto-assigns leads to available advisors, sends Gmail notifications, runs daily follow-up reminders, and appends an activity log and dashboard metrics. How it works 1. Receives a POST request via webhook with a recordtype of advisor or lead, then normalizes and standardizes the incoming fields. 2. For advisor records, it reads the AdvisorsCRM Google Sheet to detect duplicates, calculates a wealth score and grade, and uses Google Gemini to generate a structured advisor profile summary. 3. It saves (append or update) the advisor record to the AdvisorsCRM sheet and appends an advisor activity entry to the ActivityLog sheet. 4. For lead records, it reads the LeadsCRM Google Sheet to detect duplicates, calculates a lead score and priority, and uses Google Gemini to generate structured enrichment fields. 5. It reads available advisors from the AdvisorsCRM sheet, assigns the best-fit advisor based on needs/segment/city/capacity rules, then saves (append or update) the lead in LeadsCRM and drafts a Gmail notification to the assigned advisor. 6. It appends a lead activity entry to the ActivityLog sheet and responds to the original webhook request with a JSON success payload. 7. On a daily schedule, it reads LeadsCRM for due follow-ups, sends Gmail reminder emails to both the lead and assigned advisor, stamps the lead notes with a reminder timestamp, logs the reminder in ActivityLog, and writes daily summary metrics to a Dashboard sheet. 8. Receives advisor follow-up updates via a second webhook, merges the update into the matching lead row in LeadsCRM, optionally emails the lead next steps, logs the update to ActivityLog, and returns a JSON response. Setup 1. Connect Google Sheets credentials and update the spreadsheet ID/sheet tabs for AdvisorsCRM, LeadsCRM, ActivityLog, and Dashboard to match your file. 2. Ensure the Google Sheets columns match the fields written by the workflow (including advisoremail for advisor matching and leademail/leadid for lead matching). 3. Connect a Google Gemini (Google AI) credential for the two AI enrichment steps and confirm the selected model name is available in your account. 4. Connect a Gmail credential and set sending behavior as needed (one step creates advisor email drafts and other steps send follow-up and update emails). 5. Copy the two webhook URLs (advisor-lead-crm and advisor-followup-update) into your intake forms or source systems and send test POST payloads including the required identifiers (for follow-up updates, leadid is required). 6. Review the schedule trigger timing for daily follow-ups/dashboard generation and adjust it to your preferred run time before activating the workflow. Additional info How To Customize Nodes - Modify scoring logic in Check Duplicate And Score Advisor - Adjust AI prompts in AI Summarize Advisor - Change assignment rules in lead matching logic - Update email templates in Gmail nodes - Modify follow-up conditions in filter node Add-ons - Slack notifications for advisors - WhatsApp alerts for leads - Advanced AI matching (semantic similarity) - Real-time dashboard (Power BI / Looker Studio) - Capacity-based advisor load balancing Use Case Examples 1. Assign incoming leads to best financial advisor automatically 2. Manage advisor onboarding with AI scoring 3. Automate follow-ups for investment prospects 4. Track CRM activity and performance logs 5. Build internal dashboards for sales teams There can be many more use cases depending on business requirements. Troubleshooting Guide | Issue | Possible Cause | Solution | |------|--------------|---------| | Workflow stops after filter | No matching data | Check conditions in Filter Due Followups | | Emails not sending | Gmail not configured | Reconnect Gmail credentials | | Duplicate advisors | Matching logic issue | Check email/phone n