Ответ на домашнее задание №7:

https://www.youtube.com/watch?v=wCb5O_U6yVg&list=PLpn2Pzb485yhVWjcs_sfYIs1v-ddmcH2D&index=15

SELECT
  date,
  min_date,
  users,
  diff,
  FIRST_VALUE(users) OVER(PARTITION BY min_date ORDER BY diff) AS first_month_users,
  users / FIRST_VALUE(users) OVER(PARTITION BY min_date ORDER BY diff) AS retained
FROM (
  SELECT
    FORMAT_DATE("%Y - %W",PARSE_DATE("%Y%m%d",
        date)) AS date,
    FORMAT_DATE("%Y - %W",PARSE_DATE("%Y%m%d",
        min_date)) AS min_date,
    DATE_DIFF(PARSE_DATE("%Y%m%d",
        date),PARSE_DATE("%Y%m%d",
        min_date),WEEK(MONDAY)) AS diff,
    COUNT(DISTINCT(fullVisitorId)) AS users
  FROM (
    SELECT
      fullVisitorId,
      date,
      MIN(date) OVER(PARTITION BY fullVisitorId) AS min_date
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_2016*`,
      UNNEST (hits) AS hits
      WHERE hits.transaction.transactionId IS NOT NULL
    GROUP BY
      1,
      2)
  GROUP BY
    1,
    2,
    3)
GROUP BY
  1,
  2,
  3,
  4

https://datastudio.google.com/embed/reporting/d58b1a82-a763-47e3-8890-6927f0d9f055/page/bDTuB

Лекция:

https://www.youtube.com/watch?v=0kxYYi0U6G0&list=PLpn2Pzb485yhVWjcs_sfYIs1v-ddmcH2D&index=16

Домашнее задание:

Создать визуализацию с помесячным количеством пользователей по всей таблице ga_sessions. Разделить пользователей на 3 (опционально 4) типа:

1. New — первый визит пользователя был в этом месяце
2. Retained — пользователь был и в этом, и в прошлом месяце
3. Resurrected — количество пользователей, которые отпали в прошлом месяце / месяцах, но вернулись в этом (есть в этом, но не новый и нет в прошлом)
4. Опционально: Churned — количество пользователей, которые были в прошлом месяце, но отпали в этом. Показать со знаком минус.

Пример похожей визуализации здесь: https://medium.com/swlh/diligence-at-social-capital-part-1-accounting-for-user-growth-4a8a449fddfc#.qxpy0xpda

(мы добавляем понятие retained и не считаем соотношения)

Подсказки:

1. Считать нужно разницу в календарных месяцах, а не 30 дней между датами. Для этого сначала нужно преобразовать даты в месяцы, а потом уже брать DATE_DIFF. FORMAT_DATE преобразует дату в STRING, что не позволяет выполнить DATE_DIFF поверх. Возьмите функцию DATE_TRUNC(date,MONTH): она обрежет 2020-12-25 до первого дня месяца, то есть 2020-12-01: https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_trunc
2. Вам понадобится аналитическая функция LAG: поиск значения из предыдущей строки таблицы: https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#lag
3. В скобках после OVER() в аналитических функциях нет обязательных параметров: можно указывать или только PARTITION BY, или только ORDER BY, или вообще ничего не указывать, если нужны значения по неотсортированной таблице