Funnel analysis calculation, how would you calculate a funnel?

bigdata, hadoop, java, mapreduce, math

Solution

First the answer, using standard SQL, given your hypothesis: there is a table EVENTS with a simple layout:

EVENTS
-----------------------------
SESION_ID , EVENT_NAME , TMST

To get the session that performed step#1 at some time:

-- QUERY 1
SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event1' GROUP BY SESSION_ID;

Here I make the assumption that event1 can happen more then once per session. The result is a list of unique session that demonstrated event1 at some time.

In order to get step2 and step3, I can just do the same:

-- QUERY 2
SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event2' GROUP BY SESSION_ID;
-- QUERY 3
SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event3' GROUP BY SESSION_ID;

Now, you want to select sessions that performed step1, step2 and step3 - in that order. More precisely you need to count sessions that performed step 1, then count session that performed step2, then count sessions that performed step3. Basically we just need to combine the 3 above queries with left join to list the sessions that entered the funnel and which steps they performed:

-- FUNNEL FOR S1/S2/S3
SELECT 
  SESSION_ID, 
  Q1.TMST IS NOT NULL AS PERFORMED_STEP1,
  Q2.TMST IS NOT NULL AS PERFORMED_STEP2,
  Q3.TMST IS NOT NULL AS PERFORMED_STEP3
FROM
  -- QUERY 1
  (SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event1' GROUP BY SESSION_ID) AS Q1,
LEFT JOIN
  -- QUERY 2
  (SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event2' GROUP BY SESSION_ID) AS Q2,
LEFT JOIN
  -- QUERY 3
  (SELECT SESSION_ID,MIN(TMST) FROM EVENTS WHERE EVENT_NAME='event2' GROUP BY SESSION_ID) AS Q3
-- Q2 & Q3
ON Q2.SESSION_ID=Q3.SESSION_ID AND Q2.TMST<Q3.TMST
-- Q1 & Q2
ON Q1.SESSION_ID=Q2.SESSION_ID AND Q1.TMST<Q2.TMST

The result is a list of unique session who entered the funnel at step1, and may have continued to step2 and step3... e.g:

SESSION_ID_1,TRUE,TRUE,TRUE
SESSION_ID_2,TRUE,TRUE,FALSE
SESSION_ID_3,TRUE,FALSE,FALSE
...

Now we just have to compute some stats, for example:

SELECT
  STEP1_COUNT,
  STEP1_COUNT-STEP2_COUNT AS EXIT_AFTER_STEP1,
  STEP2_COUNT*100.0/STEP1_COUNT AS PERCENTAGE_TO_STEP2,
  STEP2_COUNT-STEP3_COUNT AS EXIT_AFTER_STEP2,
  STEP3_COUNT*100.0/STEP2_COUNT AS PERCENTAGE_TO_STEP3,
  STEP3_COUNT*100.0/STEP1_COUNT AS COMPLETION_RATE
FROM
(-- QUERY TO COUNT session at each step
  SELECT
    SUM(CASE WHEN PERFORMED_STEP1 THEN 1 ELSE 0 END) AS STEP1_COUNT,
    SUM(CASE WHEN PERFORMED_STEP2 THEN 1 ELSE 0 END) AS STEP2_COUNT,
    SUM(CASE WHEN PERFORMED_STEP3 THEN 1 ELSE 0 END) AS STEP3_COUNT
  FROM
    [... insert the funnel query here ...]
) AS COMPUTE_STEPS

Et voilà !

Now for the discussion. First point, the result is pretty straightforward given you take the "set"(or functional) way of thinking and not the "procedural" approach. Don't visualize the database as a collection of fixed tables with columns and rows... this is how it is implemented, but it is not the way you interact with it. It's all sets, and you can arrange the sets like the way you need!

Second point that query will be automatically optimized to run in parallel if you are using a MPP database for instance. You don't even need to program the query differently, use map-reduce or whatever... I ran the same query on my test dataset with more than 100 millions events and get results in seconds.

Last but not least, the query opens endless possibilities. Just group by the results by the referer, keywords, landing-page, user informations, and analyse which provides the best convertion rate for instance!

Problem

Original source