Track recurring People Also Ask questions with Python and SQLite

A docs team wants to know which questions keep appearing around “passkey setup.” The first check offers a couple of leads. A later check shows an empty People Also Ask list. If the collector stores only rows with a question, that empty check disappears from the report and the questions look more consistent than they were.

Store the search first. Then attach its questions. This gives you a denominator that survives empty lists and replayed responses.

Model one row per search, plus one row per question

The Serper.live response includes requestId, fetchedAt, and the effective query, country, interface language, and device. It exposes the questions as strings in peopleAlsoAsk[]. A successful response with an empty list still belongs in the observation table. Serper.live search response reference

For the question key, normalize Unicode, collapse whitespace, and use case folding. Keep display text alongside the key. Leave wording changes for an editor to review. “What happens if I lose my device?” and “How do I remove a passkey?” may belong to the same guide and still deserve two distinct rows.

Run the fixture

Save this as questions.py and run python3 questions.py with Python 3.11 or later. It uses the standard library. The example opens an in-memory database so each run starts with the same sample. Use a file path when collecting snapshots on a schedule.

import sqlite3
import unicodedata

db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE snapshot (
    request_id TEXT PRIMARY KEY,
    query TEXT NOT NULL,
    country TEXT NOT NULL,
    language TEXT NOT NULL,
    device TEXT NOT NULL,
    fetched_at TEXT NOT NULL
);
CREATE TABLE question (
    request_id TEXT NOT NULL REFERENCES snapshot(request_id),
    question_key TEXT NOT NULL,
    question_text TEXT NOT NULL,
    PRIMARY KEY (request_id, question_key)
);
""")


def record(payload):
    with db:
        inserted = db.execute("""
            INSERT INTO snapshot VALUES (?, ?, ?, ?, ?, ?)
            ON CONFLICT(request_id) DO NOTHING
        """, (
            payload["requestId"], payload["query"], payload["country"],
            payload["language"], payload["device"], payload["fetchedAt"],
        ))
        if inserted.rowcount == 0:
            return

        for question in payload["peopleAlsoAsk"]:
            text = " ".join(unicodedata.normalize("NFC", question).split())
            if text:
                db.execute("""
                    INSERT INTO question VALUES (?, ?, ?)
                    ON CONFLICT(request_id, question_key) DO NOTHING
                """, (payload["requestId"], text.casefold(), text))


def snapshot(day, questions):
    return {
        "requestId": f"fixture-{day}",
        "query": "passkey setup",
        "country": "US",
        "language": "en",
        "device": "mobile",
        "fetchedAt": f"2026-10-{day:02d}T09:00:00Z",
        "peopleAlsoAsk": questions,
    }


first = snapshot(5, [
    "Can I use a passkey on two devices?",
    "CAN I USE A PASSKEY ON TWO DEVICES?",
    "How do I remove a passkey?",
])
record(first)
record(first)  # Simulate ingesting the same API response again.
record(snapshot(6, [
    "Can I use a passkey on two devices?",
    "What happens if I lose my device?",
]))
record(snapshot(7, []))

rows = db.execute("""
    WITH cohort AS (
        SELECT request_id
        FROM snapshot
        WHERE query = ? AND country = ? AND language = ? AND device = ?
          AND julianday(fetched_at) >= julianday(?)
          AND julianday(fetched_at) < julianday(?)
    )
    SELECT MIN(q.question_text), COUNT(*) AS appearances,
           (SELECT COUNT(*) FROM cohort) AS checks
    FROM cohort JOIN question AS q USING (request_id)
    GROUP BY q.question_key
    ORDER BY appearances DESC, q.question_key
""", ("passkey setup", "US", "en", "mobile", "2026-10-05", "2026-10-08"))

for text, appearances, checks in rows:
    print(f"{appearances}/{checks}  {text}")

2/3  Can I use a passkey on two devices?
1/3  How do I remove a passkey?
1/3  What happens if I lose my device?

The two-device question counts once per observation, and the empty check is the third in the denominator. Keep collection depth and cadence fixed within a cohort. If your collector varies depth, persist the requested limit and include it in the cohort filter. Record transport or API failures as job errors and keep them out of the successful-check denominator.

Turn the report into a docs task

“Appeared in two of three checks” is a signal about recurrence under the sampled settings. To decide what to write, look for the corresponding question in support tickets or docs search logs. If users keep asking about a second device and your setup guide leaves that step implicit, a short section in the guide has a clear job. Record the source query, dates, and question with the ticket so the editor can revisit the evidence.

This tutorial was drafted with AI assistance. The SQLite example runs on fictional SERP snapshots.