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
- Savjeti i najbolji primjeri iz prakse
- Primjeri osnovnih upita
- Ukupni mjerni podatak [mjerni podatak] po dimenziji [dimenzija]
- Prosječna stopa napuštanja početne stranice po dimenziji [dimenzija]
- Prosječan broj prikaza stranice proizvoda prema vrsti kupca
- Prosječan broj transakcija po kupcu
- Prosječan iznos novca koji je potrošen po sesiji
- Redoslijed učitavanja (analiza puta)
- Više prilagođenih dimenzija na razini učitavanja ili sesije
- Primjeri naprednih upita
- Proizvodi koje su kupili korisnici koji su kupili proizvod A (klasična e-trgovina)
- Proizvodi koje su kupili korisnici koji su kupili proizvod A (poboljšana e-trgovina)
- Prosječan broj interakcija korisnika prije kupnje
- Postotak prodanih zaliha po proizvodu
- Isplativost svakog proizvoda
- Stvarna isplativost svakog proizvoda
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 * |
Bolji obrazac: upotreba naziva polja za izbjegavanje nepotrebne obrade |
---|
SELECT field1, field2 |
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.
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 |
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.
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.
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 |
Stari SQL
Tri dana uz upotrebu naziva tablica odvojenih zarezom |
---|
SELECT |
Posljednjih 1095 dana
Standardni SQL
Posljednjih 1095 dana uz upotrebu funkcije _TABLE_SUFFIX |
---|
#standardSQL |
Stari SQL
Posljednjih 1095 dana uz upotrebu funkcije TABLE_DATE_RANGE |
---|
SELECT |
Posljednjih 36 mjeseci
Standardni SQL
Posljednjih 36 mjeseci uz upotrebu funkcije _TABLE_SUFFIX |
---|
#standardSQL |
Stari SQL
Posljednjih 36 mjeseci uz upotrebu funkcije TABLE_DATE_RANGE |
---|
SELECT |
Posljednje tri godine
Standardni SQL
Posljednje tri godine uz upotrebu funkcije _TABLE_SUFFIX |
---|
#standardSQL |
Stari SQL
Posljednje tri godine uz upotrebu funkcije TABLE_DATE_RANGE |
---|
SELECT |
Određeni datumski raspon
Standardni SQL
Određeni datumski raspon uz upotrebu funkcije _TABLE_SUFFIX |
---|
#standardSQL |
Stari SQL
Određeni datumski raspon uz upotrebu funkcije TABLE_DATE_RANGE |
---|
SELECT |
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 |
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 |
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 |
Stari SQL
Ukupan broj transakcija po pregledniku uređaja u srpnju 2017. |
---|
SELECT |
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 |
Stari SQL
Stopa napuštanja početne stranice u srpnju 2017. |
---|
SELECT |
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 |
Stari SQL
Prosječan broj prikaza stranice proizvoda za korisnike koji su izvršili kupnju u srpnju 2017. |
---|
SELECT |
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 |
Stari SQL
Prosječan broj prikaza stranice proizvoda za korisnike koji nisu izvršili kupnju u srpnju 2017. |
---|
SELECT |
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 |
Stari SQL
Prosječan broj transakcija po korisniku koji je izvršio kupnju u srpnju 2017. |
---|
SELECT |
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 |
Stari SQL
Prosječan iznos novca koji je potrošen po sesiji u srpnju 2017. |
---|
SELECT |
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 |
Stari SQL
Redoslijed stranica koje su pregledali korisnici u srpnju 2017. |
---|
SELECT |
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, |
prilagođena dimenzija na razini sesije |
---|
SELECT fullVisitorId, visitId, |
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
iWITHIN RECORD
procjenjuju uvjet unutar ponovljenih polja u BigQueryju.- Uvjet unutar funkcije
MAX
procjenjuje se za svaku prilagođenu dimenziju, no za svaku koja nijeindex=1 (hits)
iliindex=2 (sessions)
vraća seNULL
. - 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 |
- 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 oznakomquantity
, s povezanom stavkom u polju proizvoda s oznakomother_purchased_products
. - 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 |
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 |
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 |
- 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).
- "Alias_Name_1" upotrebljava se za izradu polja koje upotrebljava skupnu funkciju
SUM()
za izračunavanje broja svih učitavanja zabilježenih za proizvod. - "Alias_Name_2" upotrebljava se za određivanje broja učitavanja koje korisnici izvrše po proizvodu, pomoću funkcije
COUNT()
. - 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 |
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 |
- 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.
- 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’
. [ ‘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’
).‘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.- 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 |
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 |
- Prvi redak sadrži matematičku operaciju za izračunavanje ukupne zarade za svaki proizvod.
- 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.
- 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.
- 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 |
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 |
- 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.
- 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). - 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 |
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