Skip to content

Latest commit

 

History

History
128 lines (106 loc) · 5.35 KB

README.md

File metadata and controls

128 lines (106 loc) · 5.35 KB

Automated Email Marketing, Tracking, and Call Script Generation with Google Apps Script

Flow Diagram

System Flow Diagram

Demo Video For Email Tracking

demo.mp4

Repository Structure

app_script_email_automation/
├── README.md
├── requirements.txt
└── src/
    ├── app_script.js                 (Main Apps Script code)
    ├── credentials.json              (Credentials for external services)
    ├── get_emails.py                 (Fetches and uploads email data to Sheets)
    ├── clean_and_gen_call_script.py  (Cleans data & triggers call script creation)
    └── functions/
        ├── agent.py                 (AI-related logic)
        ├── config.py                (Configuration & constants)
        ├── create_call_script.py    (Generates call scripts)
        ├── create_email.py          (Builds email content)
        └── utlis.py                 (Utility functions)

Features

  • Send emails from multiple aliases.
  • Track opens with a web app and hidden pixel.
  • Log open counts and timestamps in Google Sheets.
  • Dynamically generate call scripts for emails exceeding five opens.
  • Append generated scripts to a separate Sheet for follow-up actions.

Getting Started

  1. Set Up Google Sheets: Create or update source/destination Sheets for email list and lead data.
  2. Deploy Web App: Host the code in Google Apps Script and enable the web endpoint for open tracking.
  3. Configure Credentials: Store any required credentials for third-party services (e.g., OpenAI API).
  4. Run & Track: Send emails, check open stats, and watch new call scripts appear in the leads Sheet.

Project Setup

  1. Clone the Repository
https://github.com/aaqib-ahmed-nazir/automated-email-marketing-and-tracking.git
   cd automated-email-marketing-and-tracking

  1. Create Environment File (.env)

    • Add your OpenAI API key and other credentials.
    • Copy and paste the following into your .env file, replacing the placeholder values with your actual keys:
    OPENAI_API_KEY=your_key_here
SHEET_API_KEY=your_sheet_key_here

  2. Install Dependencies

    • In local Python projects, run:
      pip install -r requirements.txt

  3. Create or Update config.py
    Place this file under src/helpers/ and store your credentials, Sheet IDs, and any other constants.
    For example:

    email_google_sheet = "YOUR_EMAIL_SHEET_ID"
call_script_google_sheet = "YOUR_CALL_SCRIPT_SHEET_ID"
CLIENT_ID = "YOUR_ZOHO_CLIENT_ID"
CLIENT_SECRET = "YOUR_ZOHO_CLIENT_SECRET"
REFRESH_TOKEN = "YOUR_ZOHO_REFRESH_TOKEN"
ZOHO_API_BASE_URL = "https://www.zohoapis.com/crm/v2"
TOKEN_URL = "https://accounts.zoho.com/oauth/v2/token"

  4. Configure Google Apps Script

    • Paste the .gs / app_script.js code into your Google Apps Script Editor.
    • Set up triggers or web app deployments as required.

  5. Deploy Production

    • Push your local changes to GitHub or your chosen repo host.
    • In Google Apps Script, deploy with appropriate permissions for the tracking pixel.

Setup and Usage

  • Configure your Google Apps Script project with the provided code.
  • Enable the web endpoint for handling the tracking pixel.
  • Create or update your Google Sheets for storing email data, tracking opens, and logging call scripts.
  • Set necessary environment variables (e.g., API keys) in your script or use a .env file locally.
  • Deploy as a web app; then run the main script to send emails and track opens.

Additional Steps to Run

  1. Activate your virtual environment (.venv) if needed, then run get_emails.py to gather and upload emails to a Google Sheet.
  2. Open the Google Apps Script editor and run app_script.js via the web interface to handle sending and tracking.

Workflow Overview (Step-by-Step)

  1. Data Import and Comparison

    • Import existing customers from Zoho CRM.
    • Import new customer data from a CSV file.
    • Compare the domains of existing and new customers.

  2. AI-Powered Email Generation

    • If a domain match is found, generate a personalized email using AI.
    • Otherwise, generate a generic marketing email.

  3. Email Upload and Sending

    • Upload generated emails to a Google Sheet.
    • Send all emails from the Sheet using Google Apps Script.

  4. Email Tracking

    • Track email activity via Google Apps Script.
    • Update the Sheet with open status whenever an email is opened.

  5. Engagement Analysis and Call Script Generation

    • If an email’s open rate exceeds the threshold:
      • Generate a personalized call script via AI.
      • Save this call script to another Google Sheet for follow-up.

Resources

Contributors

This project was not possible without these amazing contributors:

License

This project is licensed under the MIT License.