Use MaxCompute to build an offline computing data model and connect to Quick BI for funnel analysis - MaxCompute

This topic shows how to use MaxCompute for offline computing and Quick BI to create a funnel model for an e-commerce scenario.

Background information

A funnel model uses conversion rates to measure product performance. It helps identify where users drop off by tracking conversions at each stage, which allows for continuous product optimization. In e-commerce, this model illustrates the user's purchasing journey, from browsing a product to completing an order. This topic demonstrates how to perform a funnel analysis on this journey and visualize the results.

Prerequisites

You have activated Simple Log Service. For more information, see Simple Log Service purchase guide.
You have activated MaxCompute and created a MaxCompute project. For more information, see Activate MaxCompute and Create a MaxCompute project.
You have activated DataWorks. For more information, see DataWorks purchase guide.
You have activated Quick BI. For more information, see Quick BI purchase guide.

Procedure

Collect log data with Simple Log Service.
For more information about collecting log data with Simple Log Service, see Data collection overview. This topic uses sample data. To download the sample data, click TestData.

Build an offline computing data model using MaxCompute.

In DataWorks DataStudio, run the following statement to create an operational data store (ODS) table named ods_user_trace_data.

-- Partition the table by day based on the dt column.
CREATE TABLE IF NOT EXISTS ods_user_trace_data
(
    md5                     STRING COMMENT 'The first eight characters of the MD5 hash of the user ID (UID).',
    uid                     STRING COMMENT 'User ID (UID).',
    ts                      BIGINT COMMENT 'The timestamp of the user operation.',
    ip                      STRING COMMENT 'IP address.',
    status                  BIGINT COMMENT 'The status code returned by the server.',
    bytes                   BIGINT COMMENT 'The number of bytes returned to the client.',
    device_brand            STRING COMMENT 'Device brand.',
    system_type             STRING COMMENT 'Operating system, such as Android, iOS, iPad, or Windows Phone.',
    customize_event         STRING COMMENT 'Custom event, such as logon, logoff, purchase, register, click, background, switch user, browse, or comment.',
    use_time                BIGINT COMMENT 'The duration of a single app session. This field is recorded for logoff, background, and switch user events.',
    customize_event_content STRING COMMENT 'Information about the content that the user interacted with. This column is included for browse and comment events.'
) 
PARTITIONED BY
(
    dt STRING  
);

Note

The fields in the statement are based on the sample data. For more information about creating a table in DataWorks, see Develop ODPS SQL tasks.
For more information about ODS, see operational data store (ODS).

Run the following command to add a partition to the ods_user_trace_data table.
```
ALTER TABLE ods_user_trace_data ADD PARTITION (dt=${bdp.system.bizdate});
```
Migrate the collected log data to MaxCompute.
For more information about migrating collected log data to MaxCompute, see Migrate log data to MaxCompute.

In DataWorks DataStudio, run the following statement to create a data warehouse detail (DWD) table named dw_user_trace_data.

-- Partition the table by day based on the dt column.
CREATE TABLE IF NOT EXISTS dw_user_trace_data
(
    uid                     STRING COMMENT 'User ID (UID).',
    device_brand            STRING COMMENT 'Device brand.',
    system_type             STRING COMMENT 'Operating system, such as Android, iOS, iPad, or Windows Phone.',
    customize_event         STRING COMMENT 'Custom event, such as logon, logoff, purchase, register, click, background, switch user, browse, or comment.',
    use_time                BIGINT COMMENT 'The duration of a single app session. This field is recorded for logoff, background, and switch user events.',
    customize_event_content STRING COMMENT 'Information about the content that the user interacted with. This column is included for browse and comment events.'
) 
PARTITIONED BY
(
    dt STRING 
);

Note

For more information about DWD, see data warehouse detail (DWD).

Run the following command to insert data into the dw_user_trace_data table.

INSERT INTO dw_user_trace_data PARTITION (dt = '${bdp.system.bizdate}')
SELECT  uid
        ,device_brand
        ,system_type
        ,customize_event
        ,use_time
        ,customize_event_content
FROM    ods_user_trace_data
WHERE   dt = '${bdp.system.bizdate}'
;

In DataWorks DataStudio, run the following statement to create an application data service (ADS) table named rpt_user_trace_data.

 -- Partition the table by day based on the dt column.
CREATE TABLE IF NOT EXISTS rpt_user_trace_data
(
    browse      STRING COMMENT 'Page views',
    click       STRING COMMENT 'Clicks',
    purchase    STRING COMMENT 'Purchase quantity',
    browse_rate STRING COMMENT 'Click conversion rate',
    click_rate  STRING COMMENT 'Purchase conversion rate'
) 
PARTITIONED BY
(
    dt STRING 
);

Note

For more information about ADS, see data warehouse layering.

In DataWorks DataStudio, run the following SQL statement to write the business logic.

INSERT OVERWRITE TABLE rpt_user_trace_data PARTITION (dt=${bdp.system.bizdate})
SELECT browse AS "Page views"
      ,click AS "Clicks"
      ,purchase AS "Purchase quantity"
      ,concat(round((click/browse)*100,2),'%') AS "Click conversion rate"
      ,concat(round((purchase/click)*100,2),'%') AS "Purchase conversion rate" 
FROM
(SELECT dt,count(1) browse FROM dw_user_trace_data WHERE customize_event='browse' 
 AND dt = ${bdp.system.bizdate} GROUP BY dt) a
LEFT JOIN
(SELECT dt,count(1) click FROM dw_user_trace_data WHERE customize_event='click' 
 AND dt = ${bdp.system.bizdate} GROUP BY dt) b
ON a.dt=b.dt
LEFT JOIN
(SELECT dt,count(1) purchase FROM dw_user_trace_data WHERE customize_event='purchase' 
AND dt = ${bdp.system.bizdate} GROUP BY dt)c 
ON  a.dt=c.dt 
;

Note

The user journey is browse -> click -> purchase. Each step's conversion rate is the ratio of users who continue to the next step. For example, the click conversion rate is the number of users who click divided by the number of users who browse.

Query the calculation results in the rpt_user_trace_data table.

Sample SQL:

SELECT * FROM rpt_user_trace_data WHERE dt='20231126';

Query result:

+------------+------------+------------+-------------+------------+------------+
| browse     | click      | purchase   | browse_rate | click_rate | dt         |
+------------+------------+------------+-------------+------------+------------+
| 35         | 16         | 2          | 45.71%      | 12.5%      | 20231126   |
+------------+------------+------------+-------------+------------+------------+

Visualize the data.
Use Quick BI to create a dashboard to visualize the funnel analysis results. For more information, see Add a cloud data source MaxCompute and Funnel Chart.
Important
- When creating a dataset in Quick BI, first check if the three-layer model is enabled for the MaxCompute project, and then create the dataset accordingly.
  In MaxCompute, you can run the setproject; command to view the value of the odps.namespace.schema parameter in the project properties.
  If the value is true, the three-layer model is enabled for the project. In this case, use custom SQL to create the dataset because Quick BI does not officially support the MaxCompute three-layer model. Creating a dataset by dragging fields, which internally generates SQL, will cause a "table not found" error.
  If the value is false, the three-layer model is not enabled. You can create the dataset using custom SQL or by dragging fields.
- To generate the following funnel chart, you need to select the dataset whose table object is ods_user_trace_data and use the customize_event field as the Funnel Layers/Dimension.