BigQuery упутства

Ова функција је доступна само у Аналитици 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-а у корисничком интерфејсу, интерфејсу командне линије или API-ју BigQuery-ја, односно било ком интерфејсу који користите.

Најлакши начин да започнете је да уврстите коментар standardSQL у врх стандардних SQL упита, као што је приказано у следећим примерима.

Подаци Google аналитике 360 се сваког дана прослеђују у нову табелу помоћу застарелог SQL-а. Да бисте послали упит за више табела одједном, можете да раздвојите називе табела зарезом, да користите функцију џокерског знака за табелу 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 аналитике јер тренутно нема дневне табеле.
#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

 

 

Примери основних упита

Овај одељак објашњава како да правите основне упите помоћу показатеља и аспеката из примера података Aналитике.

Укупан број [показатељ] по [аспект]?

У наставку су примери скрипти за питање: Који је укупан број трансакција генерисаних по прегледачу на уређају јула 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 се процењује за сваки прилагођени аспект, али враћа NULL за оне који нису index=1 (hits) или index=2 (sessions).
  • Враћа максималну вредност, која представља вредност за Прилагођени аспект 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 'Product Item Name A').

Правила (изјаве WHERE и AND) у (зеленом) упиту највишег нивоа занемарују вредности у hits.item.productName које су null и садрже производ А.

Ево примера упита Ако клијент купи „Brighton Metallic Pens - Set of 4“, које је још друге производе купио?

производи које је купио клијент који је купио „Brighton Metallic Pens (Set of 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;

Употреба функције WHERE expr IN у Dremel-у или BigQuery-у покреће JOIN и важе ограничења за величину. Конкретно, величина десне стране команде JOIN (у овом случају број посетилаца) мора да буде мања од 8 MB. Ово се у Dremel-у зове broadcast JOIN. Ако величина премашује 8 MB, морате да покренете 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’ је назив које је додељено подацима које је вратио сиви подупит. Овај подупит користи податке Аналитике да би открио укупну количину артикала продатих по производу.
  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. Нови веб-сајт садржи видео снимке, чланке и детаљне водиче и садржи линкове до Discord-а Google аналитике, блога, YouTube канала и GitHub складишта.

Почните да учите данас!

Претрага
Обриши претрагу
Затвори претрагу
Главни мени
15806755398924379872
true
Центар за помоћ за претрагу
true
true
true
true
true
69256
false
false