BigQuery-kookboek

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 aan de hand van 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:

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 datgene 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 [tabelnaam];

 

Betere formulering: veldnamen gebruiken om onnodige verwerking te vermijden
SELECT veld1, veld2
FROM [tabelnaam];

Caching 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. Vervolgens kunt u die tabel raadplegen in het gedeelte FROM van uw query, waardoor de hoeveelheid te verwerken gegevens lager wordt en de benodigde verwerkingstijd wordt verkort.

een tussentijdse tabel gebruiken
SELECT veld1, veld2
FROM [Naam van dataset.tabelnaam];

Fouten in query's opsporen

BigQuery spoort fouten in uw code op terwijl u deze uitwerkt. In het compositievenster wordt foutopsporing vlak onder de query weergegeven. Foutopsporing is tevens beschikbaar via de API met dryRun-markering.

Geldige query's hebben een groene indicator waarop u kunt klikken om de hoeveelheid gegevens te bekijken die door de query is verwerkt. Deze functie biedt u de mogelijkheid om uw gegevens te 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

 

Hints en praktische tips

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 verouderde SQL gebruiken

BigQuery ondersteunt twee SQL-dialecten:

Migratie naar Standaard SQL verklaart de verschillen tussen de twee dialecten.

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

Zie Standaard SQL inschakelen voor informatie over het inschakelen van Standaard SQL in de BigQuery-UI, -CLI, -API of welke interface u ook gebruikt.

De gemakkelijkste 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 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 Verouderde SQL-query's voor dezelfde gegevens.

Drie dagen

Standaard SQL
Drie 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
Verouderde SQL
Drie 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
Verouderde 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
Verouderde 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 drie 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
Verouderde 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
Verouderde 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

Verouderde 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

Verouderde 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

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

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

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

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

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

 

 

Volgorde van hits

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

Standaard SQL

Volgorde van pagina's die in juli 2017 door gebruikers zijn 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

Verouderde SQL

Volgorde van pagina's die in juli 2017 door gebruikers zijn 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 weergegeven. Elke regel van de uitvoer staat voor een paginaweergave, en deze wordt weergegeven in de standaardvolgorde van de velden in de uitdrukking 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 uitdrukking 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 staat in herhaalde velden in BigQuery.
  • De staat 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 [‘Naam van dataset’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Naam van dataset’ ]
  WHERE hits.item.productName CONTAINS 'Naam van product A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Naam van product 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 aantallen van alle aangeschafte artikelen te berekenen. Het resultaat wordt vervolgens weergegeven 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 (uitdrukkingen WHERE en AND) 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 'Metalen pennen van Brighton (Set van 4)'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Metalen pennen van Brighton (Set van 4)'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Als u in Dremel/BigQuery de uitdrukking WHERE expr IN gebruikt, wordt een JOIN geactiveerd en gelden er beperkingen voor de grootte; met name de grootte van de rechterzijde van de JOIN (in dit geval het aantal bezoekers) moet minder dan 8 MB zijn. In Dremel wordt dit een broadcast JOIN genoemd. Wanneer de grootte 8 MB overschrijdt, moet u een shuffled JOIN activeren, met behulp van de syntax JOIN EACH. Helaas kan dit niet worden gedaan met IN, maar dezelfde query kan wel worden herschreven 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 gegevens voor meerdere dagen te doorzoeken.

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 voor 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 [‘Naam van GA-dataset’ ]
 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 [‘Naam van GA-dataset’ ]
 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 twee 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 producten uit voorraad dat per product is verkocht
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 twee velden uit: één 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 weergegeven.
  2. Aangezien deze query afhankelijk is van twee datasets, moet u de functie JOIN() ... ON gebruiken. Met deze opdracht worden de rijen van de twee datasets samengevoegd op basis van het algemene veld ertussen. In dit geval zijn dit de twee 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 uitdrukking ON om het algemene veld tussen de twee datasets en de locatie waar de twee datasets zijn samengevoegd, weer te geven.

De naam van de dataset is als voorvoegsel gekoppeld aan veel van de variabelen in deze query (d.w.z. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Dit wordt gedaan ter verduidelijking van 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 uit voorraad verkochte producten 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 [ ‘Geïmporteerde dataset’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘Naam van GA-dataset’ ]
    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 uitdrukking ON om te verduidelijken welk veld de twee 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 [ ‘Naam van geïmporteerde dataset’ ] AS 'Imported_DataSet'
    JOIN (
      
SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘Naam van 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 = Alias_Name.hits.item.productSku )
);
  1. Deze query lijkt erg 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. Vervolgens 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 geretourneerde producten in overweging is genomen. 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?