This article introduces the basic concepts of the funnel analysis function and its basic usage.

The funnel analysis function is suitable for observing the summary of funnel layers for users in each time unit (day) within a selected time range, divided by a certain time range.

Brief introduction to funnel calculation logic:

• First, use finderFunnel to calculate the performance of each user within a time window;
• Then, use funnelRep to aggregate the results of finderFunnel, forming the summary result of all users in each layer of the funnel.

finderFunnel() #

finderFunnel(window, start_timestamp, check_granularity, watch_numbers, relatedNum, window_type, time_zone, time_interval)(server_time,client_timestamp, [param1, param2, ..., ] 
check_event1, check_event2...)

Parameter description

| | | \

funnelRep() #

Use funnelRep to aggregate the results of finderFunnel, generating the Funnel summary results for all users at each layer.

funnelRep(number_steps, evnet_count)(funnel_res)

Parameter description

| | | \

Description of return results
Returns a two-dimensional array. The output array contains one summary result and number_steps subarrays. The first subarray is the summary result, the second subarray is the Funnel calculation result for the first time slot within the analysis duration scope defined by window, and so on.

funnelRep2() #

The funnelRep2() function adds the targe_step and taget_interval_group parameters based on funnelRep(), aggregates the results of finderFunnel, and processes detailed data for each interval between steps.

funnelRep2(number_steps, evnet_count, targe_step, taget_interval_group)(funnel_res.1, funnel_res.2)

Parameter description

| | || \

Description of return results

( 
// Distribution intervals of conversion users for a certain layer, two-dimensional array, outer(inner) 
// outer[0] is the summary value, outer[i] represents the i-th observation unit 
// inner[i] is the number of users whose conversion interval falls within the (i+1)-th range 
[ [500, 300, 200], [200, 200, 100], [xxx, xxx, xxx], [xxx, xxx, xxx] ], 
// Arithmetic statistics distribution intervals, two-dimensional array, outer(inner) 
// outer[0] is the arithmetic statistics of the summary conversion interval, outer[i] is the arithmetic statistics of the i-th interval observation unit 
[ [avg, max, min, mid, 75-quantile, 25-quantile], [...], [...], [...] ] 
)

Example model #

The following example tables are composed of the user behavior fact table Log and the user dimension table User, forming a commonly used star schema. The subsequent sections will demonstrate the specific usage of funnel analysis functions based on these tables.

CREATE TABLE -- Event log table 
  default.LOG ( 
    `Visitor` UInt32, 
    `EventName` String, 
    `EventTime` DateTime, 
    `Province` String, 
    `City` String 
  ) ENGINE = CnchMergeTree 
  ORDER BY Visitor; 

  CREATE TABLE -- User table 
  default.User ( 
    `Visitor` UInt32, 
    `Fvisit_Time` DateTime, 
    `Fvisit_Province` String, 
    `Fvisit_City` String 
  ) ENGINE = CnchMergeTree 
  ORDER BY Visitor;

Basic conversion model #

The basic conversion model uses finderFunnel() and funnelRep() to calculate the conversion at each step of the events.
The following example refers to returning the conversion funnel for three events ('E0001', 'E0002', 'E0003') over 7 days from 2022/6/1 to 2022/6/8, using a window period of ten minutes and days as the granularity:

SELECT funnelRep(7, 3)(funnel_res) 
FROM 
( 
    SELECT finderFunnel(600, 1654012800, 86400, 7)(toUInt64(toUnixTimestamp(LOG.EventTime)), toUInt64(toUnixTimestamp(LOG.EventTime)), LOG.EventName = 'E0001', LOG.EventName = 'E0002', LOG.EventName = 'E0003') AS funnel_res 
    FROM LOG 
    LEFT JOIN 
    ( 
        SELECT * 
        FROM User AS t 
    ) AS U ON LOG.Visitor = U.Visitor 
    WHERE (EventTime >= '2022-06-01 00:00:00') AND (EventTime <'2022-06-08 00:00:00') AND (EventName IN ('E0001', 'E0002', 'E0003')) 
    GROUP BY Visitor 
) 
SETTINGS distributed_product_mode = 'local', distributed_group_by_no_merge = 1

The above example SQL returns

┌─funnelRep(7, 3)(funnel_res)─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 
│ [[5994501,5944849,56423],[3791527,3780488,39431],[3792876,3767458,33024],[3792031,3733089,26709],[3791519,3656585,20668],[3791859,3480158,14385],[3793389,3077233,8691],[3792344,2143108,3641]] │

• The overall conversion funnel (E0001->E0002->E0003) is: [5994501,5944849,56423]
• The conversion funnel for day 1 is: [3791527,3780488,39431]
• ...
• The conversion funnel for day 7 is: [3792344,2143108,3641]

Calculate conversion intervals #

The following example uses the funnelRep2() function to observe 6 days of data and two conversion events, and calculates the conversion intervals between each step.

SELECT funnelRep2(6, 2, 0, [0, 60000, 120000, 180000, 240000, 300000, 360000, 420000, 480000, 540000, 600000])(funnel_res.1, funnel_res.2) AS col 
FROM 
( 
    SELECT finderFunnel(600000, 1684512000, 86400, 6, 0, 0, 'Asia/Shanghai', 1)(toUInt64(toUnixTimestamp(LOG.EventTime)), toUInt64(toUnixTimestamp(LOG.EventTime)), LOG.EventName = 'E0001', LOG.EventName = 'E0002') AS funnel_res 
    FROM LOG 
    WHERE (EventTime >= '2023-05-20 00:00:00') AND (EventTime <'2023-05-26 00:00:00') AND (EventName IN ('E0001', 'E0002')) 
    GROUP BY Visitor 
)

• 6: Indicates a total observation of 6 days.
• 2: Indicates two conversion events.
• 0: Indicates that the conversion intervals between each step need to be calculated.
• [0, 60000, 120000, 180000, 240000, 300000, 360000, 420000, 480000, 540000, 600000]: Indicates that the intervals are divided into ten ranges: [0, 60000), [60000, 120000), ..., [540000, 600000].

As a result

( 
 [ 
[1845,100,45,24,16,14,17,11,12,32], // Indicates the total Summary of Conversions users in each Range 
[370,19,12,3,4,4,2,1,5,5], // Indicates the Summary of Conversions users in each Range for the first day 
[87,6,3,1,1,0,2,0,2,4], // Indicates the Summary of Conversions users in each Range for the second day 
[99,4,1,2,0,0,2,0,6], // Indicates the third day... and so on 
 [442,25,10,5,2,1,4,1,2,7], 
 [437,25,8,6,3,6,3,1,1,6], 
 [410,21,11,7,6,3,6,62,4] 
 ], 
 [ 
[38240.619092627596,596767,2,4829.31494140625,20458.8353515625,790.316650390625], // Indicates the total Statistical value of all conversion Intervals within six days; the six elements correspond to avg (Average value), max (Max), min (Min), mid (Median), 75-quantile (75th percentile), 25-quantile (25th percentile) 
[37865.0494117647,588283,28,5586.3125,19187.166015625,816.0833129882812], // Indicates the Statistical value of conversion Intervals for the first day; the six elements correspond as above 
[59552.33018867925,587526,14,7210.5,25011,1087], // Indicates the second day... 
[52667.491228070176,596132,47,3111,19174,781], // The third day... and so on 
 [32494.533066132266,596547,23,4434.09375,16213.638671875,731.5277709960938], 
 [34384.957661290326,59484,24,4429,23285.33203125,744.5555419921875], 
 [40416.285711428572,596767,2,5539,21438.056640625,833.88888549804688] 
 ] 
)