Introduction

Maven Toys is a fictitious chain of small toy stores; they currently have 50 stores spanning 29 cities throughout Mexico. I was hired as a BI consultant to analyze patterns and trends in their sales and inventory data to create an expansion plan for 2024.

Business Questions

1. Where should Maven Toys expand to?
2. How many stores should they open?
3. Which products will perform best at the new location(s)?

About the Data

Sales & inventory data by Maven Analytics for the fictitious toy stores, includes information about products, stores, daily sales transactions, and current inventory levels at each location. It was collected between January 1, 2022, and September 30, 2023. The data contains 4 tables consisting of 16 fields and 829,262 records in csv format.

I sourced Mexican airport passenger traffic data to determine which airports would be best suited for expansion. Wikipedia 'List of the busiest airports in Mexico' includes the 50 busiest airports by passenger traffic between 2020-2022. For the purposes of this analysis only data from 2021 and 2022 was used.

Tools Used

• Excel (Data Cleaning)
• SQL BigQuery (Data Exploration)
• Tableau (Visualization)

I created a database schema to better understand relationships between the 4 tables, In order to join the tables in Tableau.

Exploring the products and sales using SQL

Exploring the stores and potential expansion locations

The complete SQL code can be found Here

Insights

1. Where should Maven Toys expand to?

Maven Toys has 4 location/store types: Airport, Commercial, Downtown and Residential. But not all cities have all 4 of the location types, leaving room for expansion into new cities and/or location types.

The analysis shows that Maven’s top 2 stores are located at Mexico City and Guadalajara International Airports, accounting for 7% of total sales. And overall, airport locations do almost 2 times as well as other types of locations.

Cancun and Tijuana international airports are top choices for expansion in 2024. They are among the top airports in terms of passenger traffic and currently do not have any Maven Toy's stores. They also boast an impressive 36% and 27% yoy growth in passenger traffic respectively.

2. How many stores should they open?

Maven Toys has been opening stores for the past 30+ years, although they have only opened stores during 19 of those years. On average (only considering the 19 years in which stores were opened), 2.6 stores have been opened per year. Opening 2 stores would be in line with this trend.

3. Which products will perform best at the new location(s)?

Since the new stores will be airport stores, we need to determine which products do best at this type of location. Airport location sales data shows that these products (with above average sales) are the top performers at existing airport stores.

Lego Bricks, the top product, has been skyrocketing in sales. While Colorbuds has been plummeting in the same period.

The new stores can expect to see $273K (Cancun) and $111K (Tijuana) in sales based on an average 0.9% conversion rate of airport passengers to product sales.

Recommendations

• Open two Stores: Cancun and Tijuana International Airports are both fast-growing and busy airports, based on passenger traffic. These would work well since existing airport stores are top performers. We expect sales at the Cancun and Tijuana stores to be $273K and $111K, respectively.

• Display Top Products: Since space is at a premium at airport stores only stock products with above average sales within each category (based on top performers from existing airport stores):

• Toys - Lego Bricks, Action Figure, Animal Figures

• Arts & Crafts - Magic Sand, Kids Makeup Kit

• Electronics - Colorbuds, Gamer Headphones

• Games - Glass Marbles, Rubik's Cube, Deck of Cards

• Sports & Outdoors - Nerf gun, Splash Balls, Dart Gun

• Increase Sales: Colorbuds has been steadily decreasing in sales over time. This could be due to a variety of factors which would need further investigation. Meanwhile, they should be displayed prominently in stores to see if sales improve.

• Increase Profitability: Lego Bricks is the top seller but has among the lowest profit ratio of all the products. Since it's becoming a very popular product consider raising the price, avoiding markdowns and working with suppliers to reduce product costs.

Data Limitations

• The Maven Toys data is not comprehensive as it only covers one full year (2022) and partial data for 2023 (up to September).
• Airport passenger traffic data is only available up to 2022.
• To better analyze current trends, obtaining updated Maven Toys data beyond September 2023 and acquiring 2023 airport passenger traffic data would be necessary.

Data Sources

• Sales & inventory data
• List of the busiest airports in Mexico