Google Analytics E-Commerce Dashboard

Interactive business intelligence dashboard analyzing Google Merchandise Store performance (Aug 2016 - Aug 2017)

Overview

Self-service analytics platform built with BigQuery SQL and Google Sheets Connected Sheets, enabling stakeholders to explore 903,653 sessions and $1.54M in e-commerce revenue through interactive filtering and multi-dimensional analysis.

Live Dashboard: View Dashboard (View-only access)

Key Metrics

Metric	Value
Total Revenue	$1,540,071
Total Sessions	903,653
Conversion Rate	1.34%
Average Order Value	$127.12

Business Impact

Mobile Optimization Opportunity

Desktop users generate 95.6% of revenue ($1,472,575) while mobile traffic represents 23% of sessions but only 3.2% of revenue ($49,770). This significant conversion gap presents a clear optimization opportunity - improving mobile experience could unlock substantial additional revenue.

Marketing Channel Insights

• Referral traffic drives 42% of revenue ($651,430) with highest engagement
• Direct traffic contributes 28% of revenue ($434,841)
• Organic Search delivers 21% of revenue ($326,381)
• Combined top 3 channels account for 91% of total revenue

Geographic Distribution

• United States dominates with 93.8% of revenue ($1,444,139)
• International markets represent growth opportunity with only 6.2% revenue share
• United Kingdom and India show early traction as secondary markets

Technical Architecture

Data Layer (BigQuery)

Dataset: ga_analytics (7 SQL views)

Built on Google's public bigquery-public-data.google_analytics_sample dataset containing anonymized Google Analytics 360 data from the Google Merchandise Store.

Key Views:

• daily_summary - Daily performance metrics with bounce rate and conversion rate calculations
• channel_performance - Marketing channel ROI analysis with revenue per visit
• device_analysis - Device category performance comparison
• monthly_trends - Time series aggregations for trend analysis
• geographic_performance - Country-level revenue and engagement metrics
• product_performance - Product-level revenue using UNNEST operations
• summary_totals - Pre-aggregated KPIs optimized for Google Sheets formulas

SQL Techniques Used:

• UNNEST operations for nested product arrays
• Window functions for running totals and rankings
• PARSE_DATE for date transformations
• Complex aggregations across multiple dimensions
• Calculated fields for conversion rates and revenue metrics

Visualization Layer (Google Sheets)

Components:

• 4 interactive pivot tables
• 6 dynamic slicers (month, revenue, visits, channel, country, device)
• Combo chart (revenue bars + visits line)
• Pie chart (revenue distribution by channel)
• 4 KPI cards with real-time metrics

Integration:

• Connected Sheets for live BigQuery connection
• Automated refresh capability
• No-code interface for business users

Features

Interactive Filtering

6 slicers enable real-time exploration:

• Time filters: Month, revenue range, visit range
• Dimension filters: Marketing channel, device type, country

All visualizations update simultaneously when filters are applied.

Multi-Dimensional Analysis

Cross-reference performance across:

• Marketing channels (Referral, Direct, Organic Search, Display, Paid Search, Social, Affiliates)
• Device categories (Desktop, Mobile, Tablet)
• Geographic markets (United States, United Kingdom, India, and more)
• Time periods (13 months from Aug 2016 to Aug 2017)

Self-Service Analytics

Business users can:

• Explore data without SQL knowledge
• Apply filters to answer specific questions
• Export filtered data for presentations
• Refresh data on-demand from BigQuery

Technical Challenges Solved

Challenge 1: Connected Sheets Formula Limitations

Problem: Google Sheets doesn't support complex formulas (TEXT(), COUNTA(), nested calculations) on Connected Sheets data sources.

Solution: Created summary_totals view in BigQuery with pre-calculated metrics:

-- Pre-calculate conversion rate in BigQuery instead of Google Sheets formula
ROUND(SUM(totals.transactions) * 100.0 / NULLIF(SUM(totals.visits), 0), 2) as conversion_rate_pct

Then extracted the single-row result to regular Google Sheets for formula compatibility.

Impact: Enabled accurate KPI calculations while maintaining data integrity.

---

Challenge 2: 250K Row Extract Limit

Problem: master_data contains 903K rows, but Google Sheets extracts are limited to 250K rows, causing incomplete KPIs.

Solution:

• Used Connected Sheets (not extracts) for pivot tables - handles full 903K dataset
• Extracted only the 1-row summary_totals for KPI formulas
• Pivot tables aggregate in BigQuery, not Sheets

Impact: Full dataset analysis without row limitations.

---

Challenge 3: Maintaining Interactivity Across Multiple Data Sources

Problem: Slicers need to filter all pivot tables simultaneously, but each table uses different aggregation levels (daily, monthly, channel, device).

Solution: Built all pivot tables from single master_data source with session-level granularity:

SELECT 
  PARSE_DATE('%Y%m%d', date) as date,
  channelGrouping as channel,
  device.deviceCategory as device,
  geoNetwork.country as country,
  totals.visits,
  totals.transactions,
  totals.transactionRevenue / 1000000 as revenue,
  FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', date)) as month
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'

Impact: Consistent filtering across all visualizations with single slicer click.

Project Structure

google-analytics-dashboard/
├── README.md                          # Project documentation
├── INSIGHTS.md                        # Business insights and recommendations
├── sql/
│   ├── daily_summary.sql             # Daily metrics with bounce/conversion rates
│   ├── channel_performance.sql       # Marketing channel ROI analysis
│   ├── device_analysis.sql           # Device category comparison
│   ├── monthly_trends.sql            # Time series aggregations
│   ├── geographic_performance.sql    # Country-level performance
│   ├── product_performance.sql       # Product revenue with UNNEST
│   └── summary_totals.sql            # Pre-aggregated KPIs
├── screenshots/
│   ├── dashboard-overview.png        # Full dashboard view
│   ├── interactive-filtering.png     # Slicers demonstration
│   └── mobile-insight.png            # Key finding visualization
└── LICENSE                            # MIT License

Technologies Used

• SQL: BigQuery Standard SQL with advanced analytics functions
• Data Warehouse: Google BigQuery
• Visualization: Google Sheets (Pivot Tables, Charts, Slicers)
• Data Integration: Connected Sheets (live BigQuery connection)

Setup Instructions

Prerequisites

• Google Cloud Platform account
• Access to BigQuery
• Google Sheets

Step 1: Create BigQuery Dataset

-- Create dataset in your GCP project
CREATE SCHEMA `your-project-id.ga_analytics`;

Step 2: Create SQL Views

Run each SQL file in the sql/ folder to create the 7 views:

-- Example: Create daily_summary view
-- Copy content from sql/daily_summary.sql and run in BigQuery console

Step 3: Connect Google Sheets to BigQuery

1. Create new Google Sheet
2. Data → Data connectors → Connect to BigQuery
3. Select your project: your-project-id
4. Select dataset: ga_analytics
5. Connect each view as a separate sheet

Step 4: Build Dashboard

Create Pivot Tables:

1. Insert → Pivot table
2. Data range: master_data (entire sheet)
3. Configure rows, values, and sort orders
4. Repeat for all 4 pivot tables

Add Slicers:

1. Click inside pivot table
2. Data → Slicer
3. Select column (channel, device, country, month, revenue, visits)
4. Connect slicer to all pivot tables

Create Visualizations:

1. Insert → Chart
2. Configure combo chart (revenue + visits)
3. Configure pie chart (revenue by channel)

Extract Summary Totals:

1. Go to summary_totals sheet
2. Click "Extract" button
3. Create summary_extract sheet
4. Use for KPI formulas

Step 5: Format Dashboard

• Add colored borders to KPI cards
• Format numbers (currency, percentages)
• Add section headers
• Position slicers on right side
• Align all components

Full setup guide: See SETUP.md for detailed step-by-step instructions.

Use Cases

This dashboard enables stakeholders to answer:

Marketing Questions:

• Which channels deliver the best ROI?
• Where should we allocate marketing budget?
• How do paid vs organic channels compare?

Product Questions:

• How does mobile experience impact conversions?
• What's the revenue difference between device types?
• Should we prioritize mobile optimization?

Growth Questions:

• Which countries show the most potential?
• What are our seasonal revenue patterns?
• Where are untapped market opportunities?

Key Learnings

BigQuery Best Practices

• Pre-aggregate calculations in SQL when Google Sheets formulas have limitations
• Use UNNEST for nested/repeated fields in Google Analytics schema
• Create session-level master table for flexible pivot table filtering
• Optimize for Connected Sheets constraints (row limits, formula restrictions)

Dashboard Design

• Build all pivots from single data source for consistent filtering
• Extract only small aggregated tables for formula use
• Use slicers instead of dropdown filters for better UX
• Pre-calculate metrics in database layer when visualization tool has limitations

Business Intelligence

• Mobile optimization represents largest opportunity (96% desktop revenue concentration)
• Channel diversification could reduce referral dependency risk (42% concentration)
• Geographic expansion could drive growth (94% US revenue concentration)
• Data-driven insights require both technical implementation and business context

Future Enhancements

Potential improvements:

• Add customer cohort analysis
• Implement funnel visualization for checkout process
• Include time-to-purchase metrics
• Add product category performance breakdown
• Integrate with Google Analytics 4 dataset (when available)
• Automate dashboard refresh with Apps Script
• Add anomaly detection alerts

Data Source

Source: Google BigQuery Public Datasets
Dataset: bigquery-public-data.google_analytics_sample
Period: August 1, 2016 - August 1, 2017
Update Frequency: Static historical dataset (for demonstration purposes)

Note: This dataset contains anonymized Google Analytics 360 data from the Google Merchandise Store and is provided by Google for educational and demonstration purposes.

Author

Osvaldo Ruiz
Analyst | Data Visualization | Business Intelligence

• Portfolio: ruizosvaldo.github.io
• LinkedIn: linkedin.com/in/OsvaldoRuiz
• GitHub: github.com/ruizOsvaldo
• Email: oruiz.code@gmail.com

License

MIT License - See LICENSE file for details.

Acknowledgments

• Google Cloud Platform for providing public BigQuery datasets
• Google Analytics sample dataset for realistic e-commerce data
• Google Sheets Connected Sheets for enabling no-code BI solutions

---

Built with: BigQuery SQL • Google Sheets • Connected Sheets • Data Visualization
Project Type: Business Intelligence • Analytics Dashboard • Self-Service Analytics