Príručka nástroja BigQuery

Táto funkcia je k dispozícii iba v službe Analytics 360, ktorá je súčasťou súpravy služieb Google Marketing Platform.
Ďalšie informácie o súprave služieb Google Marketing Platform

Tento článok obsahuje príklady zostavovania dopytov na údaje v službe Analytics, ktoré exportujete do nástroja BigQuery. Pripravili sme pre vás vzorovú množinu dát, na ktorej môžete testovať dopyty uvedené v tomto článku.

Obsah tohto článku:

Optimalizácia dopytov

Každý spustený dopyt sa započítava do vášho mesačného povoleného limitu spracúvania údajov. Ak vyberiete doplňujúce polia, zvýši sa množstvo spracúvaných údajov a zo svojho mesačného limitu spotrebujete viac, než je nutné. Optimalizované dopyty využívajú mesačný povolený limit spracúvania údajov účinnejšie.

Ďalšie informácie o cenách

Výber len tých informácií, ktoré sú potrebné

Pri zostavovaní dopytu vyberte v dopyte typu SELECT iba relevantné polia. Nevolaním nadbytočných polí sa znižuje množstvo údajov a čas potrebný na spracovanie dopytu.

Príklad: Nepoužívajte operátor zástupného znaku.

Chybný tvar: používanie operátora zástupného znaku
SELECT *
FROM [názov tabuľky];

 

Lepší tvar: zrýchlené spracovanie pomocou názvov polí
SELECT pole1, pole2
FROM [názov tabuľky];

Povolenie ukladania do vyrovnávacej pamäte

Podľa možností nepoužívajte funkcie ako polia. Funkcie (napríklad NOW() alebo TODAY()) vracajú premenlivé výsledky, ktoré zabraňujú ukladaniu dopytov do vyrovnávacej pamäte, teda ich rýchlejšiemu získaniu. Namiesto nich použite konkrétne časy a dátumy.

Pre dopyty na viaceré tabuľky, ktoré používajú zástupný znak, momentálne nie sú podporované výsledky z vyrovnávacej pamäte, ani keď je začiarknutá možnosť Použiť výsledky z vyrovnávacej pamäte. Ak spustíte rovnaký dopyt so zástupným znakom viackrát, bude vám účtovaný každý dopyt. Ďalšie informácie

Používanie dočasných tabuliek pre často používané subdopyty

Ak zistíte, že opakovane používate konkrétny dopyt ako subdopyt, tento dopyt môžete uložiť ako dočasnú tabuľku. Kliknite na tlačidlo Uložiť ako tabuľku nad výsledkami dopytu. Na túto tabuľku potom môžete odkázať v sekcii dopytu FROM, čím sa zníži množstvo spracúvaných údajov a čas potrebný na spracovanie.

Použitie dočasnej tabuľky
SELECT pole1, pole2
FROM [názov množiny údajov názov.tabuľka];

Ladenie dopytu

BigQuery ladí váš kód už počas jeho vytvárania. V okne kompozície je ladenie znázornené hneď pod dopytom. Ladenie je dostupné aj prostredníctvom rozhrania API pomocou príznaku dryRun.

Platné dopyty majú zelený indikátor. Kliknutím naň sa zobrazí množstvo údajov spracúvaných dopytom. Táto funkcia umožňuje optimalizovať údaje ešte pred spustením dopytu, aby sa zabránilo nadbytočnému spracúvaniu údajov.

Query Debugging - Success

 

Neplatné dopyty majú červený indikátor. Kliknutím naň sa zobrazia informácie o chybe, a následne sa vyhľadá riadok a stĺpec, kde sa chyba vyskytuje. V príklade uvedenom nižšie je výraz GROUP BY prázdny a chyba je presne vymedzená.

Query Debugging - Error

 

Tipy a osvedčené postupy

Použitie vzorovej množiny údajov

V nasledujúcich príkladoch sa používa vzorová množina údajov služby Google Analytics.

Ak chcete dopyty používať s vlastnými dátami, jednoducho nahraďte názvy projektov a množín dát v príkladoch názvami vlastných projektov a množín dát.

Použitie štandardného alebo starého dialektu SQL

BigQuery podporuje dva dialekty SQL:

Migrácia na štandardný dialekt SQL vysvetľuje rozdiely medzi týmito dvoma dialektmi.

Štandardný dialekt SQL je v súčasnosti preferovaným dialektom jazyka SQL pre dopyty na údaje uložené v nástroji BigQuery.

Informácie o tom, ako povoliť štandardný dialekt SQL v používateľskom rozhraní nástroja BigQuery, pomocou príkazov CLI, rozhrania API alebo ľubovoľného iného rozhrania, nájdete v článku Povolenie štandardného dialektu SQL.

Najjednoduchšie začnete tak, že navrchu svojich dopytov v štandardnom dialekte SQL uvediete komentár standardSQL, ako je uvedené v nasledujúcich príkladoch.

V prípade starého dialektu SQL sa údaje služby Google Analytics 360 každý deň odovzdávajú do novej tabuľky. Ak chcete vytvárať dopyty na viaceré tabuľky naraz, môžete názvy tabuliek oddeliť čiarkami, použiť funkciu zástupného znaku tabuľky TABLE_DATE_RANGE alebo viaceré funkcie TABLE_DATE_RANGE oddelené čiarkou tak, ako je to uvedené v nasledujúcich príkladoch.

Dopyt vo viacerých tabuľkách

Nasledujúce príklady znázorňujú dopyty v štandardnom dialekte SQL a starom dialekte SQL spustené nad rovnakými dátami.

3 dni

Štandardný dialekt SQL
Tri dni pomocou príkazu 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
Starý dialekt SQL
Tri dni použitím názvov tabuliek oddelených čiarkou
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

 

Posledných 1 095 dní

Štandardný dialekt SQL
Posledných 1 095 dní použitím výrazu _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
Starý dialekt SQL
Posledných 1 095 dní použitím funkcie 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

 

Posledných 36 mesiacov

Štandardný dialekt SQL
Posledných 36 mesiacov použitím výrazu _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
Starý dialekt SQL
Posledných 36 mesiacov použitím funkcie 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

 

Posledné tri roky

Štandardný dialekt SQL
Posledné tri roky použitím výrazu _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
Starý dialekt SQL
Posledné tri roky použitím funkcie 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

 

Konkrétne obdobie

Štandardný dialekt SQL
Konkrétne obdobie použitím výrazu _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
Starý dialekt SQL
Konkrétne obdobie použitím funkcie 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

Posledné tri roky vrátane dnešných údajov (počas dňa)

Štandardný dialekt SQL
Posledné 3 roky vrátane dnešných dát (počas dňa) použitím príkazu UNION ALL a výrazu _TABLE_SUFFIX
Upozornenie: Tento príklad dopytu nebude fungovať s verejnou množinou údajov služby Google Analytics, pretože v súčasnosti nie je k dispozícii tabuľka denných údajov.
#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

Starý dialekt SQL

Posledné 3 roky vrátane dnešných dát (počas dňa) použitím viacerých funkcií TABLE_DATE_RANGE
Upozornenie: Tento príklad dopytu nebude fungovať s verejnou množinou údajov služby Google Analytics, pretože v súčasnosti nie je k dispozícii tabuľka denných údajov.
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

 

 

Príklady základných dopytov

Táto sekcia vysvetľuje, ako zostavovať základné dopyty pomocou metrík a dimenzií zo vzorových údajov služby Analytics.

Celková [metrika] na [dimenziu]?

Nižšie uvádzame ukážky skriptov pre otázku Aký je celkový počet transakcií vygenerovaných na prehliadač zariadenia v júli 2017?

Štandardný dialekt SQL

Celkový počet transakcií na prehliadač v zariadení v júli 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

Starý dialekt SQL

Celkový počet transakcií na prehliadač v zariadení v júli 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

 

 

Aká je priemerná miera okamžitých odchodov na [ dimenziu ]?

Skutočná miera okamžitých odchodov je definovaná ako percento návštev s jedným zobrazením stránky. Nižšie uvádzame príklady skriptov pre otázku Aká bola skutočná miera okamžitých odchodov na zdroj návštevnosti?

Štandardný dialekt SQL

Miera okamžitých odchodov na zdroj návštevnosti v júli 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

Starý dialekt SQL

Miera okamžitých odchodov na zdroj návštevnosti v júli 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

 

 

Priemerný počet zobrazení stránky výrobku podľa typu zákazníka (zákazníci v porovnaní s nekupujúcimi)

Nižšie nájdete príklady skriptov pre otázku Aký bol priemerný počet zobrazení stránky produktu pre používateľov, ktorí uskutočnili nákup v júli 2017?

Štandardný dialekt SQL

Priemerný počet zobrazení stránky výrobku používateľmi, ktorí uskutočnili nákup v júli 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 )

Starý dialekt SQL

Priemerný počet zobrazení stránky výrobku používateľmi, ktorí uskutočnili nákup v júli 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 )

 

 

Nižšie uvádzame príklady skriptov pre otázku Aký bol priemerný počet zobrazení stránky výrobku používateľmi, ktorí neuskutočnili nákup v júli 2017?

Štandardný dialekt SQL

Priemerný počet zobrazení stránky výrobku používateľmi, ktorí neuskutočnili nákup v júli 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 )

Starý dialekt SQL

Priemerný počet zobrazení stránky výrobku používateľmi, ktorí neuskutočnili nákup v júli 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 )

 

 

Priemerný počet transakcií na zákazníka

Nižšie uvádzame príklady skriptov pre otázku Aký bol celkový priemerný počet transakcií na používateľa, ktorý uskutočnil nákup v júli 2017?

Štandardný dialekt SQL

Priemerný počet transakcií na používateľa, ktorý uskutočnil nákup v júli 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 )

Starý dialekt SQL

Priemerný počet transakcií na používateľa, ktorý uskutočnil nákup v júli 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 )

 

 

Priemerná minutá suma peňazí na reláciu

Nižšie nájdete príklady skriptov pre otázku Aká je priemerná minutá suma peňazí na reláciu v júli 2017?

Štandardný dialekt SQL

Priemerná minutá suma peňazí na reláciu v júli 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 )

Starý dialekt SQL

Priemerná minutá suma peňazí na reláciu v júli 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 )

 

 

Sekvencia prístupov

Nižšie uvádzame príklady skriptov pre otázku Aká je sekvencia zobrazených stránok?

Štandardný dialekt SQL

Sekvencia zobrazených stránok používateľmi v júli 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

Starý dialekt SQL

Sekvencia zobrazených stránok používateľmi v júli 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

V tomto dopyte obmedzíte typ prístupov na typ PAGE, aby sa nezobrazovali interakcie udalostí alebo transakcií. Každý riadok výstupu predstavuje zobrazenie stránky a výstup sa zobrazuje s predvoleným zoradením polí vo výraze SELECT.

 

 

Viaceré vlastné dimenzie na úrovni prístupu alebo relácie

Vlastná dimenzia na úrovni prístupu
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

 

Vlastná dimenzia na úrovni relácie
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

V každom dopyte:

Výrazom dopytu SELECT sa získajú relevantné polia dimenzie a metriky.

Funkcia MAX:

  • Vráti vlastnú dimenziu ako nový stĺpec. Opakovaným použitím tejto funkcie môžete získať viaceré vlastné dimenzie ako nové stĺpce.
  • Výrazy WITHIN hits a WITHIN RECORD vyhodnocujú podmienku vnútri opakujúcich sa polí v nástroji BigQuery.
  • Podmienka vnútri funkcie MAX sa vyhodnotí pre každú vlastnú dimenziu, no v prípade tých, kde neplatí index=1 (prístupy) alebo index=2 (relácie), vráti hodnotu NULL.
  • Vráti maximálnu hodnotu, ktorá je hodnotou vlastnej dimenzie 1 pre prístupy alebo vlastnej dimenzie 2 pre relácie, keďže nie všetky hodnoty sú NULL.

Príklady pokročilých dopytov

Keď už viete zostavovať jednoduché dopyty, ukážeme si vytváranie dopytov pomocou pokročilých funkcií a funkcií dostupných v nástroji BigQuery.

Produkty zakúpené zákazníkmi, ktorí si zakúpili produkt A (Klasický elektronický obchod)

Nižšie je uvedená kostra skriptu pre otázku Aké ďalšie produkty si zakúpili zákazníci, ktorí si zakúpili produkt A?

Výrobky zakúpené zákazníkom, ktorý si zakúpil výrobok A (klasický elektronický obchod)
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 'názov položky výrobku A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'názov položky výrobku A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. V prvom riadku vyberiete všetky ostatné položky zakúpené používateľom a agregačná funkcia COUNT() vypočíta množstvo každej ďalšej zakúpenej položky. Výsledok sa potom zobrazí v poli označenom quantity s priradenou položkou v poli výrobku označenom other_purchased_products.
  2. V sivom čiastkovom dopyte vyberiete iba jedinečných používateľov (fullVisitorId), ktorí uskutočnili transakcie (totals.transactions>=1) a počas transakcie si zakúpili výrobok A (WHERE hits.item.productName CONTAINS 'názov položky výrobku A').

Pravidlá (výrazy WHERE a AND) v dopyte na najvyššej úrovni (text so zelenou farbou) vylúčia tie hodnoty poľa hits.item.productName, ktoré sú null a obsahujú výrobok A.

Tu je príklad dopytu Ak si zákazník zakúpi metalické perá Brighton – sada 4 ks, aké ďalšie produkty si tiež zakúpil?

Výrobky zakúpené zákazníkom, ktorý si zakúpil výrobok Metalické perá Brighton (súprava 4 ks) 24. júna 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 'Metalické perá Brighton (súprava 4 ks)'
   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;

V nástroji Dremel/BigQuery sa použitím výrazu WHERE expr IN spustí príkaz JOIN a uplatní sa obmedzenie veľkosti. Konkrétne veľkosť pravej strany príkazu JOIN (v tomto prípade počet návštevníkov) musí byť menšia ako 8 MB. V nástroji Dremel sa tento príkaz nazýva broadcast JOIN. Keď veľkosť prekročí 8 MB, musíte spustiť príkaz shuffled JOIN, čo môžete vykonať pomocou syntaxe JOIN EACH. Táto akcia sa nedá vykonať pomocou výrazu IN, no ten istý dopyt môžete prepísať použitím príkazu JOIN.

Produkty zakúpené zákazníkmi, ktorí si zakúpili produkt A (Rozšírený elektronický obchod)

Podobá sa to na predošlú kostru dopytu, ale funguje v Rozšírenom elektronickom obchode. Používa aj funkciu TABLE_DATE_RANGE pri odosielaní dopytov na údaje za viacero dní.

Výrobky zakúpené zákazníkom, ktorý si zakúpil výrobok A (rozšírený elektronický obchod)
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;

Priemerný počet interakcií používateľa pred nákupom

Tu je príklad dopytu s použitím príkazu JOIN() […] ON, ktorý závisí iba od údajov služby Analytics.

Nižšie je kostra skriptu pre otázku Aký je priemerný počet interakcií používateľov pred nákupom?

Počet interakcií používateľov pred nákupom
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. V prvom riadku sa vykoná hlavná matematická operácia, ktorá vyhľadá priemerný počet interakcií používateľov na výrobok, a tento dopyt vytvorí spojenie medzi dvoma čiastkovými dopytmi pomenovanými Alias_Name_1 a Alias_Name_2.
  2. Čiastkový dopyt Alias_Name_1 vytvorí pole, ktoré pomocou agregačnej funkcie SUM() sčíta všetky počty prístupov zaznamenaných pre daný výrobok.
  3. Čiastkový dopyt Alias_Name_2 pomocou funkcie COUNT() vyhľadá počet prístupov na výrobok vygenerovaných používateľmi.
  4. Posledný riadok obsahuje spoločné pole (hits.item.productSku), ktoré v spojení zdieľajú obe množiny údajov.

Tu je príklad dopytu Aký je priemerný počet interakcií používateľov pred nákupom dňa 10. septembra 2013?

Počet interakcií používateľov pred nákupom 10. septembra 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;

Percento predaných zásob na výrobok

Tu je príklad dopytu, ktorý nezávisí iba od údajov služby Analytics, no závisí aj od údajov mimo služby Analytics. Spojenie oboch množín údajov vám pomôže pochopiť správanie používateľov na viac segmentovanej úrovni. Do nástroja BigQuery môžete importovať aj údaje mimo služby Analytics. Pripomíname však, že import údajov vám zaúčtujeme do mesačných nákladov na úložisko údajov.

Nižšie je uvedená kostra skriptu pre otázku Aké je percento predaných zásob na produkt?

Percento predaných zásob na výrobok
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. Prvý riadok vypíše dve polia – jedno pole obsahuje všetky identifikátory výrobkov a druhé pole je matematická operácia, ktorou sa vypočíta percento predaných zásob výrobku s daným identifikátorom.
  2. Keďže tento dopyt pracuje nad dvoma množinami údajov, musíte použiť funkciu JOIN() … ON. Tento príkaz spojí riadky z dvoch množín údajov na základe ich spoločného poľa. V tomto prípade ide o množiny údajov [ 'Imported_DataSet' ] a 'Alias_Name'.
  3. Množina [ ‘Imported_DataSet’ ] obsahuje údaje mimo služby Analytics. Táto množina údajov obsahuje pole metriky, ktoré predstavuje zostávajúce množstvo tovaru (Imported DataSet.’stock_left_field’) a pole dimenzie identifikátora výrobku (Imported_DataSet.’productId_field’).
  4. Názov ‘Alias_Name’ je priradený k údajom vráteným sivým čiastkovým dopytom. Tento čiastkový dopyt vyhľadá celkové množstvo predaných položiek na výrobok v údajoch služby Analytics.
  5. Posledný riadok obsahuje výraz ON, ktorý určuje spoločné pole oboch množín údajov. Toto pole zároveň znázorňuje, kde boli obe množiny údajov spojené.

Mnohé premenné v tomto dopyte obsahujú názov množiny údajov na začiatku svojho názvu (napr. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Cieľom je znázorniť pole, ktoré vyberáte, a ku ktorej množine údajov patrí.

Tu je príklad dopytu Aké je percento predaných zásob na produkt dňa 28. júla 2013?

Percento predaných zásob na výrobok 28. júla 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;

Ziskovosť jednotlivých výrobkov

Nižšie je kostra skriptu pre otázku Aká je ziskovosť každého produktu?

Zisk podľa výrobku
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. Prvý riadok obsahuje matematickú operáciu na výpočet celkového dosiahnutého zisku pre jednotlivé výrobky.
  2. Sivý subdopyt používa údaje mimo služby Analytics, ktorým sa zhromaždia údaje o výške dosiahnutého zisku pri predaní daného produktu.
  3. Červený subdopyt je subdopyt údajov služby Analytics, ktorý sa spojí s údajmi mimo služby Analytics. Tento dopyt vypočíta množstvo predaných položiek na produkt.
  4. Posledný riadok používa výraz ON, ktorým sa určí pole zdieľané medzi oboma množinami údajov. V tomto prípade ide o identifikátor produktu.

Tu je príklad dopytu Aká bola ziskovosť každého produktu dňa 28. júla 2013?

Zisk podľa výrobku 28. júla 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 );

Zisk sa vypočíta ako rozdiel medzi predajnou cenou výrobku a nákladmi na jeho výrobu. Tieto informácie sú uložené v množine údajov mimo služby GA.

Skutočná ziskovosť každého produktu (pri zohľadnení vrátení platieb)

Nižšie je kostra skriptu pre otázku Aká je skutočná ziskovosť každého produktu?

Skutočný zisk podľa výrobku
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. Tento dopyt sa veľmi podobá dopytu Aká je ziskovosť jednotlivých výrobkov?. Jediné rozdiely sa nachádzajú v množine údajov mimo služby Analytics v sivom subdopyte a v matematickej operácii, ktorá počíta celkový zisk v prvom riadku.
  2. V množine údajov nepochádzajúcich zo služby Analytics počítate aj celkovú sumu vrátených peňazí (vo výraze SELECT červeného čiastkového dopytu).
  3. Následne sa v prvom riadku vykoná matematická operácia, ktorá vypočíta skutočný zisk tak, že od hrubého zisku odpočíta výnosy spotrebované na vrátenie peňazí.

Ďalšie informácie o dopyte nájdete v sekcii o ziskovosti jednotlivých výrobkov.

Tu je príklad dopytu Aká bola skutočná ziskovosť každého produktu dňa 28. júla 2013?

Skutočný zisk podľa výrobku 28. júla 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 ) );

Pri skutočnom zisku sa zohľadní ziskovosť výrobku po zohľadnení výrobkov, za ktoré boli vrátené peniaze. Celkové výnosy z vrátených platieb za konkrétny produkt vypočítate takto:

Celkové výnosy z vrátených platieb za výrobok = ( cena výrobku + cena za doručenie výrobku pri vrátení platby) * množstvo výrobkov, za ktoré boli vrátené peniaze

Pomohlo vám to?

Ako ju môžeme zlepšiť?

Potrebujete ďalšiu pomoc?

Vyskúšajte tieto ďalšie kroky:

Uverejniť v komunite pomoci Získať odpovede od členov komunity
Kontaktujte nás Povedzte nám viac a my vám pomôžeme
true
learning path
Vyberte si vlastnú sériu kurzov

Pozrite si google.com/analytics/learn, nový zdroj, ktorý vám pomôže vyťažiť zo služby Google Analytics 4 maximum. Na novom webe sa nachádzajú videá, články a návody, ako aj odkazy na rôzne zdroje o službe Google Analytics (Discord, blog, kanál na YouTube a odkladací priestor GitHub).

Začnite s učením ešte dnes!

Vyhľadávanie
Vymazať vyhľadávanie
Zavrieť vyhľadávanie
Hlavná ponuka
13189897496619789573
true
Vyhľadávanie v Centre pomoci
true
true
true
true
true
69256
false
false