„BigQuery“ receptų knyga

Ši funkcija pasiekiama tik „Analytics 360“, kuri priklauso „Google Marketing Platform“.
Sužinokite daugiau apie „Google Marketing Platform“.

Šiame straipsnyje pateikiama pavyzdžių, kaip sukurti „Analytics“ duomenų, kuriuos eksportuojate į „BigQuery“, užklausas. Pateikiame duomenų rinkinio pavyzdį, kad galėtumėte išbandyti užklausas.

Šiame straipsnyje

Užklausos optimizavimas

Kiekviena jūsų vykdoma užklausa pateikia per mėnesį leistiną duomenų apdorojimo kiekį. Jei pasirenkate nesusijusius laukus, padidinate duomenų, kuriuos reikia apdoroti, kiekį ir todėl naudojate daugiau per mėnesį leistino kiekio, nei reikia. Optimizuotos užklausos jūsų per mėnesį leistino duomenų apdorojimo kiekio naudojimą padaro efektyvų.

Sužinokite daugiau apie kainodarą.

Pasirinkite tik tai, ko reikia

Suformavę užklausą pasirinkite atitinkamus laukus teiginyje SELECT. Neiškviesdami nesusijusių laukų sumažinate duomenų kiekį ir laiką, kurio reikia užklausai apdoroti.

Pavyzdys: nenaudokite pakaitos simbolio operacijos ženklo

Netinkama forma: naudojamas pakaitos simbolio operacijos ženklas
SELECT *
FROM [table name];

 

Geresnė forma: naudojami laukų pavadinimai siekiant išvengti nereikalingo apdorojimo
SELECT field1, field2
FROM [table name];

Leidimas saugoti talpykloje

Kur galima, nenaudokite funkcijų kaip laukų. Funkcijos (pvz., NOW() arba TODAY()) pateikia kintamus rezultatus, kurie neleidžia užklausų padėti į talpyklą, todėl jos grąžinamos daug greičiau. Vietoje to naudokite konkretų laiką ir dienas.

Net jei parinktis „Naudoti talpykloje saugomus rezultatus“ pažymėta, šiuo metu kelias lenteles apimančių užklausų, kuriose naudojamas pakaitos simbolis, talpykloje saugomi rezultatai nepalaikomi. Jei tą pačią užklausą su pakaitos simboliu vykdote kelis kartus, esate apmokestinami už kiekvieną užklausą. Sužinokite daugiau

Dažnai naudojamų antrinių užklausų tarpinių lentelių naudojimas

Jei aptiksite, kad pakartotinai konkrečią užklausą naudojate kaip antrinę užklausą, galite išsaugoti šią užklausą kaip tarpinę lentelę virš užklausos rezultatų spustelėję Išsaugoti kaip lentelę. Tada galite nurodyti tą lentelę užklausos skiltyje FROM ir taip sumažinti apdorojamų duomenų kiekį ir apdorojimui reikalingą laiką.

Tarpinės lentelės naudojimas
SELECT field1, field2
FROM [Dataset name.table name];

Užklausos derinimas

„BigQuery“ suderina jūsų kodą, kai jį kuriate. Kūrimo lange derinimas nurodomas tiesiai po užklausa. Derinimas taip pat galimas per API naudojant „dryRun“ žymėjimą.

Galiojančios užklausos turi žalią indikatorių, kurį galite spustelėti ir peržiūrėti užklausos apdorojamų duomenų kiekį. Ši funkcija suteikia galimybę optimizuoti jūsų duomenis prieš vykdant užklausą, kad išvengtumėte nereikalingo duomenų apdorojimo.

Query Debugging - Success

 

Klaidingos užklausos turi raudoną indikatorių, kurį galite spustelėti ir peržiūrėti informaciją apie klaidą, sužinoti, kurioje eilutėje ir stulpelyje įvyksta klaida. Toliau pateiktame pavyzdyje teiginys GROUP BY yra tuščias, o klaida tiksliai nustatyta.

Query Debugging - Error

 

Patarimai ir geriausios praktikos pavyzdžiai

Pavyzdinio duomenų rinkinio naudojimas

Toliau nurodytuose pavyzdžiuose naudojamas pavyzdinis „Google Analytics“ duomenų rinkinys.

Norėdami taikyti užklausas savo duomenims, tiesiog pakeiskite pavyzdžiuose nurodytus projekto ir duomenų rinkinio pavadinimus savo projekto bei duomenų rinkinio pavadinimais.

Standartinio ir pasenusio SQL naudojimas

„BigQuery“ palaiko du SQL dialektus

Straipsnyje Perkėlimas į standartinį SQL paaiškinami abiejų dialektų skirtumai.

Dabar standartinis SQL yra rekomenduojamas „BigQuery“ saugomų duomenų užklausų SQL dialektas.

Informaciją, kaip įgalinti standartinį SQL „BigQuery“ naudotojo sąsajoje, CLI, API ar kitoje naudojamoje sąsajoje, rasite straipsnyje „Standartinio SQL įgalinimas“.

Lengviausias būdas pradėti – įtraukti komentarą „standardSQL“ standartinio SQL užklausų viršuje, kaip parodyta toliau pateiktuose pavyzdžiuose.

Jei naudojate pasenusį SQL, „Google Analytics 360“ duomenys kasdien perduodami į naują lentelę. Jei norite pateikti užklausą kelioms lentelėms vienu metu, galite kableliais atskirti lentelių pavadinimus, naudoti TABLE_DATE_RANGE lentelės pakaitos simbolių funkciją arba keliais kableliais atskirtas TABLE_DATE_RANGE funkcijas, kaip parodyta toliau pateiktuose pavyzdžiuose.

Užklausos pateikimas kelioms lentelėms

Toliau pateiktuose pavyzdžiuose parodytos tų pačių duomenų standartinio ir pasenusio SQL užklausos.

Trijų dienų

Standartinis SQL
Trijų dienų naudojant 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
Pasenęs SQL
Trijų dienų naudojant kableliais atskirtus lentelių pavadinimus
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

 

Pastarųjų 1 095 dienų

Standartinis SQL
Pastarųjų 1 095 dienų naudojant _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
Pasenęs SQL
Pastarųjų 1 095 dienų naudojant 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

 

Pastarųjų 36 mėnesių

Standartinis SQL
Pastarųjų 36 mėnesių naudojant _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
Pasenęs SQL
Pastarųjų 36 mėnesių naudojant 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

 

Pastarųjų trejų metų

Standartinis SQL
Pastarųjų trejų metų naudojant _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
Pasenęs SQL
Pastarųjų trejų metų naudojant 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

 

Konkrečios dienų sekos

Standartinis SQL
Konkrečios dienų sekos naudojant _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
Pasenęs SQL
Konkrečios dienų sekos naudojant 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

Pastarųjų trejų metų, įskaitant šiandienos duomenis (dienos)

Standartinis SQL
Pastarųjų trejų metų, įskaitant šiandienos duomenis (dienos), naudojant UNION ALL ir _TABLE_SUFFIX
Pastaba: ši pavyzdinė užklausa neveiks su „Google Analytics“ viešuoju duomenų rinkiniu, nes šiuo metu nėra dienos duomenų lentelės.
#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

Pasenęs SQL

Pastarųjų trejų metų, įskaitant šiandienos duomenis (dienos), naudojant kelias TABLE_DATE_RANGE
Pastaba: ši pavyzdinė užklausa neveiks su „Google Analytics“ viešuoju duomenų rinkiniu, nes šiuo metu nėra dienos duomenų lentelės.
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

 

 

Pagrindinių užklausų pavyzdžiai

Šioje skiltyje paaiškinama, kaip sukurti pagrindines užklausas naudojant metriką ir aspektus iš pavyzdinių „Analytics“ duomenų.

Bendra vieno [aspekto] [metrika]?

Toliau pateikti pavyzdiniai scenarijai pagal klausimą: Koks bendras įrenginio naršyklės sugeneruotas operacijų skaičius 2017 m. liepos mėn.?

Standartinis SQL

Bendras įrenginio naršyklės sugeneruotas operacijų skaičius 2017 m. liepos mėn.
#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

Pasenęs SQL

Bendras įrenginio naršyklės sugeneruotas operacijų skaičius 2017 m. liepos mėn.
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

 

 

Vidutinis vieno [aspekto] atmetimo rodiklis

Tikrasis atmetimo rodiklis apibrėžtas kaip apsilankymų su viena puslapio peržiūra procentas. Toliau pateikti pavyzdiniai scenarijai pagal klausimą: Koks tikras duomenų srauto šaltinio atmetimo rodiklis?

Standartinis SQL

Duomenų srauto šaltinio atmetimo rodiklis 2017 m. liepos mėn.
#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

Pasenęs SQL

Duomenų srauto šaltinio atmetimo rodiklis 2017 m. liepos mėn.
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

 

 

Vidutinis produktų puslapių peržiūrų skaičius pagal pirkėjo tipą (pirkėjai ir ne pirkėjai)

Toliau pateikti pavyzdiniai scenarijai pagal klausimą: Koks vidutinis naudotojų, kurie ką nors įsigijo, produktų puslapių peržiūrų skaičius 2017 m. liepos mėn.?

Standartinis SQL

Vidutinis naudotojų, kurie ką nors įsigijo, produktų puslapių peržiūrų skaičius 2017 m. liepos mėn.
#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 )

Pasenęs SQL

Vidutinis naudotojų, kurie ką nors įsigijo, produktų puslapių peržiūrų skaičius 2017 m. liepos mėn.
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 )

 

 

Toliau pateikti pavyzdiniai scenarijai pagal klausimą: Koks vidutinis nepirkusių naudotojų produktų puslapių peržiūrų skaičius 2017 m. liepos mėn.?

Standartinis SQL

Vidutinis nepirkusių naudotojų produktų puslapių peržiūrų skaičius 2017 m. liepos mėn.
#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 )

Pasenęs SQL

Vidutinis nepirkusių naudotojų produktų puslapių peržiūrų skaičius 2017 m. liepos mėn.
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 )

 

 

Vidutinis vieno pirkėjo operacijų skaičius

Toliau pateikti pavyzdiniai scenarijai pagal klausimą: Koks vidutinis bendras vieno naudotojo, kuris ką nors įsigijo, operacijų skaičius 2017 m. liepos mėn.?

Standartinis SQL

Vidutinis bendras vieno naudotojo, kuris ką nors įsigijo, operacijų skaičius 2017 m. liepos mėn.
#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 )

Pasenęs SQL

Vidutinis bendras vieno naudotojo, kuris ką nors įsigijo, operacijų skaičius 2017 m. liepos mėn.
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 )

 

 

Vidutinė per seansą išleistų pinigų suma

Toliau pateikti pavyzdiniai scenarijai pagal klausimą: Kokia vidutinė per seansą išleistų pinigų suma 2017 m. liepos mėn.?

Standartinis SQL

Vidutinė per seansą išleistų pinigų suma 2017 m. liepos mėn.
#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 )

Pasenęs SQL

Vidutinė per seansą išleistų pinigų suma 2017 m. liepos mėn.
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 )

 

 

Įvykių seka

Toliau pateikti pavyzdiniai scenarijai pagal klausimą: Kokia seka peržiūrimi puslapiai?

Standartinis SQL

2017 m. liepos mėn. naudotojų peržiūrėtų puslapių seka
#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

Pasenęs SQL

2017 m. liepos mėn. naudotojų peržiūrėtų puslapių seka
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

Šioje užklausoje apribojate įvykių tipus iki PAGES, kad nebūtų rodomas operacijų sąveikų įvykis. Kiekviena išvesties eilutė atspindi puslapio peržiūrą ir rodoma numatyta laukų tvarka teiginyje SELECT.

 

 

Keli tinkinti aspektai įvykio arba seanso lygiu

Tinkintas aspektas įvykio lygiu
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

 

Tinkintas aspektas seanso lygiu
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

Kiekvienoje užklausoje

Atitinkamų aspekto ir metrikos laukų teiginio SELECT užklausos.

MAX funkcija

  • Pateikia tinkintą aspektą kaip naują stulpelį. Galite pakartoti funkciją ir pateikti kelis tinkintus aspektus kaip naujus stulpelius.
  • WITHIN įvykiai ir WITHIN RECORD įvertina sąlygą pasikartojančiuose laukuose „BigQuery“.
  • Įvertinama kiekvieno tinkinto aspekto sąlyga, esanti MAX, tačiau tiems, kurie nėra index=1 (hits) arba index=2 (sessions), ji pateikia NULL.
  • Pateikiama maksimali vertė, kuri yra 1 tinkinto aspekto vertė, skirta įvykiams, arba 2 tinkinto aspekto vertė, skirta seansams, o visos kitos vertės yra NULL.

Išplėstiniai užklausų pavyzdžiai

Dabar, kai susipažinote su paprastomis užklausomis, galite sukurti užklausas, naudodami išplėstines funkcijas ir funkcijas, pasiekiamas „BigQuery“.

Klientų, kurie įsigijo produktą A, pirkti produktai (klasikinė el. prekyba)

Toliau pateikti klausimo: kokius kitus produktus įsigijo klientai, nusipirkę produktą A? scenarijaus apmatai.

Produktai, kuriuos įsigijo klientas, nusipirkęs produktą A (klasikinė el. prekyba)
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. Pirmoje eilutėje pasirinkite visus kitus naudotojo įsigytus produktus ir kaupimo funkciją COUNT(), naudojamą kiekvieno kito įsigyto produkto kiekiui apskaičiuoti. Tada rezultatas bus rodomas lauke, pažymėtame quantity su susieta preke produkto lauke, pažymėta kaip other_purchased_products.
  2. Pilkos spalvos antrinėje užklausoje pasirenkate tik unikalius naudotojus (fullVisitorId), atlikusius operacijas (totals.transactions>=1) ir operacijų metu įsigijusius produktą A (WHERE hits.item.productName CONTAINS „Produkto pavadinimas A“).

Taisyklės (teiginiai WHERE ir AND) aukščiausio lygio (žalioje) užklausoje nepaiso verčių, esančių hits.item.productNamem, kurios lygios nuliui ir kuriose yra produktas A.

Toliau pateikiamas užklausos: jei klientas įsigijo „Brighton“ metalinius rašiklius (4 rinkinyje), kokius kitus produktus jis įsigijo? pavyzdys.

Produktai, kuriuos įsigijo klientas, įsigijęs „Brighton“ metalinius rašiklius (4 rinkinyje)“ 2013 m. birželio 24 d.
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“ naudojamas WHERE expr IN suaktyvina JOIN ir taikomi dydžio apribojimai; tai yra, JOIN dešinės pusės dydis (lankytojų skaičiaus atveju) turi būti mažesnis nei 8 MB. „Dremel“ tai vadinama transliacijos JOIN. Kai dydis viršija 8 MB, turite suaktyvinti maišymo JOIN, kurį galima atlikti naudojant JOIN EACH sintaksę. Deja, to negalima atlikti naudojant IN, bet tą pačią užklausą galima perrašyti naudojant JOIN.

Klientų, kurie įsigijo produktą A, pirkti produktai (patobulinta el. prekyba)

Tai panašu į ankstesnės užklausos apmatus, tačiau naudojama patobulintai el. prekybai. Jai taip pat naudojama TABLE_DATE_RANGE, norint pateikti kelių dienų duomenų užklausą.

Produktai, kuriuos įsigijo klientas, nusipirkęs produktą A (patobulinta el. prekyba)
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;

Vidutinis naudotojų sąveikų prieš pirkimą skaičius

Tai komandos JOIN() [...] ON, kuri priklauso tik nuo „Analytics“ duomenų, užklausos pavyzdys.

Toliau pateikiami klausimo: Koks vidutinis naudotojo sąveikų iki pirkimo skaičius? scenarijaus apmatai.

Naudotojo sąveikų iki pirkimo skaičius
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. Pirmoje eilutėje atliekama pagrindinė matematinė operacija, skirta rasti vidutinį naudotojo sąveikų su produktu skaičių ir ši užklausa sukuria jungtį tarp dviejų antrinių užklausų, vadinamų Alias_Name_1 ir Alias_Name_2.
  2. „Alias_Name_1“ yra naudojamas kurti laukui, kuriame naudojama kaupimo funkcija SUM(), skirta sumuoti visus produktui įrašytus įvykius.
  3. „Alias_Name_2“ naudojamas įvykių, kuriuos naudotojas atliko su produktu, skaičiui gauti, naudojant funkciją COUNT().
  4. Paskutinėje eilutėje rodomas bendras laukas (hits.item.productSku), bendrinamas tarp dviejų junginio duomenų rinkinių.

Toliau pateikiamas užklausos: koks vidutinis naudotojo sąveikų iki pirkimo skaičius 2013 m. rugsėjo 10 d.? pavyzdys.

Vidutinis naudotojo sąveikų iki pirkimo skaičius 2013 m. rugsėjo 10 d.
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;

Vieno produkto parduotų atsargų procentas

Tai yra užklausos, kuri priklausoma ne tik nuo „Analytics“ duomenų, bet ir nuo ne „Analytics“ duomenų. Suderinę abu duomenų rinkinius galite pradėti suprasti naudotojo elgseną labiau segmentuotu lygiu. Galite importuoti ne „Analytics“ duomenis į „BigQuery“, bet turite atminti, kad tai turės įtakos jūsų mėnesiniam duomenų saugyklos mokesčiui.

Toliau pateikti klausimo: koks produkto atsargų procentas parduotas? scenarijaus apmatai.

Vieno produkto parduotų atsargų procentas
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. Pirmoje eilutėje pateikti du laukai: viename lauke yra visi produkto ID, o kitame yra matematinė operacija, nurodanti šio produkto ID parduotų atsargų procentą.
  2. Ši užklausa priklauso nuo dviejų duomenų rinkinių, todėl turite naudoti funkciją JOIN() ... ON. Ši komanda sujungia eilutes iš dviejų duomenų rinkinių, atsižvelgiant į bendrą lauką tarp jų. Šiuo atveju du duomenų rinkiniai yra [ ‘Imported_DataSet’ ] ir ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] yra ne „Analytics“ duomenys. Tai duomenų rinkinys, apimantis metrikos lauką, nurodantį, kiek atsargų liko (Imported DataSet.’stock_left_field’), ir produkto ID aspekto lauką (Imported_DataSet.’productId_field’).
  4. Alias_Name – tai pavadinimas, priskirtas pilkos spalvos antrinės užklausos pateiktiems duomenims. Šioje antrinėje užklausoje naudojant „Analytics“ duomenis galima išsiaiškinti bendrą kiekvieno produkto parduotų elementų kiekį.
  5. Paskutinėje eilutėje naudojamas teiginys ON norint parodyti bendrą lauką tarp dviejų duomenų rinkinių ir kur du duomenų rinkiniai susijungia.

Daugelio užklausos kintamųjų duomenų rinkinių pavadinimų pridėta prie jų kaip priešdėliai (pvz., Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Tai paaiškina, kokį lauką pasirenkate ir kuriam duomenų rinkiniui jis priklauso.

Toliau pateikiamas užklausos: koks vieno produkto atsargų kiekis parduotas 2013 m. liepos 28 d.? pavyzdys.

Vieno produkto 2013 m. liepos 28 d. parduotų atsargų procentas
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;

Kiekvieno produkto pelningumas

Toliau pateikti klausimo: koks kiekvieno produkto pelningumas? scenarijaus apmatai.

Pelningumas pagal produktą
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. Pirmojoje eilutėje yra matematinė operacija, skirta bendram kiekvieno produkto pelningumui apskaičiuoti.
  2. Pilkos spalvos antrinėje užklausoje naudojami ne „Analytics“ duomenys, kurie renka duomenis apie tai, kiek pelno sukuriama pardavus produktą.
  3. Raudona antrinė užklausa yra „Analytics“ duomenų antrinė užklausa, kuri bus sujungta su ne „Analytics“ duomenimis. Ji apskaičiuoja kiekvieno produkto parduotų prekių kiekį.
  4. Paskutinėje eilutėje naudojamas teiginys ON, paaiškinantis lauką, kurį bendrina du duomenų rinkiniai. Šiuo atveju tai produkto ID.

Toliau pateikiamas užklausos: koks kiekvieno produkto pelningumas 2013 m. liepos 28 d.? pavyzdys.

Produkto pelningumas 2013 m. liepos 28 d.
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 );

Pelningumas apskaičiuojamas gavus parduoto produkto kainos ir jo pagaminimo kainos skirtumą. Ši informacija saugoma ne GA duomenų rinkinyje.

Tikras kiekvieno produkto pelningumas (atsižvelgiama į paskyros grąžinimą)

Toliau pateikti klausimo: koks tikras kiekvieno produkto pelningumas? scenarijaus apmatai.

Tikras produkto pelnas
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. Tai labai panaši užklausa į Koks kiekvieno produkto pelningumas? Vienintelis skirtumas yra ne „Analytics“ duomenų rinkinys pilkos spalvos antrinėje užklausoje ir matematinėje operacijoje apskaičiuojamas tikras pelnas pirmoje eilutėje.
  2. Ne „Analytics“ duomenų rinkinyje taip pat apskaičiuojama bendra grąžinimams išleista pinigų suma (raudonos antrinės užklausos teiginyje SELECT).
  3. Tada atlikite matematinę operaciją pirmoje eilutėje, kad sužinotumėte tikrą pelną, iš bendro pelno atimdami grąžinimams išleistą pinigų dalį.

Jei reikia informacijos apie užklausą, peržiūrėkite skiltį apie kiekvieno produkto pelningumą.

Toliau pateikiamas užklausos: koks kiekvieno produkto tikras pelningumas 2013 m. liepos 28 d.? pavyzdys.

Tikras pelnas pagal produktą 2013 m. liepos 28 d.
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 ) );

Nustatant tikrą pelną atsižvelgiama į produkto pelningumą kartu atsižvelgiant į grąžinamus produktus. Bendros produkto grąžinamos pajamos apskaičiuojamos:

bendros produkto grąžinamos pajamos = (produkto kaina + produkto pristatymo kainos grąžinimas) * grąžintų produktų kiekis

Ar tai buvo naudinga?

Kaip galime jį patobulinti?

Reikia daugiau pagalbos?

Išbandykite kitus toliau nurodytus veiksmus.

Paskelbti klausimą pagalbos bendruomenei Gaukite bendruomenės narių atsakymus
Susisiekite su mumis Papasakokite daugiau ir mes jums padėsime
true
learning path
Mokomojo turinio rinkinio pasirinkimas

Peržiūrėkite google.com/analytics/learn, naują šaltinį, kuris padės išnaudoti visas „Google Analytics 4“ galimybes. Naujoje svetainėje yra vaizdo įrašų, straipsnių ir apžvalgų su nurodymais, taip pat pateikiamos nuorodos į „Google Analytics“ „Discord“, tinklaraštį, „YouTube“ kanalą ir „GitHub“ saugyklą.

Pradėkite mokytis šiandien!

Paieška
Išvalyti paiešką
Uždaryti paiešką
Pagrindinis meniu
8480485589423087565
true
Paieška pagalbos centre
true
true
true
true
true
69256
false
false
false