landing_page_ranking_to_signup.sql

-- landing page access ranking analysis using Treasure Data
SELECT first_path, COUNT(1) AS count
FROM (
  SELECT TD_SESSIONIZE(time, 3600, host) AS session_id, TD_FIRST(path, time) AS first_path
  FROM (
    SELECT time, v['host'] as host, v['path'] as path
    FROM www_access
    DISTRIBUTE BY v['host']
    SORT BY v['host'], v['time']
  ) ss
  GROUP BY TD_SESSIONIZE(time, 3600, host)
  HAVING path = "/signup"
) fs
GROUP BY first_path
ORDER BY count DESC
LIMIT 100

signup_contribution_page_ranking.sql

-- contribution raking for signup using Treasure Data
SELECT contributed_path, COUNT(1) AS count
FROM (
  SELECT TD_SESSIONIZE(time, 3600, host) AS session_id, COLLECT_SET(path) AS contributed_paths
  FROM (
    SELECT time, v['host'] as host, v['path'] as path
    FROM www_access
    DISTRIBUTE BY v['host']
    SORT BY v['host'], v['time']
  ) ss
  GROUP BY TD_SESSIONIZE(time, 3600, host)
  HAVING path = "/signup"
) fs
LATERAL VIEW EXPLODE(contributed_paths) AS contributed_path
WHERE contributed_path != "/signup"
GROUP BY contributed_path
ORDER BY count DESC
LIMIT 100