Knjiga z recepti za BigQuery

Ta funkcija je na voljo v storitvi Analytics 360, ki je del storitve Google Marketing Platform.
Preberite več o storitvi Google Marketing Platform.

V tem članku so opisani praktični primeri sestavljanja poizvedb iz podatkov iz storitve Google Analytics, ki jih izvozite v BigQuery. Na voljo imate vzorčni nabor podatkov, na katerem lahko vadite nekatere poizvedbe, opisane v tem članku.

V tem članku:

Optimizacija poizvedb

Vsaka poizvedba, ki jo izvedete, porablja vašo mesečno kvoto za obdelavo podatkov. Če izberete nebistvena polja, povečate količino podatkov, ki morajo biti obdelani, posledično pa porabite več mesečne dovoljene količine, kot bi bilo treba. Z optimiziranimi poizvedbami varčno porabljate mesečno kvoto za obdelavo podatkov.

Več o določanju cen.

Izberite le tisto, kar potrebujete

Ko oblikujete poizvedbo, izberite ustrezna polja znotraj stavka SELECT. Če ne kličete nebistvenih polj, zmanjšate količino podatkov in čas, potreben za obdelavo poizvedbe.

Primer: izogibajte se uporabi operatorja v obliki nadomestnega znaka

Slaba oblika: Uporaba operatorja v obliki nadomestnega znaka
SELECT *
FROM [ime tabele];

 

Boljša oblika: Uporaba imen polj, da se izognete nepotrebni obdelavi
SELECT polje 1, polje 2
FROM [ime tabele];

Dovolite predpomnjenje

Kjer je mogoče, se izognite temu, da bi funkcije uporabili kot polja. Funkcije (na primer NOW() ali TODAY() ) vrnejo rezultate s spremenljivkami, zato poizvedbe ne morejo biti predpomnjene, kar posledično onemogoča, da bi bile hitreje vrnjene. Namesto tega uporabite konkretne ure in datume.

Predpomnjeni rezultati trenutno niso podprti za poizvedbe za več tabel, ki uporabljajo nadomestni znak, tudi če je možnost »Uporabi predpomnjene rezultate« izbrana. Če večkrat zaženete poizvedbo z nadomestnim znakom, se vam zaračuna vsaka poizvedba posebej. Več o tem

Uporabite vmesne tabele za pogosto uporabljene podpoizvedbe

Če ugotovite, da večkrat uporabite specifično poizvedbo kot podpoizvedbo, jo lahko shranite kot vmesno tabelo tako, da kliknete možnost Shrani kot tabelo nad rezultati poizvedbe. Nato se lahko sklicujete na to tabelo v odseku FROM poizvedbe, kar bo zmanjšalo tako količino podatkov, ki morajo biti obdelani, kot tudi čas, potreben za obdelavo.

uporaba vmesne tabele
SELECT polje 1, polje 2
FROM [Ime nabora podatkov.ime tabele];

Odpravljanje napak v poizvedbah

BigQuery odpravlja napake v kodi, medtem ko jo pišete. V oknu za pisanje je odpravljanje napak prikazano tik pod poizvedbo. Odpravljanje napak je na voljo tudi prek API-ja z zastavico dryRun.

Veljavne poizvedbe imajo zelen indikator. S klikom tega indikatorja prikažete količino podatkov, ki jo poizvedba obdela. Ta funkcija vam omogoča, da optimizirate podatke, preden zaženete poizvedbo, in se tako izognete nepotrebni obdelavi podatkov.

Query Debugging - Success

 

Neveljavne poizvedbe imajo rdeč indikator. S klikom tega indikatorja prikažete podatke o napaki in lahko poiščete vrstico in stolpec, kjer je prišlo do napake. V spodnjem primeru lahko vidite, da je stavek GROUP BY prazen, in napaka je locirana.

Query Debugging - Error

 

Nasveti in najboljši postopki

Uporaba vzorčnega nabora podatkov

Naslednji primeri uporabljajo vzorčni nabor podatkov za Google Analytics.

Če želite poizvedbe uporabiti v lastnih podatkih, morate imena projektov in naborov podatkov v primerih samo zamenjati z lastnimi imeni projektov in naborov podatkov.

Primerjava uporabe standardnega jezika in starejše različice jezika SQL

BigQuery podpira dve narečji jezika SQL:

V članku Selitev na standardni SQL so opisane razlike med narečjema.

Standardni SQL je zdaj prednostno narečje SQL za izvajanje poizvedb za podatke, shranjene v storitvi BigQuery.

Več podatkov o omogočanju standardnega jezika SQL v uporabniškem vmesniku, CLI-ju ali API-ju BigQuery (ali drugem vmesniku, ki ga uporabljate) najdete v članku Omogočanje standardnega jezika SQL.

Najlažje začnete tako, da na vrhu poizvedb v standardnem jeziku SQL vključite komentar »standardSQL«, kot je prikazano v naslednjih primerih.

Pri jeziku SQL starejše različice se podatki storitve Google Analytics 360 vsak dan posredujejo v novo tabelo. Če želite izvesti poizvedbo za več tabel hkrati, lahko imena tabel ločite z vejico, uporabite funkcijo nadomestnih znakov za tabelo TABLE_DATE_RANGE ali uporabite več funkcij TABLE_DATE_RANGE, ločenih z vejico, kot je prikazano v naslednjih primerih.

Izvajanje poizvedb za več tabel

V naslednjih primerih so prikazane poizvedbe za enake podatke v standardnem jeziku SQL in jeziku SQL starejše različice.

3 dnevi

Standardni SQL
3 dnevi; uporaba funkcije UNION ALL
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
SQL starejše različice
3 dnevi; uporaba imen tabel, ločenih z vejico
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

 

Zadnjih 1095 dni

Standardni SQL
Zadnjih 1095 dni; uporaba funkcije _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL starejše različice
Zadnjih 1095 dni; uporaba funkcije 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

 

Zadnjih 36 mesecev

Standardni SQL
Zadnjih 36 mesecev; uporaba funkcije _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL starejše različice
Zadnjih 36 mesecev; uporaba funkcije 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

 

Zadnja 3 leta

Standardni SQL
Zadnja 3 leta; uporaba funkcije _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
SQL starejše različice
Zadnja 3 leta; uporaba funkcije 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

 

Določeno časovno obdobje

Standardni SQL
Določeno časovno obdobje; uporaba funkcije _TABLE_SUFFIX
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
SQL starejše različice
Določeno časovno obdobje; uporaba funkcije 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

Podatki zadnjih 3 let in današnjega dne (znotraj dneva)

Standardni SQL
Podatki zadnjih 3 let in današnjega dne (znotraj dneva); uporaba funkcij UNION ALL in _TABLE_SUFFIX
Opomba: Ta vzorčna poizvedba ne bo delovala v javnem naboru podatkov Google Analytics, ker tabela znotraj dneva trenutno ne obstaja.
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

SQL starejše različice

Podatki zadnjih 3 let in današnjega dne (znotraj dneva); uporaba več funkcij TABLE_DATE_RANGE
Opomba: Ta vzorčna poizvedba ne bo delovala v javnem naboru podatkov Google Analytics, ker tabela znotraj dneva trenutno ne obstaja.
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

 

 

Primeri osnovnih poizvedb

V tem poglavju je pojasnjeno, kako izdelate osnovne poizvedbe z uporabo meritev in razsežnosti iz vzorčnih podatkov iz storitve Google Analytics.

Skupna vrednost [meritve] na [razsežnost]?

Spodaj so vzorčni skripti za vprašanje, ki se glasi: Kolikšno je skupno število transakcij, ki so bile ustvarjene za posamezen brskalnik naprave julija 2017?

Standardni SQL

Skupaj transakcij za posamezen brskalnik naprave julija 2017
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

SQL starejše različice

Skupaj transakcij za posamezen brskalnik naprave julija 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

 

 

Povprečna stopnja obiskov samo prve strani na [razsežnost]?

Realna stopnja obiskov ene strani je opredeljena kot odstotek obiskov z enim samim ogledom spletne strani. Spodaj so vzorčni skripti za vprašanje, ki se glasi: Kolikšna je bila realna stopnja obiskov samo prve strani na vir prometa?

Standardni SQL

Stopnja obiskov samo prve strani na vir prometa julija 2017
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

SQL starejše različice

Stopnja obiskov samo prve strani na vir prometa julija 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

 

 

Povprečno število ogledov spletne strani izdelka glede na vrsto kupca (kupci v primerjavi z nekupci)

Spodaj so vzorčni skripti za vprašanje, ki se glasi: Kolikšno je bilo povprečno število ogledov spletne strani izdelka pri uporabnikih, ki so julija 2017 opravili nakup?

Standardni SQL

Povprečno število ogledov spletne strani pri uporabnikih, ki so julija 2017 opravili nakup
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

SQL starejše različice

Povprečno število ogledov spletne strani pri uporabnikih, ki so julija 2017 opravili nakup
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 )

 

 

Spodaj so vzorčni skripti za vprašanje, ki se glasi: Kolikšno je bilo povprečno število ogledov spletne strani izdelka pri uporabnikih, ki julija 2017 niso opravili nakupa?

Standardni SQL

Povprečno število ogledov spletne strani pri uporabnikih, ki julija 2017 niso opravili nakupa
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

SQL starejše različice

Povprečno število ogledov spletne strani pri uporabnikih, ki julija 2017 niso opravili nakupa
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 )

 

 

Povprečno število transakcij na kupca

Spodaj so vzorčni skripti za vprašanje, ki se glasi: Kolikšno je bilo povprečno število skupnih transakcij na uporabnika, ki je julija 2017 opravil nakup?

Standardni SQL

Povprečno število skupnih transakcij na uporabnika, ki je julija 2017 opravil nakup
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

SQL starejše različice

Povprečno število skupnih transakcij na uporabnika, ki je julija 2017 opravil nakup
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 )

 

 

Povprečni znesek, porabljen na sejo

Spodaj so vzorčni skripti za vprašanje, ki se glasi: Kolikšen je povprečen znesek, ki je bil julija 2017 porabljen na sejo?

Standardni SQL

Povprečen znesek, ki je bil julija 2017 porabljen na sejo
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

SQL starejše različice

Povprečen znesek, ki je bil julija 2017 porabljen na sejo
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 )

 

 

Zaporedje zadetkov

Spodaj so vzorčni skripti za vprašanje, ki se glasi: V kakšnem zaporedju so si uporabniki ogledali strani?.

Standardni SQL

Zaporedje, v katerem so si uporabniki julija 2017 ogledali strani
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

SQL starejše različice

Zaporedje, v katerem so si uporabniki julija 2017 ogledali strani
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 tej poizvedbi omejite vrste zadetkov na PAGES, da iz prikaza izločite interakcije v povezavi z dogodki ali transakcijami. Vsaka vrstica izhodnih podatkov predstavlja ogled spletne strani in je prikazana po privzetem vrstnem redu polj v stavku SELECT.

 

 

Več razsežnosti po meri na ravni zadetka ali seje

razsežnost po meri na ravni zadetka
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

 

razsežnost po meri na ravni seje
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

V vsaki poizvedbi:

Stavek SELECT poizveduje za ustreznimi polji razsežnosti in meritve.

Funkcija MAX:

  • Vrne razsežnost po meri kot nov stolpec. To funkcijo lahko ponovite, da vrne več razsežnosti po meri kot nove stolpce.
  • WITHIN hits in WITHIN RECORD ovrednotita pogoj znotraj ponovljenih polj v orodju BigQuery.
  • Pogoj znotraj funkcije MAX je ovrednoten za vsako razsežnost po meri, vendar za vse, ki niso index=1 (hits) ali index=2 (sessions), vrne NULL.
  • Vrne največjo vrednost, ki je vrednost razsežnosti po meri 1 za zadetke ali vrednost razsežnosti po meri 2 za seje, ker so vse ostale vrednosti NULL.

Napredni primeri poizvedb

Zdaj ko poznate preproste poizvedbe, lahko oblikujete poizvedbe z naprednimi funkcijami, ki so na voljo v orodju BigQuery.

Izdelki, ki so jih kupile stranke, ki so kupile izdelek A (klasična e-trgovina)

Spodaj je okvirni skript za vprašanje Katere druge izdelke kupijo stranke, ki kupijo izdelek A?

izdelki, ki jih kupi stranka, ki kupi izdelek A (klasična e-trgovina)
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. V prvi vrstici izberete vse druge artikle, ki jih je kupil uporabnik, z združevalno funkcijo COUNT() pa izračunate količino za vsakega od drugih kupljenih artiklov. Rezultat je nato prikazan v polju z oznako quantity, z njim povezan artikel pa v polju izdelka z oznako other_purchased_products.
  2. V sivo obarvani podpoizvedbi izberete samo edinstvene uporabnike (fullVisitorId), ki so izvedli transakcije (totals.transactions>=1) in med transakcijo kupili izdelek A (WHERE hits.item.productName CONTAINS »ime artikla izdelka A«).

Pravila (stavka WHERE in AND) v poizvedbi na najvišji ravni (zelena) ne upoštevajo vrednosti v elementu hits.item.productName, ki so ničelne in vsebujejo izdelek A.

Sledi primer poizvedbe Če stranka kupi štiridelni komplet pisal »Brighton Metallic Pens – Set of 4«, katere druge izdelke je še kupila?

izdelki, ki jih kupi stranka, ki kupi štiridelni komplet pisal »Brighton Metallic Pens (Set of 4)« dne 24. junija 2013
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

V orodju Dremel/BigQuery uporaba stavka WHERE expr IN sproži JOIN, kar pomeni, da začnejo veljati omejitve velikosti. Natančneje, velikost desne strani stavka JOIN (v tem primeru števila obiskovalcev) mora biti manj kot 8 MB. V orodju Dremel se to imenuje broadcast JOIN. Ko velikost preseže 8 MB, morate sprožiti shuffled JOIN, kar lahko storite s sintakso JOIN EACH. Žal tega ne morete storiti s stavkom IN, lahko pa enako poizvedbo znova napišete s stavkom JOIN.

Izdelki, ki so jih kupile stranke, ki so kupile izdelek A (izboljšana e-trgovina)

Ta poizvedba je podobna prejšnji okvirni poizvedbi, vendar deluje za izboljšano e-trgovino. Uporablja tudi funkcijo TABLE_DATE_RANGE za iskanje podatkov za več dni.

izdelki, ki jih kupi stranka, ki kupi izdelek A (izboljšana e-trgovina)
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;

Povprečno število uporabniških interakcij pred nakupom

To je primer poizvedbe ukaza JOIN() [...] ON, ki je odvisen samo od podatkov storitve Analytics.

Spodaj je okvirni skript za vprašanje: Kolikšno je povprečno število uporabniških interakcij pred nakupom?

število uporabniških interakcij pred nakupom
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. Prva vrstica izvede glavno matematično operacijo, da ugotovi povprečno število uporabniških interakcij na izdelek, poizvedba pa ustvari spoj med dvema podpoizvedbama, imenovanima »Alias_Name_1« in »Alias_Name_2«.
  2. Rezultat podpoizvedbe »Alias_Name_1« je polje, ki z združevalno funkcijo SUM() sešteje vse številke zadetkov, zabeležene za izdelek.
  3. Podpoizvedba »Alias_Name_2« ugotovi število zadetkov, ki so jih izvedli uporabniki na izdelek, s funkcijo COUNT().
  4. Zadnja vrstica prikaže skupno polje (hits.item.productSku), ki si ga delita oba nabora podatkov na spoju.

Sledi primer poizvedbe Kolikšno je povprečno število uporabniških interakcij pred nakupom dne 10. septembra 2013?

število uporabniških interakcij pred nakupom dne 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;

Odstotek zaloge, prodane na izdelek

To je primer poizvedbe, ki ni odvisna le od podatkov iz storitve Google Analytics, ampak tudi od podatkov, ki niso iz storitve Google Analytics. Če uporabite oba nabora podatkov, lahko dobite vpogled v vedenje uporabnikov na bolj segmentirani ravni. V orodje BigQuery lahko uvozite podatke, ki niso iz storitve Google Analytics, vendar upoštevajte, da bo to prispevalo k vašemu mesečnemu računu za prostor za shranjevanje podatkov.

Spodaj je okvirni skript za vprašanje Kolikšen odstotek zaloge je bil prodan na izdelek?

odstotek zaloge, prodane na izdelek
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. Rezultat prve vrstice sta dve polji: Eno vsebuje vse ID-je izdelka, drugo pa je matematična operacija, ki prikazuje odstotek zaloge, prodane za ta ID izdelka.
  2. Ker je ta poizvedba odvisna od dveh naborov podatkov, morate uporabiti funkcijo JOIN() ... ON. Ta ukaz spoji vrstice iz dveh naborov podatkov na podlagi skupnega polja med njimi. V tem primeru sta nabora podatkov [ ‘Imported_DataSet’ ] in ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] so podatki, ki niso iz storitve Google Analytics. To je nabor podatkov, ki vsebuje polje meritve za to, koliko zaloge je ostalo (Imported DataSet.'stock_left_field'), in polje razsežnosti ID-ja izdelka (Imported_DataSet.'productId_field').
  4. »Alias_Name« je ime, dodeljeno podatkom, ki jih vrne sivo obarvana podpoizvedba. Ta podpoizvedba uporabi podatke iz storitve Google Analytics, da ugotovi skupno količino artiklov, prodanih na izdelek.
  5. Zadnja vrstica s stavkom ON prikaže skupno polje med obema naboroma podatkov in mesto, kjer sta nabora podatkov spojena.

Veliko spremenljivkam v tej poizvedbi je pripeto ime njihovega nabora podatkov v obliki predpone (npr. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Namen tega je, da je jasno, katero polje izbirate, in da je nedvoumno, kateremu naboru podatkov pripada.

Sledi primer poizvedbe Kolikšen odstotek zaloge je bil prodan na izdelek dne 28. julija 2013?

odstotek zaloge, prodane na izdelek dne 28. julija 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;

Dobičkonosnost posameznega izdelka

Spodaj je okvirni skript za vprašanje Kolikšna je dobičkonosnost posameznega izdelka?

dobiček po izdelkih
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. Prva vrstica vsebuje matematično operacijo za izračun skupnega dobička, ustvarjenega pri posameznem izdelku.
  2. Sivo obarvana podpoizvedba uporablja podatke, ki niso iz storitve Google Analytics, in zbere podatke o tem, koliko dobička se ustvari, ko je izdelek prodan.
  3. Rdeče obarvana podpoizvedba je podpoizvedba po podatkih iz storitve Google Analytics, ki bodo spojeni s podatki, ki niso iz storitve Google Analytics. Ta podpoizvedba izračuna količino prodanih artiklov na izdelek.
  4. Zadnja vrstica s stavkom ON pojasni, katero polje je skupno naboroma podatkov. V tem primeru je to številka ID-ja izdelka.

Sledi primer poizvedbe Kolikšna je bila dobičkonosnost posameznega izdelka dne 28. julija 2013?

dobiček po izdelkih dne 28. julija 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 );

Dobiček se izračuna tako, da se poišče razliko med ceno, po kateri se izdelek prodaja, in stroški njegove izdelave. Te informacije so shranjene v naboru podatkov, ki niso iz storitve Google Analytics.

Realna dobičkonosnost posameznega izdelka (ob upoštevanju vračil)

Spodaj je okvirni skript za vprašanje Kolikšna je realna dobičkonosnost posameznega izdelka?

realni dobiček po izdelkih
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. Ta poizvedba je zelo podobna poizvedbi Kolikšna je dobičkonosnost posameznega izdelka? Razlike so le v naboru podatkov, ki niso iz storitve Google Analytics, v sivo obarvani podpoizvedbi in v matematični operaciji, ki izračuna realni dobiček v prvi vrstici.
  2. V naboru podatkov, ki niso iz storitve Google Analytics, izračunate znesek, porabljen za vračila kupnine (v stavku SELECT rdeče podpoizvedbe).
  3. Potem izvedete matematično operacijo v prvi vrstici, da ugotovite realni dobiček, in sicer tako, da od bruto dobička odštejete prihodke, porabljene za vračila.

Za več informacij o poizvedbi glejte razdelek o dobičkonosnosti posameznega izdelka.

Sledi primer poizvedbe Kolikšna je bila realna dobičkonosnost posameznega izdelka dne 28, julija 2013?

realni dobiček po izdelkih dne 28. julija 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 ) );

Realni dobiček upošteva dobičkonosnost izdelka, v katerem so upoštevani izdelki, za katere je bila vrnjena kupnina. Skupni prihodek vračil za izdelek izračunate tako:

skupni prihodek vračil za izdelek = (cena izdelka + povrnjena cena dostave za izdelek) * količina izdelkov, za katere je bila vrnjena kupnina

Je bilo to uporabno?

Kako lahko to izboljšamo?

Potrebujete dodatno pomoč?

Poskusite te postopke:

Objava v skupnosti za pomoč Prejemite odgovore članov skupnosti.
Stik z nami Povejte nam več in pomagali vam bomo poiskati rešitev
false
Iskanje
Počisti iskanje
Zapiranje iskanja
Glavni meni
8771087014897849278
true
Iskanje v centru za pomoč
false
true
true
true
true
true
69256
false
false
false
false