Northwind Traders KPI Dashboard

Keywords project description

Northwind Traders, a global import and export company that specializes in supplying high-quality gourmet food products to restaurants, cafes, and specialty food retailers around the world.

Role: BI Developer

Goal: top-level KPI dashboard for the executive team. Quick insights in sales trends, product performance, key customers & shipping costs.

Data

Data were delivered in .csv format, almost a 1:1 mapping of the datamodel to be.

All the renaming/removing of columns, replacing of data & merging was done in PowerQuery. I chose a different date-slider as visual to make in-between dates more userfriendly and flexible. Data-filtering for unfinished months was done in Powerquery. I think in a high-level overview it looks odd to show results of a month which just started.

I created a calendar-table from the orderdata, which starts with the first orderdate and ends with the last orderdate. I wanted the datafilter to only show the periode involved in the dataset (dynamically). The idea was to solve this in a way a dataset with transactions until today would be handled correctly.

For shipping I decided a definition of late would be more interesting than time between order and shipped. The data dictionary says about required date: "The date when the customer requested the order to be delivered". My english is not good enough to know if I can interprete this as "exactly on that date I want my delivery" or "on the required date or earlier is fine". There is no "delivered date" available in the datatables. The definition of late I used is "shipped on the required date (maybe some customers will get nervous) or later (certainly late)".

Dashboard considerations

Until now I have no idea how sales is organized at Northwind Traders. There are 9 employees in the employee table, the most important one the "VP Sales", based in the USA, a salesteam USA (4 persons) and a salesteam UK(4). Both teams have a manager, those managers report to the VP Sales.

In the timeframe we're looking at there are around 90 customers.

I've played around with my dashboard and I could not discover when looking at the two separte teams if there is another sales strategy besides "sales is just sales, the one who is awake does the transaction". For every strategy I thought of, "USA certain product(groups), UK the rest", "USA big customers, UK small ones", "UK does Europe, USA the rest", there were enough examples which contradicted this idea. The VP Sales also has his name as Northwind contact for quit some salestransactions. And maybe those transactions were mostly prepared by another teammember, hence no performance visuals on individuals.

I think of this meeting with high-level persons in the organisation, including the well-informed VP Sales looking at my dashboard. What are they talking about.

Overall performance for a certain period: the summary cards on top. I omitted insights on the dashboard, except the jobdescription of the most valuable customer contact. This one is dynamically calculated based on a dax-formula (sales by jobdescription customer).

A possible start for a conversation in the boardroom about the data presented (why is what on the dashboard)

Sales MOM%, $ per month: "Good, we're growing"

Shipping question: "Can't we switch shipper A for B, A is alway late and expensive?" Possible answers: "Yes, good idea", or "No, we can't, A does all the xxx, B can not. Maybe we can discuss the shippingcosts", or "He's not always late, the goods were not available in time, there was nothing to ship".

Ordersize > $2500: "I see the large orders are really making a difference in sales, do you have any ideas how to get more large orders or increase ordersize further?" (note, 2500 is arbitrary).

Most valuable customers: Question "I did not know Quick-Stop and Save-a-Lot Markets were that important for our sales. Have we maximized the efforts to serve them good? Do you see any other customers who could be our next Quick-Stop? Do we offer them the same service?"

Top-5, bottom-5 products: "Why haven't we discontinued our bottom 5 products?"

Sales productcategories, categorized by teams: I think this conversation would largely depend on the salesstrategy.