🛒 E-commerce Analytics & Segmentation

This repository showcases SQL-based data models designed to extract actionable insights from e-commerce and transactional data. The queries below bridge the gap between raw backend database metrics and strategic business objectives, specifically targeting customer retention and revenue growth via cross-selling.

👥 Business Impact & Stakeholder Alignment

At university, I've learned that data is only as valuable as the decisions it enables. Therefore, it is crucial to provide data solutions that are fit-for-purpose. This project directly addresses two core requests from key business stakeholders:

1. Market Basket Analysis (Cross-Selling Strategy)

Stakeholder: Marketing Manager

Objective: “Find items that are frequently bought together on all our Shopify websites.”

Why it matters: By identifying products bought together, the marketing team can optimise website UX layout, design data-driven bundles, and implement targeted email cross-selling strategies to boost Average Order Value (AOV).

2. RFM Segmentation & Churn Tracking (Retention Strategy)

Stakeholder: Managing Director

Objective: “Find frequent customers we've lost.”

Why it matters: Acquiring new customers can be expensive. By calculating Recency, Frequency, and Monetary (RFM) values alongside the Average Purchase Cycle, the business can automatically flag high-value, lapsed buyers and implement personalised win-back campaigns before they permanently churn.

Query 1: Market Basket Analysis

This query performs self-joins on transactional data to identify pairs (and triplets) of products that appear together in the same invoice, excluding non-product line items such as delivery fees.

SELECT
		 YEAR(A."Created Time") AS "Year",
		 A."Item Name" AS "Item A",
		 B."Item Name" AS "Item B",

         -- Use COALESCE to handle invoices containing only two items
		 COALESCE(C."Item Name", '(Only Two Items)') AS "Item C",

         -- Count the number of distinct invoices where items were bought together
		 COUNT(DISTINCT A."Invoice ID") AS "Times Bought Together"

FROM  "Invoice Items" A

-- Self-join to find pairs of items bought in the same invoice
JOIN "Invoice Items" B ON A."Invoice ID"  = B."Invoice ID"
	 AND	A."Item Name"  < B."Item Name" 

-- If exists, join to find a third item bought in the same invoice
LEFT JOIN "Invoice Items" C ON B."Invoice ID"  = C."Invoice ID"
	 AND	B."Item Name"  < C."Item Name"
     -- Exclude certain delivery items from the results
	 AND	C."Item Name"  NOT IN ( 'Delivery Standard'  , 'Standard Delivery'  , 'Priority Despatch'  , 'Delivery To Be Collected'  )  

-- Filtering
WHERE   A."Item Name"  NOT IN ( 'Delivery Standard'  , 'Standard Delivery'  , 'Priority Despatch'  , 'Delivery To Be Collected'  )
    AND	B."Item Name"  NOT IN ( 'Delivery Standard'  , 'Standard Delivery'  , 'Priority Despatch'  , 'Delivery To Be Collected'  )

GROUP BY YEAR(A."Created Time"),
	 A."Item Name",
	 B."Item Name",
	  C."Item Name" 

-- Filter to only include item pairs with more than one distinct invoice
HAVING COUNT(DISTINCT A."Invoice ID")  > 1  
ORDER BY "Year" DESC,
	 "Times Bought Together" DESC 

Query 2: RFM Customer Metrics & Purchase Cycles

This query aggregates customer order history to compute basic RFM values and calculates behavioral metrics such as the Average Purchase Cycle and Days Since Last Order to identify churn risks.

SELECT DISTINCT
		 c."Customer Name" AS "Customer Name",
		 min(so."Order Date") AS "First Purchase Date",
		 -- Recency
         max(so."Order Date") AS "Recency",
         -- Frequency
		 count_distinct(so."Sales order ID") AS "Frequency",
         -- Monetary
		 to_currency(SUM(so."Total (BCY)")) AS "Monetary (LTV)",
         -- Average Purchase Cycle
		 round(timestamp_diff(day, min(so."Order Date"), max(so."Order Date")) / (count_distinct(so."Sales order ID") -1), 0) AS "Average Purchase Cycle",
		 -- Days Since Last Order
         timestamp_diff(day, max(so."Order Date"), today()) AS "Days Since Last Order"
FROM  "Sales Orders" so
JOIN "Customers" c ON c."Customer ID"  = so."Customer ID"
    -- Exclude certain customers from the results
	AND	c."Customer Name"  NOT LIKE '%No Value%'  
GROUP BY  c."Customer Name" 

Key Technical Highlights

• Data Cleaning: Implemented strict exclusion rules within the WHERE and JOIN clauses to filter out noise such as standard/priority delivery SKUs and test accounts (%No Value%).
• Time Series: Handled date/time metrics dynamically using timestamp_diff to map out customer purchasing behavior cycles in real time against today().