Maven Food Market Analysis in North America

MavenMarket Project Description

1.Introduction

MavenMarket is sales company which sells food item in North America. The main goal of this project is to create an interactive dashboard using PowerBI for to track the company’s overall performance in terms of sales, revenue, profit, transaction and comparison of these parameters in three countries in North America.

2. Objectives

• A comparative analysis of given data for USA, Canada and Mexico.

• Provide a concise overview of MavenMarket's product sales, profit, and sales performance.

• A evaluation of how the company's sales are spread out in different parts of North America

In order to achieve the project's objectives, the interface will be meticulously crafted and evaluated.

3. Data Preparation & Quality issues:

Seven data tables were uploaded to the Power Query Editor and Deselect “Autodetect relationships after data is loaded”. Also set to language “English (United States)” in the Regional Settings tab. The tables were then renamed based on facts and dimensions, and the data types were checked for all tables. The quality for the data of all seven tables was checked using column quality, column distribution, and column profile from the view tab of the power query editor. The data set was 100% valid with no errors. More specific tablewise tasks are describe below:

MavenMarket_Customers

· In customer tables headers have been promoted and data types were checked for each column

· A new column is added named “full name” merging “first_name” and “last_name” by Merge column option from Add column tab.

· Another two column were added, one “year” column from birthdate and one conditional column named “ has_children” were created using conditional column from add column.

MavenMarket_product

· In the same way MavenMarket_product table was formatted. Added a calculated column named “discounted price” Groupby option was utilized to calculate the average retail price by brand. Finally, Replace "null" values with zeros in both the "recyclable" and "low-fat" columns

MavenMarket_Stores

Added a calculated column named "full_address", by merging "store_city", "store_state", and "store_country", separated by a comma and space

MavenMarket_Regions

Added new column named Start of Week (starting Sunday), Name of Day,Start of Month, Name of Month, Quarter of Year, Year using date tools in the power query editor.

MavenMarket_Returns

Two files MavenMarket_Transactions_1997 and MavenMarket_Transactions_1998 csv were

uploaded from a folder from desktop and did necessary data check.

---

4. Data modelling

At first, the fact tables and dimension tables were identified. For identification, I used primary and foreign keys for the given table. Here, transaction-data and returns were fact tables and other five were dimension/look-up tables. Usually, the dimension table holds one column that is considered as primary key of the table and consists of all distinct values. I developed one to many relations between fact and dimension tables. Generally, the fact table contains multiple foreign keys in different columns which are not distinct in nature. I followed the snowflake schema to perform data modelling here.

· I connected Transaction_Data to Customers, Products, Calender and Stores using valid primary/foreign keys. Also, connected Return_Data to Products, Calendar, and Stores using valid primary/foreign keys. Connected Stores to Regions as a "snowflake" schema

· I have also double checked that all relationships follow one-to-many cardinality, with primary keys (1) on the lookup side and foreign keys () on the data side, no both way filters, Filter context flows "downstream*" from lookup tables to data tables, data tables are connected via shared lookup tables

5. Adding DAX Measures

To advance the project, I have added the following DAX measures in report view.

· % Weekend Transaction = DIVIDE([Weekend Transaction], [Total Transaction])

· All Transaction = SUM(Transaction_Data[transaction_date])

· Last Month Profit = CALCULATE([Total Profit],DATEADD('Calendar'[date], -1, MONTH))

· Last Month Return = CALCULATE([Total Return],DATEADD('Calendar'[date], -1, MONTH))

· Last Month Revenue = CALCULATE([Total Revenue],DATEADD('Calendar'[date], -1, MONTH))

· Last Month Transaction = CALCULATE([Total Transaction],DATEADD('Calendar'[date], -1, MONTH))

· Profit Margin = DIVIDE([Total Profit], [Total Revenue])

· Quantity returned = SUM(Returns[quantity])

· Quantity returned = SUM(Returns[quantity])

· Return Rate = DIVIDE([Quantity returned], [Quantity Sold])

· Revenue Target = 1.05 * [Last Month Revenue]

· Total Cost = SUMX(Transaction_Data,Transaction_Data[quantity]*RELATED(Products[product_cost]))

· Total Profit = [Total Revenue] - [Total Cost]

· Total Return = COUNT(Returns[return_date])

· Total Rev w/o sumx = SUM(Transaction_Data[Revenue])

· Total Revenue = SUMX(Transaction_Data,Transaction_Data[quantity]* RELATED(Products[product_retail_price]))

· Total Transaction = COUNT(Transaction_Data[transaction_date])

· Unique Product = DISTINCTCOUNT(Products[product_name])

· Weekend Transaction = CALCULATE([Total Transaction], 'Calendar'[Weekend] = "Y")

· YTD Revenue = CALCULATE([Total Revenue],DATESYTD('Calendar'[date]))

6. Data Analysis:

A substantial amount of time was invested to understand the nature of the data. Understanding the context of the data is the most important part of the projects. Mostly, I used some bar chart, table, and matrix to define the whole context of the problem.

In any project, the fact tables are the main point of the whole analysis. Consequently, to my understanding, I started to develop data measures and a calculated column. Using the power query editor, the start of quarter, start of month, and start of week columns were created based on the close_date column to track the time-sensitive distribution of sales. Another column, ‘Deal Preparation Days’, was added by subtracting close_date from engage_date to track the number of days required to reach a deal. In the table per head, revenue of employee was added using the divide function.

In this project, the performance of the sales team depended on how many deals they won in 2017. Also, the final sales were pretty much related to the number of deals won. More specifically, I prepared an expression for Deal Won, lost, engaging, and prospection and also found the percentage of the respective deal stages. total number of deals and total sales were calculated using DAX measures. After developing each measure, it is essential to check the value of the measure using a card to justify the expression and also check for errors.

A line chart was developed to follow the distribution of sales over time in the year 2017. A bar graph described the product category of Maven Tech in terms of sales.

7. Dashboard Development:

· Created a Matrix visual to display Total Transactions, Total Profit, Profit Margin, and Return Rate by Product_Brand

· Used conditional formatting to display data bars in the Total Transactions column and color scales for Profit Margin (white to green) and Return Rate (white to red).

· Utilized Top N filter from filter Pane to display top 30 product and sort descending by total transaction.

· Added a KPI Card to show Total Transactions, with Start of Month as the trend axis and Last Month Transactions as the target goal. Similarly, added Total Profit (vs. Last month Profit) and one for Total Returns (vs. Last Month Returns) and updated title for all three KPIs.

· Added a Map visual to show Total Transactions by store city and added a slicer to filter store city.

· A treemap was created to break down the total transaction by store city and also made a hierarchy by making a group of store_state, store_city and store_country to enable drilldown options.

· A column chart was made to display the Total Revenue by week and formatted.

· A gauge chart was also added to display the Total Revenue Vs Revenue target and add a visual level TOP N filter to show the latest month.

· Finally Edit Interaction option was used to restricted the treemap from filtering.

· Added a bookmark carrying the info "Portland hits 1,000 sales in December"