Denne funktion er kun tilgængelig i Google Analytics 360, som er en del af Google Marketing Platform. Få flere oplysninger om Google Marketing Platform. |
Denne artikel indeholder eksempler på, hvordan man kan konstruere forespørgsler ud fra de Analytics-data, du eksporterer til BigQuery. Vi har stillet et eksempeldatasæt til rådighed, så du kan øve dig med nogle af forespørgslerne i denne artikel.
I denne artikel kan du læse om følgende:- Optimering af forespørgsler
- Tips og optimale løsninger
- Eksempler på grundlæggende forespørgsler
- I alt [metric] pr. [dimension]
- Gennemsnitlig afvisningsprocent pr. [dimension]
- Gennemsnitligt antal produktsidevisninger efter købertype
- Gennemsnitligt antal transaktioner pr. køber
- Gennemsnitligt forbrugsbeløb pr. session
- Sekvens for hits (stianalyse)
- Flere tilpassede dimensioner på hit- eller sessionsniveau
- Eksempler på avancerede forespørgsler
- Produkter, som blev købt af kunder, der har købt produkt A (klassisk e-handel)
- Produkter, som blev købt af kunder, der har købt produkt A (udvidet e-handel)
- Gennemsnitligt antal brugerinteraktioner inden køb
- Procentdel af lager solgt pr. produkt
- Rentabilitet af hvert produkt
- Reel rentabilitet af hvert produkt
Optimering af forespørgsler
Hver forespørgsel, du kører, tæller med i din månedlige databehandlingskvote. Hvis du markerer overflødige felter, øger du den mængde data, der skal behandles, og dermed bruger du mere af din månedlige kvote end nødvendigt. Optimerede forespørgsler sikrer en mere effektiv udnyttelse af din månedlige databehandlingskvote.
Få flere oplysninger om prissætning.
Vælg kun det nødvendige
Når du formulerer en forespørgsel, skal du vælge de relevante felter under udtrykket SELECT. Når du undlader at kalde overflødige felter, reducerer du den mængde data og tid, der skal bruges til at behandle forespørgslen.
Eksempel: Undgå at bruge jokertegn som operator
Dårlig praksis: brug af jokertegn som operator |
---|
SELECT * |
Bedre praksis: brug af feltnavne for at undgå unødvendig behandling |
---|
SELECT field1, field2 |
Tillad cachelagring
Undgå at bruge funktioner som felter, når det er muligt. Funktioner (eksempelvis NOW()
eller TODAY()
) returnerer variable resultater, hvilket forhindrer forespørgsler i at blive cachelagret og dermed returneret hurtigere. Brug i stedet specifikke klokkeslæt og datoer.
Brug mellemliggende tabeller ved ofte benyttede underforespørgsler
Hvis du gentagne gange benytter en bestemt forespørgsel som underforespørgsel, kan du gemme den pågældende forespørgsel som en mellemliggende tabel ved at klikke på Gem som tabel oven over forespørgselsresultaterne. Derefter kan du henvise til denne tabel i forespørgslens FROM
-sektion. Dette reducerer både mængden af data, der skal behandles, og den tid, behandlingen tager.
brug af en mellemliggende tabel |
---|
SELECT field1, field2 |
Fejlretning af forespørgsler
BigQuery foretager fejlretning af din kode, mens du opretter den. Fejlretning vises i kompositionsvinduet lige under forespørgslen. Fejlretning er også tilgængelig via API'en med dryRun-markeringen.
Gyldige forespørgsler har en grøn markering, som du kan klikke på for at se den mængde data, der behandles i forespørgslen. Denne funktion giver dig mulighed for at optimere dine data, inden du kører forespørgslen, og dermed undgå unødvendig databehandling.
Ugyldige forespørgsler har en rød markering, som du kan klikke på for at se oplysninger om fejlen samt den linje og kolonne, hvor fejlen findes. I nedenstående eksempel er udtrykket GROUP BY tomt, og fejlen er angivet.
Tips og optimale løsninger
Sådan bruger du eksempeldatasættet
De følgende eksempler bruger Google Analytics-eksempeldatasættet.
Hvis du vil bruge forespørgsler på dine egne data, skal du blot erstatte projektets og datasættets navne i eksemplerne med dit eget projekt og dine egne datasætnavne.
Brug af Standard SQL i forhold til forældet SQL
BigQuery understøtter to SQL-dialekter:
Migrering til Standard SQL forklarer forskellene mellem de to dialekter.
Standard SQL er nu den foretrukne SQL-dialekt til forespørgsel af data, der er gemt i BigQuery.
Se artiklen om aktivering af Standard SQL for at få oplysninger om aktivering af Standard SQL i BigQuery-brugerfladen, CLI, API eller den grænseflade, du bruger.
Den nemmeste måde at komme i gang på er at inkludere kommentaren "standardSQL" øverst i dine Standard SQL-forespørgsler som vist i de følgende eksempler.
Med forældet SQL overføres Google Analytics 360-data til en ny tabel hver dag. Hvis du vil sende forespørgsler til flere tabeller på én gang, kan du adskille tabelnavnene med komma, bruge TABLE_DATE_RANGE
jokertegnfunktionen i tabellen eller bruge flere tabelfunktioner af typen TABLE_DATE_RANGE
adskilt med komma som i nedenstående eksempler.
Forespørg om flere tabeller
De følgende eksempler viser forespørgsler med Standard SQL- og forældet SQL for de samme data.
3 dage
Standard SQL
3 dage med UNION ALL |
---|
#standardSQL |
Gammel SQL-dialekt
3 dage ved hjælp af kommaadskilte tabelnavne |
---|
SELECT |
Seneste 1095 dage
Standard SQL
Seneste 1095 dage ved hjælp af _TABLE_SUFFIX |
---|
#standardSQL |
Gammel SQL-dialekt
Seneste 1095 dage ved hjælp af TABLE_DATE_RANGE |
---|
SELECT |
Seneste 36 måneder
Standard SQL
Seneste 36 måneder ved hjælp af _TABLE_SUFFIX |
---|
#standardSQL |
Gammel SQL-dialekt
Seneste 36 måneder ved hjælp af TABLE_DATE_RANGE |
---|
SELECT |
Seneste 3 år
Standard SQL
Sidste 3 år ved hjælp af _TABLE_SUFFIX |
---|
#standardSQL |
Gammel SQL-dialekt
Sidste 3 år ved hjælp af TABLE_DATE_RANGE |
---|
SELECT |
Specifikt datointerval
Standard SQL
Specifikt datointerval ved hjælp af _TABLE_SUFFIX |
---|
#standardSQL |
Gammel SQL-dialekt
Specifikt datointerval ved hjælp af TABLE_DATE_RANGE |
---|
SELECT |
Sidste 3 år plus dagens data (i løbet af dagen)
Standard SQL
Seneste 3 dage plus data fra i dag (i løbet af dagen) ved hjælp af UNION ALL og _TABLE_SUFFIX |
---|
Bemærk! Dette forespørgselseksempel fungerer ikke med det offentlige Google Analytics-datasæt, fordi der i øjeblikket ikke er nogen tabel for data fra i løbet af dagen. |
#standardSQL |
Gammel SQL-dialekt
Seneste 3 år plus data fra i dag (i løbet af dagen) ved hjælp af flere TABLE_DATE_RANGE |
---|
Bemærk! Dette forespørgselseksempel fungerer ikke med det offentlige Google Analytics-datasæt, fordi der i øjeblikket ikke er nogen tabel for data fra i løbet af dagen. |
SELECT |
Eksempler på grundlæggende forespørgsler
I dette afsnit kan du se, hvordan du kan oprette grundlæggende forespørgsler vha. metrics og dimensioner fra Analytics-eksempeldata.
I alt [metric] pr. [dimension]?
Nedenfor er eksempler på scripts for spørgsmålet: Hvad er det samlede antal transaktioner, der blev genereret pr. enhedsbrowser i juli 2017?
Standard SQL
Samlet antal transaktioner pr. enhedsbrowser i juli 2017 |
---|
#standardSQL |
Gammel SQL-dialekt
Samlet antal transaktioner pr. enhedsbrowser i juli 2017 |
---|
SELECT |
Gennemsnitlig afvisningsprocent pr. [ dimension ]?
Den reelle afvisningsprocent defineres som procentdelen af besøg med en enkelt sidevisning. Nedenfor er eksempler på scripts for spørgsmålet: Hvad var den reelle afvisningsprocent pr. trafikkilde?
Standard SQL
Afvisningsprocent pr. trafikkilde i juli 2017 |
---|
#standardSQL |
Gammel SQL-dialekt
Afvisningsprocent pr. trafikkilde i juli 2017 |
---|
SELECT |
Gennemsnitligt antal produktsidevisninger efter købertype (købere ift. ikke-købere)
Nedenfor er eksempler på scripts for spørgsmålet: Hvad var det gennemsnitlige antal produktsidevisninger for brugere, der foretog et køb i juli 2017?
Standard SQL
Gennemsnitligt antal produktsidevisninger for brugere, der foretog et køb i juli 2017 |
---|
#standardSQL |
Gammel SQL-dialekt
Gennemsnitligt antal produktsidevisninger for brugere, der foretog et køb i juli 2017 |
---|
SELECT |
Nedenfor er eksempler på scripts for spørgsmålet: Hvad var det gennemsnitlige antal produktsidevisninger for brugere, der ikke foretog et køb i juli 2017?
Standard SQL
Gennemsnitligt antal produktsidevisninger for brugere, der ikke foretog et køb i juli 2017 |
---|
#standardSQL |
Gammel SQL-dialekt
Gennemsnitligt antal produktsidevisninger for brugere, der ikke foretog et køb i juli 2017 |
---|
SELECT |
Gennemsnitligt antal transaktioner pr. køber
Nedenfor er eksempler på scripts for spørgsmålet: Hvad var det gennemsnitlige samlede antal transaktioner for brugere, der foretog et køb i juli 2017?
Standard SQL
Gennemsnitligt antal transaktioner pr. bruger, der foretog et køb i juli 2017 |
---|
#standardSQL |
Gammel SQL-dialekt
Gennemsnitligt antal transaktioner pr. bruger, der foretog et køb i juli 2017 |
---|
SELECT |
Gennemsnitligt forbrugsbeløb pr. session
Nedenfor er eksempler på scripts for spørgsmålet: Hvad er det gennemsnitlige beløb, der blev brugt pr. session i juli 2017?
Standard SQL
Gennemsnitligt forbrugsbeløb pr. session i juli 2017 |
---|
#standardSQL |
Gammel SQL-dialekt
Gennemsnitligt forbrugsbeløb pr. session i juli 2017 |
---|
SELECT |
Sekvens for hits
Nedenfor er eksempler på scripts for spørgsmålet: Hvad er sekvensen af sider, der vises?
Standard SQL
Sekvens af sider, der ses af brugere i juli 2017 |
---|
#standardSQL |
Gammel SQL-dialekt
Sekvens af sider, der ses af brugere i juli 2017 |
---|
SELECT |
I denne forespørgsel begrænser du hittyper til PAGES
for at undgå at se interaktioner for hændelser eller transaktioner. Hver linje i outputtet repræsenterer en sidevisning og vises i standardrækkefølgen for felterne i SELECT
-udtrykket.
Flere tilpassede dimensioner på hit- eller sessionsniveau
tilpasset dimension på hitniveau |
---|
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time, |
tilpasset dimension på sessionsniveau |
---|
SELECT fullVisitorId, visitId, |
I hver forespørgsel:
SELECT
-udtrykket forespørger for de relevante dimensions- og metric-felter.
Funktionen MAX
:
- Returnerer den tilpassede dimension som en ny kolonne. Du kan gentage funktionen for at returnere flere tilpassede dimensioner som nye kolonner.
WITHIN hits
ogWITHIN RECORD
evaluerer betingelsen i gentagne felter i BigQuery.- Betingelsen i
MAX
evalueres for hver tilpasset dimension, men hvor det ikke opfyldes, atindex=1 (hits)
ellerindex=2 (sessions)
, returneresNULL
. - Returnerer den maksimale værdi, som er værdien for Tilpasset dimension 1 for hits eller Tilpasset dimension 2 for sessioner, eftersom alle andre værdier er
NULL
.
Eksempler på avancerede forespørgsler
Nu, hvor du kender til simple forespørgsler, kan du oprette forespørgsler med de avancerede funktioner i BigQuery.
Produkter, som blev købt af kunder, der har købt produkt A (klassisk e-handel)
Herunder ses en scriptstruktur til spørgsmålet: Hvilke andre produkter købes af kunder, som køber produkt A?
produkter, som blev købt af kunder, der har købt produkt A (klassisk e-handel) |
---|
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity |
- I første linje vælger du alle de andre varer, en bruger har købt, og sammenlægningsfunktionen
COUNT()
bruges til at beregne mængden af hver af de andre købte varer. Resultatet vises derefter i et felt med etikettenquantity
sammen med den tilknyttede vare i produktfeltet med etikettenother_purchased_products
. - I den grå underforespørgsel vælger du kun de unikke brugere (
fullVisitorId
), som har gennemført transaktioner (totals.transactions>=1
) og i løbet af en transaktion købt produkt A (WHERE hits.item.productName CONTAINS 'Produktets varenavn A'
).
Reglerne (WHERE
- og AND
-udtrykkene) i forespørgslen på øverste niveau (grøn) ser bort fra værdier i hits.item.productName
, som er nul og indeholder Produkt A.
Her er et eksempel på forespørgslen Hvis en kunde har køber Brighton Metallic-kuglepenne (sæt med 4 stk.), hvilke andre produkter køber vedkommende så også?
produkter købt af en kunde, som har købt 'Brighton Metallic-kuglepenne (sæt med fire stk.)' den 24. juni 2013 |
---|
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity |
I Dremel/BigQuery udløser brugen af WHERE expr IN
en JOIN-funktion, og størrelsesbegrænsninger er gældende: Størrelsen på højre side af JOIN (antal besøgende i dette eksempel) skal være mindre end 8 MB. I Dremel kaldes dette et broadcast JOIN. Når størrelsen overstiger 8 MB, skal du aktivere et shuffled JOIN, og det kan du gøre vha. syntaksen JOIN EACH. Det kan desværre ikke lade sig gøre med IN, men den samme forespørgsel kan omskrives med JOIN.
Produkter, som blev købt af kunder, der har købt produkt A (udvidet e-handel)
Denne er den samme som den tidligere skabelonforespørgsel, men fungerer for udvidet e-handel. Den bruger også TABLE_DATE_RANGE
til at forespørge om data fra flere dage.
produkter, som blev købt af kunder, der køber produkt A (udvidet e-handel) |
---|
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity |
Gennemsnitligt antal brugerinteraktioner inden køb
Dette er et eksempel på en forespørgsel for en JOIN() [...] ON
-kommando. som kun er afhængig af Analytics-data.
Herunder ses en scriptstruktur til spørgsmålet: Hvad er det gennemsnitlige antal brugerinteraktioner inden et køb?
antal brugerinteraktioner inden et køb |
---|
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number |
- Første linje udfører den primære matematiske operation for at finde det gennemsnitlige antal brugerinteraktioner pr. produkt, og denne forespørgsel opretter en forbindelse mellem to underforespørgsler, som kaldes 'Alias_Name_1' og 'Alias_Name_2'.
- 'Alias_Name_1' bruges til at generere et felt, der benytter sammenlægningsfunktionen
SUM()
til at addere alle hitværdierne for et produkt. - 'Alias_Name_2' bruges til at finde antallet af hits foretaget af brugere pr. produkt vha.
COUNT()
-funktionen. - Sidste linje viser det fælles felt (
hits.item.productSku
), der deles af de to datasæt i forbindelsen.
Her er et eksempel på forespørgslen Hvad er det gennemsnitlige antal brugerinteraktioner inden et køb den 10. september 2013?
antal brugerinteraktioner inden et køb den 10. september 2013 |
---|
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number |
Procentdel af lager solgt pr. produkt
Dette er et eksempel på en forespørgsel, som ikke kun er afhængig af Analytics-data, men også af ikke-Analytics-data. Ved at kombinere begge datasæt kan du begynde at forstå brugeradfærden på et mere segmenteret niveau. Du kan importere ikke-Analytics-data til BigQuery, men vær opmærksom på, at dette tæller med i forhold til dit månedlige gebyr for datalagring.
Herunder ses en scriptstruktur til spørgsmålet: Hvilken procentdel af lageret blev solgt pr. produkt?
procentdel af lager solgt pr. produkt |
---|
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold |
- Første linje genererer to felter: Ét felt med alle produkt-id'erne og ét, som er en matematisk operation, der viser den solgte procentdel af lageret for det pågældende produkt-id.
- Eftersom denne forespørgsel er afhængig af to datasæt, skal du bruge funktionen
JOIN() ... ON
. Denne kommando forbinder rækkerne fra de to datasæt baseret på det fælles felt mellem dem. I dette tilfælde er de to datasæt[ ‘Imported_DataSet’ ]
og‘Alias_Name’
. [ ‘Imported_DataSet’ ]
er ikke-Analytics-dataene. Dette er det datasæt, som indeholder metric-feltet for resterende lager (Imported DataSet.’stock_left_field’
) og produkt-id-dimensionsfeltet (Imported_DataSet.’productId_field’
).- Dataene, som returneres af den grå underforespørgsel, tildeles navnet
'Alias_Name'
. Denne underforespørgsel bruger Analytics-data til at finde den samlede mængde solgte varer pr. produkt. - Sidste linje bruger
ON
-udtrykket til at vise det fælles felt mellem de to datasæt, og hvor de to datasæt er forbundet.
Mange af variablerne i denne forespørgsel har deres datasætnavne tilknyttet som præfikser (f.eks. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold
). Det er for at tydeliggøre, hvilket felt du vælger, og hvilket datasæt det tilhører.
Her er et eksempel på forespørgslen Hvilken procentdel af lageret blev solgt pr. produkt den 28. juli 2013?
procentdel af lager solgt pr. produkt den 28. juli 2013 |
---|
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold |
Rentabilitet af hvert produkt
Herunder ses en scriptstruktur til spørgsmålet: Hvad er rentabiliteten af hvert produkt?
fortjeneste pr. produkt |
---|
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit |
- Første linje indeholder den matematiske operation, der beregner den samlede fortjeneste på hvert produkt.
- Den grå underforespørgsel bruger ikke-Analytics-data, som indsamler dataene om fortjenesten på et solgt produkt.
- Den røde underforespørgsel er for Analytics-data, som forbindes med ikke-Analytics-dataene. Den beregner mængden af solgte varer pr. produkt.
- Den sidste linje bruger
ON
-udtrykket til at klarlægge det felt, som deles af de to datasæt. I dette tilfælde er det produkt-id-nummeret.
Her er et eksempel på forespørgslen Hvad var rentabiliteten for hvert produkt den 28. juli 2013?
fortjeneste pr. produkt den 28. juli 2013 |
---|
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit |
Fortjenesten beregnes ved at finde forskellen på salgsprisen og omkostningerne ved at fremstille produktet. Disse oplysninger gemmes i ikke-GA-datasættet.
Reel rentabilitet af hvert produkt (tilbageførsler indregnes)
Herunder ses en scriptstruktur til spørgsmålet: Hvad er den reelle rentabilitet af hvert produkt?
reel fortjeneste pr. produkt |
---|
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit |
- Denne forespørgsel svarer i høj grad til Hvad er rentabiliteten af hvert produkt? De eneste forskelle er i ikke-Analytics-datasættet i den grå underforespørgsel og i den matematiske operation, som beregner den reelle fortjeneste i første linje.
- I ikke-Google Analytics-datasættet beregner du også det samlede beløb, som er brugt på tilbageførsler (i
SELECT
-udtrykket i den røde underforespørgsel). - Derefter udfører du en matematisk operation i linje 1 for at finde den reelle fortjeneste ved at trække den omsætning, der bruges på tilbageførsler, fra din bruttofortjeneste.
Du kan få flere oplysninger om forespørgslen i afsnittet om rentabiliteten af hvert produkt.
Her er et eksempel på følgende forespørgsel: Hvad var den reelle rentabilitet af hvert produkt den 28. juli 2013?
reel fortjeneste pr. produkt den 28. juli 2013 |
---|
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit |
I den reelle fortjeneste beregnes rentabiliteten af et produkt, når tilbageførte produkter er indregnet. Sådan beregnes den samlede tilbageførte omsætning for et produkt:
samlet tilbageført omsætning for et produkt = ( produktets pris + pris for levering af tilbageførsel for produktet ) * antallet af tilbageførte produkter