UVF IT: Gmail Subscription Tracker

Welcome to another official UVF IT build. Today, we are creating a minimal clone of Trackd, a tool that helps you identify active subscriptions by analyzing your email history.

Instead of connecting to your bank or using complex cloud APIs, this project uses a local Python script to parse a sample mbox file or a list of email subjects. This approach keeps everything on your machine, ensuring privacy and speed while teaching you core data processing concepts.

Prerequisites & Limitations

Requires Python 3.8+ installed. No paid APIs or bank logins are needed; we simulate the email data locally.
This is a simplified clone: it does not connect to live Gmail via OAuth. Instead, it processes a static list of email subjects to demonstrate the logic.
Common gotcha: Ensure your sample data includes realistic subscription keywords (e.g., 'Receipt', 'Invoice', 'Subscription') for the detection logic to work.

Local Subscription Tracker Architecture

Phase 1: Scaffold Project

Initialize a Python project directory and create a virtual environment to keep our dependencies isolated.

We will use only the standard library (sqlite3, re) to keep dependencies minimal, so we don't need to install any external packages via pip.

mkdir subscription-tracker
cd subscription-tracker
python3 -m venv venv
source venv/bin/activate  # On Windows use: venv\Scripts\activate

Phase 2: Data Model & Sample Data

Create a SQLite database to store detected subscriptions. This file will act as our local 'memory' for the tracker.

Define a sample list of email subjects that mimic real subscription receipts, including common keywords like 'Receipt' and 'Invoice' to test our detection logic.

setup_db.py

import sqlite3

DB_NAME = 'subscriptions.db'

SAMPLE_SUBJECTS = [
    "Your Netflix receipt for March",
    "Spotify Premium - Invoice #4921",
    "Adobe Creative Cloud - Subscription Renewal",
    "Order Confirmation - Amazon",
    "Weekly Newsletter from TechDaily",
    "Your Spotify Premium receipt"
]

def init_db():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    # Create table for subscriptions
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS subscriptions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            provider TEXT NOT NULL,
            subject TEXT NOT NULL,
            detected_date TEXT DEFAULT CURRENT_DATE
        )
    ''')
    
    # Create table for raw processed subjects (to avoid re-processing)
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS processed_subjects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            subject TEXT UNIQUE NOT NULL
        )
    ''')
    
    conn.commit()
    conn.close()
    print(f"Database '{DB_NAME}' initialized.")

if __name__ == '__main__':
    init_db()

Phase 3: Core Detection Logic

Implement a function to scan email subjects for subscription keywords. We use regular expressions to find patterns like 'receipt', 'invoice', or 'subscription'.

Extract the provider name from the subject line using simple string manipulation. We look for the first capitalized word or known brand names to identify who the service is.

tracker.py

import re
import sqlite3

DB_NAME = 'subscriptions.db'

# Keywords that indicate a subscription or recurring payment
SUBSCRIPTION_KEYWORDS = ['receipt', 'invoice', 'subscription', 'renewal', 'payment']

# Common provider names to help with extraction if keyword match is ambiguous
KNOWN_PROVIDERS = ['Netflix', 'Spotify', 'Adobe', 'Amazon', 'Apple', 'Google', 'Microsoft']

def is_subscription(subject):
    """
    Check if an email subject contains subscription-related keywords.
    """
    subject_lower = subject.lower()
    return any(keyword in subject_lower for keyword in SUBSCRIPTION_KEYWORDS)

def extract_provider(subject):
    """
    Attempt to extract the provider name from the subject line.
    Strategy: Look for known providers first, then fallback to the first capitalized word.
    """
    # 1. Check for known providers
    for provider in KNOWN_PROVIDERS:
        if provider.lower() in subject.lower():
            return provider
    
    # 2. Fallback: Find the first word that is capitalized (and not a common stop word)
    words = subject.split()
    stop_words = {'your', 'the', 'a', 'an', 'for', 'from', 'to', 'is', 'are', 'was', 'were'}
    for word in words:
        # Remove punctuation for comparison
        clean_word = word.strip('.,!?:;"\'')
        if clean_word and clean_word[0].isupper() and clean_word.lower() not in stop_words:
            return clean_word
            
    return 'Unknown'

def process_subjects(subjects):
    """
    Scan a list of subjects, detect subscriptions, and store them in the DB.
    """
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    detected_count = 0
    
    for subject in subjects:
        # Check if already processed
        cursor.execute('SELECT id FROM processed_subjects WHERE subject = ?', (subject,))
        if cursor.fetchone():
            continue
            
        # Mark as processed
        cursor.execute('INSERT INTO processed_subjects (subject) VALUES (?)', (subject,))
        
        # Check for subscription keywords
        if is_subscription(subject):
            provider = extract_provider(subject)
            cursor.execute(
                'INSERT INTO subscriptions (provider, subject) VALUES (?, ?)',
                (provider, subject)
            )
            detected_count += 1
            print(f"Detected: {provider} - {subject}")
        else:
            print(f"Skipped: {subject}")
            
    conn.commit()
    conn.close()
    return detected_count

Phase 4: CLI Interface & Execution

Now that the detection logic is ready, we need a way to interact with it. We will create main.py, which acts as the entry point for our CLI tool.

This script will initialize the database if it doesn't exist, run the scanner from tracker.py, and fetch the results. It then formats the output into a clean, readable report that lists each detected subscription and its provider.

main.py

import sqlite3
from setup_db import init_db
from tracker import scan_subscriptions

def main():
    # 1. Ensure the database and sample data are ready
    init_db()
    
    # 2. Run the detection logic
    scan_subscriptions()
    
    # 3. Query and display the results
    conn = sqlite3.connect('subscriptions.db')
    cursor = conn.cursor()
    cursor.execute("SELECT provider, subject, date FROM subscriptions ORDER BY date DESC")
    rows = cursor.fetchall()
    conn.close()
    
    print("\n--- Subscription Report ---")
    if not rows:
        print("No subscriptions detected.")
    else:
        for row in rows:
            provider, subject, date = row
            print(f"[ {date} ] {provider}: {subject}")
        print(f"\nTotal Detected: {len(rows)}")

if __name__ == "__main__":
    main()

Phase 5: Verify & Extend

Let's run the application to see if our local tracker works as expected. We will execute the main script to generate the report.

To extend this project, you could modify setup_db.py to read from a real .mbox file using Python's mailbox module, or add a function to export the results to a CSV file for spreadsheet analysis.

python main.py

Verifying the Stack

Check	Command / Why it matters
Database file created	Run `ls subscriptions.db` to confirm the SQLite file exists in your project directory.
Correct number of subscriptions detected	Run `python main.py` and verify the output ends with 'Total Detected: 3' (matching Netflix, Spotify, and Adobe).
Provider extraction works	Inspect the CLI output to ensure 'Netflix', 'Spotify', and 'Adobe' are correctly identified as providers.
No external network calls	Run the script with your internet connection disabled; the report should still generate successfully.

Next Steps

You now have a working local subscription tracker. To make it more robust, try parsing a real .mbox file using Python's mailbox module or adding a simple Flask API to serve the report as JSON.