Qualify student leads from Google Sheets with OpenAI, Twilio, Telegram and Gmail
Go to Workflow
Rahul Joshi
Description
Quick overview
This workflow polls a Google Sheets lead intake every 15 minutes, uses OpenAI (GPT-4o-mini) to score and classify each student enquiry, logs it to a CRM sheet, then sends a Twilio WhatsApp welcome message plus Telegram and Gmail notifications to the assigned counsellor, with alerts on workflow error.
How it works
Runs every 15 minutes on a schedule trigger.
Reads rows from the Google Sheets “New Leads Input” tab and skips entries where “Full Name” is empty.
Normalizes the lead fields (name, phone, email, course, city, source), generates a timestamp-based Lead ID, and captures the source row number.
Sends the normalized lead details to OpenAI (GPT-4o-mini) to return a JSON intent score, lead category, course category, and a counsellor note.
Parses the AI response, assigns a counsellor at random from a predefined roster, and combines the assignment with the AI scoring details.
Appends or updates the enriched lead record in the Google Sheets “Leads CRM” tab and updates the original intake row as Processed.
Sends the student a WhatsApp message via Twilio, and notifies the assigned counsellor via Telegram and Gmail with the lead details and AI talking points.
If the workflow errors, posts an alert to a Slack channel.
Setup
Connect Google Sheets OAuth2 credentials and replace YOUR_GOOGLE_SHEET_ID, ensuring the spreadsheet contains “New Leads Input” (with a Processed column) and “Leads CRM” tabs.
Add an OpenAI credential and confirm the model ID (gpt-4o-mini) is available in your OpenAI account.
Configure Twilio credentials (HTTP Basic Auth with Account SID/Auth Token), set your WhatsApp-enabled From number, and update the Twilio Account SID in the request URL.
Add a Telegram bot credential and replace the sample counsellor Telegram chat IDs in the counsellor roster.
Connect Gmail OAuth2 credentials and replace the sample counsellor email addresses used for routing.
Connect Slack OAuth2 credentials and select the target channel for error alerts.