Sastavljanje upita za BigQuery

Ova je značajka dostupna samo u Analyticsu 360, dijelu Google Marketing Platforma.
Saznajte više o Google Marketing Platformu.

U ovom članku nalaze se primjeri sastavljanja upita podataka Analyticsa koje izvozite u BigQuery. Na raspolaganju vam je ogledni skup podataka kako biste mogli vježbati na upitima u ovom članku.

U ovom članku:

Optimizacija upita

Svaki upit koji pokrenete ubraja se u vaše mjesečno dopuštenje obrade podataka. Ako odaberete nevažna polja, povećavate količinu podataka koju je potrebno obraditi, a stoga ćete upotrebljavati veće mjesečno dopuštenje nego što je potrebno. Optimizirani upiti učinkovito iskorištavaju mjesečno dopuštenje obrade podataka.

Saznajte više o određivanju cijena.

Odaberite samo ono što vam je potrebno

Prilikom formuliranja upita odaberite samo relevantna polja unutar tvrdnje SELECT. Nepozivanjem nevažnih polja smanjujete količinu podataka i skraćujete vrijeme potrebno za obradu upita.

Primjer: izbjegavajte upotrebljavanje operatora zamjenskog znaka

Loš obrazac: upotrebljavanje operatora zamjenskog znaka
SELECT *
FROM [table name];

 

Bolji obrazac: upotreba naziva polja za izbjegavanje nepotrebne obrade
SELECT field1, field2
FROM [table name];

Dopuštanje predmemoriranja

Kada je moguće, izbjegavajte upotrebu funkcija kao polja. Funkcije (kao što su SADA() ili DANAS()) vraćaju promjenjive rezultate, što onemogućuje predmemoriranje i brže vraćanje upita. Umjesto toga upotrebljavajte određena vremena i datume.

Trenutačno nisu podržani rezultati iz predmemorije za upite o višestrukim tablicama sa zamjenskim znakovima, čak i ako je odabrana opcija Upotreba rezultata iz predmemorije. Ako nekoliko puta izvršite isti upit sa zamjenskim znakom, naplatit će vam se svako izvršavanje upita. Saznajte više

Upotreba umetnutih tablica za često upotrebljavane podupite

Ako utvrdite da često upotrebljavate određeni upit kao podupit, taj upit možete spremiti kao umetnutu tablicu tako da kliknete Spremi kao tablicu iznad rezultata upita. Toj tablici možete pristupiti putem odjeljka FROM upita, čime ćete smanjiti količinu podataka koju je potrebno obraditi i skratiti vrijeme potrebno za obradu.

upotreba umetnute tablice
SELECT field1, field2
FROM [Dataset name.table name];

Otklanjanje pogrešaka u upitu

BigQuery otklanja pogrešku u vašem kodu dok ga sastavljate. U prozoru za sastavljanje otklanjanje pogrešaka naznačeno je neposredno ispod upita. Dostupno je i putem API-ja pomoću naredbe dryRun flag.

Važeći upiti sadrže zeleni indikator koji možete kliknuti da biste vidjeli količinu podataka koja se obrađuje u upitu. Ova značajka pruža mogućnost optimizacije podataka prije pokretanja upita kako biste izbjegli obradu nepotrebnih podataka.

Query Debugging - Success

 

Nevažeći upiti sadrže crveni indikator koji možete kliknuti da biste vidjeli informacije o pogrešci te pronašli redak i stupac u kojem dolazi do pogreške. U primjeru u nastavku tvrdnja GROUP BY prazna je i ukazuje se na pogrešku.

Query Debugging - Error

 

Savjeti i najbolji primjeri iz prakse

Upotreba oglednog skupa podataka

U sljedećim primjerima koristi se skup podataka Google Analyticsa.

Da biste upotrebljavali upite na svojim podacima, jednostavno zamijenite nazive projekta i skupa podataka iz primjera vlastitim nazivima projekta i skupa podataka.

Upotreba standardnog SQL-a u odnosu na stari SQL

BigQuery podržava dva dijalekta SQL-a:

Prelazak na standardni SQL objašnjava razlike između dva dijalekta.

Standardni SQL sada je poželjan SQL dijalekt za postavljanje upita za podatke pohranjene u BigQueryju.

Informacije o omogućivanju standardnog SQL-a u BigQueryju, korisničkom sučelju, CLI-ju, API-ju ili bilo kojem sučelju koje upotrebljavate nalaze se u odjeljku Omogućivanje standardnog SQL-a.

Najjednostavniji je način početka rada postavljanje komentara "standardSQL" na vrh upita standardnog SQL-a kao što je prikazano u primjerima u nastavku.

U starom SQL-u podaci Google Analyticsa 360 svakodnevno se prosljeđuju u novu tablicu. Da biste formulirali upit za više tablica odjednom, možete odvojiti nazive tablica zarezom, upotrijebiti funkciju zamjenskog znaka tablice TABLE_DATE_RANGE ili upotrijebiti više funkcija TABLE_DATE_RANGE odvojenih zarezom, kao u sljedećim primjerima.

Upit za više tablica

U sljedećim primjerima pokazujemo vam upite standardnog SQL-a i starog SQL-a za iste podatke.

Tri dana

Standardni SQL
Tri dana pomoću izjave 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
Stari SQL
Tri dana uz upotrebu naziva tablica odvojenih zarezom
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

 

Posljednjih 1095 dana

Standardni SQL
Posljednjih 1095 dana uz upotrebu 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
Stari SQL
Posljednjih 1095 dana uz upotrebu 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

 

Posljednjih 36 mjeseci

Standardni SQL
Posljednjih 36 mjeseci uz upotrebu 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
Stari SQL
Posljednjih 36 mjeseci uz upotrebu 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

 

Posljednje tri godine

Standardni SQL
Posljednje tri godine uz upotrebu 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
Stari SQL
Posljednje tri godine uz upotrebu 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

 

Određeni datumski raspon

Standardni SQL
Određeni datumski raspon uz upotrebu 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
Stari SQL
Određeni datumski raspon uz upotrebu 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

Posljednje tri godine i današnji podaci (unutar jednog dana)

Standardni SQL
Posljednje 3 godine i današnji podaci (unutar jednog dana) uz upotrebu funkcija UNION ALL i _TABLE_SUFFIX
Napomena: ovaj ogledni upit neće funkcionirati s javnim skupom podataka Google Analyticsa jer trenutačno ne postoji tablica podataka unutar jednog dana.
#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

Stari SQL

Posljednje 3 godine i današnji podaci (unutar jednog dana) uz upotrebu višestrukih funkcija TABLE_DATE_RANGE
Napomena: ovaj ogledni upit neće funkcionirati s javnim skupom podataka Google Analyticsa jer trenutačno ne postoji tablica podataka unutar jednog dana.
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

 

 

Primjeri osnovnih upita

Ovaj odjeljak objašnjava kako izraditi osnovne upite pomoću mjernih podataka i dimenzija na temelju primjera podataka Analyticsa.

Ukupni mjerni podatak [mjerni podatak] po dimenziji [dimenzija]?

U nastavku se nalaze primjeri skripti za pitanje: Koliki je ukupan broj transakcija generiranih po pregledniku uređaja u srpnju 2017.?

Standardni SQL

Ukupan broj transakcija po pregledniku uređaja u srpnju 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

Stari SQL

Ukupan broj transakcija po pregledniku uređaja u srpnju 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

 

 

Prosječna stopa napuštanja početne stranice po dimenziji [dimenzija]?

Stvarna stopa napuštanja početne stranice definira se kao "postotak posjeta s jednim prikazom stranice". U nastavku se nalaze primjeri skripti za pitanje: Kolika je bila stvarna stopa napuštanja početne stranice po izvoru prometa?

Standardni SQL

Stopa napuštanja početne stranice u srpnju 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

Stari SQL

Stopa napuštanja početne stranice u srpnju 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

 

 

Prosječan broj prikaza stranice proizvoda prema vrsti kupca (kupci u odnosu na korisnike koji ne izvrše kupnju)

U nastavku se nalaze primjeri skripti za pitanje: Koliki je prosječan broj prikaza stranice proizvoda za korisnike koji su izvršili kupnju u srpnju 2017.?

Standardni SQL

Prosječan broj prikaza stranice proizvoda za korisnike koji su izvršili kupnju u srpnju 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 )

Stari SQL

Prosječan broj prikaza stranice proizvoda za korisnike koji su izvršili kupnju u srpnju 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 )

 

 

U nastavku se nalaze primjeri skripti za pitanje: Koliki je bio prosječan broj prikaza stranice proizvoda za korisnike koji nisu izvršili kupnju u srpnju 2017.?

Standardni SQL

Prosječan broj prikaza stranice proizvoda za korisnike koji nisu izvršili kupnju u srpnju 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 )

Stari SQL

Prosječan broj prikaza stranice proizvoda za korisnike koji nisu izvršili kupnju u srpnju 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 )

 

 

Prosječan broj transakcija po kupcu

U nastavku se nalaze primjeri skripti za pitanje: Koliki je bio prosječan ukupni broj transakcija po korisniku koji je izvršio kupnju u srpnju 2017.?

Standardni SQL

Prosječan broj transakcija po korisniku koji je izvršio kupnju u srpnju 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 )

Stari SQL

Prosječan broj transakcija po korisniku koji je izvršio kupnju u srpnju 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 )

 

 

Prosječan iznos novca koji je potrošen po sesiji

U nastavku se nalaze primjeri skripti za pitanje: Koliki je prosječan iznos novca koji je potrošen po sesiji u srpnju 2017.?

Standardni SQL

Prosječan iznos novca koji je potrošen po sesiji u srpnju 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 )

Stari SQL

Prosječan iznos novca koji je potrošen po sesiji u srpnju 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 )

 

 

Redoslijed učitavanja

U nastavku se nalaze primjeri skripti za pitanje: Koji je bio redoslijed pregledanih stranica?.

Standardni SQL

Redoslijed stranica koje su pregledali korisnici u srpnju 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

Stari SQL

Redoslijed stranica koje su pregledali korisnici u srpnju 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

U ovom upitu ograničavate vrste učitavanja na PAGES (stranice) kako biste izbjegli prikazivanje interakcija u vezi s događajima i transakcijama. Svaki redak rezultata predstavlja prikaz stranice i prikazan je prema zadanom poretku polja u naredbi SELECT.

 

 

Više prilagođenih dimenzija na razini učitavanja ili sesije

prilagođena dimenzija na razini učitavanja
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

 

prilagođena dimenzija na razini sesije
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

U svakom upitu:

Upiti naredbe SELECT za relevantna polja dimenzije i mjernih podataka.

Funkcija MAX:

  • Vraća prilagođenu dimenziju kao novi stupac. Možete ponoviti funkciju kako biste vratili više prilagođenih dimenzija kao nove stupce.
  • Tvrdnje WITHIN i WITHIN RECORD procjenjuju uvjet unutar ponovljenih polja u BigQueryju.
  • Uvjet unutar funkcije MAX procjenjuje se za svaku prilagođenu dimenziju, no za svaku koja nije index=1 (hits) ili index=2 (sessions) vraća se NULL.
  • Vraća maksimalnu vrijednost, a to je vrijednost prilagođene dimenzije 1 za učitavanja ili prilagođenu dimenziju 2 za sesije jer su sve ostale vrijednosti NULL.

Primjeri naprednih upita

Sad kad ste se upoznali s jednostavnim upitima, možete izraditi upite pomoću naprednih funkcija i značajki dostupnih u BigQueryju.

Proizvodi koje su kupili korisnici koji su kupili proizvod A (klasična e-trgovina)

U nastavku je kostur skripte za pitanje: Koje su ostale proizvode kupili korisnici koji kupuju proizvod A?

proizvodi koje je kupio korisnik koji kupuje proizvod 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. U prvom retku odabirete sve ostale artikle koje je kupio korisnik, a za količinu pojedinih kupljenih artikala upotrebljava se skupna funkcija COUNT(). Rezultat se potom prikazuje u polju s oznakom quantity, s povezanom stavkom u polju proizvoda s oznakom other_purchased_products.
  2. U podupitu označenom sivom bojom odabirete samo pojedinačne korisnike (fullVisitorId) koji su izvršili transakcije (totals.transactions>=1) i tijekom transakcije kupili proizvod A (WHERE hits.item.productName CONTAINS "Naziv proizvoda A").

Pravila (naredbe WHERE i AND) u upitu najviše razine (označenom zelenom bojom) zanemaruju vrijednosti u hits.item.productName koje iznose null i sadrže proizvod A.

Slijedi primjer upita: Ako korisnik kupi olovke Brighton Metallic – set od 4 olovke, koje je još proizvode kupio?

proizvodi koje je kupio korisnik koji je kupio Brighton Metallic – set od 4 olovke 24. lipnja 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;

U sustavu Dremel/BigQuery upotreba funkcije WHERE expr IN pokreće naredbu JOIN te se primjenjuju ograničenja veličine; konkretno, veličina desne strane naredbe JOIN (u ovom slučaju broj posjetitelja) mora biti manja od 8 MB. U Dremelu ta se naredba naziva broadcast JOIN. Ako veličina premašuje 8 MB, trebate pokrenuti shuffled JOIN, što je moguće uraditi upotrebom sintakse JOIN EACH. Nažalost, ne može se izvršiti pomoću funkcije IN, no isti se upit može ponovno napisati s naredbom JOIN.

Proizvodi koje su kupili korisnici koji su kupili proizvod A (poboljšana e-trgovina)

Ovo je slično prethodnom kosturnom upitu, no funkcionira za poboljšanu e-trgovinu. Također iskorištava funkciju TABLE_DATE_RANGE za upite za podatke tijekom više dana.

proizvodi koje je kupio korisnik koji kupi proizvod A (poboljš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;

Prosječan broj interakcija korisnika prije kupnje

Ovo je primjer upita naredbe JOIN() [...] ON koja ovisi samo o Analytics podacima.

U nastavku je kostur skripte za pitanje: Koji je prosječan broj interakcija korisnika prije kupnje?

broj interakcija korisnika prije kupnje
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. Prvi redak izvršava glavnu matematičku operaciju određivanja prosječnog broja interakcija korisnika po proizvodu, a ovaj upit povezuje dva podupita naziva Alias_Name_1 (Kraći naziv 1) i Alias_Name_2 (Kraći naziv 2).
  2. "Alias_Name_1" upotrebljava se za izradu polja koje upotrebljava skupnu funkciju SUM() za izračunavanje broja svih učitavanja zabilježenih za proizvod.
  3. "Alias_Name_2" upotrebljava se za određivanje broja učitavanja koje korisnici izvrše po proizvodu, pomoću funkcije COUNT().
  4. Zadnji redak prikazuje zajedničko polje (hits.item.productSku) koje se dijeli između dva skupa podataka u pridruživanju.

Slijedi primjer upita Koji je prosječan broj interakcija korisnika 10. rujna 2013. prije kupnje?

broj interakcija korisnika 10. rujna 2013. prije kupnje
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;

Postotak prodanih zaliha po proizvodu

Slijedi primjer upita koji ne ovisi samo o podacima Analyticsa, nego i o podacima koji se ne odnose na Analytics. Kombiniranjem obaju skupova podataka možete steći uvid u ponašanje korisnika na segmentiranijoj razini. U BigQuery možete uvesti podatke koji se ne odnose na Analytics, no to će povećati trošak mjesečne pohrane podataka.

U nastavku je kostur skripte za pitanje: Koji je postotak prodanih zaliha po proizvodu?

postotak prodanih zaliha po proizvodu
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. Prvi redak daje dva polja: jedno polje koje sadrži sve ID-jeve proizvoda i jedno polje koje je matematička operacija koja prikazuje postotak prodanih zaliha za ID tog proizvoda.
  2. Budući da ovaj upit ovisi o dva skupa podataka, trebate upotrijebiti funkciju JOIN() ... ON. Ova naredba pridružuje retke iz dva skupa podataka na temelju polja koje im je zajedničko. U ovom slučaju dva su skupa podataka [ ‘Imported_DataSet’ ] i ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] obuhvaća podatke koji se ne odnose na Analytics. Taj skup podataka sadrži polje mjernog podatka za preostalu zalihu (Imported DataSet.’stock_left_field’) i polje dimenzije ID-ja proizvoda (Imported_DataSet.’productId_field’).
  4. ‘Alias_Name’ naziv je koji se dodjeljuje podacima koje vraća podupit označen sivom bojom. Ovaj podupit upotrebljava Analytics podatke za određivanje ukupne količine stavki prodanih po proizvodu.
  5. Posljednji redak upotrebljava naredbu ON za prikaz zajedničkog polja između dva skupa podataka i mjesta gdje se dva skupa podataka pridružuju.

Velikom broju varijabli u ovom upitu naziv skupa podataka pridružen je kao prefiks (npr. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). To je zato da bi bilo jasno koje polje odabirete i kojem skupu podataka pripada.

Slijedi primjer upita: Koji je postotak zaliha prodan po proizvodu 28. srpnja 2013.?

postotak prodanih zaliha po proizvodu 28. srpnja 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;

Isplativost svakog proizvoda

U nastavku je kostur skripte za pitanje: Koja je isplativost svakog proizvoda?

zarada po proizvodu
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. Prvi redak sadrži matematičku operaciju za izračunavanje ukupne zarade za svaki proizvod.
  2. Podupit označen sivom bojom upotrebljava podatke koji se ne odnose na Analytics, a prikuplja podatke o tome kolika se zarada ostvari prilikom prodaje proizvoda.
  3. Podupit označen crvenom bojom podupit je podataka Analyticsa koji se pridružuje podacima koji se ne odnose na Analytics. Izračunava količinu prodanih stavki po proizvodu.
  4. Posljednji redak upotrebljava naredbu ON kako bi bilo jasno koje polje dijele dva skupa podataka. U ovom slučaju riječ je o ID broju proizvoda.

Slijedi primjer upita: Kolika je bila isplativost svakog proizvoda 28. srpnja 2013.?

isplativost po proizvodu 28. srpnja 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 );

Zarada se izračunava utvrđivanjem razlike između cijene po kojoj je proizvod prodan i troška njegove proizvodnje. Ta se informacija pohranjuje u skupu podataka koji ne pripada GA-u.

Stvarna isplativost svakog proizvoda (uzimanje povrata novca u obzir)

U nastavku je kostur skripte za pitanje: Koja je stvarna isplativost svakog proizvoda?

stvarna zarada po proizvodu
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. Ovaj je upit vrlo sličan upitu: Koja je isplativost svakog proizvoda? Jedine su razlike u skupu podataka koji se ne odnose na Analytics u podupitu označenom sivom bojom i matematičkoj operaciji koja izračunava stvarnu zaradu u prvom retku.
  2. U skupu podataka koji se ne odnosi na Analytics također izračunavate ukupni iznos povrata novca (u naredbi SELECT podupita koji je označen crvenom bojom).
  3. Zatim izvršavate matematičku operaciju u prvom retku da biste odredili stvarnu zaradu oduzimanjem prihoda potrošenog na povrat sredstava od bruto dobiti.

Više informacija o upitu potražite u odjeljku o isplativosti svakog proizvoda.

Slijedi primjer upita: Kolika je bila stvarna isplativost svakog proizvoda za 28. srpnja 2013.?

stvarna zarada po proizvodu za 28. srpnja 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 ) );

Stvarna zarada u obzir uzima isplativost proizvoda obuhvaćajući proizvode za koje je isplaćen povrat novca. Za izračun ukupnog prihoda koji je isplaćen kao povrat sredstava za proizvod:

ukupni prihod isplaćen kao povrat sredstava za proizvod = (cijena proizvoda + cijena isporuke povrata sredstava za proizvod) * količina proizvoda za koje je isplaćen povrat sredstava

Je li to bilo korisno?

Kako to možemo poboljšati?

Trebate li dodatnu pomoć?

Slijedite ove upute:

Objavite u zajednici za pomoć Neka vam odgovore članovi zajednice
Kontaktirajte nas Recite nam više i pomoći ćemo vam da pronađete rješenje
true
learning path
Odaberite vlastitu putanju učenja

Pogledajte google.com/analytics/learn, novi resurs koji će vam pomoći da maksimalno iskoristite Google Analytics 4. Nova web-lokacija uključuje videozapise, članke i vođene tokove te sadrži veze na Google Analyticsov Discord, blog, YouTube kanal i GitHub spremište.

Počnite učiti već danas!

Pretraživanje
Izbriši pretraživanje
Zatvaranje pretraživanja
Glavni izbornik
13562631740882341895
true
Pretraži Centar za pomoć
true
true
true
true
true
69256
false
false