Yönetim Dashboard'u: Bir Yılın Satışı Üç SayfadaExecutive Dashboard: A Year of Sales on Three Pages

Management looks at a dashboard with three questions: how is revenue doing, who are the customers, and where is money leaking? From two years of an online retailer's sales I built a three-page Power BI dashboard that answers them.

Data model

The data is the open Online Retail II dataset I used in the customer segmentation study. This time I kept the sales without a customer ID, because total revenue must not look incomplete in a management report. In Python I reshaped the data into a star schema: sales rows in the middle, three dimension tables around them.

• Parquet instead of CSV. Column types travel with the file. Under Turkish regional settings, decimals such as 2.55 in a CSV can be misread.
• Segments carried into the model. The segment column in the customer table comes from the earlier study; the dashboard's segment breakdowns rely on it.
• A separate date table. For year-over-year comparisons I built a calendar table and marked it as the date table. Month and day names sort by their number columns.

Measures

All 17 measures are built on a few base measures. Four examples:

Net Ciro = SUM ( Satis[Ciro] )

İade Oranı = DIVIDE ( [İade Tutarı], [Brüt Ciro] )

Geçen Yıl Net Ciro = CALCULATE ( [Net Ciro], SAMEPERIODLASTYEAR ( Tarih[Tarih] ) )

Aktif Müşteri = CALCULATE ( DISTINCTCOUNTNOBLANK ( Satis[MusteriNo] ), Satis[IadeMi] = 0 )

Returns sit in the data as rows with negative amounts. Net revenue deducts them, gross revenue leaves them out, and the return rate is the returned amount divided by gross revenue. The DAX post covers the logic behind the formulas.

Dashboard

The report's three pages are below. The tabs switch pages and the buttons above switch the year; hover over a chart to see the values, and “Table view” gives the same figures as tables.

The findings in the next three sections are for 2011. Switch the year to 2010 to compare.

Page 1: Overview

Five cards summarize the year. The line chart compares each month with the same month a year earlier.

• Revenue appears to have fallen. Net revenue was £9,012,895 in 2011 and £9,135,191 in 2010, a drop of 1.34%. But the data ends on December 9, 2011. Comparing January–November, 2011 is 2.33% ahead; on the chart the two lines run together until November and part in December.
• 84% of revenue comes from one country. I excluded the United Kingdom from the bar chart; otherwise the other 36 countries are invisible. The Netherlands (£274,725) and Ireland (£250,700) lead the rest.
• Champions bring 45.9% of revenue. The segment chart shows how much of 2011 revenue each group from the segmentation study accounts for.

Open the Overview page in the dashboard ↑

Page 2: Customers

4,214 customers bought in 2011; 1,537 of them for the first time. Average revenue per customer was £1,831.

• Few customers, most of the revenue. Champions are 16% of active customers (672) and 45.9% of revenue.
• Second-year arrivals. The 1,579 customers who bought nothing in the first year brought 16.3% of 2011 revenue.
• Existing customers carry each month. Even in October, the month with the most new customers, only 221 of 1,361 active customers were new.
• Sales without a customer. 14.4% of revenue comes from sales with no customer ID. I keep that on its own card; in the segment charts it has its own row.

Open the Customers page in the dashboard ↑

Page 3: Products and Returns

The return rate was 2.54% in 2010 and 4.84% in 2011, nearly double. The monthly chart shows why. For ten months the rate stays between 1.2% and 6.5%; it jumps to 13.7% in January and 28.3% in December.

The returns chart singles out two products. Each is one giant order cancelled the day it was placed: 74,215 ceramic storage jars on January 18 (£77,184) and 80,995 paper bird decorations on December 9 (£168,470).

The heat map shows when orders arrive: the busiest hour is noon and the busiest day is Thursday (3,814 orders). There are no orders at all on Saturdays, and Sunday orders fall between 10:00 and 16:00.

Open the Products and Returns page in the dashboard ↑

How it looks in Power BI

The charts above are the visuals of the Power BI report, redrawn on this page from the same data. The figures are identical; a few visuals take a different form (the segment donut in Power BI is a bar chart here, for example). The report's first page in Power BI Desktop:

Verifying the numbers

A dashboard looks just as tidy when its numbers are wrong. So I calculated the key measures separately in Python (pandas) and compared them with the values in Power BI.

The rounded values in the Power BI column are how the cards display them; amounts are in pounds.

In the first comparison “Net revenue, last year” came out equal to this year's revenue: the relationship between the sales and date tables was missing. The chart looked fine; the control figure caught the error.

Limitations

Conclusion

Two figures pointed the wrong way when read alone: “revenue fell” was a partial December, and “returns doubled” was two cancellations. In both cases the chart next to the figure cleared it up. That was my main rule in designing the dashboard: every summary figure gets a breakdown beside it that explains it.

The most instructive part for me was seeing that the model comes before the visuals. A missing relationship or a product code written two ways produces wrong numbers however clean the visuals look.

Data: Chen, D. (2019). Online Retail II. UCI Machine Learning Repository. Amounts are in pounds sterling. The screenshot was taken with 2011 selected; the report's labels are in Turkish.