Справочник по BigQuery

Эта функция доступна только в Аналитике 360, входящей в Google Платформу для маркетинга.
Подробнее о Google Платформе для маркетинга…

Эта статья содержит примеры составления запросов данных Аналитики, экспортируемых в BigQuery. Опробовать эти запросы можно на специально созданном образце набора данных.

Содержание

Оптимизация запросов

Каждый запрос учитывается при расчете объема обработанных данных. Если выбрано слишком много полей, повышается количество обрабатываемых данных. В результате чрезмерно растет их суммарный объем. Оптимизируя запросы, вы можете эффективно использовать месячную норму обработки данных.

Подробнее о ценах…

Выбирайте только то, что вам нужно

Формулируя запрос, выбирайте только релевантные поля в операторе SELECT. Это позволит сократить объем данных и время на обработку запроса.

Пример: избегайте использования подстановочных знаков.

Неудачный вариант: использование подстановочного знака
SELECT *
FROM [название таблицы];

 

Удачный вариант: использование названий полей во избежание ненужной обработки
SELECT field1, field2
FROM [название таблицы];

Разрешите кеширование

По возможности избегайте использования функций в качестве полей. Функции (такие как NOW() или TODAY()) возвращают переменные результаты. Это препятствует кешированию запросов, ускоряющему их обработку. Вместо этого указывайте точные значения времени и даты.

В настоящее время кешированные результаты не поддерживаются для запросов к нескольким таблицам с использованием подстановочных знаков, даже если установлен флажок "Использовать кешированные результаты". Если вы выполняете один и тот же запрос с подстановочными знаками несколько раз, оплата взимается за каждый запрос. Подробнее…

Используйте промежуточные таблицы для часто используемых подзапросов

Если определенный запрос часто используется в качестве подзапроса, его можно сохранить в качестве промежуточной таблицы, нажав Save as Table (Сохранить в таблицу) над результатами запроса. Затем на эту таблицу можно ссылаться в блоке FROM запроса. Это позволит уменьшить как объем данных, так и время на их обработку.

Использование промежуточной таблицы
SELECT field1, field2
FROM [название набора данных.название таблицы];

Отладка запросов

BigQuery отлаживает код по мере его составления. В окне кода данные отладки выводятся непосредственно под запросом. Отладку можно также осуществлять в API при использовании флага dryRun.

Для корректных запросов в нижней части окна появляется зеленый индикатор, нажав на который можно узнать объем обработанных данных. Это позволяет оптимизировать данные до выполнения запроса, так что вы можете избежать ненужной обработки данных.

Query Debugging - Success

 

Для запросов с ошибками появляется красный индикатор, нажав на который можно посмотреть информацию об ошибках, в том числе номера строк и позиций в строке, где они возникли. Приведенный ниже пример содержит пустой оператор GROUP BY, который вызвал ошибку.

Query Debugging - Error

 

Советы и рекомендации

Использование образца набора данных

В приведенных ниже примерах используется образец набора данных Google Аналитики.

Чтобы использовать запрос для собственных данных, просто замените названия проекта и набора данных в примерах.

Использование стандартного SQL или прежней версии SQL

BigQuery поддерживает два диалекта SQL:

О различиях между ними читайте в статье Переход на стандартный SQL.

Сейчас для запросов данных из BigQuery, рекомендуется использовать стандартный SQL.

Вы также можете узнать, как включить стандартный SQL в BigQuery, CLI, API или другом интерфейсе.

Мы рекомендуем добавлять комментарий "standardSQL" в начало запроса на стандартном SQL, как показано в примерах ниже.

Если вы используете прежнюю версию SQL, то данные Google Аналитики 360 будут каждый день сохраняться в новой таблице. Чтобы запросить сразу несколько таблиц, можно перечислить их названия через запятую, воспользоваться подстановочной функцией TABLE_DATE_RANGE или даже несколькими функциями TABLE_DATE_RANGE через запятую, как в примерах ниже.

Запрос нескольких таблиц

Ниже приведены примеры запросов на стандартном SQL и прежней версии SQL для одних и тех же данных.

Данные за 3 дня

Стандартный SQL
Данные за 3 дня, UNION ALL
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
Прежняя версия SQL
Данные за 3 дня, названия таблиц через запятую
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

Данные за последние 1095 дней

Стандартный SQL
Данные за последние 1095 дней, _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Прежняя версия SQL
Данные за последние 1095 дней, TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Данные за последние 36 месяцев

Стандартный SQL
Данные за последние 36 месяцев, _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Прежняя версия SQL
Данные за последние 36 месяцев, TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Последние 3 года

Стандартный SQL
Данные за последние 3 года, _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Прежняя версия SQL
Данные за последние 3 года, TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Данные за указанный период

Стандартный SQL
Данные за указанный период, _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
Прежняя версия SQL
Данные за указанный период, TABLE_DATE_RANGE
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

Данные за последние 3 года и текущий день

Стандартный SQL
Данные за последние 3 года и текущий день, UNION ALL, _TABLE_SUFFIX
Примечание. Этот пример запроса не подходит для общедоступного набора данных Google Аналитики, поскольку там нет таблицы за текущий день.
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

Прежняя версия SQL

Данные за последние 3 года и текущий день, TABLE_DATE_RANGE
Примечание. Этот пример запроса не подходит для общедоступного набора данных Google Аналитики, поскольку там нет таблицы за текущий день.
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

Простые запросы: примеры

В этом разделе описывается составление простых запросов с использованием показателей и параметров из образца данных Google Аналитики.

Суммарный [показатель] на [параметр]

Ниже приведены примеры скриптов, позволяющих узнать, какое суммарное количество транзакций на браузер устройства зарегистрировано в июле 2017 г.

Стандартный SQL

Суммарное количество транзакций на браузер устройства в июле 2017 г.
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

Прежняя версия SQL

Суммарное количество транзакций на браузер устройства в июле 2017 г.
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

Средний показатель отказов на [параметр]

Показатель отказов определяется как процент посещений с одним просмотром страницы. Ниже приведены примеры скриптов, позволяющих узнать фактический показатель отказов на источник трафика.

Стандартный SQL

Показатель отказов на источник трафика в июле 2017 г.
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

Прежняя версия SQL

Показатель отказов на источник трафика в июле 2017 г.
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

Среднее количество просмотров страниц товаров по типу пользователей (покупатели и остальные посетители)

Ниже приведены примеры скриптов, позволяющие узнать среднее количество просмотров страниц товаров пользователями, совершившими покупку, в июле 2017 г.

Стандартный SQL

Среднее количество просмотров страниц товаров пользователями, совершившими покупку, в июле 2017 г.
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

Прежняя версия SQL

Среднее количество просмотров страниц товаров пользователями, совершившими покупку, в июле 2017 г.
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

Ниже приведены примеры скриптов, позволяющих узнать среднее количество просмотров страниц товаров пользователями, которые не совершили покупку, в июле 2017 г.

Стандартный SQL

Среднее количество просмотров страниц товаров пользователями, не совершившими покупку, в июле 2017 г.
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

Прежняя версия SQL

Среднее количество просмотров страниц товаров пользователями, не совершившими покупку, в июле 2017 г.
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

Среднее число транзакций на покупателя

Ниже приведены примеры скриптов, позволяющих узнать среднее суммарное количество транзакций на пользователя, совершившего покупку, в июле 2017 г.

Стандартный SQL

Среднее количество транзакций на пользователя, совершившего покупку, в июле 2017 г.
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

Прежняя версия SQL

Среднее количество транзакций на пользователя, совершившего покупку, в июле 2017 г.
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

Средняя сумма расходов за сеанс

Ниже приведены примеры скриптов, позволяющих узнать среднюю сумму расходов за сеанс в июле 2017 г.

Стандартный SQL

Средняя сумма расходов за сеанс в июле 2017 г.
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

Прежняя версия SQL

Средняя сумма расходов за сеанс в июле 2017 г.
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

Последовательность обращений

Ниже приведены примеры скриптов, позволяющих узнать последовательность просмотра страниц.

Стандартный SQL

Последовательность просмотра страниц пользователями в июле 2017 г.
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

Прежняя версия SQL

Последовательность просмотра страниц пользователями в июле 2017 г.
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

В этом запросе обращения ограничиваются типом PAGES, чтобы исключить события и транзакции. Каждая строка выходных данных представляет просмотр страницы. Строки выводятся в порядке перечисления полей в операторе SELECT.

 

 

Несколько специальных параметров на уровне обращений или сеансов

Специальный параметр на уровне обращений
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

Специальный параметр на уровне сеансов
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

В каждом запросе:

Оператор SELECT запрашивает релевантные поля параметров и показателей.

Функция MAX:

  • Возвращает специальный параметр в новом столбце. Чтобы вернуть несколько специальных параметров в новых столбцах, повторите функцию.
  • Операторы WITHIN hits и WITHIN RECORDS оценивают условие в повторяющихся полях в BigQuery.
  • Условие, заданное функцией MAX, оценивается для каждого специального параметра. Если не выполняется условие index=1 для обращений или index=2 для сеансов, возвращается значение NULL.
  • Возвращается максимальное значение, которое соответствует значению специального параметра 1 для обращений или значению специального параметра 2 для сеансов, поскольку все остальные значения равны NULL.

Примеры сложных запросов

Теперь перейдем к составлению запросов с использованием сложных функций, доступных в BigQuery.

Товары, приобретенные пользователями, также купившими товар А (обычная электронная торговля)

Ниже представлена структура скрипта, позволяющего узнать, какие товары приобретали пользователи, которые также купили товар А.

Товары, приобретенные пользователями, также купившими товар А (обычная электронная торговля)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM ['Название набора данных']
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM ['Название набора данных']
  WHERE hits.item.productName CONTAINS 'Название товара A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Название товара A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. В первой строке выбираются все остальные товары, приобретенные пользователем. Для расчета количества каждого купленного товара используется функция COUNT(). Результат отражается в поле quantity, а соответствующий товар – в поле other_purchased_products.
  2. В подзапросе, выделенном серым цветом, выбираются только уникальные пользователи (fullVisitorId), совершившие транзакции (totals.transactions>=1) и купившие в ходе транзакции товар А (WHERE hits.item.productName CONTAINS 'Product Item Name A').

Правила, заданные операторами WHERE и AND в запросе верхнего уровня, который выделен зеленым цветом, игнорируют значения hits.item.productName, равные NULL и содержащие товар А.

Ниже приведен пример запроса, который позволяет узнать, какие ещё товары приобрел покупатель, купивший Brighton Metallic Pens - Set of 4.

Товары, приобретенные покупателем, который 24 июня 2013 г. купил Brighton Metallic Pens - Set of 4
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

В Dremel/BigQuery оператор WHERE expr IN вызывает команду JOIN. При этом действуют ограничения размера. В частности, размер правой части JOIN (в данном случае это количество посетителей) не должен превышать 8 МБ. В Dremel это называется Broadcast JOIN. Если размер превышает 8 МБ, необходимо использовать Shuffled JOIN с применением синтаксиса JOIN EACH. Для этого нельзя использовать оператор IN, но тот же запрос можно создать с помощью команды JOIN.

Товары, приобретенные пользователями, также купившими товар А (расширенная электронная торговля)

Этот запрос похож на предыдущий, но предназначен для расширенной электронной торговли. В нем для получения данных за несколько дней также используются диапазоны TABLE_DATE_RANGE.

Товары, приобретенные пользователями, также купившими товар А (расширенная электронная торговля)
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Среднее количество взаимодействий пользователя перед покупкой

Это пример запроса с командой JOIN() [...] ON, который зависит только от данных Аналитики.

Ниже представлена структура скрипта, позволяющего узнать среднее количество взаимодействий пользователя перед покупкой.

Количество взаимодействий пользователя перед покупкой
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. В первой строке выполняется основная математическая операция, определяющая среднее количество взаимодействий в расчете на продукт. Этот запрос создает связь между подзапросами ‘Alias_Name_1’ и ‘Alias_Name_2’.
  2. Подзапрос ‘Alias_Name_1’ возвращает поле, в котором с помощью агрегатной функции SUM() суммируются все обращения, зафиксированные для товара.
  3. Подзапрос ‘Alias_Name_2’ определяет количество обращений пользователей в расчете на товар с помощью функции COUNT().
  4. В последней строке указано общее поле (hits.item.productSku), используемое в двух объединенных наборах данных.

Ниже приведен пример запроса, который позволяет узнать среднее количество взаимодействий пользователя перед покупкой 10 сентября 2013 г.

Количество взаимодействий пользователя перед покупкой 10 сентября 2013 г.
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

Процент проданных запасов товара

Рассмотрим пример запроса, в котором используются данные не только из Google Аналитики, но и из других систем. Объединив два набора данных, вы сможете более детально анализировать поведение пользователей. Импортируя данные из других систем в BigQuery, помните, что это повлияет на размер месячного платежа за хранение данных.

Ниже представлена структура скрипта, позволяющего узнать процент проданных запасов товара.

Процент проданных запасов товара
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. В первой строке возвращается два поля: одно содержит все идентификаторы товаров, в другом выполняется математическая операция, определяющая процент проданных запасов товара по его идентификатору.
  2. Поскольку этот запрос зависит от двух наборов данных, необходимо использовать функцию JOIN() ... ON. Она объединяет строки из двух наборов данных по общему полю. В данном случае это наборы данных [ ‘Imported_DataSet’ ] и ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] содержит внешние данные. Этот набор данных включает показатель количества оставшегося товара (Imported DataSet.’stock_left_field’) и параметр идентификатора товара (Imported_DataSet.’productId_field’).
  4. Название ‘Alias_Name’ присваивается данным, которые возвращает подзапрос, выделенный серым цветом. В этом подзапросе используются данные Google Analytics для определения общего количества проданных единиц товара.
  5. Последняя строка содержит оператор ON, отражающий общее для двух наборов данных поле, по которому они объединяются.

Многие переменные этого запроса содержат название набора данных в виде префикса (например, Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Это указывает, какое поле в каком именно наборе данных выбрано.

Ниже приведен пример запроса, который позволяет узнать процент проданных запасов товара 28 июля 2013 г.

Процент запасов товара, проданных 28 июля 2013 г.
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

Прибыльность каждого товара

Ниже представлена структура скрипта, позволяющего узнать прибыльность каждого товара

Прибыльность товаров
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. Первая строка содержит математическую операцию для расчета общей прибыли по каждому товару.
  2. Подзапрос, выделенный серым цветом, использует внешние данные по размеру прибыли от продажи товара.
  3. Подзапрос, выделенный красным цветом, использует данные Google Analytics, которые объединяются с внешними данными. Он рассчитывает количество проданного товара.
  4. Оператор ON в последней строке указывает общее для двух наборов данных поле. В данном случае это идентификатор товара.

Ниже приведен пример запроса, который позволяет узнать прибыльность каждого товара на 28 июля 2013 г.

Прибыльность товаров на 28 июля 2013 г.
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

Прибыль рассчитывается как разность между ценой товара и его себестоимостью. Эта информация хранится во внешнем наборе данных.

Фактическая прибыльность каждого товара (с учетом возвратов)

Ниже представлена структура скрипта, позволяющего узнать фактическую прибыльность каждого товара.

Фактическая прибыльность товаров
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. Этот запрос аналогичен предыдущему. Отличия относятся к внешнему набору данных в подзапросе, выделенном серым цветом, и к математической операции, рассчитывающей фактическую прибыль.
  2. Во внешнем наборе данных также рассчитывается общая сумма возвратов (в операторе SELECT в подзапросе, выделенном красным цветом).
  3. Затем выполняется математическая операция в первой строке, определяющая фактическую прибыль путем вычитания дохода, потраченного на возвраты, из валовой прибыли.

Подробную информацию о запросе читайте в разделе Прибыльность каждого товара.

Ниже приведен пример запроса, который позволяет узнать фактическую прибыльность каждого товара на 28 июля 2013 г.

Фактическая прибыльность товаров на 28 июля 2013 г.
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

При расчете фактической прибыли принимается во внимание прибыльность товара с учетом возвратов. Общий доход, потраченный на возвраты, для товара рассчитывается по следующей формуле:

общий доход, потраченный на возвраты = (цена товара + стоимость доставки товара) * количество возвращенных единиц товара

Эта информация оказалась полезной?

Как можно улучшить эту статью?

Требуется помощь?

Попробуйте следующее:

Опубликовать на справочном форуме Получите ответы от участников сообщества
Связаться с нами Расскажите о своей проблеме нашим сотрудникам
true
learning path
Выберите собственный учебный план

Посетите наш новый сайт google.com/analytics/learn и научитесь использовать Google Аналитику 4 максимально эффективно. На этом сайте вы найдете множество полезных ресурсов: видеоролики, статьи и пошаговые инструкции, а также ссылки на наш блог, сервер Google Аналитики на Discord, канал YouTube и репозиторий GitHub.

Присоединяйтесь!

Поиск
Очистить поле поиска
Закрыть поиск
Главное меню
6860975878728668426
true
Поиск по Справочному центру
true
true
true
true
true
69256
false
false