Google Search Console Winners and Losers URLs Report in Hobo SEO Dashboard

The Winners and Losers Reporting System in Hobo SEO Dashboard (in Google Sheets) is designed to work automatically as part of the SEO Dashboard’s automatic reporting. You can also run it on the loaded client in your Settings sheet, from the Hobo Admin menu.

In your Google Sheets top menu, go to Hobo Admin > Projects:

• Winners and Losers URLs
  • Activate URLs Winners and Losers project
    • Navigate to: Hobo Admin > Projects > Winners and Losers URLs > Activate URLs Winners and Losers project
    • Description: Activates the URLs Winners and Losers project.
  • Deactivate URLs Winners and Losers project
    • Navigate to: Hobo Admin > Projects > Winners and Losers URLs > Deactivate URLs Winners and Losers project
    • Description: Deactivates the URLs Winners and Losers project.

NOTE: To publish the Winners and Losers report from the dashboard, you will need to have already published a client report (e.g., a separate report in your Google Sheets the Dashboard published previously).

The Winners and Losers report is a very intensive report and is designed to work within free API limits in a personal Google account. It will use the maximum free limits, pause reporting, and restart at a certain time when free API limits are available again.

Check out Hobo SEO Dashboard.

About Google Search Console Winners and Losers Report in Hobo SEO Dashboard

The Google Search Console Winners and Losers URLs Report in the Hobo SEO Dashboard provides a unique view of your website.

With it, you can easily see your website’s top-performing and worst-performing pages, allowing you to quickly take action to prioritize improvement.

This insightful report, seamlessly integrated with the Hobo SEO Dashboard, lets you unlock the secrets of your website’s search engine rankings with real-time tracking, algorithm impact analysis, and actionable insights.

Google Search Console Winners and Losers URLs Reports are based on your own Search Console data.

Say goodbye to tedious manual reporting and hello to automated, client-ready reports that showcase your website’s successes and pinpoint areas for optimization.

The Winners and Losers reports are designed to help you prioritize which pages on your site to focus on improving.

Winners and Losers reports on pages and keywords

What Makes the Winners and Losers URLs Script a Game-Changer?

• Real-Time Performance Tracking: The script automatically fetches fresh data from Google Search Console, providing you with up-to-the-minute insights into how your URLs are performing. You can quickly see which pages are skyrocketing in search results and which need immediate attention.
• Algorithm Impact Analysis: Stay ahead of the curve by understanding how Google’s algorithm updates impact your site’s visibility. The Winners and Losers reports specifically focus on how your URLs perform before and after major updates like Core Updates and Spam Updates.
• Detailed SEO Metrics: Gain a comprehensive understanding of your website’s performance with detailed metrics for each URL, including clicks, impressions, CTR, and average position. These insights allow you to pinpoint the strengths and weaknesses of individual pages.
• Client-Ready Reporting: Impress your clients with clean, easy-to-understand reports that showcase the script’s findings. The data is automatically published into client-specific reports, eliminating the need for manual compilation and formatting.
• Effortless Automation: Once set up, the Winners and Losers URLs script handles everything from fetching data and processing it to performing comparisons and generating reports. Enjoy a frictionless and incredibly efficient reporting process that requires zero manual input.

How Does the Script Determine Winning and Losing URLs?

The Winners and Losers URLs script uses a data-driven approach to identify which pages are excelling and which need optimization. It achieves this by analyzing key performance indicators (KPIs) for each URL, primarily focusing on changes in click count as the main indicator of performance shifts.

Here’s a breakdown of the KPIs analyzed:

• Clicks: This metric tracks the number of times users clicked on a specific URL in search results, serving as a direct measure of user engagement.
• Impressions: The number of times a URL appeared in search results, providing insights into a page’s visibility.
• CTR (Click-Through Rate): Calculated as the percentage of impressions that resulted in a click, CTR reflects the effectiveness of a URL’s title tag and meta description in attracting clicks.
• Average Position: This KPI tracks the average ranking of a URL for relevant keywords, indicating its overall search engine visibility.

The script tracks these metrics over time to assess performance before and after Google algorithm updates, giving you a clear understanding of how ranking changes impact your website’s visibility.

To determine winners and losers, the script utilizes two main calculations:

• Sum of Performance Differences (Column O in the “Winners and Losers URLs” Tab): This calculation measures the cumulative change in performance metrics across multiple update periods.
• Percentage Change in Performance Based on Click Count (Column P in the “Winners and Losers URLs” Tab): This calculation specifically focuses on the percentage change in click count, highlighting URLs with substantial gains or losses in user engagement.

URLs experiencing significant positive changes in these calculated metrics, particularly in click count, are classified as “winners,” indicating improved search engine rankings and increased user traffic. Conversely, those showing substantial negative shifts are labeled “losers,” signaling a potential drop in search visibility and user engagement.

Behind the Scenes: How the Script Works

The Winners and Losers URLs script leverages Google Sheets and the Google Search Console API to automate the entire analysis and reporting process.

Here’s a step-by-step explanation of the script’s workflow:

1. Settings Configuration: The “Settings” tab within the Google Sheet serves as the control center. It contains crucial information like the target spreadsheet ID for publishing reports, the domain or URL to be analyzed, time intervals for scheduling triggers, and various flags to manage the script’s execution.
2. Data Fetching: The script initiates the data retrieval process by pulling data from the Google Search Console based on the specified domain or URL. This data, including clicks, impressions, CTR, and average position, is then populated into the “Winners and Losers URLs” tab, starting from row 3.
3. Data Processing: Once the data is fetched, the script dynamically calculates the sum of performance differences (Column O) and the percentage change in performance based on click count (Column P). These calculations are performed using formulas inserted into the respective columns, starting from row 3.
4. Publishing to Client Report: The processed data, including the identified winners and losers, is then copied and published to a designated client report. The target spreadsheet ID for the client report is specified in the “Settings” tab (cell B44). The script ensures data accuracy by clearing old data in the target spreadsheet before replacing it with updated information.
5. Clean-up and Reset: After successfully publishing the report, the script performs a clean-up operation. It clears all data from row 3 onwards in the “Winners and Losers URLs” tab, deletes any rows beyond row 20, and resets key cells in the “Settings” tab to their default states. This step ensures a tidy sheet and prepares the script for the next cycle of data fetching and processing.
6. Trigger Setup: To automate the entire process, the script sets up time-based triggers based on the intervals defined in the “Settings” tab (cells B247-B249). These triggers ensure periodic updates of the winners and losers data, keeping the report current and relevant.

Winners and Losers URLs Script: Detailed Process Breakdown

The Winners and Losers URLs script automates website performance tracking by extracting, processing, and reporting on URL performance data from Google Search Console. Here’s an in-depth look at how it works:

1. Settings Tab (Tab: “Settings”)

• Cell B44: Contains the Google Sheet ID for the target spreadsheet where the report will be published.
• Cell B265: If set to ‘YES’, the script halts to avoid exceeding Google API limits.
• Cell D8: Initially set to 50,000 to limit the number of rows processed.
• Cell D14: Set to ‘YES’ to indicate the start of data fetching.
• Cell B2: Stores the domain or URL to analyze, determining if it’s a DOMAIN or SINGLE property.
• Cell B87: Determines if the property type is ‘DOMAIN’ or ‘SINGLE’ for the Search Console API.
• Cell B245: Defines the row limit for data fetched from the Search Console.
• Cells B247–B249: Store time intervals (in minutes) for triggers to automate data retrieval, processing, and formula application.
• Cells B250, B246, B234, B261: Track the script’s state, such as ‘RUNNING’, ‘YES’, or ‘NO’, to manage the flow.

2. Winners and Losers URLs Tab (Tab: “Winners and Losers URLs”)

• Column A: Stores the URLs pulled from Search Console.
• Column B: Tracks the number of clicks each URL received.
• Columns C to F: Capture impressions, CTR, and average position.
• Columns O and P: Formula columns. Column O calculates performance differences, and Column P computes percentage changes based on click count.
• Row 3: The script begins processing data from row 3 onward.
• Last Row Calculation: The script dynamically determines the last row with data, ensuring empty rows are skipped.

3. Data Fetching and Processing

The script fetches data from Google Search Console using the API and writes it to the Winners and Losers URLs tab, populating columns A to F with metrics and columns O and P with calculated values.

4. Publishing the Data (Target Spreadsheet)

• The target spreadsheet ID is pulled from cell B44 in the Settings tab.
• It duplicates the Winners and Losers URLs tab into the target Google Sheet and clears the Settings tab before copying the new data over.
• Old data is replaced to ensure the report contains only the most recent performance insights.

5. Triggers Setup

• The script schedules time-based triggers using intervals from B247–B249, controlling data retrieval, formula addition, and performance processing. This ensures the script stays within free Google API usage limits. The winners and losers report in Search Console is a data-intensive report.

6. Cleanup and Reset

• After generating and publishing the report, the script clears old data from row 3 onward in the Winners and Losers URLs tab.
• It trims rows down to 20 and updates the Settings tab’s tracking cells, preparing the system for the next run.
• All existing triggers are deleted to avoid duplication in the next cycle.

Key Cell References:

• Triggers: Scheduled from B247–B249, controlling the script’s automation cycle.
• Data Fetching: The domain is defined in B2, and the process halts if B265 is ‘YES’ to avoid API limits.
• Data Processing: Formulas are added to Columns O and P, starting from row 3, to calculate performance shifts.
• Publishing: The report is copied into the client’s spreadsheet, using the ID from B44 in the Settings tab.

By leveraging these specific cells and tabs, the Winners and Losers URLs script automates a highly granular process of SEO analysis, making it a powerful tool for ongoing performance monitoring and reporting.

FAQ: Winners and Losers URLs Script

• Q: What is the Winners and Losers URLs script?
  • A: The Winners and Losers URLs script is an SEO performance tool that automatically identifies URLs gaining traffic (“winners”) and those losing traffic (“losers”). It leverages data from Google Search Console and provides actionable insights for SEO optimization.
• Q: How does the script determine winning and losing URLs?
  • A: The script analyzes changes in key performance indicators (KPIs) over time, with a primary focus on click count. URLs with significant positive changes, especially in click count, are categorized as “winners,” while those with substantial negative changes are classified as “losers”.
• Q: What calculations does the script use to categorize URLs?
  • A: The script utilizes two main calculations: (1) Sum of Performance Differences, which measures the cumulative change in performance metrics across multiple update periods, and (2) Percentage Change in Performance, which specifically analyzes the percentage change in click count.
• Q: How does the script work?
  • A: The script follows a five-step process: (1) Settings Configuration, (2) Data Fetching from Google Search Console, (3) Data Processing and Calculation of KPIs, (4) Publishing processed data to a client report, and (5) Clean-up and Reset for the next cycle. Time-based triggers ensure automatic periodic updates.
• Q: What are the benefits of using the Winners and Losers URLs script?
  • A: The script provides real-time performance tracking, algorithm impact analysis, detailed SEO metrics, client-ready reporting, and effortless automation.

The Winners and Losers URLs script is an invaluable tool for any website owner or SEO professional looking to gain a competitive edge in the ever-evolving world of search engine optimization. By automating the tedious task of performance monitoring and delivering actionable insights, this script empowers you to focus on what matters most – optimizing your website for success.

Hobo SEO Dashboard

The Hobo SEO Dashboard is a fully autonomous SEO reporting tool built in Google Sheets, designed to assist marketers, SEOs, and web developers in analyzing any website, tracking performance, and managing SEO for their clients.

Watch the video:

Key Features and Capabilities:

• Automated SEO Audits: The Hobo SEO Dashboard simplifies SEO audits by using Screaming Frog data to automatically generate professional SEO reports.
• Client Management: It includes a dedicated Clients tab for managing individual client settings. New clients can be added automatically based on Screaming Frog data stored in Google Drive. The system also automates the process of emailing reports directly to clients.
• Seamless Integration: Hobo SEO Dashboard integrates effortlessly with Screaming Frog, Google Search Console, and Google Analytics. It pulls and processes relevant data files from Google Drive, ensuring streamlined performance management.
• Automation and File Management: The tool cycles through your client list, loads Screaming Frog files, manages your Google Drive data, and automatically sends reports to you or your clients. It even cleans up old Screaming Frog files by moving them to Google Drive’s trash for 30 days.

Comprehensive SEO Analysis:

• Domain statistics
• Winners and Losers reports for keywords and URLs
• Rank tracking and performance analysis
• Google algorithm impact assessment
• Technical SEO insights and priorities
• Crawl errors and page indexation status
• Website performance, including Core Web Vitals
• Backlink analysis
• SEO project prioritization

Additional Highlights:

• Private and Secure: Ensures complete data privacy.
• No Ongoing Costs: One-time purchase, with no subscription fees.
• Lifetime Access: Users receive ongoing updates with no extra charge.

In summary, the Hobo SEO Dashboard is a powerful, cost-effective tool that saves time and boosts the SEO performance of websites, providing detailed insights and seamless client management.