# Google Search Console SQLite Schema Reference

The Search Analytics database is created and maintained automatically by `scripts/search_analytics.py`. Database tables, indexes, and analytical views are provisioned during sync operations.

---

## 1. Relational Tables

### `daily_site_performance`
Stores unfiltered property-level daily totals (`dimensions: ['date']`) without anonymized query filtering. Matches 100% of property clicks and impressions in the Search Console web interface and Achievement badges.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `id` | `INTEGER` | PRIMARY KEY AUTOINCREMENT | Unique record ID |
| `site_url` | `TEXT` | NOT NULL | Search Console property URL |
| `date` | `TEXT` | NOT NULL | Date of search activity (`YYYY-MM-DD`) |
| `search_type` | `TEXT` | NOT NULL, DEFAULT 'web' | Search channel (`web`, `image`, `video`, `news`) |
| `clicks` | `REAL` | NOT NULL, DEFAULT 0 | Total unfiltered property clicks |
| `impressions` | `REAL` | NOT NULL, DEFAULT 0 | Total unfiltered property impressions |
| `ctr` | `REAL` | NOT NULL, DEFAULT 0 | Property click-through rate (`clicks / impressions`) |
| `position` | `REAL` | NOT NULL, DEFAULT 0 | Average property ranking position |
| `raw_json` | `TEXT` | NOT NULL | Complete raw API row JSON payload |
| `synced_at` | `TEXT` | NOT NULL | Local sync timestamp |

*Unique Constraint:* `(site_url, date, search_type)`

---

### `search_performance`
Stores daily granular search performance partitioned by query, page, country, and device.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `id` | `INTEGER` | PRIMARY KEY AUTOINCREMENT | Unique record ID |
| `site_url` | `TEXT` | NOT NULL | Search Console property URL |
| `date` | `TEXT` | NOT NULL | Date of search activity (`YYYY-MM-DD`) |
| `query` | `TEXT` | NOT NULL, DEFAULT '' | Search query keyword |
| `page` | `TEXT` | NOT NULL, DEFAULT '' | Full URL of the landing page shown in SERP |
| `country` | `TEXT` | NOT NULL, DEFAULT '' | Three-letter ISO country code (`usa`, `gbr`, `jpn`, `bra`) |
| `device` | `TEXT` | NOT NULL, DEFAULT '' | Device category (`DESKTOP`, `MOBILE`, `TABLET`) |
| `search_appearance` | `TEXT` | NOT NULL, DEFAULT '' | Rich result type if applicable |
| `search_type` | `TEXT` | NOT NULL, DEFAULT 'web' | Search channel (`web`, `image`, `video`, `news`) |
| `clicks` | `REAL` | NOT NULL, DEFAULT 0 | Total clicks generated |
| `impressions` | `REAL` | NOT NULL, DEFAULT 0 | Total impressions displayed in SERP |
| `ctr` | `REAL` | NOT NULL, DEFAULT 0 | Click-through rate (fraction: `clicks / impressions`) |
| `position` | `REAL` | NOT NULL, DEFAULT 0 | Average ranking position (1-indexed, e.g. 1.0 = top) |
| `raw_json` | `TEXT` | NOT NULL | Complete raw API row JSON payload |
| `synced_at` | `TEXT` | NOT NULL | Local sync timestamp |

*Unique Constraint:* `(site_url, date, query, page, country, device, search_appearance, search_type)`

> [!NOTE]
> **Keyword-Level Privacy Truncation:** When data is queried with `query` in dimensions, the Search Console API omits rare long-tail "anonymized queries" to protect searcher privacy. Granular keyword rows in `search_performance` represent the non-anonymized query subset, while true property-level totals are preserved in `daily_site_performance`. The `v_daily_summary` analytical view automatically leverages `daily_site_performance` to guarantee zero data loss.

---

### `properties`
Stores verified Search Console property URLs and access permissions.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `site_url` | `TEXT` | PRIMARY KEY | Property URL (e.g. `https://example.com/` or `sc-domain:example.com`) |
| `permission_level` | `TEXT` | | Access tier (`siteOwner`, `siteFullUser`, `siteRestrictedUser`) |
| `raw_json` | `TEXT` | NOT NULL | Complete raw JSON payload from Search Console API |
| `synced_at` | `TEXT` | NOT NULL | Local sync timestamp |

---

### `sitemaps`
Stores XML sitemaps submitted to Search Console, processing status, and indexing numbers.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `site_url` | `TEXT` | PRIMARY KEY (site_url, path), FK -> `properties(site_url)` | Parent property URL |
| `path` | `TEXT` | PRIMARY KEY (site_url, path) | Full sitemap URL |
| `type` | `TEXT` | | Sitemap type (`sitemap`, `feedIndex`) |
| `last_downloaded` | `TEXT` | | Timestamp when Googlebot last downloaded the XML |
| `last_submitted` | `TEXT` | | Timestamp when sitemap was submitted |
| `errors` | `INTEGER` | DEFAULT 0 | Number of parsing errors reported |
| `warnings` | `INTEGER` | DEFAULT 0 | Number of non-fatal warnings reported |
| `indexed_count` | `INTEGER` | | Total count of indexed URLs from this sitemap |
| `raw_json` | `TEXT` | NOT NULL | Complete raw API payload |
| `synced_at` | `TEXT` | NOT NULL | Local sync timestamp |

---

### `site_milestones`
Tracks release milestones, major publications, and architectural launches for cohort impact analysis.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `commit_hash` | `TEXT` | PRIMARY KEY | Git commit hash or milestone unique identifier |
| `event_date` | `DATE` | NOT NULL | Milestone date (`YYYY-MM-DD`) |
| `title` | `TEXT` | NOT NULL | Short title of the milestone |
| `description` | `TEXT` | | Detailed context or release notes |
| `category` | `TEXT` | | Category (`release`, `article`, `campaign`, `major_feature`) |
| `scope` | `TEXT` | | Scope tag (`site`, `blog`, `oss`) |
| `author` | `TEXT` | | Author name or handle |
| `created_at` | `TIMESTAMP`| DEFAULT CURRENT_TIMESTAMP | Record creation timestamp |

---

### `sync_history`
Audit log recording every backfill and incremental sync operation.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `id` | `INTEGER` | PRIMARY KEY AUTOINCREMENT | Unique sync run ID |
| `site_url` | `TEXT` | | Search Console property URL |
| `sync_type` | `TEXT` | NOT NULL | Sync type (`incremental`, `full`, `range`) |
| `start_date` | `TEXT` | | Starting date of sync window |
| `end_date` | `TEXT` | | Ending date of sync window |
| `rows_synced` | `INTEGER` | DEFAULT 0 | Total rows inserted or updated |
| `status` | `TEXT` | NOT NULL | Status (`success`, `failed`, `running`) |
| `error_message` | `TEXT` | | Error details if failed |
| `started_at` | `TEXT` | NOT NULL | Start timestamp |
| `completed_at` | `TEXT` | | Completion timestamp |

---

## 2. Analytical SQL Views

### `v_search_performance`
Granular performance with computed calendar dimensions (`year_month`, `day_of_week`, `ctr_pct`, `avg_position`).

| Column | Type | Description |
| :--- | :--- | :--- |
| `site_url` | `TEXT` | Property URL |
| `date` | `TEXT` | Activity date (`YYYY-MM-DD`) |
| `year_month` | `TEXT` | Formatted year and month (`YYYY-MM`) |
| `day_of_week` | `TEXT` | Full day name (`Monday`, `Tuesday`, etc.) |
| `query` | `TEXT` | Search query term |
| `page` | `TEXT` | Landing page URL |
| `country` | `TEXT` | Three-letter country code |
| `device` | `TEXT` | Device category (`DESKTOP`, `MOBILE`, `TABLET`) |
| `search_type` | `TEXT` | Search type (`web`, `image`, `video`) |
| `clicks` | `REAL` | Total clicks |
| `impressions` | `REAL` | Total impressions |
| `ctr_pct` | `REAL` | Click-through rate percentage (`clicks / impressions * 100`) |
| `avg_position` | `REAL` | Average ranking position |

---

### `v_daily_summary`
Daily aggregated search totals across all queries and landing pages.

| Column | Type | Description |
| :--- | :--- | :--- |
| `site_url` | `TEXT` | Property URL |
| `date` | `TEXT` | Activity date (`YYYY-MM-DD`) |
| `year_month` | `TEXT` | Formatted year and month (`YYYY-MM`) |
| `day_of_week` | `TEXT` | Full day name |
| `distinct_queries` | `INTEGER` | Count of distinct search keywords |
| `distinct_pages` | `INTEGER` | Count of distinct landing pages |
| `total_clicks` | `REAL` | Sum of clicks for the day |
| `total_impressions`| `REAL` | Sum of impressions for the day |
| `avg_ctr_pct` | `REAL` | Overall click-through rate percentage |
| `avg_position` | `REAL` | Average ranking position |

---

### `v_top_queries`
Search queries aggregated by active days, total clicks, impressions, CTR, and average position.

| Column | Type | Description |
| :--- | :--- | :--- |
| `site_url` | `TEXT` | Property URL |
| `query` | `TEXT` | Search query term |
| `active_days` | `INTEGER` | Number of distinct days query generated impressions |
| `total_clicks` | `REAL` | Total clicks received |
| `total_impressions`| `REAL` | Total impressions displayed |
| `avg_ctr_pct` | `REAL` | Average click-through rate percentage |
| `avg_position` | `REAL` | Average ranking position |

---

### `v_top_pages`
Landing pages aggregated by distinct ranking queries, clicks, impressions, CTR, and average position.

| Column | Type | Description |
| :--- | :--- | :--- |
| `site_url` | `TEXT` | Property URL |
| `page` | `TEXT` | Landing page URL |
| `ranking_queries` | `INTEGER` | Number of unique queries ranking for this page |
| `active_days` | `INTEGER` | Number of active traffic days |
| `total_clicks` | `REAL` | Total clicks received |
| `total_impressions`| `REAL` | Total impressions displayed |
| `avg_ctr_pct` | `REAL` | Average click-through rate percentage |
| `avg_position` | `REAL` | Average ranking position |

---

### `v_country_breakdown`
Geographic search performance aggregated by country code.

| Column | Type | Description |
| :--- | :--- | :--- |
| `site_url` | `TEXT` | Property URL |
| `country` | `TEXT` | Three-letter ISO country code |
| `total_clicks` | `REAL` | Total clicks from country |
| `total_impressions`| `REAL` | Total impressions in country |
| `avg_ctr_pct` | `REAL` | Average click-through rate percentage |
| `avg_position` | `REAL` | Average ranking position |

---

### `v_device_breakdown`
Device performance breakdown (`DESKTOP`, `MOBILE`, `TABLET`).

| Column | Type | Description |
| :--- | :--- | :--- |
| `site_url` | `TEXT` | Property URL |
| `device` | `TEXT` | Device category |
| `total_clicks` | `REAL` | Total clicks from device |
| `total_impressions`| `REAL` | Total impressions on device |
| `avg_ctr_pct` | `REAL` | Average click-through rate percentage |
| `avg_position` | `REAL` | Average ranking position |

---

### `v_milestone_impact`
Pre vs post milestone comparison of organic search traffic.

| Column | Type | Description |
| :--- | :--- | :--- |
| `commit_hash` | `TEXT` | Milestone identifier |
| `milestone_title` | `TEXT` | Title of the milestone |
| `milestone_date` | `DATE` | Event date (`YYYY-MM-DD`) |
| `cohort` | `TEXT` | `Pre-Milestone` or `Post-Milestone` |
| `days_tracked` | `INTEGER` | Number of days in cohort |
| `total_clicks` | `REAL` | Total clicks in cohort |
| `total_impressions`| `REAL` | Total impressions in cohort |
| `avg_ctr_pct` | `REAL` | Average click-through rate percentage |
| `avg_position` | `REAL` | Average ranking position |