Ahmed Sherif
Workflows by Ahmed Sherif
Personalized cold email system with Google Gemini, Telegram approval & Google Sheets
## Personalized Cold Email System with Google Gemini AI and Telegram Approval An AI-powered cold email automation that uses Google Gemini to generate personalized outreach and a Telegram workflow for manual approval, ensuring high-quality, human-in-the-loop campaigns. ### Features - **Smart Lead Selection**: Fetches leads with an "Available" status from a designated Google Sheet. - **AI Email Generation**: Google Gemini creates a personalized subject line and email body based on lead data. - **Telegram Approval**: Sends each generated email to a Telegram chat for a one-click "Approve" or "Reject" decision. - **Edit & Regenerate**: Allows users to provide feedback on rejected emails, which the AI uses to regenerate a new version for re-approval. - **Auto-Send**: Approved emails are automatically dispatched via a configured SMTP server, including a professional signature. - **Complete Tracking**: Updates three separate Google Sheets (Filtered Leads, Sent Leads, Emails Sent) to maintain a clear audit trail. - **Rate Limiting**: A one-second delay between email sends helps avoid spam filters and protects sender reputation. - **Batch Processing**: Handles three leads per execution by default, which is configurable to scale up or down. ### How It Works 1. A manual trigger initiates the workflow and captures the current date and time for logging. 2. The system fetches a set number of leads (default is three) from the `Filtered Leads` sheet where the status is "Available". 3. It loops through each lead individually to generate and send a unique email. 4. The AI generates a personalized email (Subject + Body) using the lead's data. 5. A preview of the email is sent to a specified Telegram chat with "Approve" and "Reject" buttons. 6. **If approved**: The email is sent via SMTP. The lead's status is updated, and logs are written to the `Sent Leads` and `Emails Sent` sheets. The system waits one second before processing the next lead. 7. **If rejected**: The system prompts the user for feedback in Telegram. The feedback is combined with the original prompt to generate a new email draft, which is sent for re-approval. 8. A final notification is sent to Telegram when the batch is complete. ### Requirements - Google Sheets with three tabs (see structure below). - Telegram Bot Token and user/group Chat ID. - Google Gemini API Key. - SMTP Email Account credentials (server, port, username, password). --- ## Setup Instructions 1. **Google Sheets Setup**: Create a new Google Sheet. Rename three tabs to exactly `Filtered Leads`, `Sent Leads`, and `Emails Sent`. Populate the `Filtered Leads` sheet with your lead data according to the structure specified below. 2. **API Credentials**: * **Google Gemini**: Generate an API key from your Google AI Studio account. * **Telegram Bot**: Create a new bot by messaging `@BotFather` on Telegram and follow the prompts to get your API token. Get your Chat ID by messaging `@userinfobot`. 3. **SMTP Configuration**: Obtain the SMTP server address, port, username, and password from your email provider (e.g., Google Workspace, Outlook). 4. **Automation Platform**: In your automation platform (e.g., n8n, Make), create a new workflow. * Connect your Google Sheets account using OAuth2. * Add the Gemini API Key, Telegram Bot Token, Chat ID, and SMTP credentials into the respective modules or credential stores. * Map the sheet columns to the variables in the workflow modules. ## Google Sheets Structure The automation requires the following column headers in each sheet. The names must be exact. * **1. Filtered Leads (Input Sheet)** * `FirstName`: Lead's first name. * `LastName`: Lead's last name. * `Email`: Lead's email address. * `CompanyName`: Name of the lead's company. * `JobTitle`: Lead's current job title. * `Website`: Company website (for AI context). * `LinkedIn`: URL of the lead's LinkedIn profile. * `Notes`: Any specific details or pain points for personalization. * `Status`: Workflow status. Set to `Available` for leads ready to be processed. * **2. Sent Leads (Tracking Sheet)** * `Email`: Email address of the contacted lead. * `CompanyName`: Name of the company. * `TimestampSent`: Date and time the email was sent. * **3. Emails Sent (Archive Sheet)** * `RecipientEmail`: The lead's email address. * `Subject`: The exact subject line that was sent. * `Body`: The full email body that was sent. * `TimestampSent`: Date and time the email was sent. ## Customization Guide This template can be adapted to fit specific campaign needs. * **Batch Size**: The number of leads processed per execution is controlled by the initial "Get Leads" module. Change the `Limit` parameter from 3 to your desired number. * **AI Prompt**: The core of the personalization is the prompt sent to the Gemini API. Modify this prompt to change the email's tone, structure, or call-to-action. You can instruct the AI to focus on specific data from the `Notes` column or reference a different value proposition. * **Lead Filtering**: The trigger condition is set to pull leads where `Status = "Available"`. This can be changed to any other logic, such as `Priority = "High"` or `Campaign = "Q4-Push"`, by adjusting the filter in the Google Sheets module. * **Email Signature**: The signature is typically configured within the SMTP "Send Email" module. Update the HTML or plain text here to change the signature across all sends. * **Telegram Notifications**: The text and buttons sent to Telegram for approval can be modified. You can add more lead data to the approval message (e.g., Job Title, Company Name) to give the approver more context.
AI-powered lead processing from Apify with Gemini and Google Sheets
## AI-Powered Lead Scraping Automation using APIFY Scraper and Gemini Filtering to Google Sheets This is a fully automated, end-to-end pipeline designed to solve the challenge of inconsistent and low-quality lead data from large-scale scraping operations. The system programmatically fetches raw lead information from sources like Apollo or via Apify, processes it through an intelligent validation layer, and delivers a clean, deduplicated, and ready-to-use dataset directly into Google Sheets. By integrating Google Gemini for data cleansing, it moves beyond simple presence checks to enforce data hygiene and standardization, ensuring that sales teams only engage with properly formatted and complete leads. This automation eliminates hours of manual data cleaning, accelerates the speed from lead acquisition to outreach, and significantly improves the integrity of the sales pipeline. ### Features - **Batch Processing**: Systematically processes up to 1000 leads per batch and automatically loops through the entire dataset. This ensures stable, memory-efficient operation even with tens of thousands of scraped contacts. - **AI Validation**: Google Gemini acts as a data quality gatekeeper. It validates the presence and plausible format of critical fields (e.g., First Name, Company Name) and cleanses data by correcting common formatting issues. - **Smart Deduplication**: Before appending a new lead, the system cross-references its email address against the entire Google Sheet to prevent duplicate entries, ensuring a single source of truth. - **Auto Lead IDs**: Generates a unique, sequential ID for every new lead in the format `AP-DDMMYY-xxxx`. This provides a consistent reference key for tracking and CRM integration. - **Data Quality Reports**: Delivers real-time operational visibility by sending a concise summary to a Telegram channel after each batch, detailing success, warning, and error counts. - **Rate Limiting**: Incorporates a 30-second delay between batches to respect Google Sheets API limits, preventing throttling and ensuring reliable, uninterrupted execution. #### How It Works 1. The workflow is initiated by an external trigger, such as a webhook, carrying the raw scraped data payload. 2. It authenticates and fetches the complete list of leads from the Apify or Apollo API endpoint. 3. The full list is automatically partitioned into manageable batches of 1000 leads for efficient processing. 4. Each lead is individually passed to the Gemini AI Agent, which validates that required fields like Name, Email, and Company are present and correctly formatted. 5. Validated leads are assigned a unique Lead ID, and all data fields are standardized for consistency. 6. The system performs a lookup in the target Google Sheet to confirm the lead's email does not already exist. 7. Clean, unique leads are appended as a new row to the designated spreadsheet. 8. A completion notice is sent via the Telegram Bot, summarizing the batch results with clear statistics. ### Requirements - Apify/Apollo API access credentials. - Google Cloud project with OAuth2 credentials for Google Sheets API access. - A configured Telegram Bot with its API Token and a target Chat ID. - A Google Gemini API Key for data validation and cleansing. This system is ideal for sales and marketing operations teams managing high-volume lead generation campaigns, providing automated data quality assurance and accelerating pipeline development.
Build a text & image responding Telegram bot with Google Gemini 2.5 Flash
# Telegram AI Bot Workflow An intelligent Telegram bot powered by Google Gemini AI that provides smart responses to both text messages and images. ## Features - **Multi-Modal Input**: Handles both text messages and image uploads - **AI-Powered Responses**: Uses Google Gemini 2.5 Flash for intelligent reply generation - **Image Analysis**: Automatically analyzes uploaded images and responds with descriptions - **Conversation Memory**: Maintains context across 20 messages per conversation - **Formatted Output**: Delivers well-structured, Telegram-friendly responses ## How It Works 1. User sends a message (text or image) to the Telegram bot 2. Workflow routes the input based on message type 3. Images are downloaded and analyzed using Gemini Vision AI 4. AI agent processes the input with conversation context 5. Formatted response is sent back to the user instantly ## Requirements - Telegram Bot Token (from @BotFather) - Google Gemini API Key - n8n instance with webhook capability Perfect for building interactive AI assistants, customer support bots, or educational tools on Telegram.