SEO Keyword Gap Detection Workflow

import sqlite3
import requests
import csv
from datetime import date
import time

DB_PATH = "keyword_gaps.db"
SCRAVIO_KEY = "YOUR_API_KEY"
MY_DOMAIN = "scavio.com"
COMPETITOR_DOMAINS = ["serpapi.com", "serper.dev", "brightdata.com"]
KEYWORDS = [
    "search api for ai agents",
    "tiktok data api",
    "amazon product search api",
    "serp api python",
]

def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS serp_snapshots (
            keyword TEXT, domain TEXT, position INTEGER, date TEXT
        );
    """)
    conn.commit()
    return conn

def fetch_serp(keyword: str) -> list:
    resp = requests.post(
        "https://api.scavio.dev/api/v1/search",
        headers={"x-api-key": SCRAVIO_KEY},
        json={"query": keyword, "platform": "google", "num": 10}
    )
    resp.raise_for_status()
    return resp.json().get("results", [])

def run():
    conn = init_db()
    today = date.today().isoformat()
    gaps = []
    for kw in KEYWORDS:
        results = fetch_serp(kw)
        domain_positions = {}
        for i, r in enumerate(results[:10], start=1):
            url = r.get("url", "")
            for domain in [MY_DOMAIN] + COMPETITOR_DOMAINS:
                if domain in url:
                    if domain not in domain_positions:
                        domain_positions[domain] = i
            conn.execute("INSERT INTO serp_snapshots VALUES (?,?,?,?)",
                         (kw, r.get("url"), i, today))
        my_pos = domain_positions.get(MY_DOMAIN)
        competing = {d: p for d, p in domain_positions.items() if d != MY_DOMAIN}
        if competing and not my_pos:
            gap_score = sum(11 - p for p in competing.values())
            gaps.append({
                "keyword": kw,
                "your_position": "not ranking",
                "competitors": "; ".join(f"{d}@{p}" for d, p in competing.items()),
                "gap_score": gap_score
            })
        conn.commit()
        time.sleep(0.3)
    gaps.sort(key=lambda x: x["gap_score"], reverse=True)
    with open(f"keyword_gaps_{today}.csv", "w", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=["keyword", "your_position", "competitors", "gap_score"])
        writer.writeheader()
        writer.writerows(gaps)
    print(f"{len(gaps)} gap keywords found. Report: keyword_gaps_{today}.csv")

if __name__ == "__main__":
    run()

const Database = require('better-sqlite3');
const fetch = require('node-fetch');
const fs = require('fs');
const { stringify } = require('csv-stringify/sync');

const DB_PATH = 'keyword_gaps.db';
const SCRAVIO_KEY = 'YOUR_API_KEY';
const MY_DOMAIN = 'scavio.com';
const COMPETITOR_DOMAINS = ['serpapi.com', 'serper.dev', 'brightdata.com'];
const KEYWORDS = ['search api for ai agents', 'tiktok data api', 'amazon product search api'];

const db = new Database(DB_PATH);
db.exec('CREATE TABLE IF NOT EXISTS serp_snapshots (keyword TEXT, domain TEXT, position INTEGER, date TEXT);');

async function fetchSerp(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, platform: 'google', num: 10 })
  });
  return (await res.json()).results || [];
}

async function run() {
  const today = new Date().toISOString().slice(0, 10);
  const gaps = [];
  for (const kw of KEYWORDS) {
    const results = await fetchSerp(kw);
    const domainPos = {};
    results.slice(0, 10).forEach((r, i) => {
      const url = r.url || '';
      for (const d of [MY_DOMAIN, ...COMPETITOR_DOMAINS]) {
        if (url.includes(d) && !domainPos[d]) domainPos[d] = i + 1;
      }
    });
    const myPos = domainPos[MY_DOMAIN];
    const competing = Object.fromEntries(Object.entries(domainPos).filter(([d]) => d !== MY_DOMAIN));
    if (Object.keys(competing).length && !myPos) {
      const gapScore = Object.values(competing).reduce((s, p) => s + (11 - p), 0);
      gaps.push({ keyword: kw, your_position: 'not ranking', competitors: Object.entries(competing).map(([d,p]) => `${d}@${p}`).join('; '), gap_score: gapScore });
    }
    await new Promise(r => setTimeout(r, 300));
  }
  gaps.sort((a, b) => b.gap_score - a.gap_score);
  fs.writeFileSync(`keyword_gaps_${today}.csv`, stringify(gaps, { header: true }));
  console.log(`${gaps.length} gaps found.`);
}

run().catch(console.error);