Analyzing sales and locations of pizzerias based on data from the ClickHouse® database and Marketplace

We will use a ClickHouse® database connection and the Companies: supply and demand product from Marketplace as a source.

In this tutorial, you will prepare a dashboard for selecting locations to open new Dodo pizzerias.

With DataLens, you will analyze locations based on real data:

• Revenue from current Dodo pizzerias.
• Search queries to pizzerias by location (Yandex data).
• Number of all pizzerias by location (Yandex data).

This use case is also available as a video tutorial on the Yandex Cloud YouTube channel.

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

1. Import geolayers from the Marketplace.
2. Create a connection.
3. Configure the dataset fields.
4. Create a bar chart.
5. Create a line chart.
6. Create an indicator chart.
7. Create a map chart.
8. Create a dashboard.
9. Add geolayers 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.

Step 1. Import geolayers from the MarketplaceStep 1. Import geolayers from the Marketplace

1. Go to DataLens Marketplace.

2. Choose the Geolayers category and the Companies: supply and demand product.

3. Click Expand in the product card that opens.

4. Save your purchase in the Purchases folder.

5. Click Open.

6. The window that opens will display the following DataLens objects: connection, dataset, chart, and dashboard. Click the yellow dashboard icon at the end of the list.

   

7. Examine the dashboard and its description at the bottom of the screen:

   • Demand is the number of search queries made by users of Yandex geoservices to the selected category of companies or services. The category is determined from the query. Local queries from mobile devices within a search radius of 3 km are included.
   • Supply is the number of existing companies in the selected category. This is based on company data in the Yandex Business Directory.

8. Select the Pizzeria category and check the map for Demand, Supply, and Demand for supply per city measures.

   

Step 2. Create a connectionStep 2. Create a connection

1. Go to DataLens.
2. In the left-hand panel, select Connections and click Create connection.
3. Select the ClickHouse connection.

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

      • Connection type: Specify manually.
      • Host name: rc1a-g1gicp3imcc0pnsg.mdb.yandexcloud.net,rc1b-vbbl6tob599tr3oi.mdb.yandexcloud.net,rc1d-qf1ven1igeihhbep.mdb.yandexcloud.net (comma-separated).
      • HTTP interface port: 8443 (default).
      • Username: datalens-marathon-2020-11-ro.
      • Password: /4b+xBF6aSCgN9wKTevYGuDjxC9IO4Fa.

   2. Check the connection and click Create connection.

   3. Enter DODO Con for the connection name and click Create.

      

Wait for the connection to be saved.

Step 3. Configure the dataset fieldsStep 3. Configure the dataset fields

1. In the window that opens, click Create dataset.

2. In the new dataset, move the marathon-2020-11.DODO_opendata table to the workspace.

   

3. Open the Fields tab.

   1. Rename the Name field to Pizzerias in the Name column.

   2. For the Coordinates field, select the geopoint type.

   3. For the Revenue field, select the Sum aggregation type in the Aggregation column.

   4. Duplicate the Pizzerias field. Click and select Duplicate.

      

   5. Rename the created field from Pizzerias to The number of pizzerias.

   6. For the The number of pizzerias field, select the Unique count aggregation type in the Aggregation column.

   7. Click Add field.

      

   8. Create a calculated field with the revenue ranking:

      • Formula: RANK([Revenue]).
      • Field name: Rank by revenue.

      This field will be used when creating charts filtered by TOP-N.

   9. Click Create.

      

   10. In the top-right corner, click Save. In the window that opens, specify the DODO dataset name and click Create.

Step 4. Create a column chartStep 4. Create a column chart

1. Click Create chart on the workspace panel of the DODO dataset.

   

2. In the window that opens, select the OpenDate dimension in the X section and The number of pizzerias in the Y section.

   

   The chart will show statistics on how many new pizzerias opened on a certain day.

3. Set up the Opening dates grouping by month.

   1. Click the calendar icon in the OpenDate field.
      1. In the Grouping list, select Rounding → Month.
      2. Click Apply.

   

4. Click Save in the top-right corner. Name the chart as Opening dynamics and click Save.

Step 5. Create a bar chartStep 5. Create a bar chart

1. Select a different chart type: Bar chart.

   

2. Add the Pizzerias dimension to the Y section. Remove the OpenDate dimension from the Y section.

3. Add the Revenue metric to the X section. Remove the The number of pizzerias metric from the X section.

4. Add the Revenue measure to the Sorting section.

   

5. Add the Rank by revenue measure to the Filters section:

   1. Select Less than or equal to from the Operation list.
   2. Enter 10 in the Value field.
   3. Click Apply filter.

6. The chart will show the Top 10 pizzerias in Russia.

7. In the top-right corner, click → Save as. Name the chart as Top 10 pizzerias and click Save.

   

Step 6. Create an indicator chartStep 6. Create an indicator chart

1. Select the Indicator chart type.

   

2. Delete the RANK by revenue measure from the Filters field.

3. Add the Revenue measure to the Measure section.

   

4. In the top-right corner, click → Save as. Name the chart as Revenue and click Save.

5. Delete the sum(Revenue) measure from the Measure section.

6. Add the The number of pizzerias measure to the Measure section.

   

7. In the top-right corner, click → Save as. Name the chart as The number of pizzerias and click Save.

Step 7. Create a map chartStep 7. Create a map chart

1. Select Map for chart type.

   

2. Add the Coordinates dimension to the Geopoints section.

3. Add the Revenue metric to the Size and the Colors sections.

   

4. Add the following to the Tooltips section:

   • Pizzerias
   • Address
   • OpenDate
   • Revenue

5. Go to the Colors section settings.

   

   1. Select the Two-color gradient type and the Yellow (shades) color.
   2. Click Apply.

6. Save the chart as Pizzerias on the map.

   

Step 8. Create a dashboardStep 8. Create a dashboard

1. Go to DataLens.

2. Click Create dashboard.

3. In the window that opens, click Add in the top-right corner and select Chart.

4. Select the chart named The number of pizzerias.

5. Click Add.

   

6. Repeat steps 4-6 for the other charts.

7. For indicator charts (The number of pizzerias and Revenue), disable the name display. To do this, disable the Show option in the Tab settings window.

   

8. To prevent charts from being created as a single column, drag each chart to any free space in the dashboard.

9. In the top-right corner, click Add → Selector.

   1. In the window that opens, select the DODO dataset.
   2. In the selector settings:
      1. Activate the Show option to display the name.
      2. Select the City field.
      3. Click Add.

   

10. Move the selector to the top. You can resize the charts if necessary.

11. Save the dashboard:

    1. In the top-right corner, click Save.
    2. Enter DODO Dashboard as the dashboard name and click Create.

12. Choose Moscow in the selector. If the map focus remains at the same location when changing the city in the filter, refresh the page in your web browser. DataLens captures the map scale and location if you change them during the current session.

    

Step 9. Add geolayers to the dashboardStep 9. Add geolayers to the dashboard

1. Make sure that you only selected Moscow in the city filter of your dashboard.

2. Click the menu icon in the top-right corner of the map.

3. Select Edit.

   

4. Click on the DODO dataset name and choose Add dataset.

5. In the drop-down list, select Datasets instead of Personal folder.

6. Select Organizations (dataset imported from Marketplace in Step 1).

   

7. Once the dataset is selected, the link setup window appears. By default, a link is established between fields with the same names.

   1. Click Add link.

   2. Add a link between the City (DODO) and City (Organizations) dataset fields and click Save.

      

8. Click Save to close the Links window.

9. In the layer section, rename Layer 1.

   1. To do this, click the layer and select .

   

   1. In the Layer settings window that opens, enter DODO as the name and click Apply.

10. Click to add another layer. Rename it to Demand for supply.

11. Change the geolayer type from Points to Polygons.

    

12. Make sure that the current dataset is Organizations rather than DODO. If the current dataset is DODO, click its name in the top-left corner and choose Organizations.

    

13. Add the Polygon dimension to the Polygons section.

14. Add the Polyogn.Type dimension to the Filters section and set it to hash7. Click Apply filter.

15. Add the Category dimension to the Layer filters section and choose Pizzeria. Click Apply filter.

16. Add the Demand for supply per city measure to the Colors section.

17. In the color settings, select the Blue-Grey-Red three-color gradient and click Apply.

    

18. Change the transparency level to 60.

    

19. Add the following to the Tooltips section:

    • Demand
    • Supply
    • Demand for supply per city
    • Top queries
    • Top companies

20. Click Save in the top-right corner.

    

21. Go back to the dashboard (it should be open on the previous tab in your browser) and refresh the web page.

22. Choose Saint Petersburg in the selector. After that, the dashboard, including the map and external geolayer, will only be filtered by this selector.

    

If the map focus remains at the same location when changing the city in the filter, refresh the page in your web browser. DataLens captures the map scale and location if you change them during the current session.

OptionalOptional

Here are some ideas you might want to work on by yourself:

1. Adding a geolayer with businesses from the Where to eat category (restaurants and fast food chains in general, not only pizzerias).
2. Adding the Audience: interests and social demographics product from the Marketplace to display the city audience data on the map in a similar way.
3. You can use the city of Voronezh as an example to study the free geolayers by Geointellect. The demo set offers calculated indexes representing the location's business potential for fast food.

ClickHouse® is a registered trademark of ClickHouse, Inc.