Consumer360 Data Platform

Building a Governed Analytics Platform for GA4, YouTube, Machine Learning, and Controlled AI

Modern analytics projects often stop at dashboards.

Data is collected, charts are built, a few KPIs are displayed, and the project is considered complete. While dashboards are useful, they represent only the final presentation layer of a much larger analytical system.

Consumer360 was built around a different idea.

Instead of treating visualization as the core product, Consumer360 treats analytics as an end-to-end engineering system involving:

source ingestion;
data validation;
audit evidence;
transformation contracts;
data-quality testing;
machine-learning enrichment;
model provenance;
governed AI access;
read-only analytical serving;
observability;
and user-facing analytics.

The result is a governed analytics platform that transforms Google Analytics 4 and YouTube data into traceable, tested, machine-learning-enriched, and AI-accessible business intelligence.

The platform is deployed publicly through a Streamlit analytical interface backed by an authenticated FastAPI service and a restricted PostgreSQL database identity.

Live application:
https://consumer360-data-platform.streamlit.app

API service:
https://consumer360-api-adityamsr2606.onrender.com

Project Objective

The main objective of Consumer360 was to build something closer to a production-style data platform than a traditional portfolio dashboard.

The system needed to answer several engineering questions.

How can raw analytics data be collected reliably?

How can every ingestion request be traced?

How can transformed metrics be validated before they reach a dashboard?

How can machine-learning outputs retain their model and training provenance?

How can an LLM answer business questions without receiving unrestricted database access?

How can the final application remain useful while still operating under explicit security and reliability constraints?

Consumer360 addresses these questions through a layered architecture built around data contracts, provenance, least privilege, and controlled intelligence.

Data Sources

Consumer360 currently integrates two real external data sources.

Google Analytics 4

Google Analytics 4 provides website-level intelligence including:

acquisition;
engagement;
geography;
devices;
browsers;
operating systems;
user behaviour;
events;
session acquisition;
first-user acquisition;
and reporting coverage.

GA4 ingestion requires an explicit inclusive date range so that every pipeline run has a clearly defined analytical window.

YouTube Data API

The YouTube Data API provides content and audience intelligence including:

channel metrics;
video catalogue information;
video performance;
publication trends;
engagement;
comments;
language;
sentiment;
audience topics;
and topic-level performance.

The ingestion process is designed to operate within API quota constraints while retaining request-level and run-level audit evidence.

Four Analytical Products

The raw data ultimately feeds four connected analytical products.

Executive Intelligence

The executive layer provides a high-level view of business performance across platforms.

It includes:

cross-platform KPIs;
available data coverage;
recent activity;
engagement indicators;
and current analytical signals.

This layer is designed for users who need a quick summary without exploring individual source systems.

Source Intelligence

Source-specific analytical marts expose governed GA4 and YouTube intelligence.

GA4 views cover acquisition, behaviour, geography, technology, events, and daily performance.

YouTube views cover channel performance, video metrics, content trends, sentiment, and topic intelligence.

Audience Intelligence

The audience-intelligence layer combines machine learning and analytical modelling.

It includes:

language detection;
sentiment classification;
topic eligibility;
semantic embeddings;
topic discovery;
reviewed topic catalogues;
topic-level sentiment;
and topic-level video performance.

This allows the platform to move beyond basic engagement metrics and examine what audiences are actually discussing.

Operational Intelligence

The platform also exposes information about its own operation.

Operational views provide:

pipeline-run history;
ingestion evidence;
report coverage;
data-quality results;
monitoring signals;
and runtime diagnostics.

This makes the system itself observable rather than treating data pipelines as invisible background processes.

Technology Stack

Consumer360 combines data engineering, backend development, analytics, machine learning, deployment, and AI governance.

Core Development

The platform is primarily built using:

Python 3.12;
SQL;
PowerShell.

These technologies are used for modular programming, automation, configuration management, analytical processing, and infrastructure-related workflows.

Data Processing

The analytical processing layer uses:

pandas;
NumPy.

These libraries support:

data cleaning;
aggregation;
feature engineering;
preparation of analytical datasets;
and machine-learning preprocessing.

Storage and Transformation

The data platform uses:

PostgreSQL 18;
Neon PostgreSQL;
dbt Core.

PostgreSQL provides the central relational storage system, while dbt manages the transformation graph between staging, warehouse, and analytics layers.

Machine Learning and NLP

The machine-learning layer uses:

scikit-learn;
Sentence Transformers;
Hugging Face Transformers;
MiniLM embeddings;
UMAP;
HDBSCAN.

These technologies support:

text embeddings;
sentiment analysis;
topic eligibility classification;
semantic clustering;
topic discovery;
and reproducible model inference.

Backend Serving

The serving layer is implemented using:

FastAPI;
Pydantic;
psycopg2;
Uvicorn.

FastAPI exposes analytical datasets through controlled REST endpoints rather than allowing the frontend to connect directly to PostgreSQL.

Governed AI

The AI layer combines:

Gemini;
SQLGlot;
deterministic routing;
bounded evidence context;
validated NL-SQL;
and read-only analytical execution.

The LLM is treated as an analytical assistant rather than a database authority.

Dashboard and Visualization

The analytical interface uses:

Streamlit;
Plotly.

The dashboard contains multiple dedicated analytical workspaces instead of presenting a single monolithic page.

Orchestration and Monitoring

Workflow orchestration is represented through:

Apache Airflow DAGs;
pipeline audit tables;
data-quality evidence;
monitoring policies;
operational API endpoints.

Testing

Testing includes:

pytest;
dbt data tests;
contract testing;
security testing;
API validation;
regression testing;
integration testing.

Deployment and Security

Deployment uses:

Docker;
Docker Compose;
Render;
Streamlit Community Cloud;
Neon PostgreSQL;
GitHub.

The deployment architecture separates the public dashboard, authenticated API, and restricted database identity.

System Architecture

Consumer360 follows a layered data-platform architecture.

The high-level data flow is:

GA4 and YouTube provide source data.
Python ingestion pipelines retrieve and validate records.
Raw data and ingestion evidence are written to PostgreSQL.
dbt transforms source-aligned data into governed analytical models.
Machine-learning workflows enrich audience data.
Analytical marts combine business and model outputs.
FastAPI exposes approved analytical relations.
AI components query bounded analytical data through controlled paths.
Streamlit presents the final intelligence layer.

The dashboard never connects directly to PostgreSQL.

Instead, the serving path is:

Streamlit → FastAPI → restricted PostgreSQL identity

This keeps the user interface separated from database credentials and SQL execution.

Data Architecture

Consumer360 uses six PostgreSQL schemas, each with a defined responsibility.

`raw`

The raw schema stores source-aligned GA4 and YouTube records.

The goal is to preserve ingestion provenance before transformations are applied.

`audit`

The audit schema stores operational evidence such as:

pipeline runs;
ingestion logs;
report coverage;
quality results;
request-level evidence.

This creates a durable history of how data entered the system.

`staging`

The staging layer contains cleaned and typed dbt views.

Its responsibilities include:

standardising data types;
normalising fields;
controlling row grain;
preparing source records for downstream transformation.

`warehouse`

The warehouse schema contains reusable dimensions and analytical facts.

These models form the stable analytical foundation between raw source data and business-facing marts.

`ml`

The ml schema stores machine-learning outputs and provenance.

It includes:

language predictions;
sentiment results;
topic eligibility;
topic assignments;
model metrics;
model versions;
prediction probabilities;
reviewed topic catalogues.

`analytics`

The analytics schema contains the final business-facing marts consumed by FastAPI.

This separation ensures that the API reads governed analytical products rather than raw operational tables.

Database Bootstrap and Reproducibility

The foundational database structures are versioned under:

database/bootstrap/001_base_schema.sql

The bootstrap creates the core raw, audit, and ml structures.

To verify reproducibility, the schema was restored into a newly created temporary PostgreSQL database.

The verification produced:

4 audit tables;
7 ML tables;
6 raw tables.

The temporary database was then removed.

This confirms that the foundational database structure can be recreated from version-controlled SQL rather than depending on manually created local state.

dbt Transformation Layer

dbt owns the staging, warehouse, and analytics transformation graph.

The project contains:

13 staging models;
4 warehouse models;
20 analytics models;
37 total dbt models.

Staging models are generally materialized as views.

Warehouse and analytics models are materialized as tables.

A custom generate_schema_name macro ensures that models resolve directly into their intended PostgreSQL schemas instead of receiving default dbt schema prefixes.

Representative analytical models include:

analytics.consumer360_executive_snapshot;
analytics.consumer360_daily_overview;
analytics.ga4_daily_kpis;
analytics.ga4_session_acquisition_performance;
analytics.ga4_first_user_acquisition_performance;
analytics.youtube_content_performance;
analytics.youtube_daily_trends;
analytics.youtube_video_sentiment;
analytics.youtube_topic_summary;
analytics.youtube_topic_sentiment;
analytics.youtube_topic_video_performance.

Record-Level Data Journey

Every analytical record follows a controlled path through the platform.

Stage 1 — Source Retrieval

GA4 reports or YouTube resources are requested from their respective APIs.

Stage 2 — Validation

Python ingestion pipelines validate responses before persistence.

Stage 3 — Audit Evidence

Pipeline and request information is recorded in the audit schema.

Stage 4 — Transformation

dbt converts source-aligned records into typed staging views and reusable warehouse models.

Stage 5 — Machine-Learning Enrichment

Machine-learning workflows generate language, sentiment, eligibility, and topic outputs.

Stage 6 — Analytical Modelling

Analytics models combine business data and reviewed ML outputs.

Stage 7 — Controlled Serving

FastAPI reads approved relations through a restricted database role.

Stage 8 — User Experience

Streamlit dashboards and AI features consume the same governed serving contracts.

Governed Artificial Intelligence

One of the main engineering goals of Consumer360 was to prevent the language model from becoming an unrestricted SQL generator.

The platform therefore implements a controlled question-to-answer path.

AI Request Flow

When a user asks an analytical question:

FastAPI validates the request.
Deterministic routing chooses the permitted analytical domain.
Gemini receives bounded system instructions and evidence.
Generated SQL is parsed using SQLGlot.
Statement and relation policies are checked.
PostgreSQL executes the query using a restricted read-only identity.
Runtime safety conditions are verified.
Gemini synthesizes an answer only from the bounded query result.

This structure separates language generation from database authority.

AI Safety Boundaries

The AI layer operates under several explicit limits.

Examples include:

maximum question length of 1,000 characters;
exactly one generated SQL statement;
maximum NL-SQL result size of 100 rows;
five-second SQL statement timeout;
30-second Gemini request timeout;
75-second dashboard AI request budget;
read-only database transactions;
rejection of unqualified relation access;
rejection of wildcard relation access;
rejection of unapproved database relations.

These restrictions reduce the risk of unbounded or unsafe query behaviour.

Database Least Privilege

The API does not use a PostgreSQL superuser account.

Its database identity is configured to:

operate without superuser privileges;
default to read-only transactions;
read only approved analytical and audit relations;
enforce statement timeouts;
enforce lock timeouts;
enforce idle-transaction limits;
enforce connection timeouts.

This ensures that even if application logic fails, the database identity itself cannot modify analytical data.

Preserving Uncertainty

Consumer360 was designed to avoid producing confident narratives when the available data does not justify them.

For example, one verified question asked which acquisition channel generated the most sessions.

The governed analytical result contained only:

Direct | 21 sessions

Instead of presenting this as a definitive ranking against other channels, the AI explicitly stated that only one category was represented and therefore a cross-category comparison could not be established.

This behaviour is deliberate.

The system prefers preserved uncertainty over fabricated certainty.

Machine-Learning Layer

The machine-learning pipeline is designed as an analytical enrichment system rather than an automated source of truth.

Its audience-intelligence path includes:

raw comments;
language detection;
sentiment analysis;
topic eligibility classification;
topic discovery;
reviewed analytical topics.

Topic Eligibility Classification

Not every YouTube comment is suitable for topic modelling.

Some comments may contain:

greetings;
emojis;
generic praise;
spam;
extremely short responses;
unrelated discussion.

Consumer360 therefore uses a topic-eligibility classifier before clustering.

The model uses:

MiniLM sentence embeddings;
a balanced linear support-vector classifier.

The reviewed training dataset contained:

347 reviewed examples;
242 topical examples;
105 non-topical examples.

Verified evaluation results were:

holdout accuracy: 0.7810;
holdout balanced accuracy: 0.7196;
holdout macro F1: 0.7289;
cross-validation macro F1: 0.7357 ± 0.0792.

The production eligibility snapshot evaluated 5,076 comments.

Of these:

3,040 comments were retained as topical;
2,036 comments were excluded.

The reviewed training dataset is also associated with a persisted cryptographic hash, allowing training-data provenance to be verified.

Topic Discovery

Comments retained by the eligibility model are passed to the topic-discovery pipeline.

The reviewed topic model uses:

MiniLM embeddings;
UMAP dimensionality reduction;
HDBSCAN density clustering.

The production model version is:

minilm_umap_hdbscan_filtered_v1

The reviewed corpus contained 3,040 eligible comments.

Topic modelling produced:

2,153 clustered comments;
887 noise points;
70.82% cluster coverage;
0.5797 silhouette score;
20 reviewed clusters;
17 accepted topics;
3 excluded clusters.

New candidate clusters are intentionally isolated from the production topic catalogue until they are reviewed and explicitly promoted.

Sentiment and Language Intelligence

The enrichment pipeline persists more than just prediction labels.

For every supported prediction, the platform can retain:

detected language;
normalized language code;
sentiment label;
sentiment score;
confidence;
model name;
model version;
topic eligibility;
decision provenance;
topic assignment;
cluster probability;
reviewed catalogue status.

The verified sentiment snapshot contained 6,903 analyzed comments with no invalid stored sentiment classifications.

These metrics describe the reviewed Consumer360 dataset and are not intended as universal performance claims.

GA4 Analytics

The GA4 mart family provides several analytical views.

These include:

daily KPI performance;
consumer behaviour;
events;
geography;
device intelligence;
browser intelligence;
operating-system intelligence;
session acquisition;
first-user acquisition;
date and report coverage.

This allows the dashboard to move between executive KPIs and lower-level source analysis while remaining on the same governed transformation layer.

YouTube Analytics

The YouTube mart family provides:

channel KPIs;
video-level performance;
publication trends;
engagement trends;
comment features;
sentiment;
topic corpus analysis;
topic catalogue intelligence;
topic-level sentiment;
topic-level video performance.

Machine-learning outputs are therefore integrated with traditional platform metrics instead of existing as a separate experiment.

Cross-Platform Intelligence

Consumer360 also creates cross-platform analytical products combining GA4 and YouTube signals.

An important design rule is that the system does not invent missing source observations merely to create aligned charts.

The cross-platform layer therefore aligns observed dates and known activity while preserving gaps where data is unavailable.

Operational Intelligence

Consumer360 exposes operational information through dedicated API endpoints.

These include information about:

pipeline runs;
ingestion requests;
quality tests;
monitoring policies;
operational signals.

An empty monitoring result is not interpreted as universal system health.

It only means that none of the currently configured monitoring policies are emitting a signal.

Streamlit Product Experience

The Streamlit application contains ten dedicated analytical workspaces.

1. Command Center

Provides the main executive overview.

2. Consumer360 AI

Provides controlled AI-based analytical interaction.

3. GA4 Intelligence

Explores website analytics and acquisition.

4. YouTube Intelligence

Explores channel and video-level performance.

5. Cross-Platform

Combines available signals from GA4 and YouTube.

6. Topic Intelligence

Examines discovered and reviewed audience topics.

7. Sentiment Lab

Explores audience sentiment analysis.

8. Pipeline Observatory

Shows pipeline runs and ingestion evidence.

9. Data Health

Surfaces data-quality and monitoring information.

10. System

Provides platform and runtime information.

All dashboard data is retrieved through FastAPI rather than direct database access.

CSV exports are also neutralized against spreadsheet-formula prefixes before being downloaded.

REST API

FastAPI acts as the central serving contract between the analytics platform and the dashboard.

The API contains 24 routes across several functional areas.

Health and Integrated Intelligence

Endpoints include:

/;
/health;
/health/ready;
/api/v1/executive-snapshot;
/api/v1/daily-overview.

GA4 Endpoints

GA4 routes include:

coverage;
daily KPIs;
events;
geography;
devices;
browser and operating-system intelligence;
session acquisition;
first-user acquisition.

YouTube and Audience Endpoints

YouTube routes provide access to:

channel KPIs;
videos;
sentiment;
topics.

Operational Endpoints

Operational APIs expose:

pipeline runs;
ingestion logs;
data quality;
monitoring signals.

AI Endpoints

The controlled AI layer includes:

executive insight;
Copilot;
natural-language SQL queries;
bounded agents.

The complete OpenAPI specification is available from /openapi.json, with interactive Swagger documentation available under /docs.

Workflow Orchestration

Consumer360 includes two Apache Airflow DAG definitions.

GA4 Pipeline

The GA4 workflow follows:

GA4 ingestion → dbt run → dbt test

It requires explicit start_date and end_date parameters.

YouTube Pipeline

The YouTube workflow follows:

YouTube ingestion → dbt run → dbt test

Both workflows are configured with:

two retries;
a five-minute retry delay.

Airflow is versioned with the repository but is intentionally separated from the Windows serving environment.

On Windows, Airflow is expected to run through WSL2 or Linux containers.

It is not currently included in the main Docker Compose serving stack.

Testing and Verification

A major goal of the project was to provide executable evidence instead of relying on a feature checklist.

Python Test Suite

The project currently reports:

179 passing Python tests

The test suite covers:

ingestion lifecycle behaviour;
pipeline auditing;
API contracts;
input validation;
AI routing;
AI provider failures;
NL-SQL parser security;
NL-SQL executor security;
database read-only behaviour;
monitoring policies;
dashboard API integration;
Airflow DAG contracts;
spreadsheet-safe CSV exports.

dbt Verification

The dbt build reports:

PASS = 143
WARN = 0
ERROR = 0
SKIP = 0

This includes:

37 dbt models;
106 dbt data tests.

Runtime Verification

The local runtime has also been verified at the infrastructure level.

Confirmed conditions include:

healthy API container;
healthy dashboard container;
ready API status;
available PostgreSQL connection;
non-superuser API database role;
read-only API transactions;
clean dependency checks;
successful database bootstrap restore.

Cloud Deployment Architecture

Consumer360 supports two operating modes.

The first is a localhost-bound Docker Compose environment used for development and verification.

The second is a prepared free-cloud serving topology consisting of:

Streamlit Community Cloud;
Render;
Neon PostgreSQL.

The deployed architecture is:

Streamlit dashboard
→ authenticated Render FastAPI service
→ restricted read-only Neon PostgreSQL identity
→ governed analytical snapshot

Cloud Security Controls

The cloud deployment includes several security mechanisms.

These include:

dashboard-to-API service authentication;
protection of all /api/v1 analytical routes;
minimal public health endpoints;
per-client AI request budgets;
global AI request budgets;
single-request AI concurrency enforcement;
dedicated non-superuser Neon role;
PostgreSQL TLS requirements;
channel binding;
Render dynamic-port support;
managed secret placeholders;
Streamlit-specific dependency configuration.

The deployed API image has successfully:

connected to Neon;
authenticated as consumer360_api;
passed readiness checks;
blocked anonymous analytical requests;
returned real analytical snapshots through authenticated requests.

Repository Structure

The repository is organized by responsibility.

Important directories include:

ai/ for agents, routing, synthesis, and NL-SQL;
api/ for FastAPI routers, repositories, and contracts;
config/ for environment-driven configuration;
dashboard/ for the Streamlit application;
database/bootstrap/ for PostgreSQL foundation scripts;
dbt/consumer360/ for models, tests, macros, and profile templates;
ingestion/ for GA4 and YouTube ingestion pipelines;
ml/ for language, sentiment, eligibility, and topic modelling;
monitoring/ for operational monitoring policies;
orchestration/airflow/dags/ for Airflow workflows;
scripts/ for executable commands;
tests/ for unit, contract, security, and integration tests.

The repository also includes separate Dockerfiles for the API and dashboard, together with a Docker Compose configuration.

Reproducibility and Local Execution

The project uses Python 3.12 and provides versioned dependency files for different components.

A local development environment can be created using a Python virtual environment.

Configuration is stored through environment variables, while secrets such as:

database credentials;
GA4 service accounts;
YouTube API keys;
Gemini API keys;

are kept outside version control.

PostgreSQL can then be initialized using the versioned bootstrap SQL.

After database initialization, dbt is configured through an environment-driven profile.

The standard processing sequence is:

run GA4 ingestion;
run YouTube ingestion;
execute dbt build;
start FastAPI;
start Streamlit.

Docker Compose can run the API and dashboard containers together.

Evidence Instead of Feature Claims

One design principle behind Consumer360 is that project features should be demonstrable.

Examples include:

ingestion functionality supported by audit records;
transformation functionality supported by 37 dbt models;
quality controls supported by 106 dbt tests;
machine learning supported by persisted predictions and model provenance;
API serving supported by 24 routes;
security supported by restricted database roles;
AI governance supported by parsed and validated SQL;
runtime reliability supported by container health checks;
reproducibility supported by pinned dependencies and restore-tested bootstrap scripts.

The platform therefore attempts to make engineering claims verifiable rather than descriptive.

Current Scope and Limitations

Consumer360 intentionally documents what it does not yet provide.

The current platform integrates only:

Google Analytics 4;
YouTube.

The deployed PostgreSQL environment contains a validated analytical snapshot rather than continuously refreshed pipelines.

Ingestion, dbt, ML, and snapshot refresh remain controlled workflows rather than permanently hosted background jobs.

Airflow DAGs are version-controlled but are not deployed as part of the public serving environment.

The dashboard service key authenticates the Streamlit service to FastAPI. It is not an end-user identity-management system.

AI rate limiting is implemented in-process and is designed for a single free API instance rather than a distributed multi-node environment.

Free hosting may produce cold starts and does not provide an availability guarantee.

Machine-learning results apply to the reviewed Consumer360 corpus and should not automatically be generalized to other datasets.

New topic candidates must be reviewed before they become production analytical topics.

An empty monitoring response also does not imply that all upstream dependencies are healthy.

Engineering Principles

Several principles guided the design of Consumer360.

Evidence Before Narrative

Claims should be supported by executable tests, persisted evidence, or observable runtime behaviour.

Contracts Before Convenience

Data structures, API boundaries, and analytical relations should be explicitly defined.

Read-Only by Default

Analytical serving components should receive the minimum database permissions necessary.

Bound Expensive Operations

AI calls, SQL queries, result sizes, and request durations should operate under explicit limits.

Persist Provenance

Predictions should retain information about their model, version, and decision context.

Promote Reviewed Models

Experimental candidates should not automatically become production analytical outputs.

Preserve Uncertainty

The system should expose insufficient evidence instead of generating unsupported certainty.

What the Project Demonstrates

Consumer360 brings together several disciplines normally demonstrated separately in portfolio projects.

From a data-engineering perspective, it demonstrates:

API ingestion;
PostgreSQL modelling;
dimensional transformation;
dbt;
testing;
orchestration;
provenance;
monitoring.

From a backend-engineering perspective, it demonstrates:

FastAPI;
typed contracts;
repository patterns;
authentication;
controlled database access;
REST API design.

From a machine-learning perspective, it demonstrates:

text embeddings;
supervised classification;
sentiment analysis;
dimensionality reduction;
density-based clustering;
model evaluation;
reproducible inference;
model provenance.

From an AI-engineering perspective, it demonstrates:

grounded generation;
deterministic routing;
NL-SQL;
SQL parsing;
relation allowlists;
bounded execution;
safe synthesis.

From a platform-engineering perspective, it demonstrates:

Docker;
container health checks;
environment-based configuration;
cloud deployment;
service authentication;
least-privilege database roles;
runtime verification.

Final Outcome

Consumer360 started as an analytics project but evolved into a broader data-platform engineering exercise.

Its central idea is that business intelligence should not begin at the dashboard.

It should begin with trustworthy ingestion, continue through governed transformations and validated machine-learning enrichment, and end with controlled analytical access.

By combining GA4, YouTube, PostgreSQL, dbt, FastAPI, Streamlit, machine learning, Docker, Airflow, and governed AI, Consumer360 demonstrates how several modern data-platform components can operate as a single traceable system.

The project is therefore less about displaying a large number of charts and more about answering a more important engineering question:

Can every analytical output be traced, tested, controlled, and explained?

Consumer360 is built around making the answer to that question yes.