Get started
Yandex DataLens

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

Written by

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'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. 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 these resources, delete them.

Before you begin

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 organization for working in Yandex Cloud.
  3. Go to the default organization community or create a new community.
  4. Link your billing account to the DataSphere community you are going to work in. Make sure that the billing account is linked and its status is ACTIVE or TRIAL_ACTIVE. If you don't have a billing account, create one in the DataSphere interface.

Tip

To make sure Yandex DataLens and Yandex DataSphere can run within the Yandex Cloud network, create their instances in the same organization.

The cost of the infrastructure deployment includes:

1. Connect ClickHouse and DataSphere

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 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 Hosts, click . Enable Public access 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:

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

    7. Click Create cluster.

1.2. Connect DataSphere

  1. Open the [(https://datasphere.yandex.com/) homepage]DataSphere.

  2. In the window that opens, click Create → Create project.

  3. Give your project a name. Naming conventions:

    • The length can be from 3 to 63 characters.
    • It may contain lowercase Latin letters, numbers, and hyphens.
    • The first character must be a letter. The last character can't be a hyphen.

  4. Specify a community for the project and click Сreate.

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

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 application:

    1. Go to https://oauth.yandex.ru/client/new.
    2. Give the service a name.
    3. Go to Which platform is the app required for?. → Web services. In the Callback URL field, insert https://oauth.yandex.com/verification_code.
    4. Go to What data do you need? → 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. 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 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 working area.

  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.

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

Note

Skip this section if you are using your own tag data.

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

2.4. ClickHouse. Get the cluster's IP address

  1. In the management console, 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, copy the host name.

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

3.1. Connect to DataLens

  1. In the management console, open the page of the ClickHouse cluster you created.
  2. In the list on the left, select DataLens.
  3. In the window that opens, select the folder with your DataLens, then click Activate.

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. 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 password and click Check connection.
    5. 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 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 calculate the number of hits, create a calculated field: in Field, enter Hits. In the workspace, enter 1 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 upper-right corner, click Save.
  9. Name the dataset ch_metrica_data_hits and click Create.

3.4. Create a stacked area chart

  1. In the upper-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 chart

  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 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. In the panel on the left, select Dashboards and click Create issue.
  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 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.
  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. Set up a dashboard

  1. Add filtering to select a specific browser. To do this, in the top right corner, click Edit → 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. 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 Show.
  7. Click Add.
  8. Drag the selector to the top of the dashboard and stretch it horizontally.
  9. In the upper-right corner, click Save.

5. Build conversion funnels

5.1. DataSphere. Build funnels

  1. Open the [(https://datasphere.yandex.com/) homepage]DataSphere.
  2. Open the notebook 3. funnels.ipynb. Specify the host, the user, and the DB name.
  3. 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 (or click DataLens in the left 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 table metrica_data.funnels_by_bro to the editing area.
  5. Open the Fields tab:
    1. Rename the fields step X 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 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 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.
  4. Select the connection 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 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 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 Add.
  3. From the list, select the connection you created.
  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, 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.
  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: June 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 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.
  4. 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.
  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. In the top right-hand corner, 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 the retention rate 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. 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's 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 tab

  1. In the panel on the left, 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 chart ch_metrica_data_users_visits_cohorts_abs 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 chart ch_metrica_data_users_visits_cohorts_rel.
    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 chart ch_metrica_data_users_visits_cohorts_abs. You can open the chart from the dashboard or find it in the chart list.

  1. In the top right corner, click Save.
  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 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 ch_metrica_data_users_visits_cohorts_rel chart:

  1. In the top right corner, click Save.
  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. In the top right corner, click Save.
  7. Arrange the charts side by side.

How to delete created resources

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

In this article: