Příručka k nástroji BigQuery

Tato funkce je k dispozici pouze ve službě Analytics 360, která je součástí Google Marketing Platform.
Další informace o sadě služeb Google Marketing Platform.

V tomto článku jsou uvedeny příklady sestavování dotazů na údaje z Analytics, které exportujete do nástroje BigQuery. Vytvořili jsme pro vás ukázkový soubor dat, který můžete použít, abyste si sami vyzkoušeli některé z dotazů v tomto článku.

Obsah tohoto článku:

Optimalizace dotazu

Každý spuštěný dotaz zvyšuje vaše měsíční vyhrazené množství zpracovávaných údajů. Pokud vyberete dodatečná pole, zvýšíte množství údajů, které bude nutné zpracovat. V důsledku toho spotřebujete větší část svého měsíčního vyhrazeného množství, než je nutné. Optimalizované dotazy vám umožní efektivně využívat měsíční vyhrazené množství zpracovávaných údajů.

Další informace o tvorbě cen

Vybírejte pouze pole, která potřebujete

Při formulování dotazu vyberte v příkazu SELECT příslušná pole. Pokud chcete snížit množství údajů a času potřebné na zpracování dotazu, nevolejte dodatečná pole.

Příklad: nepoužívejte zástupné znaky

Špatná forma: použití zástupného znaku
SELECT *
FROM [table name];

 

Lepší forma: když použijete názvy polí, vyhnete se nadbytečnému zpracování
SELECT field1, field2
FROM [table name];

Povolte ukládání do mezipaměti

Pokud to jde, vyhněte se používání funkcí jako polí. Funkce jako NOW() nebo TODAY() vracejí proměnné výsledky, což brání ukládání dotazů do mezipaměti a tím jejich rychlejšímu vracení. Použijte raději konkrétní data a časy.

V současnosti není ukládání výsledků do mezipaměti podporováno pro dotazy na několik tabulek používající zástupné symboly, a to ani pokud je zapnuta možnost Použít ukládání výsledků do mezipaměti. Pokud stejný dotaz se zástupnými symboly provedete několikrát, bude vám účtováno každé jeho provedení. Další informace

Na často používané dílčí dotazy používejte pomocné tabulky

Pokud zjistíte, že určitý dotaz opakovaně používáte jako dílčí dotaz, můžete si jej uložit jako pomocnou tabulku, když kliknete na tlačítko Uložit jako tabulku nad výsledky dotazu. Na tuto tabulku se pak můžete odkázat v části FROM vašeho dotazu. Tím snížíte množství dat, které je nutné zpracovat, i čas potřebný na jejich zpracování.

Použití pomocné tabulky
SELECT field1, field2
FROM [Dataset name.table name];

Ladění dotazů

Každý kód v nástroji BigQuery se po sestavení ladí. V okně pro sestavení dotazu je průběh ladění vidět těsně pod dotazem. Ladění je dostupné také přes rozhraní API s příznakem dryRun.

Platné dotazy mají zelený indikátor, na který lze kliknout a zobrazit množství dat zpracovávaných na základě dotazu. Tato funkce umožňuje optimalizovat údaje před spuštěním dotazu, a vyhnout se tak nadbytečnému zpracovávání dat.

Query Debugging - Success

 

U neplatných dotazů je zobrazen červený indikátor. Po kliknutí na něj se zobrazí informace o chybě a údaje o řádku a sloupci, kde se chyba vyskytla. V následujícím příkladu je příkaz GROUP BY prázdný se zvýrazněnou chybou.

Query Debugging - Error

 

Tipy a doporučené postupy

Použití ukázkového souboru dat

Následující příklady používají ukázkový soubor dat Google Analytics.

Pokud chcete dotazy použít na vlastní údaje, jednoduše nahraďte názvy projektů a souborů dat v příkladech názvy svých vlastních projektů a souborů dat.

Použití standardního SQL a staršího SQL

Nástroj BigQuery podporuje dva jazyky SQL:

Migrace na standardní SQL vysvětluje rozdíly mezi oběma jazyky.

Preferovaným jazykem SQL pro dotazování na data uložená v nástroji BigQuery je nyní standardní SQL.

Informace o povolení standardního SQL v rozhraní BigQuery UI, CLI, API nebo libovolném jiném rozhraní, které používáte, najdete v části Povolení standardního SQL.

Nejjednodušší způsob, jak začít, je uvést komentář „standardSQL“ (standardní SQL) v horní části dotazů ve standardním SQL tak, jak je vidět v níže uvedených příkladech.

Pokud používáte starší SQL, předávají se údaje služby Google Analytics 360 každý den do nové tabulky. Pokud chcete provést dotaz na několik tabulek současně, můžete názvy tabulek oddělit čárkou, použít funkci pro „zástupný znak tabulky“ TABLE_DATE_RANGE nebo použít několik čárkami oddělených funkcí TABLE_DATE_RANGE, jako je tomu v níže uvedených příkladech.

Dotaz na více tabulek

Následující příkazy ukazují dotazy na stejná data ve standardním SQL a ve starším SQL.

3 dny

Standardní SQL
3 dny s použitím operátoru 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ší příkazy SQL
3 dny s použitím čárkou oddělených názvů tabulek
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 1095 dní

Standardní SQL
Posledních 1095 dní s použitím operátoru _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ší příkazy SQL
Posledních 1095 dní s použitím operátoru 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 měsíců

Standardní SQL
Posledních 36 měsíců s použitím operátoru _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ší příkazy SQL
Posledních 36 měsíců s použitím operátoru 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í 3 roky

Standardní SQL
Poslední 3 roky s použitím operátoru _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ší příkazy SQL
Poslední 3 roky s použitím operátoru 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étní časové období

Standardní SQL
Konkrétní období s použitím operátoru _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ší příkazy SQL
Konkrétní období s použitím operátoru 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í 3 roky plus dnešní údaje (během dne)

Standardní SQL
Poslední 3 roky plus dnešní údaje (během dne) s použitím operátorů UNION ALL a _TABLE_SUFFIX
Poznámka: Tento vzorový dotaz nebude u veřejného souboru dat Google Analytics fungovat, protože není k dispozici tabulka dnešních údajů.
#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ší příkazy SQL

Poslední 3 roky plus dnešní údaje (během dne) s použitím několika operátorů TABLE_DATE_RANGE
Poznámka: Tento vzorový dotaz nebude u veřejného souboru dat Google Analytics fungovat, protože není k dispozici tabulka dnešních údajů.
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

 

 

Základní příklady dotazů

V této části se dozvíte, jak sestavit základní dotazy pomocí metrik a dimenzí ze vzorových údajů Analytics.

[Metrika] celkem podle [dimenze]?

Níže jsou uvedeny příklady skriptů pro dotaz: Jaký je celkový počet vygenerovaných transakcí podle mobilních prohlížečů v červenci 2017?

Standardní SQL

Celkový počet transakcí podle mobilních prohlížečů v červenci 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ší příkazy SQL

Celkový počet transakcí podle mobilních prohlížečů v červenci 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

 

 

Průměrná míra okamžitého opuštění [ dimenze ]?

Skutečná míra okamžitého opuštění je definovaná jako procento návštěv s jedním zobrazením stránky. Níže jsou uvedeny příklady skriptů pro dotaz: Jaká byla skutečná míra okamžitého opuštění podle zdroje návštěvnosti?

Standardní SQL

Míra okamžitého opuštění podle zdroje návštěvnosti v červenci 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ší příkazy SQL

Míra okamžitého opuštění podle zdroje návštěvnosti v červenci 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

 

 

Průměrný počet zobrazení stránky produktu podle typu nakupujícího (zákazníci, kteří uskutečnili vs. neuskutečnili nákup)

Níže jsou uvedeny příklady skriptů pro dotaz: Jaký byl průměrný počet zobrazení stránky produktu u uživatelů, kteří uskutečnili nákup v červenci 2017?

Standardní SQL

Průměrný počet zobrazení stránky produktu u uživatelů, kteří uskutečnili nákup v červenci 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ší příkazy SQL

Průměrný počet zobrazení stránky produktu u uživatelů, kteří uskutečnili nákup v červenci 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 )

 

 

Níže jsou uvedeny příklady skriptů pro dotaz: Jaký byl průměrný počet zobrazení stránky produktu u uživatelů, kteří neuskutečnili nákup v červenci 2017?

Standardní SQL

Průměrný počet zobrazení stránky produktu u uživatelů, kteří neuskutečnili nákup v červenci 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ší příkazy SQL

Průměrný počet zobrazení stránky produktu u uživatelů, kteří neuskutečnili nákup v červenci 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 )

 

 

Průměrný počet transakcí podle nakupujícího

Níže jsou uvedeny příklady skriptů pro dotaz: Jaký byl průměrný celkový počet transakcí na jednoho uživatele, který uskutečnil nákup v červenci 2017?

Standardní SQL

Průměrný celkový počet transakcí na jednoho uživatele, který uskutečnil nákup v červenci 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ší příkazy SQL

Průměrný celkový počet transakcí na jednoho uživatele, který uskutečnil nákup v červenci 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 )

 

 

Průměrná utracená částka na jednu návštěvu

Níže jsou uvedeny příklady skriptů pro dotaz: Jaká je průměrná utracená částka na jednu návštěvu v červenci 2017?

Standardní SQL

Jaká je průměrná utracená částka na jednu návštěvu v červenci 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ší příkazy SQL

Jaká je průměrná utracená částka na jednu návštěvu v červenci 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 )

 

 

Posloupnost požadavků na server

Níže jsou uvedeny příklady skriptů pro dotaz: Jaká je posloupnost zobrazených stránek?.

Standardní SQL

Posloupnost stránek zobrazených uživateli v červenci 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ší příkazy SQL

Posloupnost stránek zobrazených uživateli v červenci 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 dotazu omezíte typy požadavků na server na stránky (PAGES). Tak se vyhnete zobrazení interakcí událostí nebo transakcí. Každý řádek výstupu představuje zobrazení stránky a je zobrazen ve výchozím pořadí polí v příkazu SELECT.

 

 

Několik vlastních dimenzí na úrovni návštěvy nebo požadavku na server

vlastní dimenze na úrovni požadavku na server
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í dimenze na úrovni návštěvy
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

V každém dotazu:

Příkaz SELECT odešle dotaz na příslušná pole dimenze a metriky.

Funkce MAX:

  • Vrátí vlastní dimenzi jako nový sloupec. Pokud chcete vrátit několik vlastních dimenzí jako nové sloupce, můžete tuto funkci zopakovat.
  • Příkazy WITHIN hits a WITHIN RECORD vyhodnotí podmínku uvnitř opakovaných polí v BigQuery.
  • Podmínka uvnitř příkazu MAX se vyhodnotí pro každou vlastní dimenzi, ale u každé dimenze, která není index=1 (požadavky) nebo index=2 (návštěvy), vrátí NULL.
  • Vrátí maximální hodnotu, což je hodnota Vlastní dimenze 1 u požadavků na server nebo Vlastní dimenze 2 u návštěv, protože všechny ostatní hodnoty jsou NULL.

Příklady pokročilých dotazů

Když jste se nyní seznámili s jednoduchými dotazy, můžete sestavovat dotazy pomocí pokročilých funkcí, které jsou v nástroji BigQuery k dispozici.

Produkty zakoupené zákazníky, kteří si koupili produkt A (klasický elektronický obchod)

Dále je uveden rámcový skript pro dotaz: Jaké další produkty si zakoupili zákazníci, kteří si koupili produkt A?

produkty zakoupené zákazníkem, který si koupí produkt 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 '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. Na prvním řádku vyberete všechny další produkty zakoupené uživatelem. Agregovaná funkce COUNT() vypočítá množství jednotlivých dalších zakoupených položek. Výsledek se pak zobrazí v poli označeném quantity, s přiřazenou položkou v poli produktu označeném jako other_purchased_products.
  2. V šedém dílčím dotazu vyberete pouze unikátní uživatele (fullVisitorId), kteří uskutečnili transakce (totals.transactions>=1) a během transakce si zakoupili produkt A (WHERE hits.item.productName CONTAINS ‚název položky produktu A').

Pravidla (příkazy WHERE a AND) v dotazu nejvyšší úrovně (zelený) ignorují hodnoty v parametru hits.item.productName, které jsou null a obsahují produkt A.

Zde je příklad dotazu Pokud si zákazník koupí „kovové stylusy Brighton (sada 4 ks)‟, jaký další produkt(y) si koupil?

produkty koupené zákazníkem, který si 24. června 2013 koupil „kovové stylusy Brighton (sada 4 ks)‟
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;

V nástroji Dremel/BigQuery se pomocí příkazu WHERE expr IN spustí příkaz JOIN a použijí se omezení velikosti. Konkrétně velikost pravé strany příkazu JOIN (v tomto případě počet návštěvníků) musí být menší než 8 MB. V nástroji Dremel se to nazývá broadcast JOIN. Jakmile velikost překročí 8 MB, budete potřebovat spustit příkaz shuffled JOIN, což můžete provést pomocí syntaxe JOIN EACH. Naneštěstí to nelze provést pomocí příkazu IN, ale stejný dotaz lze přepsat pomocí příkazu JOIN.

Produkty zakoupené zákazníky, kteří si koupili Produkt A (rozšířený elektronický obchod)

Tento dotaz se podobá předchozímu rámcovému dotazu, ale funguje pro rozšířený elektronický obchod. I on využívá při vznášení dotazu na údaje z různých dní funkci TABLE_DATE_RANGE.

produkty zakoupené zákazníkem, který si koupí produkt A (rozšířený 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;

Průměrný počet interakcí uživatele před nákupem

Toto je příklad dotazu příkazu JOIN() [...] ON, který je závislý pouze na datech služby Analytics.

Dále je uveden rámcový skript pro dotaz: Jaký je průměrný počet interakcí uživatele před nákupem?

počet interakcí uživatele před nákupem
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. Na prvním řádku se provede hlavní matematická operace, která zjistí průměrný počet interakcí uživatele na produkt. Tento dotaz pak vytvoří logický součet mezi dvěma dílčími dotazy nazvanými Alias_Name_1 a Alias_Name_2.
  2. ‚Název_aliasu_1’ vytvoří pole, které pomocí agregované funkce SUM() sečte všechna čísla požadavků na server zaznamenaných u příslušného produktu.
  3. ‚Název_aliasu_2’ zjistí pomocí funkce COUNT() počet požadavků na server provedených uživateli na daný produkt.
  4. Na posledním řádku je uvedeno společné pole (hits.item.productSku), které je společné pro oba soubory dat při logickém součtu.

Zde je příklad dotazu Jaký je průměrný počet interakcí uživatele před nákupem 10. září 2013?

počet interakcí uživatele 10. září 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;

Procento prodaných skladových zásob na jeden produkt

Toto je příklad dotazu, který není závislý jen na údajích ze služby Analytics. Sloučením obou souborů dat můžete zjistit chování uživatelů na úrovni větší segmentace. Údaje nepocházející z Analytics lze importovat do nástroje BigQuery, ale mějte na paměti, že díky tomu vzroste váš měsíční poplatek za uchovávání dat.

Dále je uveden rámcový skript pro dotaz: Jaké procento skladových zásob se prodalo na jeden produkt?

procento prodaných skladových zásob na produkt
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. Na prvním řádku se vytvoří dvě pole: jedno pole obsahující všechna ID produktů a druhé pole s matematickou operací, která vypočítá procento prodaných skladových zásob na dané ID produktu.
  2. Protože tento dotaz závisí na dvou datových sadách, je nutné použít funkci JOIN() ... ON. Tento příkaz sloučí dva řádky ze dvou souborů dat na základě jejich společného pole. Dva soubory dat v tomto případě jsou [ ‚Imported_DataSet’ ] a ‚Alias_Name’.
  3. [ ‘Imported_DataSet’ ] představuje údaje nepocházející z Analytics. Jde o soubor dat, který obsahuje pole metriky s údajem, kolik skladových zásob zbývá (Imported DataSet.’stock_left_field’) a pole dimenze ID produktu (Imported_DataSet.’productId_field’).
  4. ‚Alias_Name’ je název přiřazený údajům vráceným šedým dílčím dotazem. Tento dílčí dotaz zjistí celkové množství prodaných položek u daného produktu na základě údajů z Google Analytics.
  5. Poslední řádek zobrazí společné pole obou souborů dat na základě příkazu ON, a co oba soubory dat spojuje.

Mnoho proměnných v tomto dotazu má název souboru dat připojený ke svému názvu jako předponu (například Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Tato předpona označuje, jaké pole vybíráte, a určuje, k jakému souboru dat dané pole náleží.

Zde je příklad dotazu Jaké je procento prodaných skladových zásob na produkt k 28. červenci 2013?

procento prodaných skladových zásob na produkt k 28. červenci 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;

Ziskovost jednotlivých produktů

Dále je uveden rámcový skript pro dotaz: Jaká je ziskovost jednotlivých produktů?

zisk podle produktu
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. Na prvním řádku je matematická operace, která vypočítá celkový zisk jednotlivých produktů.
  2. Šedý dílčí dotaz použije údaje ze služby Analytics, která shromažďuje údaje o dosaženém zisku z prodeje produktu.
  3. Červený dílčí dotaz je poddotaz na data Analytics, který bude sloučen s údaji nepocházejícími z Analytics. Vypočítá množství prodaných položek na produkt.
  4. Na posledním řádku je pomocí příkazu ON upřesněno pole, které je společné pro oba soubory dat. V tomto případě je to číslo ID produktu.

Zde je příklad dotazu Jaká byla ziskovost jednotlivých produktů 28. července 2013?

zisk podle produktu 28. července 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 se vypočítá z rozdílu mezi prodejní cenou produktu a náklady na jeho výrobu. Tyto údaje jsou uloženy v souboru dat mimo GA.

Skutečná ziskovost jednotlivých produktů (při zohlednění refundací účtu)

Dále je uveden rámcový skript pro dotaz: Jaká je skutečná ziskovost jednotlivých produktů?

skutečný zisk podle produktu
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. Toto je velmi podobný dotaz jako: Jaká je ziskovost jednotlivých produktů? Jediné rozdíly jsou v datové sadě nepocházející z Analytics v šedém dílčím dotazu a v matematické operaci na výpočet skutečného zisku na prvním řádku.
  2. V datové sadě nepocházející z Analytics také počítáte celkovou částku vynaloženou na vracení plateb (ve výrazu SELECT červeného poddotazu).
  3. Pak provedete matematickou operaci na prvním řádku, která vypočítá skutečný zisk odečtením příjmu utraceného za refundace od hrubého zisku.

Více informací o dotazu najdete v části o ziskovosti jednotlivých produktů.

Zde je příklad dotazu Jaká byla skutečná ziskovost jednotlivých produktů 28. července 2013?

skutečný zisk podle produktu 28. července 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 ) );

Skutečný zisk bere v úvahu ziskovost produktu po zohlednění refundovaných produktů. Postup výpočtu celkového příjmu z refundace za produkt:

celkový příjem z refundace za produkt = ( cena produktu + refundovaná dodací cena produktu ) * množství refundovaných produktů

Pomohly vám tyto informace?

Jak bychom článek mohli vylepšit?

Potřebujete další pomoc?

Vyzkoušejte tyto další kroky:

Zveřejnit v komunitě nápovědy Získat odpovědi od členů komunity
Kontaktujte nás Řekněte nám víc a my vám pomůžeme
true
learning path
Zvolte si vlastní výukový program

Vyzkoušejte google.com/analytics/learn, nový zdroj informací, který vám pomůže využít službu Google Analytics 4 na maximum. Na novém webu najdete videa, články a postupy. Jsou zde také odkazy na Google Analytics Discord, blog, kanál YouTube a úložiště GitHub.

Pusťte se do učení!

Vyhledávání
Vymazat vyhledávání
Zavřít vyhledávání
Hlavní nabídka
11966055077877291971
true
Prohledat Centrum nápovědy
true
true
true
true
true
69256
false
false