Source: docs/guides/README_AUTOMATED_SYSTEM.md

Automated Email & Reporting System

Complete automation system with two Cloud Scheduler jobs:

Morning Report - Daily at 8 AM Eastern
Email Monitor - Every 5 minutes

Quick Setup

# Run the setup script
./deploy/setup_both_schedulers.sh

That's it! Your system is now live.

System Overview

1. Morning Report Job (Daily at 8 AM)

What it does:

✅ Syncs all Firestore data (contracts, leagues, analytics) to Google Sheets
✅ Creates/updates 5 dashboard tabs with latest pipeline data
✅ Generates beautiful HTML email report
✅ Sends report to kselander@altsportsdata.com

Schedule: 0 8 * * * (8 AM Eastern, daily)

Files:

Script: apps/backend/scripts/league_pipeline_sheets_sync.py
Endpoint: apps/backend/routes/api/morning_report.py
Config: deploy/cloud-scheduler-morning-report.yaml

2. Email Monitor Job (Every 5 Minutes)

What it does:

✅ Checks assistant@altsportsdata.com for unread emails
✅ Classifies emails (questionnaire, partnership, contract, etc.)
✅ Processes through appropriate workflow
✅ Marks as read and adds "PROCESSED" label
✅ Logs to Firestore for tracking

Schedule: */5 * * * * (Every 5 minutes)

Files:

Script: apps/backend/scripts/email_monitor_unread.py
Endpoint: apps/backend/routes/api/email_monitor.py
Config: deploy/cloud-scheduler-email-monitor.yaml

Email Classification System

The email monitor automatically classifies incoming emails:

Classification	Keywords	Action
`league_questionnaire`	questionnaire, survey, form	→ Document processing pipeline
`partnership_inquiry`	partnership, interested in	→ Partnership acquisition pipeline
`contract_follow_up`	contract, agreement, signed	→ Contract management pipeline
`technical_inquiry`	api, data feed, integration	→ Technical support workflow
`meeting_request`	meeting, call, schedule	→ Calendar booking workflow
`general`	Everything else	→ Manual review queue

Manual Testing

Test Morning Report

# Via script
python apps/backend/scripts/league_pipeline_sheets_sync.py
 
# Via API
curl -X POST https://altsports-email-agent-559065285658.us-central1.run.app/api/morning-report \
  -H "Content-Type: application/json" \
  -d '{"action":"sync_and_report","recipient":"kselander@altsportsdata.com"}'
 
# Via Cloud Scheduler
gcloud scheduler jobs run morning-report-job --location=us-central1

Test Email Monitor

# Via script
python apps/backend/scripts/email_monitor_unread.py
 
# With options
python apps/backend/scripts/email_monitor_unread.py --mark-read --add-label
 
# Via API
curl -X POST https://altsports-email-agent-559065285658.us-central1.run.app/api/email-monitor \
  -H "Content-Type: application/json" \
  -d '{"max_emails":10,"mark_read":true,"add_label":true}'
 
# Via Cloud Scheduler
gcloud scheduler jobs run email-monitor-job --location=us-central1

Monitoring & Status

Check Job Status

# View all jobs
gcloud scheduler jobs list --location=us-central1
 
# Describe specific job
gcloud scheduler jobs describe morning-report-job --location=us-central1
gcloud scheduler jobs describe email-monitor-job --location=us-central1
 
# View execution history
gcloud scheduler jobs describe morning-report-job --location=us-central1 | grep lastAttemptTime

Check API Status

# Morning report status
curl https://altsports-email-agent-559065285658.us-central1.run.app/api/morning-report/status
 
# Email monitor status
curl https://altsports-email-agent-559065285658.us-central1.run.app/api/email-monitor/status
 
# Email monitor statistics
curl https://altsports-email-agent-559065285658.us-central1.run.app/api/email-monitor/stats

View Logs

# All scheduler logs
gcloud logging read "resource.type=cloud_scheduler_job" --limit=50
 
# Specific job logs
gcloud logging read "resource.type=cloud_scheduler_job AND resource.labels.job_id=email-monitor-job" --limit=20
 
# Cloud Run logs
gcloud logging read "resource.type=cloud_run_revision" --limit=50

Managing Jobs

Pause Jobs

# Pause morning report
gcloud scheduler jobs pause morning-report-job --location=us-central1
 
# Pause email monitor
gcloud scheduler jobs pause email-monitor-job --location=us-central1

Resume Jobs

# Resume morning report
gcloud scheduler jobs resume morning-report-job --location=us-central1
 
# Resume email monitor
gcloud scheduler jobs resume email-monitor-job --location=us-central1

Update Schedule

# Change morning report to 9 AM
gcloud scheduler jobs update http morning-report-job \
  --schedule="0 9 * * *" \
  --location=us-central1
 
# Change email monitor to every 10 minutes
gcloud scheduler jobs update http email-monitor-job \
  --schedule="*/10 * * * *" \
  --location=us-central1

Delete Jobs

# Delete morning report
gcloud scheduler jobs delete morning-report-job --location=us-central1
 
# Delete email monitor
gcloud scheduler jobs delete email-monitor-job --location=us-central1

Environment Variables

Required for both systems:

# Google Cloud
export GOOGLE_CLOUD_PROJECT="altsportsdata"
export GOOGLE_APPLICATION_CREDENTIALS="path/to/credentials.json"
 
# Gmail
export GMAIL_USER="assistant@altsportsdata.com"
export GMAIL_APP_PASSWORD="your-app-password"  # For sending emails
 
# Optional: Existing Google Sheet
export LEAGUE_PIPELINE_SHEET_ID="your-sheet-id"

Reports & Output

Morning Report Output

Google Sheets: Auto-created and shared with kselander@altsportsdata.com
Email: HTML report sent daily at 8 AM
Local Report: apps/backend/output/reports/morning_report_YYYYMMDD.html

Email Monitor Output

Firestore: Logs to email_processing collection
Local Reports: apps/backend/output/reports/email_monitor_YYYYMMDD_HHMMSS.json
Gmail Labels: Adds "PROCESSED" label to handled emails

Google Sheets Structure

The morning report creates/updates these sheets:

Pipeline Overview
- Total contracts, active contracts, pending contracts
- Total leagues, last update timestamp
- Key business metrics
Active Leagues
- League name, sport, tier, status
- Contact info, scores, estimated value
Contracts
- League, status, generated date
- Contract type, tier, IDs
Analytics
- Timestamp, type, metrics, metadata
Daily Metrics
- 7-day trend: new contracts, active leagues
- Pipeline value, completion rates

Cost Estimate

Service	Usage	Monthly Cost
Cloud Scheduler (2 jobs)	2 jobs	$0.20
Cloud Run (minimal traffic)	~10K requests	~$0.50
Firestore (reads)	~65K reads	~$0.40
Gmail API	Free tier	$0.00
Google Sheets API	Free tier	$0.00
Total		~$1.10/month

Troubleshooting

No Emails Being Processed

Check Gmail API is enabled
Verify service account has Gmail delegation
Test manually: python apps/backend/scripts/email_monitor_unread.py
Check Firestore email_processing collection

Morning Report Not Sending

Verify GMAIL_APP_PASSWORD is set
Check Cloud Run environment variables
Test manually: python apps/backend/scripts/league_pipeline_sheets_sync.py
View logs: gcloud logging read "resource.type=cloud_run_revision" --limit=50

Scheduler Jobs Not Running

Check jobs are not paused: gcloud scheduler jobs list --location=us-central1
Verify Cloud Run service is healthy
Check IAM permissions for service account
View job logs: gcloud scheduler jobs describe JOB_NAME --location=us-central1

Google Sheets Not Updating

Verify Google Sheets API is enabled
Check service account permissions
Confirm GOOGLE_APPLICATION_CREDENTIALS path
Test Firestore connection

Architecture Diagram

┌─────────────────────────────────────────────────────────────┐
│                    Cloud Scheduler                           │
├──────────────────────┬──────────────────────────────────────┤
│  Morning Report      │  Email Monitor                        │
│  (Daily at 8 AM)     │  (Every 5 minutes)                   │
└──────────┬───────────┴────────────┬─────────────────────────┘
           │                        │
           ▼                        ▼
┌──────────────────────────────────────────────────────────────┐
│              Cloud Run (FastAPI Backend)                      │
├──────────────────────┬──────────────────────────────────────┤
│  /api/morning-report │  /api/email-monitor                  │
└──────────┬───────────┴────────────┬─────────────────────────┘
           │                        │
           ▼                        ▼
    ┌──────────┐            ┌──────────────┐
    │ Firestore│◄───────────┤ Gmail API    │
    └─────┬────┘            └──────────────┘
          │
          ▼
    ┌──────────────┐
    │ Google Sheets│
    └──────────────┘
          │
          ▼
    ┌──────────────┐
    │ Email Report │──► kselander@altsportsdata.com
    └──────────────┘

Next Steps

Morning report with Google Sheets sync
Email monitoring every 5 minutes
Email classification system
Firestore logging
Slack notifications for critical emails
SMS alerts for high-priority leads
Weekly summary reports
Dashboard analytics UI
Custom email workflows per classification

Support

For issues:

Email: kselander@altsportsdata.com
Logs: Google Cloud Console → Logging
Status: Check API endpoints listed above

League Intelligence Platform - MVP Simplification Plan AWS Redshift Data Exploration Setup

Automated Email & Reporting System

Quick Setup

System Overview

1. Morning Report Job (Daily at 8 AM)

2. Email Monitor Job (Every 5 Minutes)

Email Classification System

Manual Testing

Test Morning Report

Test Email Monitor

Monitoring & Status

Check Job Status

Check API Status

View Logs

Managing Jobs

Pause Jobs

Resume Jobs

Update Schedule

Delete Jobs

Environment Variables

Reports & Output

Morning Report Output

Email Monitor Output

Google Sheets Structure

Cost Estimate

Troubleshooting

No Emails Being Processed

Morning Report Not Sending

Scheduler Jobs Not Running

Google Sheets Not Updating

Architecture Diagram

Next Steps

Support

Platform

Documentation

Community

Support