Format GSC Data by Page & Query

A powerful Google Apps Script that transforms raw Google Search Console data into beautifully formatted, page-grouped reports. Perfect for content briefs, SEO reports, and identifying optimization opportunities at a glance.

⬇️ Jump to Code Copy

📄 Group by Page

Automatically organizes queries under their respective pages for easy analysis and reporting.

📊 Sorted by Impressions

Queries are ranked by impressions (or clicks) so you see the most important opportunities first.

🎯 Gap Score Calculation

Identifies optimization opportunities with a calculated "Gap Score" showing potential for improvement.

🎨 Professional Formatting

Creates beautifully styled tables with color-coding, banding, and clear visual hierarchy.

What This Script Does

✅ Groups all queries by their associated page
✅ Sorts queries within each page by impressions (or clicks)
✅ Calculates "Gap Score" for each query to identify optimization opportunities
✅ Prioritizes queries as HIGH, MEDIUM, GOOD, or STRONG based on gap score
✅ Color-codes results (red for high priority, yellow for medium, green for good/strong)
✅ Creates numbered page sections with merged headers
✅ Applies alternating row colors for easy reading
✅ Limits to 20 queries per page for focused reporting
✅ Includes helpful tooltips explaining the metrics
✅ Perfect for content briefs and SEO strategy documents

Understanding the Gap Score

Gap Score Formula:
(Impressions × Position ÷ 10) - Clicks

This metric identifies queries with high visibility (impressions × position) but low actual clicks, indicating content optimization opportunities.

Priority Levels:
🔴 HIGH: Gap Score ≥ 500 (Urgent optimization needed)
🟡 MEDIUM: Gap Score 100-499 (Consider optimization)
🟢 GOOD: Gap Score 0-99 (Acceptable performance)
💪 STRONG: Gap Score < 0 (Outperforming expectations)

How to Use

Step-by-Step Setup:

Prepare Your Data: Export GSC data (or use data from the Winners & Losers tool) with columns: Query, Page, Clicks, Impressions, CTR, Position
Import to Google Sheet: Paste your GSC data into a new Google Sheet with proper headers.
Copy the Script: Click the "Copy Script" button below to copy the entire Apps Script code.
Open Apps Script: In your Google Sheet, go to Extensions → Apps Script.
Paste the Code: Delete the default code and paste the copied script.
Save and Close: Click Save, then close the Apps Script editor.
Run the Script: Back in your Sheet, refresh the page and go to Extensions → Macros or use the menu created by the script.
View Results: A new sheet called "Page_Query_Report" will be created with your formatted report.

Customization Options

You can edit these settings at the top of the script:

REPORT_SHEET_NAME: Change the output sheet name (default: 'Page_Query_Report')
MAX_QUERIES_PER_PAGE: Limit queries shown per page (default: 20)
ORDER_BY: Sort by 'clicks' or 'impressions' (default: 'impressions')
ORDER_DIRECTION: Sort 'ASC' (ascending) or 'DESC' (descending, default)

Copy the Script

Click the button below to copy the entire Apps Script code:

💡 Pro Tips:

Use this script to generate content briefs - organize queries by page and see exactly what to focus on.
HIGH priority queries are perfect candidates for title tag and meta description rewrites.
Filter by priority level to focus on quick wins during your SEO sprints.
The script automatically limits to 20 queries per page to keep reports focused and actionable.
Export the final report as a PDF for client presentations and stakeholder updates.