AppMetrica: data export, post-processing, and visualization

In this tutorial, you will analyze user behavior in a mobile app based on AppMetrica data:

• Process the data using Python scripts in Jupyter Notebooks in Yandex DataSphere.
• Build charts and dashboards in Yandex DataLens.
• Compare products by reach and frequency.

Customer journey is a sequence of user actions. User behavior analysis helps you find out how people use your product: pages they visit, functions they use, and issues they face. This information makes it easier for you to find the right solutions to develop your product.

As a data source, we will use sampled and anonymized data from the auto.ru mobile app, exported from AppMetrica.

Data architecture scheme

First get your cloud ready and then explore and visualize the data step-by-step:

1. Connect ClickHouse® and DataSphere.
   1. Connect ClickHouse®.
   2. Connect DataSphere.
   3. Clone the repository to DataSphere.
2. Get and upload data to ClickHouse®.
   1. DataSphere. Export the test app data via Yandex Disk.
   2. Export the data from AppMetrica.
   3. ClickHouse®. Get the cluster address.
   4. DataSphere. Upload the data to ClickHouse®.
3. DataSphere. Compare products by reach and frequency.
4. 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 scatter chart.
   5. Create a table chart.
5. Create and configure a dashboard in DataLens.
6. Customer journey. Create a QL chart and a Sankey chart.
   1. Create a QL chart in DataLens.
   2. Create a Sankey chart in DataSphere.

Get your cloud readyGet your cloud ready

Sign up for Yandex Cloud and create a billing account:

1. Navigate to the management console and log in to Yandex Cloud or create a new account.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one and link a cloud to it.

If you have an active billing account, you can create or select a folder for your infrastructure on the cloud page.

Learn more about clouds and folders here.

1. Connect ClickHouse® and DataSphere1. Connect ClickHouse® and DataSphere

1.1. Connect ClickHouse®1.1. Connect ClickHouse®

1. Navigate to the management console.

2. Go to Managed Service for ClickHouse.

3. Click Create cluster.

4. Configure your ClickHouse® cluster.

   1. Basic parameters: Enter appmetrica_analysis as the cluster name.

   2. Host class: Select burstable as the virtual machine type and b2.medium as the 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. Storage size: Leave 10 GB.

   4. Database: Specify the database name (autoru_appmetrica), username, and password. Memorize these credentials.

      

   5. Hosts: Click the icon. Enable Public access and click Save.

   6. Advanced settings: Enable these four options:

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

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

1.2. Connect DataSphere1.2. Connect DataSphere

1. Navigate to the management console.
2. Go to DataSphere.
3. Click Create project.
4. Specify appmetrica-analysis as the project name and click Create.
5. Open your project. To do this, in the row with the project name, click → Open.

1.3. Clone the repository to DataSphere1.3. Clone the repository to DataSphere

1. In the top-left corner, click the Git Clone icon: .

   

2. In the window that opens, specify the repository URI, https://github.com/firstsvet/yandex_appmetrika_cloud_case, and click CLONE.

2. Get and upload data to ClickHouse®2. Get and upload data to ClickHouse®

If you do not have a Yandex Metrica app, it has not accumulated enough data, or if you want to make sure that you will get a result by completing all the tutorial steps, go to step 2.1 (skip step 2.2).

If you have an AppMetrica app and access to it, go to step 2.2. We recommend this for experienced users as you might need to edit scripts (skip step 2.1).

2.1. DataSphere. Download the test app data via Yandex Disk2.1. DataSphere. Download the test app data via Yandex Disk

1. In the left-hand menu, open the yandex_appmetrika_cloud_case folder → 1.upload_data_from_yadisk.ipynb notebook.

2. Complete all the steps, i.e., run cells with code, in the 1.upload_data_from_yadisk.ipynb notebook.

   To run a step, click the number to the left of the cell, then the run button at the top. The number will change to [*]. After the number appears again, run the next step.

   

2.2. Export the data from AppMetrica2.2. Export the data from AppMetrica

To set up the connection and export data from your app, see the guide in Exporting data in Yandex Cloud.

2.3. ClickHouse®. Get the cluster address2.3. ClickHouse®. Get the cluster address

1. Go to the appmetrica_analysis ClickHouse® cluster you created in step 1.1. Wait for the cluster status to change to Alive. Then open the cluster by clicking it.

   

2. Select Hosts from the list on the left.

3. On the Overview tab, go to the Host name column. To copy a host name, point to the right of the host name and click the copy icon.

2.4. DataSphere. Upload the data to ClickHouse®2.4. DataSphere. Upload the data to ClickHouse®

1. Open the yandex_appmetrika_cloud_case folder → 2.upload_data_to_ClickHouse®.ipynb notebook.

2. Insert the data into the variables:

   • Host name from step 2.3: Into the CH_HOST_NAME variable.

   • Username from step 1.1: Into the CH_USER variable.

   • Database name from step 1.1: Into the CH_DB_NAME variable.

     

3. In the yandex_appmetrika_cloud_case folder, create a new text file named chpass.txt.

   

4. Specify the password of the logged-in user in the chpass.txt file. Save and close the file.

5. Complete all the steps, i.e., run the cells with code, in the notebook.

3. DataSphere. Compare products by reach and frequency3. DataSphere. Compare products by reach and frequency

1. Open the yandex_appmetrika_cloud_case folder → Case_1.ipynb notebook.

2. Insert the data into the variables:

   • Host name from step 2.3: Into the CH_HOST_NAME variable.
   • Username from step 1.1: Into the CH_USER variable.
   • Database name from step 1.1: Into the CH_DB_NAME variable.

3. Complete all the steps, i.e., run the cells with code, in the notebook.

4. View the intermediate results.

   

4. Connect DataLens and create charts4. Connect DataLens and create charts

4.1. Connect to DataLens4.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.

4.2. Create a connection to ClickHouse® in DataLens4.2. Create a connection to ClickHouse® in DataLens

1. Set up your connection:

   1. Enter AppMetrica_workshop for the name.

   2. Select the ClickHouse® host from the Host name drop-down list.

   3. Select the username and enter the password from step 1.1.

   4. Enable Allow subqueries in datasets and queries from charts.

      

   5. Click Check connection.

2. After a successful check, click Create connection. In the window that opens, enter the connection name and click Create.

4.3. Create a dataset based on the connection4.3. Create a dataset based on the connection

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

2. Select the autoru_appmetrica.auto_data table as the source by dragging the table from the list on the left to the editing area.

3. Open the Fields tab.

4. Create the users calculated field:

   1. In the top-right corner, click Add field.
   2. At the top left, enter users for the field name.
   3. Paste the countd([appmetrica_device_id]) formula in the area on the right.
   4. Click Create.

   

5. Repeat the previous step for other fields:

   • Field name: reach, formula: COUNTD([appmetrica_device_id])/COUNTD([appmetrica_device_id] FIXED).
   • Field name: events, formula: COUNT([session_id]).
   • Field name: events per user, formula: [events]/[users].

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

7. Name the dataset autoru_backend_data and click Create.

4.4. Create a scatter chart4.4. Create a scatter chart

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

2. Select Scatter chart as the type.

3. Drag these fields to the chart section:

   • reach measure to the X section.
   • events per user measure to the Y section.
   • mark dimension to the Points section.
   • event_name dimension to the Colors section.

   

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

5. In the window that opens, enter Coverage and events as the chart name and click Save.

4.5. Create a table chart4.5. Create a table chart

1. Select the Table type.

2. Drag these fields to the chart section:

   • mark measure to the Columns section.
   • users measure to the Columns section.
   • users measure to the Sorting section.

   

3. In the top-right corner, click the button to the right of Save, then click Save as.

4. In the window that opens, enter Table by car brands as the chart name, then click Save.

5. Create and configure a dashboard in DataLens5. Create and configure a dashboard in DataLens

1. Open the DataLens home page.

2. In the left-hand panel, click Dashboards.

3. Click Create dashboard.

4. Add a chart to your dashboard.

   1. In the top-right corner, click Add → Chart.
   2. From the Chart drop-down list, select Table by car brands. The Name field will be populated automatically.
   3. Click Add.

   

5. Repeat the previous step for the Coverage and events chart.

6. Add and configure a selector.

   1. In the top-right corner, click Add → Selector.
   2. In the Dataset list, select autoru_backend_data.
   3. In the Field list, select event name.
   4. In the Default value list, select any option.
   5. Click Add.

7. Position the charts and selector on the dashboard. To resize an element, drag it by the bottom-right corner.

8. Save the dashboard:

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

   

Try to change event name in the selector to another value to see how the dashboard changes.

6. Customer journey. Create a QL chart and a Sankey chart6. Customer journey. Create a QL chart and a Sankey chart

6.1. Create a QL chart in DataLens6.1. Create a QL chart in DataLens

Use QL charts to delve into event sequences and experiment in DataLens.

1. Open the DataLens home page and select Connections in the left-hand menu.

2. Select the AppMetrica_workshop connection you created in step 4.2.

3. At the top right, click Create QL chart.

4. Enter this query:

   SELECT uniqExact(t.appmetrica_device_id) as counts, events_seq, 
   
   if(events_seq like '%Call%', 'Call', 
   if(events_seq like '%Message%', 'Message', 'Contact failed')) as contact
   
   FROM (
   
   SELECT
       appmetrica_device_id,
       num_steps,
       arrayStringConcat(filt_events, ' -> ') as events_seq
   FROM
       (SELECT
           appmetrica_device_id,
           groupArray(event_name) as events,
           count(event_name) as cnt_events,
           groupArray(datetime) as times,
           arrayEnumerate(events) as indexes,
           arrayDifference(arrayMap(x -> toUInt64(x), times)) as times_diffs,
           arrayFilter(e, i -> (i = 1) or (events[i - 1] != events[i]) or (times_diffs[i] >= 1800),
                       events, indexes) as filt_events,
           length(filt_events) as num_steps
       FROM 
           (SELECT
               appmetrica_device_id,
               datetime,
               event_name
           FROM autoru_appmetrica.raw_appmetrica_auto_data
           ORDER BY appmetrica_device_id,
               datetime)
       GROUP BY appmetrica_device_id
       HAVING cnt_events <= 30)) as t
   
   where  t.num_steps<10
   
   GROUP BY t.events_seq
   HAVING counts>10
   ORDER BY counts desc
   

5. Click Start.

6. Select the Bar chart type.

   

7. In the top-right corner, click Save, then enter the chart name: Event chains.

8. Add the QL chart to the dashboard.

   1. In the left-hand menu, click Dashboards.
   2. Select the auto.ru app dashboard from the list.
   3. Click Edit at the top right.
   4. Click Add → Chart.
   5. In the Chart list, select Event chains and click Add.
   6. Customize the dashboard layout and click Save.

   

6.2. Create a Sankey chart in DataSphere6.2. Create a Sankey chart in DataSphere

1. Navigate to the management console.

2. Go to DataSphere.

3. Open the yandex_appmetrika_cloud_case folder → Case_2.ipynb notebook.

4. Complete all the steps, i.e., run the cells with code, in the Case_2.ipynb notebook.

5. You will get an interactive Sankey chart that shows user behavior scenarios. You can move the chart blocks and save the result as an image.

   

ClickHouse® is a registered trademark of ClickHouse, Inc.