Довідник із BigQuery

Ця функція доступна лише в Analytics 360, що є сервісом Google Marketing Platform.
Докладніше про Google Marketing Platform.

У цій статті описано, як формувати запити на отримання даних Analytics, які експортуються в BigQuery. Ви також зможете попрацювати з деякими описаними запитами на прикладі зразка набору даних.

Зміст

Оптимізація запитів

Кожен запит збільшує щомісячну плату за обробку даних. Зайві поля збільшують обсяг даних, які необхідно обробити. Це призводить до необґрунтованого підвищення щомісячних витрат. Оптимізовані запити дають змогу зменшити щомісячні витрати на обробку даних.

Докладніше про розрахунок ціни.

Вибирайте лише потрібні поля

Під час формулювання запиту рекомендуємо вибирати за допомогою оператора SELECT лише релевантні поля. Якщо не викликати зайві поля, можна зменшити обсяг даних і час обробки запиту.

Приклад. Уникайте використання оператора підстановки

Погана форма: використовується оператор підстановки
SELECT *
FROM [table name];

 

Краща форма: використовуються назви полів для зменшення часу обробки
SELECT field1, field2
FROM [table name];

Дозвіл на кешування

Якщо це можливо, уникайте використання функцій як полів. Функції (як-от NOW() або TODAY()) повертають результати змінних. Це не дає запитам кешуватися й пришвидшує виведення результату. Натомість використовуйте конкретні значення часу й дати.

Наразі кешовані результати не підтримуються для запитів до кількох таблиць із використанням символу підстановки, навіть якщо встановлено прапорець "Використовувати кешовані результати". Якщо ви надсилатимете кілька запитів із символом підстановки, буде стягнено платню за кожний із них. Докладніше

Використовуйте проміжні таблиці для часто використовуваних підзапитів

Якщо ви постійно використовуєте конкретний запит як підзапит, збережіть його як проміжну таблицю, натиснувши кнопку Зберегти як таблицю над результатами запиту. Тоді в подальших запитах ви зможете посилатися на цю таблицю в розділі FROM. Це призведе до зменшення обсягу даних і пришвидшить обробку.

використання проміжної таблиці
SELECT field1, field2
FROM [Dataset name.table name];

Налагодження запитів

У BigQuery код налагоджується під час створення. У вікні створення статус налагодження показується прямо під запитом. Налагодження також доступне через API за допомогою позначки dryRun.

Дійсні запити позначені індикатором зеленого кольору. Його можна натиснути, щоб побачити обсяг оброблених запитом даних. Ця функція дає змогу оптимізувати дані перед виконанням запиту, щоб уникнути обробки необов’язкової інформації.

Query Debugging - Success

 

Недійсні запити мають індикатор червоного кольору, за натискання якого відображається інформація про помилку та вказується рядок і стовпець, де сталася помилка. У наведеному нижче прикладі оператор GROUP BY порожній і вказано помилку.

Query Debugging - Error

 

Рекомендації та практичні поради

Як використовувати зразок набору даних

У наведених нижче прикладах використовується зразок набору даних Google Analytics.

Щоб застосувати запити до власних даних, просто замініть назви проекту й набору даних у зразках власними назвами.

Як використовувати стандартну та застарілу версії SQL

У BigQuery підтримуються два діалекти SQL.

У статті Як перейти на стандартну версію SQL описано відмінності між цими двома діалектами.

Для запитів даних, збережених у BigQuery, зараз рекомендованим діалектом SQL є його стандартна версія.

Перегляньте цю статтю, щоб дізнатися більше про ввімкнення стандартної версії SQL у BigQuery, CLI, API або будь-якому іншому інтерфейсі, що ви використовуєте.

Найпростіший спосіб розпочати роботу – додати коментар "standardSQL" угорі запитів у стандартній версії SQL так, як показано на прикладах нижче.

Якщо використовується застаріла версія SQL, дані Google Analytics 360 групуються в окремі таблиці для кожного дня. Щоб отримати дані кількох таблиць одночасно, можна включити їх назви в запит, розділивши їх комами, скористатися функцією TABLE_DATE_RANGE (функція символів підстановки) або застосувати кілька розділених комами функцій TABLE_DATE_RANGE, як показано на прикладах нижче.

Запити на отримання даних кількох таблиць

У прикладах нижче показано запити на отримання даних у стандартній і застарілій версіях 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 Analytics, оскільки в ньому наразі немає проміжної таблиці за день.
#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 Analytics, оскільки в ньому наразі немає проміжної таблиці за день.
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

 

 

Приклади основних запитів

У цьому розділі описано, як створювати основні запити за допомогою показників і параметрів із даних Analytics.

Яка загальна кількість [показник] для кожного [параметр]?

Нижче наведено приклади скриптів для такого запитання: Яка загальна кількість трансакцій для кожного веб-переглядача пристрою в липні 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 RECORD обчислюють умову в повторюваних полях у BigQuery.
  • Умова у функції MAX оцінюється для кожного спеціального параметра, але для тих, які не є index=1 (hits) або index=2 (sessions) повертається значення 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 Назва товару А
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != Назва товару А
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. У першому рядку ви вибираєте всі інші товари, придбані користувачем, і за допомогою агрегатної функції COUNT() розраховуєте кількість інших куплених товарів. Результат з’явиться в полі quantity, а відповідний товар – у полі other_purchased_products.
  2. У сірому підзапиті ви вибираєте лише унікальних користувачів (fullVisitorId), які здійснили трансакції (totals.transactions>=1) і під час трансакції придбали товар A (WHERE hits.item.productName CONTAINS Назва товару А).

Правила (оператори WHERE та AND) у запиті вищого рівня (зеленого кольору) ігнорують значення параметра hits.item.productName, які дорівнюють нулю й містять товар А.

Нижче наведено приклад такого запиту: Клієнт купив товар "Кулькові ручки (4 шт.)". Які ще товари він придбав?

товари, придбані клієнтом, який купив товар "Кулькові ручки (4 шт.)" 24 червня 2013 року
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 Кулькові ручки (4 шт.)
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !=Кулькові ручки (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, яка залежить лише від даних Analytics.

Нижче наведено схематичний скрипт для такого запитання: Яка середня кількість взаємодій із користувачем перед покупкою?

кількість взаємодій із користувачем перед покупкою
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 ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS Псевдонім_1
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [Назва набору даних GA ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS Псевдонім_2
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. У першому рядку виконується основна математична операція з пошуку середньої кількості взаємодій користувача на товар. Цей запит створює зв’язок між двома підзапитами Псевдонім_1 і Псевдонім_2.
  2. Псевдонім_1 використовується для отримання поля, у якому за допомогою агрегатної функції SUM() додаються всі звернення, зареєстровані для товару.
  3. Псевдонім_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;

Відсоток запасу, проданого для кожного товару

Це приклад запиту, який залежить не лише від даних Analytics. Об’єднавши два набори даних, можна детальніше проаналізувати поведінку користувачів. Другий набір можна імпортувати в 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 [ Імпортований набір даних ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [Набір даних GA ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS Псевдонім
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. Перший рядок виводить два поля: одне містить усі ідентифікатори товарів, а інше є математичною операцією, за допомогою якої знаходимо відсоток запасу, проданого для цих товарів.
  2. Оскільки цей запит використовує два набори даних, для нього потрібна функція JOIN() ... ON. Ця команда об’єднує на основі спільного поля рядки з двох наборів даних (тут – [Імпортований набір даних] і Псевдонім).
  3. [Імпортований набір даних] – це дані зі сторонніх сервісів (не Analytics). Цей набір містить поле показника запасу, що залишився (Imported DataSet.’stock_left_field’), і поле параметра "Ідентифікатор товару" (Imported_DataSet.’productId_field’).
  4. Псевдонім – це назва, що призначається даним, які повернуто сірим підзапитом. Цей підзапит використовує дані 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 [ Імпортований набір даних ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ Назва набору даних GA ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS Псевдонім
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. Перший рядок містить математичну операцію, необхідну для підрахунку загального прибутку від кожного товару.
  2. Сірий підзапит використовує дані додаткового набору даних про розмір прибутку від продажу товару.
  3. Червоний підзапит використовує дані 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 [ Назва імпортованого набору даних ] AS Імпортований набір даних
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [Назва набору даних GA ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS Псевдонім
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. Цей запит дуже схожий на запит Який прибуток від кожного товару? Різниця лише в тому, що сірий підзапит використовує додатковий набір даних, а перший рядок містить математичну операцію підрахунку реального прибутку.
  2. На основі імпортованого набору даних також підраховується загальна сума, витрачена на повернення коштів (в операторі SELECTчервоного підзапиту).
  3. Відтак виконується математична операція з рядка 1, яка показує реальний прибуток (для цього від валового прибутку віднімається дохід, витрачений на повернення коштів).

Докладніше про запит читайте в розділі Прибуток від кожного товару.

Нижче наведено приклад такого запиту для запитання: Який реальний прибуток отримано від кожного товару 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 Analytics 4 з максимальною користю. На цьому веб-сайті ви знайдете відео, статті й покрокові вказівки, а також посилання на Google Analytics Discord, блог, канал YouTube і сховище GitHub.

Почніть навчання вже сьогодні!

Пошук
Очистити пошук
Закрити пошук
Головне меню
14150537545551928338
true
Пошук у довідковому центрі
true
true
true
true
true
69256
false
false