Weekly SEO Dashboard with Claude Code

import requests, os, sqlite3, json
from datetime import date

DB_PATH = "seo_tracker.db"
H = {"x-api-key": os.environ["SCAVIO_API_KEY"]}

def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.execute("""CREATE TABLE IF NOT EXISTS rankings (
        date TEXT, keyword TEXT, position INTEGER, ai_cited INTEGER,
        top_url TEXT, PRIMARY KEY (date, keyword))""")
    conn.commit()
    return conn

def collect_rankings(keywords: list, domain: str):
    conn = init_db()
    today = date.today().isoformat()
    for kw in keywords:
        resp = requests.post("https://api.scavio.dev/api/v1/search",
            headers=H,
            json={"query": kw, "include_ai_overview": True})
        data = resp.json()
        pos = None
        url = None
        for i, r in enumerate(data.get("organic_results", [])):
            if domain in r.get("link", ""):
                pos = i + 1
                url = r.get("link")
                break
        ai_sources = data.get("ai_overview", {}).get("sources", [])
        ai_cited = 1 if any(domain in s.get("link", "") for s in ai_sources) else 0
        conn.execute(
            "INSERT OR REPLACE INTO rankings VALUES (?, ?, ?, ?, ?)",
            (today, kw, pos, ai_cited, url))
    conn.commit()
    conn.close()

keywords = ["your keyword 1", "your keyword 2"]
collect_rankings(keywords, "yoursite.com")

from datetime import timedelta

def weekly_report(domain: str):
    conn = sqlite3.connect(DB_PATH)
    today = date.today()
    week_ago = (today - timedelta(days=7)).isoformat()
    today_str = today.isoformat()

    current = dict(conn.execute(
        "SELECT keyword, position FROM rankings WHERE date = ?",
        (today_str,)).fetchall())
    previous = dict(conn.execute(
        "SELECT keyword, position FROM rankings WHERE date = ?",
        (week_ago,)).fetchall())
    ai_citations = dict(conn.execute(
        "SELECT keyword, ai_cited FROM rankings WHERE date = ?",
        (today_str,)).fetchall())

    report = []
    report.append(f"SEO Report: {week_ago} to {today_str}")
    report.append(f"Domain: {domain}")
    report.append("-" * 60)

    improved = []
    declined = []
    for kw, pos in current.items():
        prev_pos = previous.get(kw)
        ai = "AI cited" if ai_citations.get(kw) else ""
        if prev_pos and pos:
            change = prev_pos - pos
            if change > 0:
                improved.append(f"  +{change} {kw}: #{pos} {ai}")
            elif change < 0:
                declined.append(f"  {change} {kw}: #{pos} {ai}")
        elif pos:
            report.append(f"  NEW {kw}: #{pos} {ai}")

    if improved:
        report.append("\nImproved:")
        report.extend(improved)
    if declined:
        report.append("\nDeclined:")
        report.extend(declined)

    conn.close()
    return "\n".join(report)

print(weekly_report("yoursite.com"))

def competitor_snapshot(keywords: list, competitors: list):
    """Check where competitors rank for your target keywords."""
    snapshot = {}
    for kw in keywords:
        resp = requests.post("https://api.scavio.dev/api/v1/search",
            headers=H, json={"query": kw})
        results = resp.json().get("organic_results", [])
        for comp in competitors:
            for i, r in enumerate(results):
                if comp in r.get("link", ""):
                    snapshot.setdefault(comp, {})[kw] = i + 1
                    break
    return snapshot

# Track 3 competitors x 20 keywords = 20 API calls = $0.10
competitors_data = competitor_snapshot(keywords, ["competitor1.com", "competitor2.com"])