🚨 Alert System Flow Specifications

Executive Summary

The Alert System is an automated monitoring solution that continuously tracks marketing performance metrics and creates actionable tasks when anomalies are detected. The system operates on a "Detect → Alert → Task → Resolve" workflow, ensuring no performance issues go unnoticed.

---

🎯 Business Value

• Proactive Issue Detection: Automatically identifies underperforming ads/campaigns
• Reduced Manual Monitoring: Eliminates need for constant manual performance checks
• Faster Response Times: Issues are flagged immediately, not during weekly reviews
• Consistent Action: Standardized response process for common issues
• Data-Driven Decisions: All alerts based on historical performance data

---

🔄 Complete Alert Flow

1. Data Collection Phase

BigQuery Data Sources → PostgreSQL Sync → Real-time Monitoring

• Source: Marketing data from Facebook, Google, TikTok ads
• Sync: Daily automated sync from BigQuery to PostgreSQL
• Frequency: Data updated every 24 hours
• Coverage: Ad-level, Campaign-level, Account-level metrics

2. Alert Detection Phase

Performance Data → Rule Evaluation → Threshold Comparison → Alert Trigger

Example Alert Rules:

• High CPL Alert: "When Cost Per Lead > 2x account average"
• Low eROAS Alert: "When effective ROAS < 1.0"
• Poor Show Rate: "When show rate < 80% of account average"
• Budget Pacing: "When daily spend > 130% of target budget"

3. Alert Processing Phase

Alert Triggered → Instance Created → Task Assignment → Notification Sent

What Happens:

1. Alert Instance Created: Records the specific data that triggered the alert
2. Task Auto-Generated: Creates actionable task for the appropriate team member
3. Assignment Logic: Assigns based on alert type and severity
4. Notification Sent: Team member receives immediate notification

4. Manual Triggering Phase

User Clicks "Trigger" → System Evaluates Rules → Creates Instance → Generates Task

What Happens When You Trigger an Alert:

1. Immediate Evaluation: System runs the alert rules against current data
2. Data Query: Queries PostgreSQL for relevant performance metrics
3. Rule Processing: Applies all configured conditions and thresholds
4. Instance Creation: If conditions are met, creates an alert instance
5. Task Generation: Automatically creates a follow-up task
6. Notification: Sends immediate notification to assigned team members
7. Status Update: Button shows "Triggering..." during processing

Trigger Results:

• ✅ Alert Triggered: Conditions met, instance created, task assigned
• ❌ No Alert: Conditions not met, no action taken
• ⚠️ Error: System issue, error logged for debugging

5. Resolution Phase

Task Assigned → Team Action → Issue Resolved 

Resolution Options:

• Resolve: Issue has been fixed, alert closed
• Dismiss: False positive, alert dismissed
• Export Data: Download detailed performance data for analysis

---

📊 Alert Types & Business Impact

Alert Type	Business Impact	Response Time	Assignment
High CPL	High	Immediate	Media Buyer + Admin
Low eROAS	High	Immediate	Media Buyer
Poor Show Rate	Medium	4 hours	Media Buyer
Budget Pacing	High	Immediate	Media Buyer + Admin
Long Lead Time	Medium	8 hours	Operations

---

🎛️ System Architecture

Frontend (User Interface)

• Alert Dashboard: View all alerts and their status
• Alert Settings: Configure thresholds and rules
• Alert History: Track resolution patterns
• Manual Triggering: Test alerts on-demand

Backend (Processing Engine)

• PostgreSQL Database: Stores alert rules and instances (notifications)
• Monitoring Service: Evaluates conditions every hour
• Task Creation API: Automatically creates follow-up tasks
• Notification System: Sends alerts to team members
• Manual Trigger API: Processes on-demand alert triggering

Data Pipeline

• BigQuery: Source of truth for marketing data
• Sync Script: Daily data replication to PostgreSQL
• Real-time Queries: Fast alert evaluation using local data

---

🔧 Key Features

1. Dynamic Thresholds

• Smart Baselines: Uses account historical averages as baselines
• Customizable Multipliers: "2x average" instead of fixed numbers
• Time-based Context: Different thresholds for different time periods

2. Multi-Rule Alerts

• Complex Conditions: "High spend AND low conversion"
• AND/OR Logic: Multiple conditions can be combined
• Custom Variables: Client-specific threshold adjustments

3. Intelligent Assignment

• Role-based: Different alerts go to different team members
• Priority Levels: High/Medium/Low based on business impact
• Escalation: Critical alerts notify multiple team members

4. Client Customization

• Per-Client Rules: Each client can have different thresholds
• Custom Alerts: Clients can create their own alert types
• Frequency Control: Daily, weekly, or custom schedules

5. Manual Triggering System

• On-Demand Testing: Test alerts without waiting for scheduled checks
• Real-Time Evaluation: Immediate rule evaluation against current data
• Debug Capabilities: See exactly what data triggered (or didn't trigger) the alert
• Button State Management: Visual feedback during processing ("Triggering...")

---

🔧 Manual Triggering - Technical Details

Step-by-Step Trigger Process

1. User Action

User clicks "Trigger" button → Frontend sends API request

2. Backend Processing

API receives request → Validates permissions → Loads alert configuration

3. Data Query Execution

-- Example query for CPL alert
SELECT ad_name, date, cpl, total_spend
FROM app.ad_daily_dashboard 
WHERE client_id = :client_id 
  AND day >= COALESCE(last_checked, CURRENT_DATE - INTERVAL '3 days')
  AND cpl > (SELECT AVG(cpl) * :multiplier FROM app.ad_daily_dashboard 
             WHERE client_id = :client_id 
               AND day >= CURRENT_DATE - INTERVAL :timeframe_days)

Timeframe Handling:

• All Time (99999 days): Uses entire account history for baseline calculation
• Specific Days (7, 30, 90): Uses last N days for baseline calculation
• Last Check Logic: Always checks data since last alert check (minimum 3 days)
• Date Range: day >= COALESCE(last_checked, CURRENT_DATE - INTERVAL '3 days')

4. Rule Evaluation

For each data row:
├── Check metric value against threshold
├── Apply operator (greater_than, less_than, etc.)
├── Compare with dynamic baseline (account average)
└── Determine if condition is met

5. Instance Creation (If Triggered)

{
  "alert_id": "uuid",
  "client_id": "uuid", 
  "triggered_at": "2024-01-15T10:30:00Z",
  "alert_data": {
    "ad_name": "Summer Sale Campaign",
    "date": "2025-06-15",
    "cpl": 45.50,
    "account_avg_cpl": 22.75,
    "multiplier": 2.0
  },
  "status": "triggered"
}

6. Task Generation

Alert triggered → Create task → Assign to team member → Send notification

7. Response to User

Success: "Alert triggered successfully - 3 instances created"
No trigger: "No conditions met - alert not triggered"
Error: "Error processing alert - check logs"

Debug Information

When triggering, the system logs:

• Query executed: Exact SQL query used
• Data returned: Number of rows and sample data
• Rule evaluation: Which rules passed/failed
• Threshold comparison: Actual vs expected values
• Performance metrics: Query execution time

Error Handling

• Database errors: Connection issues, query failures
• Permission errors: User not authorized to trigger
• Data errors: Missing or invalid data
• Configuration errors: Invalid alert rules or settings

Timeframe Configuration & Behavior

Timeframe Options

Timeframe	Days	Description	Use Case
All Time	99999	Entire account history	Long-term performance baselines
7 Days	7	Last week	Recent performance trends
30 Days	30	Last month	Monthly performance patterns
90 Days	90	Last quarter	Quarterly performance analysis

Timeframe Logic During Triggering

1. Baseline Calculation:
   ├── All Time (99999): Uses ALL historical data
   ├── Specific Days: Uses last N days only
   └── Result: Average metric value for comparison

2. Data Range Check:
   ├── Always checks: Since last alert check (minimum 3 days)
   ├── Query: day >= COALESCE(last_checked, CURRENT_DATE - INTERVAL '3 days')
   └── Purpose: Avoid duplicate alerts for same data

3. Threshold Comparison:
   ├── Current metric vs. Baseline * Multiplier
   ├── Example: CPL > (Account Avg CPL * 2.0)
   └── Timeframe affects baseline calculation only

Timeframe Examples

-- All Time Baseline (99999 days)
SELECT AVG(cpl) FROM app.ad_daily_dashboard 
WHERE client_id = :client_id 
  AND day >= CURRENT_DATE - INTERVAL '99999 days'

-- 30-Day Baseline
SELECT AVG(cpl) FROM app.ad_daily_dashboard 
WHERE client_id = :client_id 
  AND day >= CURRENT_DATE - INTERVAL '30 days'

-- Data Check Range (always last 3+ days)
SELECT * FROM app.ad_daily_dashboard 
WHERE client_id = :client_id 
  AND day >= COALESCE(last_checked, CURRENT_DATE - INTERVAL '3 days')

Timeframe Impact on Alert Sensitivity

• All Time (99999): Most stable baseline, less sensitive to recent changes
• 7 Days: Most sensitive, catches recent performance shifts quickly
• 30 Days: Balanced sensitivity, good for monthly patterns
• 90 Days: Less sensitive, good for quarterly trends

---

💼 Management Dashboard (Idea)

Key Metrics for Leadership

• Total Alerts: Number of alerts triggered (daily/weekly/monthly)
• Resolution Rate: % of alerts resolved within SLA
• False Positive Rate: % of dismissed alerts
• Cost Impact: Estimated cost savings from early detection
• Team Performance: Alert response times by team member

Alert Categories

• Critical: Requires immediate action (< 1 hour)
• High: Important but can wait (< 4 hours)
• Medium: Standard priority (< 8 hours)
• Low: Can be addressed during regular review

---

🔒 Security & Compliance

• Data Privacy: All client data encrypted and secured
• Access Control: Role-based permissions for alert management
• Audit Trail: Complete log of all alert actions

---

This alert system ensures that marketing performance issues are caught early, addressed quickly, and resolved effectively, leading to better campaign performance and higher ROI.