Sales Analysis during and outside the marketing campaigns using SQL.
We have been provided with marketing campaigns & sustainable data for a clothing store which is called sustainable clothing, which includes the information of each product, product category,quantity sold daily, prices of each product and daily purchase date.
The data spans from June 2nd 2023 to october 14th 2023; which is approximately a data of 5 months, containing over 63 transactions which comprises of the sales during and outside the marketing campaigns.
The objective is to prepare/insert the data, analyze and answer some intriquing questions using SQL, and subsequently outline the findings that would enhance decision making capabilities and boost profits for the clothing store.
The Top questions to be answered are:
- How many transactions were completed during each marketing campaign?
- Which product had the highest sales quantity?
- What is the total revenue generated from each marketing campaign?
- What is the top-selling product category based on the total revenue generated?
- Which products had a higher quantity sold compared to the average quantity sold?
- What is the average revenue generated per day during the marketing campaigns?
- What is the percentage contribution of each product to the total revenue?
- Compare the average quantity sold during marketing campaigns to outside the marketing campaigns
- Compare the revenue generated by products inside the marketing campaigns to outside the campaigns
- Rank the products by their average daily quantity sold
The dataset used in this project is gotten from this link: SUSTAINABLE_CLOTHING//steeldata.org.uk
We were given 3 tables which came along with values to be inserted and the tables contains the following field:
1.campaign_id - Unique ID for each campaign name
2.campaign_name - Unique name given to each campaign
3.product_id - Unique ID given to each product and a primary key in the sustainable table
4.start_date - The beginning of the campaign
5.end_date - The end of the campaign
1.transaction_id - Unique ID for each transaction
2.product_id - Unique ID given to each product and a primary key in the sustainable table
3.quantity - Number of unit sold daily
4.purchase_date - The date at which customer bought a product(s)
1.product_id - Unique ID for each product name
2.product_name - Unique name given to each product
3.category - Category group assigned to each product based on product type
4.size - The sizes available for each product
5.price - price at which product is sold to customers
We enter the data into a postgreSQL database using the specified information. The information has been divided into several tables, which are connected to one another by sharing common fields. Access to helpful database insights and quick and simple data manipulation are made possible via queries. A degree of data integrity is also ensured by using a database because of the enforced data types in our tables. We may prevent inaccurate values and duplicate data by establishing constraints like primary and foreign keys, which increases our confidence in the accuracy of the data we use.
The database schema was created using postgreSQL GUI
CREATE TABLE sustainable_clothing (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
size VARCHAR(10),
price FLOAT
);
INSERT INTO sustainable_clothing (product_id, product_name, category, size, price)
VALUES
(1, 'Organic Cotton T-Shirt', 'Tops', 'S', 29.99),
(2, 'Recycled Denim Jeans', 'Bottoms', 'M', 79.99),
(3, 'Hemp Crop Top', 'Tops', 'L', 24.99),
(4, 'Bamboo Lounge Pants', 'Bottoms', 'XS', 49.99),
(5, 'Eco-Friendly Hoodie', 'Outerwear', 'XL', 59.99),
(6, 'Linen Button-Down Shirt', 'Tops', 'M', 39.99),
(7, 'Organic Cotton Dress', 'Dresses', 'S', 69.99),
(8, 'Sustainable Swim Shorts', 'Swimwear', 'L', 34.99),
(9, 'Recycled Polyester Jacket', 'Outerwear', 'XL', 89.99),
(10, 'Bamboo Yoga Leggings', 'Activewear', 'XS', 54.99),
(11, 'Hemp Overalls', 'Bottoms', 'M', 74.99),
(12, 'Organic Cotton Sweater', 'Tops', 'L', 49.99),
(13, 'Cork Sandals', 'Footwear', 'S', 39.99),
(14, 'Recycled Nylon Backpack', 'Accessories', 'One Size', 59.99),
(15, 'Organic Cotton Skirt', 'Bottoms', 'XS', 34.99),
(16, 'Hemp Baseball Cap', 'Accessories', 'One Size', 24.99),
(17, 'Upcycled Denim Jacket', 'Outerwear', 'M', 79.99),
(18, 'Linen Jumpsuit', 'Dresses', 'L', 69.99),
(19, 'Organic Cotton Socks', 'Accessories', 'M', 9.99),
(20, 'Bamboo Bathrobe', 'Loungewear', 'XL', 69.99);
CREATE TABLE marketing_campaigns (
campaign_id INT PRIMARY KEY,
campaign_name VARCHAR(100),
product_id INT,
start_date DATE,
end_date DATE,
FOREIGN KEY (product_id) REFERENCES sustainable_clothing (product_id)
);
INSERT INTO marketing_campaigns (campaign_id, campaign_name, product_id, start_date, end_date)
VALUES
(1, 'Summer Sale', 2, '2023-06-01', '2023-06-30'),
(2, 'New Collection Launch', 10, '2023-07-15', '2023-08-15'),
(3, 'Super Save', 7, '2023-08-20', '2023-09-15');
CREATE TABLE transactions (
transaction_id INT PRIMARY KEY,
product_id INT,
quantity INT,
purchase_date DATE,
FOREIGN KEY (product_id) REFERENCES sustainable_clothing (product_id)
);
INSERT INTO transactions (transaction_id, product_id, quantity, purchase_date)
VALUES
(1, 2, 2, '2023-06-02'),
(2, 14, 1, '2023-06-02'),
(3, 5, 2, '2023-06-05'),
(4, 2, 1, '2023-06-07'),
(5, 19, 2, '2023-06-10'),
(6, 2, 1, '2023-06-13'),
(7, 16, 1, '2023-06-13'),
(8, 10, 2, '2023-06-15'),
(9, 2, 1, '2023-06-18'),
(10, 4, 1, '2023-06-22'),
(11, 18, 2, '2023-06-26'),
(12, 2, 1, '2023-06-30'),
(13, 13, 1, '2023-06-30'),
(14, 4, 1, '2023-07-04'),
(15, 6, 2, '2023-07-08'),
(16, 15, 1, '2023-07-08'),
(17, 9, 2, '2023-07-12'),
(18, 20, 1, '2023-07-12'),
(19, 11, 1, '2023-07-16'),
(20, 10, 1, '2023-07-20'),
(21, 12, 2, '2023-07-24'),
(22, 5, 1, '2023-07-29'),
(23, 10, 1, '2023-07-29'),
(24, 10, 1, '2023-08-03'),
(25, 19, 2, '2023-08-08'),
(26, 3, 1, '2023-08-14'),
(27, 10, 1, '2023-08-14'),
(28, 16, 2, '2023-08-20'),
(29, 18, 1, '2023-08-27'),
(30, 12, 2, '2023-09-01'),
(31, 13, 1, '2023-09-05'),
(32, 7, 1, '2023-09-05'),
(33, 6, 1, '2023-09-10'),
(34, 15, 2, '2023-09-14'),
(35, 9, 1, '2023-09-14'),
(36, 11, 2, '2023-09-19'),
(37, 17, 1, '2023-09-23'),
(38, 2, 1, '2023-09-28'),
(39, 14, 1, '2023-09-28'),
(40, 5, 2, '2023-09-30'),
(41, 16, 1, '2023-10-01'),
(42, 12, 2, '2023-10-01'),
(43, 1, 1, '2023-10-01'),
(44, 7, 1, '2023-10-02'),
(45, 18, 2, '2023-10-03'),
(46, 12, 1, '2023-10-03'),
(47, 13, 1, '2023-10-04'),
(48, 4, 1, '2023-10-05'),
(49, 12, 2, '2023-10-05'),
(50, 7, 1, '2023-10-06'),
(51, 4, 2, '2023-10-08'),
(52, 8, 2, '2023-10-08'),
(53, 16, 1, '2023-10-09'),
(54, 19, 1, '2023-10-09'),
(55, 1, 1, '2023-10-10'),
(56, 18, 2, '2023-10-10'),
(57, 2, 1, '2023-10-10'),
(58, 15, 2, '2023-10-11'),
(59, 17, 2, '2023-10-13'),
(60, 13, 1, '2023-10-13'),
(61, 10, 2, '2023-10-13'),
(62, 9, 1, '2023-10-13'),
(63, 19, 2, '2023-10-13'),
(64, 20, 1, '2023-10-14');
Exploratory Data Analysis techniques in postgreSQL will be used to analyze the prepared dataset. We will utilize various SQL elements such as Aggregate Functions, Joins etc
- How many transactions were completed during each marketing campaign?
-
Which product had the highest sales quantity?
-
What is the total revenue generated from each marketing campaign?
- What is the top-selling product category based on the total revenue generated?
- Which products had a higher quantity sold compared to the average quantity sold?
- What is the average revenue generated per day during the marketing campaigns?
- What is the percentage contribution of each product to the total revenue?
- Compare the average quantity sold during marketing campaigns to outside the marketing campaigns?
- Compare the revenue generated by products inside the marketing campaigns to outside the campaigns?
- Rank the products by their average daily quantity sold?
The campaign, named "Summer Sale," has garnered the highest number of transactions among the analyzed marketing campaigns.
with a total of 7 transactions.
The "Summer Sale" campaign appears to have resonated well with customers, resulting in a significant number of transactions.
It suggests that seasonal promotions like summer sales can be effective in driving customer engagement and sales.
The "New Collection Launch" campaign follows closely behind the Summer Sale campaign, with a total of 6 transactions.
Despite not having the highest transaction count, the "New Collection Launch" campaign still performed well, indicating customer interest in new product offerings.
Launching new collections can attract customers and encourage them to make purchases, contributing positively to overall sales.
The "Organic Cotton Sweater" has the highest sales quantity among the analyzed sustainable clothing products.
The high sales quantity of the Organic Cotton Sweater suggests that it is a popular item among customers.
This could be due to factors such as the material (organic cotton) being favored by environmentally conscious consumers, or the design and style of the sweater resonating
well with the target audience.
Both "Bamboo Yoga Leggings" and "Recycled Denim Jeans" have the same sales quantity, following closely behind the Organic Cotton Sweater.
These products also have a relatively high sales quantity, indicating their popularity among customers.
The use of sustainable materials like bamboo and recycled denim may contribute to the appeal of these products to environmentally conscious consumers.
Additionally, factors such as comfort, fit, and style may also play a role in driving sales for these items.
The "Bottom" category has the highest revenue among the analyzed sustainable clothing categories.
The significant revenue from the "Bottom" category indicates that products such as pants, skirts, or shorts are performing well in terms of sales.
Bottoms are essential wardrobe items for most consumers, and sustainable options in this category seem to be particularly attractive to customers.
The "Outwear" category follows behind the Bottom category, with slightly lower revenue.
While the revenue from the "Outwear" category is lower compared to "Bottoms," it still signifies a considerable amount of sales.
Outwear items such as jackets, coats, and sweaters may be popular choices among customers seeking sustainable options for layering and outerwear.
The "Summer Sale" campaign has the highest average sales per day among the analyzed campaigns.
The high average daily revenue for the "Summer Sale" campaign suggests that it has been very effective in driving sales on a daily basis.
This indicates strong customer engagement and conversion rates during the campaign period, resulting in significant revenue generation.
The "New Collection Launch" campaign has a moderate average sales per day.
Although the average daily revenue for the "New Collection Launch" campaign is lower compared to the "Summer Sale," it still indicates a positive response from customers.
This suggests that the launch of new collections has contributed to consistent daily sales during the campaign period.
Analyzing the average sales per day provides insights into the daily revenue generation of each campaign, allowing businesses to understand the effectiveness of their
marketing efforts on a day-to-day basis.
The "Summer Sale" campaign stands out as the most successful in terms of daily revenue generation, indicating its strong impact on driving sales during the campaign period.
The "New Collection Launch" campaign also performed well in maintaining consistent daily sales, reflecting customer interest in new product offerings.
The "Super Save" campaign, while still generating revenue, may require further analysis to understand why its daily sales are comparatively lower.