BigQuery-cookbook

Deze functie is alleen beschikbaar in Analytics 360, een onderdeel van Google Marketing Platform.
Meer informatie over Google Marketing Platform

In dit artikel staan voorbeelden van de manieren waarop u query's kunt uitwerken met de Analytics-gegevens die u naar BigQuery exporteert. We hebben een voorbeelddataset samengesteld, zodat u kunt oefenen met een aantal query's in dit artikel.

In dit artikel vindt u informatie over het volgende:

Optimalisatie van query's

Elke query die u uitvoert, telt mee voor uw maandelijkse tegoed voor gegevensverwerking. Als u overbodige velden selecteert, verhoogt u de hoeveelheid gegevens die moeten worden verwerkt, en hierdoor verbruikt u meer van uw maandelijkse tegoed dan nodig is. Geoptimaliseerde query's maken efficiënter gebruik van uw maandelijkse gegevensverwerkingstegoed.

Meer informatie over prijzen

Selecteer alleen wat u nodig heeft

Wanneer u een query formuleert, selecteert u de relevante velden binnen de uitdrukking 'SELECT'. Door overbodige velden niet aan te roepen, verlaagt u de hoeveelheid gegevens en de tijd die nodig is om de query te verwerken.

Voorbeeld: vermijd gebruik van het jokerteken

Slechte formulering: gebruik van het jokerteken
SELECT *
FROM [table name];

 

Betere formulering: veldnamen gebruiken om onnodige verwerking te vermijden
SELECT field1, field2
FROM [table name];

Opslaan in cachegeheugen toestaan

Vermijd waar mogelijk het gebruik van velden als functies. Functies (zoals NOW() of TODAY()) retourneren variabele resultaten, die voorkomen dat query's worden gecachet en zodoende sneller worden geretourneerd. Gebruik in plaats daarvan specifieke tijden en datums.

In het cachegeheugen opgeslagen resultaten worden momenteel niet ondersteund voor query's met een jokerteken voor meerdere tabellen, zelfs als de optie 'In cachegeheugen opgeslagen resultaten gebruiken' is aangevinkt. Als u dezelfde query met een jokerteken meerdere keren uitvoert, wordt u voor elke query gefactureerd. Meer informatie

Tussentijdse tabellen gebruiken voor veelgebruikte subquery's

Als u constateert dat u vaak een specifieke query als subquery gebruikt, kunt u die query opslaan als tussentijdse tabel door op Opslaan als tabel boven de resultaten van query's te klikken. Daarna kunt u die tabel bekijken in het gedeelte FROM van uw query, waardoor de hoeveelheid te verwerken gegevens en de nodige verwerkingstijd worden verminderd.

een tussentijdse tabel gebruiken
SELECT field1, field2
FROM [Dataset name.table name];

Fouten in query's opsporen

BigQuery spoort fouten in uw code op terwijl u deze uitwerkt. In het opstelvenster ziet u de informatie over foutopsporing vlak onder de query. Foutopsporing is ook beschikbaar via de API met dryRun-markering.

Geldige query's hebben een groene indicator waarop u kunt klikken om te bekijken hoeveel gegevens door de query worden verwerkt. Met deze functie kunt u uw gegevens optimaliseren voordat u de query uitvoert, zodat u voorkomt dat gegevens onnodig worden verwerkt.

Query Debugging - Success

 

Ongeldige query's hebben een rode indicator waarop u kunt klikken om informatie over de fout te bekijken, en de regel en de kolom te vinden waar de fout is opgetreden. In het onderstaande voorbeeld is de uitdrukking 'GROUP BY' leeg gelaten en is de fout gemarkeerd.

Query Debugging - Error

 

Tips en best practices

De voorbeelddataset gebruiken

In de volgende voorbeelden wordt de voorbeelddataset van Google Analytics gebruikt.

Als u de query's op uw eigen gegevens wilt gebruiken, vervangt u eenvoudig de project- en datasetnamen in de voorbeelden door uw eigen project- en datasetnamen.

Standaard SQL versus Legacy SQL gebruiken

BigQuery ondersteunt 2 SQL-dialecten:

In Migratie naar Standaard SQL worden de verschillen tussen de 2 dialecten uitgelegd.

Standaard SQL is het SQL-dialect dat nu de voorkeur heeft voor gegevens opvragen die zijn opgeslagen in BigQuery.

Zie Standaard SQL aanzetten voor informatie over hoe u Standaard SQL aanzet in de BigQuery-UI, -CLI, -API of welke interface u ook gebruikt.

De makkelijkste manier om aan de slag te gaan is door de opmerking 'standardSQL' op te nemen bovenaan uw standaard SQL-query's, zoals in de volgende voorbeelden wordt getoond.

Met Legacy SQL worden Google Analytics 360-gegevens elke dag opgenomen in een nieuwe tabel. Als u een query voor meerdere tabellen tegelijk wilt uitvoeren, kunt u de tabelnamen met komma's van elkaar scheiden, de TABLE_DATE_RANGE-jokertekenfunctie voor tabellen gebruiken of meerdere door komma's gescheiden TABLE_DATE_RANGE-functies gebruiken, zoals in de volgende voorbeelden.

Query's voor meerdere tabellen

In de volgende voorbeelden ziet u standaard SQL- en legacy SQL-query's voor dezelfde gegevens.

3 dagen

Standaard SQL
3 dagen met 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
Legacy SQL
3 dagen met door komma's gescheiden tabelnamen
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

 

Afgelopen 1095 dagen

Standaard SQL
Afgelopen 1095 dagen met _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
Legacy SQL
Afgelopen 1095 dagen met 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

 

Afgelopen 36 maanden

Standaard SQL
Afgelopen 36 maanden met _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
Legacy SQL
Afgelopen 36 maanden met 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

 

Afgelopen 3 jaar

Standaard SQL
Afgelopen 3 jaar met _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
Legacy SQL
Afgelopen 3 jaar met 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

 

Specifieke periode

Standaard SQL
Specifieke periode met _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
Legacy SQL
Specifieke periode met 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

Afgelopen 3 jaar plus de gegevens van vandaag (intradag)

Standaard SQL
Afgelopen 3 jaar plus de gegevens van vandaag (intradag) met UNION ALL en _TABLE_SUFFIX
Opmerking: Deze voorbeeldquery werkt niet met de openbare dataset van Google Analytics omdat er momenteel geen intradagtabel is.
#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

Legacy SQL

Afgelopen 3 jaar plus de gegevens van vandaag (intradag) met TABLE_DATE_RANGE
Opmerking: Deze voorbeeldquery werkt niet met de openbare dataset van Google Analytics omdat er momenteel geen intradagtabel is.
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

 

 

Voorbeelden van eenvoudige query's

In dit gedeelte wordt uitgelegd hoe basisquery's kunnen worden uitgewerkt aan de hand van statistieken en dimensies van voorbeeldgegevens van Analytics.

Totaal aantal [statistiek] per [dimensie]?

Hieronder staan voorbeeldscripts voor de vraag: Wat is het totale aantal transacties dat in juli 2017 per apparaatbrowser is gegenereerd?

Standaard SQL

Totaal aantal transacties per apparaatbrowser in 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

Legacy SQL

Totaal aantal transacties per apparaatbrowser in 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

 

 

Het gemiddelde bouncepercentage per [ dimensie ]?

Het reële bouncepercentage is gedefinieerd als het percentage bezoeken met één paginaweergave. Hieronder staan voorbeeldscripts voor de vraag: Wat was het reële bouncepercentage per verkeersbron?

Standaard SQL

Bouncepercentage per verkeersbron in 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

Legacy SQL

Bouncepercentage per verkeersbron in 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

 

 

Het gemiddelde aantal weergaven van productpagina's per type koper (kopers vs. niet-kopers)

Hieronder staan voorbeeldscripts voor de vraag: Wat was het gemiddelde aantal productpaginaweergaven voor gebruikers die in juli 2017 een aankoop hebben gedaan?

Standaard SQL

Gemiddeld aantal productpaginaweergaven voor gebruikers die in juli 2017 een aankoop hebben gedaan
#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 )

Legacy SQL

Gemiddeld aantal productpaginaweergaven voor gebruikers die in juli 2017 een aankoop hebben gedaan
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 )

 

 

Hieronder staan voorbeeldscripts voor de vraag: Wat was het gemiddelde aantal productpaginaweergaven voor gebruikers die in juli 2017 geen aankoop hebben gedaan?

Standaard SQL

Gemiddeld aantal productpaginaweergaven voor gebruikers die in juli 2017 geen aankoop hebben gedaan
#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 )

Legacy SQL

Gemiddeld aantal productpaginaweergaven voor gebruikers die in juli 2017 geen aankoop hebben gedaan
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 )

 

 

Het gemiddelde aantal transacties per koper

Hieronder staan voorbeeldscripts voor de vraag: Wat waren het gemiddelde totale aantal transacties per gebruiker die een aankoop deed in juli 2017?

Standaard SQL

Gemiddeld totale aantal transacties per gebruiker die een aankoop deed in 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 )

Legacy SQL

Gemiddeld totale aantal transacties per gebruiker die een aankoop deed in 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 )

 

 

Het bedrag dat gemiddeld per sessie is besteed

Hieronder staan voorbeeldscripts voor de vraag: Wat is het gemiddelde bedrag dat per sessie is uitgegeven in juli 2017?

Standaard SQL

Gemiddeld bedrag dat per sessie is besteed in 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 )

Legacy SQL

Gemiddeld bedrag dat per sessie is besteed in 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 )

 

 

Reeks van hits

Hieronder staan voorbeeldscripts voor de vraag: Wat is de volgorde van de bekeken pagina's?

Standaard SQL

Reeks van pagina's die gebruikers in juli 2017 hebben bekeken
#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

Legacy SQL

Reeks van pagina's die gebruikers in juli 2017 hebben bekeken
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

In deze query beperkt u de typen hits tot PAGES om te voorkomen dat er interacties met gebeurtenissen of transacties worden getoond. Elke regel van de uitvoer staat voor een paginaweergave en deze wordt getoond in de standaardvolgorde van de velden in de instructie SELECT.

 

 

Meerdere aangepaste dimensies op hit- of sessieniveau

aangepaste dimensie op hitniveau
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

 

aangepaste dimensie op sessieniveau
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

In elke query:

De instructie SELECT vraagt om de relevante dimensie- en statistiekvelden.

De functie MAX:

  • Retourneert de aangepaste dimensie als nieuwe kolom. U kunt de functie herhalen om meerdere aangepaste dimensies te retourneren als nieuwe kolommen.
  • WITHIN hits en WITHIN RECORD evalueren de voorwaarde in herhaalde velden in BigQuery.
  • De voorwaarde in MAX wordt geëvalueerd voor elke aangepaste dimensie, maar voor elke waarde die niet index=1 (hits) of index=2 (sessies) is, wordt NULL geretourneerd.
  • Retourneert de maximumwaarde, dat wil zeggen de waarde van Aangepaste dimensie 1 voor hits of Aangepaste dimensie 2 voor sessies, aangezien alle andere waarden NULL zijn.

Voorbeelden van geavanceerde query's

Nu u bekend bent met eenvoudige query's, kunt u query's uitwerken met behulp van de geavanceerde functies die beschikbaar zijn in BigQuery.

Producten die zijn gekocht door klanten die product A hebben gekocht (klassieke e-commerce)

Hieronder vindt u een skeletscript voor de vraag: Welke andere producten zijn gekocht door klanten die product A hebben gekocht?

producten die zijn gekocht door een klant die product A heeft gekocht (klassieke e-commerce)
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. In de eerste regel selecteert u alle andere artikelen die een gebruiker heeft gekocht en gebruikt u de verzamelfunctie COUNT() om de totalen van elk ander gekocht artikel te berekenen. Het resultaat is daarna te zien in een veld met de naam quantity, samen met het gekoppelde artikel in het productveld met de naam other_purchased_products.
  2. In de grijze subquery selecteert u uitsluitend de unieke gebruikers (fullVisitorId) die transacties (totals.transactions>=1) hebben uitgevoerd en Product A hebben gekocht tijdens een transactie (WHERE hits.item.productName CONTAINS 'Naam van product A').

De regels (instructies WHERE en AND statements) in de query op het hoogste niveau (groen) negeren waarden in hits.item.productName die leeg zijn en product A bevatten.

Hier volgt een voorbeeld voor de vraag: Als een klant 'Metalen pennen van Brighton - Set van 4' koopt, welke andere producten heeft deze klant nog meer gekocht?

producten die zijn gekocht door een klant die 'Metalen pennen van Brighton (Set van 4)' heeft gekocht op 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;

Als u in Dremel/BigQuery de instructie WHERE expr IN gebruikt, wordt een JOIN geactiveerd en gelden er beperkingen voor de grootte; de grootte van de rechterkant van de JOIN (in dit geval het aantal bezoekers) moet minder dan 8 MB zijn. In Dremel wordt dit broadcast JOIN genoemd. Als de grootte 8 MB overschrijdt, moet u een shuffled JOIN activeren met behulp van de syntaxis JOIN EACH. U kunt dit helaas niet doen met IN, maar u kunt dezelfde query wel herschrijven met een JOIN.

Producten die zijn gekocht door klanten die product A hebben gekocht (geoptimaliseerde e-commerce)

Dit skeletscript is vergelijkbaar met het vorige skeletscript, maar werkt voor geoptimaliseerde e-commerce. Het maakt ook gebruik van TABLE_DATE_RANGE om in gegevens van meerdere dagen te zoeken.

producten die zijn gekocht door een klant die product A heeft gekocht (geoptimaliseerde e-commerce)
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;

Het gemiddelde aantal gebruikersinteracties vóór de aankoop

Dit is een voorbeeld van een query van een JOIN() [...] ON-opdracht die alleen afhankelijk is van Analytics-gegevens.

Hieronder vindt u een skeletscript voor de vraag: Wat is het gemiddelde aantal gebruikersinteracties vóór een aankoop?

aantal gebruikersinteracties vóór een aankoop
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. De eerste regel voert de wiskundige hoofdbewerking uit om het gemiddelde aantal gebruikersinteracties per product te achterhalen, en deze query brengt een samenvoeging van 2 subquery's tot stand. Deze subquery's worden Alias_Name_1 en Alias_Name_2 genoemd.
  2. ‘Alias_Name_1’ wordt gebruikt om een veld te maken dat gebruikmaakt van de verzamelfunctie SUM() om alle geregistreerde hits voor een product op te tellen.
  3. ‘Alias_Name_2’ wordt gebruikt om het aantal hits van gebruikers per product te achterhalen, met behulp van de functie COUNT().
  4. In de laatste regel wordt het algemene veld (hits.item.productSku) gedeeld tussen de twee samengevoegde datasets.

Hier volgt een voorbeeld voor de vraag: Wat is op 10 september 2013 het gemiddelde aantal gebruikersinteracties voordat er een aankoop wordt gedaan?

aantal gebruikersinteracties voor een aankoop op 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;

Percentage verkochte voorraad per product

Dit is een voorbeeld van een query die niet alleen afhankelijk is van Analytics-gegevens, maar ook van gegevens die niet afkomstig zijn van Analytics. Door beide datasets te combineren, kunt u inzicht krijgen in het gedrag van gebruikers op een meer gesegmenteerd niveau. U kunt gegevens die niet afkomstig zijn van Analytics, in BigQuery importeren, maar houd er rekening mee dat dit bijdraagt aan uw maandelijkse kosten voor gegevensopslag.

Hieronder vindt u een skeletscript voor de vraag: Welk percentage voorraad is er per product verkocht?

percentage verkochte voorraad per product
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. De eerste regel voert 2 velden uit: een veld met alle product-ID's en een veld met een wiskundige bewerking waarin het percentage voorraad dat voor die product-ID is verkocht, wordt getoond.
  2. Aangezien deze query afhankelijk is van 2 datasets, moet u de functie JOIN() ... ON gebruiken. Met deze opdracht worden de rijen van de 2 datasets samengevoegd op basis van het algemene veld ertussen. In dit geval zijn dit de 2 datasets: [ ‘Imported_DataSet’ ] en ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] bevat de gegevens die niet afkomstig zijn van Analytics. Deze dataset bevat het statistiekveld voor het aantal producten dat nog op voorraad is (Imported_DataSet.’stock_left_field’) en het dimensieveld 'Product-ID' (Imported_DataSet.’productId_field’).
  4. ‘Alias_Name’ is de naam die is toegewezen aan de gegevens die zijn geretourneerd door de grijze subquery. Deze subquery maakt gebruik van Analytics-gegevens om het totale aantal verkochte artikelen per product te achterhalen.
  5. De laatste regel gebruikt de instructie ON om het algemene veld tussen de 2 datasets en de locatie waar de 2 datasets zijn samengevoegd, te laten zien.

De naam van de dataset is aan veel variabelen in deze query als voorvoegsel gekoppeld (bijv. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Dit wordt gedaan om te verduidelijken welk veld u selecteert en om expliciet aan te geven tot welke dataset de variabele behoort.

Hier volgt een voorbeeld voor de vraag: Welk percentage van de voorraad werd per product verkocht op 28 juli 2013?

percentage verkochte voorraad per product op 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;

Winstgevendheid van elk product

Hieronder vindt u een skeletscript voor de vraag: Wat is de winstgevendheid van elk product?

winst per product
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. De eerste regel bevat de wiskundige bewerking waarmee de totale winst voor elk product kan worden berekend.
  2. De grijze subquery maakt gebruik van gegevens die niet afkomstig zijn van Analytics en verzamelt gegevens over hoeveel winst er wordt gemaakt wanneer er een product wordt verkocht.
  3. De rode subquery is de subquery voor Analytics-gegevens, die wordt samengevoegd met gegevens die niet afkomstig zijn van Analytics. Deze berekent het aantal verkochte artikelen per product.
  4. De laatste regel maakt gebruik van de instructie ON om te verduidelijken welk veld de 2 datasets delen. In dit geval is dit de product-ID.

Hier volgt een voorbeeld van de query: Wat was de winstgevendheid van elk product op 28 juli 2013?

winst per product op 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 );

De winst wordt berekend aan de hand van het verschil tussen de verkoopprijs van het product en de productiekosten van het product. Deze informatie wordt opgeslagen in de dataset met gegevens die niet afkomstig zijn van GA.

De reële winstgevendheid van elk product (waarbij rekening wordt gehouden met terugbetalingen)

Hieronder vindt u een skeletscript voor de vraag: Wat is de reële winstgevendheid van elk product?

reële winst per product
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. Deze query lijkt veel op Wat is de winstgevendheid van elk product? De enige verschillen zitten in de dataset met gegevens die niet afkomstig zijn van Analytics in de grijze subquery en in de mathematische bewerking in de eerste regel die de reële winst berekent.
  2. In de dataset met gegevens die niet afkomstig zijn van Analytics, berekent u ook het totale bedrag dat aan terugbetalingen is besteed (in de instructie SELECT van de rode subquery).
  3. Daarna voert u in regel 1 een wiskundige bewerking uit om de reële winst te achterhalen: uw brutowinst min de opbrengst die aan terugbetalingen is besteed.

Ga voor meer informatie over de query naar het gedeelte over de winstgevendheid van elk product.

Hier volgt een voorbeeld van de query: Wat was de reële winstgevendheid van elk product op 28 juli 2013?

reële winst per product op 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 ) );

De reële winst is de winstgevendheid van een product nadat het bedrag voor terugbetaalde producten daarvan is afgetrokken. Doe het volgende om de totale terugbetaalde opbrengst van een product te berekenen:

totale terugbetaalde opbrengst van een product = ( de prijs van het product + de terug te betalen leveringsprijs van het product ) * het aantal terugbetaalde producten

Was dit nuttig?

Hoe kunnen we dit verbeteren?

Meer hulp nodig?

Probeer de volgende stappen:

Posten in de Help-community Krijg antwoorden van communityleden
Contact opnemen Vertel ons meer zodat we u kunnen helpen
true
learning path
Uw eigen leertraject kiezen

Ga naar google.com/analytics/learn, een nieuwe bron waarmee u het maximale uit Google Analytics 4 kunt halen. De nieuwe website bevat video's, artikelen en begeleide processen en biedt links naar de Discord, de blog, het YouTube-kanaal en de GitHub-repository van Google Analytics.

Direct aan de slag

Zoeken
Zoekopdracht wissen
Zoekfunctie sluiten
Hoofdmenu
1001921580855502199
true
Zoeken in het Helpcentrum
true
true
true
true
true
69256
false
false