A major restaurant chain was managing data of its restaurants from different locations. Restaurants generate a large amount of data daily, so it was a tedious task to fetch and analyse the data to enable the owner to see trends. The challenge was to first consolidate data from all the locations into a single place and then measure key KPIs like:
1. Sales and guest count for different locations
2. Daily sales and average check amount for different locations
3. Item comparison by day, item, and location
4. Lunch & dinner comparison by day, guest count, cost and discounts
We developed this Power BI solution for a customer by extracting data from APIs and feeding them into Power BI for the analysis. This solution summarized statistics on how all stores and restaurants were performing in sales, guest visits, and several other attributes.
After taking data from their APIs, we designed a hybrid Microsoft Azure-based database that consolidated all their data, then imported all the data into Power BI desktop to create reports.
The analysis of Sales and Guest count by location uses a combination chart for each hour and can be filtered using a slicer at the top. Power BI's bookmarking and buttons feature allows easy navigation BACK to the previous page and the NEXT page, and a RESET button can be used to clear all filters applied to the tab.
Owners see a more granular view with the Total Sales by Weekday of Locations chart, and the overall status of lunch and dinner revenues, the values of Guest count and Total Sales are displayed using rotating tiles. Top Selling items can be filtered using a slicer to measure which food item is providing the most revenue.
The Average Sales for Different Day Hours informs about the average sales for different locations during the day.
There is an analysis of Pizza and Burger orders. The page can be filtered based on a date slicer and an item slicer. The Venn Diagram is used to illustrate the sales distribution of Pizza and Burger. The column chart shows the percentage of Total Sales of Pizza v/s Burger in Dinner by each location. The Tornado chart compares the percentage of Total Sales of Pizza v/s Burger in Lunch by location. Added to this is the dot plot that measures the average check amount for each location. The plot uses a slicer to distinguish between the average check amounts for dinner and lunch. There is a word cloud that shows the user comments from various locations of different items.
Then the Lunch & Dinner Details page gives an insight on how the lunch and dinner times are distributed on the weekdays and how it affects the cost, discount, and guest counts for different locations.
The parallel coordinates graph shows the categorization of lunch and dinner on different weekdays and how it impacts on the overall costs, provided discounts and the count of guests.
Synced with this chart is a matrix that categorizes the guest count of all the locations of all the restaurants based on the time of the day i.e., either lunch or dinner.
This is followed by the summary page. The Total Sales by Time graph shows the 50 -50 distribution of lunch and dinner. The Actual Total by Location graph shows the sales distribution over all the locations. The decomposition tree of Location, Item, Time Distribution of Revenue decomposes the total prices by location followed by item, and which is further decomposed into lunch and dinner times.
The location map locates all the locations and can drill through to either Sales & Guest Count by Location or to Pizza & Burger for each location.
The key influencers graph analyses the actual total sales and explains it by discounts, location, weeks, the weekdays, different items & time of day i.e., dinner or lunch.