The Look E-Commerce Products Sales Analysis
SQL Assignment Project
INTRODUCTION
This is my first notable case study as a Data Analyst, and it serves as an assignment project for the Refocus Data Analytics Course. I will analyze a fictional company named The Look E-Commerce: a company focuses on selling fashion to various countries through an app, where the marketing division is currently planning to enhance sales through advertisements. In order to determine the most effective advertising approach, various analyses need to be conducted and supporting data must be prepared to enable the Marketing Division to make informed decisions. As assigned by the manager, I have been tasked with assisting the marketing team in preparing the necessary data and conducting analyses that will aid the division in determining the appropriate advertisements.
I have divided the analysis of this case study into 5 sections to facilitate understanding and traceability: understanding the objectives, data preparation, processing, analysis, and providing insights and recommendations.
OBJECTIVES
My task in conducting the analysis is to assist the marketing team in understanding sales performance, determining target advertisements, and enhancing the sales of specific products through effective strategies.
BUSINESS METRICS : Top 3 products with the highest sales revenue to be advertised and The appropriate country to target the sales advertisements for the top 3 products with the highest sales.
DATA PREPARATION
I am utilizing the dataset from The Look E-Commerce spanning from 2019 to 2021 to analyze and explore the sales history of its products. Dataset are available in this link.
The data has been preprocessed and is ready for exploration. I am utilizing pgAdmin4, an excellent open-source database management tool for PostgreSQL.
To import an SQL dataset file in pgAdmin4, first, you need to create a new database and then proceed to import the dataset files. This SQL dataset consists of 4 tables: order_items, orders, products, and users. Here are the steps that need to be followed:
After creating the database, right-click on the previously created database name (the_look_ecommerce), and then select the “Restore” option to import the dataset files.
A prompt box should appear. In the “Filename” field, please click on the folder icon to browse and locate the directory where you have saved the dataset. Once you have selected the file, click the “Restore” button to initiate the import process.
The tables within the database will appear under the “Tables” section located below the “Schemas” section. If the tables do not appear immediately, you can click the “Refresh” button to update the view and display the tables.
The four tables are as follows :
users : Contains data about the users of The Look E-Commerce application, who can also be referred to as customers.
select * from users; -- u
orders: Stores data about individual orders recorded in the system. Each order may contain multiple items.
select * from orders; -- o
order_items: Provides detailed information about each item within an order.
select * from order_items; -- oi
products: Contains detailed data about each individual product.
select * from products; -- p
Each table has different column/field names with distinct content that corresponds to its respective table’s purpose. To analyze them, a combination of the four tables needs to be performed. Therefore, I will provide abbreviations for each table to facilitate query execution. These abbreviations can be used to reference the tables and their respective columns in queries for analysis purposes.
users: u
orders: o
order_Items: oi
products: p
ANALYZE
Now, let’s dive into data analysis. For the first business metric, I will present the products with the highest total sales revenue. Specifically, the marketing team wants to focus on the top 3 products.
select distinct oi.product_id,
p.name,
oi.sale_price,
sum (o.num_of_item) total_amount_purchased,
(oi.sale_price * sum (o.num_of_item)) total_sales_revenue
from order_items oi
inner join orders o
on oi.order_id = o.order_id
inner join products p
on oi.product_id = p.id
where oi.status = 'Complete'
group by 1,2,3
order by total_sales_revenue desc
limit 3;I have created the query, retrieving the columns product_id and sale_price from the order_items table, name column from the products table, the total_amount_purchased column obtained by summing the num_of_item for each product from the orders table, and the total_sales_revenue column obtained by multiplying the sale_price by the num_of_item. Since these columns are from different tables, I use the INNER JOIN function to combine the three tables, using the order_id and product_id as the key fields. The desired results are based on transactions with a status of “Complete”. Then, I group them by product_id, name, and sale_price, sort them in descending order, and display only the top 3 highest records.
As you can see, the product “Alpha Industries Rip Stop Short” has the highest total sales revenue, amounting to $10,989, with 11 units sold at a unit price of $999. It is followed by the product “Canada Goose Women’s Expedition Parka” with a total sales revenue of $8,745, selling 11 units at a unit price of $795. Lastly, the product “Jordan Low Quarter Sock Style #427411” has a total sales revenue of $8,127, with 9 units sold at a unit price of $903.
Next, for the second business metric, I will display the countries that will be targeted by the marketing team to enhance sales through advertising strategies. This will be based on the previous analysis that highlighted the top 3 products contributing the highest total sales revenue.
select distinct oi.product_id,
p.name,
oi.sale_price,
sum (o.num_of_item) total_amount_purchased,
(oi.sale_price * sum (o.num_of_item)) total_sales_revenue,
u.country
from order_items oi
inner join orders o
on oi.order_id = o.order_id
inner join products p
on oi.product_id = p.id
inner join users u
on p.id = u.id
where oi.status = 'Complete'
group by 1,2,3,6
order by total_sales_revenue desc
limit 3;Essentially, the query I used for the second business metric is nearly identical to the query used for the first metric. However, in this query for the second metric, I include the country column from the users table to display the countries where the products are sold the most. Therefore, I need to add a third INNER JOIN between the products table and the users table, using id column as the key field, to be able to display the country column. Additionally, for grouping, I also include the country column. These are the results that show the top 3 products with the highest total sales revenue, along with the corresponding countries where they are sold the most.
Based on the total sales revenue from the first business metric, the country with the highest total sales revenue is China, followed by Brazil, and then France.
INSIGHTS
As seen and analyzed above, the products contributing the highest total sales revenue are from China, with the product “Alpha Industries Rip Stop Short” generating $10,989 in revenue from the sale of 11 items at a price of $999 per item. The second highest revenue comes from Brazil, with the product “Canada Goose Women’s Expedition Parka” generating $8,745 from the sale of 11 items at a price of $795 per item. Lastly, France contributes $8,127 in total sales revenue with the product “Jordan Low Quarter Sock Style #427411” priced at $903 per item and selling 9 items.
Although the quantity of items sold for each product may not be significant, the high unit price contributes to the overall high total sales revenue. In further data exploration, there are still many other products available with lower prices compared to the top three products mentioned above, but they have higher quantities sold.
RECOMMENDATIONS
- Targeted Advertising: Focus on promoting the top-selling products, such as “Alpha Industries Rip Stop Short,” “Canada Goose Women’s Expedition Parka,” and “Jordan Low Quarter Sock Style #427411.” Allocate a significant portion of the advertising budget to effectively market these products, as they have demonstrated high total sales revenue.
- Geographical Targeting: Capitalize on the sales success in China, Brazil, and France by tailoring marketing efforts specifically to these countries. Consider cultural nuances, consumer preferences, and local advertising channels to maximize the impact of promotional campaigns in these regions.
- Pricing and Product Mix: Analyze the performance of other products with higher quantities sold and lower price points. Explore strategies to increase sales volume for these products by offering promotions, bundling options, or expanding their target market. This can help diversify revenue streams and capture a larger customer base.
Visit my website : https://rionovriandra.github.io/
BI Portfolio : https://www.novypro.com/profile_projects/rionov
Check out my other blogs : https://medium.com/@riogeorge.n
LinkedIn : https://www.linkedin.com/in/rionovriandra/
