🔍 Project Overview
This project explores e-commerce data using Google BigQuery, SQL, and Google Sheets. The analysis covers traffic sources, device conversion rates, and top products. Results are visualized and hosted here as a portfolio project.
📌 Key Insights
- 🌐 Traffic Sources: Google Organic Search and Direct visits drove the highest revenue.
- 💻 Device Performance: Mobile had the highest conversion rate (1.67%), followed by Desktop (1.56%).
- 🛍️ Top Products: The Google Zip Hoodie and Crewneck Sweatshirt were top revenue contributors.
📊 Analysis & Queries
📈 Charts & Visualizations
🛠️ SQL Queries
Click below to expand and view the SQL code used for each analysis:
Revenue Sources Query
SELECT
traffic_source.source AS source,
traffic_source.medium AS medium,
SUM(ecommerce.purchase_revenue_in_usd) AS revenue_usd
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
WHERE ecommerce.transaction_id IS NOT NULL
GROUP BY source, medium
ORDER BY revenue_usd DESC;
Conversion Rate Query
SELECT
device.deviceCategory AS device_category,
COUNT(DISTINCT fullVisitorId) AS sessions,
COUNT(ecommerce.transaction_id) AS purchases,
SAFE_DIVIDE(COUNT(ecommerce.transaction_id), COUNT(DISTINCT fullVisitorId)) AS conversion_rate
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`
GROUP BY device_category
ORDER BY conversion_rate DESC;
Top Products Query
SELECT
p.v2ProductName AS product_name,
SUM(p.productQuantity) AS total_units,
SUM(p.productRevenue/1000000) AS revenue_usd
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, UNNEST(hits) AS h, UNNEST(h.product) AS p
WHERE p.productRevenue IS NOT NULL
GROUP BY product_name
ORDER BY revenue_usd DESC
LIMIT 10;