Build an AI SEO Opportunity Finder

Introduction

30 Second Summary

A website can appear in search for hundreds of phrases, while the busiest phrases keep drawing your eye. Promising topics can stay buried beneath pages that already get clicks.

In this project, you will build a Python workflow that turns live Google Search Console data into reviewable SEO opportunities. The workflow shows its ranking logic before asking the OpenAI API for three structured content briefs.

What You'll Build

Run one command to watch your site's search phrases become an auditable shortlist plus three Markdown content briefs ready for human review.

By the end of this project, you'll have:

• A live search-performance view that shows available dates plus clicks, impressions, CTR, and average position for each query.
• An auditable CSV that ranks every row with a transparent missed-click proxy. The original metrics stay visible for your judgment.
• A cost-gated AI report that waits for explicit AI confirmation before making the paid request. The finished seo_briefs.md pairs three briefs with their source metrics.
• Secret Mission: Audit the brief with the highest missed_clicks_proxy as an SEO lead. Record a Keep, Revise, or Reject verdict backed by source metrics.

Are there any prerequisites?

You need a Linux device, a Google account with a verified Search Console property, and a Google Cloud project with Search Console API access, OAuth consent configuration, and installed-application client credentials. The project guides you through the remaining local tools and OpenAI API key setup.

Before We Start

This opening checkpoint anchors the project to a real Search Console property. It also commits your workflow to decision support with every SEO recommendation kept under human review.

Set Up Your Linux Python Workspace

Your SEO workflow depends on several libraries that must work together every time you rerun it. An isolated workspace prevents those packages from colliding with the Linux system Python.

Visual Studio Code becomes the editor for your project folder. The uv CLI creates a controlled Python 3.13 environment from your pinned dependency file.

In this step, get ready to:

• Make Visual Studio Code available on your Linux device.
• Install uv version 0.12.12.
• Build a Python 3.13 environment with the pinned project dependencies.

Install the workspace tools

Visual Studio Code gives the project a visible file workspace. The uv CLI controls the Python interpreter and installed packages.

• Open a terminal window from your Linux application launcher.
• Check whether Visual Studio Code is available by running:

code --version

What does this check do?

The code command starts Visual Studio Code from a terminal. The version check confirms that Linux can find that command.

✔️ I see version information

Visual Studio Code is available from your terminal. Continue with the uv check below.

ⓧ Command not found

Linux package choices vary by distribution, so this part can feel fiddly. Use the installation route that matches your device.

Snap

• Install Visual Studio Code through Snap by running:

sudo snap install --classic code

What does this command do?

Snap installs the official Visual Studio Code package. The --classic flag gives the editor the file access expected by a desktop development tool.

Debian or Ubuntu

• Visit the official Visual Studio Code Linux setup guide.
• Download the .deb package recommended for Debian or Ubuntu.
• Complete the installation with your distribution's package installer.

Fedora or RHEL

• Visit the official Visual Studio Code Linux setup guide.
• Download the .rpm package recommended for Fedora or RHEL.
• Complete the installation with your distribution's package installer.

• Close the terminal after the installation finishes.
• Open a new terminal from your Linux application launcher.
• Confirm that Visual Studio Code is now available by running:

code --version

What should this confirm?

The command prints Visual Studio Code version information when the installation is available on your terminal path.

You should now see version information instead of a missing-command message.

Still unable to run the editor?

Close every terminal window after installing Visual Studio Code. A new terminal reloads the command path provided by the package.

Use the installation route for your Linux distribution if the package format does not match your device.

Help me make the Visual Studio Code command available on Linux.

• Check whether uv is already available by running:

uv --version

What does this check do?

This prints the installed uv release. The project uses version 0.12.12 for a reproducible setup.

✔️ I see uv 0.12.12

Your uv installation matches the project version. Continue to the workspace setup below.

ⓧ I see an older version

Install the pinned 0.12.12 release so your commands match this project.

• Install the pinned release by running:

curl -LsSf https://astral.sh/uv/0.12.12/install.sh | sh

What does this installer do?

The script downloads the versioned Linux installer for uv release 0.12.12.

• Close the terminal if the installer reports a path update.
• Open a new terminal from your Linux application launcher.
• Verify the installed release by running:

uv --version

What should this confirm?

The version check confirms which uv release the new terminal finds.

You should see 0.12.12 in the output.

ⓧ Command not found

Install uv with its versioned Linux installer.

• Install uv version 0.12.12 by running:

curl -LsSf https://astral.sh/uv/0.12.12/install.sh | sh

What does this installer do?

The script downloads the pinned uv installer and adds the tool to your Linux user environment.

• Close the terminal if the installer reports a path update.
• Open a new terminal from your Linux application launcher.
• Verify the installation by running:

uv --version

What should this confirm?

A successful version check confirms that the new terminal can find the installed uv command.

You should see 0.12.12 in the output.

Still unable to run uv?

Restart the terminal after the installer finishes because your existing shell may still have the old command path.

Check that the installer completed without a network or permissions failure.

Help me finish the uv installation on Linux.

Create the isolated project environment

A virtual environment gives this project its own Python interpreter and package set. The pinned requirements make the same library versions available on every run.

• Create ~/ai-seo-opportunity-finder inside your home folder by running:

mkdir -p ~/ai-seo-opportunity-finder && cd ~/ai-seo-opportunity-finder && pwd

What does this command do?

• The mkdir -p segment creates the project folder inside your home folder.
• The cd segment moves your terminal into that folder.
• The pwd segment prints the folder's full location as a visible check.

You should see a path ending in ai-seo-opportunity-finder.

• Open the current project folder in Visual Studio Code by running:

code .

What does this command do?

The dot represents the terminal's current folder. Visual Studio Code opens ai-seo-opportunity-finder as the active workspace.

Good, the editor is now anchored to the exact folder you will use throughout the project.

• Select the Explorer view in the Visual Studio Code Activity Bar.
• Select the New File... button in the Explorer view.
• Enter requirements.txt as the file name.
• Populate requirements.txt with the pinned dependencies by pasting:

google-api-python-client==2.200.0
google-auth-oauthlib==1.4.1
openai==3.11.0

What do these dependencies provide?

• The Google API Client Library builds the Search Console service used later in the workflow.
• The OAuth library provides the installed-application authorization flow.
• The OpenAI SDK provides the client used for the structured AI request.

• Press Ctrl+S to save requirements.txt.
• Confirm that requirements.txt appears under ai-seo-opportunity-finder in the Explorer view.

Don't see the requirements file?

Check that the Explorer view shows ai-seo-opportunity-finder as the open folder. Create requirements.txt directly under that folder.

Make sure the file name ends with .txt exactly once.

Help me create requirements.txt in the correct VS Code workspace.

✔️ Awesome, I've got everything!

Great. Double check that requirements.txt is saved inside ~/ai-seo-opportunity-finder.

ⓧ I'd like to double check the full code

google-api-python-client==2.200.0
google-auth-oauthlib==1.4.1
openai==3.11.0

What should match?

Your file should contain these three package pins in the same order. Each dependency uses the exact version required by this project.

• Return to the terminal from earlier.
• Create the Python 3.13 virtual environment by running:

uv venv --python 3.13

What does this command do?

The command creates a .venv environment for this project. The --python 3.13 option selects Python 3.13 for that environment.

• Check the environment's Python version by running:

.venv/bin/python --version

What does this check do?

This calls the Python executable inside .venv directly. It proves which interpreter belongs to the project.

✔️ I see Python 3.13

The project environment uses Python 3.13. Continue to the dependency installation below.

ⓧ I see an older version

The existing .venv uses the wrong interpreter version.

• Delete .venv from the Visual Studio Code Explorer view.
• Recreate the environment with Python 3.13 by running:

uv venv --python 3.13

What does this command change?

The command builds a fresh .venv with the requested Python 3.13 interpreter.

Run the version check again below the tabs before installing dependencies.

ⓧ The environment is missing

The virtual environment was not created successfully.

• Create .venv with Python 3.13 by running:

uv venv --python 3.13

What should this create?

The command creates the .venv folder and places the selected Python interpreter inside it.

Run the version check again below the tabs before installing dependencies.

Still missing Python 3.13?

Confirm that your terminal is inside ~/ai-seo-opportunity-finder before creating the environment.

Check the installer output for a network failure if uv cannot obtain the requested interpreter.

Help me create the Python 3.13 virtual environment with uv.

• Install the pinned dependencies into .venv by running:

uv pip sync requirements.txt

What does this command do?

The command reads requirements.txt and synchronizes the virtual environment with those exact package versions. This keeps the dependency set reproducible.

You should see installation output for the Google client libraries and the OpenAI SDK. That's your controlled environment assembled with every project dependency pinned.

Dependency installation failed?

Confirm that the terminal's current folder is ~/ai-seo-opportunity-finder. The sync command needs requirements.txt in that folder.

Compare the three lines in your file with the full-code tab above if a package version cannot be resolved.

Help me fix my uv dependency sync.

Verify the finished workspace

A completed installation still needs a runtime check. Importing each client proves that Python can load the exact modules your workflow uses.

• Verify all three library imports by running:

uv run python -c "from openai import OpenAI; from googleapiclient.discovery import build; from google_auth_oauthlib.flow import InstalledAppFlow; print('Dependencies ready')"

What does this check prove?

• The first import confirms that the OpenAI client is available.
• The second import confirms that the Google discovery client can build an API service.
• The third import confirms that the installed-application OAuth flow is available.

You should see Dependencies ready with no import failure.

Seeing an import failure?

Return to ~/ai-seo-opportunity-finder before running the check. The uv command selects the environment associated with that folder.

Run the dependency sync again if the terminal output says a required module is missing.

Help me resolve the failed Python import check.

Before you run the final check, which three version lines do you expect your terminal to confirm?

• Verify the complete workspace by running:

uv --version && .venv/bin/python --version && code --version

What does the final check cover?

The chained command checks uv first. It continues with the project interpreter before checking the editor command.

You should see uv version 0.12.12 and Python 3.13. Visual Studio Code version information appears after them.

That's the workspace foundation locked in: your editor, interpreter, and pinned libraries are ready for the integration.

Your reproducible Linux workspace is ready. Next up, you'll connect the API credentials without placing either secret in your source code.

Connect and Protect API Credentials

Your isolated Python workspace is ready. The workflow now needs controlled access to Google Search Console.

It also needs controlled access to the OpenAI API. Keeping both credentials outside Python source protects them from accidental exposure.

In this step, get ready to:

• Copy your installed-application Google OAuth credentials into the project.
• Export an OpenAI API key for the current terminal session.
• Exclude credentials and generated files from Git.

Protect your Google OAuth credentials

The client_secrets.json file holds the installed-application OAuth client credentials that begin browser authorization. The file remains local because it contains sensitive configuration.

• Find your existing installed-application OAuth download in your Linux file manager.
• Rename the downloaded file to client_secrets.json.
• Drag client_secrets.json into the ai-seo-opportunity-finder folder shown in the Explorer sidebar in Visual Studio Code.
• Confirm client_secrets.json appears beside requirements.txt in the Explorer sidebar.

Why keep this file local?

InstalledAppFlow.from_client_secrets_file(...) reads this file when authorization starts. The file identifies the OAuth client configured in your Google Cloud project.

Browser authorization still asks you to approve access later. The local credential file stays outside source control.

Export your OpenAI API key

The OpenAI API uses a secret key to authenticate paid requests. This step stores that credential in your current shell session.

The Paid Call Comes Later

Creating the key does not send a model request. The workflow makes its paid request only after explicit confirmation in Step 5.

The full secret appears only once, so this part can feel fiddly. You will move it directly into the current terminal session.

• Open the official OpenAI API key page in your browser.
• Sign in to the OpenAI API Platform.
• Select + Create new secret key.
• Select Create to generate the key.
• Copy the full secret key when it appears.
• Return to the terminal panel in Visual Studio Code from earlier.

• Export the copied key only in this terminal by replacing your_api_key_here in the command below:

export OPENAI_API_KEY="your_api_key_here"

What Does This Command Do?

• export places the key in the current shell environment.
• OpenAI() reads OPENAI_API_KEY from that environment later.
• The secret value stays out of your project files.
• Closing this terminal removes the exported value from its shell session.

• Keep this terminal session open for the final credential check.

Exclude credentials and generated files

A .gitignore file tells Git which local paths stay outside future commits. These rules protect every sensitive or generated artifact this workflow creates.

• Select the ai-seo-opportunity-finder folder in the Explorer sidebar.
• Create .gitignore by selecting the new-file icon at the top of the Explorer sidebar.
• Add the following exclusion rules to .gitignore:

.venv/
__pycache__/
client_secrets.json
seo_opportunities.csv
seo_briefs.md
review-notes.md

What Do These Rules Protect?

• .venv/ excludes the isolated Python environment.
• __pycache__/ excludes Python cache files.
• client_secrets.json excludes the local OAuth credential file.
• The three output paths exclude generated analysis files.

• Save .gitignore.
• Confirm .gitignore appears beside requirements.txt in the Explorer sidebar.
• Compare the saved file with the six exclusion rules above.

✔️ Awesome, I've got everything!

Good. Your ignore file now protects the workflow's sensitive local artifacts.

ⓧ I'd like to double check the full code

.venv/
__pycache__/
client_secrets.json
seo_opportunities.csv
seo_briefs.md
review-notes.md

Before you run the final check, do you expect it to reveal either credential?

• Verify credential availability without printing secret values by running this command in the terminal session from earlier:

test -f client_secrets.json && test -n "$OPENAI_API_KEY" && echo "API credentials ready"

What Does This Check Prove?

• test -f client_secrets.json confirms the local credential file exists.
• test -n "$OPENAI_API_KEY" confirms the environment variable contains a value.
• echo "API credentials ready" prints the success message only after both checks pass.

You'll see API credentials ready without either secret value.

That's the security boundary in place. The workflow can access both credentials without storing them in source code.

No Confirmation Message?

The command prints its message only after both checks pass.

• Confirm client_secrets.json is inside ~/ai-seo-opportunity-finder.
• Return to the terminal session that received the export command.

Help me diagnose the safe credential check.

• Scroll the terminal until the earlier export command is outside the visible area.

Your credentials are protected. Next, you'll use the Google credential to pull live Search Console rows.

Pull Live Search Console Rows

Your protected credentials give your Python program a safe path to the Google Search Console API. You now need to inspect the API's real response before deciding how to prioritize it.

In this step, you will authorize read-only access to Search Console. Your program will print live query metrics for one selected property across a valid date range.

In this step, get ready to:

• Authenticate with read-only Search Console access.
• Select an accessible property from the API response.
• Validate a date range and print live query metrics.

Build the read-only connection

The OAuth 2.0 installed application flow opens a browser so you can approve access with your Google account. The read-only scope limits the program to retrieving Search Console data.

• Click the new-file control in the Explorer sidebar of Visual Studio Code.
• Create seo_opportunity_finder.py inside ~/ai-seo-opportunity-finder.
• Add the shared imports and configuration by pasting this code into seo_opportunity_finder.py:

import csv
import json
import os
from datetime import date
from pathlib import Path

from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from openai import OpenAI

SCOPES = ["https://www.googleapis.com/auth/webmasters.readonly"]
CLIENT_SECRETS_FILE = Path("client_secrets.json")
MODEL = "gpt-6-astra"
QUERY_ROW_LIMIT = 100
SHORTLIST_SIZE = 3

What does this foundation do?

• The imports provide date validation plus access to the Google libraries installed earlier.
• The SCOPES value requests read-only access to Search Console data.
• The CLIENT_SECRETS_FILE value points to the protected credential file from the previous step.
• The remaining constants keep shared workflow settings in one visible place.

• Save seo_opportunity_finder.py.
• Confirm the Explorer sidebar lists seo_opportunity_finder.py beside requirements.txt.

Seeing unresolved imports?

Confirm Visual Studio Code is using the virtual environment from .venv/. The packages already installed in that environment provide the Google and OpenAI imports.

Use this prompt if the import warnings remain: Help me connect Visual Studio Code to the .venv environment in ~/ai-seo-opportunity-finder.

• Add the authentication function and temporary entry point below the constants by pasting this code:

def authenticate_search_console():
    if not CLIENT_SECRETS_FILE.exists():
        raise FileNotFoundError(
            f"Missing {CLIENT_SECRETS_FILE}. Copy your Google OAuth client file here."
        )

    flow = InstalledAppFlow.from_client_secrets_file(
        str(CLIENT_SECRETS_FILE),
        scopes=SCOPES,
    )
    credentials = flow.run_local_server()
    return build("searchconsole", "v1", credentials=credentials)


def main():
    service = authenticate_search_console()


if __name__ == "__main__":
    main()

What does this code do?

• The file check stops early when client_secrets.json is missing.
• The installed application flow reads your local OAuth client configuration.
• The local server flow opens browser authorization and returns credentials to the program.
• The final call builds an authenticated Search Console v1 client.

• Save seo_opportunity_finder.py.

The flow requests read-only access to your Search Console data. It does not use your OpenAI API key or make a paid request.

Before you run the program, what level of access do you expect Google to ask you to approve?

• Start the authentication flow from the terminal in ~/ai-seo-opportunity-finder by running:

uv run python seo_opportunity_finder.py

What does this command do?

The uv command runs the script with the virtual environment and pinned dependencies from your project.

• Complete the Google authorization flow in the browser with an account that can access your Search Console property.
• Return to the terminal after the browser confirms the authorization flow has completed.

You should see the browser confirm that the authentication flow has completed. Your terminal then returns to its prompt because this first runnable slice only creates the authenticated service.

Browser authorization did not complete?

Confirm client_secrets.json is inside ~/ai-seo-opportunity-finder. Also confirm the file contains installed-application OAuth credentials.

Use this prompt if the browser does not open or Google does not return control to the script: Help me troubleshoot the InstalledAppFlow browser authorization in my Search Console Python program.

Select a property and validate dates

Search Console identifies properties through the exact siteUrl values returned by the API. Selecting from that response prevents URL-prefix and Domain property identifiers from being guessed incorrectly.

• Insert this property-selection function above main() in seo_opportunity_finder.py:

def choose_property(service):
    response = service.sites().list().execute()
    entries = response.get("siteEntry", [])
    sites = [
        entry["siteUrl"]
        for entry in entries
        if entry.get("permissionLevel") != "siteUnverifiedUser"
    ]

    if not sites:
        raise RuntimeError("No verified Search Console properties were returned.")

    print("\nAccessible Search Console properties:")
    for index, site_url in enumerate(sites, start=1):
        print(f"  {index}. {site_url}")

    while True:
        try:
            selection = int(input("Choose a property number: "))
            return sites[selection - 1]
        except (ValueError, IndexError):
            print("Enter one of the numbers shown above.")

How does property selection work?

• The sites request retrieves properties available to the authorized Google account.
• The list excludes entries where the account is an unverified user.
• The numbered menu preserves each exact siteUrl returned by Google.
• The input loop keeps asking until the learner selects a displayed number.

• Add the property selection call below the existing service line inside main():

    site_url = choose_property(service)

Why add this line?

The selected site_url becomes the property identifier used by every Search Analytics request in this run.

• Save seo_opportunity_finder.py.
• Run the updated property selection flow with this command:

uv run python seo_opportunity_finder.py

What changes on this run?

The script authenticates again because this beginner workflow does not persist Google tokens. It now continues to the property list before exiting.

• Complete browser authorization again.
• Select one of the property numbers printed in the terminal.

You should see a numbered list under Accessible Search Console properties:. Choosing a number confirms the program can use an API-returned property identifier.

No properties listed?

Confirm you authorized the Google account that owns or can access the verified Search Console property. The program deliberately excludes unverified property entries.

Use this prompt if the list is empty: Help me find why the Search Console sites list returned no verified properties for my authorized account.

• Insert the date-input function above main() in seo_opportunity_finder.py:

def prompt_for_date(label):
    while True:
        value = input(f"{label} date (YYYY-MM-DD): ").strip()
        try:
            date.fromisoformat(value)
            return value
        except ValueError:
            print("Use YYYY-MM-DD, for example 2026-08-01.")

How are dates validated?

The function uses date.fromisoformat() to accept ISO-formatted dates. Invalid text returns the learner to the same prompt with a concrete format example.

• Add these date lines below the property selection call inside main():

    start_date = prompt_for_date("Start")
    end_date = prompt_for_date("End")

    if date.fromisoformat(start_date) > date.fromisoformat(end_date):
        raise ValueError("Start date must not be after end date.")

What does the second check protect?

Each value already has a valid ISO format. The comparison also prevents a start date that falls after the end date.

• Save seo_opportunity_finder.py.
• Run the script to test the property and date prompts:

uv run python seo_opportunity_finder.py

What should this run prove?

The script should accept one API-returned property plus two dates in YYYY-MM-DD format. It then returns to the prompt because the Search Analytics request comes next.

• Complete browser authorization.
• Select an accessible property number.
• Enter a valid start date when prompted.
• Enter a valid end date when prompted.

You should see separate start-date and end-date prompts. A valid chronological range lets the script finish without a date-validation failure.

Date input keeps repeating?

Use four digits for the year plus two digits for the month and day. Make sure the start date is not later than the end date.

Use this prompt if a valid range is still rejected: Help me troubleshoot the ISO date validation in prompt_for_date without changing its required format.

Query and inspect live metrics

Search Analytics groups results using requested dimensions. A date-dimension request confirms that the selected range contains data before the program asks for query-level metrics.

• Insert the shared query helper and date-availability function above main() in seo_opportunity_finder.py:

def execute_search_analytics_query(service, site_url, body):
    return (
        service.searchanalytics()
        .query(siteUrl=site_url, body=body)
        .execute()
    )


def verify_available_dates(service, site_url, start_date, end_date):
    response = execute_search_analytics_query(
        service,
        site_url,
        {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["date"],
        },
    )
    return response.get("rows", [])

What do these functions do?

• The shared helper sends one Search Analytics request for the selected property.
• The availability function groups results by date across the requested inclusive range.
• Returning an empty list gives the program a safe signal when no rows are available.

• Add the date-availability check below the date-order check inside main():

    available_dates = verify_available_dates(
        service,
        site_url,
        start_date,
        end_date,
    )
    if not available_dates:
        raise RuntimeError("Search Console returned no data for that date range.")

    print(
        "\nAvailable data runs from "
        f"{available_dates[0]['keys'][0]} to {available_dates[-1]['keys'][0]}."
    )

Why print the available dates?

The first and last returned date keys show the actual data span inside the requested range. The empty-result check stops the workflow before it requests query rows with no usable date data.

• Save seo_opportunity_finder.py.

Before you run the check, do you expect the entire requested range to contain Search Console data?

• Test the date-availability request by running:

uv run python seo_opportunity_finder.py

What does this check reveal?

This run compares your requested dates with the date rows Search Console actually returns. The printed range proves the API found data before the query request begins.

• Complete browser authorization.
• Select the property you want to analyze.
• Enter a date range that should contain performance data.

You should see a line beginning with Available data runs from. The two dates show the data span returned for your selected property.

No data for that date range?

Try a wider historical range that falls after the property began collecting Search Console data. Also confirm you selected the intended property from the numbered list.

Use this prompt if known reporting dates still return no rows: Help me troubleshoot an empty date-dimension Search Analytics response for my verified property.

• Insert the query retrieval and table-printing functions above main() in seo_opportunity_finder.py:

def fetch_query_rows(service, site_url, start_date, end_date):
    response = execute_search_analytics_query(
        service,
        site_url,
        {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["query"],
            "rowLimit": QUERY_ROW_LIMIT,
        },
    )
    return response.get("rows", [])


def print_raw_rows(rows, limit=10):
    print("\nRaw query rows returned by Search Console")
    print(f"{'Query':40} {'Clicks':>8} {'Impr.':>10} {'CTR':>8} {'Pos.':>8}")
    print("-" * 80)
    for row in rows[:limit]:
        query = row["keys"][0][:40]
        print(
            f"{query:40} "
            f"{row['clicks']:8.0f} "
            f"{row['impressions']:10.0f} "
            f"{row['ctr'] * 100:7.2f}% "
            f"{row['position']:8.2f}"
        )

How does the raw table work?

• The query request groups performance data by search query.
• The row limit asks Search Console for up to 100 top rows.
• The printer keeps the first 10 rows visible in the terminal.
• Each displayed row preserves clicks, impressions, CTR, and average position.

• Add the final query retrieval lines below the available-date message inside main():

    rows = fetch_query_rows(service, site_url, start_date, end_date)
    if not rows:
        raise RuntimeError("Search Console returned no query rows for that range.")

    print_raw_rows(rows)

Why check the query rows?

The empty-result check prevents the program from printing a misleading blank table. A non-empty response moves directly into the raw metric display.

• Save seo_opportunity_finder.py.

Before you run the finished pull, which metric do you think Search Console uses to order the returned query rows?

• Run the completed live-data workflow with this command:

uv run python seo_opportunity_finder.py

What happens during this run?

The program authorizes Search Console access and validates data availability. It then requests query rows before formatting the first ten as a terminal table.

• Complete browser authorization with your Google account.
• Select the exact property number printed in the terminal.
• Enter a valid start date.
• Enter a valid end date.

You should see available dates followed by query rows containing clicks, impressions, CTR, and position. The rows descend by click count because Search Console returns query-grouped results in that order.

A high-impression query with weak CTR can sit below an already successful query. This intended shortfall proves the raw response cannot yet serve as your SEO opportunity ranking.

Why does the raw output fall short?

Click order highlights queries already earning visits. It does not directly reveal which impressions are failing to convert into clicks.

The original metrics remain valuable evidence. The next step can use them to build a transparent prioritization aid without hiding average position or asking AI to invent the ranking.

Raw query table missing?

Confirm the available-date message appears before the query request. If it does, try a wider range with known search activity for the selected property.

Use this prompt if dates appear but query rows do not: Help me troubleshoot why my Search Analytics date query returns rows but my query-dimension request is empty.

✔️ Awesome, I've got everything!

Great work. Your program now authenticates safely and prints live Search Console query metrics in their original click-sorted order.

ⓧ I'd like to double check the full code

import csv
import json
import os
from datetime import date
from pathlib import Path

from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from openai import OpenAI

SCOPES = ["https://www.googleapis.com/auth/webmasters.readonly"]
CLIENT_SECRETS_FILE = Path("client_secrets.json")
MODEL = "gpt-6-astra"
QUERY_ROW_LIMIT = 100
SHORTLIST_SIZE = 3


def authenticate_search_console():
    if not CLIENT_SECRETS_FILE.exists():
        raise FileNotFoundError(
            f"Missing {CLIENT_SECRETS_FILE}. Copy your Google OAuth client file here."
        )

    flow = InstalledAppFlow.from_client_secrets_file(
        str(CLIENT_SECRETS_FILE),
        scopes=SCOPES,
    )
    credentials = flow.run_local_server()
    return build("searchconsole", "v1", credentials=credentials)


def choose_property(service):
    response = service.sites().list().execute()
    entries = response.get("siteEntry", [])
    sites = [
        entry["siteUrl"]
        for entry in entries
        if entry.get("permissionLevel") != "siteUnverifiedUser"
    ]

    if not sites:
        raise RuntimeError("No verified Search Console properties were returned.")

    print("\nAccessible Search Console properties:")
    for index, site_url in enumerate(sites, start=1):
        print(f"  {index}. {site_url}")

    while True:
        try:
            selection = int(input("Choose a property number: "))
            return sites[selection - 1]
        except (ValueError, IndexError):
            print("Enter one of the numbers shown above.")


def prompt_for_date(label):
    while True:
        value = input(f"{label} date (YYYY-MM-DD): ").strip()
        try:
            date.fromisoformat(value)
            return value
        except ValueError:
            print("Use YYYY-MM-DD, for example 2026-08-01.")


def execute_search_analytics_query(service, site_url, body):
    return (
        service.searchanalytics()
        .query(siteUrl=site_url, body=body)
        .execute()
    )


def verify_available_dates(service, site_url, start_date, end_date):
    response = execute_search_analytics_query(
        service,
        site_url,
        {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["date"],
        },
    )
    return response.get("rows", [])


def fetch_query_rows(service, site_url, start_date, end_date):
    response = execute_search_analytics_query(
        service,
        site_url,
        {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["query"],
            "rowLimit": QUERY_ROW_LIMIT,
        },
    )
    return response.get("rows", [])


def print_raw_rows(rows, limit=10):
    print("\nRaw query rows returned by Search Console")
    print(f"{'Query':40} {'Clicks':>8} {'Impr.':>10} {'CTR':>8} {'Pos.':>8}")
    print("-" * 80)
    for row in rows[:limit]:
        query = row["keys"][0][:40]
        print(
            f"{query:40} "
            f"{row['clicks']:8.0f} "
            f"{row['impressions']:10.0f} "
            f"{row['ctr'] * 100:7.2f}% "
            f"{row['position']:8.2f}"
        )


def main():
    service = authenticate_search_console()
    site_url = choose_property(service)
    start_date = prompt_for_date("Start")
    end_date = prompt_for_date("End")

    if date.fromisoformat(start_date) > date.fromisoformat(end_date):
        raise ValueError("Start date must not be after end date.")

    available_dates = verify_available_dates(
        service,
        site_url,
        start_date,
        end_date,
    )
    if not available_dates:
        raise RuntimeError("Search Console returned no data for that date range.")

    print(
        "\nAvailable data runs from "
        f"{available_dates[0]['keys'][0]} to {available_dates[-1]['keys'][0]}."
    )

    rows = fetch_query_rows(service, site_url, start_date, end_date)
    if not rows:
        raise RuntimeError("Search Console returned no query rows for that range.")

    print_raw_rows(rows)


if __name__ == "__main__":
    main()

How to use this reference

Compare each function plus the order of the definitions with your saved file. The final main() function should authenticate before selecting a property and requesting data.

You have a working live-data pull plus firsthand evidence of its ordering limitation. Next, you will turn these raw rows into an auditable SEO opportunity shortlist.

Rank and Export SEO Opportunities

The live rows you pulled from Google Search Console proved that your workflow can retrieve private search-performance data. You can now see the query metrics that drive the next decision.

Search Console orders query results by clicks. A transparent local heuristic can reveal high-impression rows with weak CTR while preserving every original metric for review.

In this step, get ready to:

• Calculate a missed-click proxy for every query row.
• Rank three candidates while keeping their original metrics visible.
• Export the complete scored dataset to an auditable CSV file.

Score each query transparently

The missed-click proxy estimates how many impressions did not become clicks. Future traffic remains outside this calculation.

• Return to seo_opportunity_finder.py in Visual Studio Code.
• Find the line that defines main().
• Add the scoring function directly above main() by pasting this code:

def score_opportunities(rows):
    opportunities = []
    for row in rows:
        impressions = float(row.get("impressions", 0))
        ctr = float(row.get("ctr", 0))
        opportunities.append(
            {
                "query": row["keys"][0],
                "clicks": round(float(row.get("clicks", 0)), 2),
                "impressions": round(impressions, 2),
                "ctr_percent": round(ctr * 100, 2),
                "position": round(float(row.get("position", 0)), 2),
                "missed_clicks_proxy": round(impressions * (1 - ctr), 2),
            }
        )

    return sorted(
        opportunities,
        key=lambda item: item["missed_clicks_proxy"],
        reverse=True,
    )

What does this code do?

• The opportunities list stores one normalized dictionary for every Search Console row.
• The ctr_percent value converts the API's decimal CTR into a percentage that is easier to inspect.
• The missed_clicks_proxy value multiplies impressions by one minus ctr.
• The final sort places the largest proxy values first while preserving clicks and position for human judgment.

• Save seo_opportunity_finder.py.
• Confirm that the live data workflow remains runnable by running this command in the terminal from earlier:

uv run python seo_opportunity_finder.py

What does this check prove?

The command runs your updated Python file in the existing uv environment. Seeing the live query table again proves that the scoring function parses cleanly without disrupting the Search Console workflow.

• Complete the browser authorization when prompted.
• Select an accessible property from the numbered list.
• Enter a valid start date.
• Enter a valid end date.

You should see the available date range followed by the first ten click-sorted query rows. Your existing data pull still works after the scoring logic was added.

Did the script stop after your edit?

Check that the closing parentheses in opportunities.append() match the reference code. Confirm that score_opportunities() sits above main() with no extra indentation.

Use the first line of the terminal output to locate the affected line in seo_opportunity_finder.py.

Help me fix my scoring function.

Export the complete scored dataset

A shortlist needs an audit trail. Exporting every scored row lets you inspect the ranking independently of the three candidates shown in the terminal.

• Find the end of score_opportunities() in seo_opportunity_finder.py.
• Add the export function below score_opportunities() by pasting this code:

def write_opportunities_csv(opportunities):
    output_path = Path("seo_opportunities.csv")
    fieldnames = [
        "query",
        "clicks",
        "impressions",
        "ctr_percent",
        "position",
        "missed_clicks_proxy",
    ]

    with output_path.open("w", newline="", encoding="utf-8") as output_file:
        writer = csv.DictWriter(output_file, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(opportunities)

    return output_path

What does this code do?

• The output_path points to seo_opportunities.csv inside your existing project folder.
• The fieldnames list fixes the column order so the source metrics stay beside the proxy.
• The DictWriter writes a header followed by every ranked opportunity.
• The returned path gives main() a concrete filename to report later.

• Scroll to the bottom of main().
• Find this line:

    print_raw_rows(rows)

Where does the export start?

This line is the current endpoint of your workflow. The new statements belong directly below it so they reuse the rows that Search Console already returned.

• Replace that line with this expanded block:

    print_raw_rows(rows)
    opportunities = score_opportunities(rows)
    csv_path = write_opportunities_csv(opportunities)

How is the CSV produced?

The first new line passes every API row through your scoring heuristic. The second new line writes the sorted results to seo_opportunities.csv.

• Save seo_opportunity_finder.py.
• Create the scored CSV by running the updated workflow:

uv run python seo_opportunity_finder.py

What does this run add?

The script retrieves fresh query rows before applying the local score. It then writes the complete sorted dataset to seo_opportunities.csv.

• Complete the browser authorization when prompted.
• Select an accessible property from the numbered list.
• Enter a valid start date.
• Enter a valid end date.
• Select seo_opportunities.csv from the file list in Visual Studio Code.

You should see the columns query, clicks, impressions, ctr_percent, position, and missed_clicks_proxy.

The first data row should hold the largest proxy value. The remaining rows should descend from there.

Can't find the CSV?

Confirm that you saved seo_opportunity_finder.py before rerunning it. Check that the terminal is still working inside ~/ai-seo-opportunity-finder.

Check that write_opportunities_csv(opportunities) appears after score_opportunities(rows) inside main().

Help me create the scored CSV.

Print the three strongest candidates

The CSV preserves the full ranking. A short terminal view brings the top three candidates forward while keeping impressions, CTR, average position, and the proxy visible.

• Find the end of write_opportunities_csv().
• Add the shortlist printer below it by pasting this code:

def print_shortlist(shortlist):
    print("\nLocal opportunity shortlist")
    print("The proxy is a prioritization aid, not a traffic forecast.")
    for index, item in enumerate(shortlist, start=1):
        print(
            f"  {index}. {item['query']} | "
            f"impressions={item['impressions']:.0f}, "
            f"CTR={item['ctr_percent']:.2f}%, "
            f"position={item['position']:.2f}, "
            f"proxy={item['missed_clicks_proxy']:.0f}"
        )

What does this code do?

• The heading labels the terminal output as a local shortlist.
• The second line states the boundary of the heuristic before anyone interprets the ranking.
• The loop prints each query with the metrics needed for human review.
• The enumerate() call numbers the candidates from one.

• Return to the final lines of main().
• Find this block:

    print_raw_rows(rows)
    opportunities = score_opportunities(rows)
    csv_path = write_opportunities_csv(opportunities)

Where does the shortlist connect?

The current block already holds the sorted opportunities and exported file path. The shortlist can now take the first three entries from that same ranked list.

• Replace that block with this completed local-ranking flow:

    print_raw_rows(rows)
    opportunities = score_opportunities(rows)
    csv_path = write_opportunities_csv(opportunities)
    shortlist = opportunities[:SHORTLIST_SIZE]
    print_shortlist(shortlist)
    print(f"Stopped before the paid request. CSV saved to {csv_path}.")

How does the final flow work?

The slice uses SHORTLIST_SIZE to select the first three scored rows. The next line prints those candidates with their review metrics.

The final message confirms that the CSV is saved. It also marks the boundary before the paid request added in the next step.

• Save seo_opportunity_finder.py.

This run ends before any paid OpenAI request. Your OPENAI_API_KEY remains available in the terminal without being used by this version.

Pause for a prediction: which queries do you think the proxy will move above the click leaders.

• Run the completed local ranking workflow with this command:

uv run python seo_opportunity_finder.py

What does this command run?

uv runs seo_opportunity_finder.py with the dependencies from your existing environment. The script retrieves live data before scoring and exporting it locally.

• Complete the browser authorization when prompted.
• Select an accessible property from the numbered list.
• Enter a valid start date.
• Enter a valid end date.

You should see three numbered queries under Local opportunity shortlist. Each query should show impressions, CTR, position, and proxy values.

The terminal should finish with a message confirming that the paid request was skipped. It should also confirm that seo_opportunities.csv was saved.

Shortlist missing or in the wrong order?

Confirm that score_opportunities() returns the result of sorted() with reverse=True.

Check that shortlist uses opportunities[:SHORTLIST_SIZE] before print_shortlist(shortlist) runs.

Help me debug my ranked shortlist.

✔️ Awesome, I've got everything!

Strong work. Your script now turns click-sorted Search Console rows into an auditable ranking without making a paid AI request.

ⓧ I'd like to double check the full code

import csv
import json
import os
from datetime import date
from pathlib import Path

from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from openai import OpenAI

SCOPES = ["https://www.googleapis.com/auth/webmasters.readonly"]
CLIENT_SECRETS_FILE = Path("client_secrets.json")
MODEL = "gpt-6-astra"
QUERY_ROW_LIMIT = 100
SHORTLIST_SIZE = 3


def authenticate_search_console():
    if not CLIENT_SECRETS_FILE.exists():
        raise FileNotFoundError(
            f"Missing {CLIENT_SECRETS_FILE}. Copy your Google OAuth client file here."
        )

    flow = InstalledAppFlow.from_client_secrets_file(
        str(CLIENT_SECRETS_FILE),
        scopes=SCOPES,
    )
    credentials = flow.run_local_server()
    return build("searchconsole", "v1", credentials=credentials)


def choose_property(service):
    response = service.sites().list().execute()
    entries = response.get("siteEntry", [])
    sites = [
        entry["siteUrl"]
        for entry in entries
        if entry.get("permissionLevel") != "siteUnverifiedUser"
    ]

    if not sites:
        raise RuntimeError("No verified Search Console properties were returned.")

    print("\nAccessible Search Console properties:")
    for index, site_url in enumerate(sites, start=1):
        print(f"  {index}. {site_url}")

    while True:
        try:
            selection = int(input("Choose a property number: "))
            return sites[selection - 1]
        except (ValueError, IndexError):
            print("Enter one of the numbers shown above.")


def prompt_for_date(label):
    while True:
        value = input(f"{label} date (YYYY-MM-DD): ").strip()
        try:
            date.fromisoformat(value)
            return value
        except ValueError:
            print("Use YYYY-MM-DD, for example 2026-08-01.")


def execute_search_analytics_query(service, site_url, body):
    return (
        service.searchanalytics()
        .query(siteUrl=site_url, body=body)
        .execute()
    )


def verify_available_dates(service, site_url, start_date, end_date):
    response = execute_search_analytics_query(
        service,
        site_url,
        {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["date"],
        },
    )
    return response.get("rows", [])


def fetch_query_rows(service, site_url, start_date, end_date):
    response = execute_search_analytics_query(
        service,
        site_url,
        {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["query"],
            "rowLimit": QUERY_ROW_LIMIT,
        },
    )
    return response.get("rows", [])


def print_raw_rows(rows, limit=10):
    print("\nRaw query rows returned by Search Console")
    print(f"{'Query':40} {'Clicks':>8} {'Impr.':>10} {'CTR':>8} {'Pos.':>8}")
    print("-" * 80)
    for row in rows[:limit]:
        query = row["keys"][0][:40]
        print(
            f"{query:40} "
            f"{row['clicks']:8.0f} "
            f"{row['impressions']:10.0f} "
            f"{row['ctr'] * 100:7.2f}% "
            f"{row['position']:8.2f}"
        )


def score_opportunities(rows):
    opportunities = []
    for row in rows:
        impressions = float(row.get("impressions", 0))
        ctr = float(row.get("ctr", 0))
        opportunities.append(
            {
                "query": row["keys"][0],
                "clicks": round(float(row.get("clicks", 0)), 2),
                "impressions": round(impressions, 2),
                "ctr_percent": round(ctr * 100, 2),
                "position": round(float(row.get("position", 0)), 2),
                "missed_clicks_proxy": round(impressions * (1 - ctr), 2),
            }
        )

    return sorted(
        opportunities,
        key=lambda item: item["missed_clicks_proxy"],
        reverse=True,
    )


def write_opportunities_csv(opportunities):
    output_path = Path("seo_opportunities.csv")
    fieldnames = [
        "query",
        "clicks",
        "impressions",
        "ctr_percent",
        "position",
        "missed_clicks_proxy",
    ]

    with output_path.open("w", newline="", encoding="utf-8") as output_file:
        writer = csv.DictWriter(output_file, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(opportunities)

    return output_path


def print_shortlist(shortlist):
    print("\nLocal opportunity shortlist")
    print("The proxy is a prioritization aid, not a traffic forecast.")
    for index, item in enumerate(shortlist, start=1):
        print(
            f"  {index}. {item['query']} | "
            f"impressions={item['impressions']:.0f}, "
            f"CTR={item['ctr_percent']:.2f}%, "
            f"position={item['position']:.2f}, "
            f"proxy={item['missed_clicks_proxy']:.0f}"
        )


def main():
    service = authenticate_search_console()
    site_url = choose_property(service)
    start_date = prompt_for_date("Start")
    end_date = prompt_for_date("End")

    if date.fromisoformat(start_date) > date.fromisoformat(end_date):
        raise ValueError("Start date must not be after end date.")

    available_dates = verify_available_dates(
        service,
        site_url,
        start_date,
        end_date,
    )
    if not available_dates:
        raise RuntimeError("Search Console returned no data for that date range.")

    print(
        "\nAvailable data runs from "
        f"{available_dates[0]['keys'][0]} to {available_dates[-1]['keys'][0]}."
    )

    rows = fetch_query_rows(service, site_url, start_date, end_date)
    if not rows:
        raise RuntimeError("Search Console returned no query rows for that range.")

    print_raw_rows(rows)
    opportunities = score_opportunities(rows)
    csv_path = write_opportunities_csv(opportunities)
    shortlist = opportunities[:SHORTLIST_SIZE]
    print_shortlist(shortlist)
    print(f"Stopped before the paid request. CSV saved to {csv_path}.")


if __name__ == "__main__":
    main()

Your local ranking layer now produces an auditable CSV. Next, you'll turn its three candidates into structured content briefs behind an explicit cost gate.

Generate Structured AI Content Briefs

Your scored Google Search Console rows already identify three queries worth reviewing. The shortlist now needs a consistent content direction that an SEO lead can evaluate.

In this step, the OpenAI Responses API turns those rows into reviewable briefs through Structured Outputs. A strict JSON Schema gives every brief the same required sections.

In this step, get ready to:

• Define the schema for three complete SEO briefs.
• Send only the shortlisted metric rows through one confirmed AI request.
• Export the source metrics with the structured recommendations to a Markdown report.

Define the strict brief schema

The schema controls the shape of the AI response before your program parses it. It requires three briefs with a fixed set of fields.

The new function contains one nested Python construct. You will paste its two parts consecutively before saving the file.

• In the open seo_opportunity_finder.py tab, place your cursor on the blank line below print_shortlist().
• Start the new brief_schema() function by pasting this first part:

def brief_schema():
    brief_item = {
        "type": "object",
        "properties": {
            "query": {"type": "string"},
            "search_intent": {
                "type": "string",
                "enum": [
                    "informational",
                    "commercial",
                    "transactional",
                    "navigational",
                    "local",
                ],
            },
            "opportunity_rationale": {"type": "string"},
            "recommended_page_type": {"type": "string"},
            "working_title": {"type": "string"},
            "outline": {
                "type": "array",
                "items": {"type": "string"},
                "minItems": 3,
                "maxItems": 3,
            },
            "cta": {"type": "string"},
        },

What Does This Schema Part Define?

• The properties object names every field that one brief can contain.
• The search_intent enum limits intent to five recognized categories.
• The outline rules require exactly three sections.

• Continue directly below the final line by pasting the rest of brief_schema():

        "required": [
            "query",
            "search_intent",
            "opportunity_rationale",
            "recommended_page_type",
            "working_title",
            "outline",
            "cta",
        ],
        "additionalProperties": False,
    }

    return {
        "type": "object",
        "properties": {
            "briefs": {
                "type": "array",
                "items": brief_item,
                "minItems": SHORTLIST_SIZE,
                "maxItems": SHORTLIST_SIZE,
            }
        },
        "required": ["briefs"],
        "additionalProperties": False,
    }

How Does the Schema Stay Strict?

• The required lists prevent briefs from omitting a field.
• The additionalProperties settings prevent unplanned fields from appearing.
• The two SHORTLIST_SIZE limits require exactly three briefs.

• Save seo_opportunity_finder.py.
• Confirm the updated file parses by running the existing workflow:

uv run python seo_opportunity_finder.py

What Does This Check Prove?

Python loads the new schema function while running the existing workflow. Reaching the shortlist confirms that both schema parts form valid Python.

• Complete the browser authorization flow.
• Select one accessible Search Console property.
• Enter a valid start date.
• Enter a valid end date.

You should see the ranked shortlist followed by the existing stop message. No paid request runs during this check.

Seeing a Syntax Error?

Check that the second schema snippet begins directly after the first snippet. Confirm that both parts stay indented inside brief_schema().

Compare each closing brace with the snippets above. A missing brace prevents Python from reaching the Search Console flow.

Help me fix my brief schema syntax.

Generate and validate the briefs

The generator sends only the three shortlisted metric rows to OpenAI. It also checks the response status before parsing the returned text.

This function is another nested construct. Paste all three parts consecutively before saving it.

• Place your cursor on the blank line below brief_schema().
• Start generate_briefs() with the credential check and system instruction:

def generate_briefs(shortlist):
    if not os.environ.get("OPENAI_API_KEY"):
        raise RuntimeError("OPENAI_API_KEY is not set in this terminal.")

    client = OpenAI()
    response = client.responses.create(
        model=MODEL,
        store=False,
        max_output_tokens=2500,
        input=[
            {
                "role": "system",
                "content": (
                    "You are an SEO strategist. Use only the supplied metrics. "
                    "Treat missed_clicks_proxy as a prioritization heuristic, not a "
                    "forecast or promise. Produce one concise content brief per query."
                ),
            },

What Does This First Part Control?

• The environment check stops the function if the current terminal cannot access OPENAI_API_KEY.
• The OpenAI() client reads the key from that environment variable.
• The system instruction limits the analysis to the supplied metrics.
• The store=False setting disables default response storage.

• Continue the same function by adding the shortlist payload and strict response format:

            {
                "role": "user",
                "content": json.dumps(
                    {
                        "task": "Create reviewable SEO content briefs.",
                        "candidates": shortlist,
                    },
                    indent=2,
                ),
            },
        ],
        text={
            "format": {
                "type": "json_schema",
                "name": "seo_opportunity_briefs",
                "strict": True,
                "schema": brief_schema(),
            }
        },
    )

What Gets Sent to the Model?

The user payload contains the task plus the three items in shortlist. Your complete CSV stays local.

The text.format configuration applies the schema returned by brief_schema().

• Finish generate_briefs() with the response checks and JSON parsing:

    if response.status != "completed":
        raise RuntimeError(f"OpenAI response ended with status: {response.status}")
    if not response.output_text:
        raise RuntimeError("OpenAI returned no text output.")

    return json.loads(response.output_text)["briefs"]

Why Check Before Parsing?

The status check rejects responses that did not complete. The text check prevents an empty result from reaching json.loads().

Successful output becomes a Python list of three briefs.

• Save seo_opportunity_finder.py.
• Confirm the completed generator parses by rerunning the workflow:

uv run python seo_opportunity_finder.py

What Should This Run Reach?

The workflow should complete its local scoring path and print the existing stop message. The new generator remains inactive until the cost gate is wired into main().

• Complete the browser authorization flow.
• Select one accessible property.
• Enter a valid start date.
• Enter a valid end date.

You should reach the local shortlist with no syntax error. The run still stops before any paid request.

Does the Generator Fail to Parse?

Check that the user payload stays inside the open input list from the first snippet. Confirm that the response checks align with the first line of generate_briefs().

Help me repair my generator function.

Write the report and add the cost gate

A valid model response still needs source context before a human can review it. The report combines each brief with the metrics that led to its selection.

The final control belongs in main(). The model runs only when the learner enters the required confirmation.

• Place your cursor on the blank line below generate_briefs().
• Start write_markdown_report() with the report heading and method statement:

def write_markdown_report(site_url, start_date, end_date, shortlist, briefs):
    metrics_by_query = {item["query"]: item for item in shortlist}
    lines = [
        "# AI SEO Opportunity Report",
        "",
        f"Property: {site_url}",
        f"Date range: {start_date} to {end_date}",
        "",
        (
            "Method: Candidates are ranked by impressions multiplied by one minus CTR. "
            "This missed-click proxy supports prioritization and does not forecast traffic."
        ),
        "",
    ]

What Starts the Report?

• The query lookup connects each returned brief to its source metrics.
• The first report lines record the selected property and date range.
• The method statement keeps the proxy's purpose visible to every reviewer.

• Continue the function by rendering each brief with its source metrics:

    for index, brief in enumerate(briefs, start=1):
        metrics = metrics_by_query.get(brief["query"], {})
        lines.extend(
            [
                f"## {index}. {brief['query']}",
                "",
                f"- Clicks: {metrics.get('clicks', 'n/a')}",
                f"- Impressions: {metrics.get('impressions', 'n/a')}",
                f"- CTR: {metrics.get('ctr_percent', 'n/a')}%",
                f"- Average position: {metrics.get('position', 'n/a')}",
                f"- Missed-click proxy: {metrics.get('missed_clicks_proxy', 'n/a')}",
                f"- Search intent: {brief['search_intent']}",
                f"- Recommended page type: {brief['recommended_page_type']}",
                "",
                f"Rationale: {brief['opportunity_rationale']}",
                "",
                f"Working title: {brief['working_title']}",
                "",
                "Outline:",
                *[f"1. {item}" for item in brief["outline"]],
                "",
                f"CTA: {brief['cta']}",
                "",
            ]
        )

How Is Each Brief Rendered?

Each report section begins with the query and its original performance metrics. The AI fields follow in a consistent review order.

The source data stays visible beside the recommendation. That makes human evaluation possible without switching back to the CSV.

• Finish the function by writing the assembled lines to seo_briefs.md:

    output_path = Path("seo_briefs.md")
    output_path.write_text("\n".join(lines), encoding="utf-8")
    return output_path

What Does the Final Report Step Do?

The function joins the report lines with newline characters. It writes the finished Markdown document using UTF-8 encoding.

• In main(), find the existing stop message:

    print(f"Stopped before the paid request. CSV saved to {csv_path}.")

What Does the Existing Line Do?

This line ends the current local-only workflow after saving the scored CSV. You will replace it with the explicit confirmation path.

• Replace that line with the cost gate and report generation path below:

    confirmation = input(
        "\nType AI to send these three metric rows to OpenAI, or press Enter to stop: "
    )
    if confirmation.strip().upper() != "AI":
        print(f"Stopped before the paid request. CSV saved to {csv_path}.")
        return

    briefs = generate_briefs(shortlist)
    markdown_path = write_markdown_report(
        site_url,
        start_date,
        end_date,
        shortlist,
        briefs,
    )
    print(f"\nSaved scored opportunities to {csv_path}.")
    print(f"Saved structured AI briefs to {markdown_path}.")

How Does the Cost Gate Work?

The program normalizes the confirmation to uppercase. Any input except AI stops the workflow before the model call.

A confirmed run generates the briefs and writes the report. The program only prints the two output paths after both files are ready.

• Save seo_opportunity_finder.py.

✔️ Awesome, I've got everything!

Great. Double check that you saved seo_opportunity_finder.py before running the finished workflow.

ⓧ I'd like to double check the full code

import csv
import json
import os
from datetime import date
from pathlib import Path

from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from openai import OpenAI

SCOPES = ["https://www.googleapis.com/auth/webmasters.readonly"]
CLIENT_SECRETS_FILE = Path("client_secrets.json")
MODEL = "gpt-6-astra"
QUERY_ROW_LIMIT = 100
SHORTLIST_SIZE = 3


def authenticate_search_console():
    if not CLIENT_SECRETS_FILE.exists():
        raise FileNotFoundError(
            f"Missing {CLIENT_SECRETS_FILE}. Copy your Google OAuth client file here."
        )

    flow = InstalledAppFlow.from_client_secrets_file(
        str(CLIENT_SECRETS_FILE),
        scopes=SCOPES,
    )
    credentials = flow.run_local_server()
    return build("searchconsole", "v1", credentials=credentials)


def choose_property(service):
    response = service.sites().list().execute()
    entries = response.get("siteEntry", [])
    sites = [
        entry["siteUrl"]
        for entry in entries
        if entry.get("permissionLevel") != "siteUnverifiedUser"
    ]

    if not sites:
        raise RuntimeError("No verified Search Console properties were returned.")

    print("\nAccessible Search Console properties:")
    for index, site_url in enumerate(sites, start=1):
        print(f"  {index}. {site_url}")

    while True:
        try:
            selection = int(input("Choose a property number: "))
            return sites[selection - 1]
        except (ValueError, IndexError):
            print("Enter one of the numbers shown above.")


def prompt_for_date(label):
    while True:
        value = input(f"{label} date (YYYY-MM-DD): ").strip()
        try:
            date.fromisoformat(value)
            return value
        except ValueError:
            print("Use YYYY-MM-DD, for example 2026-08-01.")


def execute_search_analytics_query(service, site_url, body):
    return (
        service.searchanalytics()
        .query(siteUrl=site_url, body=body)
        .execute()
    )


def verify_available_dates(service, site_url, start_date, end_date):
    response = execute_search_analytics_query(
        service,
        site_url,
        {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["date"],
        },
    )
    return response.get("rows", [])


def fetch_query_rows(service, site_url, start_date, end_date):
    response = execute_search_analytics_query(
        service,
        site_url,
        {
            "startDate": start_date,
            "endDate": end_date,
            "dimensions": ["query"],
            "rowLimit": QUERY_ROW_LIMIT,
        },
    )
    return response.get("rows", [])


def print_raw_rows(rows, limit=10):
    print("\nRaw query rows returned by Search Console")
    print(f"{'Query':40} {'Clicks':>8} {'Impr.':>10} {'CTR':>8} {'Pos.':>8}")
    print("-" * 80)
    for row in rows[:limit]:
        query = row["keys"][0][:40]
        print(
            f"{query:40} "
            f"{row['clicks']:8.0f} "
            f"{row['impressions']:10.0f} "
            f"{row['ctr'] * 100:7.2f}% "
            f"{row['position']:8.2f}"
        )


def score_opportunities(rows):
    opportunities = []
    for row in rows:
        impressions = float(row.get("impressions", 0))
        ctr = float(row.get("ctr", 0))
        opportunities.append(
            {
                "query": row["keys"][0],
                "clicks": round(float(row.get("clicks", 0)), 2),
                "impressions": round(impressions, 2),
                "ctr_percent": round(ctr * 100, 2),
                "position": round(float(row.get("position", 0)), 2),
                "missed_clicks_proxy": round(impressions * (1 - ctr), 2),
            }
        )

    return sorted(
        opportunities,
        key=lambda item: item["missed_clicks_proxy"],
        reverse=True,
    )


def write_opportunities_csv(opportunities):
    output_path = Path("seo_opportunities.csv")
    fieldnames = [
        "query",
        "clicks",
        "impressions",
        "ctr_percent",
        "position",
        "missed_clicks_proxy",
    ]

    with output_path.open("w", newline="", encoding="utf-8") as output_file:
        writer = csv.DictWriter(output_file, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(opportunities)

    return output_path


def print_shortlist(shortlist):
    print("\nLocal opportunity shortlist")
    print("The proxy is a prioritization aid, not a traffic forecast.")
    for index, item in enumerate(shortlist, start=1):
        print(
            f"  {index}. {item['query']} | "
            f"impressions={item['impressions']:.0f}, "
            f"CTR={item['ctr_percent']:.2f}%, "
            f"position={item['position']:.2f}, "
            f"proxy={item['missed_clicks_proxy']:.0f}"
        )


def brief_schema():
    brief_item = {
        "type": "object",
        "properties": {
            "query": {"type": "string"},
            "search_intent": {
                "type": "string",
                "enum": [
                    "informational",
                    "commercial",
                    "transactional",
                    "navigational",
                    "local",
                ],
            },
            "opportunity_rationale": {"type": "string"},
            "recommended_page_type": {"type": "string"},
            "working_title": {"type": "string"},
            "outline": {
                "type": "array",
                "items": {"type": "string"},
                "minItems": 3,
                "maxItems": 3,
            },
            "cta": {"type": "string"},
        },
        "required": [
            "query",
            "search_intent",
            "opportunity_rationale",
            "recommended_page_type",
            "working_title",
            "outline",
            "cta",
        ],
        "additionalProperties": False,
    }

    return {
        "type": "object",
        "properties": {
            "briefs": {
                "type": "array",
                "items": brief_item,
                "minItems": SHORTLIST_SIZE,
                "maxItems": SHORTLIST_SIZE,
            }
        },
        "required": ["briefs"],
        "additionalProperties": False,
    }


def generate_briefs(shortlist):
    if not os.environ.get("OPENAI_API_KEY"):
        raise RuntimeError("OPENAI_API_KEY is not set in this terminal.")

    client = OpenAI()
    response = client.responses.create(
        model=MODEL,
        store=False,
        max_output_tokens=2500,
        input=[
            {
                "role": "system",
                "content": (
                    "You are an SEO strategist. Use only the supplied metrics. "
                    "Treat missed_clicks_proxy as a prioritization heuristic, not a "
                    "forecast or promise. Produce one concise content brief per query."
                ),
            },
            {
                "role": "user",
                "content": json.dumps(
                    {
                        "task": "Create reviewable SEO content briefs.",
                        "candidates": shortlist,
                    },
                    indent=2,
                ),
            },
        ],
        text={
            "format": {
                "type": "json_schema",
                "name": "seo_opportunity_briefs",
                "strict": True,
                "schema": brief_schema(),
            }
        },
    )

    if response.status != "completed":
        raise RuntimeError(f"OpenAI response ended with status: {response.status}")
    if not response.output_text:
        raise RuntimeError("OpenAI returned no text output.")

    return json.loads(response.output_text)["briefs"]


def write_markdown_report(site_url, start_date, end_date, shortlist, briefs):
    metrics_by_query = {item["query"]: item for item in shortlist}
    lines = [
        "# AI SEO Opportunity Report",
        "",
        f"Property: {site_url}",
        f"Date range: {start_date} to {end_date}",
        "",
        (
            "Method: Candidates are ranked by impressions multiplied by one minus CTR. "
            "This missed-click proxy supports prioritization and does not forecast traffic."
        ),
        "",
    ]

    for index, brief in enumerate(briefs, start=1):
        metrics = metrics_by_query.get(brief["query"], {})
        lines.extend(
            [
                f"## {index}. {brief['query']}",
                "",
                f"- Clicks: {metrics.get('clicks', 'n/a')}",
                f"- Impressions: {metrics.get('impressions', 'n/a')}",
                f"- CTR: {metrics.get('ctr_percent', 'n/a')}%",
                f"- Average position: {metrics.get('position', 'n/a')}",
                f"- Missed-click proxy: {metrics.get('missed_clicks_proxy', 'n/a')}",
                f"- Search intent: {brief['search_intent']}",
                f"- Recommended page type: {brief['recommended_page_type']}",
                "",
                f"Rationale: {brief['opportunity_rationale']}",
                "",
                f"Working title: {brief['working_title']}",
                "",
                "Outline:",
                *[f"1. {item}" for item in brief["outline"]],
                "",
                f"CTA: {brief['cta']}",
                "",
            ]
        )

    output_path = Path("seo_briefs.md")
    output_path.write_text("\n".join(lines), encoding="utf-8")
    return output_path


def main():
    service = authenticate_search_console()
    site_url = choose_property(service)
    start_date = prompt_for_date("Start")
    end_date = prompt_for_date("End")

    if date.fromisoformat(start_date) > date.fromisoformat(end_date):
        raise ValueError("Start date must not be after end date.")

    available_dates = verify_available_dates(
        service,
        site_url,
        start_date,
        end_date,
    )
    if not available_dates:
        raise RuntimeError("Search Console returned no data for that date range.")

    print(
        "\nAvailable data runs from "
        f"{available_dates[0]['keys'][0]} to {available_dates[-1]['keys'][0]}."
    )

    rows = fetch_query_rows(service, site_url, start_date, end_date)
    if not rows:
        raise RuntimeError("Search Console returned no query rows for that range.")

    print_raw_rows(rows)
    opportunities = score_opportunities(rows)
    csv_path = write_opportunities_csv(opportunities)
    shortlist = opportunities[:SHORTLIST_SIZE]
    print_shortlist(shortlist)

    confirmation = input(
        "\nType AI to send these three metric rows to OpenAI, or press Enter to stop: "
    )
    if confirmation.strip().upper() != "AI":
        print(f"Stopped before the paid request. CSV saved to {csv_path}.")
        return

    briefs = generate_briefs(shortlist)
    markdown_path = write_markdown_report(
        site_url,
        start_date,
        end_date,
        shortlist,
        briefs,
    )
    print(f"\nSaved scored opportunities to {csv_path}.")
    print(f"Saved structured AI briefs to {markdown_path}.")


if __name__ == "__main__":
    main()

Use this full-file view to compare function order, indentation, and closing brackets. It matches the finished seo_opportunity_finder.py file.

Before you run the finished workflow, do you expect seo_briefs.md to exist before or after you type AI?

• Start the finished workflow by running:

uv run python seo_opportunity_finder.py

What Does This Run Do?

The command fetches live Search Console rows and rebuilds the scored CSV. It then pauses at the explicit AI confirmation prompt.

• Complete the browser authorization flow.
• Select the Search Console property you want to analyze.
• Enter a valid start date.
• Enter a valid end date.
• Review the three shortlisted queries in the terminal.

Before You Confirm

Typing AI sends one paid OpenAI request. Only the three shortlisted metric rows are included.

Pressing Enter without that confirmation keeps the run local and saves only the CSV.

• Type AI at the confirmation prompt.
• Keep the terminal session open while the model creates the three briefs.

A short pause is expected while the model produces the structured response. When it finishes, you should see messages naming seo_opportunities.csv and seo_briefs.md.

• Return to the Explorer sidebar in Visual Studio Code.
• Select seo_briefs.md to inspect the generated report.

You should see the selected property and date range at the top. Three numbered briefs should each include source metrics, search intent, rationale, page type, title, a three-part outline, and a CTA.

Report Missing or Request Failed?

If the terminal reports OPENAI_API_KEY is not set in this terminal. return to the terminal session where you exported the key. The key remains available only in that session.

If the workflow stops before the request, confirm that you entered exactly AI at the cost gate. If a response-status error appears, keep the status value for troubleshooting.

Help me troubleshoot the structured brief request.

That completes the workflow. Your local scoring logic now feeds three transparent candidates into one confirmed AI request and produces a report ready for human review.

Secret mission

Audit the AI Like an SEO Lead

Audit the brief associated with the highest missed-click proxy before anyone acts on it. You will compare the recommendation with its source metrics. You will record an evidence-based verdict plus one reusable prompt improvement.

Clean Up Your Resources

Clean Up Your Resources

Only future OpenAI API requests can add cost. Choose whether to keep your resources available, pause local API access, or delete the project folder and API key.

Cost warning

An active API key does not make requests on its own. Any future run that passes the AI confirmation gate can incur usage-based charges.

• Choose Pause if you want to keep the project without leaving the key in your current terminal session.
• Choose Delete if you no longer need the workflow or its OpenAI API key.

Resources you used:

• Local project folder ~/ai-seo-opportunity-finder, including seo_opportunity_finder.py, requirements.txt, .venv/, and the installed Python packages.
• Protected local credential file client_secrets.json, which remains excluded by .gitignore.
• Generated analysis files seo_opportunities.csv, seo_briefs.md, and review-notes.md.
• The OPENAI_API_KEY value exported in your current terminal session.
• The OpenAI API key stored in your API Keys dashboard.

Keep everything running

No action is needed. Choose this option if you want to generate more reports or improve the scoring and briefing workflow.

• Keep the ~/ai-seo-opportunity-finder folder with its source files and generated reports.
• Keep the OpenAI API key active if you plan to make more deliberate API requests.
• Keep the current terminal session open if you want OPENAI_API_KEY to remain available.
• Monitor your OpenAI API usage when you generate another report.

Pause - I'll come back to this later

Clear the key from your current shell to prevent this terminal from making another AI request. Your project files and generated reports remain available for later.

• Remove the exported API key from the current terminal session by running this command:

unset OPENAI_API_KEY

What does this command do?

The command clears OPENAI_API_KEY from the current shell. The API key in your OpenAI account remains active.

• Close the current terminal window when you finish working.
• Keep the OpenAI API key active if you expect to resume this project.
• Export the API key in a new terminal session before generating another AI report.

Delete - I don't want to use this again

Deleting these resources is permanent, so save any reports you still need first. Your existing Google Cloud project and verified Google Search Console property stay untouched.

• Return to the OpenAI API Keys dashboard from earlier.
• Remove the API key you created for this project.
• Delete the local project folder by running this command:

rm -rf ~/ai-seo-opportunity-finder

What does this command remove?

This permanently removes ~/ai-seo-opportunity-finder from your Linux device. The source code, virtual environment, protected client credentials, CSV data, AI briefs, and review notes are deleted together.

• Close the current terminal window to clear the exported OPENAI_API_KEY value from that shell session.
• Leave your existing Google Cloud project in place.
• Leave your verified Google Search Console property in place.

Nice Work!

Nice Work!

You made it! You built a read-only Google Search Console workflow that turns live query performance data into reviewable SEO content briefs.

You've learned how to:

• Built a read-only OAuth 2.0 workflow that selects an accessible Search Console property from the API response. The workflow verifies date availability before retrieving query metrics.
• Created an auditable opportunity shortlist with a transparent missed-click proxy. The CSV export preserves every source metric for human review.
• Used the OpenAI Responses API with strict Structured Outputs after an explicit cost confirmation. The Markdown report contains three schema-validated content briefs beside their source metrics.
• Secret Mission: Audited the highest-ranked AI brief as an SEO lead using its source metrics. The review-notes.md record gives one Keep, Revise, or Reject verdict. It captures a reusable prompt improvement without treating the missed-click proxy as a traffic forecast.

Ready to quiz yourself?