BI Portfolio Project · Marc-Olivier do Rego
Ecommerce Marketing Intelligence Dashboard
How it's built:
Two data sources. One warehouse. One source of truth.
GA4 event-level exports and SEMrush keyword data are normalized and unified in BigQuery through a scalable star schema architecture. Metric definitions are computed once in SQL reporting views and reused across the entire dashboard, keeping business logic centralized, consistent, and independent from the visualization layer.
Where reporting becomes automated decision support
The analytics workflow includes an AI recommendation layer where aggregated channel metrics are analyzed through the Claude API to generate concise stakeholder-ready recommendations. The resulting insight is written back into BigQuery and surfaced live inside the dashboard.
01
From business question to dashboard brief
Scoping
The project was scoped as three distinct stakeholder briefs before a single query was written — channel performance for marketing leadership, landing page prioritization for the SEO team, and competitive benchmarking for strategy.
Stakeholder mapping
KPI design
02
Multi-source pipeline, one source of truth
Engineering
GA4 event-level data and SEMrush keyword data are joined through a custom URL normalization pipeline into a BigQuery star schema. Metric definitions are computed once in SQL reporting views and reused across all three pages.
SQL
BigQuery
GA4 + SEMrush
Star schema
03
From business data to automated insight
Delivery
Business performance data flows from BigQuery into an AI generation pipeline. Claude API synthesizes channel metrics into a structured brief covering the top channel, active concern, and recommended action, written back to the warehouse and surfaced live in the dashboard.
Automated insights
Looker Studio
Claude API
Want to learn more about the project?
Marc-Olivier do Rego
BI & Marketing Analytics
Contact
marcolivier.dorego@gmail.com