How does maxcompute analyze funnel model in e-commerce scenarios

Time:2021-4-26

Introduction: taking an e-commerce case as an example, this paper introduces how to use off-line calculation and make funnel chart for you.

background

Funnel model is actually a tool to judge the operation of products by the conversion rate of various data. Transformation funnel is to judge which link of the product has problems through the transformation of data in each stage, and then continuously optimize the product. E-commerce funnel model, the path of users to buy goods, from browsing goods to paying orders in every aspect of the transformation. This article will show the funnel analysis and display from the user’s “browse click buy” link.

prerequisite

  • Open log service
  • Open maxcompute
  • Open dataworks
  • Open quick Bi

case

1. Business structure diagram

How does maxcompute analyze funnel model in e-commerce scenarios

2. Business process

  • Collect log data through alicloud log service.
  • The data of the log service is synchronized to the big data computing service maxcompute.
  • Maxcompute does offline calculation.
  • Data visualization through alicloud quick Bi.

3. Preparation

The incremental data collected by the log service is synchronized to the maxcompute partition table (this case shows the conversion rate of each link in time days). Please refer to: log service migration to maxcompute for specific steps. And through the dataworks set timing scheduling execution, every morning from the previous day’s data, calculate the conversion rate funnel in days. For details, see dispatching parameters

Table 1. Log source table: ODS_ user_ trans_ d

How does maxcompute analyze funnel model in e-commerce scenarios

Table 2. Creating ODS layer table: ODS_ user_ trace_ Data, please refer to: data import layer (ODS) for related data warehouse model definitions

CREATE TABLE IF NOT EXISTS ods_user_trace_data
(
    MD5 string comment 'top 8 bits of MD5 value of user uid',
    Uid string comment 'user uid',
    TS bit comment 'user action timestamp',
    IP string comment 'IP address',
    Status bit comment 'server returns status code',
    Bytes binary comment 'the number of bytes returned to the client',
    device_ Brand string comment 'equipment brand',
    Device string comment 'terminal model',
    system_ Type string comment 'system type, Android, IOS, iPad, windows_ phone',
    customize_ Event string comment 'custom event: login / exit / purchase / register / click / background / switch user / Browse / comment',
    use_ Time bigint comment 'the duration of a single use of the app. This item is available when the event is exit, background or user switch',
    customize_ event_ Content string comment 'users pay attention to content information in customize_ Include this column when event is browse and comment '
) 
PARTITIONED BY
(
    DT string -- Take dt as time partition, and the unit is day.
);
CREATE TABLE IF NOT EXISTS ods_user_trace_data 
( 
MD5 string comment 'top 8 bits of MD5 value of user uid',
Uid string comment 'user uid',
TS bit comment 'user action timestamp',
IP string comment 'IP address',
Status bit comment 'server returns status code',
Bytes binary comment 'the number of bytes returned to the client',
device_ Brand string comment 'equipment brand',
Device string comment 'terminal model',
system_ Type string comment 'system type, Android, IOS, iPad, windows_ phone',
customize_ Event string comment 'custom event: login / exit / purchase / register / click / background / switch user / Browse / comment',
use_ Time bigint comment 'the duration of a single use of the app. This item is available when the event is exit, background or user switch',
customize_ event_ Content string comment 'users pay attention to content information in customize_ Include this column when event is browse and comment '
) 
PARTITIONED BY 
( 
DT string -- Take dt as time partition, and the unit is day.
);

Table 3. Creating DW layer table: DW_ user_ trace_ Data, please refer to DWD for related data warehouse model definitions

CREATE TABLE IF NOT EXISTS dw_user_trace_data 
( 
Uid string comment 'user uid',
device_ Brand string comment 'equipment brand',
Device string comment 'terminal model',
system_ Type string comment 'system type, Android, IOS, iPad, windows_ phone',
customize_ Event string comment 'custom event: login / exit / purchase / register / click / background / switch user / Browse / comment',
use_ Time bigint comment 'the duration of a single use of the app. This item is available when the event is exit, background or user switch',
customize_ event_ Content string comment 'users pay attention to content information in customize_ Include this column when event is browse and comment '
) 
PARTITIONED BY 
( 
DT string -- Take dt as time partition, and the unit is day.
);

Table 4. Create ads layer result table: RPT_ user_ trace_ Data, please refer to data warehouse layering for related data warehouse model definitions

CREATE TABLE IF NOT EXISTS rpt_user_trace_data 
( 
Browse string comment 'views',
Click string comment 'hits',
Purchase string comment 'purchase quantity',
browse_ Rate string comment 'Browse conversion rate',
click_ Rate string comment 'click conversion'
) 
PARTITIONED BY 
( 
DT string -- Take dt as time partition, and the unit is day.
);

4. Write business logic

User path: browse, click, purchase, conversion rate of each link (conversion rate = the ratio of people entering the next page from one page).

insert OVERWRITE table rpt_user_trace_data PARTITION (dt=${bdp.system.bizdate}) 
Select browse as
, click as hits
, purchase as
, 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 
;

5. Results

How does maxcompute analyze funnel model in e-commerce scenarios

6. Data visualization

Through quick Bi to create the dashboard of user analysis portrait, the visualization of the data table is realized. Please refer to: quick Bi for details

How does maxcompute analyze funnel model in e-commerce scenarios

From the figure above, we find that the business volume from browsing to clicking is obviously reduced, and the conversion rate is low. Analyzing which link is the weak link in the current business process can help people focus more on the weak link and improve the output of the whole process. And then improve the efficiency of the whole process.

test data

According to the funnel model case described above, alicloud provides you with some demo data. You can download the data and complete the operation of the whole case according to the example above, so as to get your funnel model diagram. The data are as follows: test data

The above is about how to use maxcompute and make funnel chart.

Author: Zhang Yajing
Original link
This article is the original content of Alibaba cloud and cannot be reproduced without permission