Web analytics with funnels and cohorts calculated based on Yandex Metrica data

Yandex Metrica data is used as the data source.
In this scenario, you'll learn how to build conversion funnels, conduct a cohort analysis, and calculate the retention of the user base.

This scenario is also available as a recording from the Yandex Cloud YouTube channel.

To visualize and explore data, set up your cloud, then follow the steps below:

1. Connect ClickHouse and DataSphere
   1. Connect ClickHouse
   2. Connect DataSphere
   3. Clone the repository to DataSphere
2. Retrieve and upload data to ClickHouse
   1. Yandex Metrica Create an app and get an access token
   2. DataSphere. Upload data via the Yandex Metrica Logs API
   3. DataSphere. Download test tag data via Yandex Disk
   4. ClickHouse. Get the cluster's IP address
   5. DataSphere. Upload the data to ClickHouse
3. Connect DataLens and create charts
   1. Connect to DataLens
   2. Create a connection to ClickHouse in DataLens
   3. Create a dataset based on the connection
   4. Create a stacked area chart
   5. Create a pivot table chart
4. Create and configure a dashboard in DataLens
   1. Create a dashboard
   2. Set up a dashboard
5. Funnels
   1. DataSphere. Build funnels
   2. DataLens. Funnels by browser. Create a dataset
   3. DataLens. Funnels by browser. Create a chart
   4. DataLens. Funnels by browser. Add a chart to your dashboard
   5. DataLens. Funnels by browser. Set up a dashboard
6. Cohorts
   1. DataSphere. Perform a Cohort analysis
   2. DataLens. Create a dataset and a chart with cohort visualization
   3. DataLens. Configure a chart with cohort visualization
   4. DataLens. Create a chart with retention
   5. DataLens. Add charts to a new dashboard tab
   6. DataLens. Create charts
   7. DataLens. Add charts to the dashboard

Before you start

Before working, you need to register in Yandex Cloud and create a billing account:

1. Go to the management console. Then log in to Yandex Cloud or sign up if don't already have an account.
2. On the billing page, make sure you linked a billing account, and it has the ACTIVE or TRIAL_ACTIVE status. If you don't have a billing account, create one.

If you have an active billing account, you can create or select a folder to run your VM in from the Yandex Cloud page.

Learn more about clouds and folders.

1. Connect ClickHouse and DataSphere

1.1 Connect ClickHouse

1. In the management console, select Managed Service for ClickHouse from the list on the left.
2. In the window that opens, click Create cluster.
3. Specify the settings for a ClickHouse cluster.

   1. Under Basic parameters, specify a name for the cluster.

   2. Under Host class, select the burstable VM type and b2.medium host type.

      Warning

      We don't recommend using burstable VM configurations in production environments. This tutorial uses them as an example. For production solutions, use standard or memory-optimized configurations.

      

   3. Under Storage size, keep the value of 10 GB.

   4. Under Database, specify the database name, for example, metrica_data, the username, and the password. Remember the database name.

      

   5. Under Hosts, click . Enable Public access and click Save.

   6. Under Additional settings, enable 4 options:

      • DataLens access
      • Access from the management console
      • Access from Yandex Metrica and AppMetrica
      • Serverless access

   7. After configuring all the settings, click Create cluster.

1.2 Connect DataSphere

1. Go to the management console.
2. Select DataSphere from the list on the left.
3. In the window that opens, click Create project.
4. Specify a name for the project and click Сreate.
5. Open the project. To do this, in the line with the project name, click → Open.

Now we see the JupyterLab development environment, where we'll continue to work.

1.3 Clone the repository to DataSphere

1. In the upper-left corner, click Git Clone.
2. In the window that opens, specify the URI of the repository https://github.com/zhdanchik/yandex_metrika_cloud_case.git and click CLONE.

2. Retrieve and upload data to ClickHouse

If you don't have a Yandex Metrica tag, it doesn't have enough data, or if you want to make sure and complete all the steps in the instructions and get a result, go to step 2.3 (skip steps 2.1 and 2.2).

If you have a Yandex Metrica tag and can access it, go to step 2.1 and 2.2 (skip step 2.3). We recommend these steps if you're an experienced user because the logic of calculating funnels and cohorts depends on the data itself and you may need to tweak the scripts.

2.1 Yandex Metrica. Create an app and get an access token

1. To work with the API, get your OAuth token.

2. Create an application:

   1. Go to https://oauth.yandex.com/client/new.
   2. Specify a name for the app.
   3. Go to Platforms → Web services. In the Callback URI #1 field, insert https://oauth.yandex.com/verification_code.
   4. Go to Accesses → Yandex Metrica (metrika). Enable Access to statistics and ability to view all counter settings (metrika:read).
   5. Click Create app.
   6. A description of our application appears in the window that opens. Copy the ID of your app.

3. Click https://oauth.yandex.com/authorize?response_type=token&client_id=<app ID>. For the client_id parameter, add the copied ID of your app.

4. Click Log in as …

5. Copy the received access token.

2.2 DataSphere. Upload data via the Yandex Metrica Logs API

1. In the DataSphere project, in the root of the working directory, create a text file.

   

2. Name the file .yatoken.txt and insert the received access token into the file content. Save your changes and close the file.

3. Open the folder yandex_metrika_cloud_case → notebook 1a. get_data_via_logs_api.ipynb.

If you couldn't get data for the demo tag from the Logs API, you can download it via Yandex Disk.

2.3 DataSphere. Download the test tag data via Yandex Disk

1. Open the folder yandex_metrika_cloud_case → notebook 1b. get_data_via_yadisk.ipynb.
2. Complete all the steps (cells with the code) in the notebook 1b. get_data_via_yadisk.ipynb.

2.4 ClickHouse. Get the cluster's IP address

1. Go to the created ClickHouse cluster. Wait for the cluster status to be Alive. Then open the cluster by clicking on it.

   

2. Select Hosts from the list on the left.

3. On the Overview tab, go to the Hostname column. To copy a hostname, point to the right of the hostname and click the copy icon.

2.5 DataSphere. Upload the data to ClickHouse

1. Open the folder yandex_metrika_cloud_case → notebook 2. upload_data_to_ClickHouse.ipynb.

   1. Paste the copied hostname into the CH_HOST_NAME variable.

   2. Use the name of the logged-in user in the CH_USER variable.

   3. Use the name of the opened database in the CH_DB_NAME variable.

      

2. In the root directory, create a new text file named .chpass.txt.

   

3. Enter the password of the logged-in user in the .chpass.txt file. Save and close the file.

4. Complete all the steps (the cells with the code) in the notebook.

3. Connect DataLens and create charts

3.1 Connect to DataLens

1. On the page of the created ClickHouse cluster, on the left in the menu, select DataLens.
2. In the window that opens, click Activate.
3. In the window that opens, select the default folder and click Activate DataLens.

3.2 Create a connection to ClickHouse in DataLens

1. Click Create connection.
2. Select a ClickHouse connection.
3. Fill in the connection settings.

   1. Add a name.

   2. Select a ClickHouse host from the Hostname drop-down list.

   3. Select the username.

   4. Enter the password and click Check connection.

      

   5. After checking the connection, in the upper-right corner, click Create.

3.3 Create a dataset based on the connection

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

2. Select the metrica_data.hits table as a source. To do this, drag the table from the list on the left to the editing area.

3. Open the Fields tab.

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

5. To count the number of hits, create the calculated Hits field equal to 1. Click Create.

   

6. For the Hits field, select the Sum value in the aggregation.

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

8. Name the dataset ch_metrica_data_hits and click Create.

3.4 Create a stacked area chart

Now create charts.

1. In the upper-right corner, click Create chart.

2. In the window that opens, drag the following fields to the chart section:

   • Drag the EventDate field to the X section.
   • Drag the Browser field to the Colors section.
   • Drag the Hits field to the Y section.

3. Change the chart type to Stacked area chart.

   

4. Click Save.

   

5. In the window that appears, enter ch_metrica_data_hits_area as the chart name and click Save.

3.5 Create a pivot table chart

Create another chart based on the pre-existing chart: a pivot table.

1. In the top right-hand corner, click → Save as.
2. For the chart copy, enter ch_metrica_data_hits_table as the new name and click Save.
3. Select Pivot table as the new chart type.
4. Add or adjust the following fields in the chart area:
   • Drag the Browser field to the Rows section.
   • Drag the Hits field to the Sorting section.
5. Click Save.

4. Create and configure a dashboard in DataLens

4.1 Create a dashboard

1. Open the DataLens homepage and click Create dashboard.

2. Enter ch_metrica_data as the name of the dashboard and click Create.

3. Add the first chart to the dashboard. To do this, in the upper-right corner, click Add → Chart.

   1. From the Chart drop-down list, select ch_metrica_data_hits_area.
   2. In the Name field, enter Hits by browser as the chart name and click Add.

4. Similarly, add the chart ch_metrica_data_hits_table named Hits by browser for period.

   

5. Move the charts and resize them on the dashboard.

   1. Drag the table chart to the right of the diagram chart.
   2. To change the vertical dimensions of the charts, drag them by the lower-right corner.

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

4.2 Configure a dashboard

1. Add filtering to select a specific browser. To do this, in the upper-right corner, click Add → Selector.

2. You can add the selector to a field from any dataset. From the Dataset list, select the created dataset ch_metrica_data_hits.

   

3. From the Field list, select Browser. In the Name field, enter a name for the selector and enable Show.

4. Enable the Multiple choice option.

5. In the Default value field, select browsers:

   • android_browser
   • chrome
   • chromemobile
   • firefox
   • opera
   • safari
   • safari_mobile
   • samsung_internet
   • yandex_browser
   • yandexsearch

6. Click Add.

7. Drag the selector to the top of the dashboard and stretch it horizontally.

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

   

5. Funnels

5.1 DataSphere. Build funnels

1. Open the notebook 3. funnels.ipynb. Specify the host, the user, and the DB name.

   

2. Run the cells and evaluate the analysis results.

   

In ClickHouse, the table metrica_data.funnels_by_bro is created, where funnels by browser are calculated.

5.2 DataLens. Funnels by browser. Create a dataset

Create a new dataset based on the new table and the connection to ClickHouse.

1. Open the DataLens homepage and click Create dataset.

2. Go to the Connections section and click Add.

3. From the list of connections, select metrica_analysis.

4. Drag the new table metrica_data.funnels_by_bro to the editing area.

5. Open the Fields tab.

   1. Rename the fields step X → Step X, where X is the step sequence number.
   2. Specify the value of the Sum aggregation for the Step X fields and click Save.

   

6. Name the dataset ch_metrica_data_funnels_by_bro and click Create.

5.3 DataLens. Funnels by browser. Create a chart

Create a chart based on the dataset ch_metrica_data_funnels_by_bro.

1. Click Create chart.
2. Select the Pivot table chart type.
3. Drag the following fields to the chart section:
   • The Browser field to the Rows section.
   • The Step X field to the Measures section.
   • The Step 1 field to the Sorting section.
4. Click Save.
5. Enter ch_metrica_data_funnels_by_bro_table as the chart name and click Save.

5.4 DataLens. Funnels by browser. Add a chart to your dashboard

1. Go to the created dashboard (you can do it from the dashboards page).

2. Add a new chart. In the upper-right corner, click Edit.

3. Add the chart ch_metrica_data_funnels_by_bro_table. Enter Funnels by browser as the name and click Add.

4. Place the new chart to the right of the existing two. Stretch the chart so that it matches the others vertically and reaches the right border of the page.

5. Click Save.

   

5.5 DataLens. Funnels by browser. Set up a dashboard

Configure relationships so that the selector affects the new chart from another dataset.

1. Click Edit → Links.

2. In the window that opens, select the Browser: selector from the list.

3. On the page with the other dashboard elements, scroll down to the Funnels by browser chart, and click on the list with the link.

   1. Select the connection type Outgoing link.

   

4. From each list, select the fields for the Browser link. Click Add.

5. In the upper-right corner of the dashboard, click Save.

6. In the upper-left corner, click → Rename.

7. Enter Supermarket.ru — funnel and cohort analysis as the name. Click Done.

   

6. Cohorts

6.1 DataSphere. Cohort analysis

1. Open the notebook 4. cohorts.ipynb. Specify the host, the user, and the DB name.

   

2. Run the cells and evaluate the analysis results.

   

In ClickHouse, the table metrica_data.retention_users is created, which contains all the data necessary to render visualization in DataLens.

6.2 DataLens. Create a dataset and a chart with cohort visualization

Create a dataset based on the new table and the connection to ClickHouse.

1. Open the DataLens homepage and click Create dataset.

2. In the Connections section, click Add.

3. From the list of connections, select metrica_analysis.

4. Drag the new table metrica_data.retention_users to the work area to connect to it.

5. Open the Fields tab and create the new calculated field week_num, which is equal to ([date]-[min_date])/7.
   This field indicates the number of weeks from the user's first visit.

6. Click Create.

7. For the visits, purchases, and revenue fields, enable the Sum aggregation.

8. Rename the fields to Visits, Purchases, and Revenue, respectively.

9. Save the dataset.

   1. Name the dataset ch_metrica_data_users_visits.
   2. Click Create.

10. Create a new chart based on the dataset:

    • Change the chart type to a pivot table.
    • Drag the week_num field to the Columns section.
    • Drag the min_date field to the Rows section.
    • Drag the Visits field to the Measures section.

    

6.3 DataLens. Configure a chart with cohort visualization

Filter out incomplete weeks of June 29, 2020 and September 28, 2020.

1. Drag the min_date field to the Filters section.
2. In the min_date field, click the calendar icon.
   1. In the window that opens, select the start and end dates of the date range for filtering:
      • Start date: July 06, 2020.
      • End date: September 27, 2020.
   2. Click Apply filter.
3. Format the numbers in the values of the week_num field by removing the decimal places. To do this, in the Columns section, in the week_num field, click the grid icon. In the window that opens, set the following configuration:
   1. Set the Precision measure to 0.
   2. Set the Show delimiter to Hide.
   3. Click Apply.
4. To color the table, add the Visits field to the Colors section and click the gear icon. In the window that opens, configure the colors:
   1. Select Gradient type: 3 point.
   2. Select Color: Orange-Violet-Blue.
   3. Enable Set threshold values and specify the values 100, 1000, and 5000.
   4. Click Apply.
5. Click Save.
6. Name the chart ch_metrica_data_users_visits_cohorts_abs and click Save.

6.4 DataLens. Create a chart with retention

Create a chart with retention based on the ch_metrica_data_users_visits_cohorts_abs chart. You can open the chart from the dashboard or find it in the chart list.

1. Click Save as.
2. Enter ch_metrica_data_users_visits_cohorts_rel as the name of the chart and click Save.
3. Create a new calculated field to calculate retention relative to the first week:
   1. In the left part of the screen, click above the list of dataset fields and select Add field.
   2. Name the field Visits from the first week.
   3. Enter the following formula: SUM([Visits])/RMAX(SUM([Visits]) among [week_num]).
   4. Click Create.
4. Drag the Visits from the first week field to the Measures section.
5. Drag the Visits from the first week field to the Colors section in place of the Visits field.
6. Select the format for Visits from the first week. To do this, click the grid icon under Measures in the Visits from the first week field. In the window that opens, set the following configuration:
   1. Set Format to Percent.
   2. Click Apply.
7. Edit the threshold values for the measure's colors. Under Colors, click the gear icon. In the resulting window, specify the threshold values of 0.01, 0.025, and 0.1 and click Apply.
8. Click Save.

6.5 DataLens. Add charts to a new dashboard tab

Now return to the dashboard.

1. Click Edit → Tabs.
2. Rename the existing tab Overview + Funnels.
3. Add a new tab and name it Cohorts. Click Save.
4. Go to the new Cohort tab.
   1. Add the chart ch_metrica_data_users_visits_cohorts_abs to the dashboard.
   2. In the Name field, specify Visits by cohort (absolute).
   3. To add a new tab, click Add on the left.
      1. In the new tab, add the chart ch_metrica_data_users_visits_cohorts_rel.
      2. Enter Visits by cohort (relative) as the name.
   4. Click Add.

Now you have a chart with two switchable tabs.

6.6 DataLens. Create charts

Create a new chart based on the chart ch_metrica_data_users_visits_cohorts_abs. You can open the chart from the dashboard or find it in the chart list.

1. Click Save as.

2. Enter ch_metrica_data_users_revenue_cohorts_abs as the name of the chart and click Save.

3. Drag the Revenue field to the Measures and Colors sections on top of the Visits field.

4. In the Revenue section, click the grid icon. Change the field formatting.

   1. Select 1 decimal place.
   2. Select the Millions scale.
   3. Change the color thresholds for the new field to 500000, 1500000, and 10000000.

5. Save the chart.

   

Create another chart based on the chart ch_metrica_data_users_visits_cohorts_rel.

1. Click Save as.

2. Enter ch_metrica_data_users_revenue_cohorts_rel as the name of the chart and click Save.

3. Change the Visits from the first week field.

   1. Rename the field Revenue from the first week.
   2. Change the formula to SUM([Revenue])/RMAX(SUM([Revenue]) among [week_num]).
   3. Change the color thresholds for the new field to 0.01, 0.2, and 0.3.

4. Save the chart.

   

6.7 DataLens. Add charts to the dashboard

Add charts with cohort visualization to the dashboard.

1. Click Edit.

2. Click Add.

3. Choose Chart.

4. Select the chart ch_metrica_data_users_revenue_cohorts_abs from the chart list.

5. Enter Revenue by cohort (absolute) as the name.

6. Use the + Add button to create a new tab.

   1. In the new tab, select the chart ch_metrica_data_users_revenue_cohorts_rel from the chart list.
   2. Enter Revenue by cohort (relative) as the name.
   3. Click Save.

7. Arrange the charts side by side.