--- name: gsc-pull description: Pull and analyze Google Search Console data via the GSC API. Produces: weekly query × page performance with WoW/MoM/YoY deltas, top movers report (winners + losers ranked by absolute click change), click-loss attribution (lost rank? lost impressions? lost CTR?), query-page mismatch detection (page ranking for the wrong query), branded-vs-nonbranded split, position-bucket analysis (top 3, 4-10, 11-20, 21+), country / device / search-type segmentation, and the URL Inspection findings for sample URLs (indexed? canonical correct? mobile-friendly? Core Web Vitals?). All output is YOUR data in YOUR DB — no vendor lock-in. Matches the GSC integration that Visibly AI / Semrush / Ahrefs gate behind monthly subscriptions. TRIGGER on "GSC", "Google Search Console", "Search Console API", "rank tracking", "SEO position changes", "click loss", "impression loss", "top movers", "indexing report", "query analysis". version: "1.0.0" category: analysis platforms: - CLAUDE_CODE --- # GSC Pull & Analysis Pipeline You generate a Google Search Console data pipeline + analyzer. The platform value of Semrush/Ahrefs/Visibly is mostly "we wrap the GSC API for you." You can do it better by giving the user the data + the queries + the analysis in their own stack. ============================================================ === PRE-FLIGHT === ============================================================ - [ ] **GSC property** verified and owned. Domain property preferred over URL prefix. - [ ] **GSC API access**: enabled via Google Cloud Console (Webmasters API). Service account with owner-level access to the property. - [ ] **Storage target**: SQLite (solo), Postgres (team), BigQuery (enterprise / longitudinal analysis past 16-month GSC retention). - [ ] **Time range**: GSC API returns 16-month rolling window. For longitudinal trends, you must persist locally — start the pipeline NOW even if you don't analyze yet. - [ ] **Brand list**: list of branded terms to split branded vs nonbranded traffic. Recovery: - If service account isn't set up: generate the gcloud + IAM commands; the user runs them once. - If property is URL prefix only: warn user; data will be incomplete vs domain property. ============================================================ === PHASE 1: API CLIENT + AUTH === ============================================================ Generate `gsc_client.py` (Python preferred — Google's `google-api-python-client` is mature): ```python from google.oauth2 import service_account from googleapiclient.discovery import build class GSCClient: SCOPES = ["https://www.googleapis.com/auth/webmasters.readonly"] def __init__(self, service_account_path: str, site_url: str): creds = service_account.Credentials.from_service_account_file( service_account_path, scopes=self.SCOPES ) self.service = build("searchconsole", "v1", credentials=creds) self.site = site_url # "sc-domain:example.com" or "https://example.com/" def search_analytics(self, start, end, dimensions, filters=None, row_limit=25000): """ dimensions: ["date", "query", "page", "country", "device", "searchAppearance"] Returns up to 25k rows per call. Use date partitioning for full coverage. """ body = { "startDate": start.isoformat(), "endDate": end.isoformat(), "dimensions": dimensions, "rowLimit": row_limit, "dataState": "final", # vs "all" which includes fresh (estimated) data } if filters: body["dimensionFilterGroups"] = filters # paginate via startRow up to 50k total per (start, end) range ... def inspect_url(self, inspection_url: str, language_code="en-US"): """URL Inspection API — 2k requests per day per site. Use sparingly.""" return self.service.urlInspection().index().inspect(body={ "inspectionUrl": inspection_url, "siteUrl": self.site, "languageCode": language_code, }).execute() ``` VALIDATION: Smoke test fetches recent 7-day query data. URL inspection returns IndexStatusResult for a known indexed page. ============================================================ === PHASE 2: DATA SCHEMA === ============================================================ ```sql CREATE TABLE gsc_daily ( date DATE NOT NULL, query TEXT, page TEXT, country TEXT, device TEXT, search_appearance TEXT, impressions INTEGER, clicks INTEGER, position REAL, ctr REAL GENERATED ALWAYS AS (CASE WHEN impressions > 0 THEN clicks * 1.0 / impressions ELSE 0 END), PRIMARY KEY (date, query, page, country, device, search_appearance) ); CREATE INDEX idx_gsc_query ON gsc_daily(query); CREATE INDEX idx_gsc_page ON gsc_daily(page); CREATE INDEX idx_gsc_date ON gsc_daily(date); CREATE TABLE gsc_url_inspection ( url TEXT PRIMARY KEY, last_inspected DATETIME, index_status TEXT, -- "PASS" / "FAIL" / "NEUTRAL" coverage_state TEXT, google_canonical TEXT, user_canonical TEXT, mobile_usable BOOLEAN, rich_results_eligible BOOLEAN, last_crawl DATETIME, crawled_as TEXT, -- DESKTOP / MOBILE page_fetch_state TEXT, raw_response JSON ); ``` VALIDATION: 16-month backfill loads without primary-key conflicts. Storage size estimate matches expectation (~10MB per 1k unique queries per month). ============================================================ === PHASE 3: WEEKLY PULL JOB === ============================================================ Schedule (cron or scheduled skill): ``` daily 02:00 → pull yesterday's data, idempotent upsert weekly Mon 03:00 → reconcile last 7 days, compute deltas monthly 1st 04:00 → 16-month backfill check, fill gaps ``` Per pull: 1. Fetch `[date, query, page, country, device]` daily. 2. Fetch `[date, query]` and `[date, page]` separately (deduplicated rollups, smaller payload). 3. Sample 50 URLs/day for URL Inspection (respect 2k/day cap). VALIDATION: Daily cron runs without manual intervention for 30 days. No duplicate-key errors. ============================================================ === PHASE 4: ANALYSIS — TOP MOVERS === ============================================================ Generate `top_movers.py`: For each (query, page) pair appearing in either of two windows (current week vs comparison week), compute: ```python @dataclass class Mover: query: str page: str clicks_current: int clicks_prior: int clicks_delta: int # current - prior clicks_pct_change: float # (current - prior) / prior impressions_delta: int position_delta: float # negative = position improved ctr_delta: float is_winner: bool # clicks_delta > 0 AND magnitude meaningful ``` **Winners**: top 25 by `clicks_delta` (filter `impressions_prior > 100` to filter noise). **Losers**: bottom 25 by `clicks_delta` (same filter). **Click-loss attribution**: for each loser, classify: - `RANK_LOSS`: position_delta > 1.0 and clicks_delta < 0 - `IMPRESSION_LOSS`: position stable, impressions down (Google reduced surfacing) - `CTR_LOSS`: position stable, impressions stable, CTR down (SERP feature ate clicks) - `SEASONAL`: matches a YoY pattern at the same query VALIDATION: Movers report identifies a known win/loss from a fixture. ============================================================ === PHASE 5: ANALYSIS — QUERY-PAGE MISMATCH === ============================================================ A query-page mismatch happens when: - A query ranks via the WRONG page on your site (target page is buried, intent page underperforms). - Multiple pages compete for the same query (= cannibalization — chain to `/content-cannibalization`). Detect: for each query with > 100 impressions in the window, list all (page, impressions, clicks, avg position). Flag where the top-impression page differs from the page you intended to rank. Output `query_page_mismatch.csv` with recommended action (consolidate, canonicalize, redirect, differentiate, or update internal links). VALIDATION: Mismatches show concrete page paths + actionable recommendation. ============================================================ === PHASE 6: BRANDED-VS-NONBRANDED SPLIT === ============================================================ For each query, classify branded or nonbranded based on the brand alias list: ```python def classify(query: str, brand_aliases: list[str]) -> str: q = query.lower() return "branded" if any(a.lower() in q for a in brand_aliases) else "nonbranded" ``` Compute weekly totals: `branded_clicks`, `nonbranded_clicks`, `branded_share`. Track trend over time. Healthy growth profile: nonbranded growing faster than branded (you're acquiring new users, not just being searched for by existing ones). VALIDATION: Branded/nonbranded sums equal total clicks. Trend chart renders. ============================================================ === PHASE 7: REPORTS === ============================================================ ``` gsc-pull/ ├── README.md ├── data/ │ └── gsc.db # SQLite (or pointer to Postgres) ├── reports/ │ ├── weekly_summary.md │ ├── top_movers.csv # winners + losers │ ├── click_loss_attribution.md │ ├── query_page_mismatch.csv │ ├── branded_vs_nonbranded.csv │ ├── position_bucket_analysis.csv # % of clicks from top 3 / 4-10 / 11-20 / 21+ │ └── url_inspection_findings.md └── cron/ └── daily_pull.sh ``` Weekly summary structure: ```markdown # GSC Weekly — {site} — {week} ## TL;DR - Total clicks: X (+/- Y vs prior week, +/- Z YoY) - Total impressions: X - Avg position: X - Branded share: X% - Top winner: {query} — {page} (+N clicks) - Top loser: {query} — {page} (-N clicks) ## Top 5 Winners {table} ## Top 5 Losers + Attribution {table with rank-loss / impression-loss / CTR-loss labels} ## Query-Page Mismatches {N detected — see csv} ## Recommended Actions {ordered by impact} ``` VALIDATION: Summary fits one screen. Recommended actions reference specific queries + pages. ============================================================ === SELF-REVIEW === ============================================================ - **Complete**: API client + schema + cron + 4 analyses + reports? - **Robust**: Handles 2k/day URL inspection cap? Survives API rate limits? - **Clean**: SQLite/Postgres choice transparent? CSVs Excel-friendly? - **GSC-credible**: Would an SEO consultant who lives in GSC daily accept the analyses? Common gap: forgetting `dataState=final` vs `all` and getting estimated data that shifts next day. Confirm which is used in each query. ============================================================ === LEARNINGS CAPTURE === ============================================================ `~/.claude/skills/gsc-pull/LEARNINGS.md`. ============================================================ === STRICT RULES === ============================================================ - Never query `dataState="all"` and then declare the data stable. Use `final` for analytical work. - Never hit URL Inspection > 2,000/day per property — it's a hard cap. - Never lose history. Start persisting NOW even if the user isn't analyzing yet. - Never analyze without WoW/MoM/YoY context. Absolute numbers without comparison are vanity. - Always classify click loss into the four root-cause buckets. "Clicks down" without attribution is useless.