BigQuery Cookbook

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

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 *
FROM [table name];

 

Bättre syntax: Använda fältnamn för att undvika onödig bearbetning
SELECT field1, field2
FROM [table name];

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.

För närvarande stöds inte cachelagrade resultat från sökfrågor med jokertecken som körs i flera tabeller, även om alternativet Use Cached Results är markerat. Om du kör samma sökfråga med jokertecken flera gånger debiteras du för varje sökfråga. Läs mer

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
FROM [Dataset name.table name];

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.

Query Debugging - Success

 

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.

Query Debugging - Error

 

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
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
Äldre SQL
Tre dagar med kommaavgränsade tabellnamn
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

 

Senaste 1 095 dagarna

Standard-SQL
Senaste 1 095 dagarna med _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
Äldre SQL
Senaste 1 095 dagarna med 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

 

Senaste 36 månaderna

Standard-SQL
Senaste 36 månaderna med _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
Äldre SQL
Senaste 36 månaderna med 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

 

Senaste tre åren

Standard-SQL
Senaste tre åren med _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
Äldre SQL
Senaste tre åren med 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

 

Specifikt datumintervall

Standard-SQL
Specifikt datumintervall med _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
Äldre SQL
Specifikt datumintervall med 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

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
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

Ä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
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

 

 

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
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

Äldre SQL

Totalt antal transaktioner per enhetswebbläsare i juli 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

 

 

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
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

Äldre SQL

Avvisningsfrekvens per trafikkälla i juli 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

 

 

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
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 )

Äldre SQL

Genomsnittligt antal produktsidvisningar för användare som köpte något i juli 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 )

 

 

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
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 )

Äldre SQL

Genomsnittligt antal produktsidvisningar för användare som inte köpte något i juli 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 )

 

 

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
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 )

Äldre SQL

Genomsnittligt antal transaktioner per användare som köpte något i juli 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 )

 

 

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
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 )

Äldre SQL

Genomsnittligt belopp som spenderades per session i juli 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 )

 

 

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
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

Äldre SQL

Sidsekvenser som användarna tittade på i juli 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

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,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

Anpassad dimension på sessionsnivå
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

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 och WITHIN 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 är index=1 (hits) eller index=2 (sessions) blir resultatet NULL.
  • 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
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. 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 rubriken quantity och motsvarande artiklar visas i produktfältet med etiketten other_purchased_products.
  2. 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
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;

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
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;

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
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. 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.
  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.
  3. 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().
  4. 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
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;

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
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. 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.
  2. 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’.
  3. [ ‘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’).
  4. ‘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.
  5. 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
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;

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
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. Den första raden innehåller den matematiska beräkningen av den totala vinsten för varje produkt.
  2. 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.
  3. 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.
  4. 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
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 );

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
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. 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.
  2. 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).
  3. 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
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 ) );

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

Var det här till hjälp?

Hur kan vi förbättra den?

Behöver du mer hjälp?

Testa detta härnäst:

Gör ett inlägg i hjälpforumet Få svar från communityn
Kontakta oss Berätta mer så hjälper vi dig
true
learning path
Välj din egen utbildningsväg

Ta en titt på google.com/analytics/learn, en ny resurs som hjälper dig att få ut mesta möjliga av Google Analytics 4. Den nya webbplatsen innehåller videor, artiklar och vägledda flöden samt länkar till Discord, bloggen, YouTube-kanalen och GitHub-lagringsplatsen för Google Analytics.

Fördjupa dina kunskaper redan i dag!

Sök
Rensa sökning
Stäng sökrutan
Huvudmeny
9372380689172745026
true
Sök i hjälpcentret
true
true
true
true
true
69256
false
false