E-Commerce Product Performance

As a capstone project at the end of my Boolean Data Analyst Boot Camp, I was tasked with a project where I acted as a data analyst within an E-commerce company who had been asked to look at the product performance within the company. All of the data for this task was stored within Google Big Queries public DataSet ''thelook_eccomerce".

My first step was to understand the contents of each table and the relationship between the tables, I therefore previewed the contents of each and began to sketch out initial primary/foreign keys.

Once I had an understanding of how the tables related to each other, I started to play around with some SQL queries to see if I could extract the information I wanted. This took some time and lots of research but was a great learning curve!

Finally, I ended up with a query that looked like:

SELECT

 p.name,

 SUM(oi.sale_price)as Revenue,

 p.category,

 OI.created_at,

 U.gender,

 U.age

FROM `bigquery-public-data.thelook_ecommerce.order_items` OI

LEFT JOIN bigquery-public-data.thelook_ecommerce.products P

ON OI.product_id = P.id

LEFT JOIN bigquery-public-data.thelook_ecommerce.users U

ON OI.user_id=U.id

GROUP BY p.name, p.category, OI.created_at, U.gender, U.age

ORDER BY Revenue DESC

 Which provided me with:

I was able to export this table to Google Drive and then save the file locally. I had to use this workaround as I was using the free version of Big Query. 

I then imported this into Tableau Desktop.

Once the data was imported into Tableau, I was able to create my visuals. 

Top 10 Products (Revenue)

 The top 10 revenue-generating products, revealing a clear leader. Notably, the majority of these products belong to similar categories, a trend that will be explored in greater detail.

Top 10 Products (Revenue)

This visualisation breaks down the same information by age groups, offering insights into our most popular product target audience.

To create the age groups within Tableau, I created a new Calculated Field that used the 'Age' column from the SQL export. I wrote the following:

IF [Age] >= 0 AND [Age] <= 12 THEN 'Child'

ELSEIF [Age] >= 13 AND [Age] <= 19 THEN 'Teen'

ELSEIF [Age] >= 20 AND [Age] <= 35 THEN 'Young Adult'

ELSEIF [Age] >= 36 AND [Age] <= 50 THEN 'Adult'

ELSE 'Senior'

END

Bottom 10 Products (Revenue)

And in reverse, this visualisation reveals our bottom 10 products based on revenue, suggesting they may not align with our business goals. Consideration for withdrawal is recommended.

Best Performing Categories (Revenue)

Ranked by revenue, "Outerwear and Coats" emerges as the top-performing category, generating $469,621 followed by "Jeans" at $376,391. These categories maintain their strength when further exploring subgroups, as evident in upcoming slides. This suggests the strategic importance for the business to prioritise continued development for these categories as high importance.

Best Performing Categories (Revenue)

Again, a breakdown of age groups for the same visualisation,  offering insights into our best performing category target audience

Worst Performing Categories (Revenue)

And in reverse, this visualisation reveals our worst performing categories based on revenue. It's important to acknowledge that certain products within these categories are smaller items, commonly serving as basket fillers. While these may not yield high markup, they are essential consumer necessities.

Category Preference (Revenue)

Digging a little deeper into which categories our customers prefer

Product Preference (Revenue)

Revenue per Quarter

The quarterly revenue trend since 2019 shown, demonstrates a consistent growth pattern. The data indicates a doubling of revenue each year, showcasing a strong upward trajectory.

Revenue Forecast (Next 4 Quarters)

The forecast indicates a potential revenue milestone, reaching the million mark by the conclusion of Q4 in 2024.

Revenue - What If - Increase 5%

The green line represents an increase in revenue of 5%. What could revenue look like if all revenue had/does increase by 5%? What does the forecast then look like if all revenue increases by 5%

Revenue - What If - Increase 10%

The green line represents an increase in revenue of 10%. What could revenue look like if all revenue had/does increase by 10%? What does the forecast then look like if all revenue increases by 10%

Revenue - What If - Increase 15%

The green line represents an increase in revenue of 15%. What could revenue look like if all revenue had/does increase by 15%? What does the forecast then look like if all revenue increases by 15%

1. Rethink company strategy for the bottom 10 products. They are not bringing in revenue. Is it time to remove these products?

2. Outerwear & coats and Jeans are a clear leader in revenue. The company would be wise to develop strategies to market these products further. Suggestions include product expansion - new styles to fir with customer demand. Competitor analysts/Analyse pricing strategies etc.

3. Review revenue targets. Stakeholders to review the 'Whatif' target % and decide what company targets could be.