SEO Analytics & Metadata Aggregator

A Python script that concurrently collects search engine analytics and page metadata, normalizes and merges the data across sources, and exports a clean, beautifully formatted Excel report.

🌟 Key Features

• Multi-Source Aggregation: Integrates data from three major search engines:
  • Google Search Console (GSC) (via Google API Client)
  • Yandex Webmaster (via REST API v4)
  • Bing Webmaster Tools (BWT) (via Bing Webmaster API)
• Metadata Scraping: Fetches custom metadata for pages via REST API endpoints defined in the configuration.
• Concurrent Execution: Uses Python's concurrent.futures.ThreadPoolExecutor to fetch data from all sources simultaneously for faster performance.
• URL Normalization: Normalizes and matches URL structures across all services to ensure accurate row-level merging.
• Automated Excel Formatting:
  • Saves snapshots onto date-stamped worksheets (YYYY-MM-DD).
  • Applies custom color-coded headers for each data provider (Gray for Metadata, Blue for Google, Orange for Yandex, Green for Bing).
• Data Sanitization: Strips invalid XML 1.0 control characters to prevent output corruption.

---

🛠️ Architecture & Workflow

               ┌──────────────────────────────┐
               │    ThreadPoolExecutor        │
               └──────────────┬───────────────┘
                              │
        ┌─────────────┬───────┴───────┬─────────────┐
        ▼             ▼               ▼             ▼
   ┌─────────┐   ┌─────────┐     ┌─────────┐   ┌─────────┐
   │ Site    │   │ Yandex  │     │ Bing    │   │ Google  │
   │ API     │   │ Web     │     │ Web     │   │ Search  │
   │ Metadata│   │ master  │     │ master  │   │ Console │
   └────┬────┘   └────┬────┘     └────┬────┘   └────┬────┘
        │             │               │             │
        └─────────────┼───────────────┴─────────────┘
                      ▼
            ┌───────────────────┐
            │  URL Normalization│
            │  & Data Merging   │
            └─────────┬─────────┘
                      ▼
            ┌───────────────────┐
            │ Sanitization &    │
            │ Excel Formatting  │
            └─────────┬─────────┘
                      ▼
            ┌───────────────────┐
            │    output.xlsx    │
            └───────────────────┘

---

📋 Prerequisites

• Python: 3.9+
• API Credentials:
  • Google: Service Account JSON key with read access to Search Console.
  • Yandex: OAuth token with Webmaster API access.
  • Bing: API Key from Bing Webmaster Tools.

---

📦 Installation

1. Clone the Repository:

   git clone https://github.com/your-username/seo-analytics-aggregator.git
   cd seo-analytics-aggregator

2. Install Dependencies:

   pip install -r requirements.txt

Dependencies

Create a requirements.txt file if needed:

pandas
openpyxl
requests
json5
google-api-python-client
google-auth

---

⚙️ Configuration Setup

The script relies on a central settings.json file and credential JSON files.

1. settings.json

Create a settings.json file in the root directory:

{
  "website": "https://example.com/",
  "duration_in_days": 14,
  "output_file": "seo_report.xlsx",
  "bing_settings_file_location": "bwt_credentials.json",
  "yandex_settings_file_location": "ywt_credentials.json",
  "google_settings_file_location": "gsc_credentials.json",
  "endpoints": ["/api/v1/pages"],
  "query": "?page={}&page_size={}",
  "query_page_size": 100,
  "data_path": "items",
  "fields": ["title", "description"],
  "id_field": "slug",
  "leave_id_field": true,
  "yandex_webmaster_tools": {
    "device_type_indicator": "ALL",
    "text_indicator_type": "URL"
  }
}

2. Credentials Files

• Google (gsc_credentials.json): Standard Google Service Account JSON key file downloaded from Google Cloud Console.
• Yandex (ywt_credentials.json):

  {
    "token": "YOUR_YANDEX_OAUTH_TOKEN"
  }

• Bing (bwt_credentials.json):

  {
    "api_key": "YOUR_BING_API_KEY"
  }

---

🚀 Usage

Run the main script:

python main.py

Output File Structure

The script generates or updates the Excel file specified in settings.json (e.g., seo_report.xlsx).

Each run creates/overwrites a tab named after the current date (YYYY-MM-DD) with the following column structure:

Column Group	Color Header	Fields Included
Metadata	⚪ Light Gray	url, Title, Description, etc.
Google	🔵 Light Blue	gsc_clicks, gsc_impressions, gsc_ctr, gsc_position
Yandex	🟠 Light Orange	yw_clicks, yw_impressions, yw_ctr, yw_position
Bing	🟢 Light Green	bwt_clicks, bwt_impressions, bwt_ctr, bwt_position

---

📄 License

This project is open-source and available under the MIT License.