"Jokes and pranks are serious business, we need a sales dashboard" - Fred Weasley

Everybody knows about Harry Potter, but do you remember Fred and George Weasley who opened up a joke shop in Diagon Alley? Their shop, Weasleys’ Wizard Wheezes Store, sells prank items, up-to-no-good products, love potions and Muggle magic tricks, among other things. But even in the wizarding world, running a business requires data-driven decisions. That’s why I offered my services as a Muggle data analyst.

If you don't want to read through the whole article, here's a video to show my interactive Power BI dashboard:

How I created the Dataset

Since the Weasleys aren't very well versed in computers and bookkeeping, I had to create the dataset from scratch. Using Python, I generated five CSV files, each representing a table: order, order_line, product, category, and customer. I filled these tables with information provided by the two brothers, what I could remember from the Harry Potter books, and additional data I found online. Additionally, I filled my Calendar table with dates from 01/01/1996 to 31/12/1997, that's the only time frame the Weasleys could provide to me.

Connecting to Power BI and Setting Up the Table Modeling

I uploaded the five tables to Power BI and created a Calendar table as well as a @Measures table. My modeling schema was star-like:

-- two Fact tables with different granularity (it might not be the 'conventional' way which usually involves one single Fact table, but I could not merge both tables as I would have lost information):

order and order_line

-- four Dimension tables:

customer, product, category, and Calendar

The @Measures table (for DAX) was not linked to any other tables.

Focusing on the Master Pranksters' Needs and Expectations

To make the dashboard useful and relevant, I sat down with Fred and George to make sure I understood what they wanted from this dashboard. During our meeting, they shared their business goals and metrics they wanted to monitor. They emphasized the importance of simplicity: “No fancy visuals that nobody understands! We are not Hermione!”

Here’s what they told me:

• Fred: "Alright, let’s cut to the chase. We track a few KPIs, but only the ones that actually matter to us. What we care about is: Total sales this year compared to last year, the average basket size for each customer, and the total number of orders this year versus last year. Those are our TOP 3 most important metrics."
• George: "We don't care so much about Profit and Margin, as we hardly make any. Especially now that You-know-Who is on the loose. We think it’s our duty to sell Shield Hats and Shield Cloaks at discount prices, just a bit of public service."
• Fred: "Well said, twiny! Having a breakdown of monthly sales for this year vs last year would be nice (like in a table, that we could perhaps import to Excel? wink). Also, we’d like to know which months and which days of the week performed best in terms of revenue.”
• George: "Regarding our products and lines, we’d like to know which are our TOP 5 best products in terms of Galleons spent and quantity sold. Also, what time of the day do our biggest sales occur? We need to know so we can bring more sales staff accordingly.”
• Fred: “Last but not least, we need a bit of customer intel... er, customer information. It would be nice to know which Hogwarts school years buy the most, and which Wizard Houses. We get a sense that lots of first years are buying our stuff, and that Slytherin people hate our guts, but a dashboard would confirm that. Might as well track customers by Wizard heritage?... We might tailor products for our Muggle fan base, so we need to track them. No harm in that."
• George: "And that would be it! Nothing else is needed. We want something simple, straightforward, and relevant to our needs."

Dashboard Structure

Based on what Fred and George had told me, I built the following dashboard:

Page 1: Sales Overview

Metrics & Visuals:

• Total Sales (1997 vs. 1996) → KPI Card with Reference Labels
• Total Orders (1997 vs. 1996) → KPI Card with Reference Labels
• Average Basket Size (1997 vs. 1996) → KPI Card with Reference Labels
• Total Revenue per Month (1997 vs. 1996) → Bar Chart
• Total Revenue per Month (Detailed View) → Matrix
• Top Months for Sales → Bar Chart with N Filter
• Top Days of the Week for Sales → Bar Chart with N Filter

Page 2: Products & Lines

Metrics & Visuals:

• Annual Revenue by Product Line → Tree Map
• Top 5 Best-Selling Products (by Sales & Quantity) → Bar Chart with N Filter & Slicer
• Hourly Product Sales (Time of Day Analysis) → Heat Map

Page 3: Customers

Metrics & Visuals:

• New Customers Acquired This Year → KPI Card
• Returning Customers → KPI Card
• Payment Methods by Popularity → Tree Map
• Customer Segmentation by:
• Wizard Heritage → Donut Chart
• Hogwarts House → Ring Chart
• Hogwarts School Year → Bar Chart

Accio Data! Here's the Power Bi Dashboard

Insights and Recommendations

Below are the main insights revealed by the PBI dashboard:

• Sales Growth: Sales grew significantly in 1997 compared to 1996. Fred and George think it might be due to great word-of-mouth and that big order of 400 Shield Hats made from the Ministry of Magic. Summer and Winter Holidays drove sales, with December seeing a fiery boost during Holiday Season. Regarding top days: customers shop more on weekends, as expected. However, sales also spike on Mondays. This trend is worth monitoring, and if it proves consistent, the Weasley twins could capitalize on it by launching special Monday promotions or bundle products that help beat the back-to-school Monday blues.
• Best Products and Lines: Unsurprisingly, the Love Potion from the WonderWitch line was the best-selling product, reflecting a high demand for romance-related items (ask Romilda Vane and her crush on Harry Potter). Beyond that, the always-popular Edible Jokes line (with customer favorites like Nosebleed Nougat and U-No-Poo) continued to drive strong sales. Interestingly, the Defense Against the Dark Arts line, particularly Shield Hats, experienced a major surge, likely driven by the 400 shield hats order made by the Ministry of Magic. With rising concerns over safety due to You-Know-Who being on the loose, wizards get ready to protect themselves.
• Peak Sales Hours: The biggest sales usually occur between 5 pm and 7 pm, so the Weasleys might need extra salespeople during this time to stay efficient and keep customers happy. Because a happy wizard is a return wizard.
• Customer Insights: Data confirmed that First-Year Hogwarts students are the biggest customer base. However, the Weasley brothers need to think about how to attract and retain students as they move to higher years. Gryffindor House is very loyal, which isn’t surprising, but the Weasleys need to cater to Slytherin, Ravenclaw, and Hufflepuff as well. More analysis could help understand their needs and preferences better.

Conclusion

This project was fun to work on! Yet I also learned a lot from it and I took very seriously the shop owners' needs and expectations. By focusing on Fred and George’s specific needs, I built a Power BI dashboard that is both simple and relevant to the Weasley brothers. With insights and recommendations on sales trends, product performance and customer fan base, I helped Fred and George Weasley make better data-driven decisions, even in the unpredictable world of wizardry.

A big thank you to everyone who made it to the end of this article!