Denna funktion är endast tillgänglig i Analytics 360, en del av Google Marketing Platform. Läs mer om Google Marketing Platform. |
Den här artikeln innehåller exempel på hur du skapar sökfrågor för Analytics-data som du exporterar till BigQuery. Det finns ett exempeldataset som du kan använda för att öva med några av sökfrågorna i artikeln.
Innehåll i artikeln:- Optimering av sökfrågor
- Tips och rekommenderade metoder
- Exempel på enkla sökfrågor
- Totalt [metric] per [dimension]
- Genomsnittlig avvisningsfrekvens per [dimension]
- Genomsnittligt antal produktsidvisningar per köpartyp
- Genomsnittligt antal transaktioner per köpare
- Genomsnittligt belopp som spenderades per session
- Träffsekvens (sökvägsanalys)
- Flera anpassade dimensioner på träff- eller sessionsnivå
- Exempel på avancerade sökfrågor
- Produkter som köptes av kunder som köpte produkt A (klassisk e-handel)
- Produkter som köptes av kunder som köpte produkt A (förbättrad e-handel)
- Genomsnittligt antal användarinteraktioner före köp
- Procentandel av lagret som såldes per produkt
- Lönsamhet för varje produkt
- Faktisk lönsamhet för varje produkt
Optimering av sökfrågor
Alla sökfrågor som du kör räknas in i din månadskvot för databearbetning. Om du väljer onödiga fält ökar mängden data som behöver bearbetas, vilket betyder att du förbrukar mer av din månadskvot än nödvändigt. Genom att optimera sökfrågorna utnyttjar du månadskvoten för databearbetning mer effektivt.
Läs mer om priser.
Välj bara det du behöver
Välj relevanta fält i SELECT-uttrycket när du utformar en sökfråga. Genom att inte anropa onödiga fält minskar du mängden data och tiden som krävs för att bearbeta sökfrågan.
Exempel: Undvik att använda jokerteckenoperatorn.
Dålig syntax: Använda jokerteckenoperator |
---|
SELECT * |
Bättre syntax: Använda fältnamn för att undvika onödig bearbetning |
---|
SELECT field1, field2 |
Tillåt cachelagring
Undvik om möjligt att använda funktioner som fält. Funktioner (som NOW()
och TODAY()
) returnerar variabla resultat, vilket förhindrar att sökfrågorna cachelagras och därmed returneras snabbare. Använd specifika tidpunkter och datum i stället.
Använd mellanliggande tabeller för vanligt förekommande delsökfrågor
Om du märker att du ofta använder en specifik sökfråga som delsökfråga kan du spara den som en mellanliggande tabell genom att klicka på Spara som tabell ovanför sökresultaten. Sedan kan du referera till den tabellen i avsnittet FROM
i sökfrågan, vilket minskar både mängden data som behöver bearbetas och bearbetningstiden.
Använda en mellanliggande tabell |
---|
SELECT field1, field2 |
Felsökning av sökfrågor
BigQuery felsöker koden efter hand som du skriver den. Felsökningen visas strax under sökfrågan i meddelandefönstret. Du kan även felsöka via API:et med hjälp av dryRun-flagga.
Giltiga sökfrågor visas med en grön indikator som du kan klicka på för att se mängden data som har bearbetats av sökfrågan. Med den här funktionen kan du optimera data innan du kör sökfrågan för att undvika onödig databearbetning.
Ogiltiga sökfrågor visas med en röd indikator som du kan klicka på för att se information om felet och hitta raden och kolumnen där det förekommer. I exemplet nedan är GROUP BY-uttrycket tomt och felet markerat.
Tips och rekommenderade metoder
Använda exempeldatasetet
I följande exempel använder vi exempeldatasetet från Google Analytics.
Om du vill använda sökfrågorna på din egen data byter du helt enkelt ut namnen på projektet och datasetet i exemplen med dina egna namn.
Använda standard-SQL eller äldre SQL
BigQuery har stöd för två SQL-dialekter:
I artikeln Migrating to Standard SQL går vi igenom skillnaderna mellan de två dialekterna.
Standard-SQL är den rekommenderade SQL-dialekten för sökningar i data som lagras i BigQuery.
Se artikeln Enabling Standard SQL för mer information om hur du aktiverar standard-SQL i BigQuery-användargränssnittet, CLI, API eller det gränssnitt du använder.
Det enklaste sättet att komma igång är att inkludera kommentaren ”standardSQL” överst i SQL-sökfrågan enligt följande exempel.
När du använder äldre SQL skickas Google Analytics 360-data till en ny tabell varje dag. Om du vill söka i flera tabeller samtidigt kan du avgränsa tabellnamnen med kommatecken, använda tabelljokerfunktionen TABLE_DATE_RANGE
eller använda flera kommaavgränsade TABLE_DATE_RANGE
-funktioner, som i följande exempel.
Söka i flera tabeller samtidigt
I följande exempel visas sökfrågor med standard-SQL och äldre SQL för samma data.
Tre dagar
Standard-SQL
Tre dagar med UNION ALL |
---|
#standardSQL |
Äldre SQL
Tre dagar med kommaavgränsade tabellnamn |
---|
SELECT |
Senaste 1 095 dagarna
Standard-SQL
Senaste 1 095 dagarna med _TABLE_SUFFIX |
---|
#standardSQL |
Äldre SQL
Senaste 1 095 dagarna med TABLE_DATE_RANGE |
---|
SELECT |
Senaste 36 månaderna
Standard-SQL
Senaste 36 månaderna med _TABLE_SUFFIX |
---|
#standardSQL |
Äldre SQL
Senaste 36 månaderna med TABLE_DATE_RANGE |
---|
SELECT |
Senaste tre åren
Standard-SQL
Senaste tre åren med _TABLE_SUFFIX |
---|
#standardSQL |
Äldre SQL
Senaste tre åren med TABLE_DATE_RANGE |
---|
SELECT |
Specifikt datumintervall
Standard-SQL
Specifikt datumintervall med _TABLE_SUFFIX |
---|
#standardSQL |
Äldre SQL
Specifikt datumintervall med TABLE_DATE_RANGE |
---|
SELECT |
Senaste tre åren plus dagens data (under dagen)
Standard-SQL
Senaste tre åren plus dagens data (under dagen) med UNION ALL och _TABLE_SUFFIX |
---|
Obs! Denna exempelsökfråga fungerar inte med det offentliga datasetet från Google Analytics, eftersom det för närvarande inte innehåller en under dagen-tabell. |
#standardSQL |
Äldre SQL
Senaste tre åren plus dagens data (under dagen) med flera TABLE_DATE_RANGE |
---|
Obs! Denna exempelsökfråga fungerar inte med det offentliga datasetet från Google Analytics, eftersom det för närvarande inte innehåller en under dagen-tabell. |
SELECT |
Exempel på enkla sökfrågor
I det här avsnittet beskriver vi hur du skapar enkla sökfrågor med hjälp av mätvärden och dimensioner från exempeldata från Analytics.
Totalt [mätvärde] per [dimension]?
Nedan följer exempelskript för sökfrågan Hur många transaktioner genererades totalt per enhetswebbläsare i juli 2017?
Standard-SQL
Totalt antal transaktioner per enhetswebbläsare i juli 2017 |
---|
#standardSQL |
Äldre SQL
Totalt antal transaktioner per enhetswebbläsare i juli 2017 |
---|
SELECT |
Genomsnittlig avvisningsfrekvens per [ dimension ]?
Faktisk avvisningsfrekvens definieras som procentandelen besök med en enda sidvisning. Nedan följer exempelskript för frågan Vilken var den faktiska avvisningsfrekvensen per trafikkälla?.
Standard-SQL
Avvisningsfrekvens per trafikkälla i juli 2017 |
---|
#standardSQL |
Äldre SQL
Avvisningsfrekvens per trafikkälla i juli 2017 |
---|
SELECT |
Genomsnittligt antal produktsidvisningar utifrån köpartyp (användare som köper jämfört med användare som inte köper)
Nedan följer exempelskript för sökfrågan Vad var det genomsnittliga antalet produktsidvisningar för användare som köpte något i juli 2017?.
Standard-SQL
Genomsnittligt antal produktsidvisningar för användare som köpte något i juli 2017 |
---|
#standardSQL |
Äldre SQL
Genomsnittligt antal produktsidvisningar för användare som köpte något i juli 2017 |
---|
SELECT |
Nedan följer exempelskript för sökfrågan Vad var det genomsnittliga antalet produktsidvisningar för användare som inte köpte något i juli 2017?.
Standard-SQL
Genomsnittligt antal produktsidvisningar för användare som inte köpte något i juli 2017 |
---|
#standardSQL |
Äldre SQL
Genomsnittligt antal produktsidvisningar för användare som inte köpte något i juli 2017 |
---|
SELECT |
Genomsnittligt antal transaktioner per köpare
Nedan följer exempelskript för sökfrågan Vad var det genomsnittliga totala antalet transaktioner per användare som köpte något i juli 2017?.
Standard-SQL
Genomsnittligt antal transaktioner per användare som köpte något i juli 2017 |
---|
#standardSQL |
Äldre SQL
Genomsnittligt antal transaktioner per användare som köpte något i juli 2017 |
---|
SELECT |
Genomsnittligt belopp som spenderades per session
Nedan följer exempelskript för sökfrågan Vilket är det genomsnittliga belopp som spenderades per session i juli 2017?.
Standard-SQL
Genomsnittligt belopp som spenderades per session i juli 2017 |
---|
#standardSQL |
Äldre SQL
Genomsnittligt belopp som spenderades per session i juli 2017 |
---|
SELECT |
Träffsekvens
Nedan följer exempelskript för sökfråga: I vilken sekvens visades sidorna?.
Standard-SQL
Sidsekvenser som användarna tittade på i juli 2017 |
---|
#standardSQL |
Äldre SQL
Sidsekvenser som användarna tittade på i juli 2017 |
---|
SELECT |
I denna sökfråga begränsas träfftypen till PAGES
för att undvika att ta med händelse- och transaktionsinteraktioner. Varje rad i resultatet motsvarar en sidvisning och visas i standardordningen för fälten i SELECT
-uttrycket.
Flera anpassade dimensioner på träff- eller sessionsnivå
Anpassad dimension på träffnivå |
---|
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time, |
Anpassad dimension på sessionsnivå |
---|
SELECT fullVisitorId, visitId, |
I varje sökfråga gäller följande:
SELECT
-uttrycket söker i relevanta dimensions- och mätvärdesfält.
Följande gäller för funktionen MAX
:
- Funktionen returnerar de anpassade dimensionerna som en ny kolumn. Du kan upprepa funktionen för att returnera flera anpassade dimensioner som nya kolumner.
WITHIN hits
ochWITHIN RECORD
utvärderar villkoret i upprepade fält i BigQuery.- Villkoret i
MAX
utvärderas för alla anpassade dimensioner, men för alla som inte ärindex=1 (hits)
ellerindex=2 (sessions)
blir resultatetNULL
. - Funktionen returnerar maxvärdet, vilket är värdet på anpassad dimension 1 för träffar eller anpassad dimension 2 för sessioner, eftersom alla andra värden är
NULL
.
Exempel på avancerade sökfrågor
Nu när du är bekant med enkla sökfrågor kan du skapa sökfrågor med hjälp av de avancerade funktionerna i BigQuery.
Produkter som köptes av kunder som köpte produkt A (klassisk e-handel)
Nedan följer en skriptmall för sökfrågan Vilka andra produkter köpte de kunder som köpte produkt A?.
Produkter som köptes av kunder som köpte produkt A (klassisk e-handel) |
---|
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity |
- På den första raden väljs alla andra artiklar som en användare har köpt. Sedan används sammanställningsfunktionen
COUNT()
för att beräkna hur många andra artiklar som har köpts. Resultatet visas i ett fält med rubrikenquantity
och motsvarande artiklar visas i produktfältet med etikettenother_purchased_products
. - I den grå delsökfrågan väljs de unika användare (
fullVisitorId
) som har genomfört transaktioner (totals.transactions>=1
) och under en transaktion köpt produkt A (WHERE hits.item.productName CONTAINS ”Product Item Name A”
).
Reglerna (uttrycken WHERE
och AND
) i den överordnade (grönmarkerade) sökfrågan bortser från värden i hits.item.productName
som är null och innehåller produkt A.
Här följer ett exempel på sökfrågan Om en kund köper paket med fyra stycken Brighton Metallic-pennor, vilka andra produkter köpte kunden?.
Produkter som köptes av kunder som köpte ”Brighton Metallic-pennor (paket med fyra)” den 24 juni 2013 |
---|
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity |
Om du använder WHERE expr IN
i Dremel/BigQuery utlöses en JOIN och storleksbegränsningar tillämpas. Den högra delen av JOIN (i detta fall antalet besökare) måste då vara mindre än 8 MB. I Dremel kallas detta broadcast JOIN. Om storleken överstiger 8 MB måste du utlösa en shuffled JOIN, vilket endast går att göra med syntaxen JOIN EACH. Detta kan inte åstadkommas med IN, men samma sökfråga kan skrivas om med en JOIN.
Produkter som köptes av kunder som köpte produkt A (förbättrad e-handel)
Detta påminner om sökfrågemallen ovan men fungerar för förbättrad e-handel. Den använder också TABLE_DATA_RANGE
för att söka i data från flera dagar.
Produkter som köptes av kunder som köpte produkt A (förbättrad e-handel) |
---|
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity |
Genomsnittligt antal användarinteraktioner före köp
Detta är ett exempel på en sökfråga med kommandot JOIN() [...] ON
, som endast är beroende av Analytics-data.
Här följer en skriptmall för frågan Vilket är det genomsnittliga antalet användarinteraktioner innan ett köp?.
Antal användarinteraktioner före ett köp |
---|
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number |
- Den första raden utför den primära matematiska beräkningen för att beräkna det genomsnittliga antalet användarinteraktioner per produkt. Sökfrågan skapar en koppling mellan de två delsökfrågorna Alias_Name_1 och Alias_Name_2.
- Alias_Name_1 används för att skapa ett fält som använder sammanställningsfunktionen
SUM()
för att summera alla träffnummer som har registrerats för en produkt. - Alias_Name_2 används för att ta reda på antalet träffar som användare gör per produkt med hjälp av funktionen
COUNT()
. - Den sista raden visar fältet (
hits.item.productSku
) som de två dataseten har gemensamt.
Här följer ett exempel på sökfrågan Vilket är det genomsnittliga antalet användarinteraktioner före ett köp den 10 september 2013?.
Antal användarinteraktioner före ett köp den 10 september 2013 |
---|
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number |
Procentandel av lagret som såldes per produkt
Detta är ett exempel på en sökfråga som inte bara är beroende av Analytics-data utan även av data som inte kommer från Analytics. Genom att kombinera båda dataseten kan du börja förstå användarnas beteende på en mer segmenterad nivå. Du kan importera data som inte kommer från Analytics till BigQuery, men kom ihåg att detta räknas in i månadskostnaden för datalagring.
Nedan följer en skriptmall för sökfrågan Hur stor procentandel av lagret har sålts per produkt?.
Procentandel av lagret som såldes per produkt |
---|
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold |
- Den första raden returnerar två fält. Det ena fältet innehåller alla produkt-id och det andra är en matematisk beräkning av procentandelen av lagret som såldes för detta produkt-id.
- Eftersom sökfrågan är beroende av två dataset måste du använda funktionen
JOIN() ... IB
. Detta kommando slår samman raderna från två dataset utifrån fältet de har gemensamt. I det här fallet är de två dataseten[ ‘Imported_DataSet’ ]
och‘Alias_Name’
. [ ‘Imported_DataSet’ ]
är data som inte kommer från Analytics. Detta är det dataset som innehåller mätvärdesfältet för resterande lager (Imported DataSet.’stock_left_field’
) och fältet med dimensionerna för produkt-id (Imported_DataSet.’productId_field’
).‘Alias_Name’
är namnet på den data som returneras av den grå delsökfrågan. Delsökfrågan använder Analytics-data för att beräkna det totala antalet artiklar som har sålts per produkt.- Den sista raden använder uttrycket
ON
för att visa det fält som de två dataseten har gemensamt och det fält där de har slagits samman.
Många av variablerna i sökfrågan har datasetnamnet som prefix (till exempel Imported_DataSet.’productId_field’, Alias_Name.quantity_sold
). Detta är för att förtydliga vilket fält du väljer och vilket dataset fältet tillhör.
Här följer ett exempel på sökfrågan Hur stor procentandel av lagret såldes per produkt den 28 juli 2013?.
Procentandel av lagret som såldes per 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 |
Lönsamhet för varje produkt
Nedan följer en skriptmall för sökfrågan Hur lönsam är varje produkt?.
Vinst per produkt |
---|
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit |
- Den första raden innehåller den matematiska beräkningen av den totala vinsten för varje produkt.
- Den grå delsökfrågan använder data som inte kommer från Analytics, som samlar in data om vinsten på varje såld produkt.
- Den röda delsökfrågan är delsökfrågan för Analytics-data, som slås ihop med data som inte kommer från Analytics. Den beräknar antalet artiklar som har sålts av varje produkt.
- Den sista raden använder
ON
-uttrycket för att förtydliga fältet som de två dataseten har gemensamt. I detta fall är det produktens id-nummer.
Här följer ett exempel på sökfrågan Hur lönsam var varje produkt den 28 juli 2013?.
Vinst per produkt den 28 juli 2013 |
---|
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit |
Vinsten räknas ut genom att beräkna skillnaden mellan priset som produkten säljs för och kostnaden för att tillverka den. Informationen lagras i datasetet som inte kommer från Google Analytics.
Faktisk lönsamhet för varje produkt (med hänsyn till återbetalningar)
Nedan följer en skriptmall för sökfrågan Vilken är den faktiska lönsamheten för varje produkt?.
Faktisk vinst per produkt |
---|
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit |
- Denna sökfråga är mycket lik Hur lönsam är varje produkt?. De enda skillnaderna är i datasetet som inte kommer från Analytics i den grå delsökfrågan samt i den matematiska beräkningen av faktisk vinst på första raden.
- I datasetet som inte kommer från Analytics beräknas även det totala beloppet för återbetalningar (i
SELECT
-uttrycket i den röda delsökfrågan). - Sedan genomförs en matematisk beräkning på rad ett för att ta reda på den faktiska vinsten genom att subtrahera återbetalningsbeloppet från bruttovinsten.
Se avsnittet om lönsamhet för varje produkt för mer information om sökfrågan.
Här följer ett exempel på sökfrågan Vilken var den faktiska lönsamheten för varje produkt den 28 juli 2013?.
Faktisk vinst per produkt den 28 juli 2013 |
---|
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit |
Beräkningen av faktisk vinst tar hänsyn till lönsamheten för en produkt efter att återbetalade produkter har subtraherats. Så här beräknar du den totala återbetalningen för en produkt:
total återbetalning för en produkt = (produktpris + återbetalat belopp för produkten) * antal återbetalade produkter