Задача:

Посчитать конверсию из добавления корзины в транзакцию в рамках одной сессии для самых больших посадочных страниц (>1000 просмотров за месяц) июля 2017 года.

1. Посчитать конверсию корзина -> транзакция в рамках одной сессии (visitId)
2. Найти посадочные страницы (hits.page.pagePath при hits.isEntrance=TRUE), у которых было >1000 хитов за июль 2017
3. Пересчитать воронку из п.1 только для сессий, которые начинались со страниц из п.2 (большие посадочные)
4. Разбить конверсию из п.1 по урлам этих посадочных страниц

SELECT
  big_pages,
  ROUND(COUNT(DISTINCT(transactions.visitId)) / COUNT(DISTINCT(visitId)),3) AS carts_to_transactions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`,
  UNNEST(hits) AS hits
LEFT JOIN (
  SELECT
  IF
    ( hits.TRANSACTION.transactionId IS NOT NULL,
      visitId,
      NULL) AS visitId
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`,
    UNNEST(hits) AS hits
  GROUP BY
    1) AS transactions
USING
  (visitId)
JOIN (
  SELECT
    visitId,
    big_pages
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`,
    UNNEST(hits) AS hits
  JOIN (
    SELECT
      hits.page.pagePath AS big_pages,
    FROM
      `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`,
      UNNEST(hits) AS hits
    WHERE
      hits.isEntrance=TRUE
    GROUP BY
      1
    HAVING
      COUNT(1)>1000)
  ON
    (hits.page.pagePath=big_pages)
  WHERE
    hits.isEntrance=TRUE
  GROUP BY
    1,
    2) AS big_page_visit_ids
USING
  (visitId)
WHERE
  hits.eventInfo.eventAction = "Add to Cart"
GROUP BY
  1

https://datastudio.google.com/embed/reporting/df411966-dd48-4b35-861e-def7d4a4934d/page/jivtB