GA4 Live Analytics

Setting the Stage: GCP Service Account Provisioning

Every good data pipeline starts with a boring but critical question: who gets to talk to the API?

For this project, we needed a way to authenticate with Google's analytics APIs — and the answer led us into the world of GCP service accounts. Google Cloud Platform provides a clean identity model for machine-to-machine communication: instead of using a personal Google account (which would break the moment someone changes their password or leaves the team), you create a service account — a dedicated, non-human identity that exists solely to interact with specific APIs.

We provisioned a purpose-built service account — a dedicated identity whose sole job is reading GA4 data. No admin privileges, no write access to anything it doesn't need, no shared credentials with other services. Just a clean, scoped identity following the principle of least privilege.

The setup was straightforward. We created the service account within our GCP project and granted it exactly one IAM role: Analytics Viewer on our GA4 property. That's it. It can read report data. It can't modify property settings, manage users, or touch anything outside its lane. GCP's IAM system makes this kind of granular scoping natural — you define what the account can do, and everything else is implicitly denied.

The credential is stored securely on the server and referenced via an environment variable. This pattern keeps credentials out of source control and makes rotation simple — swap the file, restart the app, done.

Why go through this trouble instead of using a single account for everything? Because service accounts should be disposable and replaceable. If this one gets compromised, the blast radius is limited to read-only analytics data. No lateral movement, no privilege escalation, no "oh no, that account could also deploy to production" moments. GCP encourages this pattern — one service account per integration, each with the minimum permissions it needs. It's the kind of boring infrastructure decision that pays dividends when things go sideways.

Talking to Google: GA4 Data API Integration

With our analytics service account credentialed and scoped, the next step was building a Python client that could actually have a conversation with the GA4 Data API v1.

We wrapped Google's google-analytics-data package in a thin GA4Client class. The design philosophy was simple: authenticate once, expose a clean run_report() method, and handle all the API weirdness internally so the rest of the codebase never has to think about it.

The client authenticates using from_service_account_json() — no Application Default Credentials, no ambient auth magic. Explicit is better than implicit, especially when you're debugging at 2 AM wondering why your pipeline suddenly can't reach Google.

Each run_report() call maps directly to a GA4 RunReport request. You pass in dimensions (like date, pagePath, deviceCategory), metrics (like totalUsers, sessions, screenPageViews), and a date range. The client fires the request, parses the response rows into flat Python dicts, and casts metric values to proper numeric types. No more stringly-typed analytics data floating around.

The fun part was handling rate limits. Google's API will occasionally respond with a 429 (Resource Exhausted) when you're pulling a lot of data. Our client catches these, reads the Retry-After header (defaulting to 120 seconds if Google doesn't specify), sleeps, and retries up to three times. If it still fails after three attempts, it raises a GA4APIError with the full context so the ETL pipeline can log it and move on.

All Google API exceptions get wrapped in our custom GA4APIError class, which preserves the HTTP status code and original error details. This gives the ETL layer a single exception type to catch, regardless of whether the underlying failure was a permission issue, a network timeout, or a rate limit.

The Blueprint: Star Schema Data Warehouse Design

Raw GA4 data is great for ad-hoc queries, but if you want to power fast, flexible visualizations — the kind where a user picks a date range and sees charts update in under a second — you need a proper data warehouse.

We went with a classic star schema design: a set of dimension tables that describe the "what" (which page, which device, which campaign) surrounding fact tables that store the "how much" (users, sessions, pageviews, bounce rates). It's a pattern that's been battle-tested in analytics for decades, and for good reason — it makes aggregation queries blazing fast and keeps the data model intuitive.

The schema lives in a dedicated database on the same server as the main application, kept separate through Django's multi-database routing. The DataWarehouseRouter ensures analytics models always read from and write to the warehouse, while the rest of the app stays on the default database. This separation means analytics queries — which can be heavy — never compete with the application's transactional workload.

The Dimension Tables

Seven dimension tables provide the context for every metric we store:

• DimDataSource — Identifies the GA4 property (or future data sources). Each source has a type, key, and metadata like account name and property URL.
• DimDate — A pre-populated calendar dimension with derived fields: year, month, quarter, day of week, weekend flag. The primary key is an integer in YYYYMMDD format for efficient range scans.
• DimContent — Deduplicated page content, keyed by an MD5 hash of the page path and title. Stores the full path, title, and content type.
• DimChannel — Traffic channels with both the high-level channel group (Organic Search, Direct, Social) and the granular source/medium pair.
• DimDevice — Device categories: desktop, mobile, tablet. Simple but essential for understanding how people consume your content.
• DimCampaign — Marketing campaign names, linked to their source and medium.
• DimAuthor — Content authors, supporting the author-level analytics that let us see which writers drive the most engagement.

The Fact Tables

Eight fact tables capture metrics at different granularities and dimensions:

• FactDailyTotals — Site-wide daily metrics: users, sessions, pageviews, bounce rate, session duration. The workhorse for trend charts.
• FactMonthlyTotals — Monthly rollups for longer-term trend analysis.
• FactYearlyTotals — Annual aggregates for year-over-year comparisons.
• FactPageDaily — Per-page daily metrics broken down by traffic channel. Powers the "top pages" chart.
• FactAuthorDaily — Author-level daily metrics for content performance analysis.
• FactCampaign — Campaign performance at both daily and monthly granularity.
• FactTrafficSource — Traffic source metrics by channel, at daily and monthly granularity.
• FactDeviceMonthly — Device category breakdown at monthly granularity.

Every fact table uses update_or_create() with a composite unique key for upserts. This makes the entire pipeline idempotent — you can re-run any sync for any date range and it'll update existing records rather than creating duplicates. No delete-before-insert gymnastics, no orphaned rows, no "why do we have three copies of January's data" mysteries.

Entity Relationship Diagram

Here's how it all fits together — dimensions on the left, facts on the right, with foreign key relationships connecting them:

The Engine Room: ETL Pipeline Implementation

A data warehouse is only as good as the pipeline feeding it. Ours is built as a set of Django management commands — each one responsible for a specific slice of the analytics data, all sharing a common base that handles the repetitive stuff.

The ETLCommandMixin is where the shared logic lives. It handles date argument parsing, active source discovery, date dimension lookups, and the ETL audit logging lifecycle. Every sync command inherits from this mixin, which means every command automatically gets consistent error handling, progress logging, and audit trail creation without duplicating a single line of code.

The Command Lineup

Ten management commands cover the full spectrum of GA4 data:

• ga4_register — Registers a GA4 property as a data source and verifies API connectivity
• ga4_populate_dates — Pre-populates the date dimension for a given range (default: 2020–2030)
• ga4_sync_daily — Pulls daily site-wide totals (users, sessions, pageviews, bounce rate, etc.)
• ga4_sync_pages — Syncs page-level metrics with channel breakdowns
• ga4_sync_monthly / ga4_sync_yearly — Aggregated totals for longer-term trends
• ga4_sync_traffic — Traffic source metrics at daily and monthly granularity
• ga4_sync_devices — Device category breakdowns (desktop, mobile, tablet)
• ga4_sync_campaigns — Campaign performance data, with smart filtering to exclude organic/direct/referral noise
• ga4_sync_all — The master orchestrator that runs everything in sequence

The Upsert Pattern

Every fact table write uses Django's update_or_create() with the composite unique key as the lookup. This is the heart of the pipeline's idempotency — run the same sync twice for the same date range and you get updated records, not duplicates. It's a simple pattern, but it eliminates an entire class of data quality bugs that plague pipelines built on delete-and-reinsert strategies.

Scheduling

Three cron scripts keep the warehouse current:

• Daily (06:00 UTC) — Syncs yesterday's data across all commands
• Weekly (Sunday 04:00 UTC) — Reprocesses the last 30 days to catch GA4 data restatements
• Monthly (last day, 03:00 UTC) — Reprocesses the last 90 days for a thorough reconciliation

The master orchestrator catches and logs failures for individual commands without aborting the rest. If the campaign sync fails but everything else succeeds, you still get fresh daily totals, page metrics, and traffic data. The failure gets logged with the full error context, and you can investigate and re-run just the failed command.

Making It Visual: Chart.js + Vue.js

Data in a database is useful. Data in a chart is compelling. The final layer of this pipeline turns warehouse queries into interactive visualizations that update in real time as you adjust the date range.

We chose Chart.js for rendering — it's lightweight, well-documented, and handles the chart types we need (line, bar, doughnut, pie, horizontal bar) without pulling in a massive dependency tree. For state management, we're using Vue.js reactive refs, which keep the charts in sync with the date range picker without any extra framework overhead.

Each chart component handles its own data fetching, loading state, and rendering lifecycle. When the page loads, each chart calls its API endpoint, shows a loading spinner while waiting, and then renders the chart with theme-appropriate colors. When you change the date range, all charts re-fetch and re-render.

The color palettes are theme-aware — the Professional track (dark theme) gets a palette optimized for dark backgrounds, and the charts automatically adapt when switching between themes. Chart.js handles the rest: tooltips on hover show exact values, and clicking legend items toggles data series visibility.

The five charts below are pulling live data from the warehouse right now. What you're seeing is the actual output of the pipeline described above — from GA4 API call to database to JSON endpoint to rendered chart, end to end.

Daily Traffic Trend

Site-wide users, sessions, and pageviews over the selected date range — the pulse of the site.

Monthly Comparison

Month-over-month totals — useful for spotting seasonal patterns and growth trends.

Traffic Sources

Where visitors come from — organic search, direct, social, referral, and everything in between.

Device Categories

Desktop vs. mobile vs. tablet — how people are actually consuming the content.

Top Pages

The ten most-viewed pages over the selected date range — a quick snapshot of what's resonating.

What's Next

This pipeline is live and running, but it's far from finished. On the roadmap: author-level dashboards, campaign ROI tracking, anomaly detection alerts, and a comparison mode that lets you overlay metrics from different time periods side by side.

The beauty of the star schema approach is that adding new dimensions and facts is straightforward — the architecture scales horizontally without requiring a redesign. New data sources (Search Console, social media APIs) can plug into the same warehouse with their own dimension and fact tables, and the Chart API pattern extends naturally to serve new visualizations.

If you're building something similar — or if you're staring at a GA4 property wondering how to get the data out and into something useful — let's talk. This is exactly the kind of problem we love solving.