Retail chain's dashboard based on a ClickHouse® DB

In this tutorial, you will set up a dashboard to visualize sales analytics for a Moscow retail chain. This dashboard will present:

• Sales dynamics by day and month.
• Sales by product categories.
• Heat map of orders.

We will use a Moscow retail chain's demo sales ClickHouse® database as our data source.

To visualize and explore data, set up DataLens and follow the steps below:

1. Create a workbook.
2. Create a connection.
3. Create a dataset.
4. Create a line chart.
5. Create a bar chart.
6. Create a pivot table chart.
7. Create a heat map.
8. Create a dashboard and add charts to it.
9. Add selectors to the dashboard.

Getting startedGetting started

To get started with DataLens:

If you have any technical questions about the service, contact Yandex Cloud support. To ask for advice, discuss the solution to your problem or best practices of the service, write to the DataLens chat in Telegram.

Create a workbookCreate a workbook

1. Go to the DataLens home page.
2. In the left-hand panel, select Collections and workbooks.
3. In the top-right corner, click Create → Create workbook.
4. Enter a name for the workbook: Tutorials.
5. Click Create.

Create a connectionCreate a connection

A connection named Sample ClickHouse will be created for database access.

1. In the top-right corner of the workbook, click Create → Connection.

2. Under Databases, select the ClickHouse® connection.

   1. In the window that opens, select Specify manually for the connection type and provide the following connection parameters:

      • Host name: rc1a-ckg8nrosr2lim5iz.mdb.yandexcloud.net
      • HTTP interface port: 8443 (default)
      • Username: samples_ro
      • Password: MsgfcjEhJk

   2. Enable Allow subqueries in datasets and queries from charts.

   3. Click Check connection and make sure you get a green check mark.

   4. Click Create connection.

      

   5. Enter the connection name:Sample ClickHouse.

   6. Click Create.

Wait for the connection to be saved.

Create a datasetCreate a dataset

Create a dataset based on the Sample ClickHouse connection:

1. In the top-right corner of the connection page, click Create dataset.

2. Drag the MS_SalesFacts table to the workspace.

   

3. Drag the MS_Clients table to the workspace. The tables will be linked together automatically.

   

4. To check the link, click the link icon between the tables.

   

5. The tables are linked by the ClientID field. If needed, you can modify or expand the link by specifying another pair of fields. To close the link settings window, click or anywhere outside the window.

   

6. Drag the MS_Products table to the workspace. The table will automatically link with the left (root) table MS_SalesFacts.

   

7. Drag the MS_Shops table to the workspace. The table will automatically link with the left (root) table MS_SalesFacts.

   

8. Navigate to the Fields tab.

9. Delete the duplicate fields left over from joining the tables: ClientID (1), ProductID (1), and ShopID (1). To do this, select them and click Delete in the panel below the list of fields.

   

10. Create an order date field named OrderDate:

    1. Duplicate the OrderDatetime field: on the right side of the row with the field, click and select Duplicate.
    2. Rename the OrderDatetime (1) duplicate field as OrderDate: click the field name, delete the current name, and enter the new one.
    3. In the Type column, change the data type from Date and time to Date.

    

11. Change data types for the fields as follows:

    • ShopDistrictCoordinates: Change to Geopolygon.
    • DeliveryDistrictCoordinates: Change to Geopolygon.
    • DeliveryAddressCoord: Change to Geopoint.

12. Create a measure for the order amount: in the Aggregation column, select Sum for the Price field. The aggregation field will change its color to blue: it is now a measure.

    

13. Create a measure for the number of orders:

    1. Duplicate the OrderID field.
    2. Rename the OrderID (1) duplicate field to OrderCount.
    3. Change the aggregation type to Number of unique.

14. Create a calculated field for the average sales amount per order:

    1. In the top-right corner, click Add field.
    2. At the top-left, specify the field name: Sales per Order.
    3. In the column to the left, click the Price field.
    4. Enter /.
    5. In the column to the left, click the OrderCount field.
    6. Click Create.

    

15. Save the dataset:

    1. In the top-right corner, click Save.
    2. Enter Moscow Sales dataset for the dataset name and click Create.

Create a line chartCreate a line chart

To visualize sales dynamics by month, create a line chart:

1. Click Create chart in the top-right corner of the dataset page.

2. For the visualization type, choose Line chart.

   

3. Add a sales date to the chart. To do this, drag the OrderDate field from Dimensions to the X section.

4. Add a sales measure to the chart. To do this, drag the Price field from Measures to the Y section.

5. Add a delivery type to the chart. To do this, drag the PaymentType field from Dimensions to the Colors section.

   

6. Display the chart by month:

   1. Click the calendar icon next to the OrderDate field in the X section.
   2. In the Grouping field, select Rounding ⟶ Month and click Apply.

   

7. Save the chart:

   1. In the top-right corner, click Save.
   2. In the window that opens, enter a name for the chart: Sales dynamics by month and payment type, and click Save.

   

Create a column chartCreate a column chart

To visualize sales by brand and product category, create a column chart.

1. Copy the chart you created in the previous step:

   1. In the top-right corner, click → Save as copy.

      

   2. In the window that opens, enter the Sales by brand and category name for the new chart and click Save.

2. Select Bar chart as the visualization type.

   

3. The OrderDate, Price, and PaymentType fields are automatically added to the X, Y, and Colors sections, respectively.

4. Replace the months with brands on the X-axis. To do this, drag the ProductBrand field from Dimensions to the X section and hold it over the OrderDate field until the latter turns red.

5. Under Colors, replace payment types with product categories. To do this, drag the ProductCategory field from Dimensions to the Colors section and hold it over the PaymentType field until the latter turns red.

6. Sort the chart in descending order of the sales measure. To do this, drag the Price field from Measures to the Sorting section.

7. Save your chart: click Save in the top-right corner.

   

Create a pivot table chartCreate a pivot table chart

To visualize sales by product and time, create a pivot table chart.

1. Copy the chart you created in the previous step:

   1. In the top-right corner, click → Save as copy.
   2. In the window that opens, enter the name Sales by year and product for the new chart, and click Save.

2. Choose the Pivot table visualization type.

   

3. The ProductBrand and Price fields will automatically appear in the Columns and Measures sections, respectively.

4. Remove ProductBrand from the table. To do this, click (the icon appears when you hover over the field) next to the field in the Columns section.

5. Add the order date to the table. To do this, drag the OrderDate field from Dimensions to the Columns section.

6. Change the display format in the OrderDate field to years:

   1. Click the calendar icon next to the OrderDate field in the Columns section.
   2. In the Grouping field, choose Date portion ⟶ Year and click Apply.

   

7. Add the product category and subcategory to the table. To do this, drag the ProductCategory and ProductSubcategory fields from Dimensions to the Rows section.

8. Change the color of the sales measure in the table. To do this, drag the Price field from Measures to the Colors section.

9. In the top-right corner, click Save.

   

Create a heat map chartCreate a heat map chart

To visualize the density of orders on the map of Moscow, create a heat map chart.

1. Copy the chart you created in the previous step:

   1. In the top-right corner, click → Save as copy.
   2. In the window that opens, enter the Sales heat map name for the new chart and click Save.

2. Select the Map visualization type.

   

3. Select the Heat map (Geopoints) layer type.

   

4. Add the delivery point coordinates to the map. To do this, drag the DeliveryAddressCoord field from Dimensions to the Heatmap (Geopoints) section.

5. In the top-right corner, click Save.

   

Create a dashboard and add charts to itCreate a dashboard and add charts to it

Create a dashboard to add charts and other widgets to:

1. In the left-hand panel, select Collections and workbooks and go to the Tutorials workbook.

2. In the top-right corner, click Create → Dashboard.

3. In the panel at the bottom of the page, hold down Chart and drag it to the required area.

   

4. In the window that opens, click Select.

5. Select the chart Sales dynamics by month and payment type.

6. Click Add.

   

7. Repeat steps 3-6 to add these charts:

   • Sales by brand and category
   • Sales by year and product
   • Sales heat map

8. Adjust the size of the charts with your mouse and place them on the dashboard as you prefer.

   

Add selectors to the dashboardAdd selectors to the dashboard

Add selectors to filter your charts by date, Moscow district, product, and customer status:

1. In the panel at the bottom of the page, hold down Selector and drag it to the required area.

   

2. Add the calendar selector for the order date:

   1. Select Moscow Sales dataset.
   2. Select the OrderDate field.
   3. Enable Range.
   4. Click Save.

   

3. Add a product category selector:

   1. Make sure you selected the Moscow Sales dataset dataset.
   2. Select the ProductCategory field.
   3. Enable Multiple choice.
   4. Click Save.

4. Similarly, add selectors for the following fields:

   • ProductBrand
   • DeliveryDistrictName
   • DeliveryType
   • PaymentType

5. Position the selectors on the dashboard however you like.

6. Save the dashboard:

   1. In the top-right corner of the dashboard, click Save.
   2. Enter Moscow Shops dashboard for the dashboard name and click Create.

Your dashboard is ready.

You can now use various filter combinations to see how your sales evolved for different products and cross sections. For example, you can analyze the dynamics of sales with delivery for the Household goods and Home appliances categories within the Izmaylovo delivery area over the period 01.01.2017 - 31.12.2018 and estimate the sales density on the heat map. To do this, set the selectors as follows:

• OrderDate: 01.01.2017 - 31.12.2018
• ProductCategory: Household goods and Home appliances
• DeliveryDistrictName: Izmaylovo
• DeliveryType: Delivery

ClickHouse® is a registered trademark of ClickHouse, Inc.