BigQuery-veiledning

Denne funksjonen er bare tilgjengelig i Analytics 360, som er en del av Google Marketing Platform.
Finn ut mer om Google Marketing Platform.

I denne artikkelen finner du eksempler på hvordan du kan lage spørringer ved hjelp av Analytics-dataene du eksporterer til BigQuery. Vi har gjort tilgjengelig et eksempeldatasett, slik at du kan øve deg med spørringene i denne artikkelen.

Du finner følgende informasjon i denne artikkelen:

Optimalisering av spørringer

Alle spørringene du kjører, bidrar til den månedlige datamengden som blir behandlet. Hvis du velger irrelevante felt, øker du datamengden som må behandles. Da bruker du mer av den månedlige kvoten din enn du trenger. Når du bruker optimaliserte spørringer, bruker du den månedlige databehandlingskvoten på en mer effektiv måte.

Finn ut mer om prissetting.

Velg bare det du trenger

Når du formulerer en spørring, velger du de relevante feltene i SELECT-utsagnet. Hvis du ikke kaller irrelevante felt, reduserer du datamengden – og tiden det tar å behandle spørringen.

Eksempel: Unngå å bruke jokertegnoperatoren

Ugunstig valg: bruk av jokertegnoperatoren
SELECT *
FROM [tabellnavn];

 

Gunstigere valg: bruk av feltnavn for å unngå unødvendig behandling
SELECT field1, field2
FROM [tabellnavn];

Tillat bufring

Når det er mulig, bør du unngå å bruke funksjoner som felt. Funksjoner (f.eks. NOW() eller TODAY()) returnerer variable resultater. Da blir ikke spørringene bufret, og de blir returnert raskere. Bruk i stedet bestemte klokkeslett og datoer.

Bruk mellomtabeller for delspørringer som brukes mye

Hvis du ser at du ofte bruker en bestemt spørring som en delspørring, kan du lagre spørringen som en mellomtabell ved å klikke på Save as Table (Lagre som tabell) ovenfor spørringsresultatene. Deretter kan du henvise til tabellen i FROM-delen av spørringen din. På denne måten reduseres datamengden som må behandles, og behandlingstiden.

bruk av mellomtabeller
SELECT field1, field2
FROM [Dataset name.table name];

Feilsøking av spørringer

Koden din blir feilsøkt i BigQuery mens du lager den. Feilsøkingen blir angitt like nedenfor spørringen i inndatavinduet. Feilsøking er også tilgjengelig via API-et med dryRun-flagg.

Gyldige spørringer inneholder en grønn indikator du kan klikke på for å se hvor store datamengder som blir behandlet av spørringen. Med denne funksjonen har du muligheten til å optimalisere dataene dine før du kjører spørringen, slik at du kan unngå å behandle unødig store datamengder.

Query Debugging - Success

 

Ugyldige spørringer inneholder en rød indikator du kan klikke på for å se informasjon om feilen. På den måten kan du finne ut hvilken linje og kolonne feilen har oppstått i. I eksempelet nedenfor er GROUP BY-utsagnet tomt, og feilen er identifisert.

Query Debugging - Error

 

Tips og gode fremgangsmåter

Ved bruk av eksempeldatasettet

Eksempeldatasettet for Google Analytics er brukt i eksemplene nedenfor.

Du kan bruke spørringene på dine egne data ved å erstatte prosjekt- og datasettnavnene i eksemplene med dine egne prosjekt- og datasettnavn.

Ved bruk av standard SQL kontra en eldre versjon av SQL

BigQuery har støtte for to SQL-dialekter:

I artikkelen om overgang til standard SQL forklarer vi forskjellene mellom de to dialektene.

Standard SQL er nå den foretrukne SQL-dialekten for spørringer etter data som er lagret i BigQuery.

I artikkelen om aktivering av standard SQL finner du informasjon om hvordan du slår på standard SQL i BigQuery-, CLI- eller API-grensesnittet eller andre grensesnitt.

Den enkleste måten å komme i gang på er ved å ta med kommentaren «standardSQL» øverst i spørringene dine med standard SQL, slik du ser i eksemplene nedenfor.

> Med spørringer med den eldre versjonen av SQL sendes Google Analytics 360-dataene til en ny tabell hver dag. For å sende en spørring til flere tabeller av gangen kan du bruke komma som skilletegn mellom tabellnavnene, bruke TABLE_DATE_RANGE-jokertegnfunksjonen for tabeller eller bruke flere TABLE_DATE_RANGE-funksjoner med komma som skilletegn, som i eksemplene nedenfor.

Søk i flere tabeller

I eksemplene nedenfor kan du se spørringer med standard SQL og en eldre versjon av SQL for de samme dataene.

Tre dager

Standard SQL
Tre dager ved hjelp av 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
Eldre versjon av SQL
Tre dager ved hjelp av kommadelte tabellnavn
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

 

Siste 1095 dager

Standard SQL
Siste 1095 dager ved hjelp av _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
Eldre versjon av SQL
Siste 1095 dager ved hjelp av 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

 

Siste 36 måneder

Standard SQL
Siste 36 måneder ved bruk av _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
Eldre versjon av SQL
Siste 36 måneder ved hjelp av 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

 

Siste tre år

Standard SQL
Siste tre år ved hjelp av _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
Eldre versjon av SQL
Siste tre år ved hjelp av 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

 

Spesifikk datoperiode

Standard SQL
Spesifikk datoperiode ved hjelp av _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
Eldre versjon av SQL
Spesifikk datoperiode ved hjelp av 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

Siste tre år pluss dagens data (intradag)

Standard SQL
Siste tre år pluss dagens data (intradag) ved hjelp av UNION ALL og _TABLE_SUFFIX
Merk: Denne eksempelspørringen fungerer ikke med det offentlige datasettet for Google Analytics fordi det for tiden ikke finnes noen tabell med data fra dagens dato.
#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

Eldre versjon av SQL

Siste tre år pluss dagens data (intradag) ved bruk av flere TABLE_DATE_RANGE
Merk: Denne eksempelspørringen fungerer ikke med det offentlige datasettet for Google Analytics fordi det for tiden ikke finnes noen tabell med data fra dagens dato.
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

 

 

Eksempler på enkle spørringer

I denne delen finner du informasjon om hvordan du kan lage enkle spørringer ved hjelp av beregninger og dimensjoner fra eksempeldata fra Google Analytics.

Samlet antall [beregninger] per [dimensjon]?

Nedenfor finner du skripteksempler for følgende spørsmål: Hvor mange transaksjoner ble generert per enhetsnettleser totalt i juli 2017?

Standard SQL

Totalt antall transaksjoner per enhetsnettleser 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

Eldre versjon av SQL

Totalt antall transaksjoner per enhetsnettleser 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

 

 

Hva er den gjennomsnittlige fluktfrekvensen per [dimensjon]?

Den virkelige fluktfrekvensen er angitt som prosentandelen av besøk som består av en enkelt sidevisning. Nedenfor ser du skripteksempler for følgende spørsmål: Hva var den virkelige fluktfrekvensen per trafikkilde?

Standard SQL

Fluktfrekvens per trafikkilde 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

Eldre versjon av SQL

Fluktfrekvens per trafikkilde 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

 

 

Gjennomsnittlig antall sidevisninger for produkt etter kjøpertype (brukerne som foretar et kjøp kontra brukerne som ikke foretar et kjøp)

Nedenfor ser du skripteksempler for følgende spørsmål: Hva var det gjennomsnittlige antallet sidevisninger for produkt for brukerne som foretok et kjøp i juli 2017?

Standard SQL

Gjennomsnittlig antall sidevisninger for produkt for brukerne som foretok et kjøp 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 )

Eldre versjon av SQL

Gjennomsnittlig antall sidevisninger for produkt for brukerne som foretok et kjøp 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 )

 

 

Nedenfor ser du skripteksempler for følgende spørsmål: Hva var det gjennomsnittlige antallet sidevisninger for produkt for brukerne som ikke foretok et kjøp i juli 2017?

Standard SQL

Gjennomsnittlig antall sidevisninger for produkt for brukerne som ikke foretok et kjøp 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 )

Eldre versjon av SQL

Gjennomsnittlig antall sidevisninger for produkt for brukerne som ikke foretok et kjøp 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 )

 

 

Gjennomsnittlig antall transaksjoner per bruker som foretok et kjøp

Nedenfor ser du skripteksempler for følgende spørsmål: Hva var det gjennomsnittlige antallet transaksjoner per bruker som foretok et kjøp i juli 2017?

Standard SQL

Gjennomsnittlig antall transaksjoner per bruker som foretok et kjøp 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 )

Eldre versjon av SQL

Gjennomsnittlig antall transaksjoner per bruker som foretok et kjøp 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 )

 

 

Gjennomsnittlig beløp som ble brukt per økt

Nedenfor ser du skripteksempler for følgende spørsmål: Hva er det gjennomsnittlige beløpet som ble brukt per økt i juli 2017?

Standard SQL

Gjennomsnittlig beløp som ble brukt per økt 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 )

Eldre versjon av SQL

Gjennomsnittlig beløp som ble brukt per økt 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 )

 

 

Treffsekvens

Nedenfor finner du skripteksempler for følgende spørsmål: Hvilken rekkefølge ble sidene besøkt i?

Standard SQL

Rekkefølgen brukerne besøkte sidene i, 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

Eldre versjon av SQL

Rekkefølgen brukerne besøkte sidene i, 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 denne spørringen begrenser du trefftypene til PAGES, for at du skal slippe å se hendelses- eller transaksjonsinteraksjoner. Hver enkelt linje i resultatene representerer en sidevisning, og blir vist i standardrekkefølgen for feltene i SELECT-utsagnet.

 

 

Flere egendefinerte dimensjoner på treff- eller øktnivå

egendefinert dimensjon på treffnivå
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

 

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

I hver spørring:

SELECT-utsagnet spør etter de aktuelle dimensjons- og beregningsfeltene.

MAX-funksjonen:

  • returnerer den egendefinerte dimensjonen som en ny kolonne. Du kan gjenta funksjonen for at flere egendefinerte dimensjoner skal returneres som nye kolonner.
  • WITHIN-treff og WITHIN RECORD evaluerer vilkåret i gjentatte felt i BigQuery.
  • Vilkåret i MAX blir evaluert for hver egendefinerte dimensjon. NULL returneres for alle dimensjonene som ikke er av typen index=1 (treff) eller index=2 (økter).
  • returnerer maksimumsverdien. Dette er verdien for den egendefinerte dimensjonen 1 for treff eller den egendefinerte dimensjonen 2 for økter ettersom alle de andre verdiene er NULL.

Eksempler på avanserte spørringer

Nå som du har lært litt om enkle spørringer, kan du lage spørringer ved hjelp av de avanserte funksjonene som er tilgjengelige i BigQuery.

Produkter kjøpt av kunder som kjøpte Produkt A (klassisk netthandel)

Nedenfor ser du et enkelt skript for følgende spørring: Hvilke andre produkter kjøpte kundene som kjøpte produkt A?

produkter kjøpt av en kunde som kjøpte produkt A (klassisk netthandel)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [Navn på datasett]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [Navn på datasett]
  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. I den første linjen velger du alle de andre varene en bruker har kjøpt, og samlefunksjonen COUNT() brukes for å beregne antallet av de forskjellige andre varene som er kjøpt. Resultatene blir deretter vist i et felt kalt quantity, med tilhørende vare i produktfeltet kalt other_purchased_products.
  2. I den grå delspørringen velger du bare de unike brukerne (fullVisitorId) som har gjennomført transaksjoner (totals.transactions>=1), og som har kjøpt produkt A under en transaksjon (WHERE hits.item.productName CONTAINS 'Product Item Name A').

Verdiene i hits.item.productNamem som er null og inneholder produkt A, blir ignorert av reglene (WHERE- og AND-utsagn) i spørringen på øverste nivå (grønt).

Nedenfor ser du et enkelt skript for følgende spørring: Hvis en kunde har kjøpt Brighton metallpenner – fire stk, hvilke andre produkter kjøpte kunden samtidig?

produkter kjøpt av en kunde som kjøpte Brighton metallpenner (fire stk) 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 metallpenner – fire stk'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton metallpenner – fire stk'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

I Dremel/BigQuery brukes WHERE expr IN-utsagnet for å utløse en JOIN-kommando. Størrelsesbegrensninger gjelder. Størrelsen på høyre side av JOIN-kommandoen (i dette tilfellet er det antallet besøkende), må være mindre enn 8 MB. I Dremel kalles dette en broadcast JOIN-kommando. Når størrelsen overstiger 8 MB, må du utløse en shuffled JOIN-kommando. Dette kan du gjøre ved å bruke JOIN EACH-syntaksen. Det kan dessverre ikke gjøres ved hjelp av IN-utsagnet, men den samme spørringen kan omskrives ved å bruke JOIN-kommandoen.

Produkter kjøpt av kunder som kjøpte produkt A (utvidet netthandel)

Dette ligner det forrige enkle søket, men fungerer for Utvidet netthandel. Det bruker også TABLE_DATE_RANGE til å søke etter data fra flere dager.

produkter kjøpt av en kunde som kjøper produkt A (utvidet netthandel)
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;

Gjennomsnittlig antall brukerinteraksjoner før et kjøp

Dette er et eksempel på en spørring for en JOIN() [...] ON-kommando, som bygger utelukkende på Google Analytics-data.

Nedenfor ser du et enkelt skript for følgende spørring: Hva er det gjennomsnittlige antallet brukerinteraksjoner før et kjøp?

antallet brukerinteraksjoner før et kjø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 [Navn på GA-datasett]
 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 [Navn på GA-datasett]
 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. I den første linjen utføres den viktigste matematiske operasjonen for å finne det gjennomsnittlige antallet brukerinteraksjoner for hvert produkt. Med denne spørringen blir to delspørringer kalt 'Alias_Name_1' og 'Alias_Name_2', slått sammen.
  2. Alias_Name_1’ brukes for å generere et felt som bruker samlefunksjonen SUM() for å summere alle trefftallene som er registrert for et produkt.
  3. Alias_Name_2’ brukes for å finne antallet treff brukerne har stått for for hvert produkt, ved å benytte COUNT()-funksjonen.
  4. Den siste linjen inneholder fellesfeltet (hits.item.productSku), som deles mellom de to datasettene som er slått sammen.

Nedenfor ser du et enkelt skript for følgende spørring: Hva var det gjennomsnittlige antallet brukerinteraksjoner før et kjøp 10. september 2013?

antallet brukerinteraksjoner før et kjøp 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;

Prosentandelen av varebeholdningen solgt for hvert produkt

Dette er et eksempel på en spørring som ikke bare bygger på Google Analytics-data, men også på data som ikke er fra Google Analytics. Ved å kombinere begge datasettene får du en bedre forståelse av brukeratferden på et mer segmentert nivå. Du kan importere data som ikke er fra Google Analytics, i BigQuery, men husk at dette fører til at den månedlige kostnaden for datalagring blir høyere.

Nedenfor ser du et enkelt skript for følgende spørring: Hvilken prosentandel av varebeholdningen var solgt for hvert produkt?

prosentandel av varebeholdningen solgt for hvert 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-datasett]
  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ørste linjen har to felt: ett felt som inneholder alle produkt-ID-ene, og ett som er en matematisk operasjon som viser prosentandelen av varebeholdningen som er solgt for den aktuelle produkt-ID-en.
  2. Ettersom denne spørringen bygger på to datasett, må du bruke JOIN() ... ON-funksjonen. Med denne kommandoen blir radene fra de to datasettene slått sammen med utgangspunkt i fellesfeltet mellom dem. I dette tilfellet er de to datasettene [Imported_DataSet] og 'Alias_Name'.
  3. [Imported_DataSet] er dataene som ikke er fra Google Analytics. Dette er datasettet som inneholder beregningsfeltet for hvor stor del av varebeholdningen som er igjen (Imported DataSet.'stock_left_field'), og dimensjonsfeltet for produkt-ID (Imported_DataSet.'productId_field').
  4. ‘Alias_Name’ er navnet som er tilordnet til dataene som returneres av den grå delspørringen. Delspørringen bruker Analytics-data for å finne det samlede antallet varer som er solgt per produkt.
  5. I den siste linjen brukes ON-utsagnet for å vise fellesfeltet mellom de to datasettene, og hvor de to datasettene er slått sammen.

Mange av variablene i denne spørringen har datasettnavnet sitt knyttet til seg som prefiks (f.eks. Imported_DataSet.'productId_field', Alias_Name.quantity_sold). Dette er for å avklare hvilket felt du velger, og for å gjøre det tydelig hvilket datasett feltet tilhører.

Nedenfor ser du et enkelt skript for følgende spørring: Hvilken prosentandel av varebeholdningen var solgt for hvert produkt 28. juli 2013?

prosentandel av varebeholdningen solgt for hvert produkt 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;

Fortjenesten for hvert produkt

Nedenfor ser du et enkelt skript for følgende spørring: Hva er fortjenesten for hvert produkt?

fortjeneste etter 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 [Importert datasett]
  JOIN (
    SELECT hits.item.productSku, SUM(hits.item.itemQuantity) AS quantity
    FROM [Navn på GA-datasett]
    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ørste linjen inneholder den matematiske operasjonen for å beregne den samlede fortjenesten for hvert produkt.
  2. Den grå delspørringen bygger på data som ikke er fra Google Analytics, slik at dataene om hvor stor fortjenesten er når et produkt blir solgt, blir samlet inn.
  3. Den røde delspørringen er delspørringen for Google Analytics-data, som blir slått sammen med dataene som ikke er Google Analytics-data. Antallet solgte varer per produkt blir beregnet.
  4. I den siste linjen blir ON-utsagnet brukt for å avklare hvilken bane de to datasettene deler. I dette tilfellet er det produktets ID-nummer.

Nedenfor ser du et enkelt skript for følgende spørring: Hva var fortjenesten for hvert produkt 28. juli 2013?

fortjeneste etter produkt 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
);

Fortjenesten beregnes ved å finne forskjellen mellom prisen produktet selges for, og kostnadene for å produsere produktet. Denne informasjonen blir lagret i datasettet som ikke er et GA-datasett.

Virkelig fortjeneste for hvert produkt (når refusjoner er tatt med i beregningen)

Nedenfor ser du et enkelt skript for følgende spørring: Hva er den virkelige lønnsomheten for hvert produkt?

virkelig fortjeneste etter 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 [Navn på importert datasett] AS 'Imported_DataSet'
    JOIN (
      
SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [Navn på GA-datasett]
      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. Denne spørringen er svært lik spørringen Hva er fortjenesten for hvert produkt? De eneste forskjellene er i datasettet som ikke er et Google Analytics-datasett, i den grå delspørringen, og i den matematiske operasjonen der den virkelige fortjenesten blir beregnet i første linje.
  2. I datasettet som ikke er et Google Analytics-datasett, blir også det samlede beløpet som er brukt på refusjoner, beregnet (i SELECT-utsagnet i den røde delspørringen).
  3. Deretter utfører du en matematisk operasjon i linje 1 for å finne den virkelige fortjenesten. Dette gjør du ved å trekke inntektene som er brukt på refusjoner, fra bruttofortjenesten din.

Hvis du ønsker mer informasjon om spørringen, kan du se delen om fortjeneste for hvert produkt.

Nedenfor ser du et enkelt skript for følgende spørring: Hva var den virkelige fortjenesten for hvert produkt 28. juli 2013?

virkelig fortjeneste etter produkt 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 )
);

I den virkelige fortjenesten er lønnsomheten for et produkt hensyntatt etter at refunderte produkter er tatt med i betraktningen. Slik beregnes de samlede refusjonsinntektene for et produkt:

samlede refusjonsinntekter for et produkt = (prisen på produktet + refusjonsprisen for produktet) * antallet produkter som er refundert

Var dette nyttig for deg?
Hvordan kan vi forbedre den?