| name | sqr-pipeline |
| description | End-to-end search query report (SQR) negative-keyword pipeline — pull search terms across your MCC, prep classification batches, run 3 independent classification passes for consensus, (optionally) check geo conflicts, write reviewable agree tabs, then upload approved PHRASE negatives to shared lists with two-step mutation safety. Includes a remove branch to un-negate mistakes. Auto-invoke when the user says "run SQR pipeline", "classify search queries", "pull search terms", "upload negatives", "sqr uploader", "remove negative", or "un-negate keyword". |
| allowed-tools | ["Bash","Read","Write","Task"] |
SQR Pipeline — Negative Keyword Operator
One operator owns the search-query-report negative-keyword workflow end to end:
pull → prep → 3-run classify → (optional geo) → consensus → human review → upload,
with a maintenance branch to remove a negative that was added by mistake.
The classification step is LLM-in-the-loop — Claude reads each batch and
classifies it three independent times. That is the whole point: three independent
opinions per query, with consensus (3-of-3 unanimous / 2-of-3 majority) filtering
out one-off misclassifications before anything touches a live account.
This is a single generic vertical. There is no profile switching and no
account-specific branching — point it at your MCC, your Google Sheet, and
your competitor list (references/offbrand-keywords.txt) and it runs.
Triggers
run SQR pipeline / classify search queries — full forward run (enters at the right step)pull search terms / sqr pull — pull only (step 0), then stopupload negatives / sqr uploader — upload an already-reviewed sheet (step 6)remove negative / un-negate keyword — maintenance branch (step 7)
Entry is wherever the work is. A "pull" enters at step 0 and stops. "Upload
negatives" with a reviewed sheet enters at step 6. "Run SQR pipeline" with prep
already done enters at step 2. Read the relevant step below before running it.
Step chain
| Step | Tool | What it does | Type |
|---|
| 0 Pull | scripts/mcc_search_query_report.py | Pull last-30-day search terms across the MCC → the SQR tab. (Or use your own scheduled Google Ads Script.) | script |
| 1 Prep | scripts/sqr_prep.py | Build classification batches from the sheet; flag off-brand candidates via the keyword stub. | script |
| 2 Classify | references/classify-prompt.md | 3 independent classification passes over every batch (Claude Task agents). | LLM |
| 3 Geo (optional) | scripts/prep_geo_batches.py + references/geo-prompt.md | Re-check off-brand hits that contain a location you actively target — don't negate those. | script + LLM |
| 4 Consensus | scripts/sqr_compare.py (+ optional scripts/sqr_ngram_analysis.py) | Merge the 3 runs → 3-3 Agree / 2-3 Agree tabs; optional per-account n-grams. | script |
| 5 Review gate | — | STOP. Human marks Include? (col M). Loop back to step 2 on systematic misclassification. | human |
| 6 Upload | scripts/sqr_upload_negatives.py | MUTATION: dry-run preview → approval code → upload PHRASE negatives to shared lists. | script |
| 7 Remove | scripts/sqr_remove_negatives.py | MAINTENANCE: un-negate an incorrectly-negated keyword. | script |
All scripts run from your project root (where token.json and google-ads.yaml
live), e.g. python scripts/sqr_prep.py --sheet-id YOUR_SHEET_ID.
Step 0 — Pull
python scripts/mcc_search_query_report.py --sheet-id YOUR_SHEET_ID --dry-run
python scripts/mcc_search_query_report.py --sheet-id YOUR_SHEET_ID --clear
Filter with --labels "Search,Active" or --cids 1234567890,.... The MCC comes
from login_customer_id in google-ads.yaml (override with --mcc-id). Then
verify the input tab refreshed before prep: python scripts/sqr_prep.py --sheet-id YOUR_SHEET_ID --dry-run.
Step 1 — Prep
python scripts/sqr_prep.py --sheet-id YOUR_SHEET_ID
Reads the Have Cost tab (col I = Waiting), writes batches under
./data/sqr-pipeline/ plus manifest.json (status: prepared, num_batches).
Verify manifest.json status is prepared and the batch-file count matches
num_batches before classifying.
Step 2 — Classify (3 independent runs)
Spawn 3 Task agents (one per run, in parallel). Each agent classifies every
batch in ./data/sqr-pipeline/ob_batches/ and writes results to
./data/sqr-pipeline/run{R}/step1/.
Agent prompt template (fill in {RUN_NUMBER} and {TOTAL_BATCHES}):
You are processing SQR classification Run {RUN_NUMBER} of 3.
Classification prompt: read the full prompt from references/classify-prompt.md.
For each batch ob_001.json … ob_{TOTAL_BATCHES:03d}.json in
./data/sqr-pipeline/ob_batches/:
- Read the batch (it contains
queries, brand_names, off_brand_keywords).
- Classify each query per the prompt rules.
- Write a JSON array of
{"CID","Query","Category"} to
./data/sqr-pipeline/run{RUN_NUMBER}/step1/ob_{NNN}.json.
CRITICAL — YOU must be the classifier:
- Do NOT write a Python script, regex, or any deterministic code to classify.
- YOU read each batch and classify each query with your own judgment.
- The entire point of 3 independent runs is 3 independent LLM opinions. If you
codify rules into code, all 3 runs produce identical output and the consensus
mechanism is meaningless.
Categories must be lowercase: high intent, low intent, informational,
off-brand. Process ALL batches. If a batch errors, log it and continue.
Completeness check (mandatory before handoff): each run's step1/ must have
the same number of result files as num_batches. If a run stalled, re-spawn an
agent for ONLY its missing batch range — never restart the whole phase.
Step 3 — Geo conflict (optional)
Only if you ran prep with --geo-tab (so geo_targets.json is populated):
python scripts/prep_geo_batches.py
Then spawn 3 Task agents that read each run{R}/step2_batches/geo_NNN.json,
apply references/geo-prompt.md, and write verdicts to run{R}/step2/geo_NNN.json.
A geo FAIL means the query collides with a location you actively target, so it is
excluded from the negate list. Skip this step entirely for the core flow.
Step 4 — Consensus
python scripts/sqr_compare.py --sheet-id YOUR_SHEET_ID
python scripts/sqr_ngram_analysis.py --sheet-id YOUR_SHEET_ID
Writes 3-3 Agree (unanimous) and 2-3 Agree (majority) tabs with Include?
(col M) left empty for review. Report the row counts as the review handoff.
Step 5 — Review gate (STOP)
A human marks x in col M (Include?) on the agree tabs. The operator never
marks Include? itself. An empty Uploader tab does NOT mean failure — it means
review hasn't happened yet, or nothing was approved. Both are valid; wait. If the
human reports systematic misclassification, loop back to step 2 with their
corrections folded into the agent prompt.
Step 6 — Upload (MUTATION)
Two-step mutation flow — see the mutation-safety skill.
Never skip the dry-run. Never auto-generate or auto-approve a code.
python scripts/sqr_upload_negatives.py --sheet-id YOUR_SHEET_ID
python scripts/sqr_upload_negatives.py --sheet-id YOUR_SHEET_ID APPROVE-XXXXXXXX
Present the preview, WAIT for the human to provide the approval code, then run
step 2. The code is a hash of the pending work — if the sheet changed since the
preview, the code won't match and execution is refused. On any error or
unexpected state, STOP and report; do not retry into the API.
Step 7 — Remove (maintenance)
Un-negate a keyword added by mistake. Progressive discovery → preview → approve:
python scripts/sqr_remove_negatives.py --customer-id 1234567890
python scripts/sqr_remove_negatives.py --customer-id 1234567890 --list-name "Brand"
python scripts/sqr_remove_negatives.py --customer-id 1234567890 --list-name "Brand" --keyword "apartments near me"
python scripts/sqr_remove_negatives.py --customer-id 1234567890 --list-name "Brand" --keyword "apartments near me" APPROVE-XXXXXXXX
Same two-step safety as upload. Partial keyword matches can hit multiple criteria
— read the preview, don't assume one match.
Files in this skill
| File | Purpose |
|---|
SKILL.md | This file — orchestration for all steps |
README.md | Setup guide + prerequisites |
sheet-template.md | The Google Sheet tab/column spec (you build the sheet) |
scripts/mcc_search_query_report.py | Step 0 — pull search terms to the SQR tab |
scripts/sqr_prep.py | Step 1 — build classification batches |
scripts/prep_geo_batches.py | Step 3 (optional) — build geo batches |
scripts/sqr_compare.py | Step 4 — merge runs → agree tabs |
scripts/sqr_ngram_analysis.py | Step 4 (optional) — per-account n-grams |
scripts/sqr_upload_negatives.py | Step 6 — upload PHRASE negatives (two-step) |
scripts/sqr_remove_negatives.py | Step 7 — remove a negative (two-step) |
references/classify-prompt.md | Step 2 — generic classification prompt |
references/geo-prompt.md | Step 3 — generic geo conflict prompt |
references/offbrand-keywords.txt | Sample competitor stub — replace with your own |
Runtime data lives at ./data/sqr-pipeline/ (gitignored), never inside the skill.
Prerequisites
- Google Ads API —
google-ads.yaml at project root with login_customer_id
set to your MCC. See google-ads-api-setup.
- Google Sheets —
token.json at project root with the spreadsheets scope
(or a google-ads.yaml refresh token that includes that scope).
- A Google Sheet built to
sheet-template.md (Have Cost, SQR, agree tabs, Uploader).
- Your competitor list — replace
references/offbrand-keywords.txt with your own.
- Mutation safety — review
mutation-safety; steps 6 and 7 mutate live accounts.
pip install google-ads google-auth google-api-python-client pyyaml
What this skill deliberately does NOT do
- No auto-approval. Every mutation requires a human to read the preview and supply the approval code.
- No automatic negating without review. A human marks
Include? before anything uploads.
- No external classification API. Classification runs through Claude Task agents, not a paid LLM API.
- No profile/account switching. One MCC, one sheet, one competitor list per install.
