Daily AI Overview Position Tracker

import sqlite3
import requests
import smtplib
from datetime import date, timedelta
from email.message import EmailMessage
import time

DB_PATH = "ai_overview_tracker.db"
SCRAVIO_KEY = "YOUR_API_KEY"
MONITORED_DOMAIN = "scavio.com"
ALERT_EMAIL = "[email protected]"

def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS keywords (id INTEGER PRIMARY KEY, keyword TEXT UNIQUE);
        CREATE TABLE IF NOT EXISTS daily_results (
            keyword TEXT, date TEXT, is_cited INTEGER,
            ai_overview_present INTEGER, sources TEXT,
            PRIMARY KEY (keyword, date)
        );
        CREATE TABLE IF NOT EXISTS daily_scores (
            date TEXT PRIMARY KEY, score REAL, cited INTEGER, total_with_overview INTEGER
        );
    """)
    conn.commit()
    return conn

def check_keyword(keyword: str, domain: str) -> dict:
    resp = requests.post(
        "https://api.scavio.dev/api/v1/search",
        headers={"x-api-key": SCRAVIO_KEY},
        json={"query": keyword, "include_ai_overview": True}
    )
    resp.raise_for_status()
    data = resp.json()
    ao = data.get("ai_overview", {})
    sources = ao.get("sources", [])
    source_urls = [s.get("url", "") for s in sources]
    is_cited = any(domain in url for url in source_urls)
    return {
        "ai_overview_present": bool(ao),
        "is_cited": is_cited,
        "sources": ",".join(source_urls)
    }

def run():
    conn = init_db()
    today = date.today().isoformat()
    yesterday = (date.today() - timedelta(days=1)).isoformat()
    keywords = [r[0] for r in conn.execute("SELECT keyword FROM keywords").fetchall()]
    cited = 0
    total_with_ao = 0
    changes = []
    for kw in keywords:
        result = check_keyword(kw, MONITORED_DOMAIN)
        conn.execute(
            "INSERT OR REPLACE INTO daily_results VALUES (?,?,?,?,?)",
            (kw, today, int(result["is_cited"]), int(result["ai_overview_present"]), result["sources"])
        )
        if result["ai_overview_present"]:
            total_with_ao += 1
            if result["is_cited"]:
                cited += 1
        yesterday_row = conn.execute(
            "SELECT is_cited FROM daily_results WHERE keyword=? AND date=?",
            (kw, yesterday)
        ).fetchone()
        if yesterday_row:
            prev_cited = bool(yesterday_row[0])
            if prev_cited != result["is_cited"]:
                direction = "gained" if result["is_cited"] else "lost"
                changes.append(f"{kw}: {direction} AI Overview citation")
        time.sleep(0.1)  # respect rate limits
    score = round(cited / total_with_ao * 100, 1) if total_with_ao else 0
    conn.execute("INSERT OR REPLACE INTO daily_scores VALUES (?,?,?,?)", (today, score, cited, total_with_ao))
    conn.commit()
    if changes:
        print("Citation changes:", changes)
        # send email alert

if __name__ == "__main__":
    run()

const Database = require('better-sqlite3');
const fetch = require('node-fetch');

const DB_PATH = 'ai_overview_tracker.db';
const SCRAVIO_KEY = 'YOUR_API_KEY';
const MONITORED_DOMAIN = 'scavio.com';

const db = new Database(DB_PATH);
db.exec(`
  CREATE TABLE IF NOT EXISTS keywords (keyword TEXT UNIQUE);
  CREATE TABLE IF NOT EXISTS daily_results (
    keyword TEXT, date TEXT, is_cited INTEGER, ai_overview_present INTEGER, sources TEXT,
    PRIMARY KEY (keyword, date)
  );
  CREATE TABLE IF NOT EXISTS daily_scores (date TEXT PRIMARY KEY, score REAL, cited INTEGER, total INTEGER);
`);

async function checkKeyword(keyword) {
  const res = await fetch('https://api.scavio.dev/api/v1/search', {
    method: 'POST',
    headers: { 'x-api-key': SCRAVIO_KEY, 'Content-Type': 'application/json' },
    body: JSON.stringify({ query: keyword, include_ai_overview: true })
  });
  const data = await res.json();
  const ao = data.ai_overview || {};
  const sources = (ao.sources || []).map(s => s.url || '');
  return {
    ai_overview_present: Object.keys(ao).length > 0,
    is_cited: sources.some(u => u.includes(MONITORED_DOMAIN)),
    sources: sources.join(',')
  };
}

async function run() {
  const today = new Date().toISOString().slice(0, 10);
  const yesterday = new Date(Date.now() - 86400000).toISOString().slice(0, 10);
  const keywords = db.prepare('SELECT keyword FROM keywords').all().map(r => r.keyword);
  let cited = 0, totalWithAO = 0;
  const changes = [];
  for (const kw of keywords) {
    const result = await checkKeyword(kw);
    db.prepare('INSERT OR REPLACE INTO daily_results VALUES (?,?,?,?,?)').run(
      kw, today, result.is_cited ? 1 : 0, result.ai_overview_present ? 1 : 0, result.sources
    );
    if (result.ai_overview_present) { totalWithAO++; if (result.is_cited) cited++; }
    const prev = db.prepare('SELECT is_cited FROM daily_results WHERE keyword=? AND date=?').get(kw, yesterday);
    if (prev && Boolean(prev.is_cited) !== result.is_cited)
      changes.push(`${kw}: ${result.is_cited ? 'gained' : 'lost'} citation`);
    await new Promise(r => setTimeout(r, 100));
  }
  const score = totalWithAO ? Math.round(cited / totalWithAO * 1000) / 10 : 0;
  db.prepare('INSERT OR REPLACE INTO daily_scores VALUES (?,?,?,?)').run(today, score, cited, totalWithAO);
  if (changes.length) console.log('Changes:', changes);
}

run().catch(console.error);