Видеолекции:

Ответ на домашнее задание после занятия №6

https://www.youtube.com/watch?v=0YO6rSlu-3M&list=PLpn2Pzb485yhVWjcs_sfYIs1v-ddmcH2D&index=13

Лекция

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

Ответ на домашнее задание после занятия №6:

1. Модифицировать запрос из вебинара 2 при помощи INNER JOIN так, чтобы он выводил не все команды, а только те, у которых средний победный счет за сезон 2017 был больше 85.

SELECT
  month,
  winners,
  MAX(max_winner_score) AS max_winner_score
FROM ( (
    SELECT
      FORMAT_DATETIME("%Y-%m",
        DATE(gametime)) AS month,
      CASE
        WHEN h_points_game>a_points_game THEN h_name
        WHEN h_points_game<a_points_game THEN a_name
      ELSE
      "Draw"
    END
      AS winners,
      MAX(CASE
          WHEN h_points_game>a_points_game THEN h_points_game
          WHEN h_points_game<a_points_game THEN a_points_game
        ELSE
        0
      END
        ) AS max_winner_score
    FROM
      `bigquery-public-data.ncaa_basketball.mbb_games_sr`
    WHERE
      season=2017
    GROUP BY
      1,
      2))
INNER JOIN (
  SELECT
    CASE
      WHEN h_points_game>a_points_game THEN h_name
      WHEN h_points_game<a_points_game THEN a_name
    ELSE
    "Draw"
  END
    AS winners,
    AVG(CASE
        WHEN h_points_game>a_points_game THEN h_points_game
        WHEN h_points_game<a_points_game THEN a_points_game
      ELSE
      0
    END
      ) AS avg_winner_score
  FROM
    `bigquery-public-data.ncaa_basketball.mbb_games_sr`
  WHERE
    season=2017
  GROUP BY
    1) AS season_winners
USING
  (winners)
WHERE
  avg_winner_score>85
GROUP BY
  1,
  2
ORDER BY
  1,
  2

2. Сделать визуализацию воронки Session → Store pageview (URL страницы содержит redesign) → Add to Cart → Transaction в динамике по дням за июнь 2017. Прохождение воронки считать в рамках одной сессии (visitId)

SELECT
  date,
  COUNT(DISTINCT(visitId)) AS sessions,
  COUNT(DISTINCT(store_visits.visitId)) AS store_visits,
  COUNT(DISTINCT(carts.visitId)) AS carts,
  COUNT(DISTINCT(transactions.visitId)) AS transactions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_201706*`
LEFT JOIN (
  SELECT
  IF
    ( hits.page.pagePath LIKE "%redesign%",
      visitId,
      NULL) AS visitId
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_201706*`,
    UNNEST(hits) AS hits
  GROUP BY
    1) AS store_visits
USING
  (visitId)
LEFT JOIN (
  SELECT
  IF
    ( hits.eventInfo.eventAction = "Add to Cart",
      visitId,
      NULL) AS visitId
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_201706*`,
    UNNEST(hits) AS hits
  GROUP BY
    1) AS carts
USING
  (visitId)
LEFT JOIN (
  SELECT
  IF
    ( hits.TRANSACTION.transactionId IS NOT NULL,
      visitId,
      NULL) AS visitId
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_201706*`,
    UNNEST(hits) AS hits
  GROUP BY
    1) AS transactions
USING
  (visitId)
GROUP BY
  1
ORDER BY
  1

https://datastudio.google.com/embed/reporting/d711face-eb14-4dd1-b836-3361a6387d2b/page/nsZtB

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

1. Визуализировать понедельную retention-матрицу (недельные когорты и недельный diff) по покупкам (первая и повторная покупка) с начала данных таблицы ga_sessions_ (2016-08-01) до конца 2016 года.

Дополнительные материалы:

Запрос с именами из занятия:

SELECT
  year,
  gender,
  biggest_name
FROM (
  SELECT
    year,
    name,
    gender,
    number,
    FIRST_VALUE(name) OVER(PARTITION BY year, gender ORDER BY number DESC) AS biggest_name
  FROM
    `bigquery-public-data.usa_names.usa_1910_current`
  GROUP BY
    1,
    2,
    3, 4)
GROUP BY
  1,
  2,
  3