Day 21: AI Insights Layer - Executive Summary Generator
One-line pitch: Claude-powered insights engine that reads pre-validated SaaS metrics from BigQuery and generates executive-ready strategic summaries with quantified recommendations, reducing analysis time from 4 hours (manual) to 2 minutes (automated).
Part of: Advent Automation 2025 - 25 Days of Data Engineering
Navigation
Quick Access (By Role)
Executive Summary
Business Problem: SaaS executives receive Metabase dashboards with 10+ charts but lack time to analyze patterns, identify root causes, or prioritize strategic actions - manual analysis takes 4+ hours per month.
Solution Delivered: AI insights layer using Claude API (Sonnet 4.5) that automatically reads BigQuery metrics from Day 16, generates executive summaries with quantified recommendations, and triggers automated alerts when metrics cross critical thresholds.
Business Impact: Automated monthly insights generation reduces executive analysis time from 4 hours to 2 minutes while providing prioritized, ROI-ranked recommendations worth $20K+ in retained MRR (e.g., “Intervene with top 10 at-risk customers this week to save $8K-$10K in LTV”).
| For: SaaS Executive Team |
Industry: SaaS/Software |
Time: 3 hours |
Status: ✅ Core pipeline complete |
Key Takeaways
Business Value
- Primary Capability: AI-generated executive summaries with 3-5 prioritized recommendations, each with estimated ROI and effort level
- Decision Enabled: Monthly strategic planning transformed from “here are 10 charts” to “here are 3 actions prioritized by business impact”
- Efficiency Gain: Analysis time reduced from 4 hours (manual chart review + Excel) to 2 minutes (automated AI insights generation)
Technical Achievement
- Core Capability: Version-controlled AI prompt engineering with Claude API integration on pre-validated BigQuery metrics
- Architecture: BigQuery (validated metrics) → Python fetch → Claude API → Markdown insights report
- Scalability: Generates insights on 24 months × 23 cohorts (576 data points) in ~10 seconds at $0.04/run ($1.20/month for daily insights)
Critical Learning
AI on validated metrics beats AI on raw data: Running Claude on pre-calculated aggregates (Day 16 tables) ensures consistent metric definitions, faster processing, and auditability. The AI focuses on pattern recognition and strategic recommendations, not data cleaning - resulting in higher-quality insights at 1/10th the cost.
Business Context
The Challenge
Day 16 created a comprehensive Metabase dashboard with MRR trends, cohort retention curves, and customer health alerts. However, executives still faced:
- Pattern blindness: “MRR is declining, but why?”
- No prioritization: “We have 15 at-risk customers - who should we call first?”
- Time constraints: Monthly board deck preparation required 4+ hours of manual analysis
Why This Matters:
- Stakeholder Impact: Executive time is expensive; automating analysis frees 4 hours/month for strategic work ($400-$2,000/month value depending on role)
- Strategic Value: AI can identify cross-metric patterns humans miss (e.g., “Enterprise customers show 3x churn risk when Feb cohort onboarding process wasn’t followed”)
- Urgency/Frequency: Monthly board meetings require consistent insight quality; manual analysis quality varies with analyst availability
Success Criteria
From Stakeholder Perspective:
- Insights ready in <2 minutes vs. 4 hours of manual analysis
- Recommendations quantified with dollar impact and ROI estimation
- Automated alerts for critical thresholds (e.g., “MRR declined 2+ consecutive months”)
Technical Validation:
- ✅ Fetches validated metrics from BigQuery (reuses Day 16 connection)
- ✅ Generates insights via Claude API with structured prompt template
- ✅ Outputs timestamped markdown reports for historical tracking
- ✅ Triggers 4 types of automated alerts with severity levels
- ✅ Cost: $0.04/run (~4,200 tokens total) = $1.20/month for daily insights
Solution Overview
What It Does
| Capability |
Business Outcome |
| Automated Metrics Fetching |
Pulls latest KPIs, MRR growth, cohort retention, and at-risk customers from BigQuery without manual exports |
| AI Pattern Recognition |
Identifies trends like “MRR declined 2 months consecutively” and hypothesizes root causes |
| Prioritized Recommendations |
Generates 3-5 actions ranked by ROI (e.g., “[URGENT] Save $8K LTV by calling top 10 at-risk customers”) |
| Automated Alerts |
Triggers warnings when churn >40%, MRR declining 2+ months, or at-risk LTV >$10K |
| Historical Tracking |
Saves timestamped insights reports for month-over-month strategy comparison |
Architecture at a Glance
[INPUT] → [TRANSFORMATION] → [OUTPUT]
Day 16 BigQuery Tables → Claude API Analysis → Executive Markdown Report
↓ ↓ ↓
8 pre-calculated tables Sonnet 4.5 (4K tokens) 5-section insights
24 months MRR trends Version-controlled prompt ROI-ranked actions
23 cohort curves Temperature: 0.3 (factual) Automated alerts
10 at-risk customers Cost: $0.04/run 2-minute generation
Technology Stack
- AI Model: Claude Sonnet 4.5 (
claude-sonnet-4-5-20250929)
- Data Source: Google BigQuery (
advent2025-day16.day16_saas_metrics)
- Orchestration: Python 3.9+ with
anthropic SDK + google-cloud-bigquery
- Configuration: YAML-based settings (thresholds, alerts, output format)
- Version Control: Prompt templates tracked in git for iterative improvement
Key Results & Insights
Sample AI-Generated Insights (Synthetic Data)
From actual generated report:
Executive Summary
“The business shows concerning signals with MRR declining 2.03% month-over-month and a 35.6% churn rate. However, 95.3% of active customers remain healthy, suggesting the issue is concentrated in specific cohorts. Immediate focus should be on the $13,790 in at-risk LTV before year-end.”
Strategic Recommendations
| Priority |
Action |
Impact |
Effort |
ROI |
| [URGENT] |
Intervene with top 10 at-risk customers this week |
Save $8K-$10K LTV |
Low (10 calls) |
800%+ |
| [HIGH] |
Analyze Feb 2023 cohort success factors |
+5-8% future retention |
Medium (data analysis) |
$15K+ retained MRR/year |
| [MEDIUM] |
Audit Enterprise plan value proposition |
-20% Enterprise churn |
High (product review) |
$20K+ annual impact |
Automated Alerts Triggered
| Alert Type |
Severity |
Message |
| MRR Decline |
🔴 High |
MRR has declined for 2 consecutive months (-2.03% latest) |
| High Risk LTV |
🟡 Medium |
Total at-risk LTV ($13,790) exceeds $10K threshold |
| Retention Gap |
🟡 Medium |
12% best-worst cohort gap indicates inconsistent customer experience |
| Metric |
Value |
Context |
| Generation Time |
~10 seconds |
From metrics fetch to saved report |
| Input Tokens |
~2,500 |
Metrics JSON + prompt template |
| Output Tokens |
~2,000 |
5-section insights report |
| Cost per Run |
$0.04 |
(2.5K × $0.003) + (2K × $0.015) |
| Monthly Cost |
$1.20 |
Assuming daily insights generation |
| Time Saved |
4 hours → 2 min |
Manual analysis vs. automated |
Analytical Capabilities Demonstrated
- ✅ Cross-Metric Pattern Recognition - AI identified correlation between MRR decline and Q3 2024 cohort underperformance
- ✅ Quantified Business Impact - Every recommendation includes dollar value (e.g., “$8K-$10K LTV at risk”)
- ✅ ROI-Based Prioritization - Actions ranked by impact/effort ratio, not arbitrary urgency
- ✅ Root Cause Hypotheses - AI suggests “Enterprise customers all at-risk → pricing model issue?” based on plan tier patterns
- ✅ Time-Bound Action Items - Recommendations specify “this week” vs. “this month” vs. “this quarter”
Risks & Limitations
Current Limitations
| Limitation |
Impact |
Mitigation Path |
| Synthetic data only |
Cannot validate real business hypotheses |
Pilot with 90 days real SaaS data before trusting recommendations |
| AI can’t verify causation |
Root cause hypotheses may be wrong |
Treat insights as starting points for investigation, not facts |
| No human feedback loop |
Can’t improve based on “was this recommendation useful?” |
Add feedback mechanism (thumbs up/down on each recommendation) |
| English-only prompts |
Cannot generate insights in other languages |
Add i18n support for prompt templates and outputs |
| Single LLM provider |
Anthropic API downtime = no insights |
Add fallback to OpenAI GPT-4 or Google Gemini |
Assumptions Made
- Validated Metrics Assumption - Assumes Day 16 BigQuery tables are correct; AI doesn’t question data quality
- Monthly Frequency Assumption - Designed for monthly insights generation; daily insights may be too frequent for strategic decisions
- Executive Audience Assumption - Prompt optimized for C-level; analysts may want more technical depth
- SaaS Industry Assumption - Benchmarks (5-7% churn) and terminology (“MRR”, “LTV”) specific to SaaS; wouldn’t apply directly to e-commerce or manufacturing
Recommendations
For SaaS Executive Team
Immediate Next Steps (Week 1):
- Run first insights report - Execute
python day21_AI_generate_insights.py to see actual output for your data
- Validate top recommendation - Test AI’s #1 recommendation (contact top 10 at-risk customers) and track actual retention impact
Short-Term (Month 1):
- Compare AI vs. Manual Analysis - Run both side-by-side for 1 month; measure time saved and decision quality
- Tune Alert Thresholds - Adjust
config/day21_CONFIG_insight_settings.yaml to your business (e.g., churn threshold may be 20% for B2C SaaS)
- Monthly Board Deck Integration - Add AI insights section to monthly board deck template
Production Readiness:
- Data Integration: Connect to production BigQuery (currently uses synthetic Day 6 data)
- Validation Required: Verify AI recommendations against known business outcomes for 3 months
- Stakeholder Review: Have CFO/COO validate business logic and threshold settings
For Portfolio/Technical Evolution
Reusability:
- Prompt engineering pattern applicable to any “metrics → insights” workflow (marketing, finance, operations)
- Alert threshold configuration (YAML-based) can be extracted as shared utility for Days 22-25
- BigQuery → AI pipeline transferable to any Google Cloud data warehouse project
Scale Considerations:
- Current capacity: 24 months × 23 cohorts = 576 data points in ~10 seconds
- Optimization needed at: 1,000+ cohorts (would require metric pre-aggregation)
- Architecture changes if >10K cohorts: Switch to embedding-based cohort clustering, then AI analyzes cluster summaries (not individual cohorts)
How to Use This Project
Quick Start (5 minutes)
# 1. Navigate
cd advent-automation-2025/day21
# 2. Install dependencies
pip install -r day21_requirements.txt
# 3. Configure API keys
# Add to ../config/.env:
echo "DAY21_ANTHROPIC_API_KEY=sk-ant-api03-your_key_here" >> ../config/.env
# 4. Verify Day 16 BigQuery connection exists
# Should already have: DAY16_BIGQUERY_CREDENTIALS_PATH in ../config/.env
# 5. Generate insights (full pipeline)
python day21_AI_generate_insights.py
Expected Runtime: ~2 minutes (10 sec fetch + 10 sec AI generation + file I/O)
Expected Output:
insights/day21_METRICS_20251228_143522.json # Raw metrics snapshot
insights/day21_INSIGHT_20251228_143522.md # Executive insights report
Example Output Preview:
======================================================================
DAY 21: AI INSIGHTS LAYER - FULL PIPELINE
======================================================================
[STEP 1/3] Fetching metrics from BigQuery...
✅ Current MRR: $210,596.39
✅ Churn Rate: 35.60%
💰 Total at-risk LTV: $13,790.43
[STEP 2/3] Generating AI insights...
🤖 Model: claude-sonnet-4-5-20250929
✅ Tokens used: 2,341 input, 1,876 output
[STEP 3/3] Saving insights report...
💾 insights/day21_INSIGHT_20251228_143522.md
✅ PIPELINE COMPLETE
Adapting for Real Data
Priority Changes (Do These First):
- Connect to Production BigQuery - Update
config/day21_CONFIG_insight_settings.yaml with your project_id and dataset_id
- Adjust Alert Thresholds - Change
alerts.churn_rate_threshold_pct from 40% to your industry benchmark (B2C SaaS may use 20-30%)
- Customize Prompt Template - Edit
day21_AI_prompt_template.txt to match your business context (industry, model, audience)
Schema Mapping:
| Your Data | This Project | Transform Needed |
|———–|————–|——————|
| Monthly Recurring Revenue | current_mrr | None (same field) |
| Customer Churn % | churn_rate_pct | None (same field) |
| Cohort signup month | cohort_month | Cast to YYYY-MM format |
| Customer risk score | health_status | Map to ‘At Risk’ / ‘Healthy’ / ‘Churned’ |
Business Logic Adjustments:
# In config/day21_CONFIG_insight_settings.yaml
# Adjust these to your business:
alerts:
mrr_growth_negative_months: 2 # Alert if negative X months (you may want 3)
churn_rate_threshold_pct: 40.0 # Alert if above X% (B2C SaaS: 20-30%)
at_risk_ltv_total: 10000.0 # Alert if total > $X (adjust to your scale)
retention_gap_threshold_pct: 10.0 # Alert if best-worst gap > X%
Full adaptation guide: See “Detailed Adaptation” section below
Technical Deep Dive
📋 Full Technical Documentation (Click to Expand)
### Technical Stack
**Core:**
- **Language:** Python 3.9+
- **AI Model:** Claude Sonnet 4.5 (via Anthropic API)
- **Data Warehouse:** Google BigQuery
- **Configuration:** YAML + python-dotenv
**Dependencies:**
```
anthropic>=0.40.0 # Claude API client
google-cloud-bigquery>=3.14.1 # BigQuery integration (reused from Day 16)
google-auth>=2.25.2 # GCP authentication
pyyaml>=6.0.0 # Configuration parsing
python-dateutil>=2.9.0 # Timestamp handling
```
### Data Model
**Input: BigQuery Tables (from Day 16)**
```
day06_dashboard_kpis
├── current_mrr (FLOAT64) - Current monthly recurring revenue
├── churn_rate_pct (FLOAT64) - Overall churn rate percentage
├── active_customers (INT64) - Count of active customers
└── healthy_customer_pct (FLOAT64) - % of customers in healthy status
day06_mrr_summary
├── month (DATE) - Calendar month
├── current_mrr (FLOAT64) - MRR at end of month
├── net_mrr (FLOAT64) - Net change in MRR
└── mom_growth_rate_pct (FLOAT64) - Month-over-month growth %
day06_retention_curves
├── cohort_month (STRING) - Customer signup month (YYYY-MM)
├── months_since_signup (INT64) - Tenure in months (0-12)
└── retention_rate_pct (FLOAT64) - % of cohort still active
day06_customer_health
├── customer_id (INT64) - Unique customer identifier
├── health_status (STRING) - 'Healthy' / 'At Risk' / 'Churned'
├── ltv_estimate (FLOAT64) - Estimated lifetime value
├── current_mrr (FLOAT64) - Current monthly payment
└── plan_tier (STRING) - 'Free' / 'Basic' / 'Pro' / 'Enterprise'
```
**Output: Generated Insights Report**
```markdown
# SaaS Health Metrics - Executive Insights Report
**Generated:** YYYY-MM-DD HH:MM:SS
## 1. EXECUTIVE SUMMARY
[3-5 sentence overview with critical finding and recommended action]
## 2. KEY METRICS OVERVIEW
[KPIs with context and trend indicators]
## 3. GROWTH TRAJECTORY ANALYSIS
[MRR trends, inflection points, 3-month projection]
## 4. RETENTION PERFORMANCE INSIGHTS
[Best vs worst cohorts, gap analysis, root cause hypotheses]
## 5. CUSTOMER RISK ASSESSMENT
[At-risk customers, LTV at risk, intervention strategies]
## 6. STRATEGIC RECOMMENDATIONS
[3-5 prioritized actions with ROI estimates]
## 7. APPENDIX: RAW METRICS
[Complete data snapshot for reference]
```
### Architectural Decisions
#### Decision 1: AI on Pre-Validated Metrics vs. AI on Raw Data
**Context:** Should Claude query raw transaction data or read pre-calculated metrics from Day 16 tables?
**Options Evaluated:**
| Option | Pros | Cons | Decision |
|--------|------|------|----------|
| **AI queries raw data directly** | More flexible, can calculate custom metrics | Slow (complex queries), expensive (10x tokens), inconsistent metric definitions | ❌ Rejected |
| **AI reads pre-calculated metrics** | Fast (~10 sec), cheap ($0.04/run), consistent with dashboard | Less flexible, requires separate ETL (Day 16) | ✅ **Chosen** |
| **Hybrid: AI can query if needed** | Flexibility for custom analysis | Complex architecture, hard to audit | ❌ Rejected |
**Rationale:** Pre-calculated metrics ensure AI insights match dashboard numbers exactly (no "why doesn't AI say $210K when dashboard shows $210K?" discrepancies). Also reduces cost by 90% (2,500 tokens vs. 25,000+ for raw data context).
**Tradeoffs Accepted:**
- ✅ **Gained:** Consistency, speed, cost efficiency, auditability
- ⚠️ **Sacrificed:** AI can't calculate custom metrics not in Day 16 tables
**Generalization:** Use this pattern for any "AI on business metrics" project - always pre-calculate aggregates rather than giving AI raw data access.
---
#### Decision 2: Structured Prompt vs. Free-Form Analysis
**Context:** Should AI output be free-form or forced into specific sections?
**Options Evaluated:**
| Option | Pros | Cons | Decision |
|--------|------|------|----------|
| **Free-form: "Analyze these metrics"** | Creative, might find unexpected patterns | Inconsistent structure, hard to compare month-to-month | ❌ Rejected |
| **Structured: 7 required sections** | Consistent format, comprehensive coverage | Less creative, might feel formulaic | ✅ **Chosen** |
| **Hybrid: Suggest structure but allow flexibility** | Balance of consistency and creativity | Hard to enforce, quality varies | ❌ Rejected |
**Rationale:** Executive reports need consistency for month-over-month comparison. Structured sections ensure no critical analysis is skipped (e.g., AI must address customer risk even if MRR looks healthy).
**Tradeoffs Accepted:**
- ✅ **Gained:** Consistent reports, comprehensive analysis, easy comparison
- ⚠️ **Sacrificed:** AI creativity, potential unexpected insights
**Generalization:** Use structured prompts for recurring reports (monthly insights), free-form for ad-hoc analysis.
---
#### Decision 3: Temperature 0.3 vs. Higher Creativity
**Context:** What temperature setting balances factual accuracy with insight quality?
**Options Evaluated:**
| Option | Pros | Cons | Decision |
|--------|------|------|----------|
| **Temperature 0.1 (deterministic)** | Highly consistent, factual | May miss creative connections, repetitive | ❌ Rejected |
| **Temperature 0.3 (low creativity)** | Consistent + slight variation, factual focus | Less creative hypotheses | ✅ **Chosen** |
| **Temperature 0.7 (balanced)** | Creative insights, varied output | Inconsistent tone, may hallucinate | ❌ Rejected |
**Rationale:** Executive insights require factual accuracy (don't want AI inventing trends), but some creativity helps with root cause hypotheses ("Feb cohort success may be due to...").
**Tradeoffs Accepted:**
- ✅ **Gained:** Consistent, factual analysis with reasonable hypotheses
- ⚠️ **Sacrificed:** Highly creative (but potentially inaccurate) insights
**Generalization:** Temperature 0.2-0.4 for business analysis, 0.7+ for creative content.
---
### Implementation Details
**Key Components:**
#### 1. Metrics Fetching (`day21_DATA_fetch_metrics.py`)
```python
def day21_fetch_kpis():
"""Fetch current KPI snapshot"""
query = f"""
SELECT current_mrr, churn_rate_pct, active_customers, healthy_customer_pct
FROM `{PROJECT}.{DATASET}.day06_dashboard_kpis`
LIMIT 1
"""
result = day21_bq_client.query(query).to_dataframe()
return result.to_dict('records')[0]
# Similar functions for: day21_fetch_mrr_growth(), day21_fetch_retention(), day21_fetch_at_risk_customers()
```
#### 2. Automated Alert Logic
```python
def day21_check_alerts(metrics_data):
"""Check if any automated alerts should be triggered"""
alerts = []
# Example: Check MRR decline
mrr_growth = metrics_data['mrr_growth']
negative_months = sum(1 for m in mrr_growth[:2]
if m['mom_growth_rate_pct'] < 0)
if negative_months >= 2:
alerts.append({
"type": "mrr_decline",
"severity": "high",
"message": f"MRR declined for {negative_months} consecutive months"
})
# Similar checks for: high churn, at-risk LTV, retention gap
return alerts
```
#### 3. AI Insights Generation
```python
def day21_generate_insights(metrics_data):
"""Generate AI insights from metrics data"""
# Load prompt template
prompt_template = day21_load_prompt_template()
# Format metrics as JSON
metrics_json = json.dumps(metrics_data, indent=2)
# Fill template
prompt = prompt_template.format(
metrics_json=metrics_json,
alerts_summary=day21_format_alerts(metrics_data['alerts'])
)
# Call Claude API
message = day21_anthropic_client.messages.create(
model="claude-sonnet-4-5-20250929",
max_tokens=4000,
temperature=0.3,
messages=[{"role": "user", "content": prompt}]
)
return message.content[0].text, message.usage
```
#### 4. Prompt Template Structure
```
[Role Definition]
You are an executive business analyst specializing in SaaS metrics...
[Business Context]
Industry: SaaS | Audience: Executives | Frequency: Monthly
[Metrics Data]
{metrics_json} # Injected dynamically
[Automated Alerts]
{alerts_summary} # Injected dynamically
[Required Output Structure]
## 1. EXECUTIVE SUMMARY (3-5 sentences)
## 2. KEY METRICS OVERVIEW
## 3. GROWTH TRAJECTORY ANALYSIS
## 4. RETENTION PERFORMANCE INSIGHTS
## 5. CUSTOMER RISK ASSESSMENT
## 6. STRATEGIC RECOMMENDATIONS (3-5 prioritized)
[Analysis Guidelines]
- Be Quantitative: Always include numbers
- Identify Causation: Hypothesize why trends exist
- Calculate Impact: Quantify dollar impact
- Prioritize by ROI: Focus on highest revenue impact
- Be Actionable: Every insight → specific next step
```
**Performance Characteristics:**
- **Current dataset:** 576 data points (24 months × 23 cohorts + 10 at-risk customers) in ~10 seconds
- **Token usage:** 2,500 input + 2,000 output = 4,500 total (~$0.04/run)
- **Bottleneck:** Claude API call (8-12 seconds); BigQuery fetch is <1 second
- **Optimization:** Could batch multiple months' metrics in single API call (but reduces insight specificity)
### Testing Approach
**Validation Queries:**
```python
# 1. Data completeness check
assert len(metrics_data['kpis']) > 0, "KPIs not fetched"
assert len(metrics_data['mrr_growth']) == 12, "Should have 12 months of data"
assert len(metrics_data['retention']) > 0, "Retention data missing"
# 2. Alert logic validation
test_metrics = {
'kpis': {'churn_rate_pct': 45.0}, # Above 40% threshold
'mrr_growth': [{'mom_growth_rate_pct': -2.0}, {'mom_growth_rate_pct': -1.5}]
}
alerts = day21_check_alerts(test_metrics)
assert len(alerts) >= 2, "Should trigger churn + MRR decline alerts"
# 3. Cost validation
assert usage.input_tokens < 3000, "Input tokens too high (cost concern)"
assert usage.output_tokens < 5000, "Output tokens too high (cost concern)"
```
**Test Results:**
- ✅ All data fetching functions return expected schema
- ✅ Alert thresholds trigger correctly at boundary values
- ✅ Cost per run averages $0.038-$0.042 (within $0.05 budget)
Detailed Adaptation Guide
🔄 Step-by-Step Production Adaptation (Click to Expand)
### Step 1: Assess Your Data
**Checklist:**
- [ ] Do you have BigQuery access with SaaS metrics tables?
- [ ] Does your schema match Day 16 structure (KPIs, MRR growth, retention, customer health)?
- [ ] What's your data volume? (X cohorts × Y months)
- [ ] What's your update frequency? (daily/weekly/monthly insights generation)
- [ ] Do you have Claude API access? (Sign up at console.anthropic.com)
### Step 2: Map Your Schema
| Your BigQuery Table | Project Table | Required Columns |
|---------------------|---------------|------------------|
| `your_kpis_table` | `day06_dashboard_kpis` | `current_mrr`, `churn_rate_pct`, `active_customers`, `healthy_customer_pct` |
| `your_mrr_table` | `day06_mrr_summary` | `month`, `current_mrr`, `net_mrr`, `mom_growth_rate_pct` |
| `your_cohort_table` | `day06_retention_curves` | `cohort_month`, `months_since_signup`, `retention_rate_pct` |
| `your_customers_table` | `day06_customer_health` | `customer_id`, `health_status`, `ltv_estimate`, `plan_tier` |
### Step 3: Modify Configuration
**Update: `config/day21_CONFIG_insight_settings.yaml`**
```yaml
# Change these to your BigQuery setup
bigquery:
project_id: "your-gcp-project-id" # Your GCP project
dataset_id: "your_saas_metrics_dataset" # Your dataset
# Change table names to match your schema
metrics_sources:
kpis:
table: "your_kpis_table" # Instead of day06_dashboard_kpis
mrr_growth:
table: "your_mrr_table" # Instead of day06_mrr_summary
# etc...
# Adjust thresholds to your business
alerts:
mrr_growth_negative_months: 3 # Your tolerance (vs. 2)
churn_rate_threshold_pct: 25.0 # Your industry benchmark (vs. 40%)
at_risk_ltv_total: 50000.0 # Your scale (vs. $10K)
```
### Step 4: Customize AI Prompt
**Edit: `day21_AI_prompt_template.txt`**
Change business context:
```
# BUSINESS CONTEXT
Industry: [Your Industry - e.g., B2C SaaS, FinTech, EdTech]
Business Model: [Your Model - e.g., Freemium, Enterprise B2B]
Primary Metric: [Your Metric - e.g., ARR instead of MRR]
Target Audience: [Your Audience - e.g., Board of Directors, VC investors]
```
Adjust analysis guidelines:
```
# ANALYSIS GUIDELINES
1. **Be Quantitative**: Always include ARR values (not MRR)
2. **Industry Benchmarks**: Compare to EdTech standards (15-20% churn is normal)
3. **Seasonality**: Note Q1 spike is expected (back-to-school effect)
```
### Step 5: Validate with Sample
**Test with 1 month of data:**
```bash
# Manually limit data in config YAML
metrics_sources:
mrr_growth:
limit: 1 # Just 1 month for testing
# Run pipeline
python day21_AI_generate_insights.py
```
**Compare AI insights to known analysis:**
- [ ] Does AI correctly identify known trends (e.g., "Q4 spike")?
- [ ] Are recommendations aligned with business priorities?
- [ ] Do dollar values match dashboard exactly?
**Common issues and fixes:**
| Issue | Fix |
|-------|-----|
| AI says "$X" but dashboard shows "$Y" | Check BigQuery table is same one Metabase uses |
| Recommendations seem generic | Add more business context to prompt template |
| Cost >$0.10/run | Reduce max_tokens from 4000 to 2000 |
### Step 6: Scale to Production
**Incremental approach:**
1. **Week 1:** Generate insights manually, review with stakeholders
2. **Week 2:** Compare AI insights vs. manual analysis quality
3. **Week 3:** Automate with cron/Airflow for monthly generation
4. **Week 4:** Add email delivery + Slack notifications for alerts
**Automation example (cron):**
```bash
# Run insights on 1st of each month at 8am
0 8 1 * * cd /path/to/day21 && python day21_AI_generate_insights.py && ./send_email.sh
```
**Monitor:**
- API cost trends (should stay <$2/month for daily runs)
- Insight quality (are recommendations getting better or stale?)
- Stakeholder usage (are execs actually reading the reports?)
Project Files
day21/
├── README.md # This file
├── config/
│ └── day21_CONFIG_insight_settings.yaml # All configuration (thresholds, alerts, output)
├── insights/ # Generated reports (timestamped)
│ ├── day21_METRICS_YYYYMMDD_HHMMSS.json # Raw metrics snapshots
│ └── day21_INSIGHT_YYYYMMDD_HHMMSS.md # AI-generated insights
├── tests/ # Unit tests (future)
├── day21_requirements.txt # Python dependencies
├── day21_DATA_fetch_metrics.py # Fetch from BigQuery
├── day21_AI_generate_insights.py # Main pipeline script
├── day21_AI_prompt_template.txt # Claude prompt (version-controlled)
└── .env.example # Environment variables template
Appendix
Time Breakdown
| Phase |
Time |
% |
| Planning & Setup |
30 min |
17% |
| Development (data fetch, AI integration, prompt engineering) |
90 min |
50% |
| Testing (validation, cost optimization) |
30 min |
17% |
| Documentation |
30 min |
17% |
| Total |
180 min |
100% |
Learning Outcomes
Technical Skills Acquired:
- Prompt Engineering: Structured prompts with dynamic variable injection for consistent AI output
- Claude API Integration: Anthropic SDK usage, token optimization, cost management
- YAML Configuration: Centralized settings for thresholds, alerts, and business logic
- BigQuery Reusability: Leveraging existing Day 16 connection and credentials for new use case
Business Domain Understanding:
- SaaS executive reporting requirements (ROI-ranked recommendations, time-bound actions)
- AI limitations in business analysis (can identify patterns but not verify causation)
- Cost-benefit analysis of AI automation ($1.20/month saves 48 hours/year = $2K-$10K value)
Process Improvements for Next Project:
- Start with prompt template design before coding (prompt is 50% of value)
- Version control prompts in git, not just code (enables A/B testing)
- Build cost monitoring early (easy to overspend on LLM APIs without tracking)
Naming Conventions Reference
All project files use day21_ prefix for isolation.
Examples:
- Functions:
day21_fetch_kpis(), day21_generate_insights()
- Variables:
day21_CONFIG, day21_anthropic_client
- Files:
day21_AI_generate_insights.py, day21_requirements.txt
See PROMPT_project_setup.md for complete naming standards.
Links & Resources