Analytics Background Jobs

This document describes the scheduled jobs that maintain and optimize the analytics system.

📋 Overview

Three background jobs keep your analytics data clean, aggregated, and performant:

Daily Aggregation - Summarizes yesterday's data
Session Cleanup - Closes inactive sessions
Data Retention - Archives old events

🔄 Job 1: Daily Analytics Aggregation

File: src/jobs/aggregate-daily-analytics.ts

Purpose

Aggregates yesterday's raw analytics events into the analytics_daily_stats table for faster historical queries.

Schedule

0 1 * * *  (Every day at 1:00 AM)

What It Does

Collects Yesterday's Events
- Gets all analytics events from yesterday (00:00 - 23:59)
- Groups by website_id
Calculates Aggregated Stats
- Total pageviews
- Total custom events
- Unique visitors
- Unique sessions
- Top 10 pages
- Top 10 referrers
Stores in Daily Stats Table
- Creates one record per website per day
- Enables fast historical queries
- Reduces database load

Benefits

✅ 10x faster historical queries
✅ Reduces load on main events table
✅ Enables long-term trend analysis
✅ Pre-calculated metrics ready to display

Example Output

{
  website_id: "01JM1PEW9H0ES7GGMD173GM2T9",
  date: "2024-01-15",
  total_pageviews: 1247,
  total_custom_events: 89,
  unique_visitors: 342,
  unique_sessions: 456,
  top_pages: [
    { item: "/", count: 450 },
    { item: "/products", count: 234 },
    { item: "/about", count: 123 }
  ],
  top_referrers: [
    { item: "google", count: 234 },
    { item: "direct", count: 189 },
    { item: "facebook", count: 45 }
  ]
}

Logs

[Analytics Job] Starting daily aggregation...
[Analytics Job] Aggregating data for 2024-01-15
[Analytics Job] ✅ Aggregated 1247 events for website 01JM1PEW9H0ES7GGMD173GM2T9
[Analytics Job] ✅ Daily aggregation completed for 1 website(s)

🧹 Job 2: Session Cleanup

File: src/jobs/cleanup-analytics-sessions.ts

Purpose

Closes sessions that have been inactive for 30+ minutes and calculates their final duration.

Schedule

*/10 * * * *  (Every 10 minutes)

What It Does

Finds Stale Sessions
- Queries sessions with last_activity_at > 30 minutes ago
- Only sessions where ended_at is null
Closes Each Session
- Sets ended_at to last_activity_at
- Calculates duration_seconds
- Marks session as complete
Updates Analytics
- Accurate session duration metrics
- Correct "current visitors" count
- Clean data for reporting

Benefits

✅ Accurate active visitor counts
✅ Proper session duration metrics
✅ Clean data for analytics
✅ Prevents stale session buildup

Session Lifecycle

User visits page
    ↓
Session created (started_at)
    ↓
User browses (last_activity_at updated)
    ↓
User leaves (no activity for 30 min)
    ↓
Job closes session (ended_at, duration_seconds)

Example

// Before cleanup
{
  session_id: "session_abc123",
  started_at: "2024-01-15T10:00:00Z",
  last_activity_at: "2024-01-15T10:25:00Z",
  ended_at: null,
  duration_seconds: null
}

// After cleanup (10:55 AM)
{
  session_id: "session_abc123",
  started_at: "2024-01-15T10:00:00Z",
  last_activity_at: "2024-01-15T10:25:00Z",
  ended_at: "2024-01-15T10:25:00Z",
  duration_seconds: 1500  // 25 minutes
}

Logs

[Analytics Cleanup] Checking for inactive sessions before 2024-01-15T10:25:00Z
[Analytics Cleanup] Found 5 stale session(s) to close
[Analytics Cleanup] ✅ Closed session session_abc123 (duration: 1500s)
[Analytics Cleanup] ✅ Closed 5 inactive session(s)

🗄️ Job 3: Data Retention & Archival

File: src/jobs/archive-old-analytics.ts

Purpose

Deletes raw analytics events older than 90 days while keeping aggregated daily stats.

Schedule

0 2 * * 0  (Every Sunday at 2:00 AM)

What It Does

Identifies Old Data
- Finds events older than 90 days
- Counts total events to archive
Batch Deletion
- Deletes in batches of 1000
- Prevents database overload
- Includes small delays between batches
Cleans Up Sessions
- Also deletes sessions older than 90 days
- Keeps database lean
Preserves Aggregated Stats
- Daily stats are kept indefinitely
- Historical trends remain available

Benefits

✅ Reduces database size (can save 80%+ storage)
✅ Improves query performance
✅ Lowers storage costs
✅ Maintains historical trends (via daily stats)
✅ GDPR compliant (data retention policy)

Data Retention Strategy

┌─────────────────────────────────────────────────────┐
│                  Data Retention                     │
├─────────────────────────────────────────────────────┤
│                                                     │
│  Raw Events:        90 days (then deleted)         │
│  Sessions:          90 days (then deleted)         │
│  Daily Stats:       Forever (kept)                 │
│                                                     │
│  Why?                                              │
│  - Raw events for recent detailed analysis         │
│  - Daily stats for long-term trends                │
│  - Balance between detail and storage              │
│                                                     │
└─────────────────────────────────────────────────────┘

Example

Day 1-90:   Full detail available (raw events)
Day 91+:    Aggregated stats only (daily summaries)

Query "Show me traffic for last 30 days"
  → Uses raw events (fast, detailed)

Query "Show me traffic for last year"
  → Uses daily stats (fast, summarized)

Logs

[Analytics Archive] Starting data retention job...
[Analytics Archive] Archiving events older than 2023-10-15 (90 days)
[Analytics Archive] Found 45,234 event(s) to archive
[Analytics Archive] Deleted batch of 1000 events (1000/45234)
[Analytics Archive] Deleted batch of 1000 events (2000/45234)
...
[Analytics Archive] ✅ Archived 45,234 old event(s) (kept daily aggregated stats)
[Analytics Archive] ✅ Archived 12,456 old session(s)
[Analytics Archive] ✅ Data retention job completed successfully

🎯 Job Configuration Summary

Job	Frequency	Duration	Impact
Daily Aggregation	Daily (1 AM)	~1-5 min	Low
Session Cleanup	Every 10 min	~10-30 sec	Very Low
Data Retention	Weekly (Sun 2 AM)	~5-30 min	Low

📊 Performance Impact

Database Load

Daily Aggregation:
  - Reads: ~10K-100K events/day
  - Writes: 1-10 records/day
  - Impact: Low (runs at 1 AM)

Session Cleanup:
  - Reads: ~10-100 sessions
  - Writes: ~10-100 updates
  - Impact: Very Low (small batches)

Data Retention:
  - Reads: ~1K-100K events
  - Deletes: ~1K-100K events
  - Impact: Low (batched, weekly)

Storage Savings

Without jobs:
  - 1M events/month = ~500 MB/month
  - 12 months = ~6 GB

With jobs:
  - 3 months raw events = ~1.5 GB
  - Daily stats = ~50 MB
  - Total = ~1.55 GB (74% savings!)

🔧 Customization

Adjust Retention Period

// src/jobs/archive-old-analytics.ts
const retentionDays = 90; // Change to 30, 60, 180, etc.

Adjust Session Timeout

// src/jobs/cleanup-analytics-sessions.ts
const sessionTimeout = 30 * 60 * 1000; // Change to 15, 45, 60 min

Adjust Aggregation Time

// src/jobs/aggregate-daily-analytics.ts
export const config = {
  schedule: "0 1 * * *", // Change to "0 3 * * *" for 3 AM
};

🐛 Troubleshooting

Job Not Running

# Check logs
tail -f logs/medusa.log | grep "Analytics"

# Verify job is registered
# Jobs should auto-load from src/jobs/

Job Failing

// Check error logs
[Analytics Job] ❌ Error during daily aggregation: ...

// Common issues:
// 1. Database connection
// 2. Missing permissions
// 3. Invalid date ranges

Performance Issues

// If aggregation is slow:
// 1. Add indexes on timestamp fields
// 2. Reduce batch sizes
// 3. Run at off-peak hours

// If cleanup is slow:
// 1. Increase frequency (every 5 min)
// 2. Add index on last_activity_at

📈 Monitoring

Key Metrics to Track

Job Execution Time
- Should be consistent
- Spikes indicate issues
Events Processed
- Should match daily traffic
- Drops indicate missing data
Database Size
- Should stabilize after 90 days
- Growth indicates retention not working
Error Rate
- Should be near zero
- Errors need investigation

Recommended Alerts

// Alert if job fails
if (jobStatus === "failed") {
  sendAlert("Analytics job failed!");
}

// Alert if aggregation is delayed
if (lastAggregation > 36 hours) {
  sendAlert("Daily aggregation is behind!");
}

// Alert if database grows too large
if (databaseSize > 10 GB) {
  sendAlert("Analytics database is large!");
}

✅ Verification

Test Jobs Manually

# In MedusaJS admin or via API
POST /admin/jobs/run
{
  "job_name": "aggregate-daily-analytics"
}

Check Job History

-- View recent aggregations
SELECT * FROM analytics_daily_stats 
ORDER BY date DESC 
LIMIT 10;

-- Check closed sessions
SELECT COUNT(*) FROM analytics_session 
WHERE ended_at IS NOT NULL;

-- Verify data retention
SELECT COUNT(*) FROM analytics_event 
WHERE timestamp < NOW() - INTERVAL '90 days';
-- Should be 0 or very low

🎉 Summary

Your analytics system now has automated maintenance:

✅ Daily aggregation for fast historical queries
✅ Session cleanup for accurate metrics
✅ Data retention for optimal performance

These jobs run automatically in the background, keeping your analytics system fast, accurate, and cost-effective! 🚀

📋 Overview​

🔄 Job 1: Daily Analytics Aggregation​

Purpose​

Schedule​

What It Does​

Benefits​

Example Output​

Logs​

🧹 Job 2: Session Cleanup​

Purpose​

Schedule​

What It Does​

Benefits​

Session Lifecycle​

Example​

Logs​

🗄️ Job 3: Data Retention & Archival​

Purpose​

Schedule​

What It Does​

Benefits​

Data Retention Strategy​

Example​

Logs​

🎯 Job Configuration Summary​

📊 Performance Impact​

Database Load​

Storage Savings​

🔧 Customization​

Adjust Retention Period​

Adjust Session Timeout​

Adjust Aggregation Time​

🐛 Troubleshooting​

Job Not Running​

Job Failing​

Performance Issues​

📈 Monitoring​

Key Metrics to Track​

Recommended Alerts​

✅ Verification​

Test Jobs Manually​

Check Job History​

🎉 Summary​

📋 Overview

🔄 Job 1: Daily Analytics Aggregation

Purpose

Schedule

What It Does

Benefits

Example Output

Logs

🧹 Job 2: Session Cleanup

Purpose

Schedule

What It Does

Benefits

Session Lifecycle

Example

Logs

🗄️ Job 3: Data Retention & Archival

Purpose

Schedule

What It Does

Benefits

Data Retention Strategy

Example

Logs

🎯 Job Configuration Summary

📊 Performance Impact

Database Load

Storage Savings

🔧 Customization

Adjust Retention Period

Adjust Session Timeout

Adjust Aggregation Time

🐛 Troubleshooting

Job Not Running

Job Failing

Performance Issues

📈 Monitoring

Key Metrics to Track

Recommended Alerts

✅ Verification

Test Jobs Manually

Check Job History

🎉 Summary