SEO Reports & Keyword Analysis Using Pandas

Search engine optimization (SEO) and content audits require parsing real-world search telemetry. In this tutorial, learn how to ingest messy Google Analytics 4 (GA4) traffic exports, evaluate Google Search Console (GSC) ranking queries using regular expressions, extract keyword frequency distributions with stack().value_counts(), and automate XML sitemap production.

Google Analytics 4 (GA4) Search Traffic Ingestion 🔝

When you download organic landing page reports from Google Analytics 4, the CSV contains header metadata rows before the tabular records:

To load the raw report cleanly, configure pd.read_csv() with skiprows, specific column indices via usecols, and clean column names via names:

import pandas as pd
from sqlalchemy import create_engine

# Define target columns and ingest CSV while skipping metadata headers
custom_headers = ["page", "clicks", "imp", "ctr", "position", "tm"]
csv_path = "data/y24_09.csv"

# Ingest using column subsets and assign custom names
df = pd.read_csv(
    csv_path,
    skiprows=11,
    usecols=[0, 1, 2, 3, 4, 8],
    names=custom_headers,
    index_col=0
)

# Data cleaning: round metrics and filter out URL query strings
df["position"] = df["position"].round(2)
df["ctr"] = df["ctr"].round(2)
df["month"] = "24-Sep"

# Exclude URLs containing question mark query parameters
df_clean = df[~df.index.str.contains(r"\?", na=False)]
print(df_clean.head())

# Export to local SQLite database using to_sql()
engine = create_engine("sqlite:///analytics.db")
df_clean.to_sql("y_all", con=engine, if_exists="append")

Analyzing Search Console Queries with Regex 🔝

From Google Search Console, navigate to Performance > Search results > Export > Download CSV to obtain Queries.csv:

To isolate queries containing target keywords, evaluate whether you require an OR match (any term appears) or an AND match (all terms must appear in any order):

import pandas as pd

df_queries = pd.read_csv("Queries.csv")
keywords = ["tkinter", "color"]

# 1. OR Combination: queries containing 'tkinter' OR 'color'
or_regex = "|".join(keywords)
df_or = df_queries[df_queries["queries"].str.contains(or_regex, case=False, na=False)]

# 2. AND Combination: positive lookahead requiring all terms
and_regex = "".join([f"(?=.*{w})" for w in keywords])
df_and = df_queries[df_queries["queries"].str.contains(and_regex, case=False, na=False)]

# 3. Striking distance filter: average position between 6 and 10 (Page 1 bottom)
df_striking = df_and[df_and["Position"].between(6.0, 10.0)]
print(f"Striking Distance Queries (Pos 6-10): {len(df_striking)}")

Keyword Frequency Extraction with stack() & value_counts() 🔝

To discover recurring search topics and identify high-authority terms, split all query sentences into tokens, reshape the 2D matrix into a 1D Series using stack(), and compute term counts using value_counts():

import pandas as pd

df_queries = pd.DataFrame({
    "queries": [
        "tkinter button color",
        "pandas dataframe plot",
        "tkinter label color background",
        "python pandas date range",
        "tkinter combobox select event"
    ]
})

# Split query strings into words, stack into a single column, and count occurrences
word_counts = (df_queries["queries"]
    .str.split(expand=True)
    .stack()
    .value_counts())

print("Top Keywords by Frequency:
", word_counts.head(5))
assert word_counts["tkinter"] == 3
assert word_counts["color"] == 2

Automated XML Sitemap Generation 🔝

Keep your site indexing healthy by generating a standard XML sitemap from an Excel URL inventory:

import pandas as pd

url_inventory = pd.DataFrame({
    "url": [
        "https://www.plus2net.com/python/pandas.php",
        "https://www.plus2net.com/python/pandas-dt.php",
        "https://www.plus2net.com/python/pandas-style.php"
    ],
    "dt": ["2026-10-10", "2026-10-11", "2026-10-11"],
    "status": [200, 200, 200]
})

# Generate XML sitemap markup
xml_header = '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="https://www.sitemaps.org/schemas/sitemap/0.9">
'
xml_body = ""
for _, row in url_inventory.iterrows():
    xml_body += f'  <url><loc>{row["url"]}</loc><lastmod>{row["dt"]}</lastmod></url>
'
xml_footer = '</urlset>'

full_sitemap = xml_header + xml_body + xml_footer
print(full_sitemap)
assert "<loc>https://www.plus2net.com/python/pandas-dt.php</loc>" in full_sitemap

Hands-on Exercise: Identifying Striking-Distance Keywords 🔝

Given a search performance DataFrame with columns ["query", "clicks", "impressions", "position"], filter for queries where impressions >= 500 and position is between 5.0 and 15.0. Calculate the Click-Through Rate (ctr = clicks / impressions) and sort descending by CTR.

import pandas as pd

gsc_data = pd.DataFrame({
    "query": ["pandas date range", "python sqlite", "tkinter treeview", "pandas cut bins"],
    "clicks": [45, 80, 25, 120],
    "impressions": [900, 1200, 350, 1500],
    "position": [7.2, 8.5, 12.0, 6.1]
})

# Apply filtering and compute CTR
filtered = gsc_data[(gsc_data["impressions"] >= 500) & (gsc_data["position"].between(5.0, 15.0))].copy()
filtered["ctr"] = (filtered["clicks"] / filtered["impressions"]).round(4)
filtered = filtered.sort_values(by="ctr", ascending=False)

print(filtered)
assert len(filtered) == 3
assert filtered.iloc[0]["query"] == "pandas cut bins"

Practice in Google Colab & GitHub 🔝

Open in Google Colab View on GitHub
Run GA4 cleaning pipelines, regex search query filters, keyword frequency tokenizers, and XML sitemap generators interactively in Google Colab.

More Details & Related Tutorials 🔝

• read_csv() — Ingesting tabular CSV exports with custom header mappings.
• str.contains() — Regular expression pattern matching on text series.
• str.split() — Delimiting sentences and extracting keywords into columns.
• value_counts() — Frequency distributions of categorical values.
• to_excel() — Exporting cleaned data and reports to formatted Excel spreadsheets.
• Creating Sitemaps in Python — Comprehensive website auditing and XML sitemap generator.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: