BigQuery-integroinnin opas

Tämä ominaisuus on käytettävissä vain Google Marketing Platformiin kuuluvassa Google Analytics 360:ssä.
Lue lisää Google Marketing Platformista.

Tämä artikkeli sisältää esimerkkejä siitä, kuinka voit muodostaa kyselyjä BigQueryyn viedyn Analytics-datan pohjalta. Käytettävissäsi on esimerkkidatajoukko, jonka avulla voit harjoitella tässä artikkelissa kuvattujen kyselyiden käyttämistä.

Sisällys:

Kyselyiden optimointi

Kaikki suorittamasi kyselyt lasketaan mukaan sinulle sallittuun kuukausittaiseen datankäsittelykiintiöön. Jos valitset kyselyihin ylimääräisiä kenttiä, käsiteltävän datan määrä kasvaa suotta. Tällaisissa tapauksissa saatat käyttää kuukausittain sallitusta datankäsittelykiintiöstäsi enemmän kuin olisi tarpeen. Optimoimalla kyselysi voit varmistaa, että käytät kuukausittaisen sallitun datankäsittelykiintiösi mahdollisimman tehokkaasti.

Lue lisää hinnoittelusta.

Valitse vain tarpeelliset kentät

Kun muodostat kyselyä, valitse asianomaiset kentät SELECT-käskyllä. Välttämällä ylimääräisten kenttien kutsumisen voit rajoittaa käsittelyn edellyttämän datankäsittelyn määrää ja vähentää siihen kuluvaa aikaa.

Esimerkki: Vältä jokerimerkki-operaattorin käyttämistä

Ei suositeltava muoto: jokerimerkki-operaattorin käyttäminen
SELECT *
FROM [taulukon nimi];

 

Parempi muoto: kenttien nimien käyttäminen turhan datankäsittelyn välttämiseksi
SELECT kenttä1, kenttä2
FROM [taulukon nimi];

Salli välimuistitoiminnot

Jos mahdollista, vältä funktioiden käyttämistä kenttinä. Funktiot (kuten NOW() tai TODAY()) palauttavat muuttuvia tuloksia, mikä voi estää kyselyiden tallentamisen välimuistiin ja nopean palauttamisen. Käytä funktioiden sijaan tarkkoja kellonaikoja ja päivämääriä.

Tällä hetkellä välimuistiin tallennettuja tuloksia ei tueta kyselyissä, jotka liittyvät useisiin jokerimerkkiä käyttäviin taulukoihin, vaikka Käytä välimuistissa olevia tuloksia ‑asetus olisi valittuna. Jos suoritat saman jokerimerkkikyselyn useita kertoja, sinua laskutetaan jokaisesta kyselystä. Lue lisää

Käytä välitaulukkoja usein käytetyille alikyselyille

Jos käytät usein tiettyä kyselyä alikyselynä, se kannattaa tallentaa välitaulukoksi valitsemalla kyselyn tulosten yläpuolelta Tallenna taulukoksi. Voit tämän jälkeen viitata kyseiseen taulukkoon kyselysi CODE-osiosta käyttämällä alla esitettyä muotoa. Näin voit rajoittaa käsiteltävän datan määrää ja vähentää datankäsittelyyn kuluvaa aikaa.

Välitaulukon käyttäminen
SELECT kenttä1, kenttä2
FROM [Datajoukon nimi.taulukon nimi];

Kyselyiden virheenkorjaus

BigQuery tutkii koodiasi virheiden jäljittämiseksi ja korjaamiseksi koodin kirjoittamisen aikana. Nämä vianmääritystiedot näkyvät koodinkirjoitusikkunassa kyselyn alapuolella. Ne ovat myös käytettävissä sovellusliittymän kautta dryRun-merkinnän avulla.

Kelvollisilla kyselyillä on vihreä ilmaisin, jonka klikkaaminen näyttää, kuinka paljon dataa kysely käsittelee. Tämän ominaisuuden avulla käyttäjät voivat harkita datan optimointiin liittyviä asioita jo ennen kyselyn suorittamista. Näin voidaan välttää turhaa datan käsittelyä.

Query Debugging - Success

 

Virheellisillä kyselyillä on punainen ilmaisin, jonka klikkaaminen näyttää tietoja virheestä ja ohjaa käyttäjät sen rivin ja sarakkeen kohdalle, jossa virhe ilmenee. Alla olevassa esimerkissä GROUP BY ‑käskyä ei ole määritetty kokonaan, ja kyselyn alla näkyy tieto tästä virheestä.

Query Debugging - Error

 

Vinkit ja parhaat käytännöt

Esimerkkidatajoukon käyttäminen

Seuraavissa esimerkeissä käytetään Google Analyticsin esimerkkidatajoukkoa.

Jos haluat käyttää kyselyitä omassa datassasi, korvaa esimerkeissä olevat projektin ja datajoukon nimet omilla projektin ja datajoukon nimilläsi.

Vakio-SQL vs. vanha SQL

BigQuery tukee seuraavaa kahta SQL-muotoa:

Näiden muotojen erot on kuvattu tällä vakio-SQL:ään siirtymistä käsittelevällä sivulla.

Vakio-SQL on nyt SQL-muoto, jota suositellaan BigQueryyn tallennettuun dataan kohdistuvien kyselyiden tekemiseen.

Vakio-SQL:n käyttöönottoa käsittelevällä sivulla on tietoja siitä, miten voit ottaa vakio-SQL:n käyttöön BigQueryn käyttöliittymässä, komentoriviliittymässä, sovellusliittymässä tai missä tahansa muussa käyttämässäsi liittymässä.

Helpoin tapa aloittaa on lisätä kommentti "standardSQL" vakio-SQL-kyselyiden yläosaan seuraavien esimerkkien tapaan.

Vanhaa SQL:ää käytettäessä Google Analytics 360 ‑data siirretään uuteen taulukkoon päivittäin. Voit tehdä kyselyitä useisiin taulukoihin kerralla erottamalla taulukoiden nimet toisistaan pilkulla, käyttämällä taulukon jokerimerkkifunktiota TABLE_DATE_RANGE tai käyttämällä useita pilkulla erotettuja TABLE_DATE_RANGE-funktioita seuraavissa esimerkeissä kuvatuilla tavoilla.

Usean taulukon kysely

Seuraavat esimerkit sisältävät saman datan kyselyt vakio-SQL:n ja vanhan SQL:n mukaisesti.

3 päivää

Vakio-SQL
3 päivää: 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
Vanha SQL
3 päivää: pilkulla erotetut taulukoiden nimet
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

 

1 095 viime päivää

Vakio-SQL
1 095 viime päivää: _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
Vanha SQL
1 095 viime päivää: 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

 

36 edellistä kuukautta

Vakio-SQL
36 edellistä kuukautta: 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
Vanha SQL
36 edellistä kuukautta: 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

 

3 viime vuotta

Vakio-SQL
3 viime vuotta: _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
Vanha SQL
3 viime vuotta: 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

 

Tietty ajanjakso

Vakio-SQL
Tietty ajanjakso: _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
Vanha SQL
Tietty ajanjakso: 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

3 viime vuotta ja tämän päivän data (päivänsisäinen)

Vakio-SQL
Viimeiset 3 vuotta ja tämän päivän data (päivänsisäinen): UNION ALL ja _TABLE_SUFFIX
Huom. Tämä esimerkkikysely ei toimi Google Analyticsin julkisen datajoukon kanssa, koska siinä ei tällä hetkellä ole päivänsisäistä taulukkoa.
#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

Vanha SQL

Viimeiset 3 vuotta ja tämän päivän data (päivänsisäinen): Monta TABLE_DATE_RANGE-funktiota
Huom. Tämä esimerkkikysely ei toimi Google Analyticsin julkisen datajoukon kanssa, koska siinä ei tällä hetkellä ole päivänsisäistä taulukkoa.
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

 

 

Esimerkkejä peruskyselyistä

Tässä osiossa käsitellään peruskyselyiden laatimista Analytics-esimerkkidatasta saatujen mittareiden ja ulottuvuuksien perusteella.

[Mittari] yhteensä [ulottuvuutta] kohti

Alla olevien esimerkkiskriptien avulla voit selvittää, mikä oli luotujen tapahtumien kokonaismäärä laitteen selainta kohti heinäkuussa 2017.

Vakio-SQL

Tapahtumien kokonaismäärä laitteen selainta kohti heinäkuussa 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

Vanha SQL

Tapahtumien kokonaismäärä laitteen selainta kohti heinäkuussa 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

 

 

Keskimääräinen välitön poistuminen prosenteissa [ulottuvuutta] kohti

Todellinen välitön poistuminen prosenteissa on määritelmän mukaan vain yhden sivun katselun sisältävien käyntien prosenttiosuus käyntien kokonaismäärästä. Alla olevien esimerkkiskriptien avulla voit selvittää, mikä oli todellinen välitön poistuminen prosenteissa tiettyä liikenteen lähdettä kohti.

Vakio-SQL

Välitön poistuminen prosenteissa liikenteen lähdettä kohti heinäkuussa 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

Vanha SQL

Välitön poistuminen prosenteissa liikenteen lähdettä kohti heinäkuussa 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

 

 

Tuotesivun katselut keskimäärin tietyn ostajatyypin mukaan (ostoksen tehneet ja muut kuin ostoksen tehneet)

Alla olevien esimerkkiskriptien avulla voit selvittää, mikä oli ostoksia tehneiden käyttäjien keskimääräinen tuotesivun katselujen määrä heinäkuussa 2017.

Vakio-SQL

Ostoksia tehneiden käyttäjien keskimääräinen tuotesivun katselujen määrä heinäkuussa 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 )

Vanha SQL

Ostoksia tehneiden käyttäjien keskimääräinen tuotesivun katselujen määrä heinäkuussa 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 )

 

 

Alla olevien esimerkkiskriptien avulla voit selvittää, mikä oli keskimääräinen tuotesivun katselujen määrä heinäkuussa 2017 käyttäjillä, jotka eivät tehneet ostoksia.

Vakio-SQL

Keskimääräinen tuotesivun katselujen määrä heinäkuussa 2017 käyttäjillä, jotka eivät tehneet ostoksia
#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 )

Vanha SQL

Keskimääräinen tuotesivun katselujen määrä heinäkuussa 2017 käyttäjillä, jotka eivät tehneet ostoksia
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 )

 

 

Tapahtumien keskimääräinen määrä ostajaa kohti

Alla olevien esimerkkiskriptien avulla voit selvittää, mikä oli tapahtumien keskimääräinen käyttäjäkohtainen määrä ostoksia tehneillä käyttäjillä heinäkuussa 2017.

Vakio-SQL

Tapahtumien keskimääräinen käyttäjäkohtainen määrä ostoksia tehneillä käyttäjillä heinäkuussa 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 )

Vanha SQL

Tapahtumien keskimääräinen käyttäjäkohtainen määrä ostoksia tehneillä käyttäjillä heinäkuussa 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 )

 

 

Keskimääräinen istuntoa kohti käytetty rahasumma

Alla olevilla esimerkkiskripteillä voi selvittää, mikä oli keskimääräinen istuntoa kohti käytetty rahasumma heinäkuussa 2017.

Vakio-SQL

Keskimääräinen istuntoa kohti käytetty rahasumma heinäkuussa 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 )

Vanha SQL

Keskimääräinen istuntoa kohti käytetty rahasumma heinäkuussa 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 )

 

 

Osumien järjestys

Alla olevien esimerkkiskriptien avulla voit selvittää, mikä oli sivujen katselujärjestys.

Vakio-SQL

Käyttäjien katselemien sivujen järjestys heinäkuussa 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

Vanha SQL

Käyttäjien katselemien sivujen järjestys heinäkuussa 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

Tässä kyselyssä osumatyypit rajoitetaan sivuiksi (PAGES), jotta muita tapahtumia ei näytettäisi. Kukin tulosteen rivi ilmaisee yhden katselukerran, ja rivit näytetään SELECT-käskyn kenttien oletusjärjestyksessä.

 

 

Useat omat ulottuvuudet osuma- tai istuntotasolla

Oma ulottuvuus osumatasolla
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

 

Oma ulottuvuus istuntotasolla
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

Kussakin kyselyssä:

SELECT-käsky esittää kyselyt tarvittavista ulottuvuus- ja mittarikentistä.

MAX-funktio:

  • palauttaa oman ulottuvuuden uutena sarakkeena. Funktio voidaan toistaa, jos tarkoitus on palauttaa useita omia ulottuvuuksia uusina sarakkeina.
  • Arvioi BigQueryn toistuvissa kentissä olevaa ehtoa WITHIN hits- ja WITHIN RECORD ‑kohdissa
  • Arvioi MAX-funktion sisäistä ehtoa kunkin oman ulottuvuuden osalta ja palauttaa arvon NULL kaikista kohteista, jotka eivät ole index=1 (hits) tai index=2 (sessions).
  • Palauttaa enimmäisarvon, joka on oman ulottuvuuden 1 arvo osumille ja oman ulottuvuuden 2 arvo istunnoille, sillä kaikki muut arvot ovat NULL.

Esimerkkejä kehittyneistä kyselyistä

Nyt kun olet tutustunut yksinkertaisiin kyselyihin, voit muodostaa kyselyitä käyttämällä BigQueryn kehittyneitä funktioita ja ominaisuuksia.

Tuotteen A ostaneiden asiakkaiden ostamat tuotteet (perinteinen verkkokauppa)

Alla esitetyllä koodirungolla selvitetään, mitä muita tuotteita tuotteen A ostaneet asiakkaat ostivat.

Tuotteen A ostaneen asiakkaan ostamat tuotteet (perinteinen verkkokauppa)
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. Ensimmäisellä rivillä valitaan kaikki käyttäjän ostamat muut kohteet. Koostefunktiolla COUNT() lasketaan jokaisen muun ostetun kohteen määrä. Tulos näytetään tämän jälkeen quantity-kentässä ja tulokseen liittyvä tuote näytetään other_purchased_products-tuotekentässä.
  2. Harmaassa alikyselyssä valitaan vain yksittäisiä käyttäjiä (fullVisitorId), jotka ovat tehneet tapahtumia (totals.transactions>=1) ja ostaneet tapahtuman aikana tuotteen A (WHERE hits.item.productName CONTAINS 'Tuotenimikkeen nimi A').

Säännöt (WHERE-ja AND-käskyt) ylimmän tason (vihreässä) kyselyssä eivät huomioi hits.item.productName-arvoja, jotka ovat null ja sisältävät tuotteen A.

Alla olevalla esimerkkikyselyllä selvitetään, mitä muita tuotteita Brighton Metallic Pens – Set of 4 ‑tuotteen ostanut asiakas osti.

24.6.2013 tuotteen "Brighton Metallic Pens – Set of 4" ostaneiden asiakkaiden ostamat muut tuotteet
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;

Dremel/BigQuery-käytössä WHERE expr IN käynnistää JOIN-komennon, jossa kokorajoitukset ovat voimassa. Tämä tarkoittaa, että JOIN-komennon oikean puolen arvon (tässä tapauksessa vierailijoiden määrän) on oltava pienempi kuin 8 Mt. Dremelissä tätä kutsutaan broadcast JOIN ‑komennoksi. Jos koko on suurempi kuin 8 Mt, shuffled JOIN ‑käsky on kutsuttava. Tämä voidaan tehdä JOIN EACH ‑syntaksin avulla. Valitettavasti tätä ei voi tehdä IN-komennolla, mutta sama kysely on mahdollista kirjoittaa uudelleen JOIN-komennolla.

Tuotteen A ostaneiden asiakkaiden ostamat tuotteet (tehostettu verkkokauppa)

Tämä on edellä olevan kaltainen koodirunko, jota käytetään tehostetussa verkkokaupassa. Se hyödyntää myös TABLE_DATE_RANGE-toimintoa useamman päivän datakyselyissä.

Tuotteen A ostaneen asiakkaan ostamat tuotteet (tehostettu verkkokauppa)
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;

Käyttäjän interaktioiden keskimääräinen määrä ennen ostotapahtumaa

Alla on esimerkki JOIN() [...] ON ‐komennosta, joka on riippuvainen vain Analytics-datasta.

Alla esitetyllä koodirungolla selvitetään, mikä on käyttäjän interaktioiden keskimääräinen määrä ennen ostotapahtumaa.

Käyttäjän interaktioiden määrä ennen ostotapahtumaa
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. Ensimmäinen rivi suorittaa päälaskutoimituksen, jolla selvitetään käyttäjän tapahtumien keskimääräinen määrä tuotetta kohti. Tämä kysely luo join-liitoksen kahden alikyselyn eli kyselyiden 'Alias_Name_1' ja 'Alias_Name_2' välille.
  2. ‘Alias_Name_1’ tuottaa kentän, joka laskee kaikkien tuotteelle kirjattujen osumien summan koostefunktiolla SUM().
  3. ‘Alias_Name_2’ selvittää käyttäjien tuotekohtaisesti tuottamien osumien määrän COUNT()-funktiolla.
  4. Viimeinen rivi näyttää yhteisen kentän (hits.item.productSku), joka on jaettu liitoksessa kahden datajoukon välillä.

Alla olevalla esimerkkikyselyllä selvitetään, mikä oli käyttäjän keskimääräinen tapahtumien määrä ennen ostosta 10.9.2013.

Käyttäjän interaktioiden määrä 10.9.2013 ennen ostosta
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;

Myyty varasto-osuus tuotetta kohti

Alla on esimerkki kyselystä, joka on riippuvainen Analytics-datan lisäksi myös muusta kuin Analytics-datasta. Yhdistämällä molemmat datajoukot saat tietoa käyttäjien toiminnasta entistä paremmin jaotellulla tavalla. Voit tuoda BigQueryyn myös muuta kuin Analytics-dataa. Muista kuitenkin, että tämä vaikuttaa datan tallennuksen kuukausiveloitukseen.

Alla olevalla koodirungolla selvitetään, mikä oli myyty varasto-osuus tuotetta kohti.

Myyty varasto-osuus tuotetta kohti
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. Ensimmäinen rivi tuottaa kaksi kenttää: kaikista tuotetunnuksista koostuvan kentän sekä laskutoimituskentän, joka näyttää tuotetunnusta kohden myydyn prosenttiosuuden varastosta.
  2. Koska tämä kysely riippuu kahdesta datajoukosta, sinun on käytettävä JOIN() ... ON ‐funktiota. Tämä komento liittää kahden datajoukon rivit yhteen niiden yhteisen kentän perusteella. Tässä tapauksessa kyseiset kaksi datajoukkoa ovat [ ‘Imported_DataSet’ ] ja ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] sisältää muun kuin Analytics-datan. Tämä datajoukko sisältää varaston jäljellä olevasta määrästä kertovan mittarikentän (Imported DataSet.’stock_left_field’) ja tuotetunnuksen ulottuvuuskentän (Imported_DataSet.’productId_field’).
  4. ‘Alias_Name’ on nimi, joka on määritetty harmaan alikyselyn palauttamalle datalle. Kyseinen alikysely selvittää Analytics-datan perusteella myytyjen kohteiden tuotekohtaisen kokonaismäärän.
  5. Viimeisellä rivillä käytettävä ON-käsky näyttää kahden datajoukon välisen yhteisen kentän ja kyseisten datajoukkojen liitoskohdan.

Useissa tämän kyselyn muuttujissa datajoukon nimi on liitetty niihin etuliitteenä (esim. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Näin selkeytetään sitä, mikä kenttä valitaan ja mihin datajoukkoon kyseinen kenttä kuuluu.

Alla olevalla esimerkkikyselyllä selvitetään, mikä oli myyty varasto-osuus tuotetta kohti 28.7.2013.

Myyty varasto-osuus tuotetta kohti 28.7.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;

Kunkin tuotteen tuottavuus

Alla olevalla koodirungolla selvitetään, mikä on kunkin tuotteen tuottavuus.

Tuotekohtainen tuotto
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. Ensimmäisen rivin laskutoimituksella lasketaan kunkin tuotteen kokonaistuotto.
  2. Harmaa alikysely käyttää muuta kuin Analytics-dataa ja kerää dataa tietyn tuotteen myynnin tuotosta.
  3. Punainen alikysely on Analytics-datan alikysely, joka liitetään yhteen muun kuin Analytics-datan kanssa. Tämä alikysely laskee myytyjen kohteiden tuotekohtaisen määrän.
  4. Viimeisellä rivillä käytetään ON-käskyä, joka selvittää kahden datajoukon välisen yhteisen kentän. Tässä tapauksessa se on tuotetunnuksen numero.

Alla olevalla esimerkkikyselyllä selvitetään, mikä oli kunkin tuotteen tuottavuus 28.7.2013.

Tuotekohtainen tuottavuus 28.7.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 );

Tuotto lasketaan selvittämällä tuotteen myynti- ja valmistushinnan välinen ero. Tämä tieto tallennetaan muuhun kuin GA-datajoukkoon.

Kunkin tuotteen todellinen tuottavuus (kun hyvitykset on otettu huomioon)

Alla olevalla koodirungolla selvitetään, mikä on kunkin tuotteen todellinen tuottavuus.

Todellinen tuotekohtainen tuotto
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. Tämä kysely on erittäin samankaltainen kuin kysely, jolla selvitetään, mikä on kunkin tuotteen tuottavuus. Ainoan eron muodostavat harmaan alikyselyn sisältämä muu kuin Analytics-datajoukko ja ensimmäisen rivin laskutoimitus, jolla lasketaan todellinen tuotto.
  2. Muussa kuin Analytics-datajoukossa lasketaan myös hyvityksiin kulunut kokonaissumma (punaisen alikyselyn SELECT-käskyssä).
  3. Tämän jälkeen todellinen tuotto selvitetään ensimmäisen rivin laskutoimituksella, jossa hyvityksiin kulunut summa vähennetään kokonaistulosta.

Lisätietoja kyselystä on osiossa Kunkin tuotteen tuottavuus.

Alla olevalla esimerkkikyselyllä selvitetään, mikä oli kunkin tuotteen todellinen tuottavuus 28.7.2013.

Todellinen tuotekohtainen tuotto 28.7.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 ) );

Todellinen tuotto kertoo tietyn tuotteen tuottavuuden sen jälkeen, kun hyvitetyt tuotteet on otettu huomioon. Voit laskea tuotteen hyvitysten kokonaistulon seuraavasti:

tuotteen hyvitysten kokonaistulo = (tuotteen hinta + tuotehyvityksen toimitushinta) * hyvitettyjen tuotteiden lukumäärä.

Oliko tästä apua?

Miten sivua voisi parantaa?

Tarvitsetko lisäapua?

Kokeile näitä vaiheita:

Postaa ohjeyhteisölle Katso vastauksia yhteisön jäseniltä
Ota yhteyttä Kerro lisää, niin autamme sinua
true
learning path
Valitse haluamasi oppimispolku

Käy osoitteessa google.com/analytics/learn. Se on uusi verkkosivusto, jonka avulla voit saada Google Analytics 4:stä parhaan mahdollisen hyödyn. Sivustolta löytyy videoita, artikkeleita ja esittelyitä sekä linkit Google Analyticsin Discordiin, blogiin, YouTube-kanavalle ja GitHub-datasäilöön.

Aloita oppiminen jo tänään!

Haku
Tyhjennä haku
Sulje haku
Päävalikko
7054564875413207556
true
Ohjekeskushaku
true
true
true
true
true
69256
false
false