Tự tìm hiểu

BigQuery – Truy vấn Daily retention của

Query: Daily retention

 

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
    `swordman-reforged.analytics_197394231.events_*`
  WHERE
    event_name = ‘user_engagement’
    AND geo.continent = ‘Europe’ AND (_TABLE_SUFFIX BETWEEN ‘20190502’ AND ‘20190530’)
  GROUP BY
    install_date,
    event_realdate,
    days_since_install
  )
GROUP BY install_date
HAVING day_0 > 0
ORDER BY install_date

Bài viết liên quan

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Close

Oops!! Có vẻ nhưng bạn đang bật tính năng chặn quảng cáo

Quảng cáo giúp blog duy trì website và tiếp tục chia sẻ các thủ thuật đến bạn. Mong bạn vui lòng tắt Adblock hoặc cho blog vào danh sách trắng trong công cụ chặn quảng cáo của bạn. Cảm ơn đã ủng hộ website!