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

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

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

Лекция

https://www.youtube.com/watch?v=Ju-QryDx2Oc&list=PLpn2Pzb485yhVWjcs_sfYIs1v-ddmcH2D&index=12

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

Датасет google_analytics_sample, таблица ga_sessions

1. Построить воронку по сессиям на 3 этапа:
   1. Session
   2. Add to Cart
   3. Transactions

Период - с 10 мая по 10 июля 2017. Нужно иметь возможность выбрать диапазон дат для визуализации в Google Data Studio.

SELECT
date,
COUNT(DISTINCT(visitId)) as sessions,
COUNT(DISTINCT(IF(hits.eventInfo.eventAction="Add to Cart",visitId,NULL))) as addtocarts,
COUNT(DISTINCT(IF(hits.transaction.transactionRevenue >0,visitId,NULL))) as transactions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '0510'
  AND '0710'
  GROUP BY 1
  ORDER BY 1

https://datastudio.google.com/u/1/reporting/9c9cc289-8911-467b-9bbe-de9b959484dc/page/ZlRtB/edit

2. Визуализировать понедельную динамику конверсии из сессий в транзакции для каналов (medium). Период — весна 2017 года. Нужно иметь возможность фильтровать по странам в Google Data Studio.

Вариант 1, вычисление ECR (ecommerce conversion rate) и недель в GDS:

SELECT
  date,
  trafficSource.medium AS medium,
  geoNetwork.country AS country,
  COUNT(DISTINCT(visitId)) AS sessions,
  COUNT(DISTINCT(
    IF
      (hits.TRANSACTION.transactionRevenue >0,
        visitId,
        NULL))) AS transactions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '0301'
  AND '0531'
GROUP BY
  1,
  2,
  3
ORDER BY
  1

https://datastudio.google.com/embed/reporting/9c9cc289-8911-467b-9bbe-de9b959484dc/page/doRtB

Вариант 2, вычисление ECR и недель в SQL:

SELECT
  FORMAT_DATE("%W",PARSE_DATE('%Y%m%d',
      date)) AS weeknum,
  geoNetwork.country AS country,
  trafficSource.medium AS medium,
  COUNT(DISTINCT(
    IF
      (hits.TRANSACTION.transactionRevenue >0,
        visitId,
        NULL))) / COUNT(DISTINCT(visitId)) AS ECR,
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '0301'
  AND '0531'
GROUP BY
  1,
  2,
  3
ORDER BY
  1

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