Практическо ръководство за BigQuery

Тази функция е налице само за Google Анализ 360 – част от Google Marketing Platform.
Научете повече за Google Marketing Platform.

Тази статия съдържа примери за това как да съставяте заявки за данните от Анализ, които експортирате в 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 Анализ.

За да използвате заявките за собствените си данни, просто заменете имената на проекта и набора от данни в примерите с тези на собствения си проект и набор от данни.

Използване на стандартен SQL спрямо наследен SQL

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

В Преминаване към стандартен SQL се обясняват разликите между двата диалекта.

Стандартният SQL вече е предпочитаният SQL диалект за изпращане на заявки за данни, съхранявани в BigQuery.

Вижте Активиране на стандартен SQL за информация относно активирането на стандартния SQL в потребителския интерфейс на BigQuery, в CLI, приложния програмен интерфейс (API) или в друг интерфейс, който използвате.

Най-лесният начин да започнете е да включите коментара „standardSQL“ в горната част на заявките си за стандартен SQL, както е показано в примерите по-долу.

Чрез наследения SQL данните от Google Анализ 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
конкретен период от време с използване на ABLE_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

 

 

Примери за основни заявки

Този раздел обяснява как да съставяте основни заявки с помощта на показатели и величини от примерни данни от Анализ.

Общо [показател] на [величина]?

Следват примерни скриптове за въпроса: Какъв е общият брой на транзакциите, генерирани на браузър на устройство през юли 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 [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name 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 'Име на елемент на продукт А').

Правилата (изразите 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 '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;

Процент продадена стока по продукт

Това е пример за заявка, която зависи не само от данни от Анализ, но също и от данни, които не са от Анализ. Чрез комбиниране на двата набора от данни можете да започнете да разбирате поведението на потребителите на едно по-сегментирано ниво. Можете да импортирате данни, които не са от Анализ, в 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 Анализ, за да установи общото количество на продадените артикули по продукт.
  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. Червената подзаявка е подзаявката към данните от Анализ, които ще бъдат обединени с тези, които не са от Анализ. Тя изчислява количеството на продадените артикули по продукт.
  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 );

Печалбата се изчислява, като се установи разликата между продажната цена на продукта и разходите за производството му. Тази информация се съхранява в набора от данни, които не са от Google Анализ.

Реална доходност на всеки продукт (като се вземат предвид възстановяванията на суми)

Следва основен скрипт за въпроса: Каква е реалната доходност на всеки продукт?

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

Започнете обучението си още днес!

Търсене
Изчистване на търсенето
Затваряне на търсенето
Главно меню
7545360443130863308
true
Търсене в Помощния център
true
true
true
true
true
69256
false
false