SQL · BigQuery

eCommerce User Behavior Exploration

⚙️ Tool

BigQuery + Google Analytics sample dataset.

🛠️ Approach

1️⃣ I turned the analysis into 8 focused SQL questions so each query answers one business point.
2️⃣ I combined CTEs and UNNEST to cleanly work with nested hits/product data.
3️⃣ I read the journey in order: traffic quality first, then revenue trends, then funnel conversion.

💡 Insight

1️⃣ March showed the strongest transaction momentum, signaling healthier purchase intent.
2️⃣ Google drove volume, but channel quality varied a lot once bounce and conversion were checked.
3️⃣ Direct traffic and deeper pageviews were closely tied to stronger revenue outcomes.

Python · Pandas

Retailer RFM Segmentation

⚙️ Tool

Python (Pandas, NumPy, Seaborn, Matplotlib, Squarify).

🛠️ Approach

1️⃣ I cleaned and standardized raw orders first so the segment logic runs on trustworthy data.
2️⃣ I scored Recency, Frequency, and Monetary with quantile ranks, then mapped customers to clear segment labels.
3️⃣ I summarized each segment with behavior + revenue views to make campaign planning practical.

💡 Insight

1️⃣ Customer value is uneven, so sending one generic campaign to everyone wastes budget.
2️⃣ Potential Loyalists are the best growth lever, while At-Risk groups need timely win-back actions.
3️⃣ Recency and Frequency are usually the fastest levers for retention impact.

Power BI · DAX

Purchasing Department Performance

⚙️ Tool

Power BI + DAX + Power Query.

🛠️ Approach

1️⃣ I used design thinking first to pin down user pain points and decision moments.
2️⃣ I built a KPI-first structure with an executive overview and vendor drill-down flow.
3️⃣ I modeled relationships and DAX measures to make comparisons fast and reliable in monthly reviews.

💡 Insight

1️⃣ Vendor performance is uneven, so procurement decisions need segmented actions, not blanket rules.
2️⃣ A KPI-first dashboard helps managers spot delivery and cost issues much earlier.
3️⃣ Drill-down views make negotiation priorities clearer and easier to defend.

Toolkit

  • Excel
  • SQL
  • Power BI
  • Python
SQL · BigQuery

SQL Explore eCommerce Dataset

Analyzing user engagement and revenue trends on the Google Analytics public dataset in BigQuery.

Approach & Platform: Explore a public dataset of Google Analytics in BigQuery. Main Techniques: Window functions, CTEs, UNNEST on nested hits and product arrays.

Result: Extract key insights, such as engagement rates and revenue performance over time.

Query 01 · Total visits, pageviews, transactions (Jan–Mar 2017)

SELECT
    FORMAT_DATE("%m-%Y", PARSE_DATE("%Y%m%d", date)) AS month,
    SUM(totals.visits)       AS number_of_visit,
    SUM(totals.pageviews)    AS number_of_pageview,
    SUM(totals.transactions) AS number_of_transaction
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`
WHERE _table_suffix BETWEEN '0101' AND '0331'
GROUP BY month;
MonthVisitsPageviewsTransactions
01-201764,694257,708713
02-201762,192233,373733
03-201769,931259,522993

✅ March 2017 had the most transactions; January 2017 had the highest visits.

Query 02 · Bounce rate per traffic source (Jul 2017)

SELECT
    trafficSource.source,
    COUNT(visitNumber)                                       AS total_visits,
    SUM(totals.bounces)                                      AS total_no_of_bounces,
    ROUND(SUM(totals.bounces) / COUNT(visitNumber) * 100, 2) AS bounce_percent
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
GROUP BY trafficSource.source
ORDER BY total_visits DESC;
SourceVisitsBouncesBounce rate
google38,40019,79851.56%
(direct)19,8918,60643.27%
youtube.com6,3514,23866.73%
analytics.google.com1,9721,06453.96%
m.facebook.com66943064.28%

✅ Google had the highest visits; YouTube and Facebook had the highest bounce rates.

Query 03 · Revenue by traffic source, weekly & monthly (Jun 2017)

WITH monthly_revenue AS (
    SELECT "month" AS time_type,
        FORMAT_DATE("%m-%Y", PARSE_DATE("%Y%m%d", date)) AS time,
        trafficSource.source AS source,
        ROUND(SUM(product.productRevenue / 1000000), 2) AS revenue
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201706*`,
        UNNEST(hits) AS hits, UNNEST(hits.product) AS product
    WHERE product.productRevenue IS NOT NULL
    GROUP BY time_type, time, source
),
weekly_revenue AS (
    SELECT "week" AS time_type,
        FORMAT_DATE("%W-%Y", PARSE_DATE("%Y%m%d", date)) AS time,
        trafficSource.source AS source,
        ROUND(SUM(product.productRevenue) / 1000000, 2) AS revenue
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201706*`,
        UNNEST(hits) AS hits, UNNEST(hits.product) AS product
    WHERE product.productRevenue IS NOT NULL
    GROUP BY time_type, time, source
)
SELECT * FROM monthly_revenue
UNION ALL SELECT * FROM weekly_revenue
ORDER BY revenue DESC;
TypeTimeSourceRevenue
Month06-2017(direct)$97,333.62
Week24-2017(direct)$30,908.91
Week25-2017(direct)$27,295.32
Month06-2017google$18,757.18
Month06-2017dfa$8,862.23

✅ Direct traffic dominated June 2017 with ~$97k monthly revenue, far ahead of Google ($18.7k).

Query 04 · Avg pageviews: purchasers vs non-purchasers (Jun–Jul 2017)

WITH purchase_data AS (
    SELECT FORMAT_DATE("%m-%Y", PARSE_DATE("%Y%m%d", date)) AS month,
        ROUND(SUM(totals.pageviews) / COUNT(DISTINCT fullVisitorId), 2) AS avg_pageviews_purchase
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
        UNNEST(hits) AS hits, UNNEST(hits.product) AS product
    WHERE totals.transactions IS NOT NULL AND product.productRevenue IS NOT NULL
        AND _table_suffix BETWEEN '0601' AND '0731'
    GROUP BY month
),
non_purchase_data AS (
    SELECT FORMAT_DATE("%m-%Y", PARSE_DATE("%Y%m%d", date)) AS month,
        ROUND(SUM(totals.pageviews) / COUNT(DISTINCT fullVisitorId), 2) AS avg_pageviews_non_purchase
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
        UNNEST(hits) AS hits, UNNEST(hits.product) AS product
    WHERE totals.transactions IS NULL AND product.productRevenue IS NULL
        AND _table_suffix BETWEEN '0601' AND '0731'
    GROUP BY month
)
SELECT p.month, p.avg_pageviews_purchase, n.avg_pageviews_non_purchase
FROM purchase_data p
FULL OUTER JOIN non_purchase_data n USING (month)
ORDER BY p.month;

✅ Purchasers viewed significantly more pages than non-purchasers; July had slightly higher averages than June.

Query 05 · Avg transactions per purchasing user (Jul 2017)

SELECT
    "201707" AS Month,
    ROUND(SUM(totals.transactions) / COUNT(DISTINCT fullVisitorId), 2)
        AS Avg_total_transactions_per_user
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`,
    UNNEST(hits) AS hits, UNNEST(hits.product) AS product
WHERE totals.transactions >= 1
    AND product.productRevenue IS NOT NULL;
MonthAvg transactions / user
2017074.16

Query 06 · Avg revenue per session for purchasers (Jul 2017)

SELECT
    FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', date)) AS month,
    ROUND((SUM(product.productRevenue) / SUM(totals.visits)) / 1000000, 2)
        AS Avg_revenue_by_user_per_visit
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`,
    UNNEST(hits) AS hits, UNNEST(hits.product) AS product
WHERE product.productRevenue IS NOT NULL
    AND totals.transactions IS NOT NULL
GROUP BY month;
MonthAvg revenue / session
2017-07$43.86

Query 07 · Cross-sell from “YouTube Men’s Vintage Henley” (Jul 2017)

WITH henley_cust_id AS (
    SELECT DISTINCT fullVisitorId
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`,
        UNNEST(hits) AS hits, UNNEST(hits.product) AS product
    WHERE product.v2ProductName = "YouTube Men's Vintage Henley"
        AND totals.transactions >= 1
        AND product.productRevenue IS NOT NULL
)
SELECT
    product.v2ProductName AS other_purchased_products,
    SUM(product.productQuantity) AS quantity
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`,
    UNNEST(hits) AS hits, UNNEST(hits.product) AS product
WHERE fullVisitorId IN (SELECT * FROM henley_cust_id)
    AND product.v2ProductName <> "YouTube Men's Vintage Henley"
    AND totals.transactions >= 1
    AND product.productRevenue IS NOT NULL
GROUP BY product.v2ProductName
ORDER BY quantity DESC;
Other purchased productsQty
Google Sunglasses20
Google Women's Vintage Hero Tee7
SPF-15 Slim & Slender Lip Balm6
Google Women's Short Sleeve Hero Tee4
YouTube Men's Fleece Hoodie Black3
Google Men's Short Sleeve Badge Tee3

✅ Customers who bought the Henley favored Google Sunglasses, highlighting strong interest in casual Google/YouTube branded accessories.

Query 08 · Cohort funnel: view → cart → purchase (Q1 2017)

WITH productview AS (
    SELECT FORMAT_DATE("%Y%m", PARSE_DATE("%Y%m%d", date)) AS month,
        COUNT(eCommerceAction.action_type) AS num_product_view
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`, UNNEST(hits) AS hits
    WHERE _table_suffix BETWEEN '0101' AND '0331'
        AND eCommerceAction.action_type = '2'
    GROUP BY month
),
cart AS (
    SELECT FORMAT_DATE("%Y%m", PARSE_DATE("%Y%m%d", date)) AS month,
        COUNT(eCommerceAction.action_type) AS num_addtocart
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`, UNNEST(hits) AS hits
    WHERE _table_suffix BETWEEN '0101' AND '0331'
        AND eCommerceAction.action_type = '3'
    GROUP BY month
),
purchase AS (
    SELECT FORMAT_DATE("%Y%m", PARSE_DATE("%Y%m%d", date)) AS month,
        COUNT(eCommerceAction.action_type) AS num_purchase
    FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
        UNNEST(hits) AS hits, UNNEST(hits.product) AS product
    WHERE _table_suffix BETWEEN '0101' AND '0331'
        AND eCommerceAction.action_type = '6'
        AND product.productRevenue IS NOT NULL
        AND totals.transactions IS NOT NULL
    GROUP BY month
)
SELECT month, num_product_view, num_addtocart, num_purchase,
    ROUND(num_addtocart / num_product_view * 100, 2) AS add_to_cart_rate,
    ROUND(num_purchase  / num_product_view * 100, 2) AS purchase_rate
FROM productview JOIN cart USING (month) JOIN purchase USING (month)
ORDER BY month;
MonthViewsAdd-to-cartPurchasesATC ratePurchase rate
20170125,7877,3422,14328.47%8.31%
20170221,4897,3602,06034.25%9.59%
20170323,5498,7822,97737.29%12.64%

✅ March 2017 peaked at 12.64% purchase rate; upward trend across all three months signals improving funnel health.

View .sql file
Python · Pandas

Python RFM Model Analysis

Segment SuperStore customers by Recency, Frequency, and Monetary value to power targeted marketing campaigns.

Platform: Google Colab. Library: Pandas, NumPy, Seaborn, Matplotlib, Squarify. Technique: EDA, data transformations, aggregation and grouping.

Context: SuperStore, a global retail company, seeks to segment its large customer base for marketing campaigns. The RFM model scores each customer on three dimensions: how recently they bought, how often, and how much they spent.

1 · Data Cleaning

The dataset has two tables: transaction information (many-to-one: each order ID → multiple product IDs) and a segmentation lookup table.

Import libraries

!pip install squarify

from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import squarify

Import data

data_link = '1yNt8-kkoDyYzq8tYbqWRqqrAfyhPNtBlfSo-9aRvbCY'
data = gc.open_by_key(data_link)

order_info   = pd.DataFrame(data.worksheet('ecommerce retail').get_all_records())
segmentation = pd.DataFrame(data.worksheet('Segmentation').get_all_records())

Prepare segmentation lookup

segmentation.columns = ['segment', 'score']
segmentation['score'] = segmentation['score'].str.split(',')
segmentation = segmentation.explode('score')
segmentation['score'] = segmentation['score'].str.strip()

Clean order info

# Drop duplicates
order_info = order_info.drop_duplicates()

# Drop cancelled orders (InvoiceNo starts with 'C')
order_info = order_info[~order_info['InvoiceNo'].astype(str).str.startswith('C')]

# Remove bad-debt rows where InvoiceNo is not int
order_info = order_info[order_info['InvoiceNo'].apply(lambda x: isinstance(x, int))]

# Keep only valid rows
order_info = order_info[
    (order_info['Quantity'] > 0) &
    (order_info['CustomerID'] != '') &
    (order_info['UnitPrice'] > 0)
]

# Parse dates
order_info['InvoiceDate'] = pd.to_datetime(order_info['InvoiceDate'],
                                            format='%m/%d/%y %H:%M', errors='coerce')
order_info['Date'] = order_info['InvoiceDate'].dt.date.astype('datetime64[ns]')
order_info = order_info.drop(columns=['InvoiceDate'])

# Rename columns
order_info = order_info.rename(columns={
    'InvoiceNo': 'invoiceid', 'StockCode': 'stockcode',
    'Description': 'description', 'Quantity': 'quantity',
    'UnitPrice': 'price', 'CustomerID': 'customerid',
    'Country': 'country', 'Date': 'date'
})

2 · RFM Segmentation

# Revenue column
order_info['revenue'] = order_info['quantity'] * order_info['price']

# Aggregate per customer
customer_rfm = order_info.groupby('customerid').agg(
    {'date': 'max', 'invoiceid': 'nunique', 'revenue': 'sum'}
).reset_index()

# Recency = days from 31/12/2011 to last purchase
customer_rfm['date'] = (
    pd.to_datetime('31/12/2011', format='%d/%m/%Y') - customer_rfm['date']
).dt.days
customer_rfm.columns = ['customerid', 'recency', 'frequency', 'monetory']

# Score 1-5 with quintiles (R inverted: lower recency = higher score)
customer_rfm['r_rank'] = 6 - (pd.qcut(customer_rfm['recency'].rank(method='first'), 5, labels=False) + 1)
customer_rfm['f_rank'] = pd.qcut(customer_rfm['frequency'].rank(method='first'), 5, labels=False) + 1
customer_rfm['m_rank'] = pd.qcut(customer_rfm['monetory'].rank(method='first'),  5, labels=False) + 1

# 3-digit score string, merge with segment lookup
customer_rfm['score'] = customer_rfm['r_rank'].astype(str) + customer_rfm['f_rank'].astype(str) + customer_rfm['m_rank'].astype(str)
customer_rfm = segmentation.merge(customer_rfm, on='score', how='right')

# Aggregate by segment
rfm = customer_rfm.groupby('segment').agg(
    num_ctm=('customerid', 'count'),
    avg_r=('recency',  'mean'),
    avg_f=('frequency','mean'),
    sum_rvn=('monetory','sum')
).round(1).reset_index()

3 · Visualization

rfm['%_ctm'] = round((rfm['num_ctm'] / rfm['num_ctm'].sum()) * 100, 1)
rfm['%_rvn'] = round((rfm['sum_rvn'] / rfm['sum_rvn'].sum()) * 100, 1)
color = sns.color_palette("RdYlGn_r", len(rfm))

for col, title, ylabel in [
    ('avg_r', 'Average Recency by Segment',   'Day(s)'),
    ('avg_f', 'Average Frequency by Segment', 'Order Time(s)'),
    ('%_rvn', '% Revenue by Segment',         '% Revenue'),
]:
    rfm_s = rfm.sort_values(col, ascending=(col == 'avg_r'))
    plt.figure(figsize=(8, 5))
    sns.barplot(x=rfm_s['segment'], y=rfm_s[col], palette=color)
    plt.title(title); plt.xlabel('Segment'); plt.ylabel(ylabel)
    plt.xticks(rotation=45); plt.tight_layout(); plt.show()

rfm_s = rfm.sort_values('%_ctm', ascending=False)
labels = [f"{s}\n{v:.1f}%" for s, v in zip(rfm_s['segment'], rfm_s['%_ctm'])]
plt.figure(figsize=(16, 6))
squarify.plot(sizes=rfm_s['%_ctm'], label=labels, color=color, alpha=0.8)
plt.title('% of Customers by Segment'); plt.axis('off'); plt.show()
Avg recency by segment Avg frequency by segment % revenue by segment % customers treemap

4 · Insights & Recommendations

By end of 2011, SuperStore had a mixed business situation: strong segments (Champions, Loyal, Potential Loyalist) alongside weak ones (Hibernating, About to Sleep, At Risk, Lost). Champions made up 19.2% of total customers; Hibernating 15.9%.

Clear skewed averages indicate a significant gap between strong and weak segments. Heavy reliance on Champions suggests issues in turning new customers into loyal ones. Prioritize Recency and Frequency over Monetary — loyal shoppers make many purchases at lower amounts throughout the year.

Segment Characteristics

SegmentCharacteristics & RFM Scores
ChampionsHighly engaged and valuable
555, 554, 544, 545, 454, 455, 445
LoyalRegularly purchases; strong retention
543, 444, 435, 355, 354, 345, 344, 335
Potential LoyalistEngaging but not fully committed
553, 551, 552, 541, 542, 533, 532, 531, 452, 451, 442, 441, 431, 453, 433, 432, 423, 353, 352, 351, 342, 341, 333, 323
HibernatingInactive but may return with effort
332, 322, 233, 232, 223, 222, 132, 123, 122, 212, 211
About to SleepDecreasing engagement; needs attention
331, 321, 312, 221, 213, 231, 241, 251
At RiskLikely to disengage; require intervention
255, 254, 245, 244, 253, 252, 243, 242, 235, 234, 225, 224, 153, 152, 145, 143, 142, 135, 134, 133, 125, 124
Lost CustomersNo recent activity; need reactivation
111, 112, 121, 131, 141, 151

Campaign Recommendations

SegmentCampaignDescription
ChampionsExclusive RewardsTiered loyalty, early sale access, gifts.
LoyalPersonalized CommunicationProduct recs based on past purchases & special dates.
Potential LoyalistEngagement IncentivesTargeted promos to encourage repeat purchases.
HibernatingRe-EngagementEmail discounts highlighting new offerings.
About to SleepNudge RemindersReminders and small offers to revive engagement.
At RiskWin-Back OffersLimited-time discounts or exclusive deals.
Lost CustomersRecovery CampaignSignificant discounts or free gifts.

View notebook (nbviewer) Download .ipynb
Power BI · DAX · Power Query

Purchasing Department Performance

Operational dashboards for the AdventureWorks purchasing team — vendor reliability and cost optimization.

Approach: Apply design thinking to identify key metrics, pull data from BigQuery, transform in Power Query, model relationships, and build DAX measures.

Context: AdventureWorks is a fictitious bicycle manufacturer. The purchasing department must supply enough goods on time at optimal import prices.

Design Thinking · Step 1 — Empathize

Who views this?Purchasing Manager, accountant.
Key stakeholderPurchasing Manager.
ProblemEnhance vendor oversight, promote data-driven decisions, optimize vendor relationships and costs.
In one sentenceProvide the Purchasing Manager with overview situation and per-vendor performance details.
When / WhereMonthly meetings or solo manager reviews.
WhyUnderstand vendor performance and evaluate procurement decisions.
HowUnderstand the situation to make informed decisions.

Stakeholder Empathy Map

PainsLarge amount of data to analyze; difficulty identifying what's important.
Thinking & feelingWorry about purchasing department effectiveness; opinions about vendor reliability.
SeeingCompany purchasing overview and per-vendor info for comparison.
Saying & doingOverview dashboard gives the big picture; vendor detail gives in-depth analysis.
GainsSee overview changes and vendor operating details to make the right choice.

Design Thinking · Step 2 — North Star Metric

Value to measureEffectiveness of orders.
Delivery successOrders completed with no errors (received qty = stockable qty).
Metric name% No-error Orders
Why this metricGoal is to provide enough goods for the production department.

Design Thinking · Step 3 — Ideate

Layer 0 — Total MetricsLayer 1 — Breakdown
% no-error orders, % received, % rejected, % extra feeOver time, by ship method, by sub-category

Data Model

Power BI data model

Visual 1 · Vendor Performance Overview

Vendor overview dashboard

Pedals subcategory (2013–2014): ~76% error-free orders, avg extra fee 8.68%, total cost 13.60M across 7 vendors. Reliability dipped to 71% in March 2014; costs spiked to 1.57M in July 2014. VendorID 1586 leads at 78.72%; VendorID 1506 at 77.66% but not among top for low extra fees.

Visual 2 · Vendor Performance Detail

Vendor detail dashboard

Pedals category extra fee is 9.5% vs 8.68% overall average — limiting competitiveness. Prioritize VendorID 1586 for future orders; negotiate freight reduction with VendorID 1506 to improve cost efficiency.

How to Use the Dashboard

ActionDescription
Review SummaryOverview tab: error-free rate, total cost, extra fees, active vendors.
Analyze TrendsSpot reliability dips or cost spikes; vendor rankings highlight who needs attention.
Select VendorVendor tab: per-vendor metrics, trends, and comparisons.
Examine DetailsProduct-level pricing, fees, taxes, and freight costs.
Take ActionGenerate reports and renegotiate contracts using insights.

Download .pbix