Back to Blog

How to get CSV report with related search queries and links to their target pages from Google Search Console and Google Analytics

2595 words
12 min read
Last updated December 06, 2024

Table of Contents

If you use Google Analytics 4 and Google Search Console, you probably know that you can access "Performance" in Google Search Console to look at search queries that led to your website. While you can click on them one by one to see each query separately, getting a comprehensive report of all queries and their linked pages for the big picture isn't straightforward through the interface.


While you can export to CSV directly from Google Search Console, you're limited to exporting either a list of search queries or a list of pages (URLs) separately.


Fortunately, there's a solution using the script we've made! You can generate a complete CSV report that shows both the list of queries and their corresponding pages/URLs that appeared in Google search results.

flowchart TD A[Google Search Console API] --> B[Python Script] B --> C[Process Data] C --> D[Generate Reports] D --> E[search_console_data.csv] D --> F[page_summary.csv] D --> G[query_summary.csv]

Here is the screenshot of the output report with both Google Search queries and their target URLs:
Sample CSV report showing search queries and their target URLs

This can be accomplished using a Python script that interfaces with the Google Search Console API. Although you can copy the script from our repository, you'll need to set up API access to your Google Search Console and complete several setup steps to get everything working. Let's walk through the process step by step!

graph TD A[Setup Steps] --> B[Install Python] A --> C[Create Project] A --> D[Enable API] B --> E[Setup Complete] C --> E D --> E E --> F[Run Script]

Step 1: Setup Python on your computer

Windows Installation:

  1. Visit python.org and download the latest Python version (3.8 or higher)
  2. Run the installer, making sure to check "Add Python to PATH"
  3. Download Rye from rye-up.com
  4. Run the Rye installer
  5. Open Command Prompt and verify with: python --version and rye --version

Mac Installation:

  1. Open Terminal
  2. Install Homebrew if not already installed: /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
  3. Install Python: brew install python
  4. Install Rye: curl -sSf https://rye-up.com/get | bash
  5. Verify installation: python3 --version and rye --version
pie title "Report Components" "Search Queries" : 33 "Page URLs" : 33 "Performance Metrics" : 34

Step 2: Create a folder and save Python script

  1. Create a new folder for your project (e.g., "search-console-report")
  2. Open your preferred text editor
  3. Create a new file named get-queries-and-urls.py
  4. Copy the following Python script into the file:

Step 3: Run Rye to install required Python packages

Open your terminal or command prompt in your project directory and run these commands:

rye init
rye add google-api-python-client google-auth pandas
rye sync

These commands will:

  • Initialize a new Rye project
  • Add required dependencies
  • Install all packages and create a virtual environment

Step 4: Signup for Google Developer Cloud

  1. Visit console.cloud.google.com
  2. Click "Sign Up" if you don't have an account
  3. Follow the registration process
  4. Enable 2-Step Verification:
    • Go to your Google Account settings
    • Select "Security"
    • Enable "2-Step Verification"
    • Choose your preferred second factor (phone, authenticator app, or security key)

Step 5: Create a new API project

  1. Go to Google Cloud Console (console.cloud.google.com)
  2. Click on the project dropdown at the top of the page
  3. Click "New Project" in the modal window
  4. Enter a meaningful project name (e.g., "search-console-reports")
  5. Leave the organization as "No Organization" if you don't have one
  6. Click "Create" to initialize your new project

Pro Tip:

Choose a descriptive project name that helps you identify its purpose later, especially if you manage multiple Google Cloud projects.

Step 6: Enable Google Search Console API

  1. Navigate to: Google Search Console API
  2. Make sure your new project is selected in the top dropdown
  3. Click the "Enable" button
  4. Wait for the API to be enabled (usually takes a few seconds)
  5. Verify that the status changes to "API Enabled"

Note:

If you don't see the "Enable" button, make sure you're logged in with the correct Google account and have selected your project.

Step 7: Generate access credentials

  1. In the Google Cloud Console, go to "APIs & Services" > "Credentials"
  2. Click "Create Credentials" and select "Service Account"
  3. Fill in the service account details:
    • Name: "Search Console Reporter"
    • ID: Will be auto-generated
    • Description: "Service account for Search Console reporting"
  4. Click "Create and Continue"
  5. For role, select "Owner" (Required for Search Console access)
  6. Click "Continue" an then "Done"
  7. Also copy the service account email address because you will need it in Step 10 when setting up Search Console permissions (format: yourname@project-id.iam.gserviceaccount.com)

Important:

Save the service account email address - you'll need it in Step 10 when setting up Search Console permissions.

Step 8: Save credentials.json file

  1. In the Google Cloud Console, go to your service account
  2. Click on the "Keys" tab
  3. Click "Add Key" > "Create new key"
  4. Select "JSON" as the key type
  5. Click "Create" - the file will automatically download
  6. Rename the downloaded file to credentials.json
  7. Move credentials.json to your project folder (same location as get-queries-and-urls.py)

Security Warning:

Keep your credentials.json file secure and never share it publicly. This file grants access to your Google Search Console data.

Step 9: Create output folder

Create a folder to store the generated reports:

Windows:

md output

Mac/Linux:

mkdir output

Your project structure should now look like this:

project-folder/
├── get-queries-and-urls.py
├── credentials.json
└── output/

Step 10: Configure Search Console access

  1. Go to Google Search Console
  2. Select your property
  3. Click "Settings" (gear icon) in the left sidebar
  4. Click "Users and permissions"
  5. Click "Add User"
  6. Enter the service account email address you saved earlier
  7. Set permission level to "Restricted"
  8. Click "Add"

Verification:

You should see the service account email listed under "Users" with "Restricted" access level.

Step 11: Run the script

Running the script:

rye run python get-queries-and-urls.py
Here is the output in the console (terminal) that you should see:

Console output showing the script execution progress

After that output subfolder will contain output with reports based on your data 


project-folder/
└── output/
    ├── search_console_data-[timestamp].csv
    ├── page_summary-[timestamp].csv
    └── query_summary-[timestamp].csv

If you encounter any errors:

  • Verify that credentials.json is in the correct location
  • Check that all required packages are installed
  • Ensure your service account has proper Search Console access
  • Verify your Python environment is activated

Step 12: Access your reports

After successful execution, you'll find three CSV files in your output folder:

Main Report:

/output/search_console_data-[timestamp].csv

Contains comprehensive data linking search queries with their corresponding URLs

Additional Reports:

  • /output/page_summary-[timestamp].csv

    Aggregated metrics by page, including total impressions, clicks, and average position

  • /output/search_query_summary-[timestamp].csv

    Search query performance data, showing how different queries perform across all pages

Using the Reports:

  • Use the main report for detailed query-URL analysis
  • Reference page summary for content performance ideas
  • Analyze query summary for keyword optimization opportunities
  • Import into Excel or Google Sheets for further analysis

Frequently Asked Questions

1. Do I need a paid Google Cloud account to run this script?

No. You can use a free tier Google Cloud account as long as you enable the Search Console API. Most users won’t exceed the free usage limits for this purpose.

|
2. Can I run this Python script on both Windows and macOS?

Yes. The script works on any system that supports Python (Windows, macOS, or Linux). Just make sure you install Python 3.8+ and set up the Rye environment as described.

|
3. Do I need prior coding knowledge to implement this solution?

Basic familiarity with command-line operations and Python is helpful, but you can follow the provided step-by-step instructions without being an expert coder.

|
4. How do I add the service account email to Google Search Console?

Go to “Users and permissions” in Search Console settings, click “Add User,” and enter the service account email. Assign at least Restricted access to allow data retrieval.

|
5. Why do I need to enable 2-Step Verification on my Google account?

It’s a best practice for securely managing API credentials. It helps protect your Google Cloud and Search Console data from unauthorized access.

|
6. How can I automate this CSV reporting process?

You can schedule the Python script via cron jobs on Linux/macOS or Task Scheduler on Windows. Adjust the command to run regularly and ensure valid credentials remain accessible.

|
7. What if I encounter an error saying my service account lacks permission?

Double-check the service account email is correctly added under “Users and permissions” in Search Console. Also verify you have the correct JSON credentials file in your project folder.

|
8. Can I specify custom date ranges for the report?

Yes. You can modify the date range parameters within the Python script to define the start and end dates you want. Adjust these lines to fetch older or more recent data as needed.

|
9. Do I need to pay for Google Analytics to use this script?

No. Google Analytics (GA4 or Universal Analytics) has free tiers. This script primarily uses the Search Console API, which also offers a free tier within its usage quotas.

|
10. Can I integrate Google Analytics metrics with the Search Console output?

The provided script focuses on Google Search Console data. If you want GA metrics (sessions, bounce rates, etc.), you’d need to use the Google Analytics API and merge datasets.

|
11. Are there any API usage limits I should worry about?

Google Search Console API and Google Cloud have daily usage limits. However, most small-to-medium sites rarely hit these limits. Monitor your quotas in Google Cloud Console if concerned.

|
12. Does this script collect personal information about my site visitors?

No. The Search Console API only provides aggregated data about clicks, impressions, and queries. There’s no personally identifiable information (PII) in the API responses.

|

Keywords

Google Search Console Google Analytics search queries links to target pages CSV report Google Search Console report Google Analytics report

About The Author

Ayodesk Team of Writers

Ayodesk Team of Writers

Experinced team of writers and marketers at Ayodesk

Continue Reading:

Top AI Search Engines for 2025

look at the 2025 field of AI search engines, including ChatGPT Plus and its deep...

How to Enable Disk Encryption in Windows 11, Mac OS, Ubuntu, and Chromebook

Step-by-step guide for enabling disk encryption on major operating systems, improving your compliance with HIPAA,...

How to Lock Screen and Access a Computer on a Laptop

Learn the importance of locking your laptop screen and discover shortcuts to lock screens on...