Digital marketing is all about making data-driven decisions.
In order to do that, you have to understand and analyze the data you have. That’s where SQL and data analytics skills come in. By using these skills, you can make smart decisions that help grow your business.
Unfortunately, a lot of digital marketers never get started because they don’t see the full value of these critical skills:
- Mistakes can be Costly: Without SQL and data analytics skills, you will miss important digital marketing trends in your data. Pre-canned reports from your vendors won’t always do the trick. This can lead to mistakes that cost your business money and make customers unhappy.
- Get Ahead of the Competition: You can gain a competitive advantage by learning how to use SQL and data analytics for digital marketing efforts. You can make better decisions. And you can make decisions faster and more accurately than your competitors, which leads to success for your clients or your boss.
- Data Overload: Without SQL skills, marketers risk being overwhelmed by data, missing key insights, and making flawed decisions that negatively impact their campaigns.
- Frustration and Confusion: Without the ability to work with data, it’s easy to become frustrated and confused by the sheer amount of data you have to work with.
Learning SQL and data analytics can seem like an impossible task. As a digital marketer, you might feel like they don’t have the skills or knowledge to tackle it. You might not have the time or resources to devote to learning SQL and data analytics. You might feel like it’s not worth the investment.
Learning data analytics doesn’t have to be scary or overwhelming for digital marketers.
The truth is that anyone can learn SQL and data analytics. By following along with this case study, digital marketers will learn how to use SQL to track inventory levels for an online store. You’ll learn how to use data to make smart decisions that help grow your business and impress your clients or your boss.
Three parts to this case study:
- Problem statement
- Database Table Setup
- Questions & Answers
Let’s dive in and see what we can learn!
Problem Statement
The survival of a large online retailer is at stake due to inaccurate inventory tracking causing lost sales and unhappy customers.
As a digital marketer, your task is to use SQL to track and analyze the inventory levels of the retailer’s eCommerce store to save the day!
Database Table Setup
We will be working with two tables in this analysis:
Follow along with the interactive SQL for this case study on DB Fiddle
Table 1: products
This table contains information about the products sold by the eCommerce store. The fields in this table include:
product_id: unique identifier for each productproduct_name: name of the productproduct_category: category of the productproduct_price: price of the product
Here is the SQL code to create the products table and insert some sample data:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(50),
product_category VARCHAR(20),
product_price NUMERIC(10,2)
);
INSERT INTO products (product_name, product_category, product_price)
VALUES ('Product A', 'Category 1', 19.99),
('Product B', 'Category 2', 29.99),
('Product C', 'Category 1', 39.99),
('Product D', 'Category 3', 49.99),
('Product E', 'Category 2', 59.99);
Table 2: inventory
This table contains information about the inventory levels of the products sold by the eCommerce store. The fields in this table include:
product_id: unique identifier for each productinventory_date: date of the inventory countinventory_level: number of units in inventory on the inventory date
Here is the SQL code to create the inventory table and insert some sample data:
CREATE TABLE inventory (
product_id INT,
inventory_date DATE,
inventory_level INT
);
INSERT INTO inventory (product_id, inventory_date, inventory_level)
VALUES (1, '2022-01-01', 100),
(2, '2022-01-01', 200),
(3, '2022-01-01', 150),
(4, '2022-01-01', 75),
(5, '2022-01-01', 250),
(1, '2022-01-02', 80),
(2, '2022-01-02', 180),
(3, '2022-01-02', 100),
(4, '2022-01-02', 60),
(5, '2022-01-02', 220),
(1, '2022-01-03', 50),
(2, '2022-01-03', 150),
(3, '2022-01-03', 75),
(4, '2022-01-03', 80),
(5, '2022-01-03', 200);
That’s it for setup! Now we can work through some questions to analyze the results.
Case Study Questions & Answers
Question 1. What are the top 5 products with the highest inventory levels on the most recent inventory date?
SELECT p.product_name, i.inventory_level FROM products p JOIN inventory i ON p.product_id = i.product_id WHERE i.inventory_date = (SELECT MAX(inventory_date) FROM inventory) ORDER BY i.inventory_level DESC LIMIT 5;
Answer
| product_name | inventory_level |
|---|---|
| Product E | 200 |
| Product B | 150 |
| Product D | 80 |
| Product C | 75 |
| Product A | 50 |
Explanation
This SQL statement joins the products and inventory tables on the product_id field and selects the product_name and inventory_level fields.
It then filters the results to only include data from the most recent inventory date and sorts the results in descending order by inventory_level.
Finally, the LIMIT 5 clause is used to limit the output to the top 5 products with the highest inventory levels on the most recent inventory date.
Question 2. What is the total inventory level for each product category on the most recent inventory date?
SELECT p.product_category, SUM(i.inventory_level) AS total_inventory_level FROM products p JOIN inventory i ON p.product_id = i.product_id WHERE i.inventory_date = (SELECT MAX(inventory_date) FROM inventory) GROUP BY p.product_category;
Answer
| product_category | total_inventory_level |
|---|---|
| Category 1 | 125 |
| Category 2 | 350 |
| Category 3 | 80 |
Explanation
This SQL statement joins the products and inventory tables on the product_id field and selects the product_category and the sum of the inventory_level fields for each category.
It then filters the results to only include data from the most recent inventory date and groups the results by product_category.
Question 3. What is the average inventory level for each product category for the month of January 2022?
SELECT p.product_category, AVG(i.inventory_level) AS avg_inventory_level FROM products p JOIN inventory i ON p.product_id = i.product_id WHERE i.inventory_date >= '2022-01-01' AND i.inventory_date < '2022-02-01' GROUP BY p.product_category;
Answer
| product_category | avg_inventory_level |
|---|---|
| Category 1 | 92.50 |
| Category 2 | 200.0 |
| Category 3 | 71.67 |
Explanation
This SQL statement joins the products and inventory tables on the product_id field and selects the product_category and the average of the inventory_level fields for each category.
It then filters the results to only include data from the month of January 2022 and groups the results by product_category.
Question 4. Which products had a decrease in inventory level from the previous inventory date to the current inventory date?
SELECT i1.product_id, p.product_name, i1.inventory_level - i2.inventory_level AS inventory_diff
FROM inventory i1
JOIN inventory i2 ON i1.product_id = i2.product_id
AND i1.inventory_date = i2.inventory_date + INTERVAL '1 day'
JOIN products p ON i1.product_id = p.product_id
WHERE i1.inventory_level < i2.inventory_level;
Answer
| product_id | product_name | inventory_diff |
|---|---|---|
| 1 | Product A | -20 |
| 1 | Product A | -30 |
| 2 | Product B | -20 |
| 2 | Product B | -30 |
| 3 | Product C | -50 |
| 3 | Product C | -25 |
| 4 | Product D | -15 |
| 5 | Product E | -30 |
| 5 | Product E | -20 |
Explanation
This SQL statement self-joins the inventory table twice on the product_id field and the previous inventory date. It selects the product_id, product_name, and the difference between the inventory_level on the current inventory date and the previous inventory date.
It then joins the products table on the product_id field and filters the results to only include products where the inventory level has decreased from the previous inventory date to the current inventory date.
Question 5. What is the overall trend in inventory levels for each product category over the month of January 2022?
SELECT p.product_category, i.inventory_date, AVG(i.inventory_level) AS avg_inventory_level FROM products p JOIN inventory i ON p.product_id = i.product_id WHERE i.inventory_date >= '2022-01-01' AND i.inventory_date < '2022-02-01' GROUP BY p.product_category, i.inventory_date ORDER BY p.product_category, i.inventory_date;
Answer
| product_category | inventory_date | avg_inventory_level |
|---|---|---|
| Category 1 | 2022-01-01 | 125.0 |
| Category 1 | 2022-01-02 | 90.0 |
| Category 1 | 2022-01-03 | 62.5 |
| Category 2 | 2022-01-01 | 225.0 |
| Category 2 | 2022-01-02 | 200.0 |
| Category 2 | 2022-01-03 | 175.0 |
| Category 3 | 2022-01-01 | 75.0 |
| Category 3 | 2022-01-02 | 60.0 |
| Category 3 | 2022-01-03 | 80.0 |
Explanation
This SQL statement joins the products and inventory tables on the product_id field and selects the product_category, inventory_date, and the average of the inventory_level fields for each category and date. It then filters the results to only include data from the month of January 2022 and groups the results by product_category and inventory_date. Finally, it sorts the results by product_category and inventory_date. This query will show the overall trend in inventory levels for each product category over the month of January 2022.
That’s it!
In this case study, we’ve explored how SQL can help a big online store track and manage its inventory.
We learned how to create and use SQL tables, filter data using the WHERE clause, and group data using the GROUP BY clause. These skills can be super helpful for digital marketers, but not learning SQL could put you at a disadvantage.
Without knowing SQL, you might miss out on opportunities to grow your career and help your business succeed.
