Implementation Guide: Analyze campaign performance data and produce plain-language insights reports for clients

Hardware Procurement

27-inch 4K Monitor

Software Procurement

Prerequisites

• Stable internet connection of 50 Mbps or higher (symmetric preferred) at the agency office for reliable API data pulls from multiple advertising platforms simultaneously
• Google Workspace or Microsoft 365 account for the agency (needed for Looker Studio access, email-based report delivery, and calendar scheduling of review meetings)
• Admin-level access to all client advertising platform accounts: Google Ads Manager/MCC account, Meta Business Manager with ad account admin role, LinkedIn Campaign Manager, TikTok Ads Manager
• Admin-level access to client web analytics: Google Analytics 4 properties with at minimum Analyst role, or Adobe Analytics access if applicable
• Admin-level access to client CRM systems: HubSpot (Super Admin), Salesforce (System Administrator profile), or equivalent
• Admin-level access to client email marketing platforms: Mailchimp (Owner/Admin), ActiveCampaign (Admin), Klaviyo (Owner/Admin)
• Admin-level access to client SEO tools: SEMrush (Owner), Ahrefs (Owner/Admin), Moz (Admin) as applicable
• A dedicated agency email address for SaaS platform registration (e.g., analytics@agencyname.com) to avoid account lockout when staff turn over
• Modern web browser (Chrome 120+ or Edge 120+ recommended) on all workstations that will access dashboards
• Credit card or payment method authorized for SaaS platform subscriptions
• Client data processing agreements (DPAs) executed between the agency and each end-client, authorizing collection and AI-based analysis of campaign performance data
• Inventory of all active client campaigns including: platform names, account IDs, campaign names, KPIs tracked, and current reporting frequency
• Agency brand assets (logo in SVG/PNG, brand colors in hex codes, preferred fonts) for white-label report customization
• Dedicated Slack workspace or Microsoft Teams instance for internal alert routing (if automated alerting is desired)

Installation Steps

Step 1: Audit Current Agency Reporting Workflow

Before configuring any technology, conduct a 60–90 minute discovery session with agency leadership and account managers. Document every client account, the platforms used per client, current reporting cadence (weekly/monthly/quarterly), KPIs tracked per client, and pain points in the current reporting process. This audit directly informs how many data sources to license and how to structure dashboard templates.

Step 2: Provision AgencyAnalytics Account and Configure Organization

Create the AgencyAnalytics account under the agency's dedicated analytics email. Select the appropriate plan tier based on client count from Step 1 audit. Configure organization settings including agency name, branding (logo, colors), default timezone, and default currency. Enable two-factor authentication for all admin users.

Step 3: Create Client Campaigns and Connect Data Sources

For each client identified in Step 1, create a dedicated campaign in AgencyAnalytics. Then systematically connect each data source using OAuth authentication or API key entry. Work through one client completely before moving to the next to identify and resolve any authentication issues early.

Step 4: Design White-Label Dashboard Templates

Create reusable dashboard templates in AgencyAnalytics for the agency's most common reporting scenarios. Most agencies need 3–4 templates: a monthly overview template, a paid media deep-dive template, an SEO performance template, and a social media template. Each template should include the agency's branding and standardized KPI widgets.

Step 5: Configure AgencyAnalytics Ask AI for Built-In Insights

Enable and configure the Ask AI feature within AgencyAnalytics to generate instant insights for each client campaign. This provides the first layer of AI-powered analysis directly within the reporting platform without requiring external tools.

Step 6: Provision and Configure Narrative BI Account

Set up Narrative BI as the dedicated AI narrative generation engine. Connect the same data sources that feed AgencyAnalytics (Narrative BI connects directly to source platforms, not through AgencyAnalytics). Configure anomaly detection thresholds and scheduled insight delivery.

Step 7: Set Up OpenAI API for Custom Narrative Generation

Configure the OpenAI API as a custom narrative engine for agencies requiring highly branded, tone-specific report narratives that go beyond Narrative BI's standard output. This involves creating an API key, setting up a lightweight Python script to call GPT-4.1 with structured campaign data, and storing prompt templates.

pip install openai==1.35.0 pandas openpyxl python-dotenv requests

mkdir -p ~/agency-insights/{prompts,data,output,logs}

echo 'OPENAI_API_KEY=sk-your-key-here' > ~/agency-insights/.env

python -c "from openai import OpenAI; client = OpenAI(); print(client.models.list().data[0].id)"

Step 8: Configure Automated Report Scheduling and Delivery

Set up automated report generation and delivery pipelines using AgencyAnalytics' built-in scheduler and Zapier for additional delivery channels. Configure separate schedules for weekly quick-updates and monthly comprehensive reports.

In AgencyAnalytics

In Zapier (for extended delivery)

Step 9: Configure Compliance and Data Governance Controls

Implement privacy compliance measures including data processing agreements, retention policies, AI disclosure statements, and access controls. This is critical for agencies handling EU client data (GDPR) or California resident data (CCPA/CPRA).

Step 10: Pilot Testing with Two Client Campaigns

Before rolling out to all clients, run a full end-to-end pilot with two representative client campaigns — ideally one simple (single-channel) and one complex (multi-channel). Generate complete reports using both Narrative BI and custom OpenAI prompts, compare output quality, and refine configurations.

Step 11: Full Rollout to All Client Campaigns

After successful pilot validation, systematically onboard all remaining client campaigns. Work in batches of 3–5 clients per day to maintain quality control. For each client: connect data sources, apply the appropriate dashboard template, configure report scheduling, and verify data accuracy.

Follow the batch onboarding process — for each client (repeat Steps 3, 4, 5 for each):

Track progress by updating the master sheet with the following columns:

• Client
• Status
• Data Sources Connected
• Templates Applied
• Schedules Set
• Verified

Step 12: Train Agency Staff and Document Standard Operating Procedures

Conduct a 90-minute training session with all agency staff who will interact with the system. Cover dashboard navigation, report customization, AI insight interpretation, anomaly alert response procedures, and common troubleshooting. Leave behind comprehensive SOPs.

• Record the training session (Zoom/Teams) and save to agency's shared drive
• Deliver SOP document covering: Monthly report generation checklist, OAuth token refresh procedure (Meta every 60 days), New client onboarding procedure, Anomaly alert response workflow, AI insight review and approval process, Escalation path to MSP support

Custom AI Components

Campaign Insight Narrative Generator

Type: prompt A structured prompt template system for OpenAI GPT-4.1 that takes structured campaign performance data (as CSV or JSON) and generates a plain-language executive summary with key insights, trend analysis, anomaly callouts, and actionable recommendations. The prompt is designed to produce agency-quality narrative copy that can be pasted directly into client-facing reports.

Implementation

# system prompt, user template, and filled example
# (monthly_report_prompt.py)

# Campaign Insight Narrative Generator - Prompt Template System
# File: ~/agency-insights/prompts/monthly_report_prompt.py

MONTHLY_REPORT_SYSTEM_PROMPT = """
You are a senior marketing analyst at a digital marketing agency. Your job is to analyze campaign performance data and write a clear, professional, and insightful executive summary for a client who is NOT a marketing expert.

Your writing style guidelines:
- Use plain, jargon-free language. If you must use a marketing term, define it parenthetically.
- Lead with the most important finding (best news or most urgent issue).
- Use specific numbers and percentages, not vague qualifiers.
- Compare current period to previous period and highlight meaningful changes.
- Identify 2-3 key wins to celebrate.
- Identify 1-2 areas needing attention with specific, actionable recommendations.
- End with a clear "Next Steps" section with 2-3 prioritized actions.
- Keep the total summary between 300-500 words.
- Use a professional but warm tone — this is a trusted advisor speaking to a business owner.
- Format with clear headers: Performance Overview, Key Wins, Areas for Improvement, Next Steps.
- NEVER fabricate data. Only reference metrics provided in the input data.
- If data is insufficient to make a conclusion, say so honestly.
"""

MONTHLY_REPORT_USER_TEMPLATE = """
Please analyze the following campaign performance data for {client_name} covering the period {date_range}.

The client's business is: {client_business_description}
The client's primary goals are: {client_goals}
The client's industry is: {client_industry}

## Campaign Performance Data

### Overall Metrics (Current Period vs. Previous Period)
{overall_metrics_table}

### Channel Breakdown
{channel_breakdown_table}

### Top Performing Campaigns
{top_campaigns_table}

### Conversion Data
{conversion_data_table}

### Additional Context
{additional_context}

Please generate the executive summary following your guidelines. Remember to use the client's business context to make the insights relevant and actionable for them specifically.
"""

# Example usage with actual data formatting:

EXAMPLE_FILLED_PROMPT = """
Please analyze the following campaign performance data for Sunshine Dental covering the period March 1-31, 2025 vs February 1-28, 2025.

The client's business is: Multi-location dental practice (3 locations in Tampa Bay, FL)
The client's primary goals are: Increase new patient appointments, reduce cost per acquisition
The client's industry is: Healthcare / Dental

## Campaign Performance Data

### Overall Metrics (Current Period vs. Previous Period)
| Metric | March 2025 | February 2025 | Change |
|--------|-----------|---------------|--------|
| Total Ad Spend | $4,250 | $4,100 | +3.7% |
| Total Impressions | 185,000 | 162,000 | +14.2% |
| Total Clicks | 3,420 | 2,890 | +18.3% |
| Click-Through Rate | 1.85% | 1.78% | +0.07pp |
| Total Conversions | 89 | 72 | +23.6% |
| Cost Per Conversion | $47.75 | $56.94 | -16.1% |
| Website Sessions (GA4) | 5,230 | 4,100 | +27.6% |
| Bounce Rate | 42% | 48% | -6pp |

### Channel Breakdown
| Channel | Spend | Conversions | CPA | ROAS |
|---------|-------|-------------|-----|------|
| Google Ads - Search | $2,800 | 62 | $45.16 | 4.2x |
| Meta Ads - Facebook | $1,050 | 21 | $50.00 | 2.8x |
| Meta Ads - Instagram | $400 | 6 | $66.67 | 1.9x |

### Top Performing Campaigns
| Campaign | Platform | Conversions | CPA |
|----------|----------|-------------|-----|
| Emergency Dental Tampa | Google Ads | 28 | $38.20 |
| New Patient Special | Google Ads | 19 | $42.50 |
| Smile Makeover Retarget | Facebook | 14 | $45.00 |

### Conversion Data
| Conversion Type | Count | % of Total |
|----------------|-------|------------|
| Phone Calls | 52 | 58.4% |
| Form Submissions | 31 | 34.8% |
| Online Booking | 6 | 6.7% |

### Additional Context
- New landing page for Emergency Dental launched March 8
- Increased Meta Ads budget by $200 mid-month
- Competitor (Aspen Dental) opened new location nearby in March
"""

Campaign Data Export and Transform Script

Type: workflow

Python script that exports campaign data from AgencyAnalytics via its API (or from CSV exports), transforms it into the structured format required by the narrative prompt template, calls the OpenAI API, and outputs a formatted narrative ready for insertion into client reports. Designed to run on a schedule via cron or Windows Task Scheduler.

Implementation:

# Full campaign data export, transform, and AI narrative generation script

#!/usr/bin/env python3
"""
Campaign Data Export, Transform, and AI Narrative Generation
File: ~/agency-insights/generate_report.py

Usage:
  python generate_report.py --client "Client Name" --period "2025-03" --output pdf
  python generate_report.py --client all --period "2025-03" --output markdown

Requirements:
  pip install openai==1.35.0 pandas openpyxl python-dotenv requests jinja2
"""

import os
import json
import argparse
from datetime import datetime, timedelta
from pathlib import Path

import pandas as pd
import requests
from dotenv import load_dotenv
from openai import OpenAI

# Load environment variables
load_dotenv(Path(__file__).parent / '.env')

# Configuration
OPENAI_API_KEY = os.getenv('OPENAI_API_KEY')
AGENCY_ANALYTICS_API_KEY = os.getenv('AGENCY_ANALYTICS_API_KEY', '')
OUTPUT_DIR = Path(__file__).parent / 'output'
DATA_DIR = Path(__file__).parent / 'data'
PROMPTS_DIR = Path(__file__).parent / 'prompts'
LOG_DIR = Path(__file__).parent / 'logs'

# Ensure directories exist
for d in [OUTPUT_DIR, DATA_DIR, LOG_DIR]:
    d.mkdir(exist_ok=True)

# Client Configuration (loaded from JSON file)
CLIENT_CONFIG_PATH = Path(__file__).parent / 'clients.json'

# Example clients.json structure:
# {
#   "clients": [
#     {
#       "name": "Sunshine Dental",
#       "business_description": "Multi-location dental practice in Tampa Bay, FL",
#       "goals": "Increase new patient appointments, reduce cost per acquisition",
#       "industry": "Healthcare / Dental",
#       "data_sources": ["google_ads", "meta_ads", "ga4"],
#       "report_template": "monthly_overview",
#       "kpi_priorities": ["conversions", "cpa", "roas"]
#     }
#   ]
# }

def load_client_config():
    """Load client configurations from JSON file."""
    if CLIENT_CONFIG_PATH.exists():
        with open(CLIENT_CONFIG_PATH) as f:
            return json.load(f)['clients']
    return []

def load_campaign_data_from_csv(client_name: str, period: str) -> dict:
    """
    Load campaign data from CSV exports.
    Expected files in data/ directory:
      - {client_name}_{period}_overall.csv
      - {client_name}_{period}_channels.csv
      - {client_name}_{period}_campaigns.csv
      - {client_name}_{period}_conversions.csv
    """
    prefix = f"{client_name.lower().replace(' ', '_')}_{period}"
    data = {}
    
    for data_type in ['overall', 'channels', 'campaigns', 'conversions']:
        filepath = DATA_DIR / f"{prefix}_{data_type}.csv"
        if filepath.exists():
            df = pd.read_csv(filepath)
            data[data_type] = df.to_markdown(index=False)
        else:
            data[data_type] = f"No {data_type} data available for this period."
    
    # Load additional context notes if available
    notes_path = DATA_DIR / f"{prefix}_notes.txt"
    if notes_path.exists():
        data['context'] = notes_path.read_text()
    else:
        data['context'] = 'No additional context provided.'
    
    return data

def generate_narrative(client_config: dict, campaign_data: dict, period: str) -> str:
    """
    Call OpenAI GPT-4.1 API with structured prompt to generate plain-language narrative.
    """
    client = OpenAI(api_key=OPENAI_API_KEY)
    
    system_prompt = (
        "You are a senior marketing analyst at a digital marketing agency. "
        "Your job is to analyze campaign performance data and write a clear, professional, "
        "and insightful executive summary for a client who is NOT a marketing expert.\n\n"
        "Writing style guidelines:\n"
        "- Use plain, jargon-free language. Define marketing terms parenthetically if used.\n"
        "- Lead with the most important finding.\n"
        "- Use specific numbers and percentages.\n"
        "- Compare current vs previous period with meaningful context.\n"
        "- Identify 2-3 key wins and 1-2 areas needing attention.\n"
        "- End with a 'Next Steps' section with 2-3 prioritized actions.\n"
        "- Keep the total summary between 300-500 words.\n"
        "- Use headers: Performance Overview, Key Wins, Areas for Improvement, Next Steps.\n"
        "- NEVER fabricate data. Only reference provided metrics.\n"
        "- If data is insufficient, say so honestly.\n"
    )
    
    user_prompt = (
        f"Analyze the following campaign data for {client_config['name']} "
        f"for the period {period}.\n\n"
        f"Business: {client_config['business_description']}\n"
        f"Goals: {client_config['goals']}\n"
        f"Industry: {client_config['industry']}\n"
        f"Priority KPIs: {', '.join(client_config.get('kpi_priorities', ['conversions']))}\n\n"
        f"## Overall Metrics\n{campaign_data['overall']}\n\n"
        f"## Channel Breakdown\n{campaign_data['channels']}\n\n"
        f"## Top Campaigns\n{campaign_data['campaigns']}\n\n"
        f"## Conversions\n{campaign_data['conversions']}\n\n"
        f"## Additional Context\n{campaign_data['context']}\n\n"
        f"Generate the executive summary now."
    )
    
    response = client.chat.completions.create(
        model='gpt-4.1',
        messages=[
            {'role': 'system', 'content': system_prompt},
            {'role': 'user', 'content': user_prompt}
        ],
        temperature=0.4,
        max_tokens=1500
    )
    
    narrative = response.choices[0].message.content
    
    # Log usage for cost tracking
    usage = response.usage
    log_entry = {
        'timestamp': datetime.now().isoformat(),
        'client': client_config['name'],
        'period': period,
        'model': 'gpt-4.1',
        'input_tokens': usage.prompt_tokens,
        'output_tokens': usage.completion_tokens,
        'estimated_cost': (usage.prompt_tokens * 2.0 + usage.completion_tokens * 8.0) / 1_000_000
    }
    log_path = LOG_DIR / f"api_usage_{datetime.now().strftime('%Y%m')}.jsonl"
    with open(log_path, 'a') as f:
        f.write(json.dumps(log_entry) + '\n')
    
    return narrative

def save_output(client_name: str, period: str, narrative: str, fmt: str = 'markdown'):
    """Save generated narrative to file."""
    safe_name = client_name.lower().replace(' ', '_')
    if fmt == 'markdown':
        path = OUTPUT_DIR / f"{safe_name}_{period}_insights.md"
        path.write_text(f"# Campaign Performance Insights: {client_name}\n"
                       f"## Period: {period}\n\n"
                       f"---\n\n{narrative}\n\n"
                       f"---\n\n*Insights generated with AI assistance and reviewed for accuracy.*\n")
    elif fmt == 'json':
        path = OUTPUT_DIR / f"{safe_name}_{period}_insights.json"
        with open(path, 'w') as f:
            json.dump({'client': client_name, 'period': period, 'narrative': narrative,
                      'generated_at': datetime.now().isoformat()}, f, indent=2)
    print(f"Output saved to: {path}")
    return path

def main():
    parser = argparse.ArgumentParser(description='Generate AI campaign insight reports')
    parser.add_argument('--client', required=True, help='Client name or "all"')
    parser.add_argument('--period', required=True, help='Report period (YYYY-MM)')
    parser.add_argument('--output', default='markdown', choices=['markdown', 'json'])
    args = parser.parse_args()
    
    clients = load_client_config()
    
    if args.client.lower() == 'all':
        targets = clients
    else:
        targets = [c for c in clients if c['name'].lower() == args.client.lower()]
        if not targets:
            print(f"Client '{args.client}' not found in clients.json")
            return
    
    for client_config in targets:
        print(f"\nGenerating report for: {client_config['name']} ({args.period})")
        campaign_data = load_campaign_data_from_csv(client_config['name'], args.period)
        narrative = generate_narrative(client_config, campaign_data, args.period)
        save_output(client_config['name'], args.period, narrative, args.output)
        print(f"Estimated API cost: see {LOG_DIR}/api_usage_{datetime.now().strftime('%Y%m')}.jsonl")

if __name__ == '__main__':
    main()

Anomaly Detection Alert Router

Type: workflow A Zapier/Make.com workflow configuration that routes Narrative BI anomaly alerts to the appropriate agency team members based on severity and client, creates follow-up tasks in the agency's project management tool, and logs all alerts for monthly review.

Implementation

ZAP 1: Anomaly Alert Triage and Slack Notification

Trigger Setup

Action 1: Parse Email Body

Action 2: Conditional Routing (Paths by Zapier)

Path A — High Severity

🚨 *HIGH PRIORITY ANOMALY*
*Client:* {Client Name}
*Metric:* {Metric Name}
*Change:* {Change Percentage}% {Direction}
*Action Required:* Review immediately and contact client if budget impact detected.

Path B — Medium Severity

⚠️ *Anomaly Detected*
*Client:* {Client Name}
*Metric:* {Metric Name}
*Change:* {Change Percentage}% {Direction}
*Action:* Review during next weekly check-in.

Path C — Low Severity

ZAP 2: Weekly Anomaly Summary Digest

Make.com Alternative (for complex routing)

Google Sheet: Anomaly Log Template

• Create a Google Sheet named 'Campaign Anomaly Log' with the following columns:
• A: Date Detected
• B: Client Name
• C: Data Source (Google Ads, Meta, GA4, etc.)
• D: Metric Name
• E: Previous Period Value
• F: Current Period Value
• G: Change Percentage
• H: Direction (Increase/Decrease)
• I: Severity (High/Medium/Low)
• J: Alert Routed To (Slack channel or person)
• K: Resolution Status (Open/Acknowledged/Resolved)
• L: Resolution Notes
• M: Resolved By
• N: Resolution Date

Client Onboarding Data Collection Form

Type: integration

A structured data collection template and automation for onboarding new agency clients into the insights platform. Captures all required account credentials, KPI definitions, reporting preferences, and brand guidelines needed to configure the system.

Implementation:

Section 1: Client Business Information

• Client/Company Name (text, required)
• Client Website URL (url, required)
• Industry/Vertical (dropdown: Healthcare, Legal, Home Services, E-commerce, B2B SaaS, Restaurant/Hospitality, Real Estate, Education, Financial Services, Other)
• Business Description (textarea: 2-3 sentences describing what the business does)
• Primary Geographic Market (text: e.g., 'Tampa Bay, FL metro area')
• Number of Locations (number, if applicable)

Section 2: Marketing Goals & KPIs

• Primary Marketing Goal (dropdown: Lead Generation, E-commerce Sales, Brand Awareness, App Downloads, Store Visits, Phone Calls, Other)
• Top 3 KPIs to Track (multi-select: Conversions, Cost Per Acquisition, ROAS, Click-Through Rate, Impressions, Website Traffic, Bounce Rate, Revenue, Phone Calls, Form Submissions, Email Signups)
• Target CPA (if known) (currency field)
• Target ROAS (if known) (number field)
• Monthly Ad Budget (currency field, required)

Section 3: Active Advertising Platforms

For each platform, collect the following fields:

• Platform Name (checkbox list: Google Ads, Meta/Facebook Ads, Instagram Ads, LinkedIn Ads, TikTok Ads, Microsoft/Bing Ads, Pinterest Ads, Twitter/X Ads, Snapchat Ads)
• Account ID (text field per platform)
• Admin Email (email field — the login that has admin access)
• Monthly Spend on this Platform (currency)

Google Ads specific:

• MCC Account ID (text: xxx-xxx-xxxx)
• Individual Account ID (text: xxx-xxx-xxxx)

Meta specific:

• Business Manager ID (text)
• Ad Account ID (text: act_xxxxxxxxxx)
• Facebook Page URL (url)
• Instagram Handle (text)

Section 4: Analytics & Tracking

• Google Analytics Version (radio: GA4, Universal Analytics/legacy, Not sure, None)
• GA4 Property ID (text, if applicable)
• Google Tag Manager Container ID (text: GTM-XXXXXXX, if applicable)
• Are conversion tracking pixels properly installed? (radio: Yes, No, Not sure)

Section 5: Other Marketing Tools

• CRM Platform (dropdown: HubSpot, Salesforce, Pipedrive, Zoho, None, Other)
• Email Marketing Platform (dropdown: Mailchimp, ActiveCampaign, Klaviyo, Constant Contact, None, Other)
• SEO Tool (dropdown: SEMrush, Ahrefs, Moz, None, Other)
• Social Media Management (dropdown: Hootsuite, Sprout Social, Buffer, None, Other)
• E-commerce Platform (dropdown: Shopify, WooCommerce, BigCommerce, Magento, None, Other)
• For each selected tool: Account admin email + API key location instructions

Section 6: Reporting Preferences

• Report Frequency (dropdown: Weekly, Bi-weekly, Monthly, Quarterly)
• Preferred Report Delivery (multi-select: Email PDF, Live Dashboard Link, Slack Message, Client Portal)
• Report Recipients (repeating fields: Name, Email, Role)
• Previous Report Samples (file upload: attach examples of reports client has received before)
• Specific Metrics Client Cares About Most (textarea)
• Anything to Avoid in Reports (textarea: e.g., 'Don't show impression data, client doesn't understand it')

Section 7: Branding (for white-label reports)

• Agency Logo (file upload: SVG or PNG, min 200x200px)
• Primary Brand Color (text: hex code, e.g., #2563EB)
• Secondary Brand Color (text: hex code)
• Preferred Font (text, or dropdown of common web fonts)
• Report Footer Text (text: e.g., 'Prepared by XYZ Marketing Agency')

Automation: Post-Submission Workflow

When the form is submitted, trigger the following steps:

Project subtasks (all assigned to MSP tech unless noted):

Monthly API Cost Tracker and Budget Alert

Type: skill

A lightweight monitoring script that tracks OpenAI API usage costs across all client report generations, compares against the monthly budget cap, and sends alerts when approaching limits. Runs daily via cron to ensure cost visibility.

Implementation:

# Daily cron script for tracking OpenAI API spend and sending budget alerts
# via email and Slack

#!/usr/bin/env python3
"""
Monthly API Cost Tracker and Budget Alert
File: ~/agency-insights/cost_tracker.py

Run daily via cron:
  0 8 * * * /usr/bin/python3 /home/user/agency-insights/cost_tracker.py

Or via Windows Task Scheduler equivalent.
"""

import json
import os
import smtplib
from datetime import datetime
from email.mime.text import MIMEText
from pathlib import Path

from dotenv import load_dotenv

load_dotenv(Path(__file__).parent / '.env')

# Configuration
LOG_DIR = Path(__file__).parent / 'logs'
MONTHLY_BUDGET = float(os.getenv('MONTHLY_API_BUDGET', '100.00'))  # dollars
ALERT_THRESHOLD = float(os.getenv('ALERT_THRESHOLD', '0.80'))  # 80% of budget
ALERT_EMAIL = os.getenv('ALERT_EMAIL', 'msp-admin@example.com')
SMTP_HOST = os.getenv('SMTP_HOST', 'smtp.gmail.com')
SMTP_PORT = int(os.getenv('SMTP_PORT', '587'))
SMTP_USER = os.getenv('SMTP_USER', '')
SMTP_PASS = os.getenv('SMTP_PASS', '')
SLACK_WEBHOOK_URL = os.getenv('SLACK_WEBHOOK_URL', '')

def get_monthly_costs(year_month: str) -> dict:
    """Parse JSONL log file and sum costs by client."""
    log_file = LOG_DIR / f'api_usage_{year_month}.jsonl'
    costs = {'total': 0.0, 'by_client': {}, 'call_count': 0}
    
    if not log_file.exists():
        return costs
    
    with open(log_file) as f:
        for line in f:
            if line.strip():
                entry = json.loads(line)
                cost = entry.get('estimated_cost', 0)
                client = entry.get('client', 'unknown')
                costs['total'] += cost
                costs['by_client'][client] = costs['by_client'].get(client, 0) + cost
                costs['call_count'] += 1
    
    return costs

def send_alert(subject: str, body: str):
    """Send alert via email and Slack."""
    # Email alert
    if SMTP_USER and SMTP_PASS:
        msg = MIMEText(body)
        msg['Subject'] = subject
        msg['From'] = SMTP_USER
        msg['To'] = ALERT_EMAIL
        try:
            with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
                server.starttls()
                server.login(SMTP_USER, SMTP_PASS)
                server.send_message(msg)
            print(f'Email alert sent to {ALERT_EMAIL}')
        except Exception as e:
            print(f'Email send failed: {e}')
    
    # Slack alert
    if SLACK_WEBHOOK_URL:
        import requests
        try:
            requests.post(SLACK_WEBHOOK_URL, json={'text': f'*{subject}*\n{body}'})
            print('Slack alert sent')
        except Exception as e:
            print(f'Slack send failed: {e}')

def main():
    current_month = datetime.now().strftime('%Y%m')
    costs = get_monthly_costs(current_month)
    
    # Generate daily summary
    summary = (
        f"OpenAI API Cost Report - {datetime.now().strftime('%Y-%m-%d')}\n"
        f"{'='*50}\n"
        f"Month: {datetime.now().strftime('%B %Y')}\n"
        f"Total API calls: {costs['call_count']}\n"
        f"Total cost: ${costs['total']:.4f}\n"
        f"Budget: ${MONTHLY_BUDGET:.2f}\n"
        f"Budget used: {(costs['total']/MONTHLY_BUDGET*100):.1f}%\n"
        f"Budget remaining: ${(MONTHLY_BUDGET - costs['total']):.2f}\n\n"
        f"Cost by client:\n"
    )
    for client, cost in sorted(costs['by_client'].items(), key=lambda x: -x[1]):
        summary += f"  {client}: ${cost:.4f}\n"
    
    print(summary)
    
    # Check budget threshold
    usage_pct = costs['total'] / MONTHLY_BUDGET if MONTHLY_BUDGET > 0 else 0
    
    if usage_pct >= 1.0:
        send_alert(
            '🚨 OpenAI API Budget EXCEEDED',
            f"Monthly budget of ${MONTHLY_BUDGET:.2f} has been exceeded.\n"
            f"Current spend: ${costs['total']:.2f} ({usage_pct*100:.0f}%)\n"
            f"Action: Review usage immediately and increase budget cap if warranted.\n\n{summary}"
        )
    elif usage_pct >= ALERT_THRESHOLD:
        send_alert(
            '⚠️ OpenAI API Budget Warning',
            f"Monthly spend has reached {usage_pct*100:.0f}% of ${MONTHLY_BUDGET:.2f} budget.\n"
            f"Current spend: ${costs['total']:.2f}\n"
            f"Remaining: ${(MONTHLY_BUDGET - costs['total']):.2f}\n\n{summary}"
        )

if __name__ == '__main__':
    main()

Testing & Validation

• CONNECTIVITY TEST: For each connected data source in AgencyAnalytics, verify the 'Last Synced' timestamp is within the past 24 hours. Navigate to each client campaign > Integrations and confirm every source shows a green 'Connected' status. Document any sources showing 'Error' or 'Disconnected' and resolve before proceeding.
• DATA ACCURACY TEST: For each pilot client, manually pull the 'Total Ad Spend' metric directly from the source platform (e.g., Google Ads dashboard) for the same date range and compare it to the value shown in AgencyAnalytics. The values should match within 2% tolerance. Repeat for 'Total Clicks' and 'Total Conversions'. Document any discrepancies greater than 2% and investigate data sync timing or attribution model differences.
• NARRATIVE BI INSIGHT QUALITY TEST: Generate a Narrative BI insight report for a pilot client and have the agency's account manager review it against their domain knowledge. Validate that: (1) all cited metrics are factually accurate, (2) trend descriptions match actual data direction, (3) no metrics are fabricated or hallucinated, (4) the language is appropriate for a non-technical client audience. Score on a 1-5 scale for accuracy, relevance, and clarity.
• OPENAI API NARRATIVE TEST: Run the generate_report.py script for both pilot clients and verify: (1) the script completes without errors, (2) output files are created in the output/ directory, (3) the narrative follows the specified format (Performance Overview, Key Wins, Areas for Improvement, Next Steps), (4) the narrative stays within 300-500 words, (5) no data is hallucinated. Cross-reference every number cited in the AI narrative against the input CSV data.
• AUTOMATED REPORT DELIVERY TEST: Trigger a manual report send from AgencyAnalytics and verify: (1) the PDF email arrives at the designated recipient within 5 minutes, (2) the PDF renders correctly with agency branding (logo, colors, footer), (3) all charts and tables are legible, (4) the AI-generated executive summary text block appears in the correct position. Check email formatting in both Gmail and Outlook clients.
• ANOMALY ALERT ROUTING TEST: Manually create a test entry in the Narrative BI anomaly notification (or simulate by sending a test email with the expected subject line format to analytics@agencyname.com). Verify: (1) the Zapier/Make.com workflow triggers, (2) the Slack message appears in the correct channel with proper formatting, (3) the task is created in Monday.com/Asana with correct assignee and priority, (4) the anomaly is logged in the Google Sheet anomaly log.
• WHITE-LABEL BRANDING TEST: Generate a complete client report and verify: (1) the agency's logo appears in the header, (2) brand colors are applied to charts and headers, (3) no AgencyAnalytics or Narrative BI branding is visible anywhere in the client-facing output, (4) the footer contains the correct agency attribution and AI disclosure text, (5) the report would be indistinguishable from one the agency produced manually.
• OAUTH TOKEN REFRESH TEST: Intentionally disconnect and reconnect a Meta/Facebook Ads data source to verify the OAuth re-authentication process works smoothly. Document the exact steps required and the time taken. This validates that the SOP for the 60-day Meta token refresh is accurate and executable by agency staff without MSP assistance.
• MULTI-CLIENT BATCH GENERATION TEST: Run the generate_report.py script with the '--client all' flag to generate narratives for all configured clients in sequence. Verify: (1) all reports generate successfully, (2) total API cost logged in the JSONL file is within expected range, (3) each client's narrative is distinct and references that client's specific data (no cross-contamination between client reports), (4) total execution time is under 5 minutes for up to 20 clients.
• COST TRACKING AND BUDGET ALERT TEST: After running several API calls, execute cost_tracker.py and verify: (1) it accurately sums costs from the JSONL log file, (2) per-client cost breakdown is accurate, (3) when the simulated spend exceeds the ALERT_THRESHOLD, an email and/or Slack alert is sent with the correct information. Temporarily set MONTHLY_BUDGET to $0.01 to trigger the exceeded alert and verify it fires.
• COMPLIANCE VERIFICATION TEST: Audit all SaaS vendor accounts and verify: (1) DPAs are signed and filed for AgencyAnalytics, Narrative BI, OpenAI, and Zapier, (2) OpenAI data controls show 'Improve our models' is disabled, (3) AgencyAnalytics data retention policy is set and matches client contracts, (4) all user accounts have 2FA enabled, (5) role-based access controls restrict client data to appropriate team members only, (6) AI disclosure footer text appears on all report templates.
• END-TO-END INTEGRATION TEST: Starting from raw data in advertising platforms, trace the complete data flow: Source Platform → AgencyAnalytics (verify sync) → Narrative BI (verify insight generation) → OpenAI API (verify narrative generation) → PDF Report (verify formatting) → Email/Slack Delivery (verify receipt). Time the entire process and confirm it completes within the expected automated schedule window. Document the end-to-end latency.

Client Handoff

The client handoff session should be a structured 90-minute meeting with all agency stakeholders who will interact with the system, including agency owners, account managers, and anyone who reviews or sends client reports.

Training Topics to Cover (with hands-on demonstrations)

Documentation to Leave Behind

• Recorded video of the 90-minute training session (uploaded to agency's shared drive)
• Standard Operating Procedure (SOP) document in Google Docs with: monthly report generation checklist, OAuth refresh procedures, new client onboarding guide, anomaly alert response workflow, escalation contacts
• Master Configuration Sheet showing all connected accounts, credentials locations, and schedule settings
• Client onboarding form template (Google Form or Typeform) ready for use
• Architecture diagram (visual, one-page) suitable for posting on an office wall or intranet
• MSP support contact card: email, phone, expected response times, escalation path

Success Criteria to Review Together

Maintenance

Ongoing MSP Maintenance Responsibilities:

Weekly (15-30 minutes)

• Review the anomaly alert log in Google Sheets; confirm no alerts went unacknowledged for more than 48 hours
• Check AgencyAnalytics integration status dashboard; verify all data sources show 'Connected' with recent sync timestamps
• Review OpenAI API cost tracker output; confirm spend is tracking within budget

Monthly (1-2 hours)

• Generate and review the monthly API cost report; compare against budget and adjust if needed
• Check for SaaS platform updates: review AgencyAnalytics, Narrative BI, and Zapier changelogs for new features, deprecated integrations, or pricing changes
• Refresh any OAuth tokens approaching expiration (especially Meta Ads tokens every 60 days — set recurring calendar reminders on day 55)
• Review AI narrative output quality with a sample of 2-3 client reports; check for drift in quality, relevance, or accuracy
• Update OpenAI prompt templates if GPT model versions change or if agency feedback indicates narrative quality issues
• Verify automated report delivery is functioning: spot-check that reports arrived on schedule in agency and client inboxes

Quarterly (2-4 hours)

• Conduct a full compliance audit: verify DPAs are current, data retention policies are enforced, 2FA is enabled for all accounts, role-based access controls are properly configured
• Review and update the SOP document based on any changes in the past quarter
• Meet with agency leadership to review system ROI: compare time spent on reporting before vs. after implementation, client satisfaction scores, and any new client needs
• Evaluate new features from SaaS vendors (e.g., new AgencyAnalytics AI capabilities, Narrative BI updates, new OpenAI models) and recommend upgrades
• Review and optimize Zapier/Make.com automation workflows; check for failed executions in the workflow logs
• Update client onboarding templates if new platforms or data sources have been added

Annual (half-day)

• Full platform re-evaluation: assess whether current SaaS stack is still the best fit or if alternatives offer better value
• Renegotiate SaaS vendor contracts if approaching annual renewal (look for volume discounts as client count grows)
• Comprehensive security review: rotate API keys, audit user access lists, remove former employees
• Update data processing agreements with any vendors whose terms have changed
• Review and update privacy compliance posture against new regulations (new US state privacy laws take effect throughout 2025-2026)

SLA Considerations

• Target 4-hour response time for critical issues (data sources disconnected, report delivery failures, security concerns)
• Target 1-business-day response for non-critical requests (new client onboarding, template changes, configuration adjustments)
• Maintain 99.5% report delivery reliability (no more than 1 missed scheduled report per 200 deliveries)
• Monthly uptime/reliability report provided to agency

Escalation Path

Model/Prompt Retraining Triggers

• OpenAI releases a new recommended model (e.g., GPT-4.1 → GPT-5): test new model with existing prompts, compare output quality, and migrate if superior
• Agency feedback indicates narrative quality decline (e.g., generic insights, missed trends): refine system prompts and add client-specific context
• New client industry added that existing prompts don't handle well: create industry-specific prompt variants
• Significant changes in ad platform metrics or terminology (e.g., Google Ads deprecating a metric): update prompt templates to reference current terminology

Alternatives

Turnkey All-in-One: Whatagraph Platform

Replace the AgencyAnalytics + Narrative BI stack with Whatagraph as a single platform that provides data aggregation, visualization, AI-assisted reporting, and white-label client delivery in one tool. Whatagraph offers credit-based pricing where each data source costs 1 credit regardless of plan, with unlimited users on higher tiers. The Professional plan ($199/month) supports 25 data sources and 5 users; the Premium plan ($299/month) supports 50 data sources and 10 users.

Custom Data Pipeline: Supermetrics + BigQuery + Looker Studio + OpenAI API

Build a fully custom analytics pipeline using Supermetrics (€29–€399/month) for data extraction, Google BigQuery (free tier: 1TB queries/month, 10GB storage) as the data warehouse, Google Looker Studio (free) for dashboards, and OpenAI GPT-4.1 API ($15–$50/month typical) for narrative generation. All orchestrated by custom Python scripts running on a small cloud VM ($30–$50/month).

Microsoft Power BI + Azure OpenAI Service

For agencies already embedded in the Microsoft 365 ecosystem, use Power BI Pro ($14/user/month) for dashboards and visualization, with data connectors pulling from ad platforms via Supermetrics or direct API connectors. Use Azure OpenAI Service (same GPT-4.1 models at similar pricing) for narrative generation, keeping all infrastructure within the Microsoft/Azure ecosystem. Power Automate replaces Zapier for workflow automation.

HubSpot Marketing Hub Professional + ChatSpot AI

For agencies heavily invested in HubSpot, leverage HubSpot Marketing Hub Professional ($800/month) which includes built-in campaign analytics, attribution reporting, and AI-powered insights via ChatSpot (HubSpot's AI assistant). This consolidates CRM, marketing automation, analytics, and AI insights into a single platform.