Welcome to the first blog of 2023! It’s amazing how quickly another year has passed, and now we are starting a new journey together. As we embark on this new chapter, it’s perfect timing for The Data School’s well-known Dashboard week. During this week, we will have to build a dashboard, present it, and write a blog about a new dataset within one day. An exciting aspect of today’s challenge is that we will be using Power BI instead of Tableau to create the dashboard.

The Dataset

The focus of today’s challenge is the question: “Where should I open a new cafe?”. We have been provided with a dataset containing information about cafes, restaurants, bistro locations, and seating capacity over time. This dataset can be accessed here. Additionally, we have the option to supplement our analysis with additional data.

My Approach

As I have no prior experience in running my own business, there may be some difficulties in understanding all of the requirements. However, I have some experience in the customer service industry and I believe that having a thorough understanding of your target customer is crucial for success in business. To gain insights about my target customer, I plan to supplement the data with information about the number of businesses in different industries for different blocks. This will allow me to determine which blocks would be the best locations for my cafe. Additionally, I would like to consider the total number of jobs in each block as a way to gauge the potential population of people working in the area. To do this, I will use a third dataset in this challenge.

Data Preparation

Stage 1: Alteryx

The initial stage of Data Preparation will involve using Alteryx to join the three datasets that I mentioned previously. I have chosen to use Alteryx rather than Power BI’s Transforming Data feature to merge the datasets because I need to join the files using more than one common field, which is not possible with Power BI’s relationship feature. Additionally, using the merge queries option in Power BI may make it difficult for me to control the filters on the dashboard due to my limited experience with this software. Therefore, using Alteryx to join the three files will be a safer choice for me in this case.

Stage 2: Power BI Transforming Data

The next stage of the process will be using Power BI.

In this stage there are 3 parts:

  1. Standard step in inserting query.
  2. Change Census Data type from Numerical to Year. In order to do this we have to convert from numerical data to text, and then text to Year. If we convert directly from number to text then an error of wrong year converted will appear.
  3. Pivot the data from business establishment dataset columns

Dashboard

And now is the time for the dashboard. First, let’s have a quick look at the dashboard

There are some key things about this dashboard:

  1. You can choose your target customer based on different industry. In the example above, my ideal customer is student, therefore the dashboard will be filtered by Education and Training.
  2. The bar chart will list down all the block with its average number of businesses in each block, sorted by number of businesses. Noted that the dashboard will be using the newest census date (2021).
  3. I have 3 cards showing the information about that block: Number of cafe/ restaurant in this block, the number of base property and total number of jobs/employees in this block.

Insight: As we can see that there are numerous of coffee stores in this block, however there is currently no coffee store in the top left corner. Therefore, I believe this present an opportunity to open a coffee store in that location, which could attract customers from that side of the street.

Last function of the dashboard, in terms of customer, It would be better if we know more about our competitors, using a navigator button in the bottom left of the dashboard, where we can see number of seats that each of the competitors have

Now that we have identified our competitors and have chosen a suitable location for our business, let’s start serving coffee and see how things go. Bon appétit!

 

The Data School
Author: The Data School