📬 Automated Jobs Email Tracker

A high-performance, two-stage email parsing pipeline that scans your inbox, detects job application updates (applications, interviews, offers, and rejections), and logs them to a styled Google Sheet with interactive status dropdowns.

---

System Architecture

The pipeline processes thousands of historical emails in seconds using a two-stage filtering architecture:

graph TD
    A[Start Pipeline] --> B[Connect to Google Sheets]
    B --> C[Connect to IMAP Server]
    C --> D[Fetch Last 1000 Email Headers]
    D --> E{For each email header}
    E -->|Already in tracker.db?| F[Skip Email]
    E -->|New Email| G{Header Job-Related?}
    G -->|No: Personal/Spam/Mailing List| H[Mark Unrelated in tracker.db]
    G -->|Yes: Candidate Email| I[Lazy-Fetch Full Email Body]
    I --> J{Deep Relevance Check}
    J -->|Relevance Failed| K[Mark Unrelated in tracker.db]
    J -->|Relevance Passed| L[Extract Company, Role, Status & Comp]
    L --> M[Log Entry to Google Sheet]
    M --> N[Mark Logged in tracker.db]
    H --> E
    K --> E
    N --> E
    F --> E
    E -->|All Done| O[Format Sheet & Close Connections]
    O --> P[End]

Loading

---

Features

• High Performance: Header-only pre-filtering in memory skips newsletters, group discussions, and personal consumer domains without downloading email bodies.
• Idempotent / Deduplicated: Keeps track of processed Message-IDs in a local SQLite database (tracker.db) to ensure emails are never processed twice.
• 99% Parse Accuracy: Detailed keyword matching and fallback patterns extract clean Company names, precise Roles, and Statuses.
• Premium Spreadsheet Design: Autoresizes columns, freezes headers, and inserts interactive dropdown lists (Applied, Interview Scheduled, Offer, Rejected) in the Status column.

---

Configuration & Setup

1. Gmail Setup (App Password)

1. Go to your Google Account Security settings.
2. Enable 2-Step Verification.
3. Under 2-Step Verification, scroll to the bottom and select App passwords.
4. Create a custom app named your-app and copy the generated 16-character code.

2. Google Sheets Setup (API Credentials)

1. Go to the Google Cloud Console.
2. Create a project and enable the Google Sheets API and Google Drive API.
3. Navigate to APIs & Services > Credentials and create a Service Account.
4. Generate a JSON key for the Service Account, download it, and rename it to credentials.json in your project folder.
5. Create a new Google Sheet, copy the Sheet ID from its URL, and Share it with the service account's client email as an Editor.

3. Local Environment

Create a .env file in the root directory:

# IMAP Email Settings
IMAP_SERVER=imap.gmail.com
IMAP_PORT=993
EMAIL_ADDRESS=your_email@gmail.com
EMAIL_PASSWORD=your_16_character_app_password_without_spaces

# Google Sheets Settings
GOOGLE_SHEET_ID=your_sheet_id_from_url
GOOGLE_SERVICE_ACCOUNT_FILE=credentials.json

# Local SQLite Database Path
DATABASE_PATH=tracker.db

---

Running the Tracker

Install the requirements and execute the pipeline:

pip install -r requirements.txt
python run_pipeline.py

Automatic Scheduling (Windows Task Scheduler)

To run this automatically in the background every day:

1. Open Task Scheduler on Windows.
2. Click Create Basic Task... and set the trigger to Daily.
3. Set the action to Start a program and select run_tracker.bat from the project folder.
4. Go to the task's properties under the Conditions tab and uncheck Start the task only if the computer is on AC power (recommended for laptops).