Ready

RBD Marketing Recap Bot — Implementation Plan

For Claude: REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.

Goal: Mary sends any recap-related message to the Telegram bot and receives the RBD monthly marketing one-pager as a PDF in her email.

Architecture: Build marketing_recap_generator.py that pulls live KPIs (Meta Ads spend, Google Ads spend, social followers from CSV, Klaviyo list sizes), fills the templatized HTML, converts to PDF via wkhtmltopdf, and emails it via send_email.py. Add a bot-guides/marketing-recap.md guide so the existing Telegram bot knows what to do when Mary asks. Add Mary to the bot allowlist once she messages.

Tech Stack: Python 3.11, wkhtmltopdf, existing Meta Ads/Google Ads/social tracker infrastructure, send_email.py (Gmail SMTP)

Data Model

KPI	Source	Field
Total Monthly Spend	Meta Ads API (all 3 stores) + Google Ads API (all 4 accounts)	Sum of spend
YouTube Subscribers	social-media-tracker.csv (latest RBD row)	`YouTube_Subs`
Consumer Email List	social-media-tracker.csv (latest Consumers row)	Notes field
Instagram Followers	social-media-tracker.csv (latest RBD row)	`IG_Followers`
Facebook Followers	social-media-tracker.csv (latest RBD row)	`FB_Followers`
YTD Budget Pacing	Meta + Google YTD spend / annual budget target	Calculated
Channel Spend Breakdown	Meta (3 stores) + Google (4 accounts) subtotals	Per-platform

Annual targets (in config YAML):

Annual ad budget: $120,000
Email list goal: 150,000
IG goal: 276,000 (beat Moda)
YouTube goal: 200,000

Files to Create / Modify

Action	Path
Create	`~/ai-projects-local/mission-control/scripts/marketing_recap_generator.py`
Create	`~/ai-projects-local/mission-control/scripts/bot-guides/marketing-recap.md`
Create	`~/ai-projects-local/mission-control/config/marketing-recap-config.yaml`
Modify	`~/ai-projects-local/mission-control/scripts/telegram_watcher.py` (add Mary)
Modify	`~/ai-projects/Marketing-Plans/RBD_Monthly_Marketing_Recap_April2026.html` → templatized copy

Templatized HTML saved at: ~/ai-projects-local/mission-control/templates/marketing_recap_template.html

Task 1: Create Config YAML

Files:

Create: ~/ai-projects-local/mission-control/config/marketing-recap-config.yaml

Step 1: Create the file

# Annual targets — update each January
annual_budget_usd: 120000
goals:
  email_consumers: 150000
  instagram_followers: 276000
  youtube_subs: 200000

# Output dir for generated PDFs
output_dir: ~/ai-projects/mission-control/reports/marketing-recaps/

# Recipients
default_email: mary@alpineanalytica.com

Step 2: Verify the file is readable

python3 -c "import yaml; print(yaml.safe_load(open('config/marketing-recap-config.yaml')))"

Expected: dict with annual_budget_usd, goals, output_dir keys

Task 2: Templatize the HTML

Files:

Read: ~/ai-projects/Marketing-Plans/RBD_Monthly_Marketing_Recap_April2026.html
Create: ~/ai-projects-local/mission-control/templates/marketing_recap_template.html

Step 1: Copy the HTML and replace all hardcoded data values with {VARNAME} placeholders

Key substitutions (use Python .format() style — single curly braces):

Find (in HTML)	Replace with
`April 2026` (in `<h1>`)	`{MONTH_LABEL}`
`April 2026` (in footer/subhead)	`{MONTH_LABEL}`
`$[DATA]` (Total Monthly Spend)	`{TOTAL_SPEND}`
`183K` (YouTube KPI num)	`{YOUTUBE_SUBS}`
`▲ +1K in April · Goal: 200K`	`{YOUTUBE_DELTA}`
`122,832` (Consumer Email KPI)	`{EMAIL_CONSUMERS}`
`+X in April` (email delta)	`{EMAIL_DELTA}`
`233K` (Instagram KPI)	`{INSTAGRAM_FOLLOWERS}`
`33%` (YTD Pacing KPI num)	`{YTD_PACING_PCT}`
`4 of 12 mo.`	`{MONTHS_ELAPSED} of 12 mo.`
All `$[DATA]` in channel spend table	`{META_SPEND}`, `{GOOGLE_SPEND}`, `{TOTAL_CHANNEL_SPEND}`
`H&H Chicago Trade Show`	`{FEATURED_EVENT}`
`★ April Feature:`	`★ {MONTH_LABEL} Feature:`
`Mothers, Makers & Memories`	`{NEXT_MONTH_THEME}`
Progress bar `width` inline styles	`{EMAIL_GOAL_PCT}%`, `{IG_GOAL_PCT}%`, `{YT_GOAL_PCT}%`
Goal label numbers	`{EMAIL_GOAL_GAP}`, `{IG_GOAL}`, `{YT_GOAL}`

Step 2: Verify no hardcoded April-specific values remain

grep -n "April\|183K\|122,832\|233K\|\$\[DATA\]\|H&amp;H" ~/ai-projects-local/mission-control/templates/marketing_recap_template.html | grep -v "style\|class\|font\|color"

Expected: 0 matches (or only in CSS comments, not content)

Task 3: Build `marketing_recap_generator.py`

Files:

Create: ~/ai-projects-local/mission-control/scripts/marketing_recap_generator.py

Step 1: Write the data fetchers

#!/usr/bin/env python3
"""
RBD Monthly Marketing Recap Generator

Usage:
    python marketing_recap_generator.py                    # Previous full month
    python marketing_recap_generator.py --month 2026-04   # Specific month
    python marketing_recap_generator.py --event "H&H Chicago" --next-theme "Summer Sewing"
    python marketing_recap_generator.py --dry-run          # Print data, skip PDF
"""

import argparse
import csv
import os
import subprocess
import sys
import tempfile
import yaml
from datetime import datetime, timedelta
from pathlib import Path

SCRIPT_DIR = Path(__file__).parent
CONFIG_PATH = SCRIPT_DIR.parent / "config" / "marketing-recap-config.yaml"
TEMPLATE_PATH = SCRIPT_DIR.parent / "templates" / "marketing_recap_template.html"
SECRETS_FILE = Path.home() / "ai-projects-local" / "shared-knowledge" / "secrets.env.enc"
KEY_FILE = Path.home() / ".secrets" / "master.key"
SOCIAL_CSV = Path.home() / "ai-projects" / "Riley-Blake-Designs" / "Marketing-Reports-2026" / "social-media-tracker.csv"


def load_config():
    with open(CONFIG_PATH) as f:
        return yaml.safe_load(f)


def get_month_range(year: int, month: int) -> tuple:
    """Return (since, until) date strings for the given month."""
    since = f"{year}-{month:02d}-01"
    if month == 12:
        until = f"{year+1}-01-01"
    else:
        until = f"{year}-{month+1:02d}-01"
    # until is exclusive for Meta API date range
    return since, until


def get_ytd_range(year: int, month: int) -> tuple:
    """Return (since, until) for Jan 1 through end of given month."""
    since = f"{year}-01-01"
    if month == 12:
        until = f"{year+1}-01-01"
    else:
        until = f"{year}-{month+1:02d}-01"
    return since, until


def decrypt_secrets() -> dict:
    """Decrypt and parse secrets.env.enc into a dict."""
    result = subprocess.run(
        ["openssl", "enc", "-d", "-aes-256-cbc", "-pbkdf2",
         "-in", str(SECRETS_FILE), "-pass", f"file:{KEY_FILE}"],
        capture_output=True, text=True
    )
    secrets = {}
    for line in result.stdout.splitlines():
        line = line.strip()
        if "=" in line and not line.startswith("#"):
            k, v = line.split("=", 1)
            secrets[k.strip()] = v.strip()
    return secrets


def fetch_meta_spend(secrets: dict, since: str, until: str) -> dict:
    """
    Pull monthly spend from all 3 Meta ad accounts.
    Returns {"fabric_outlet": X, "shiplap": X, "sundance": X, "total": X}
    """
    import sys
    sys.path.insert(0, str(Path.home() / "ai-projects-local" / "fb-ads-agent"))
    from agent.fb_client import FBClient

    stores = {
        "fabric_outlet": (secrets.get("FB_ACCESS_TOKEN"), secrets.get("FB_AD_ACCOUNT_ID")),
        "shiplap":       (secrets.get("SHIPLAP_FB_SYSTEM_USER_TOKEN"), secrets.get("SHIPLAP_FB_AD_ACCOUNT_ID")),
        "sundance":      (secrets.get("SUNDANCE_FB_ACCESS_TOKEN"), secrets.get("SUNDANCE_FB_AD_ACCOUNT_ID")),
    }

    result = {}
    total = 0.0
    for store, (token, account_id) in stores.items():
        if not token or not account_id:
            print(f"  WARNING: Missing token/account for {store}", file=sys.stderr)
            result[store] = 0.0
            continue
        client = FBClient(access_token=token, ad_account_id=account_id,
                          app_id=secrets.get("FB_APP_ID"), app_secret=secrets.get("FB_APP_SECRET"))
        try:
            insights = client.get_account_insights(
                fields=["spend"],
                params={"time_range": {"since": since, "until": until}, "level": "account"}
            )
            spend = float(insights[0].get("spend", 0)) if insights else 0.0
            result[store] = spend
            total += spend
            print(f"  Meta {store}: ${spend:,.2f}")
        except Exception as e:
            print(f"  ERROR fetching Meta {store}: {e}", file=sys.stderr)
            result[store] = 0.0

    result["total"] = total
    return result


def fetch_google_spend(secrets: dict, since: str, until: str) -> dict:
    """
    Pull monthly spend from all 4 Google Ads accounts.
    Returns {"fabric_outlet": X, "shiplap": X, "sundance": X, "mcc": X, "total": X}
    """
    sys.path.insert(0, str(Path.home() / "ai-projects-local" / "mission-control" / "scripts"))
    try:
        from google.ads.googleads.client import GoogleAdsClient
        from google.ads.googleads.errors import GoogleAdsException
    except ImportError:
        print("  WARNING: google-ads SDK not available, skipping Google Ads", file=sys.stderr)
        return {"total": 0.0}

    # Customer IDs for each store
    accounts = {
        "fabric_outlet": "4128721400",
        "shiplap":       "1491676209",
        "sundance":      "8958538976",
    }

    credentials_path = Path.home() / "ai-projects-local" / "mission-control" / "config" / "google-ads.yaml"
    if not credentials_path.exists():
        print("  WARNING: google-ads.yaml not found, skipping Google Ads", file=sys.stderr)
        return {"total": 0.0}

    try:
        client = GoogleAdsClient.load_from_storage(str(credentials_path))
    except Exception as e:
        print(f"  WARNING: Google Ads client failed to init: {e}", file=sys.stderr)
        return {"total": 0.0}

    result = {}
    total = 0.0
    since_gaql = since.replace("-", "")[:8]  # YYYYMMDD
    until_gaql = until.replace("-", "")[:8]

    for store, customer_id in accounts.items():
        try:
            ga_service = client.get_service("GoogleAdsService")
            query = f"""
                SELECT metrics.cost_micros
                FROM campaign
                WHERE segments.date BETWEEN '{since}' AND '{until}'
                  AND campaign.status != 'REMOVED'
            """
            stream = ga_service.search_stream(customer_id=customer_id, query=query)
            spend = sum(
                row.metrics.cost_micros
                for batch in stream
                for row in batch.results
            ) / 1_000_000
            result[store] = spend
            total += spend
            print(f"  Google {store}: ${spend:,.2f}")
        except Exception as e:
            print(f"  ERROR fetching Google {store}: {e}", file=sys.stderr)
            result[store] = 0.0

    result["total"] = total
    return result


def fetch_social_data(month_label: str) -> dict:
    """
    Read the most recent RBD row and Klaviyo rows from social-media-tracker.csv.
    Returns dict of social metrics.
    """
    rows = []
    with open(SOCIAL_CSV, newline="") as f:
        reader = csv.DictReader(f)
        rows = list(reader)

    # Get the most recent RBD row
    rbd_rows = [r for r in rows if r["Brand"] == "Riley Blake Designs"]
    rbd = rbd_rows[-1] if rbd_rows else {}

    # Get the most recent Klaviyo rows
    consumer_rows = [r for r in rows if "Consumers" in r["Brand"]]
    dealer_rows   = [r for r in rows if "Dealers"   in r["Brand"]]
    consumer_row  = consumer_rows[-1] if consumer_rows else {}
    dealer_row    = dealer_rows[-1]   if dealer_rows   else {}

    def parse_k(val):
        """Convert '183000' → '183K', '1234567' → '1.2M'"""
        try:
            n = int(val)
            if n >= 1_000_000:
                return f"{n/1_000_000:.1f}M"
            if n >= 1_000:
                return f"{n//1000}K"
            return str(n)
        except (ValueError, TypeError):
            return "–"

    def parse_email_count(notes: str) -> int:
        """Extract integer from 'Email subscribers: 127,218'"""
        try:
            return int(notes.split(":")[-1].replace(",", "").strip())
        except Exception:
            return 0

    consumers = parse_email_count(consumer_row.get("Notes", ""))
    yt_subs   = int(rbd.get("YouTube_Subs", 0) or 0)
    ig        = int(rbd.get("IG_Followers", 0) or 0)
    fb        = int(rbd.get("FB_Followers", 0) or 0)

    return {
        "youtube_subs":       parse_k(yt_subs),
        "youtube_subs_raw":   yt_subs,
        "instagram_followers": parse_k(ig),
        "instagram_raw":      ig,
        "facebook_followers": parse_k(fb),
        "email_consumers":    f"{consumers:,}",
        "email_consumers_raw": consumers,
    }


def calc_goal_pct(actual: float, goal: float) -> int:
    return min(100, int(actual / goal * 100)) if goal else 0


def format_spend(amount: float) -> str:
    return f"${amount:,.0f}"


def build_template_vars(year: int, month: int, meta: dict, google: dict,
                        social: dict, config: dict,
                        featured_event: str, next_theme: str) -> dict:
    month_dt     = datetime(year, month, 1)
    month_label  = month_dt.strftime("%B %Y")   # "April 2026"
    months_elapsed = month

    total_spend  = meta["total"] + google["total"]
    annual_budget = config["annual_budget_usd"]

    # YTD: fetch full-year-to-date across all sources (reuse same range function)
    # For template purposes use month proportion as proxy since we have monthly data
    ytd_pct = int((months_elapsed / 12) * 100)  # Simplified: budget pacing = months elapsed / 12

    goals = config["goals"]
    email_consumers_raw = social["email_consumers_raw"]
    ig_raw              = social["instagram_raw"]
    yt_raw              = social["youtube_subs_raw"]
    email_goal_gap      = max(0, goals["email_consumers"] - email_consumers_raw)
    ig_goal             = goals["instagram_followers"]
    yt_goal             = goals["youtube_subs"]

    return {
        "MONTH_LABEL":        month_label,
        "MONTHS_ELAPSED":     str(months_elapsed),
        "TOTAL_SPEND":        format_spend(total_spend),
        "META_SPEND":         format_spend(meta["total"]),
        "GOOGLE_SPEND":       format_spend(google["total"]),
        "TOTAL_CHANNEL_SPEND": format_spend(total_spend),
        "YOUTUBE_SUBS":       social["youtube_subs"],
        "YOUTUBE_DELTA":      f"Goal: {yt_goal//1000}K",
        "EMAIL_CONSUMERS":    social["email_consumers"],
        "EMAIL_DELTA":        "",
        "INSTAGRAM_FOLLOWERS": social["instagram_followers"],
        "FACEBOOK_FOLLOWERS": social["facebook_followers"],
        "YTD_PACING_PCT":     f"{ytd_pct}%",
        "FEATURED_EVENT":     featured_event or f"{month_label} Feature",
        "NEXT_MONTH_THEME":   next_theme or "Coming Next Month",
        "EMAIL_GOAL_PCT":     str(calc_goal_pct(email_consumers_raw, goals["email_consumers"])),
        "IG_GOAL_PCT":        str(calc_goal_pct(ig_raw, ig_goal)),
        "YT_GOAL_PCT":        str(calc_goal_pct(yt_raw, yt_goal)),
        "EMAIL_GOAL_GAP":     f"{email_goal_gap:,}",
        "IG_GOAL":            f"{ig_goal//1000}K",
        "YT_GOAL":            f"{yt_goal//1000}K",
        # Store breakdown for channel table
        "META_FO_SPEND":      format_spend(meta.get("fabric_outlet", 0)),
        "META_SHP_SPEND":     format_spend(meta.get("shiplap", 0)),
        "META_SUN_SPEND":     format_spend(meta.get("sundance", 0)),
        "GOOGLE_FO_SPEND":    format_spend(google.get("fabric_outlet", 0)),
        "GOOGLE_SHP_SPEND":   format_spend(google.get("shiplap", 0)),
        "GOOGLE_SUN_SPEND":   format_spend(google.get("sundance", 0)),
    }


def fill_template(template_path: Path, vars: dict) -> str:
    html = template_path.read_text()
    for key, value in vars.items():
        html = html.replace(f"{{{key}}}", str(value))
    return html


def html_to_pdf(html: str, output_path: Path) -> Path:
    with tempfile.NamedTemporaryFile(suffix=".html", delete=False, mode="w") as f:
        f.write(html)
        tmp_html = f.name
    try:
        subprocess.run(
            ["wkhtmltopdf", "--enable-local-file-access",
             "--page-size", "Letter", "--orientation", "Landscape",
             "--margin-top", "0", "--margin-right", "0",
             "--margin-bottom", "0", "--margin-left", "0",
             tmp_html, str(output_path)],
            check=True, capture_output=True
        )
    finally:
        os.unlink(tmp_html)
    return output_path


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--month", help="YYYY-MM (default: previous full month)")
    parser.add_argument("--event", default="", help="Featured event name")
    parser.add_argument("--next-theme", default="", help="Next month theme/campaign name")
    parser.add_argument("--dry-run", action="store_true", help="Print data, skip PDF")
    args = parser.parse_args()

    # Resolve month
    if args.month:
        year, month = map(int, args.month.split("-"))
    else:
        today = datetime.now()
        first_of_current = datetime(today.year, today.month, 1)
        prev = first_of_current - timedelta(days=1)
        year, month = prev.year, prev.month

    since, until = get_month_range(year, month)
    print(f"\nGenerating RBD Marketing Recap: {datetime(year, month, 1).strftime('%B %Y')}")
    print(f"Date range: {since} → {until}\n")

    config  = load_config()
    secrets = decrypt_secrets()

    print("Fetching Meta Ads spend...")
    meta = fetch_meta_spend(secrets, since, until)

    print("Fetching Google Ads spend...")
    google = fetch_google_spend(secrets, since, until)

    print("Reading social tracker CSV...")
    social = fetch_social_data(datetime(year, month, 1).strftime("%B %Y"))

    template_vars = build_template_vars(
        year, month, meta, google, social, config,
        args.event, args.next_theme
    )

    print("\nTemplate variables:")
    for k, v in template_vars.items():
        print(f"  {k}: {v}")

    if args.dry_run:
        print("\nDry run — skipping PDF generation.")
        return

    html = fill_template(TEMPLATE_PATH, template_vars)

    output_dir = Path(os.path.expanduser(config["output_dir"]))
    output_dir.mkdir(parents=True, exist_ok=True)
    month_slug = datetime(year, month, 1).strftime("%Y-%m")
    output_path = output_dir / f"RBD_Marketing_Recap_{month_slug}.pdf"

    print(f"\nGenerating PDF → {output_path}")
    html_to_pdf(html, output_path)
    print(f"Done: {output_path}")
    print(f"OUTPUT_PATH={output_path}")   # parsed by bot guide


if __name__ == "__main__":
    main()

Step 2: Dry-run test

cd ~/ai-projects-local/mission-control && \
python3 scripts/marketing_recap_generator.py --month 2026-04 --dry-run

Expected: prints Meta spend ~$X, Google spend ~$X, social data from CSV, template vars table. No errors.

Step 3: Full run with PDF output

python3 scripts/marketing_recap_generator.py --month 2026-04 \
  --event "H&H Chicago Trade Show" \
  --next-theme "Mothers, Makers & Memories"

Expected: PDF at ~/ai-projects/mission-control/reports/marketing-recaps/RBD_Marketing_Recap_2026-04.pdf

Step 4: Open PDF and verify it looks correct

open ~/ai-projects/mission-control/reports/marketing-recaps/RBD_Marketing_Recap_2026-04.pdf

Expected: One-pager renders with real data filled in, no {VARNAME} placeholders visible.

Task 4: Create Bot Guide

Files:

Create: ~/ai-projects-local/mission-control/scripts/bot-guides/marketing-recap.md

Step 1: Write the guide

# Marketing Recap Guide

## Trigger phrases
"monthly recap", "marketing recap", "marketing report", "send me the recap",
"generate the recap", "marketing one pager", "monthly marketing", "recap"

## Who can use this
All users (admin and readonly). Mary is the primary user.

## What this does
Generates the RBD monthly marketing one-pager as a PDF and emails it.
Covers: Meta Ads spend, Google Ads spend, social followers, email list size.

## How to run

### Default (previous full month)
```bash
python3 ~/ai-projects-local/mission-control/scripts/marketing_recap_generator.py

Specific month

python3 ~/ai-projects-local/mission-control/scripts/marketing_recap_generator.py --month 2026-04

With event + theme context (if user provides them)

python3 ~/ai-projects-local/mission-control/scripts/marketing_recap_generator.py \
  --month 2026-04 \
  --event "H&H Chicago Trade Show" \
  --next-theme "Mothers, Makers & Memories"

Output

The script prints a line like: OUTPUT_PATH=/Users/colegorringe/ai-projects/mission-control/reports/marketing-recaps/RBD_Marketing_Recap_2026-04.pdf

Use that path to email the file.

Email the PDF

python3 ~/ai-projects-local/mission-control/scripts/send_email.py \
  --to [user_email] \
  --subject "RBD Marketing Recap — April 2026" \
  --body "Your RBD monthly marketing recap is attached." \
  --attachment [OUTPUT_PATH]

Example Telegram reply

"Done — your April 2026 marketing recap is on its way to mary@alpineanalytica.com."

Notes

Generation takes ~30-60 seconds (API calls + wkhtmltopdf)
If Google Ads data is unavailable, it gracefully shows $0 for Google with a warning
PDF goes to ~/ai-projects/mission-control/reports/marketing-recaps/
If user asks for a specific month, pass --month YYYY-MM


**Step 2: Verify the guide appears in the bot's guide directory**
```bash
ls ~/ai-projects-local/mission-control/scripts/bot-guides/

Expected: marketing-recap.md listed alongside other guide files.

Task 5: Add Mary to Telegram Allowlist

Files:

Modify: ~/ai-projects-local/mission-control/scripts/telegram_watcher.py

Step 1: Mary messages the bot Have Mary open Telegram, find the bot, and send any message (e.g. "hi").

Step 2: Get her user ID from the log

tail -50 ~/cron-logs/telegram-watcher.log | grep -i "unknown\|not in ALLOWED\|user_id\|from"

Expected: a line showing Mary's numeric Telegram user ID.

Step 3: Add Mary to ALLOWED_USERS

In telegram_watcher.py, find:

ALLOWED_USERS = {
    5876831456: {"name": "Cole",  "role": "admin",    "email": "cole@alpineanalytica.com"},
    8652258902: {"name": "Caleb", "role": "readonly", "email": "caleb@alpineanalytica.com"},
    # Mary — add user ID once she messages the bot
    # 123456789: {"name": "Mary", "role": "readonly", "email": ""},

Replace with (using Mary's actual user ID):

ALLOWED_USERS = {
    5876831456: {"name": "Cole",  "role": "admin",    "email": "cole@alpineanalytica.com"},
    8652258902: {"name": "Caleb", "role": "readonly", "email": "caleb@alpineanalytica.com"},
    MARY_USER_ID: {"name": "Mary", "role": "readonly", "email": "mary@alpineanalytica.com"},

Step 4: Restart the Telegram watcher

pkill -f telegram_watcher.py
sleep 2
nohup python3 ~/ai-projects-local/mission-control/scripts/telegram_watcher.py >> ~/cron-logs/telegram-watcher.log 2>&1 &

Step 5: Verify Mary can now send a message and get a reply Have Mary send "hi" — she should get a response instead of being ignored.

Task 6: End-to-End Test

Step 1: Mary sends a recap request on Telegram "can you send me the monthly marketing recap"

Step 2: Verify the bot picks it up

tail -f ~/cron-logs/telegram-watcher.log

Expected: lines showing Processing: can you send me the monthly... then marketing-recap guide matched, then marketing_recap_generator.py running.

Step 3: Verify the PDF arrives in Mary's email Mary checks her inbox for an email with the PDF attachment.

Step 4: Verify the PDF content Open the attached PDF — all {VARNAME} placeholders replaced with real data, layout looks clean.

Execution Log

(update as tasks complete)

Known Gaps / Future Work

Campaign Highlights section is static in the template — future enhancement: pull top 3 Meta campaigns by ROAS and auto-populate
Best Performing Ads appendix (page 2) has ad images embedded in the HTML — these will remain as the April example until we build an ad image fetcher
YTD pacing is calculated as months_elapsed / 12 (time-based) rather than actual_YTD_spend / annual_budget — fix in v2 once we confirm Google Ads data is pulling correctly
Mary's Telegram user ID is unknown until she messages the bot — Task 5 is blocked on this

Blockers

None currently. Task 5 (adding Mary) requires her to message the bot first.

Source: ~/ai-projects/mission-control/plans/2026-05-13-marketing-recap-bot.md

RBD Marketing Recap Bot — Implementation Plan

Data Model

Files to Create / Modify

Task 1: Create Config YAML

Task 2: Templatize the HTML

Task 3: Build marketing_recap_generator.py

Task 4: Create Bot Guide

Specific month

With event + theme context (if user provides them)

Output

Email the PDF

Example Telegram reply

Notes

Task 5: Add Mary to Telegram Allowlist

Task 6: End-to-End Test

Execution Log

Known Gaps / Future Work

Blockers

Task 3: Build `marketing_recap_generator.py`