A supermarket chain wants to determine whether customers purchasing lactose-free dairy products exhibit different purchasing behaviors compared to customers buying regular dairy products to assess whether the lactose-free category requires a differentiated commercial strategy in terms of marketing, product placement, inventory management and cross-selling opportunities.
The dataset was downloaded from the Kaggle website. Raw data was imported into MySQL software for the preliminary cleaning step. It was a dataset composed of multiple tables such as orders, departments, aisles and products. SQL queries were required to shape data, as all products and transactions were included in the tables and represented about 3 million rows for the transactions and 33,000 rows for products.
As it’s the first project, I created many tables to practice. SQL queries used during this project are shown below:
# Creating the database lactose-free products:
CREATE DATABASE lactose_free_project;
# Using lactose_free_project dataset:
SET GLOBAL local_infile = 1;
USE lactose_free_project;
# Creating the table 'products':
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255),
aisle_id INT,
department_id INT
);
# EDA (Exploratory Data Analysis):
# Controlling null rows:
SELECT COUNT(*) FROM products WHERE product_name IS NULL OR product_name = '';
# Controlling unique rows:
SELECT product_name, COUNT(product_name)
FROM products
GROUP BY product_name
HAVING COUNT(product_name) > 1;
SELECT COUNT(product_name) AS total, COUNT(DISTINCT product_name) AS unique
FROM products;
# Deleting duplicates rows:
SET SQL_SAFE_UPDATES = 0;
DELETE p1 FROM products p1
INNER JOIN products p2
ON p1.product_name = p2.product_name
WHERE p1.product_id > p2.product_id;
# Sorting lactose-free products:
SELECT product_name
FROM products
WHERE product_name LIKE '%lactose%free%'
OR product_name LIKE '%milk%free%'
OR product_name LIKE '%milkless%'
OR product_name LIKE '%lactose%reduced%'
OR product_name LIKE '%dairy%free%';
# Creating the table lactose_free_products:
CREATE TABLE lactose_free_products AS
SELECT *
FROM products
WHERE product_name LIKE '%lactose%free%'
OR product_name LIKE '%milk%free%'
OR product_name LIKE '%milkless%'
OR product_name LIKE '%lactose%reduced%'
OR product_name LIKE '%dairy%free%'
OR product_name LIKE '%plant%based%'
OR product_name LIKE '%vegan%';
# Creating the table aisles:
CREATE TABLE aisles (
aisle_id INT PRIMARY KEY,
aisle VARCHAR(50)
);
# Creating the table departments:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department VARCHAR(50)
);
# Creating the table order_products_prior:
CREATE TABLE order_products_prior (
order_id INT,
product_id INT,
add_to_cart_order INT,
reordered INT
);
# Creating the table 'order_lactose_free_products_prior':
CREATE TABLE order_lactose_free_products_prior AS
SELECT
order_products_prior.order_id,
order_products_prior.product_id,
order_products_prior.add_to_cart_order,
order_products_prior.reordered,
lactose_free_products.product_name,
aisles.aisle_id,
aisles.aisle,
departments.department_id,
departments.department
FROM order_products_prior
INNER JOIN lactose_free_products
ON order_products_prior.product_id = lactose_free_products.product_id
INNER JOIN aisles
ON aisles.aisle_id = lactose_free_products.aisle_id
INNER JOIN departments
ON departments.department_id = lactose_free_products.department_id;
# Creating the table order_lactose_free_products_train:
CREATE TABLE order_lactose_free_products_train AS
SELECT
order_products_train.order_id,
order_products_train.product_id,
order_products_train.add_to_cart_order,
order_products_train.reordered,
lactose_free_products.product_name,
aisles.aisle_id,
aisles.aisle,
departments.department_id,
departments.department
FROM order_products_train
INNER JOIN lactose_free_products
ON order_products_train.product_id = lactose_free_products.product_id
INNER JOIN aisles
ON aisles.aisle_id = lactose_free_products.aisle_id
INNER JOIN departments
ON departments.department_id = lactose_free_products.department_id;
# Creating the table lactose_free_orders:
CREATE TABLE lactose_free_orders AS
SELECT
orders.*,
order_lactose_free_products_prior.product_id,
order_lactose_free_products_prior.add_to_cart_order,
order_lactose_free_products_prior.reordered,
order_lactose_free_products_prior.product_name,
order_lactose_free_products_prior.aisle_id,
order_lactose_free_products_prior.aisle,
order_lactose_free_products_prior.department_id,
order_lactose_free_products_prior.department
FROM orders
JOIN order_lactose_free_products_prior
ON order_lactose_free_products_prior.order_id = orders.order_id
UNION ALL
SELECT
orders.*,
order_lactose_free_products_train.product_id,
order_lactose_free_products_train.add_to_cart_order,
order_lactose_free_products_train.reordered,
order_lactose_free_products_train.product_name,
order_lactose_free_products_train.aisle_id,
order_lactose_free_products_train.aisle,
order_lactose_free_products_train.department_id,
order_lactose_free_products_train.department
FROM orders
JOIN order_lactose_free_products_train
ON order_lactose_free_products_train.order_id = orders.order_id;
# Creating the table total_free_lactose_items:
CREATE TABLE total_free_lactose_items AS
SELECT COUNT(DISTINCT product_name) AS item_count
FROM lactose_free_orders;
# Creating the table milk_purchase_periodicity:
CREATE TABLE milk_purchase_periodicity AS
SELECT product_name,
AVG(days_since_prior_order) AS milk_purchase_periodicity
FROM products
JOIN order_products_prior
ON order_products_prior.product_id = products.product_id
JOIN orders
ON orders.order_id = order_products_prior.order_id
WHERE product_name LIKE 'milk'
GROUP BY product_name;
# Creating the table lactose_free_milk_purchase_periodicity:
CREATE TABLE free_lactose_milk_purchase_periodicity AS
SELECT AVG(days_since_prior_order) AS free_lactose_milk_purchase_periodicity
FROM lactose_free_orders
WHERE product_name LIKE '%milk%';
RENAME TABLE free_lactose_milk_purchase_periodicity
TO lactose_free_milk_purchase_periodicity;
# Creating the table AVG_add_to_cart_order:
CREATE TABLE AVG_add_to_cart_order AS
SELECT AVG(add_to_cart_order) AS avg_add_to_cart_order
FROM lactose_free_orders;
# Creating the table Departments_free_lactose_products:
CREATE TABLE Departments_free_lactose_products AS
SELECT department,
COUNT(department) AS number_of_purchases
FROM lactose_free_orders
GROUP BY department
ORDER BY number_of_purchases DESC;
# Creating the table aisle_free_lactose_products:
CREATE TABLE aisle_free_lactose_products AS
SELECT aisle,
COUNT(aisle) AS number_of_purchases
FROM lactose_free_orders
GROUP BY aisle
ORDER BY number_of_purchases DESC;
# Creating the table reordering_ratio:
CREATE TABLE reordering_ratio AS
SELECT AVG(reordered) AS reordering_ratio
FROM lactose_free_orders;
# Creating the table sales:
CREATE TABLE sales AS
SELECT product_name,
COUNT(product_name) AS sales
FROM lactose_free_orders
GROUP BY product_name
ORDER BY sales DESC;
# Creating the table free_lactose_items:
CREATE TABLE free_lactose_items AS
SELECT COUNT(DISTINCT product_id) AS item_count
FROM lactose_free_orders;
# Creating the table basket_analysis:
CREATE TABLE basket_analysis AS
SELECT
user_id,
order_number,
product_id,
product_name
FROM all_orders
WHERE user_id IN (
SELECT DISTINCT user_id
FROM all_orders
WHERE product_name LIKE '%lactose%free%'
);
# Creating the table regular_and_free_lactose:
CREATE TABLE regular_and_free_lactose AS
SELECT
CASE
WHEN product_name LIKE '%lactose%' AND product_name LIKE '%milk%' THEN 'Free lactose milk'
WHEN product_name LIKE '%milk%' THEN 'Regular milk'
WHEN product_name LIKE '%lactose%' AND product_name LIKE '%yogurt%' THEN 'Free lactose yogurt'
WHEN product_name LIKE '%yogurt%' THEN 'Regular yogurt'
WHEN product_name LIKE '%lactose%' AND product_name LIKE '%cheese%' THEN 'Free lactose cheese'
WHEN product_name LIKE '%cheese%' THEN 'Regular cheese'
ELSE 'Others'
END AS product_category,
AVG(add_to_cart_order) AS avg_add_to_cart_order,
AVG(reordered) AS avg_reordered
FROM total_orders
JOIN products
ON products.product_id = total_orders.product_id
GROUP BY product_category;
After conducting data transformation through SQL and developing a relational table model, I created a dashboard updating visualizations that contain key data to find insights, which could lead to recommendations. As you can observe below, I created a relational dashboard that shows metrics like the reordering ratio, average add-to-cart order, and average purchase periodicity. The reordering ratio is data related to customer loyalty, while the average add-to-cart orders and the average purchase periodicity are essential to know about purchase behavior. All these metrics, comparing cheese, milk, and yogurt lactose-free items to the regular ones, provide an interesting view of shopping patterns between regular and lactose-free customers. Sales show the market share between lactose-free and regular items. I used core items like milk, cheese, and yogurt to study the gap between both product ranges. On the right side of the dashboard, we can visualize lactose-free products, departments, aisles, and co-purchased items. Examining the data associated with departments, aisles, and co-purchased items can provide cross-selling opportunities.
| Observation | Findings | Insights | Recommendations | | --- | --- | --- | --- | | The reordering ratio between lactose-free and regular items is quite similar. | Customers on both sides usually repurchase the product. | Customer loyalty is equivalent and positive on both sides | Inventory management could be similar, as customer loyalty is equivalent. | | The average add-to-cart order between lactose-free items and regular ones is equivalent. | Lactose-free and regular items are added to the cart almost simultaneously. | The buying behavior is equivalent. | No important distinction in implementing strategies according to the client behavior. | | The average purchase periodicity between all lactose-free items and regular ones is similar | Both customer segments come back to the supermarket after the same period. | There is a highly correlated buying behavior between lactose-free and regular clients. | This supermarket could standardize strategies across both market niches. | | Sales from lactose-free items are concentrated in many departments. | Lactose-free items are distributed in different parts of the supermarket. | This strategy of placing lactose-free items in different aisles with regular items is working. | This mart shouldn’t concentrate all dairy-free items in the aisle ‘Soy lactosefree’. | | Co-purchased items are organic and conventional fruits. | Customers buying dairy-free items are health-conscious customers. | Lactose-free customers correspond to health-focused buyers. | This mart could include a variety of lactose-free items like milkshakes, yogurts with fruit, and organic products. On the other hand, the company could include lactose-free items in aisles with healthy items. | | Sales between lactose-free and regular products are not at the same level. | Sales are higher for regular items. | Market share of regular products is much higher than for dairy-free products. | The budget to implement commercial strategies shouldn’t be equivalent. |
In conclusion, to answer the question “A supermarket chain wants to determine whether customers purchasing lactose-free dairy products exhibit different purchasing behaviors compared to customers buying regular dairy products to assess whether the lactose-free category requires a differentiated commercial strategy in terms of marketing, product placement, inventory management, and cross-selling opportunities”. There is no significant difference between lactose-free and regular customer behavior or loyalty. Some cross-selling opportunities have been identified such as organic lactose-free products or lactose-free products including fruits flavors. It’s interesting to keep placing lactose-free items in different aisles to boost this category. Getting straight to the point, the company could standardize strategies between both categories with different budgets.