How to Automate Weekly Ad Performance Summaries

When you automate your weekly ad report email summary, clients get consistent updates without you spending Friday afternoons building slide decks. I send these to every client. The script pulls data, generates insights with AI, and sends a formatted email by 9 AM Monday.

Clients love it because it is reliable. I love it because it takes zero time once built.

What You Need Before Starting

• Ad performance data in SQLite (from your daily pull)
• Anthropic API key for generating insights
• SMTP credentials (Gmail app password works)
• Python 3.8+ with anthropic, smtplib

Step 1: Pull Weekly Performance Data

import sqlite3
from datetime import datetime, timedelta

def get_weekly_data(db_path, account_name):
    conn = sqlite3.connect(db_path)
    end_date = datetime.now().strftime("%Y-%m-%d")
    start_date = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")
    
    summary = conn.execute("""
        SELECT SUM(spend), SUM(impressions), SUM(clicks), SUM(leads),
            SUM(purchases), SUM(purchase_value),
            CASE WHEN SUM(clicks)>0 THEN SUM(spend)/SUM(clicks) ELSE 0 END,
            CASE WHEN SUM(leads)>0 THEN SUM(spend)/SUM(leads) ELSE 0 END
        FROM ad_daily
        WHERE account_name = ? AND date BETWEEN ? AND ?
    """, (account_name, start_date, end_date)).fetchone()
    
    prev_start = (datetime.now() - timedelta(days=14)).strftime("%Y-%m-%d")
    prev_end = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")
    
    prev_summary = conn.execute("""
        SELECT SUM(spend), SUM(impressions), SUM(clicks), SUM(leads),
            SUM(purchases), SUM(purchase_value)
        FROM ad_daily WHERE account_name = ? AND date BETWEEN ? AND ?
    """, (account_name, prev_start, prev_end)).fetchone()
    
    top_ads = conn.execute("""
        SELECT ad_name, SUM(spend), SUM(leads),
            CASE WHEN SUM(leads)>0 THEN SUM(spend)/SUM(leads) ELSE 999 END as cpa
        FROM ad_daily
        WHERE account_name = ? AND date BETWEEN ? AND ?
        GROUP BY ad_name HAVING SUM(spend) > 5
        ORDER BY cpa ASC LIMIT 5
    """, (account_name, start_date, end_date)).fetchall()
    
    conn.close()
    return {"current": summary, "previous": prev_summary, "top_ads": top_ads, "period": f"{start_date} to {end_date}"}

Step 2: Generate AI Insights

import anthropic

def generate_weekly_insight(data):
    client = anthropic.Anthropic()
    
    curr = data["current"]
    prev = data["previous"]
    
    spend_change = ((curr[0] or 0) - (prev[0] or 0)) / (prev[0] or 1) * 100
    lead_change = ((curr[3] or 0) - (prev[3] or 0)) / (prev[3] or 1) * 100
    
    prompt = f"""Write a 3-sentence weekly ad performance insight.

This week: ${curr[0] or 0:.2f} spend, {curr[3] or 0} leads, ${curr[7] or 0:.2f} CPL
Last week: ${prev[0] or 0:.2f} spend, {prev[3] or 0} leads
Spend change: {spend_change:+.1f}%, Lead change: {lead_change:+.1f}%

Top performing ad: {data['top_ads'][0][0] if data['top_ads'] else 'N/A'}

Be direct. No fluff. State what happened, why it matters, and what to do next.
Do not use the words "leverage", "utilize", or "it's important to note"."""

    resp = client.messages.create(
        model="claude-sonnet-4-20250514",
        max_tokens=300,
        messages=[{"role": "user", "content": prompt}]
    )
    return resp.content[0].text

Step 3: Build the Email Template

def build_email_html(data, insight):
    curr = data["current"]
    top_ads_html = ""
    for ad in data["top_ads"]:
        top_ads_html += f"<tr><td style='padding:8px;border-bottom:1px solid #eee;'>{ad[0]}</td><td>${ad[1]:.2f}</td><td>{ad[2]}</td><td>${ad[3]:.2f}</td></tr>"
    
    html = f"""
    <div style="font-family:Arial,sans-serif;max-width:600px;margin:0 auto;">
        <h2 style="color:#333;">Weekly Ad Performance Summary</h2>
        <p style="color:#666;">{data['period']}</p>
        
        <table style="width:100%;border-collapse:collapse;margin:20px 0;">
            <tr style="background:#f5f5f5;">
                <td style="padding:12px;"><strong>Spend</strong><br>${curr[0] or 0:.2f}</td>
                <td style="padding:12px;"><strong>Leads</strong><br>{curr[3] or 0}</td>
                <td style="padding:12px;"><strong>CPL</strong><br>${curr[7] or 0:.2f}</td>
                <td style="padding:12px;"><strong>Clicks</strong><br>{curr[2] or 0}</td>
            </tr>
        </table>
        
        <h3>Insight</h3>
        <p style="color:#333;line-height:1.6;">{insight}</p>
        
        <h3>Top Performing Ads</h3>
        <table style="width:100%;border-collapse:collapse;">
            <tr style="background:#f5f5f5;"><th style="padding:8px;text-align:left;">Ad</th><th>Spend</th><th>Leads</th><th>CPL</th></tr>
            {top_ads_html}
        </table>
    </div>"""
    return html

Step 4: Send the Email

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import os

def send_report(to_email, subject, html_content):
    smtp_user = os.getenv("GMAIL_ADDRESS")
    smtp_pass = os.getenv("GMAIL_APP_PASSWORD")
    
    msg = MIMEMultipart("alternative")
    msg["Subject"] = subject
    msg["From"] = smtp_user
    msg["To"] = to_email
    msg.attach(MIMEText(html_content, "html"))
    
    with smtplib.SMTP("smtp.gmail.com", 587) as server:
        server.starttls()
        server.login(smtp_user, smtp_pass)
        server.sendmail(smtp_user, to_email, msg.as_string())
    
    print(f"Report sent to {to_email}")

Step 5: Schedule for Monday Morning

if __name__ == "__main__":
    clients = [
        {"name": "Client A", "account": "Client A Account", "email": "[email protected]"},
    ]
    
    for client in clients:
        data = get_weekly_data("meta_ads.db", client["account"])
        insight = generate_weekly_insight(data)
        html = build_email_html(data, insight)
        send_report(client["email"], f"Weekly Ad Report - {data['period']}", html)

# Monday at 9 AM
0 9 * * 1 cd /path/to/project && python3 weekly_report.py

Consistent, data-driven, and automatic. Clients see value every Monday without you lifting a finger.

What to Build Next

Add week-over-week trend charts using matplotlib or a charting library. Then add a "This Week's Plan" section that uses AI to recommend budget shifts and creative tests.

Related Reading

• AI-Powered Reporting That Actually Gets Read - making reports that drive action
• The Operator Model - operating like a true AI systems operator
• The Compounding Advantage Nobody Talks About - consistent delivery compounds trust