Home About Expertise Projects Blogs Contact
SQLBigQueryCompleted

Target Brazil
E-Commerce SQL Analytics

19-step SQL analysis on 100,000 Brazilian e-commerce orders using Google BigQuery, covering revenue, delivery performance, customer behaviour, and product category insights.

TypeSQL Analytics · Google BigQuery
DomainE-Commerce / Retail
Dataset100K orders · 8 tables · Brazilian market
ToolsSQL · Google BigQuery · Data Studio
CourseScaler Academic Case Study
99,441
Total Orders
₹12.5M
Credit Card Revenue
143%
YoY Revenue Growth
12.5
Avg Delivery Days
01, Business Context

100K orders. What's actually happening?

This analysis covers a Brazilian e-commerce dataset with 100,000 orders across 8 relational tables, customers, orders, order items, payments, reviews, products, sellers, and geolocation. The goal: extract actionable business intelligence using only SQL in Google BigQuery.

🗄️
The schema
8 tables joined on customer_id, order_id, product_id, and seller_id. The analysis runs 19 progressive SQL queries, from basic inspection to complex time-series and customer segmentation.
02, Schema Overview

The data architecture

TableKey ColumnsRecords
ordersorder_id, customer_id, order_status, timestamps99,441
customerscustomer_id, customer_unique_id, state, city99,441
order_itemsorder_id, product_id, seller_id, price, freight112,650+
paymentsorder_id, payment_type, installments, value103,886
order_reviewsorder_id, review_score, comment99,224
productsproduct_id, category, dimensions, weight32,951
sellersseller_id, zip, city, state3,095
geolocationzip_code_prefix, lat, lng, city, state1M+
03, Key SQL Analyses

19 queries. The most important ones.

SQL, customer_segmentation.sql
-- Customer spend segmentation using CTEs
WITH customer_spend AS (
  SELECT
    c.customer_unique_id,
    ROUND(SUM(p.payment_value), 2) AS total_spent,
    COUNT(DISTINCT o.order_id)            AS total_orders,
    ROUND(SUM(p.payment_value) / COUNT(DISTINCT o.order_id), 2) AS avg_spend
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  JOIN payments p  ON o.order_id   = p.order_id
  WHERE o.order_status = 'delivered'
  GROUP BY c.customer_unique_id
),
segmented AS (
  SELECT *,
    CASE
      WHEN avg_spend <= 100  THEN 'Low Spender'
      WHEN avg_spend <= 500  THEN 'Medium Spender'
      ELSE 'High Spender'
    END AS spend_segment
  FROM customer_spend
)
SELECT spend_segment, COUNT(*) AS customers
FROM segmented
GROUP BY spend_segment ORDER BY customers DESC;
-- Medium: 45,167 (48%) · Low: 44,226 (47%) · High: 3,964 (4%)
SQL, yoy_revenue_growth.sql
-- Year-on-Year revenue growth (Jan–Aug comparison)
WITH yearly AS (
  SELECT
    EXTRACT(YEAR FROM o.order_purchase_timestamp) AS year,
    ROUND(SUM(p.payment_value), 2) AS total_payment
  FROM orders o JOIN payments p ON o.order_id = p.order_id
  WHERE o.order_status = 'delivered'
    AND EXTRACT(MONTH FROM order_purchase_timestamp) BETWEEN 1 AND 8
    AND EXTRACT(YEAR FROM order_purchase_timestamp) IN (2017, 2018)
  GROUP BY year
)
SELECT
  MAX(CASE WHEN year=2017 THEN total_payment END) AS y2017,
  MAX(CASE WHEN year=2018 THEN total_payment END) AS y2018,
  ROUND((MAX(CASE WHEN year=2018 ...) - MAX(CASE WHEN year=2017 ...))
    / MAX(CASE WHEN year=2017 ...) * 100, 2) AS pct_increase
FROM yearly;
-- 2017: R$3.47M → 2018: R$8.45M → 143.33% growth
04, Key Business Findings

What the data reveals

💳
Credit card dominates at R$12.5M
Credit card accounts for 73% of total revenue. UPI (boleto) is second. Vouchers and debit cards are niche channels.
📈
143% revenue growth in one year
Jan–Aug 2017 vs 2018: R$3.47M to R$8.45M. Brazilian e-commerce was in hyper-growth mode.
🔁
1.0 orders per customer, zero retention
Every customer bought exactly once. A loyalty programme is the single highest-impact revenue lever available.
🏠
Bed/Bath and Health/Beauty lead revenue
These two categories generate R$1.71M and R$1.65M respectively, but have below-average review scores. Quality improvement opportunity.
🪑
Furniture takes 20+ days to deliver
Furniture office category averages 20.79 delivery days, vs 12.5 day overall average. Needs dedicated logistics handling.
☀️
Afternoon is peak shopping time
36,965 orders placed in the afternoon, more than night and morning combined. Ad spend should be weighted heavily toward 1–6pm.
05, Tech Stack
SQLGoogle BigQueryCTEsWindow FunctionsJOINsTime Series SQLCASE WHEN
← Back to Projects View on GitHub ↗