ファンネル分析

tutorial_funnel.sql

-- 1.1 チュートリアルファンネル（All） --
td query -w -f csv -d your_app -o tutorial_all.csv "
SELECT
  t2.step AS step,
  cnt,
  prev_cnt,
  ROUND(cnt/enter_cnt*100) AS rate_from_enter,
  IF(prev_cnt IS NULL, 100,ROUND(cnt/prev_cnt*100)) AS rate_from_prev,
  IF(prev_cnt IS NULL, 0,ROUND((prev_cnt-cnt)/prev_cnt*100)) AS normed_rate_from_enter
FROM
(
  SELECT COUNT(DISTINCT v['uid']) AS enter_cnt, 1 AS one
  FROM tutorial
  WHERE CAST(v['step'] AS INT) = 1
) t1
JOIN
(
  SELECT CAST(v['step'] AS INT) AS step, COUNT(DISTINCT v['uid']) AS cnt, 1 AS one
  FROM tutorial
  GROUP BY v['step']
) t2
ON
(t1.one=t2.one)
LEFT OUTER JOIN
(
  SELECT CAST(v['step'] AS INT) AS step, COUNT(DISTINCT v['uid']) AS prev_cnt
  FROM tutorial
  GROUP BY v['step']
) t3
ON
(t2.step = t3.step+1)
ORDER BY step
"

-- 1.2 チュートリアルファンネル（Segmented By 「流入動機」） --
td query -w -f csv -d your_app -o tutorial_by_segment.csv "
SELECT
  t2.motivated_by AS motivated_by,
  t2.step AS step,
  cnt,
  prev_cnt,
  ROUND(cnt/enter_cnt*100) AS rate_from_enter,
  IF(prev_cnt IS NULL, 100,ROUND(cnt/prev_cnt*100)) AS rate_from_prev,
  IF(prev_cnt IS NULL, 0,ROUND((prev_cnt-cnt)/prev_cnt*100)) AS normarized_rate_from_enter
FROM
(
  SELECT
    v['motivated_by'] AS motivated_by,
    COUNT(DISTINCT v['uid']) AS enter_cnt,
    1 AS one
  FROM tutorial
  WHERE CAST(v['step'] AS INT) = 1
  GROUP BY v['motivated_by']
) t1
JOIN
(
  SELECT
   v['motivated_by'] AS motivated_by,
   CAST(v['step'] AS INT) AS step,
   COUNT(DISTINCT v['uid']) AS cnt,
   1 AS one
  FROM tutorial
  GROUP BY v['motivated_by'], v['step']
) t2
ON
(t1.one=t2.one AND t1.motivated_by=t2.motivated_by)
LEFT OUTER JOIN
(
  SELECT
    v['motivated_by'] AS motivated_by,
    CAST(v['step'] AS INT) AS step,
    COUNT(DISTINCT v['uid']) AS prev_cnt
  FROM tutorial
  GROUP BY v['motivated_by'], v['step']
) t3
ON
(t2.step=t3.step+1 AND t2.motivated_by=t3.motivated_by)
ORDER BY motivated_by,step
"