BigQuery szakácskönyv

Ez a funkció csak a Google Marketing Platform részét képező Analytics 360 szolgáltatásban áll rendelkezésre.
További információ a Google Marketing Platformról.

Ebben a cikkben arra láthat példákat, hogyan állíthatók össze lekérdezések azokból az Analytics-adatokból, amelyeket a BigQuery eszközbe exportál. Összeállítottunk egy mintaadatkészletet is, amelyen kipróbálhatja a cikkben bemutatott egyes lekérdezéseket.

A cikk tartalma:

Lekérdezések optimalizálása

Minden futtatott lekérdezés beleszámít a havonta rendelkezésre álló adatfeldolgozási mennyiségbe. Ha felesleges mezőket is kiválaszt, azzal megnöveli a feldolgozandó adatok mennyiségét, és ennek eredményeként a szükségesnél többet használt fel a havonta rendelkezésre álló adatfeldolgozásból. A lekérdezések optimalizálásával hatékonyan használhatja fel a havonta rendelkezésre álló adatfeldolgozási kvótát.

További információ az árazásról

Csak annyit jelöljön ki, amennyit szükséges

A lekérdezés megfogalmazásakor kizárólag a SELECT utasítás releváns mezőit jelölje ki. Ha nem hív meg felesleges mezőket, akkor csökkentheti az adatmennyiséget és a lekérdezés feldolgozásához szükséges időt.

Példa: ne használjon helyettesítő karaktert operátorként

Rossz megoldás: helyettesítő karakter használata operátorként
SELECT *
FROM [table name];

 

Jobb megoldás: a mezők nevének használatával elkerüli a szükségtelen adatfeldolgozást
SELECT field1, field2
FROM [table name];

Gyorsítótárazás engedélyezése

Ha lehetséges, ne használjon függvényeket mezőkként. A függvények (mint például a MOST() és a MA()) változó eredményeket hívnak le, ez viszont ellehetetleníti a lekérdezések gyorsítótárba vételét, amivel meggyorsíthatná a lehívást. Használjon inkább konkrét időpontokat és dátumokat.

Jelenleg a több táblázatot érintő, helyettesítő karaktert használó lekérdezések nem támogatják a gyorsítótárban lévő találatokat, még ha be is van jelölve a „Gyorsítótárban lévő találatok használata” beállítás. Ha többször futtatja ugyanazt a lekérdezést, mindegyiket kiszámlázzuk. További információ.

A gyakran használt segédlekérdezésekhez használjon köztes táblázatokat

Ha egy adott lekérdezést gyakran használ segédlekérdezésként, érdemes azt köztes táblázatként elmentenie: ehhez kattintson a lekérdezési eredmények felett található Mentés táblázatként elemre. Ezután erre a táblázatra is hivatkozhat a lekérdezés FROM részében, ami a feldolgozandó adatok mennyiségét és a feldolgozáshoz szükséges időt is csökkenti.

köztes táblázat használata
SELECT field1, field2
FROM [Dataset name.table name];

Hibakeresés a lekérdezésekben

A BigQuery a létrehozás során hibakeresést végez a kódban. A hibakeresés a szerkesztőablakban közvetlenül a lekérdezés alatt jelenik meg. A hibakeresés funkció emellett az API-n keresztül is elérhető a dryRun jelölővel.

Az érvényes lekérdezéseknél zöld jelölő látható, amelyre rákattinthat, és így megtekintheti a lekérdezés által feldolgozott adatokat. Ezzel a funkcióval még a lekérdezés futtatása előtt optimalizálhatja az adatokat, és így elkerülheti a felesleges adatfeldolgozást.

Query Debugging - Success

 

Az érvénytelen lekérdezések piros jelölővel jelennek meg, amelyekre rákattintva megtekintheti a hiba információit, és megtalálhatja a hiba előfordulásának sorát és oszlopát. Az alábbi példában a GROUP BY utasítás üres, a rendszer pedig azonosítja a hibát.

Query Debugging - Error

 

Tippek és bevált módszerek

A mintaadatkészlet használata

Az alábbi példák a Google Analytics mintaadatkészletét használják.

Ha a lekérdezéseket a saját adataival szeretné használni, csak cserélje le a példában szereplő projekt- és adatkészletnevet saját projektjének és adatkészletének a nevére.

A normál SQL és a régi SQL használata

A BigQuery az SQL nyelv két változatát támogatja:

A két változat közötti különbségeket a normál SQL-re történő átállással foglalkozó cikk részletezi.

A BigQuery rendszerbe tárolt adatokhoz való hozzáférés előnyben részesített módja jelenleg a normál SQL használata.

A normál SQL bekapcsolásáról szóló cikkből tájékozódhat arról, hogyan kell bekapcsolni a normál SQL használatát a BigQuery kezelőfelületén, CLI-felületén, API-jában, illetve az Ön által használt egyéb felületeken.

A váltás felé vezető első lépést a legegyszerűbben úgy teheti meg, hogy a normál SQL-lekérdezések tetején elhelyezi a „standardSQL” megjegyzést, ahogyan ez az alábbi példákban is látható.

A régi SQL használatakor a Google Analytics 360 az adatokat minden nap új táblázatnak adja át. Ha több táblázatot szeretne egyszerre lekérdezni, adja meg a táblázatok nevét vesszővel elválasztva, használja a TABLE_DATE_RANGE táblázatkezelési helyettesítő függvényt, vagy használjon több TABLE_DATE_RANGE függvényt vesszővel elválasztva, ahogyan az alábbi példákban is látható.

Több táblázat lekérdezése

Az alábbi példákban láthatja, hogy hogyan kérdezhetők le ugyanazok az adatok a normál SQL és a régi SQL használatával.

3 nap adatainak lekérdezése

Normál SQL
3 nap adatainak lekérdezése a UNION ALL használatával
#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
Régi SQL
3 nap adatainak lekérdezése vesszővel tagolt táblázatnevek használatával
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

 

Az elmúlt 1095 nap adatainak lekérdezése

Normál SQL
Az elmúlt 1095 nap adatainak lekérdezése a _TABLE_SUFFIX használatával
#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
Régi SQL
Az elmúlt 1095 nap adatainak lekérdezése a TABLE_DATE_RANGE használatával
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

 

Az elmúlt 36 hónap adatainak lekérdezése

Normál SQL
Az elmúlt 36 hónap adatainak lekérdezése a _TABLE_SUFFIX használatával
#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
Régi SQL
Az elmúlt 36 hónap adatainak lekérdezése a TABLE_DATE_RANGE használatával
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

 

Az elmúlt 3 év

Normál SQL
Az elmúlt 3 év adatainak lekérdezése a _TABLE_SUFFIX használatával
#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
Régi SQL
Az elmúlt 3 év adatainak lekérdezése a TABLE_DATE_RANGE használatával
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

 

Adott dátumtartomány adatainak lekérdezése

Normál SQL
Adott időszak adatainak lekérdezése a _TABLE_SUFFIX használatával
#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
Régi SQL
Adott időszak adatainak lekérdezése a TABLE_DATE_RANGE használatával
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

Az elmúlt 3 év és a mai nap adatainak (napon belüli adatok) lekérdezése

Normál SQL
Az elmúlt 3 év és a mai nap adatainak (napon belüli adatok) lekérdezése a UNION ALL és a _TABLE_SUFFIX használatával
Megjegyzés: Ez a példalekérdezés nem működik a Google Analytics nyilvános adatkészletével, mert jelenleg nincs napon belüli adatokat tartalmazó táblázat.
#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

Régi SQL

Az elmúlt 3 év és a mai nap adatainak (napon belüli adatok) lekérdezése több TABLE_DATE_RANGE utasítás használatával
Megjegyzés: Ez a példalekérdezés nem működik a Google Analytics nyilvános adatkészletével, mert jelenleg nincs napon belüli adatokat tartalmazó táblázat.
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

 

 

Lekérdezések – alappéldák

Ez a szakasz bemutatja, hogyan hozhat létre alapvető lekérdezéseket Analytics mintaadatokból, mutatók és dimenziók használatával.

Mennyi az összes [mutató] száma per [dimenzió]?

Alább olyan példaszkripteket láthat, amelyek a következő kérdést válaszolják meg: Az eszközön található böngészőkben összesen hány tranzakcióra került sor 2017 júliusában?

Normál SQL

A 2017 júliusában az eszközön található böngészőkben végzett összes tranzakció
#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

Régi SQL

A 2017 júliusában az eszközön található böngészőkben végzett összes tranzakció
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

 

 

Átlagos visszafordulási arány per [ dimenzió ]?

A tényleges visszafordulási arány definíciója: az egyetlen oldalmegtekintést tartalmazó látogatások százalékos aránya. Alább olyan példaszkripteket láthat, amelyek a következő kérdést válaszolják meg: Milyen tényleges visszafordulási arány volt jellemző a forgalom egyes forrásaira?

Normál SQL

A forgalomforrások szerinti visszafordulási arány 2017 júliusában
#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

Régi SQL

A forgalomforrások szerinti visszafordulási arány 2017 júliusában
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

 

 

Termékoldal-megtekintések átlagos száma vásárlótípusonként (vásárló kontra és vásárló felhasználók esetén)

Alább olyan példaszkripteket láthat, amelyek a következő kérdést válaszolják meg: Átlagosan hány termékoldal-megtekintést regisztrált a rendszer azoknál a felhasználóknál, akik 2017 júliusában vásároltak?

Normál SQL

A termékoldal-megtekintések átlagos száma azok esetén a felhasználók esetén, akik 2017 júliusában vásároltak
#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 )

Régi SQL

A termékoldal-megtekintések átlagos száma azok esetén a felhasználók esetén, akik 2017 júliusában vásároltak
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 )

 

 

Alább olyan példaszkripteket láthat, amelyek a következő kérdést válaszolják meg: Átlagosan hány termékoldal-megtekintést regisztrált a rendszer azoknál a felhasználóknál, akik 2017 júliusában nem vásároltak?

Normál SQL

A termékoldal-megtekintések átlagos száma azok esetén a felhasználók esetén, akik 2017 júliusában nem vásároltak
#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 )

Régi SQL

A termékoldal-megtekintések átlagos száma azok esetén a felhasználók esetén, akik 2017 júliusában nem vásároltak
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 )

 

 

Tranzakciók átlagos száma vásárlónként

Alább olyan példaszkripteket láthat, amelyek a következő kérdést válaszolják meg: Átlagosan mennyi tranzakció jut egy olyan felhasználóra, aki 2017 júliusában vásárolt?

Normál SQL

A tranzakciók átlagos száma a 2017 júliusában vásárló felhasználók esetén
#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 )

Régi SQL

A tranzakciók átlagos száma a 2017 júliusában vásárló felhasználók esetén
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 )

 

 

A munkamenetenként átlagosan elköltött pénzösszeg

Alább olyan példaszkripteket láthat, amelyek a következő kérdést válaszolják meg: Átlagosan mennyi pénzt költöttek a felhasználók munkamenetenként 2017 júliusában?

Normál SQL

A munkamenetenként átlagosan elköltött összeg 2017 júliusában
#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 )

Régi SQL

A munkamenetenként átlagosan elköltött összeg 2017 júliusában
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 )

 

 

Lekérések sorrendje

Alább olyan példaszkripteket láthat, amelyek a következő kérdést válaszolják meg: Milyen sorrendben következnek egymás után a megtekintett oldalak?

Normál SQL

Azoknak az oldalaknak a sorrendje, amelyeket a felhasználók 2017 júliusában tekintettek meg
#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

Régi SQL

Azoknak az oldalaknak a sorrendje, amelyeket a felhasználók 2017 júliusában tekintettek meg
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

Ez a lekérdezés a lekéréseket a PAGES típusra korlátozza, hogy az esemény és a tranzakció típusú interakciók ne jelenjenek meg. A kimenet valamennyi sora egy-egy oldalmegtekintést jelent, és ezek a SELECT utasítás mezőinek alapértelmezett sorrendjében jelennek meg.

 

 

Több egyéni dimenzió a lekérés vagy a munkamenet szintjén

lekérésszintű egyéni dimenzió
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

 

munkamenetszintű egyéni dimenzió
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

Mindegyik lekérdezésre igaz a következő:

A SELECT utasítás lekérdezi a releváns dimenzió- és mutatómezőket.

A MAX függvény:

  • Új oszlopként adja vissza az egyéni dimenziót. Ha több egyéni dimenziót szeretne új oszlopként visszaadni, a függvény többször is használható.
  • A WITHIN hits és a WITHIN RECORD lekérések az ismétlődő mezőkön belüli feltételeket értékelik a BigQuery eszközben.
  • A rendszer a MAX függvényen belüli feltételt minden egyéni dimenzióhoz értékeli, ha azonban ez a feltétel nem index=1 (hits) vagy index=2 (sessions), akkor a visszaadott érték NULL.
  • A maximális értéket adja vissza: ez lekérések esetén az 1. egyéni dimenzió, munkamenetek esetén pedig a 2. egyéni dimenzió (mivel a többi érték NULL).

Lekérdezések – példák haladóknak

Most, hogy megismerte az egyszerűbb lekérdezéseket, olyanokat is összeállíthat, amelyek a BigQuery speciális funkcióit és szolgáltatásait is használják.

Az „A” terméket megvásároló ügyfelek által vásárolt termékek (hagyományos elektronikus kereskedelem)

Az alábbiakban a következő kérdés megválaszolására szolgáló szkript vázlata látható: Milyen egyéb termékeket vásároltak az „A” terméket megvásároló ügyfelek?

az „A” terméket megvásároló ügyfelek által vásárolt termékek (hagyományos elektronikus kereskedelem)
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. Az első sor az összes olyan további elemet kiválasztja, amelyet egy felhasználó megvásárolt, majd a COUNT() összesítő függvénnyel minden megvásárolt további elem mennyiségét kiszámítja. Az eredmény egy quantity (mennyiség) címkéjű mezőben, a társított elem pedig az other_purchased_products (egyéb vásárolt termékek) címkével ellátott termékmezőben jelenik meg.
  2. A szürke segédlekérdezésben csak azokat az egyedi felhasználókat választjuk ki (fullVisitorId), akik tranzakciókat hajtottak végre (totals.transactions>=1), a tranzakció során pedig megvásárolták az „A” terméket (WHERE hits.item.productName CONTAINS 'A termék neve').

A felső (zöld) lekérdezésbe foglalt szabályok (WHERE és AND utasítások) kizárják azokat az értékeket a hits.item.productName elemből, amelyek értéke NULL, és tartalmazzák az „A” terméket.

Az alábbi példa azt a lekérdezést mutatja be, miszerint Amelyik vásárló megvásárolta a négydarabos Brighton fém tollkészletet, milyen más termék(ek)et vásárolt még?

a 2013. június 24-én négydarabos Brighton fém tollkészletet vásároló ügyfél által vásárolt termékek
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;

A Dremel/BigQuery eszközben a WHERE expr IN aktiválási szabály egy JOIN műveletet vált ki, amelynek méretkorlátozásai vannak: a JOIN parancs jobb oldalának mérete (ebben az esetben a látogatók száma) legfeljebb 8 MB lehet. A Dremel eszközben ennek broadcast JOIN a neve. Ha a méret 8 MB-nál nagyobb, akkor a shuffled JOIN szabályt kell aktiválni, amelyhez a JOIN EACH szintaxis használható. Ez sajnos nem oldható meg az IN utasítással, de ugyanez a lekérdezés átírható úgy, hogy egy JOIN parancs szerepeljen benne.

Az „A” terméket megvásároló ügyfelek által vásárolt termékek (Továbbfejlesztett elektronikus kereskedelem)

Az alább látható szkriptvázlat hasonlít az előzőre, azonban a Továbbfejlesztett elektronikus kereskedelem módszerrel működik. Továbbá a TABLE_DATE_RANGE utasítást is használja több nap adatainak lekérdezéséhez.

az „A” terméket megvásároló ügyfél által vásárolt termékek (Továbbfejlesztett elektronikus kereskedelem)
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;

A vásárlás előtti felhasználói interakciók átlagos száma

Az alábbiakban egy, csak az Analytics-adatoktól függő JOIN() [...] ON parancsot használó lekérdezés példája látható.

Az alábbi szkriptvázlattal a következő kérdésre válaszolhat: átlagosan hány felhasználói interakcióra kerül sor egy vásárlás előtt?

a felhasználói interakciók száma egy vásárlás előtt
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. Az első sor végzi el azt a fő matematikai műveletet, amely kikeresi a termékenként lezajlott átlagos felhasználói interakciószámot, és ez a lekérdezés csatlakoztatja egymáshoz a ‘Alias Name_1’ és ‘Alias Name_2’ nevű két segédlekérdezést.
  2. Az ‘Alias Name_1’ olyan mezőt hoz létre, amely a SUM() összesítő függvénnyel összegzi a termékekhez rögzített lekérések számát.
  3. Az „Alias Name_2” COUNT() függvényével állapítjuk meg, hogy a felhasználók termékenként hány lekérést kezdeményeztek.
  4. Az utolsó sor jeleníti meg a két összekapcsolt adatkészlet között megosztott közös mezőt (hits.item.productSku).

Az alábbiakban a következő lekérdezés példája látható: átlagosan hány felhasználói interakcióra került sor egy vásárlás előtt 2013. szeptember 10-én?

a felhasználói interakciók száma egy vásárlás előtt 2013. szeptember 10-én
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;

Az értékesített készlet százalékos aránya termékenként

Az alábbiakban olyan lekérdezésre látható példa, amely nem kizárólag az Analytics adataitól függ, hanem az Analytics rendszertől független adatoktól is. A két adatkészlet kombinálásával a felhasználók viselkedéseinek egyes összetevői is jobban megérthetők. A BigQuery eszközbe az Analytics rendszertől független adatok is importálhatók; ezek beleszámítanak az adattárolás havi költségébe.

Az alábbi szkriptvázlattal a következő kérdésre válaszolhat: a készlet hány százalékát sikerült értékesíteni termékenként?

az értékesített készlet százalékos aránya termékenként
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. Az első sor kivitele két mezőt tartalmaz: az egyik az összes termékazonosítót tartalmazza, a másik egy matematikai művelet, amely megmutatja az adott termékazonosítóhoz tartozó értékesített készlet százalékos arányát.
  2. Mivel ez a lekérdezés két adatkészlettől függ, használja a JOIN() ... ON függvényt. Ez a parancs – a közös mező alapján – összekapcsolja a két adatkészletből származó sorokat. A két adatkészlet ebben az esetben az [ ‘Imported_DataSet’ ] és az ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] az Analytics rendszertől független adat. Ez az adatkészlet tartalmazza a megmaradt készletet jelző mutató mezőjét (Imported_DataSet.’stock_left_field’), illetve a termékazonosító dimenzió mezőjét (Imported_DataSet.’productId_field’).
  4. Az ‘Alias Name’ a szürke segédlekérdezés által visszaadott adatokhoz rendelt név. Ez a segédlekérdezés Analytics-adatok használatával állapítja meg, hogy termékenként összesen hány egység értékesítésére került sor.
  5. Az utolsó sor az ON utasítással mutatja meg a két adatkészlet közös mezőjét, illetve az adatkészletek összeillesztési helyét.

A lekérdezés számos változójához előtagként csatoltuk a hozzá tartozó adatkészlet nevét is (például: Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Ezzel egyértelművé tehető, hogy mely mezőt választja ki, illetve nyilvánvaló lesz, hogy az melyik adatkészlethez tartozik.

Az alábbiakban a következő lekérdezés példája látható: a készlet hány százalékának értékesítésére került sor termékenként 2013. július 28-án?

a 2013. július 28-án értékesített készlet százalékos aránya termékenként
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;

Az egyes termékek jövedelmezősége

Az alábbi szkriptvázlattal a következő kérdésre válaszolhat: mennyire jövedelmezők az egyes termékek?

a termékenkénti haszon
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. Az első sorban található az egyes termékek által generált teljes haszon kiszámításához szükséges matematikai művelet.
  2. A termékek értékesítésekor generált haszon nagyságára vonatkozó adatokat összegyűjtő szürke segédlekérdezés az Analytics rendszertől független adatokat használ.
  3. A piros segédlekérdezés vonatkozik azon Analytics-adatokra, amelyeket majd egyesítünk az Analytics rendszertől független adatokkal. Ez számítja ki, hogy egy termékből hány egységet értékesítettek.
  4. Az utolsó sorban az ON​CODE utasítás mutatja meg, melyik mezőn osztozik a két adatkészlet. Ebben az esetben ez a termék azonosítószáma.

Az alábbiakban a következő lekérdezés példája látható: mennyire voltak jövedelmezők az egyes termékek 2013. július 28-án?

a termékenkénti haszon 2013. július 28-án
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 );

A nyereség kiszámítása során megállapítjuk a termék értékesítési ára és a gyártási költség közötti különbséget. A rendszer a Google Analytics rendszertől független adatokat tartalmazó adatkészletben tárolja ezeket az információkat.

Az egyes termékek tényleges jövedelmezősége (a visszatérítéseket figyelembe véve)

Az alábbi szkriptvázlattal a következő kérdésre válaszolhat: mennyire jövedelmezők ténylegesen az egyes termékek?

a termékenkénti tényleges haszon
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. Ez a lekérdezés nagyon hasonlít arra, hogy mennyire jövedelmezők az egyes termékek? A különbség mindössze a szürke segédlekérdezésben lévő, az Analytics rendszertől független adatkészletben, illetve az első sorban lévő, a tényleges nyereséget kiszámító matematikai műveletben van.
  2. Az Analytics adatoktól független adatkészletben emellett kiszámítja a visszatérítésekre fordított teljes pénzösszeget (a piros segédlekérdezés SELECT utasításával).
  3. Ezután az első sor matematikai műveletének végrehajtásával megállapítja a tényleges nyereséget: kivonja a bevétel visszatérítésekre költött részét a bruttó nyereségből.

A lekérdezésről az az egyes termékek jövedelmezősége részben talál részletesebb információkat.

Az alábbiakban a következő lekérdezés példája látható: mennyire voltak valóban jövedelmezők az egyes termékek 2013. július 28-án?

a termékenkénti tényleges haszon 2013. július 28-án
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 ) );

A tényleges haszon megállapításakor a visszatérített termékek számításba vételével kell figyelembe venni a termékek jövedelmezőségét. A termékekhez tartozó összes visszatérített bevétel kiszámítása:

termékenkénti összes visszatérített bevétel = ( a termék ára + a termék visszatérített szállítási költsége ) * a visszatérítésben érintett termékek száma

Hasznosnak találta?

Hogyan fejleszthetnénk?

További segítségre van szüksége?

Próbálja ki a következő lépéseket:

Közzététel a súgóközösségben Válaszokat kaphat kérdéseire a közösség tagjaitól
Vegye fel velünk a kapcsolatot Mondja el a részleteket, és segítünk a megoldásban
true
learning path
Saját tanmenet kiválasztása

Tekintse meg a google.com/analytics/learn webhelyet – új forrásunkat, amely segít, hogy a legtöbbet hozza ki a Google Analytics 4 szolgáltatásból. Az új webhely videókat, cikkeket és irányított folyamatokat kínál, valamint a Google Analytics Discord-szerverére, blogjára, YouTube-csatornájára és GitHub-tárhelyére mutató linkeket is biztosít.

Vágjon bele a tanulásba még ma!

Keresés
Keresés törlése
A keresés bezárása
Főmenü
1175520905162446420
true
Keresés a Súgóoldalakon
true
true
true
true
true
69256
false
false