An end-to-end cloud data analytics project analyzing raw digital e-commerce event streams, user session journeys, multi-stage conversion funnels, and tracking taxonomy using Google BigQuery and Google Analytics 4 (GA4) data architectures.
Modern digital commerce platforms capture granular user touchpoints across millions of daily events. Converting raw semi-structured clickstreams into strategic revenue insights requires structuring unnested JSON payloads, sessionizing user journeys, and isolating conversion bottlenecks.
- Event Modeling & Parameter Unnesting: Query complex nested GA4 schemas and extract key event parameters (
ga_session_id,page_location,currency). - Multi-Step Funnel Conversion: Track progression and drop-off across sequential e-commerce steps:
session_start➔view_item➔add_to_cart➔begin_checkout➔purchase. - Sessionization & User Journey: Reconstruct full user sessions using Window Functions to determine landing pages, exit pages, session duration, and bounce behaviors.
- Monetisation & Cart Abandonment: Quantify cart abandonment rates and Average Order Value (AOV) broken down by device type and marketing channel.
- Data Governance & Taxonomy: Map all custom parameters to an enterprise-grade Customer Tracking Plan.
Google-BigQuery-SQL-Analytics/
├── sql/
│ ├── 01_ecommerce_funnel_analysis.sql # Multi-step conversion funnel and stage drop-offs
│ ├── 02_sessionization_user_journey.sql # Window functions for session duration, landing & bounce
│ └── 03_cart_abandonment_and_aov.sql # Cart creation, abandonment rates, and revenue KPIs
├── customer_tracking_plan.xlsx # Comprehensive event taxonomy & tracking plan
└── README.md # Architecture documentation and project walkthrough
| Technique | Business Application in Queries |
|---|---|
UNNEST(event_params) |
Flattens repeated key-value pairs to extract session identifiers and page paths without Cartesian products. |
| Common Table Expressions (CTEs) | Modularizes multi-step data transformations into isolated, readable, and performant stages. |
Window Functions (ROW_NUMBER()) |
Flags first and last chronological interactions per session to determine landing vs. exit pages. |
Conditional Aggregations (COUNTIF, MAX(IF(...))) |
Efficiently pivots transactional event milestones into session-level binary flags. |
Safe Mathematics (SAFE_DIVIDE) |
Prevents zero-division errors when calculating ratios across low-volume traffic subsets. |
The repository includes customer_tracking_plan.xlsx, specifying:
- Core Event Lifecycle:
session_start,view_item,select_item,add_to_cart,begin_checkout,purchase. - Parameter Governance: Parameter names, required vs. optional constraints, valid data types, sample values, and analytics triggering rules.
- 📄 Google Sheets Tracking Plan (Web Preview)
- 📈 Looker Studio Dynamic Dashboard
- 🎯 Amplitude Behavioral Analytics Dashboard
Suat Amet
Data Analyst & Business Intelligence Specialist
- 🌐 GitHub Profile