I Turned 16 Months of Google Search Console Data Into a Vector DB

Analyzing organic keywords in CSV spreadsheets is slow, linear, and outdated. If you are managing a site with thousands of ranking queries, traditional Excel filters make it incredibly difficult to spot macro trends, keyword cannibalization, or thematic opportunities.

To solve this, I built a system that syncs 16 months of Google Search Console (GSC) query data into a local vector database. By embedding these search queries, we can perform semantic querying and automated clustering.

Here is the technical architecture, implementation steps, and how you can query your metrics using natural language.

---

The Architecture Overview

The system runs locally and consists of four main layers:

1. Extraction: A Python script pulling daily keyword metrics (queries, impressions, clicks, CTR, and average position) from the Google Search Console API.
2. Vectorization: Processing queries through OpenAI’s text-embedding-3-small model to generate 1536-dimensional dense vector representations.
3. Storage: Storing both vectors and performance metadata in ChromaDB, an open-source vector database.
4. Agent UI: A local Streamlit interface that uses LangChain and GPT-4o to write SQL/metadata queries against the database based on conversational prompts.

---

Step-by-Step Implementation

Step 1: Querying the GSC API

We use the official Google API client to retrieve granular query-level data. Here is how we initialize the request and build our data structures:

from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials

def fetch_gsc_data(property_url, start_date, end_date):
    creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', [
        'https://www.googleapis.com/auth/webmasters.readonly'
    ])
    service = build('webmasters', 'v3', credentials=creds)
    
    request = {
        'startDate': start_date,
        'endDate': end_date,
        'dimensions': ['query', 'page'],
        'rowLimit': 25000
    }
    response = service.searchanalytics().query(siteUrl=property_url, body=request).execute()
    return response.get('rows', [])

Step 2: Generating Embeddings & Storing in ChromaDB

Once the rows are fetched, we format the queries and insert them as documents. ChromaDB stores the text query alongside a metadata dictionary containing clicks, impressions, CTR, and positions.

import chromadb
from chromadb.utils import embedding_functions

# Initialize local ChromaDB client
chroma_client = chromadb.PersistentClient(path="./gsc_vector_db")
openai_ef = embedding_functions.OpenAIEmbeddingFunction(
    api_key="your-api-key",
    model_name="text-embedding-3-small"
)

collection = chroma_client.get_or_create_collection(
    name="gsc_queries", 
    embedding_function=openai_ef
)

# Batch add documents
for i, row in enumerate(gsc_rows):
    query_text = row['keys'][0]
    target_page = row['keys'][1]
    
    collection.add(
        documents=[query_text],
        metadatas=[{
            "page": target_page,
            "clicks": row['clicks'],
            "impressions": row['impressions'],
            "ctr": row['ctr'],
            "position": row['position']
        }],
        ids=[f"id_{i}"]
    )

---

Semantic Querying Examples

Now that the data is vectorized, we can query our search metrics semantically instead of matching exact strings.

Example 1: Finding Cannibalization

• Prompt: “Find all queries related to ‘crawling tools’ ranking on multiple pages.”
• How it works: The vector database finds all queries semantically similar to “crawling tools” (e.g. “site auditing scraper”, “python spider library”). The agent then analyzes the metadata to see if different URLs are returned for these similar queries, indicating keyword cannibalization.

Example 2: Spotting Low-Hanging Opportunities

• Prompt: “What are my rising informational questions that are ranking on page 2?”
• How it works: The agent performs a vector search for question prefixes (e.g., “how to”, “why does”, “what is”) and filters the metadata where position is between 11 and 20.

---

The Benefits of Semantic SEO Databases

1. Thematic Clustering: Automatically group search terms into semantic clusters. This helps you identify content gaps (e.g. discovering that users are searching for “headless crawler auth” but you only have basic crawling tutorials).
2. Trend Mapping: By comparing vector spaces month-over-month, you can visually track how search interest shifts from informational queries to transactional ones.
3. Intent Identification: Easily classify keywords into intent categories using semantic similarity classifiers rather than complex regular expressions.

Moving your search console data into a vector database turns static metrics into a conversational intelligence tool, helping you make content decisions faster and with higher precision.