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

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

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

You can also watch this tutorial on the Yandex Cloud YouTube channel.

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

1. Create a workbook.
2. Import geolayers from Marketplace.
3. Review the dashboard.
4. Create a connection.
5. Create a dataset.
6. Analyze the rate of opening new pizzerias.
7. Analyze the revenue of pizzerias.
8. Calculate the total revenue and number of pizzerias.
9. Analyze order density on the map.
10. Create a dashboard.
11. 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.

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 the workbook name: Companies: supply and demand.
5. Click Create.

Import geolayers from MarketplaceImport geolayers from Marketplace

1. Go to DataLens in Marketplace.

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

3. Click Expand in the product card that opens.

4. Select the Companies: supply and demand workbook.

5. Click Deploy.

   

Review the dashboardReview the dashboard

1. Open the Companies: supply and demand dashboard.

2. Review the dashboard:

   • At the top, there are filters by city, category, and polygon type.

   • The tables gives information on demand and supply:

     • 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.
     • Demand-to-supply ratio shows the extent to which the supply meets the demand in a polygon compared to the entire city.

   • The map visualizes the distribution of supply and demand across the city.

3. Select the Pizzeria category and check the map for Demand, Supply, and Demand-to-supply ratio per city measures.

   

Create a connectionCreate a connection

1. Go to the Companies: supply and demand workbook by clicking its name in the dashboard path.

   

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

3. 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-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. Click Check connection and make sure you get a green check mark.

   3. Click Create connection.

   4. Enter the connection name:DODO Con.

   5. Click Create.

      

Wait for the connection to be saved.

Create a datasetCreate a dataset

Create a dataset based on the DODO Con connection:

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

2. Drag the marathon-2020-11.DODO_opendata table to the workspace.

   

3. Go to the Fields tab.

4. Rename the Name field to Pizzerias. To rename a field, click its current name, delete it, and enter a new one.

5. For the Coordinates field, select Geopoint in the Type column.

6. For the Revenue field, set the aggregation type to Sum. The fields with defined aggregation functions become measures and change their color to blue.

   

7. Create a measure for the number of pizzerias.

   1. Duplicate the Pizzerias field: on the right side of the row with the field, click and select Duplicate.

   2. Rename the Pizzerias (1) duplicate field to The number of pizzerias.

   3. Change the aggregation type to Number of unique.

      

8. Create a calculated field to rank pizzerias by revenue:

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

   2. At the top-left, specify the field name: Rank by revenue.

   3. In the formula field, enter RANK([Revenue]).

   4. Click Create.

      

9. Save the dataset:

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

Analyze the rate of opening new pizzeriasAnalyze the rate of opening new pizzerias

Create a bar chart to visualize the monthly rate of opening pizzerias:

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

2. Add the opening date to your chart. To do this, drag the OpenDate field from Dimensions to the X section.

3. Add the number of pizzerias as a measure to your chart. To do this, drag the The number of pizzerias field from Measures to the Y section.

   

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

4. Display the chart by month:

   1. Click the calendar icon next to the OpenDate field in the X section.

   2. In the Grouping field, select Rounding ⟶ Month and click Apply.

      

5. Save the chart:

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

   2. In the window that opens, enter Opening rate for the chart name and click Save.

      

Analyze the revenue of pizzeriasAnalyze the revenue of pizzerias

Create a bar chart to display the top 10 pizzerias by revenue:

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 Top 10 pizzerias for the new chart name and click Save.

2. For the visualization type, select Line chart.

   

3. Replace the months with pizzeria names on the Y axis. To do this, drag the Pizzerias field from Dimensions to the Y section and hold it over the OpenDate field until the latter turns red.

4. Replace the number of pizzerias with the total revenue on the X axis. To do this, drag the Revenue field from Measures to the X section and hold it over the The number of pizzerias field until the latter turns red.

5. Sort the chart by revenue in descending order. To do this, drag the Revenue field from Measures to the Sorting section. The chart now displays the total revenue by pizzeria.

6. Add a filter to get the top 10 pizzerias by revenue:

   1. Drag the Rank by revenue field from Measures to the Filters section.

   2. Select Less than or equal to from the Operation list.

   3. Set Value to 10.

      

   4. Click Apply filter.

   The chart will show the top 10 pizzerias in Russia.

   

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

Calculate the total revenue and number of pizzeriasCalculate the total revenue and number of pizzerias

Create an indicator to display the total revenue:

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 Revenue for the new chart name and click Save.

2. Select the Indicator visualization type.

   

3. Clear the filter for the top 10 pizzerias by revenue. To do this, click (the icon appears on when you hover over the field) next to the Rank by revenue field in the Filters section.

4. Drag the Revenue field from Measures to the Measure section.

5. Disable the header display in the chart settings:

   1. Click to the right of the visualization type.

   2. Set the header to Hide.

   3. Click Apply.

      

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

   

Create another indicator to display the number of pizzerias:

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 Number of pizzerias for the new chart name and click Save.

2. Replace the revenue with the number of pizzerias. To do this, drag the The number of pizzerias field from Measures to the Measure section and hold it over the Revenue field until the latter turns red.

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

   

Analyze order density on the mapAnalyze order density on the map

To visualize order density across Russia, create a map.

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 Map of pizzerias for the new chart name and click Save.

2. Select the Map visualization type.

   

3. Add the coordinates of pizzerias to the map. To do this, drag the Coordinates field from Dimensions to the Points (Geopoints) section.

4. Change the point size based on the revenue measure. To do this, drag the Revenue field from Measures to the Size section.

5. Change the point color based on the revenue measure. To do this, drag the Revenue field from Measures to the Colors section.

6. Add the following fields to the Tooltips section:

   • Pizzerias

   • Address

   • OpenDate

   • Revenue

     

7. Update the Colors settings:

   1. Click in the Colors section (the icon appears when you hover over the section).

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

   3. Click Apply.

      

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

   

Create a dashboardCreate a dashboard

1. Go to the Companies: supply and demand workbook by clicking its name in the dashboard path.

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

3. In the panel at the bottom of the page, select Chart.

   

4. In the window that opens, click Select.

5. Select the Opening rate chart. This will automatically fill in the Name field.

6. Click Add.

   

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

   • Top 10 pizzerias.
   • Revenue.
   • Number of pizzerias.
   • Map of pizzerias.

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

   

Add a selector to filter charts by city:

1. In the panel at the bottom of the page, choose Selector.

   

2. Add the calendar selector for the order date:

   1. Select DODO.

   2. Select the City field.

   3. Specify the header: City.

   4. Click Save.

      

3. Drag the selector to the top of the dashboard.

4. Save the dashboard:

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

Your dashboard is ready.

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

Add geolayers to the dashboardAdd geolayers to the dashboard

1. Make sure you only selected Moscow in the city filter of your dashboard. Click → Edit in the top-right corner of the map.

   

2. Click the DODO dataset name.

3. Click Add dataset.

4. Select the Companies dataset imported from Marketplace.

   

5. 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 fields in the DODO and Companies datasets and click Save.

      

6. Click Save to close the Links window.

7. Rename Layer 1:

   1. Click the layer and then (the icon appears when you hover over the layer) to the right of its name.

      

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

8. Add another layer:

   1. Click to the right of the current layer.
   2. Rename the new layer to Demand-to-supply ratio.
   3. Change the geolayer type from Points (Geopoints) to Polygons (Geopolygons).

   

9. Make sure your current dataset is Companies, not DODO. If the current dataset is DODO, click its name in the top-left corner and select Companies.

   

10. Drag the Polygon dimension to the Polygons section.

11. Add the Polygon.Type dimension to the Filters section and set it to hash7. Click Apply filter.

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

13. Add the Demand-to-supply ratio per city measure to the Colors section.

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

    

15. Change the transparency level to 60.

    

16. Add the following fields to the Tooltips section:

    • Demand.

    • Supply.

    • Demand-to-supply ratio per city.

    • Top queries.

    • Top companies.

      

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

18. Go back to the dashboard opened in the previous tab in your browser and refresh the page.

19. Choose Saint Petersburg in the selector. This will filter the entire dashboard, including the map and external geolayer, by this selector.

    

If the map focus remains at the same location when changing the city in the filter, refresh the page in your 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 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.