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. Upload data via the Yandex Metrica Logs API.
   3. DataSphere. Download the test tag data via Yandex Disk.
   4. ClickHouse®. Get the cluster 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. 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 your dashboard.
   5. DataLens. Funnels by browser. Set up a dashboard.
6. Perform cohort analysis:
   1. DataSphere. Perform 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.

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

Getting startedGetting 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 in 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 that you have a billing account linked 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 resourcesRequired 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 DataSphere1. Connect ClickHouse® and DataSphere

1.1. Connect ClickHouse®1.1. Connect 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 the Intel Cascade Lake platform, the burstable type, and the b2.medium host type.

      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 value of 10 GB.

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

   5. Under DBMS settings, disable user management via SQL, enter the 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. Connect DataSphere1.2. Connect 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 and description (optional) for the project. The naming requirements are as follows:

   • The name must be from 3 to 63 characters long.
   • It may contain lowercase Latin letters, numbers, and hyphens.
   • The first character must be a letter and the last character cannot be 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 DataSphere1.3. Clone the repository to DataSphere

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

2. Retrieve and upload data to ClickHouse®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 token2.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, enter https://oauth.yandex.com/verification_code.
   4. Under Access to data, enter metrika and select Access to statistics and ability to view all counter settings (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. Click https://oauth.yandex.ru/authorize?response_type=token&client_id=<app_ID>. Paste the ClientID of your app as <app_ID>.

4. Click Log in as.

5. Save the received access token.

2.2. DataSphere. Upload data via the Yandex Metrica Logs API2.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 obtained access token to the file contents. Save the changes and close the file.

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

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

5. Enter the start date for the analyzed period in the START_DATE variable.

6. Enter the end date for the analyzed period in the END_DATE variable.

   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 Disk2.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 the code) in the 1b. get_data_via_yadisk.ipynb notebook.

2.4. ClickHouse®. Get the cluster IP address2.4. ClickHouse®. Get the cluster IP address

1. In the management console, go to the created ClickHouse® cluster. 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®2.5. DataSphere. Upload the data to ClickHouse®

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

   1. Paste the copied host name into the CH_HOST_NAME variable.
   2. In the CH_USER variable, insert the name of the user 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, paste 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 charts3. Connect DataLens and create charts

3.1. Connect to DataLens3.1. Connect to DataLens

1. In the management console, open the page of the ClickHouse® cluster you created.
2. On the left side of the window, select DataLens.
3. Click Create connection.

3.2. Create a connection to ClickHouse® in DataLens3.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 connection3.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 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 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 a stacked area chart3.4. Create a stacked area chart

1. In the top-right corner, click Create chart.
2. In the window that opens, drag the following fields to the chart section:
   • EventDate, to the X section.
   • Browser, to the Colors section.
   • Hits, to the Y section.
3. Change the chart type from Column chart to Stacked 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 chart3.5. Create a pivot table chart

1. In the top-right corner, click → Save as copy.
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 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 DataLens4. Create and configure a dashboard in DataLens

4.1. Create a dashboard4.1. Create a dashboard

1. In the left-hand panel, select Dashboards 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 ch_metrica_data_hits_area.
   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 as the dashboard name and click Create.

4.2. Set up a dashboard4.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 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. 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 funnels5. Build conversion funnels

5.1. DataSphere. Build funnels5.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, where funnels are calculated by browser.

5.2. DataLens. Funnels by browser. Create a dataset5.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 the Connections section 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. Specify 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 chart5.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. 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 dashboard5.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 dashboard5.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 analysis6. Perform cohort analysis

6.1. DataSphere. Perform cohort analysis6.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 required to render visualization in DataLens.

6.2. DataLens. Create a dataset and a chart with cohort visualization6.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 home page 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 to the workspace to connect to it.
5. Open the Fields tab and create a calculated field named week_num, which is 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 visualization6.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: June 06, 2020
      • End date: September 27, 2020
   2. Click Apply filter.
2. Format the numbers in the values of the week_num field by removing the decimal places. To do this, in the Rows section, in the week_num field, click the icon. In the window that opens, set the following configuration:
   1. Set the Decimal place measure 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 the 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.
4. Click Save.
5. Name the chart ch_metrica_data_users_visits_cohorts_abs and click Save.

6.4. DataLens. Create a chart with retention6.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 name of the chart 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 the 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 colors. Under Colors, click the icon. In the window that opens, enable Set threshold values, then 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 tab6.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 charts6.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 the icon. 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 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 dashboard6.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 createdHow 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.