Functory
functory.
All Articles
6 min read
Functory

Generate CTR Reports from Google Ads Keyword Exports in Python for Small Teams

Small marketing teams often receive messy CSV exports from Google Ads with creative-level data, search terms, and keyword metrics. This article shows how to turn those raw exports into a clean, actionable CTR (click-through rate) report using a single-file Python script you can run locally or publish as a Functory cloud API. The solution is minimal—one pinned dependency (pandas)—and intended for teams that cannot hire a dedicated data engineer but need reproducible, auditable reports for weekly acquisition reviews.

What this function does (inputs, processing, outputs)

Input: one CSV file exported from Google Ads (UTF-8 or Excel-saved CSV) with columns similar to:

  • date (YYYY-MM-DD),
  • campaign,
  • ad_group,
  • search_term or keyword,
  • impressions (integer),
  • clicks (integer),
  • cost_micros or cost (micro-units or float currency).

Processing steps: case-insensitive column normalization, cleaning numeric fields (remove commas, percent signs, convert micros to dollars), filtering out low-impression noise, grouping and aggregating by keyword (or creative), computing CTR = clicks / impressions, computing average CPC and cost totals, and exporting sorted top-N rows.

Output: a CSV (or JSON) report with schema: keyword,campaign,impressions,clicks,ctr,cost_usd,avg_cpc_usd. Example row: "blue running shoes","SHOE-Prospect",12000,360,0.03,180.50,0.5014.

Real-world scenario — concrete inputs and outputs

Imagine a 3-person acquisition team that runs weekly reviews. They export a file named google_ads_export_week34.csv which contains 12,345 rows with these noisy issues: the header includes "Search term (match)" and "Cost" (dollar string with $ and commas), some rows have impressions as "—" for low-volume buckets, and the file mixes cost_micros (int) with cost (string). The function:

  • Standardizes headers to search_term, impressions, clicks, cost_usd.
  • Drops rows where impressions < 50 (configurable),
  • Aggregates by search_term and returns the top 50 keywords by impressions and top 20 by CTR (min impressions threshold applied).

Output example file: ctr_report_week34.csv with 70 rows (50 impressions-ranked + 20 CTR-ranked distinct rows) ready for import into a Google Sheet or BI tool.

Example dataset used in this article

Fabricated but realistic dataset: 1,000 rows of creative-level rows with columns: date,campaign,ad_group,search_term,impressions,clicks,cost_micros. Size: ~120 KB. Problem solved: automated normalization and aggregation to eliminate ~2 hours per week of manual cleaning in spreadsheets.

Step-by-step mini workflow (end-to-end)

  1. Export CSV from Google Ads UI: save as raw_ads_export.csv.
  2. Run the script: it normalizes headers, parses numeric formats, and applies thresholds.
  3. The script writes ctr_report.csv and prints a short summary to stdout.
  4. Upload the CSV to the shared team drive; schedule the script in an automation runner or publish on Functory for on-demand API calls from your dashboard.
  5. Use the report in the weekly acquisition review to prioritize creative tests and pause poor-performing keywords.

Algorithm (high-level)

  1. Read CSV with flexible header matching (case-insensitive, synonyms: 'search_term', 'keyword').
  2. Normalize numeric columns: convert 'cost_micros' → USD (divide by 1_000_000) and parse currency strings to float.
  3. Filter rows with impressions < threshold (default 50) or missing clicks/impressions.
  4. Group by keyword (and optionally campaign/ad_group), aggregate impressions, clicks, sum cost.
  5. Compute CTR = clicks / impressions, avg_cpc = cost_usd / clicks where clicks > 0.
  6. Sort and export top-N views (by impressions and by CTR with min impressions constraint).

Code example — small, runnable Python script

from typing import Optional
import pandas as pd

def main(input_csv: str, output_csv: str = 'ctr_report.csv', min_impr: int = 50, top_n: int = 50) -> str:
    df = pd.read_csv(input_csv, dtype=str)

    # normalize columns (simple mapping)
    mapping = {c.lower(): c for c in df.columns}
    def find(col_names):
        for name in col_names:
            if name in mapping:
                return mapping[name]
        return None

    search_col = find(['search_term','keyword','search term','query'])
    imp_col = find(['impressions','impr','imps'])
    clk_col = find(['clicks','click'])
    cost_micros_col = find(['cost_micros','cost micros'])
    cost_col = find(['cost','cost_usd'])

    # coerce numbers
    df['impressions'] = pd.to_numeric(df[imp_col].str.replace(',','').replace('—', '0'), errors='coerce').fillna(0).astype(int)
    df['clicks'] = pd.to_numeric(df[clk_col].str.replace(',',''), errors='coerce').fillna(0).astype(int)

    if cost_micros_col:
        df['cost_usd'] = pd.to_numeric(df[cost_micros_col], errors='coerce').fillna(0) / 1_000_000
    elif cost_col:
        df['cost_usd'] = (df[cost_col].astype(str).str.replace('[$,]','', regex=True).replace('', '0')).astype(float)
    else:
        df['cost_usd'] = 0.0

    df['keyword'] = df[search_col].astype(str).str.strip().str.lower()

    # filter low-volume
    df = df[df['impressions'] >= min_impr]

    # aggregate
    agg = df.groupby(['keyword'], as_index=False).agg({
        'impressions':'sum',
        'clicks':'sum',
        'cost_usd':'sum'
    })
    agg['ctr'] = (agg['clicks'] / agg['impressions']).fillna(0)
    agg['avg_cpc_usd'] = (agg['cost_usd'] / agg['clicks']).replace([pd.NA, float('inf')], 0).fillna(0)

    # two views: top by impressions and top by CTR (with impressions filter)
    top_by_impr = agg.sort_values('impressions', ascending=False).head(top_n)
    top_by_ctr = agg[agg['impressions'] >= min_impr].sort_values('ctr', ascending=False).head(20)

    out = pd.concat([top_by_impr, top_by_ctr]).drop_duplicates().reset_index(drop=True)
    out.to_csv(output_csv, index=False)
    print(f'Wrote {len(out)} rows to {output_csv}')
    return output_csv

# Example local run
if __name__ == '__main__':
    main('google_ads_export_week34.csv', 'ctr_report_week34.csv')

When to use this vs. alternatives

Common current approaches:

  • Manual spreadsheets: copy/paste, fragile formulas, hard to audit.
  • Ad-hoc Jupyter notebooks: reproducible but not easily callable by non-technical teammates.
  • Full data pipeline into BigQuery or a data warehouse: robust but requires engineering time and ongoing infra costs.

This single-file function approach balances reproducibility and low maintenance: it is more auditable and faster than spreadsheets, simpler to operationalize than notebooks, and far cheaper to start than a warehouse-based pipeline.

How Functory Makes It Easy

To publish this as a Functory function, wrap the core logic in a main(input_csv: str, output_csv: str = 'ctr_report.csv', min_impr: int = 50) entrypoint. On Functory you must pick an exact Python runtime (for example 3.11.11) and supply a requirements.txt with pinned versions, e.g.:

pandas==2.1.3

Functory will expose input_csv as a file upload field or URL string and min_impr as a numeric input in the web UI and API. When main returns a path string (like ctr_report_week34.csv) Functory exposes the file as a downloadable result. Benefits: no server management, autoscaling on demand, pay-per-use execution, and logs captured from print() for debugging. You can chain functions: e.g., pre-processing → this CTR aggregation → a reporting function that uploads results to Google Drive or triggers a Slack message to the acquisition channel.

Comparison: why function-based automation helps

Compared to spreadsheets, a function is deterministic (same inputs → same outputs), testable, and scriptable from CI or other automations. Compared to full ETL platforms, this approach has near-zero onboarding time and cost—no infra to configure and you pay only for runs. Compared to notebooks, it provides a callable API endpoint for non-technical users.

Business impact

Conservative estimate: replacing manual weekly cleaning (≈4 hours/week) with this automated function reduces manual processing time to about 20–40 minutes for review and validation—an ~80–90% time reduction for the reporting task. For a small team charging $60/hr for marketer time, that saves roughly $180/week or ≈$9,000/year.

According to a 2024 Gartner report, ~62% of SMB marketing teams still rely on spreadsheets for campaign reporting; automating this saves time and reduces human error in acquisition decisions (Gartner, 2024).

Conclusion: converting messy Google Ads exports into a reliable CTR report is practical with a compact, auditable Python script. Next steps: try the script on a recent export, tune the min_impr threshold to your traffic levels, and publish it to Functory to let product and marketing stakeholders run reports without touching code. If you want more, extend the function to fetch exports via the Google Ads API and push summarized reports directly into Google Sheets as a next automation step.

Thanks for reading.