Looker Studio SEO analysis system on top of GSC

 

Great - let’s build this together step-by-step. Follow the sequence exactly once, and you’ll have a working Looker Studio SEO analysis system on top of GSC.

You already have:

  • Template spec: looker_studio_seo_template.json
  • Optional intent map: intent_mapping.csv

Step 1 - Create a new report & connect GSC

  1. Open Looker Studio → Blank report.
  2. Add data → choose Google Search Console → pick your site.
  3. Select the table that includes Page and Query (URL × Query level).
    You need these fields:     Date, Query, Page, Impressions, Clicks, CTR, Position.

Tip: Name this data source GSC: Site — URL×Query so it’s easy to find later.

Step 2 - Add the calculated fields (copy–paste)

In Resource → Manage added data sources → Edit your GSC source → Add a Field for each:

A) Intent (Regex) — DIMENSION

CASE

  WHEN REGEXP_MATCH(Query, '(?i)(what|how|why|tips|guide|best|meaning|examples|tutorial)') THEN 'Informational'

  WHEN REGEXP_MATCH(Query, '(?i)(vs|versus|difference|compare|review|comparison)') THEN 'Comparative'

  WHEN REGEXP_MATCH(Query, '(?i)(buy|price|near me|hire|get|book|download|quote|cost)') THEN 'Transactional'

  WHEN REGEXP_MATCH(Query, '(?i)(login|contact|apply|signup|sign up|about|hours|phone)') THEN 'Navigational'

  ELSE 'Other'

END

B) Word Count — METRIC

LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1

C) Keyword Length Bucket — DIMENSION

CASE

  WHEN (LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1) <= 2 THEN 'Short (1-2)'

  WHEN (LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1) <= 4 THEN 'Mid (3-4)'

  ELSE 'Long (5+)'

END

D) URL Cluster — DIMENSION

CASE

  WHEN REGEXP_MATCH(Page, '(^|/)blog(/|$)') THEN 'Blog'

  WHEN REGEXP_MATCH(Page, '(^|/)services?(/|$)') THEN 'Services'

  WHEN REGEXP_MATCH(Page, '(^|/)locations?(/|$)') THEN 'Location'

  WHEN REGEXP_MATCH(Page, '(^|/)products?(/|$)') THEN 'Product'

  ELSE 'Other'

END

E) Cannibalized (Pages per Query) — METRIC

COUNT_DISTINCT(Page)

Use this when the dimension = Query. Values > 1 indicate potential cannibalization.

F) CTR (safer recompute) — METRIC

SAFE_DIVIDE(Clicks, Impressions)

Click Save.

Step 3 - Build Page 1: Overview

  1. Add Scorecards (Clicks, Impressions, CTR, Position).
    • In Date range: Last 28 days.
    • Toggle Compare to previous period.
  2. Add a Time Series:
    • Dimension: Date
    • Metrics: Clicks, Impressions (enable Dual axis).
  3. Add a Bar Chart:
    • Dimension: Intent (Regex)
    • Metrics: Clicks, Impressions, CTR
    • Sort by Clicks desc.

Step 4 - Page 2: Search Intent deep-dive

  1. Pie/Donut: Dimension Intent (Regex), Metric Impressions.
  2. Table:
    • Dimensions: Query, Intent (Regex), Keyword Length Bucket
    • Metrics: Clicks, Impressions, CTR, Position
    • Add report-level filter control for Intent (Regex) to slice quickly.
  3. Bubble chart (quality at a glance):
    • Dimension: Intent (Regex)
    • X = CTR, Y = Position (note: lower is better; invert axis in style if you want)
    • Size = Impressions, Color = Intent (Regex).

Step 5 - Page 3: Cannibalization map

Option A (simplest):

  1. Table
    • Dimension: Query
    • Metrics: Cannibalized (Pages per Query), Clicks, Impressions
    • Conditional formatting: highlight when Cannibalized (Pages per Query) > 1.

Option B (visual cross-check):

  1. Pivot Table
    • Rows: Query
    • Columns: Page
    • Metric: Impressions
    • Look for rows where many columns have values → overlapping URLs per query.

Step 6 - Page 4: Ranking dynamics & anomalies

  1. Time Series
    • Dimension: Date
    • Metric: Position
    • Turn on Compare to previous period (see shifts visually).
  2. Table (Anomaly monitor)
    • Dimensions: Query, Page
    • Metrics: Impressions, Clicks, CTR, Position
    • Add a custom field at chart level (or reuse the one below if added globally):

Impressions Anomaly Flag — DIMENSION

CASE

  WHEN Impressions > (AVG(Impressions) * 1.5) THEN '↑ Sudden Spike'

  WHEN Impressions < (AVG(Impressions) * 0.5) THEN '↓ Drop Detected'

  ELSE 'Normal'

END

    • Add conditional formatting: green for “↑ Sudden Spike”, red for “↓ Drop Detected”.

Note: Anomaly logic works best with granularity = Day and at Query or Page grouping. Feel free to adjust the 1.5× / 0.5× thresholds for your traffic levels.

Step 7 - Page 5: URL cluster analysis

  1. Treemap
    • Dimension: URL Cluster
    • Metrics: Impressions, Clicks.
  2. Bubble chart
    • Dimension: URL Cluster
    • X = CTR, Y = Position, Size = Impressions.
  3. Table (drill-down)
    • Dimensions: URL Cluster, Page
    • Metrics: Clicks, Impressions, CTR, Position
    • Sort by Clicks desc.

Step 8 - (Optional) Use the intent mapping CSV

If you want human-controlled labeling in addition to regex:

  1. Upload intent_mapping.csv to Google Drive and open as Google Sheets.
  2. Add data in your report → choose that Sheet (fields: pattern, intent).
  3. Blend data (Resource → Manage blends → Blend data):
    • Left: GSC: Site — URL×Query
    • Right: Intent Sheet
    • There’s no native “regex join”; instead, add a Looker Studio calculated field on the GSC side like:

o    CASE

o      WHEN REGEXP_MATCH(Query, pattern) THEN intent

o      ELSE NULL

o    END

    • In practice, most teams keep the regex intent within the GSC source and use the Sheet to override special cases (via filters or a CASE that checks known exceptions first).

Step 9 - Filters, controls, and usability polish

  • Add Date range control (default Last 28 days).
  • Add Search box control on Query for quick lookups.
  • Add Drop-downs for Intent (Regex) and URL Cluster.
  • In Style, set Position axis to show lower is better (invert axis or annotate).

Step 10 - QA & guardrails

  • Sampling/thresholds: GSC may withhold low-volume data. Focus on top queries/pages first.
  • Compare periods: Keep the same day count (e.g., last 28 vs previous 28) to avoid seasonality misreads.
  • Timezone: Your account is in Asia/Kolkata; if your site targets US, consider that when interpreting day-level anomalies.
  • Cannibalization: Not all overlaps are bad. If two URLs intentionally target different sub-intents, keep both; otherwise consolidate with internal links, canonicals, or content merges.

What you’ll see when it’s working

  • Intent pie shows distribution of impressions; a growing Transactional slice is great for money pages.
  • Cannibalization table highlights queries with >1 pages—use this to pick a primary URL and support with internal links.
  • Ranking dynamics graph shows position deltas with period comparison.
  • Anomaly table immediately flags drops/spikes by Query/Page so you can investigate SERP changes or site issues.
  • URL clusters reveal which sections (blog/services/location) pull real visibility and which need attention.

Next upgrades (optional but powerful)

  • Pipe GSC → BigQuery for longer history and unsampled joins, then connect BigQuery to Looker Studio.
  • Add GA4 conversions to see which queries/pages actually convert.
  • Use a small Apps Script to email weekly anomaly snapshots to your team.

If you want, I can generate a Google Sheets starter workbook with:

  • a live cannibalization pivot,
  • an anomaly detector sheet (with thresholds), and
  • a regex intent editor tab—
    so your team can manage labels without touching Looker Studio.

 

at No comments:
Subscribe to: Comments (Atom)

Looker Studio SEO analysis system on top of GSC

  Great - let’s build this together step-by-step. Follow the sequence exactly once, and you’ll have a working Looker Studio SEO analysis sys...