Exploring Playful Insights with Maven Toys

Retail, Timeseries & Geospatial Analysis

Store KPI Report

Explore the interactive dashboard

SCENARIO:

For this project I acted as a new Data Analyst for Maven Toys, a fictious toy store chain with multiple store locations in Mexico.

Task Assignment:

Build a simple, interactive report that the leadership team can use to monitor key business metrics and high-level trends.

The Goal & Objectives:

The main goal is to find out what products drive profits between each store location, search for any seasonal trends or patterns in sales and observe the impact of inventory, specifically out-of-stock products.

Task is divided into a 4-step process: Connect and profile the data, create a relational model, add calculated measures and fields, and build an interactive report.

Data Source:

The data used was downloaded from Maven Analytics, which was last updated on 05/03/2021. The data includes 6 CSV files containing data from January 2022 – September 2023. There are multiple tables with 829,263 records and 19 fields, which include information about products, stores, daily transactions, and current inventory levels at each location.

OBJECTIVE 1: Connect and profile the data

Connected to the source data files, conducted basic profiling and QA tasks, and became familiar with the tables and fields.

Connect to the sales, products, stores, and calendar csv files:

Connected to each file source in Power BI.

Reviewed table columns, checked for blank or null values and confirmed that data types are accurately defined:

By reviewing the tables, I confirmed that there were no blank or null values. I also changed Store_City’s Data Category to “City” and verified that all other data types were correct.

Primary and foreign keys:

Store_ID is shared among the Store and Sales tables.

Product_ID is shared among the Sales and Products tables.

Date is also shared between Sales and Calendar.

Data Profiling:

There are a total of 829,262 transactions across 50 store locations.

By sorting Product_Price in ascending and descending order, I can tell that the lowest priced item is the $2.99 PlayDoh Can in Arts & Crafts, and the highest priced item is the $39.99 Lego Bricks in Toys.

Calculated columns in the calendar table for ‘start of month’ and ‘start of week’:

Wrote DAX functions for each new column and named them appropriately:

OBJECTIVE 2: Create a relational model

Created a relational data model by defining relationships between tables, created simple hierarchies, and adjusted model properties.

Table relationships:

Created the appropriate relationships between the tables in the data model by connecting the primary and foreign keys of each table.


This model uses a star schema, with 1:many relationships between fact and dimension tables.

Date hierarchy:

Made a date hierarchy containing the ‘start of month’, ‘start of week’, and ‘date’ fields.

Hid foreign keys from the report view:

I hid the fields by right-clicking them in the Model view.

OBJECTIVE 3: Add calculated measures & fields

Enhanced the data model and prepared for analysis by defining new measures and calculated fields.

Calculated columns in the sales table:

I pulled in ‘cost’ and ‘price’ from the products table by using the RELATED function.

Revenue and profit for each transaction:

Using a simple multiplication formula, I calculated revenue into a new column.

Then added a column to calculate the profit.

Measures to calculate the count of orders (‘total orders’):

Using the DISTINCTCOUNT function, I made a new card on the report view showing the total count of orders.

Sum of revenue (‘total revenue’) and sum of profit (‘total profit’):

I could have used the simple SUM function to calculate Total Revenue and Profit, but instead chose to practice using the SUMX function.

Total Revenue = SUMX(sales, sales[Units] * RELATED(products[Product_Price])

Total Profit = SUMX(sales, sales[Total Revenue] – (sales[Unites] * RELATED(products[Product_Cost])))

OBJECTIVE 4: Build an interactive report

Visualized the data and created an interactive report to show orders, revenue and profit over time and by product category.

KPI card visuals:

I added visuals showing ‘total orders’, ‘total revenue’ and ‘total profit’ for the current month, along with monthly trends for each metric.

Added a slicer to filter the report page by store location:

Bar & line charts:

Added bar charts to show ‘total orders’ by product category, and a line chart showing ‘total revenue’ with the date hierarchy on the x-axis:

Format & Finalize the Report:

Added a company logo (I created myself) and assembled the charts into a logical layout and adjusted formatting, alignment and polished the overall appearance.

Through further assessment, I realized that I could include a few more charts to show number of units and how that compared to the monthly revenue.

I also included more bar charts to break down the product categories to see stats on specific products and stores.

OBSERVATIONS & INSIGHTS

The Toys category drives the most in profits overall, with Colorbuds being the most profitable product. It dominates orders across all store locations, except one. The Art & Crafts category is more popular Downtown with its most popular product being PlayDoh Can. I suggest exploring the Downtown art scene and find out why Art & Crafts are popular in that location. We can feature the PlayDoh Can in local Art events or town festivals.

Overall, there is a dip in the months of August and September and a spike in profits in December. This shows that shopping trends tend to be slower at the end of summer and the expected upward trend during the holiday season. By advertising certain products with branding focused on education, such as school supplies and classroom learning tools, we can increase the profits during these months. For example, let’s bring PlayDoh Cans into the classroom for learning.

Games and Electronics seem to only have spikes during the Christmas holiday. These do not seem to bring in a lot of profit overall, but maybe pushing advertisements during the holiday season will help increase some profits in these categories.

I see a trend in the Sports & Outdoors category, with spikes during the summer months. I suggest focusing on this category leading up to summer to increase profits during summer.

By analyzing monthly revenue compared to units in stock, we can see they follow the same trends closely, meaning there is correlation between the two. This is true for each location. Because these have such a strong correlation, we can try increasing in-stock units and we may be able to see an increase in orders and revenue as a result. This doesn’t necessarily mean there is causation between units and revenue, but it is something to explore.