Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to analyze the funnel model of MaxCompute in e-commerce scene". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "how to analyze the funnel model of MaxCompute in the e-commerce scene".

Background

The funnel model is actually a tool to judge the operation of the product by the conversion rate of each data of the product. The conversion funnel is to judge which link of the product has gone wrong through the transformation of the data of each stage, and then constantly optimize the product. E-commerce funnel model, the path for users to buy goods, the transformation of every link from browsing goods to paying orders. This article will show the funnel analysis and display from the user "browse-click-buy" link.

prerequisite

Activate log service

Activate MaxCompute

Activate Dataworks

Activate Quick BI

Case

1. Business architecture diagram

two。 Business proc

Collect log data through Ali Cloud log service.

The data of the log service is synchronized to the big data computing service MaxCompute.

MaxCompute does offline calculation.

Visually display the data through Ali Cloud Quick BI.

3. Preparatory work

Synchronize the data increments collected by the log service into the MaxCompute partition table (this case shows the conversion rate of each link in time and days). For more information, please see: migration from CLS to MaxCompute. And through the Dataworks setting timing scheduling execution, every morning at a fixed time to take the data of the previous day, calculate the day-to-day conversion funnel chart. For more information, please see: scheduling parameters

Table 1. Log source table: ods_user_trans_d

Table 2. Create ODS layer table: ods_user_trace_data. For more information on warehouse model definition, please see: data Import layer (ODS)

CREATE TABLE IF NOT EXISTS ods_user_trace_data (the first 8 digits of the MD5 value of md5 STRING COMMENT 'user uid', uid STRING COMMENT 'user uid', ts BIGINT COMMENT' user operation timestamp', ip STRING COMMENT'ip address', status BIGINT COMMENT 'server return status code' Bytes BIGINT COMMENT 'bytes returned to client', device_brand STRING COMMENT 'device brand', device STRING COMMENT 'terminal model', system_type STRING COMMENT 'system type Android, IOS, ipad, Windows_phone', customize_event STRING COMMENT 'Custom event: login / logout / purchase / Registration / Click / backend / switch user / Browse / comment', use_time BIGINT COMMENT 'APP single use duration, when the event is exit, backend, switch user, there is this item, customize_event_content STRING COMMENT' user follow content information When customize_event is browsing and commenting, include the column') PARTITIONED BY (dt STRING-- dt as the time partition, in days.) CREATE TABLE IF NOT EXISTS ods_user_trace_data (the first 8 digits of the MD5 value of md5 STRING COMMENT 'user uid', uid STRING COMMENT 'user uid', ts BIGINT COMMENT' user operation timestamp', ip STRING COMMENT'ip address', status BIGINT COMMENT 'server return status code' Bytes BIGINT COMMENT 'bytes returned to client', device_brand STRING COMMENT 'device brand', device STRING COMMENT 'terminal model', system_type STRING COMMENT 'system type Android, IOS, ipad, Windows_phone', customize_event STRING COMMENT 'Custom event: login / logout / purchase / Registration / Click / backend / switch user / Browse / comment', use_time BIGINT COMMENT 'APP single use duration, when the event is exit, backend, switch user, there is this item, customize_event_content STRING COMMENT' user follow content information When customize_event is browsing and commenting, include the column') PARTITIONED BY (dt STRING-- dt as the time partition, in days.)

Table 3. Create dw layer table: dw_user_trace_data. For more information on warehouse model definition, please see: detail and Fine Grain fact layer (DWD)

CREATE TABLE IF NOT EXISTS dw_user_trace_data (uid STRING COMMENT 'user uid', device_brand STRING COMMENT' device brand', device STRING COMMENT 'terminal model', system_type STRING COMMENT 'system type) Android, IOS, ipad, Windows_phone', customize_event STRING COMMENT 'Custom event: login / logout / purchase / Registration / Click / backend / switch user / Browse / comment', use_time BIGINT COMMENT 'APP single use duration, when the event is exit, backend, switch user, there is this item, customize_event_content STRING COMMENT' user follow content information When customize_event is browsing and commenting, include the column') PARTITIONED BY (dt STRING-- dt as the time partition, in days.)

Table 4. Create ADS layer result table: rpt_user_trace_data. For more information on the definition of data warehouse model, see: data warehouse layering.

CREATE TABLE IF NOT EXISTS rpt_user_trace_data (browse STRING COMMENT 'pageviews', click STRING COMMENT 'clicks', purchase STRING COMMENT 'purchases', browse_rate STRING COMMENT 'browsing conversion rate', click_rate STRING COMMENT 'clicks converted') PARTITIONED BY (dt STRING-dt as the time partition, in days.)

4. Write business logic

User path: browse-> Click-> Buy, the conversion rate of each link (conversion rate = the number of people from one page to the next).

Insert OVERWRITE table rpt_user_trace_data PARTITION (dt=$ {bdp.system.bizdate}) SELECT browse as views, click as clicks, purchase as purchases, concat (round ((click/browse) * 100jue 2),'%') as click conversion rate, concat (round ((purchase/click) * 100prime2),'%') 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) aleft JOIN (select dt,count (1) click from dw_user_trace_data where customize_event='click' and dt= ${bdp.system.bizdate} group by dt) bon a.dt=b.dtleft JOIN (select dt,count (1) purchase from dw_user_trace_data where customize_event='purchase'and dt= ${bdp.system.bizdate} group by dt) con a.dt=c.dt

5. Result

6. Data visualization display

Create the dashboard of the user's analysis portrait of the website through Quick BI to realize the visualization of the data table. For more information, please see: Quick BI

From the above picture, we found that the volume of business browsed to the click showed an obvious decreasing trend, and the conversion rate was 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.

At this point, I believe you have a deeper understanding of "how to analyze the funnel model of MaxCompute in the e-commerce scene". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.