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

In this tutorial, you will learn how to build conversion funnels, run cohort analysis, calculate the Retention rate for the user base in Yandex DataSphere, and visualize the data in Yandex DataLens.

Yandex Metrica data is used as the data source.

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. Export data via the Yandex Metrica Logs API.
   3. DataSphere. Export test tag data via Yandex Disk.
   4. ClickHouse®. Get the cluster 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 an area chart.
   5. Create a pivot table chart.
4. Create and configure a dashboard in DataLens:
   1. Create a dashboard.
   2. Set up the dashboard.
5. Build conversion 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 the dashboard.
   5. DataLens. Funnels by browser. Set up the dashboard.
6. Perform cohort analysis:
   1. DataSphere. Perform cohort analysis.
   2. DataLens. Create a dataset and a chart with cohort visualization.
   3. DataLens. Set up 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.

If you no longer need the resources you created, delete them.

Getting started

Before getting started, register in Yandex Cloud, set up a community, and link your billing account to it.

1. On the DataSphere home page, click Try for free and select an account to log in with: Yandex ID or your working account with the identity federation (SSO).
2. Select the Yandex Cloud Organization organization you are going to use in Yandex Cloud.
3. Create a community.
4. Link your billing account to the DataSphere community you are going to work in. Make sure you have a linked billing account and its status is ACTIVE or TRIAL_ACTIVE. If you do not have a billing account yet, create one in the DataSphere interface.

Required paid resources

The cost of the infrastructure deployment includes:

• Fee for the cluster computing resources and storage (see Managed Service for ClickHouse® pricing).
• Fee for the computation time (see DataSphere pricing).
• Fee for the outbound traffic (see Virtual Private Cloud pricing).

1. Connect ClickHouse® and DataSphere

1.1. Enable ClickHouse®

1. In the management console, select a folder to create a ClickHouse® cluster in.
2. Select Managed Service for ClickHouse.
3. In the window that opens, click Create ClickHouse cluster.
4. Specify the settings for a ClickHouse® cluster:

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

   2. Under Resources, select Intel Cascade Lake for platform, burstable for type, and b2.medium for host class.

      Warning

      We do not 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 Size of storage, keep the 10 GB value.

   4. Under Hosts, click . Enable the Public access option and click Save.

   5. Under DBMS settings, disable user management via SQL, enter username, password, and database name, e.g., metrica_data.

   6. Under Service settings, enable the following options:

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

   7. Click Create cluster.

1.2. Enable DataSphere

1. Open the DataSphere home page.

2. In the left-hand panel, select Communities.

3. Select the community to create a project in.

4. On the community page, click Create project.

5. In the window that opens, enter a name for the project. You can also add a description as needed. The name should match the following format:

   • It must be from 2 to 63 characters long.
   • It may contain lowercase Latin letters, numbers, and hyphens.
   • It must start with a letter and cannot end with a hyphen.

6. Click Create.

7. Click Open project in JupyterLab.

This is the JupyterLab development environment, and you are going to use it to complete the next steps.

1.3. Clone the repository to DataSphere

1. In the Git menu, select Clone.
2. In the window that opens, specify the repository URI, https://github.com/zhdanchik/yandex_metrika_cloud_case.git, and click CLONE.
3. Click OK.

2. Retrieve and upload data to ClickHouse®

If you do not yet have a Yandex Metrica tag or it has not accumulated enough data, or if you want to be sure that you get a result by completing all steps in the tutorial, 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 walking through these steps if you are 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 app:

   1. Go to https://oauth.yandex.ru/client/new.
   2. Enter a name for the service.
   3. Go to Platforms → Web services. In the Redirect URI field, paste https://oauth.yandex.com/verification_code.
   4. Under Data access, enter metrika and select Get statistics, read data from your own and trusted counters (metrika:read).
   5. Click Create app.
   6. In the window that opens, a description of the application will appear. Save the ClientID of your app.

3. Go to https://oauth.yandex.ru/authorize?response_type=token&client_id=<app_ID>. Paste your app's ClientID as <app_ID>.

4. Click Log in as.

5. Save 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. To do this, click Text File in the workspace.

2. Name the file .yatoken.txt and paste the received access token to the file. Save the changes and close the file.

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

4. Specify your Yandex Metrica tag ID as the COUNTER_ID variable value. You can find your Yandex Metrica tag ID on the My tags page.

5. Specify the start date of the analyzed period as the START_DATE variable value.

6. Specify the end date of the analyzed period as the END_DATE variable value.

   Warning

   The date range will NOT include the end date. For example, to get data up to December 5, 2022, paste 2022-12-06 into the END_DATE variable.

7. Complete all steps (cells with code) in the 1a. get_data_via_logs_api.ipynb notebook.

If you could not 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 yandex_metrika_cloud_case folder → 1b. get_data_via_yadisk.ipynb notebook.
2. Complete all steps (cells with code) in the 1b. get_data_via_yadisk.ipynb notebook.

2.4. ClickHouse®. Get the cluster address

1. In the management console, go to the ClickHouse® cluster you already created. Wait until the cluster status changes to Alive. Then open the cluster by clicking it.
2. Select Hosts from the list on the left.
3. On the Overview tab, copy the host name.

2.5. DataSphere. Upload the data to ClickHouse®

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

   1. Paste the copied host name into the CH_HOST_NAME variable.
   2. In the CH_USER variable, insert the username you specified when creating your ClickHouse® cluster.
   3. In the CH_DB_NAME variable, insert the database name you specified when creating your ClickHouse® cluster.

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

3. In the .chpass.txt file, insert the user password you specified when creating your ClickHouse® cluster. 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. In the management console, open the page of the new ClickHouse® cluster.
2. On the left side of the window, select DataLens.
3. Click Create connection.

3.2. Create a connection to ClickHouse® in DataLens

1. Fill in the connection settings:

   1. Select a cluster from the Cluster drop-down list or create a new one. If the cluster is missing in the list, click Specify manually, then specify the ClickHouse® cluster name.
   2. Select a ClickHouse® host from the Host name drop-down list.
   3. Select the username.
   4. Enter the password and click Check connection.

2. When the connection check succeeds, click Create connection. In the window that opens, enter the connection name and click Create.

3.3. Create a dataset based on the connection

1. In the top-right corner, click Create dataset.
2. Select the metrica_data.hits table as the 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 top-right corner, click Add field.
5. To calculate the number of hits, create a calculated field: name it Hits, enter 1 in the workspace and click Create.
6. For the Hits field, select the Amount value in the Aggregation column.
7. Rename the Browser field to Browser.
8. In the top-right corner, click Save.
9. Name the dataset ch_metrica_data_hits and click Create.

3.4. Create an area chart

1. In the top-right corner, click Create chart.
2. In the window that opens, drag the following fields to these chart sections:
   • EventDate, to X.
   • Browser, to Colors.
   • Hits, to Y.
3. Change the chart type from Column chart to Area chart.
4. Click Save.
5. In the window that opens, enter ch_metrica_data_hits_area as the chart name and click Save.

3.5. Create a pivot table chart

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

4. Create and configure a dashboard in DataLens

4.1. Create a dashboard

1. Select Dashboards in the left-hand panel and click Create dashboard.

2. Add the first chart to the dashboard. To do this, in the top right corner, click Add → Chart:

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

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

4. 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 bottom-right corner.

5. Save the dashboard:

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

4.2. Set up a dashboard

1. Add filtering to select a specific browser. To do this, in the top-right corner, click Add → Chart.
2. You can add the selector to a field from any dataset. From the Dataset list, select the ch_metrica_data_hits dataset you created.
3. In the Field list, select Browser.
4. Enable Multiple choice.
5. In the Default value field, select browsers:
   • android_browser
   • chrome
   • chromemobile
   • firefox
   • opera
   • safari
   • safari_mobile
   • samsung_internet
   • yandex_browser
   • yandexsearch
6. In the Name field, enter a name for the selector and enable the option.
7. Click Add.
8. Drag the selector to the top of the dashboard and stretch it horizontally.
9. In the top-right corner, click Save.

5. Build conversion funnels

5.1. DataSphere. Build funnels

1. Open the DataSphere home page.
2. Open the 3. funnels.ipynb notebook. Specify the host, the user, and the DB name.
3. Run the cells and evaluate the analysis results.
   In ClickHouse®, the metrica_data.funnels_by_bro table will be created with funnels counted by browser.

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 home page (or click DataLens in the left-hand panel) and click Create dataset.
2. Go to Connections and click Add.
3. From the list of connections, select the connection name that you created in Step 3.2.
4. Drag the new metrica_data.funnels_by_bro table to the editing area.
5. Open the Fields tab:
   1. Rename the step X fields to Step X, where X is the step number.
   2. Select the Sum value in the Aggregation column 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 ch_metrica_data_funnels_by_bro dataset:

1. Click Create chart.
2. Select the Pivot table chart type.
3. Drag the fields to the chart sections:
   • Browser, to the Rows section.
   • Step X, to the Measures section, where X is the step sequence number.
   • Step 1, to the Sorting section.
4. Click Save.
5. Specify the ch_metrica_data_funnels_by_bro_table chart name and click Save.

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

1. Go to the created dashboard (from the dashboards page).
2. Add a new chart. In the top-right corner, click Edit:
   1. Click Add → Chart.
   2. From the Chart drop-down list, select the ch_metrica_data_funnels_by_bro_table chart.
   3. In the Name field, enter Funnels by browser as the chart name and click Add.
3. 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.
4. 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.
4. Select the link type: Outgoing link.
5. From each list, select the fields for the Browser link. Click Add.
6. Click Save.
7. In the top-left corner, click → Rename.
8. Enter Supermarket.ru — funnel and cohort analysis as the name. Click Done.

6. Perform cohort analysis

6.1. DataSphere. Perform cohort analysis

1. Open the 4. cohorts.ipynb notebook. Specify the host, the user, and the DB name.
2. Run the cells and evaluate the analysis results.

In ClickHouse®, the metrica_data.retention_users table will be created with all the data you need to render visualization in DataLens.

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

Create a new 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 Create dataset and then click Add.
3. From the list, select the connection you created.
4. Drag the new metrica_data.retention_users table into the workspace to connect to it.
5. Open the Fields tab and create a new calculated field named week_num equal to ([date]-[min_date])/7.
   This field will indicate the number of weeks from the user's first visit.
6. Click Create.
7. For the visits, purchases, and revenue fields, select the Sum value in the Aggregation column.
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:
    1. Change the chart type to Pivot table.
    2. Drag the week_num field to the Columns section.
    3. Drag the min_date field to the Rows section.
    4. 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.
   1. In the window that opens, select the start and end dates of the date range for filtering:
      • Start date: 29.06.2020
      • End date: 27.09.2020
   2. Click Apply filter.
2. Format the numbers in the week_num field values by removing the decimal places. To do this, click in the week_num field under Rows. In the window that opens, set the following configuration:
   1. Set Decimal places to 0.
   2. Set the Show delimiter measure to Hide.
   3. Click Apply.
3. To color the table, add the Visits field to the Colors section and click . 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 100, 1000, and 5000.
   4. Click Apply.
4. Click Save.
5. 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. In the top-right corner, click → Save as copy.
2. Enter ch_metrica_data_users_visits_cohorts_rel as the chart name and click Save.
3. Create a calculated field to calculate the retention rate relative to the first week:
   1. On the left side of the screen, click above the list of dataset fields and select Field.
   2. Name the field Visits from the first week.
   3. Paste 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 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 colors. Under Colors, click . In the window that opens, enable Set threshold values, 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

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

Now you have a chart with two switchable tabs.

6.6. DataLens. Create charts

Create a new chart 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. In the top-right corner, click → Save as copy.
2. Enter ch_metrica_data_users_revenue_cohorts_abs as the chart name 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 . Change the field formatting:
   1. Select 1 decimal place.
   2. Select the Millions, M 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 ch_metrica_data_users_visits_cohorts_rel chart:

1. In the top-right corner, click → Save as copy.
2. Enter ch_metrica_data_users_revenue_cohorts_rel as the chart name 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 → Chart.
3. Select ch_metrica_data_users_revenue_cohorts_abs from the chart list.
4. Enter Revenue by cohort (absolute) as the name.
5. Use the Add button to create a new tab:
   1. In the new tab, select ch_metrica_data_users_revenue_cohorts_rel from the chart list.
   2. Enter Revenue by cohort (relative) as the name.
   3. In the top-right corner, click Save.
6. Arrange the charts side by side.

How to delete the resources you created

To stop paying for the resources you created, delete the cluster.

ClickHouse® is a registered trademark of ClickHouse, Inc.