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:

Email Marketing Checklist for Mailer Scheduling


1.      Email List Cleansing

Before scheduling your mailer, it is crucial to clean the email list to ensure accuracy and avoid unnecessary bounces or spam issues. Follow this checklist for cleansing the email list:

  • Remove Duplicate Entries: Ensure no duplicate email addresses are present in the list.
  • Check Contact Names: Verify that the contact names associated with the email addresses are correct.
  • Verify Email Addresses: Ensure that the email addresses are accurate (e.g., replacing "gmx.com" with "gmail.com" where appropriate).
  • Remove Test Emails: Eliminate any test ads or placeholder email addresses from the list.
  • Remove Non-Commercial Domains: Delete any email addresses ending with "@Facebook," ".gov," or other non-commercial domains.
  • Eliminate Irrelevant Symbols: Remove any email addresses containing irrelevant symbols that could affect delivery.

2. Include Sulekha Verified Email IDs

Adding Sulekha verified email IDs to your list can significantly reduce the number of spammers and increase the likelihood of engagement. Always prioritize verified email addresses when scheduling mailers.

3. Schedule Mailers at Regular Intervals

Timing is key in email marketing. Schedule your mailers at regular intervals to maximize user engagement. Stagger the email sends over a period of time to improve the open rate, as users may be more likely to open the email when they receive it after a few days.

  • Maintain EST Timing: Ensure that all mailers are scheduled according to EST (Eastern Standard Time) to align with the target audience’s time zone.

4. Remove Inactive Email Addresses

Segment and clean your email list by removing inactive addresses. Specifically:

  • Remove email addresses that have shown no activity (e.g., have not opened an email or emails were not delivered) in the last five years.
  • Segment the email list based on engagement to focus on active users.

5. Frequently Change HTML Design and Content

Update the design and content of your HTML mailers frequently to maintain user interest. It is recommended to refresh the content every 3 days. Consistently changing the design will keep the audience engaged and reduce the chances of your emails being marked as repetitive or irrelevant.

6. Optimize the Subject Line

The subject line is crucial for enticing recipients to open your emails. Follow these guidelines:

·         Avoid Offers and Symbols: Steer clear of including offer percentages (e.g., "10% OFF") or symbols like exclamation points ("!") in the subject line, as these can trigger spam filters.

·         Check Engagement Score: Adjust the subject line based on engagement metrics to improve performance.

·         Suggestions and Emojis: Consider using suggested titles based on subject line analysis and enable the use of emojis to make your subject lines more appealing and relevant.

Checklist for Subject Line Optimization: i) Include the title taken from the page. ii) Review and adjust the subject line based on click engagement scores. iii) Utilize suggested titles for better performance (you can use recommendations). iv) Enable the emoji button for added visual appeal.

7. Remove Unsubscribed Email IDs

Unsubscribed email addresses must be identified and removed from your mailing list to comply with email regulations and avoid negative impacts on deliverability.

8. Cross-Check and Upload Corrected Email IDs

Any email addresses that were not uploaded initially should be cross-checked for accuracy. Once corrected, upload the updated email base separately to ensure no errors in the mailing process.

9. Add UTM Source for Event Tracking

To track the performance of your mailer campaigns effectively, ensure that UTM parameters are added for event tracking. This can be done within the scheduling tool (e.g., Netcore) on the first page.

10. Maintain a Daily Mailer Report

Keep a detailed report of your mailer campaigns on a daily basis. The report should include all necessary fields such as sent emails, open rates, click-through rates, and other relevant metrics. This will help you monitor the effectiveness of your campaigns and make data-driven improvements.

at No comments:
Labels: ,
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...