Google Analytics 4 SQLite Schema Reference #
The Google Analytics database is created and maintained automatically by scripts/google_analytics.py. Database tables, indexes, and analytical views are provisioned during sync operations.
1. Relational Tables #
daily_pages #
Stores daily pageviews, active users, sessions, active dwell time, and bounce rate broken down by country, device, and traffic source.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
INTEGER |
PRIMARY KEY AUTOINCREMENT | Unique row ID |
property_id |
TEXT |
NOT NULL | GA4 Property ID |
date |
TEXT |
NOT NULL | Activity date (YYYY-MM-DD) |
page_path |
TEXT |
NOT NULL | URL path component (e.g. /blog/post-1/) |
page_title |
TEXT |
HTML page title | |
country |
TEXT |
Visitor country name | |
device_category |
TEXT |
Device category (desktop, mobile, tablet) |
|
source_medium |
TEXT |
Acquisition source / medium (e.g. google / organic) |
|
screen_page_views |
INTEGER |
DEFAULT 0 | Total pageviews |
active_users |
INTEGER |
DEFAULT 0 | Count of distinct active visitors |
sessions |
INTEGER |
DEFAULT 0 | Number of visitor sessions |
user_engagement_duration |
REAL |
DEFAULT 0.0 | Total active dwell time in seconds |
bounce_rate |
REAL |
DEFAULT 0.0 | Fraction of unengaged sessions (0.0 to 1.0) |
raw_json |
TEXT |
Complete raw API row JSON payload | |
synced_at |
TEXT |
Timestamp of local database sync |
Unique Constraint: (property_id, date, page_path, country, device_category, source_medium)
daily_traffic #
Stores daily acquisition channels, source/medium pairs, new users, engaged sessions, and dwell duration.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
INTEGER |
PRIMARY KEY AUTOINCREMENT | Unique row ID |
property_id |
TEXT |
NOT NULL | GA4 Property ID |
date |
TEXT |
NOT NULL | Activity date (YYYY-MM-DD) |
session_source_medium |
TEXT |
NOT NULL | Source / Medium (e.g. direct / none, linkedin.com / referral) |
session_default_channel_group |
TEXT |
High-level channel (Direct, Organic Search, Organic Social, Referral) |
|
country |
TEXT |
Visitor country name | |
device_category |
TEXT |
Device category | |
sessions |
INTEGER |
DEFAULT 0 | Total sessions initiated |
active_users |
INTEGER |
DEFAULT 0 | Total distinct active visitors |
new_users |
INTEGER |
DEFAULT 0 | First-time visitors |
engaged_sessions |
INTEGER |
DEFAULT 0 | Sessions lasting >10s, with 2+ views, or conversions |
user_engagement_duration |
REAL |
DEFAULT 0.0 | Total active dwell time in seconds |
bounce_rate |
REAL |
DEFAULT 0.0 | Bounce rate fraction |
raw_json |
TEXT |
Raw API row JSON payload | |
synced_at |
TEXT |
Timestamp of local database sync |
Unique Constraint: (property_id, date, session_source_medium, session_default_channel_group, country, device_category)
NOTE
Organic Search Tracking: GA4 records 100% of landing sessions originating from organic search across all search engines (
Google, Bing, DuckDuckGo, Ecosia, AI search engines) without query-level privacy stripping. This represents actual on-site visitor arrivals and naturally aligns with Search Console property-level totals (daily_site_performance in search-analytics and GSC Web UI Performance cards).
daily_events #
Stores daily user engagement events (scroll, click, first_visit, user_engagement, page_view).
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
INTEGER |
PRIMARY KEY AUTOINCREMENT | Unique row ID |
property_id |
TEXT |
NOT NULL | GA4 Property ID |
date |
TEXT |
NOT NULL | Activity date (YYYY-MM-DD) |
event_name |
TEXT |
NOT NULL | Event name identifier |
page_path |
TEXT |
URL path where event occurred | |
country |
TEXT |
Visitor country | |
device_category |
TEXT |
Device category | |
event_count |
INTEGER |
DEFAULT 0 | Total event occurrences |
total_users |
INTEGER |
DEFAULT 0 | Unique users triggering the event |
raw_json |
TEXT |
Raw API row JSON payload | |
synced_at |
TEXT |
Timestamp of local database sync |
outbound_clicks #
Stores external link exit destinations, referring pages, and user clicks.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
INTEGER |
PRIMARY KEY AUTOINCREMENT | Unique row ID |
property_id |
TEXT |
NOT NULL | GA4 Property ID |
date |
TEXT |
NOT NULL | Activity date (YYYY-MM-DD) |
link_url |
TEXT |
NOT NULL | Destination URL clicked |
page_path |
TEXT |
Referring page path | |
country |
TEXT |
Visitor country | |
event_count |
INTEGER |
DEFAULT 0 | Total clicks on destination link |
total_users |
INTEGER |
DEFAULT 0 | Unique visitors clicking destination |
raw_json |
TEXT |
Raw API row JSON payload | |
synced_at |
TEXT |
Timestamp of local database sync |
properties #
Verified GA4 property metadata, time zone, and account configuration.
| Column | Type | Constraints | Description |
|---|---|---|---|
property_id |
TEXT |
PRIMARY KEY | GA4 Property numeric ID (e.g. 123456789) |
name |
TEXT |
GA4 resource name (properties/123456789) |
|
account_id |
TEXT |
Parent GA4 Account ID | |
display_name |
TEXT |
Human-readable property title | |
industry_category |
TEXT |
Industry category classification | |
time_zone |
TEXT |
Reporting timezone string (e.g. UTC, America/New_York) |
|
currency_code |
TEXT |
Reporting currency code (e.g. USD) |
|
service_level |
TEXT |
GA4 service level (GOOGLE_ANALYTICS_STANDARD, GOOGLE_ANALYTICS_360) |
|
create_time |
TEXT |
Creation timestamp | |
update_time |
TEXT |
Modification timestamp | |
raw_json |
TEXT |
Complete raw JSON property payload | |
last_synced_at |
TEXT |
Timestamp of local database sync |
site_milestones #
Tracks release milestones, major blog launches, and architectural updates for cohort impact analysis.
| Column | Type | Constraints | Description |
|---|---|---|---|
commit_hash |
TEXT |
PRIMARY KEY | Git commit hash or unique milestone identifier |
event_date |
DATE |
NOT NULL | Milestone date (YYYY-MM-DD) |
title |
TEXT |
NOT NULL | Short title of the release / 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 |
property_id |
TEXT |
NOT NULL | GA4 Property ID |
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 | |
pages_synced |
INTEGER |
DEFAULT 0 | Count of page rows synced |
traffic_synced |
INTEGER |
DEFAULT 0 | Count of traffic rows synced |
events_synced |
INTEGER |
DEFAULT 0 | Count of event rows synced |
outbound_synced |
INTEGER |
DEFAULT 0 | Count of outbound click rows synced |
status |
TEXT |
NOT NULL | Status (success, failed, running) |
error_message |
TEXT |
Error details if failed | |
started_at |
TEXT |
NOT NULL | Start timestamp |
finished_at |
TEXT |
Completion timestamp |
2. Analytical SQL Views #
v_daily_summary #
Daily aggregated traffic totals across all landing pages.
| Column | Type | Description |
|---|---|---|
date |
TEXT |
Activity date (YYYY-MM-DD) |
total_sessions |
INTEGER |
Sum of sessions |
total_active_users |
INTEGER |
Sum of active visitors |
total_page_views |
INTEGER |
Sum of pageviews |
total_engagement_min |
REAL |
Total active dwell time in minutes |
avg_bounce_pct |
REAL |
Average bounce rate percentage |
v_page_performance #
Page-level rollup with views, active users, average dwell time, and bounce rate.
| Column | Type | Description |
|---|---|---|
page_path |
TEXT |
URL path |
page_title |
TEXT |
Page title tag |
total_views |
INTEGER |
Total pageviews |
total_users |
INTEGER |
Distinct active visitors |
total_sessions |
INTEGER |
Visitor sessions |
avg_dwell_sec |
REAL |
Average active dwell time per user (seconds) |
total_dwell_min |
REAL |
Total cumulative active dwell time (minutes) |
avg_bounce_pct |
REAL |
Average bounce rate percentage |
v_channel_performance #
Acquisition channels and source/medium pairs with sessions, new users, and engagement rate %.
| Column | Type | Description |
|---|---|---|
channel_group |
TEXT |
High-level channel (Direct, Organic Search, Referral) |
source_medium |
TEXT |
Specific source / medium |
total_sessions |
INTEGER |
Total sessions initiated |
total_users |
INTEGER |
Total active visitors |
total_new_users |
INTEGER |
First-time visitors |
total_engaged_sessions |
INTEGER |
Count of engaged sessions |
engagement_rate_pct |
REAL |
Engagement percentage (engaged_sessions / sessions * 100) |
total_dwell_min |
REAL |
Total active dwell time in minutes |
avg_bounce_pct |
REAL |
Average bounce rate percentage |
v_geo_breakdown #
Geographical ranking by country with sessions, pageviews, and dwell time.
| Column | Type | Description |
|---|---|---|
country |
TEXT |
Country name |
total_sessions |
INTEGER |
Total sessions |
total_users |
INTEGER |
Total active visitors |
total_page_views |
INTEGER |
Total pageviews |
avg_dwell_sec |
REAL |
Average dwell time per user (seconds) |
avg_bounce_pct |
REAL |
Average bounce rate percentage |
v_events_summary #
Aggregate event interaction counts.
| Column | Type | Description |
|---|---|---|
event_name |
TEXT |
Event name identifier |
total_events |
INTEGER |
Total event occurrences |
total_users |
INTEGER |
Count of distinct users triggering event |
v_outbound_links #
Outbound exit destinations ranked by click counts and unique referring pages.
| Column | Type | Description |
|---|---|---|
link_url |
TEXT |
External destination URL |
total_clicks |
INTEGER |
Total click count |
total_users |
INTEGER |
Distinct users who clicked |
referring_pages_count |
INTEGER |
Number of distinct local pages linking to destination |
v_milestone_impact #
Pre vs post milestone comparison of page traffic and engagement.
| 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 |
Days tracked in cohort |
total_views |
INTEGER |
Total pageviews in cohort |
total_users |
INTEGER |
Total active visitors in cohort |
total_sessions |
INTEGER |
Total sessions in cohort |
avg_engagement_sec |
REAL |
Average active engagement duration (seconds) |
avg_bounce_pct |
REAL |
Average bounce rate percentage |