Analyzing a store chain's sales based on data from a ClickHouse DB

As a data source, we'll use a demo ClickHouse database on sales in a Moscow chain of stores.

In this example, DataLens is used to visualize:

• Sales dynamics by day and month.
• Sales by product categories.
• Order heat map.

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

To visualize and analyze data, set up DataLens and follow these steps:

1. Define the data source for a dataset.
2. Configure dataset fields.
3. Create a line chart.
4. Create a column chart.
5. Create a pivot table chart.
6. Create a heat map chart.
7. Create a dashboard.
8. Add charts to the dashboard.
9. Add selectors to the dashboard.

Before you start

To get started with DataLens:

If you have a technical question about the service, please contact support. To ask for advice or discuss the solution to your problem or best service practices, write to the DataLens chat in Telegram.

Step 1. Create a connection and a dataset

1. Go to the connections page.

2. Click Create connection.

3. Select the ClickHouse connection.

   1. In the window that opens, specify the connection parameters:

      • Connection name: Sample ClickHouse.

      • Connection type: Specify manually.

      • Host name: rc1a-ckg8nrosr2lim5iz.mdb.yandexcloud.net.

      • HTTP interface port: 8443 (default).

      • Username: samples_ro.

      • Password: MsgfcjEhJk.

   2. Enable the HTTPS and the Allow subqueries in datasets options.

   3. Check the connection and click Create.

      

      Wait for the connection to be saved.

4. In the upper-right corner, click Create dataset.

Step 2. Configure dataset fields

1. Drag the MS_SalesFacts table to the workspace.

   

2. Drag the MS_Clients table to the workspace. The tables are automatically linked.

   

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

   

4. 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 the cross button or anywhere outside the window.

   

5. Drag the MS_Products table to the workspace. The table is automatically linked to the left (root) table MS_SalesFacts.

   

6. Drag the MS_Shops table to the workspace. The table is automatically linked to the left (root) table MS_SalesFacts.

   

7. Go to the Fields tab.

   

8. Delete the duplicate fields left over from joining the tables: ClientID (1), ProductID (1), and ShopID (1).

   

9. Create an order date field named OrderDate.

   1. Duplicate the OrderDatetime field.

      

   2. Rename the OrderDatetime (1) duplicate field to OrderDate: click the row name, delete the current name, and enter the new one.

      

   3. Change the data type from Date and time to Date.

      

10. For the ShopDistrictCoordinates field, change the data type to Geopolygon.

11. For the DeliveryDistrictCoordinates field, change the data type to Geopolygon.

12. For the DeliveryAddressCoord field, change the data type to Geopoint.

13. In the Aggregation column, select Sum for the Sales field.

    

    The aggregation field changes color to blue: Sales is now a measure.

    

14. 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.

    

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

    1. In the upper-right corner, click Add field.

    2. In the Name field, specify Sales per Order.

    3. In the column to the left, click on the Sales field.

    4. Enter a /.

    5. In the column to the left, click the OrderCount field.

    6. Click Create.

       

16. Click Save in the upper-right corner to save the dataset.

    

17. Enter a name for the dataset: Moscow Sales dataset, then click Create.

18. When the dataset is saved, click Create chart.

    

Step 3. Create a line chart

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

1. For the visualization type, choose Line chart.

   

2. Add the sales date to the chart. Drag the OrderDate field from the Dimensions section to the X section.

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

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

   

5. 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.

   

6. Save the chart.

   1. Click Save in the upper-right corner to save the chart.

      

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

Step 4. Create a column chart

To visualize sales by brands and product categories, create a chart: column chart.

1. Copy the chart from the previous step.

   1. Click the down arrow next to the Save button in the upper-right corner.

   2. Choose Save as.

      

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

2. For the visualization type, choose Column chart.

   

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

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

   

5. Replace payment types for product categories in the Colors section. Drag ProductCategory from the Dimensions to the Colors section and hold it over the PaymentType field until it turns red.

   

6. Sort the chart in descending order of sales measure. Drag the Sales field from the Measures section to the Sorting section.

   

7. Save the chart.

Step 5. Create a pivot table chart

To visualize sales in terms of products and time, create a chart: pivot table.

1. Copy the chart from the previous step.

   1. Click the down arrow next to the Save button in the upper-right corner.
   2. Click Save as.
   3. In the window that opens, enter the name Sales by year and product for the new chart.
   4. Click Save.

2. Choose the Pivot table visualization type.

   

3. The ProductBrand and Sales fields are automatically copied to the Columns and Measures sections, respectively.

   

4. Remove ProductBrand from the table.

5. Add the order date to the table. To do this, drag the OrderDate field from the Dimensions section 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, select Date part ⟶ Year and click Apply.

   

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

   

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

   

9. Save the chart.

Step 6. Create a heat map chart

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

1. Copy the chart from the previous step.

   1. Click the icon next to the Save button in the upper-right corner and select Save as.
   2. In the window that opens, enter the name Sales heat map for the new chart.
   3. Click Save.

2. Select the Map visualization type.

   

3. Select the Points (heat map) layer type.

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

   

5. Save the chart.

Step 7. Create a dashboard

Create a dashboard to add your charts to.

1. Go to the DataLens main page.

2. Click Create dashboard.

   

3. Enter the name Moscow Shops dashboard for the dashboard and click Create.

Step 8. Add charts to the dashboard

1. The first time you open the dashboard after saving, it opens in edit mode. If you open it later, click Edit in the upper-right corner.

   

2. Click Add and select Chart.

   

3. In the window that opens, click Select.

4. Select the chart Sales dynamics by month and payment type. This automatically fills in the Title field with the name of the selected chart.

5. Click Add.

   

6. Similarly, add the following charts:

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

7. Position the charts on the dashboard however you like.

   

Step 9. Add selectors to the dashboard

Add selectors to filter the charts by date, Moscow districts, products, and customer statuses.

1. Click Add.

2. Choose Selector.

   

3. Add the calendar selector for the order date.

   1. Select the Moscow Sales dataset.
   2. Select the OrderDate field.
   3. This automatically fills in Title with the name of the selected field. Click the Show checkbox next to the selector title.
   4. Select the Calendar type.
   5. Enable Range.
   6. Click Add.

   

4. Add a product category selector.

   1. Select the Moscow Sales dataset.
   2. Select the ProductCategory field.
   3. This automatically fills in Title with the name of the selected field. Click the Show checkbox next to the selector title.
   4. Enable the Multiple choice option.
   5. Click Add.

   

5. Similarly, add selectors for the following fields:

   • ProductBrand
   • DeliveryDistrictName
   • DeliveryType
   • PaymentType

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

7. Save the dashboard.

   

8. Your dashboard is ready. Now you can filter charts using selectors.