Query: Daily retention

Calculate daily retention by date of install.

Notes: The first day should be ignored as it contains partial data.

Query:

SELECT install_date, 
  SUM(CASE WHEN days_since_install = 0 THEN users ELSE 0 END) as day_0,
  SUM(CASE WHEN days_since_install = 1 THEN users ELSE 0 END) as day_1,
  SUM(CASE WHEN days_since_install = 2 THEN users ELSE 0 END) as day_2,
  SUM(CASE WHEN days_since_install = 3 THEN users ELSE 0 END) as day_3,
  SUM(CASE WHEN days_since_install = 4 THEN users ELSE 0 END) as day_4,
  SUM(CASE WHEN days_since_install = 5 THEN users ELSE 0 END) as day_5,
  SUM(CASE WHEN days_since_install = 6 THEN users ELSE 0 END) as day_6,
  SUM(CASE WHEN days_since_install = 7 THEN users ELSE 0 END) as day_7,
  SUM(CASE WHEN days_since_install = 8 THEN users ELSE 0 END) as day_8,
  SUM(CASE WHEN days_since_install = 9 THEN users ELSE 0 END) as day_9,
  SUM(CASE WHEN days_since_install = 10 THEN users ELSE 0 END) as day_10,
  SUM(CASE WHEN days_since_install = 11 THEN users ELSE 0 END) as day_11,
  SUM(CASE WHEN days_since_install = 12 THEN users ELSE 0 END) as day_12,
  SUM(CASE WHEN days_since_install = 13 THEN users ELSE 0 END) as day_13,
  SUM(CASE WHEN days_since_install = 14 THEN users ELSE 0 END) as day_14,
  SUM(CASE WHEN days_since_install = 15 THEN users ELSE 0 END) as day_15,
  SUM(CASE WHEN days_since_install = 16 THEN users ELSE 0 END) as day_16,
  SUM(CASE WHEN days_since_install = 17 THEN users ELSE 0 END) as day_17,
  SUM(CASE WHEN days_since_install = 18 THEN users ELSE 0 END) as day_18,
  SUM(CASE WHEN days_since_install = 19 THEN users ELSE 0 END) as day_19,
  SUM(CASE WHEN days_since_install = 20 THEN users ELSE 0 END) as day_20,
  SUM(CASE WHEN days_since_install = 21 THEN users ELSE 0 END) as day_21,
  SUM(CASE WHEN days_since_install = 22 THEN users ELSE 0 END) as day_22,
  SUM(CASE WHEN days_since_install = 23 THEN users ELSE 0 END) as day_23,
  SUM(CASE WHEN days_since_install = 24 THEN users ELSE 0 END) as day_24,
  SUM(CASE WHEN days_since_install = 25 THEN users ELSE 0 END) as day_25,
  SUM(CASE WHEN days_since_install = 26 THEN users ELSE 0 END) as day_26,
  SUM(CASE WHEN days_since_install = 27 THEN users ELSE 0 END) as day_27,
  SUM(CASE WHEN days_since_install = 28 THEN users ELSE 0 END) as day_28,
  SUM(CASE WHEN days_since_install = 29 THEN users ELSE 0 END) as day_29,
  SUM(CASE WHEN days_since_install = 30 THEN users ELSE 0 END) as day_30
FROM
  (
  SELECT
    DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) AS install_date,
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_realdate,
    DATE_DIFF(DATE(TIMESTAMP_MICROS(event_timestamp)), DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)), day) AS days_since_install,
    COUNT(DISTINCT user_pseudo_id) AS users
  FROM
    `analytics_XXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20181201' AND '20181231' 
  GROUP BY
    install_date,
    event_realdate,
    days_since_install 
  )
GROUP BY install_date
HAVING day_0 > 0 /* Remove older dates - not enough data, you should also ignore the first record for partial data */
ORDER BY install_date