You
already have:
- Template
spec: looker_studio_seo_template.json
- Optional
intent map: intent_mapping.csv
Step 1 - Create a new
report & connect GSC
- Open
Looker Studio → Blank report.
- Add
data →
choose Google Search Console → pick your site.
- 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
- Add
Scorecards (Clicks, Impressions, CTR, Position).
- In Date
range: Last 28 days.
- Toggle
Compare to previous period.
- Add
a Time Series:
- Dimension:
Date
- Metrics:
Clicks, Impressions
(enable Dual axis).
- Add
a Bar Chart:
- Dimension:
Intent
(Regex)
- Metrics:
Clicks, Impressions, CTR
- Sort
by Clicks
desc.
Step 4 - Page 2: Search
Intent deep-dive
- Pie/Donut:
Dimension Intent (Regex), Metric Impressions.
- Table:
- Dimensions:
Query, Intent (Regex), Keyword Length Bucket
- Metrics:
Clicks, Impressions, CTR, Position
- Add
report-level filter control for Intent (Regex) to
slice quickly.
- 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):
- Table
- Dimension:
Query
- Metrics:
Cannibalized
(Pages per Query), Clicks, Impressions
- Conditional
formatting: highlight when Cannibalized (Pages per Query) > 1.
Option B (visual cross-check):
- 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
- Time
Series
- Dimension:
Date
- Metric:
Position
- Turn
on Compare to previous period (see shifts visually).
- 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
- Treemap
- Dimension:
URL
Cluster
- Metrics:
Impressions, Clicks.
- Bubble
chart
- Dimension:
URL
Cluster
- X =
CTR, Y
= Position,
Size = Impressions.
- 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:
- Upload
intent_mapping.csv to Google Drive and open as Google
Sheets.
- Add
data in
your report → choose that Sheet (fields: pattern, intent).
- 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.
No comments:
Post a Comment