If you use Google Analytics 4 and Google Search Console, you probably know that you can access "Performance" in Google Search Console to explore 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.

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!

Step 1: Setup Python on your computer

Windows Installation:

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

Mac Installation:

Open Terminal
Install Homebrew if not already installed: /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
Install Python: brew install python
Install Rye: curl -sSf https://rye-up.com/get | bash
Verify installation: python3 --version and rye --version

Step 2: Create a folder and save Python script

Create a new folder for your project (e.g., "search-console-report")
Open your preferred text editor
Create a new file named get-queries-and-urls.py
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

Visit console.cloud.google.com
Click "Sign Up" if you don't have an account
Follow the registration process
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

Go to Google Cloud Console (console.cloud.google.com)
Click on the project dropdown at the top of the page
Click "New Project" in the modal window
Enter a meaningful project name (e.g., "search-console-reports")
Leave the organization as "No Organization" if you don't have one
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

Navigate to: Google Search Console API
Make sure your new project is selected in the top dropdown
Click the "Enable" button
Wait for the API to be enabled (usually takes a few seconds)
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

In the Google Cloud Console, go to "APIs & Services" > "Credentials"
Click "Create Credentials" and select "Service Account"
Fill in the service account details:
- Name: "Search Console Reporter"
- ID: Will be auto-generated
- Description: "Service account for Search Console reporting"
Click "Create and Continue"
For role, select "Owner" (Required for Search Console access)
Click "Continue" an then "Done"
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

In the Google Cloud Console, go to your service account
Click on the "Keys" tab
Click "Add Key" > "Create new key"
Select "JSON" as the key type
Click "Create" - the file will automatically download
Rename the downloaded file to credentials.json
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

Go to Google Search Console
Select your property
Click "Settings" (gear icon) in the left sidebar
Click "Users and permissions"
Click "Add User"
Enter the service account email address you saved earlier
Set permission level to "Restricted"
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 insights
Analyze query summary for keyword optimization opportunities
Import into Excel or Google Sheets for further analysis

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