Skip to content

Latest commit

 

History

History
300 lines (286 loc) · 8.47 KB

Marketing Analysis.md

File metadata and controls

300 lines (286 loc) · 8.47 KB
Raw

Challenge 6 - Marketing Analysis

Introduction

As the Marketing Analyst for the 'Sustainable Clothing Co.', I am at the forefront of assessing the effectiveness of our recent marketing campaigns.My task is to dive deep into the data from these campaigns, analyzing various metrics and customer responses to determine their success. Through this analysis, I will provide valuable insights that will shape the future marketing strategies of 'Sustainable Clothing Co.', ensuring that our message resonates with our audience and aligns with our brand values.

Table Creation

CREATE TABLE sustainable_clothing (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
size VARCHAR(10),
price FLOAT
);
-- Insert data into the table
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 the table
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 data into the table
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 the table
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 data into the table
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');

Analysis Queries

How many transactions were completed during each marketing campaign?
SELECT
campaign_name,
COUNT(transaction_id) AS number_of_transactions
FROM
marketing_campaigns mc
LEFT JOIN
transactions t
ON mc.product_id = t.product_id
WHERE 
t.purchase_date BETWEEN mc.start_date AND mc.end_date
GROUP BY campaign_name;
Which product had the highest sales quantity?
SELECT 
product_name,
t.product_id,
SUM(quantity) as sales_quantity
FROM 
transactions t
LEFT JOIN
sustainable_clothing sc
ON t.product_id = sc.product_id
GROUP BY product_name, t.product_id
ORDER BY sales_quantity DESC
LIMIT 1;
What is the total revenue generated from each marketing campaign?
SELECT
campaign_name,
ROUND(SUM(t.quantity * sc.price),0) AS total_revenue
FROM
marketing_campaigns mc
LEFT JOIN
transactions t
ON mc.product_id = t.product_id
LEFT JOIN
sustainable_clothing sc
ON t.product_id = sc.product_id
WHERE
t.purchase_date BETWEEN mc.start_date AND mc.end_date
GROUP BY campaign_name;
What is the top-selling product category based on the total revenue generated?
SELECT
Category,
ROUND(SUM(t.quantity * sc.price),0) AS total_revenue
FROM
transactions t
JOIN
sustainable_clothing sc
ON t.product_id = sc.product_id
GROUP BY category
ORDER BY total_revenue DESC
LIMIT 1;
Which products had a higher quantity sold compared to the average quantity sold?
WITH total_sales AS (
    SELECT 
        product_id, 
        SUM(quantity) as total_quantity
    FROM 
        transactions
    GROUP BY 
        product_id
), average_sales AS (
    SELECT 
        AVG(total_quantity) as avg_quantity
    FROM 
        total_sales
)
SELECT 
    ts.product_id, 
    ts.total_quantity
FROM 
    total_sales ts, 
    average_sales
WHERE 
    ts.total_quantity > average_sales.avg_quantity;
What is the average revenue generated per day during the marketing campaigns?
SELECT 
ROUND(SUM(t.quantity * sc.price) / COUNT(DISTINCT t.purchase_date),2) AS avg_daily_revenue
FROM 
transactions t
JOIN 
sustainable_clothing sc ON t.product_id = sc.product_id
JOIN 
marketing_campaigns mc ON t.product_id = mc.product_id
WHERE 
t.purchase_date BETWEEN mc.start_date AND mc.end_date;
What is the percentage contribution of each product to the total revenue?
WITH total_revenue AS (
    SELECT 
        SUM(t.quantity * sc.price) AS revenue
    FROM 
        transactions t
    JOIN 
        sustainable_clothing sc ON t.product_id = sc.product_id
)
SELECT 
    t.product_id, 
    ROUND(SUM(t.quantity * sc.price) / (SELECT revenue FROM total_revenue) * 100,2) AS percentage_contribution
FROM 
    transactions t
JOIN 
    sustainable_clothing sc ON t.product_id = sc.product_id
GROUP BY 
    t.product_id;
Compare the average quantity sold during marketing campaigns to outside the marketing campaigns
SELECT 
sc.product_id,
AVG(CASE WHEN t.purchase_date BETWEEN mc.start_date AND mc.end_date THEN t.quantity ELSE NULL END) AS avg_quantity_during_campaigns,
AVG(CASE WHEN t.purchase_date NOT BETWEEN mc.start_date AND mc.end_date THEN t.quantity ELSE NULL END) AS avg_quantity_outside_campaigns
FROM 
sustainable_clothing sc
LEFT JOIN 
transactions t ON sc.product_id = t.product_id
LEFT JOIN 
marketing_campaigns mc ON sc.product_id = mc.product_id
GROUP BY 
sc.product_id;
Compare the revenue generated by products inside the marketing campaigns to outside the campaigns
SELECT 
sc.product_id,
SUM(CASE WHEN t.purchase_date BETWEEN mc.start_date AND mc.end_date THEN t.quantity * sc.price ELSE 0 END) AS revenue_during_campaigns,
SUM(CASE WHEN t.purchase_date NOT BETWEEN mc.start_date AND mc.end_date THEN t.quantity * sc.price ELSE 0 END) AS revenue_outside_campaigns
FROM 
sustainable_clothing sc
LEFT JOIN 
transactions t ON sc.product_id = t.product_id
LEFT JOIN 
marketing_campaigns mc ON sc.product_id = mc.product_id
GROUP BY 
sc.product_id;
Rank the products by their average daily quantity sold
SELECT 
product_id, 
AVG(quantity) AS avg_daily_quantity_sold,
RANK() OVER (ORDER BY AVG(quantity) DESC) AS ranking
FROM 
transactions
GROUP BY 
product_id;