League Pipeline Morning Report System

Automated daily morning report system that syncs league pipeline data to Google Sheets and sends email reports to stakeholders.

Features

• Automated Google Sheets Sync: Daily sync of contracts, leagues, and analytics from Firestore
• Beautiful HTML Email Reports: Comprehensive morning report with key metrics and insights
• Cloud Scheduler Integration: Automated daily execution at 8 AM
• 5 Dashboard Sheets:
  • Pipeline Overview: High-level metrics and KPIs
  • Active Leagues: League details with scores and status
  • Contracts: All contracts with workflow tracking
  • Analytics: Business intelligence metrics
  • Daily Metrics: 7-day trend analysis

Quick Start

1. Environment Setup

# Required environment variables
export GOOGLE_CLOUD_PROJECT="your-project-id"
export GOOGLE_APPLICATION_CREDENTIALS="path/to/credentials.json"
export GMAIL_USER="assistant@altsportsdata.com"
export GMAIL_APP_PASSWORD="your-app-password"  # For sending emails
 
# Optional: Specify existing sheet
export LEAGUE_PIPELINE_SHEET_ID="your-sheet-id"

2. Manual Execution

# Full sync and report
python apps/backend/scripts/league_pipeline_sheets_sync.py
 
# Sync only (no email)
python apps/backend/scripts/league_pipeline_sheets_sync.py --sync-only
 
# Report only (no sync)
python apps/backend/scripts/league_pipeline_sheets_sync.py --report-only
 
# Custom recipient
python apps/backend/scripts/league_pipeline_sheets_sync.py --email user@example.com

3. Cloud Scheduler Setup

# Create daily job (8 AM Eastern)
gcloud scheduler jobs create http morning-report-job \
  --schedule="0 8 * * *" \
  --uri="https://altsports-email-agent-559065285658.us-central1.run.app/api/morning-report" \
  --http-method=POST \
  --message-body='{"action":"sync_and_report","recipient":"kselander@altsportsdata.com"}' \
  --time-zone="America/New_York" \
  --location="us-central1"
 
# Test manually
gcloud scheduler jobs run morning-report-job --location="us-central1"
 
# Check status
gcloud scheduler jobs describe morning-report-job --location="us-central1"

4. API Endpoint Usage

# Trigger via API
curl -X POST https://your-service.run.app/api/morning-report \
  -H "Content-Type: application/json" \
  -d '{"action":"sync_and_report","recipient":"kselander@altsportsdata.com"}'
 
# Check status
curl https://your-service.run.app/api/morning-report/status

Google Sheets Structure

Pipeline Overview

• Total Contracts
• Active Contracts
• Pending Contracts
• Total Leagues
• Last Updated timestamp

Active Leagues

• League Name
• Sport
• Tier Classification
• Status
• Contact Email
• Created Date
• Partnership Score
• Estimated Contract Value

Contracts

• League Name
• Status (active/pending/completed)
• Generated Date
• Contract Type
• Tier
• Envelope ID
• Workflow ID

Analytics

• Timestamp
• Type
• Metric
• Value/Metadata

Daily Metrics

• Date
• New Contracts
• Active Leagues
• Pipeline Value
• Completion Rate

Email Report Contents

The morning report includes:

1. Key Metrics Dashboard

   • Total contracts with 24h change
   • Active contracts
   • Total leagues
   • Estimated pipeline value

2. Top 5 Leagues by Partnership Score

   • League name, sport, score, tier, status

3. Recent Contracts

   • Last 5 contracts generated

4. Direct Link to Live Dashboard

   • Google Sheets link for real-time data

Permissions

The Google Sheets are automatically shared with:

• kselander@altsportsdata.com (Writer)

To add more recipients:

# In the script, add after spreadsheet creation:
sync.drive_service.permissions().create(
    fileId=spreadsheet_id,
    body={
        'type': 'user',
        'role': 'writer',
        'emailAddress': 'newuser@example.com'
    }
).execute()

Data Sources

All data is synced from Firestore collections:

• contracts: Contract generation and workflow tracking
• leagues: League intelligence and scoring
• analytics: Business intelligence metrics

Scheduling Options

Cloud Scheduler (Recommended for Production)

• Reliable, managed service
• Automatic retries
• Easy monitoring

Cron (Alternative)

# Add to crontab for 8 AM daily
0 8 * * * cd /path/to/project && python apps/backend/scripts/league_pipeline_sheets_sync.py

GitHub Actions (CI/CD Option)

name: Daily Morning Report
on:
  schedule:
    - cron: '0 8 * * *'
jobs:
  report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - name: Run morning report
        run: python apps/backend/scripts/league_pipeline_sheets_sync.py

Troubleshooting

No Emails Sent

• Verify GMAIL_APP_PASSWORD is set
• Check Gmail API is enabled
• Confirm sender email has appropriate permissions

Sheets Not Updating

• Verify Google Sheets API is enabled
• Check service account has Drive permissions
• Confirm GOOGLE_APPLICATION_CREDENTIALS path is correct

Firestore Connection Issues

• Verify GOOGLE_CLOUD_PROJECT is set correctly
• Check Firestore is enabled in your project
• Confirm service account has Firestore read permissions

Check Logs

# View Cloud Scheduler logs
gcloud scheduler jobs describe morning-report-job --location="us-central1"
 
# View Cloud Run logs
gcloud logging read "resource.type=cloud_run_revision" --limit=50
 
# Local testing with verbose output
python apps/backend/scripts/league_pipeline_sheets_sync.py --verbose

Cost Considerations

• Google Sheets API: Free for reasonable usage
• Cloud Scheduler: $0.10 per job per month
• Gmail API: Free for sending
• Firestore Reads: ~150 reads per execution (minimal cost)

Estimated Monthly Cost: < $1

Future Enhancements

• Slack integration for notifications
• PDF export of reports
• Weekly/monthly summary reports
• Custom metric dashboards
• Real-time alerts for critical changes
• Multi-recipient support with role-based views
• Historical trend analysis

Support

For issues or questions:

• Email: kselander@altsportsdata.com
• View logs in Google Cloud Console
• Check Firestore data quality