BigQuery lietojuma piemēri

Šī funkcija ir pieejama tikai pakalpojumā Analytics 360, kas ir daļa no produktu komplekta Google Marketing Platform.
Uzzināt vairāk par Google Marketing Platform.

Šajā rakstā ir ietverti piemēri par to, kā veidot to Analytics datu vaicājumus, kas eksportēti uz rīku BigQuery. Varat izmantot datu kopas paraugu, lai izmēģinātu dažus šajā rakstā minētos vaicājumus.

Šajā rakstā ietvertās tēmas

Vaicājumu optimizācija

Uz katru jūsu izpildīto vaicājumu attiecas ikmēneša datu apstrādes kvota. Ja atlasīsiet liekus laukus, palielināsies apstrādājamo datu apjoms, un tādējādi jūs izmantosiet vairāk ikmēneša datu apjoma, nekā nepieciešams. Optimizēti vaicājumi ļauj efektīvāk izmantot katrā mēnesī pieejamo datu apstrādes apjomu.

Uzziniet vairāk par cenām.

Tikai nepieciešamo datu atlase

Kad formulējat vaicājumu, atlasiet attiecīgos laukus, izmantojot priekšrakstu SELECT. Neveidojot lieku lauku izsaukumus, jūs samazināt datu apjomu un vaicājuma apstrādei nepieciešamo laiku.

Piemērs: neizmantojiet aizstājējzīmes operatoru

Nav ieteicams: aizstājējzīmes operatora izmantošana
SELECT *
FROM [tabulas nosaukums];

 

Ieteicams: izmantojiet lauku nosaukumus, lai novērstu nevajadzīgu apstrādi
SELECT field1, field2
FROM [tabulas nosaukums];

Atļauja saglabāt kešatmiņā

Ja tas ir iespējams, neizmantojiet funkcijas kā laukus. Funkcijas (piemēram, NOW() vai TODAY()) parāda mainīgus rezultātus, tādējādi vaicājumus nevar saglabāt kešatmiņā un parādīt ātrāk. Tā vietā izmantojiet konkrētus laikus un datumus.

Kešatmiņā saglabātie rezultāti pašlaik netiek atbalstīti vaicājumiem, kuros, izmantojot aizstājējzīmi, tiek pieprasīti dati no vairākām tabulām. Minētais ir spēkā pat tad, ja ir atzīmēta opcija “Izmantot kešatmiņā saglabātos rezultātus”. Ja vairākas reizes aktivizēsiet vienu un to pašu vaicājumu ar aizstājējzīmi, iespējams, no jums tiks iekasēta maksa par katru vaicājumu. Uzziniet vairāk.

Starpniektabulu izmantošana bieži lietotiem apakšvaicājumiem

Ja konstatējat, ka vairākkārt izmantojat konkrētu vaicājumu kā apakšvaicājumu, varat to saglabāt kā starpniektabulu, noklikšķinot uz Saglabāt kā tabulu virs vaicājuma rezultātiem. Pēc tam varat atsaukties uz šo tabulu vaicājuma sadaļā FROM, tādējādi samazinot gan apstrādājamo datu apjomu, gan apstrādei nepieciešamo laiku.

Starpniektabulas izmantošana
SELECT field1, field2
FROM [datu kopas nosaukums.tabulas nosaukums];

Vaicājuma atkļūdošana

BigQuery atkļūdo jūsu kodu jau tā izveides laikā. Izveides logā atkļūdošana ir norādīta tieši zem vaicājuma. Atkļūdošana ir pieejama arī saskarnē API, izmantojot karodziņu dryRun.

Derīgus vaicājumus apzīmē zaļš indikators, uz kura varat noklikšķināt, lai skatītu vaicājuma apstrādāto datu apjomu. Šī funkcija ļauj optimizēt datus pirms vaicājuma izpildes, lai varētu izvairīties no liekas datu apstrādes.

Query Debugging - Success

 

Nederīgus vaicājumus apzīmē sarkans indikators, uz kura varat noklikšķināt, lai skatītu informāciju par kļūdu un atrastu rindiņu un sleju, kurā rodas kļūda. Tālāk minētajā piemērā priekšraksts GROUP BY ir tukšs, un ir konstatēta kļūda.

Query Debugging - Error

 

Padomi un paraugprakse

Datu kopas parauga izmantošana

Tālāk aprakstītajos piemēros tiek izmantots Google Analytics datu kopas paraugs.

Lai izmantotu vaicājumus saviem datiem, vienkārši aizstājiet paraugos norādītos projekta un datu kopas nosaukumus ar sava projekta un datu kopas nosaukumiem.

Standarta SQL un mantotais SQL — salīdzinājums

BigQuery atbalsta divus tālāk minētos SQL dialektus.

Rakstā Migrating to Standard SQL (Migrēšana uz standarta SQL) ir aprakstītas abu dialektu atšķirības.

Standarta SQL pašlaik ir ieteicamais SQL dialekts rīkā BigQuery glabāto datu vaicājumu izveidei.

Rakstā Enabling Standard SQL (Standarta SQL iespējošana) skatiet informāciju par standarta SQL iespējošanu BigQuery lietotāja saskarnē, komandrindas saskarnē, saskarnē API vai jebkurā citā saskarnē, ko izmantojat.

Vienkāršākais veids, kā sākt darbu, ir iekļaut komentāru “standardSQL” savu standarta SQL vaicājumu augšdaļā, kā parādīts tālāk sniegtajos piemēros.

Ja izmantojat mantoto SQL, Google Analytics 360 dati katru dienu tiek ievietoti jaunā tabulā. Lai izveidotu vaicājumus vairākām tabulām vienlaikus, varat ar komatiem atdalīt tabulu nosaukumus, izmantot tabulas aizstājējzīmes funkciju TABLE_DATE_RANGE vai vairākas komatatdalītas funkcijas TABLE_DATE_RANGE, kā parādīts tālāk sniegtajos piemēros.

Vaicājumu veidošana vairākām tabulām

Tālāk sniegtajos piemēros ir parādīti standarta SQL un mantotā SQL vaicājumi vieniem un tiem pašiem datiem.

Trīs dienas

Standarta SQL
Trīs dienas, izmantojot 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
Mantotais SQL
Trīs dienas, izmantojot ar komatiem atdalītus tabulu nosaukumus
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

 

Pēdējās 1095 dienas

Standarta SQL
Pēdējās 1095 dienas, izmantojot funkciju _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
Mantotais SQL
Pēdējās 1095 dienas, izmantojot funkciju 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

 

Pēdējie 36 mēneši

Standarta SQL
Pēdējie 36 mēneši, izmantojot funkciju _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
Mantotais SQL
Pēdējie 36 mēneši, izmantojot funkciju 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

 

Pēdējie 3 gadi

Standarta SQL
Pēdējie 3 gadi, izmantojot funkciju _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
Mantotais SQL
Pēdējie 3 gadi, izmantojot funkciju 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

 

Konkrēts datumu diapazons

Standarta SQL
Konkrēts datumu diapazons, izmantojot funkciju _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
Mantotais SQL
Konkrēts datumu diapazons, izmantojot funkciju 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

Pēdējie trīs gadi un šodiena (diennakts dati)

Standarta SQL
Pēdējie trīs gadi un šodiena (diennakts dati), izmantojot operatoru UNION ALL un funkciju _TABLE_SUFFIX
Piezīme. Šis vaicājuma piemērs nedarbosies ar Google Analytics publiski pieejamo datu kopu, jo tajā pašlaik nav iekļauta diennakts datu tabula.
#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

Mantotais SQL

Pēdējie trīs gadi un šodiena (diennakts dati), vairākkārt izmantojot funkciju TABLE_DATE_RANGE
Piezīme. Šis vaicājuma piemērs nedarbosies ar Google Analytics publiski pieejamo datu kopu, jo tajā pašlaik nav iekļauta diennakts datu tabula.
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

 

 

Pamata vaicājumu piemēri

Šajā sadaļā ir paskaidrots, kā veidot pamata vaicājumus, izmantojot parauga Analytics datos pieejamo metriku un kategorijas.

Kopējā [metrikas] vērtība katrā [kategorijā]

Tālāk ir sniegti skripta paraugi jautājumam: “Kāds ir kopējais darījumu skaits katrā ierīces pārlūkprogrammā 2017. gada jūlijā?”

Standarta SQL

Kopējais darījumu skaits katrā ierīces pārlūkprogrammā 2017. gada jūlijā
#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

Mantotais SQL

Kopējais darījumu skaits katrā ierīces pārlūkprogrammā 2017. gada jūlijā
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

 

 

Vidējais atlēcienu līmenis katrā [kategorijā]

Faktiskais atlēcienu līmenis ir to apmeklējumu procentuālā daļa, kuros tika reģistrēts viens lapas apmeklējums. Tālāk ir sniegti skripta piemēri jautājumam: “Kāds bija faktiskais atlēcienu līmenis katrā datplūsmas avotā?”

Standarta SQL

Atlēcienu līmenis katrā datplūsmas avotā 2017. gada jūlijā
#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

Mantotais SQL

Atlēcienu līmenis katrā datplūsmas avotā 2017. gada jūlijā
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

 

 

Vidējais produkta lapas skatījumu skaits pēc pircēja veida (pircēji salīdzinājumā ar nepircējiem)

Tālāk ir sniegti skripta paraugi jautājumam: “Kāds bija vidējais produkta lapas apmeklējumu skaits no lietotājiem, kuri veica pirkumu 2017. gada jūlijā?”

Standarta SQL

Vidējais produkta lapas skatījumu skaits no lietotājiem, kuri veica pirkumu 2017. gada jūlijā
#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 )

Mantotais SQL

Vidējais produkta lapas skatījumu skaits no lietotājiem, kuri veica pirkumu 2017. gada jūlijā
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 )

 

 

Tālāk ir sniegti skripta piemēri jautājumam: “Kāds bija vidējais produkta lapas skatījumu skaits no lietotājiem, kuri neveica pirkumu 2017. gada jūlijā?”

Standarta SQL

Vidējais produkta lapas skatījumu skaits no lietotājiem, kuri neveica pirkumu 2017. gada jūlijā
#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 )

Mantotais SQL

Vidējais produkta lapas skatījumu skaits no lietotājiem, kuri neveica pirkumu 2017. gada jūlijā
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 )

 

 

Vidējais darījumu skaits katram pircējam

Tālāk ir sniegti skripta paraugi jautājumam: “Kāds bija vidējais kopējais darījumu skaits katram lietotājam, kurš veica pirkumu 2017. gada jūlijā?”

Standarta SQL

Vidējais darījumu skaits katram lietotājam, kurš veica pirkumu 2017. gada jūlijā
#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 )

Mantotais SQL

Vidējais darījumu skaits katram lietotājam, kurš veica pirkumu 2017. gada jūlijā
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 )

 

 

Vidējā iztērētā summa katrā sesijā

Tālāk ir sniegti skripta paraugi jautājumam: “Kāda bija vidējā iztērētā summa katrā sesijā 2017. gada jūlijā?”

Standarta SQL

Vidējā iztērētā summa katrā sesijā 2017. gada jūlijā
#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 )

Mantotais SQL

Vidējā iztērētā summa katrā sesijā 2017. gada jūlijā
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 )

 

 

Trāpījumu secība

Tālāk ir sniegti skripta paraugi jautājumam: “Kādā secībā lapas tika apmeklētas?”

Standarta SQL

2017. gada jūlijā lietotāju skatīto lapu secība
#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

Mantotais SQL

2017. gada jūlijā lietotāju skatīto lapu secība
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

Šajā vaicājumā tiek izgūti tikai trāpījumi ar veidu PAGES, lai netiktu rādīti notikumu vai darījumu mijiedarbības gadījumi. Katra iegūtā rindiņa ataino lapas skatījumu, un tās tiek rādītas noklusējuma lauku secībā, kas norādīta operatorā SELECT.

 

 

Vairākas pielāgotās kategorijas trāpījuma vai sesijas līmenī

Pielāgotās kategorijas trāpījuma līmenī
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

 

Pielāgotās kategorijas sesijas līmenī
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

Katrā vaicājumā:

Operators SELECT veic attiecīgo kategoriju un metrikas lauku vaicājumus.

Funkcija MAX

  • Pielāgotā kategorija tiek parādīta kā jauna sleja. Varat atkārtoti izpildīt funkciju, lai rādītu vairākas pielāgotās kategorijas kā jaunas slejas.
  • Priekšraksti WITHIN hits un WITHIN RECORD izvērtē nosacījumu atkārtotos laukos rīkā BigQuery.
  • Funkcijā MAX ietvertais nosacījums tiek izvērtēts katrai pielāgotajai kategorijai, taču, ja kategorijas vērtība nav index=1 (hits) vai index=2 (sessions), tiek atgriezta vērtība NULL.
  • Tiek atgriezta maksimālā vērtība, kas ir 1. pielāgotās kategorijas trāpījumu vērtība vai 2. pielāgotās kategorijas sesiju vērtība, jo visas pārējās vērtības ir NULL.

Sarežģītu vaicājumu piemēri

Tagad, kad esat iepazinies ar vienkāršiem vaicājumiem, varat veidot vaicājumus, izmantojot rīkā BigQuery pieejamās papildu funkcijas un iespējas.

Produkti, ko iegādājās klienti, kuri nopirka produktu A (klasiskā e-komercija)

Tālāk ir sniegts skripta paraugs jautājumam “Kādus citus produktus iegādājās klienti, kuri nopirka produktu A?”.

Produkti, ko iegādājās klients, kurš nopirka produktu A (klasiskā e-komercija)
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. Pirmajā rindiņā tiek atlasītas visas citas preces, ko iegādājies lietotājs. Pēc tam tiek izmantota apkopojuma funkcija COUNT(), lai aprēķinātu katras citas iegādātās preces skaitu. Rezultāts tiek parādīts laukā quantity, savukārt saistītā prece — produktu laukā other_purchased_products.
  2. Pelēkajā apakšvaicājumā tiek atlasīti tikai unikālie lietotāji (fullVisitorId), kas ir veikuši darījumus (totals.transactions>=1) un iegādājušies produktu A (WHERE hits.item.productName CONTAINS Produkta nosaukums A).

Kārtulas (operatori WHERE un AND) augšējā līmeņa (zaļajā) vaicājumā ignorē lauk hits.item.productNamem vērtības — tās ir “nulle” un ietver produktu A.

Tālāk ir sniegts piemērs vaicājumam “Ja klients iegādājās pildspalvas (4 pildspalvu komplekts), kādus citus produktus viņš nopirka?”.

Produkti, ko iegādājās klients, kurš iegādājās pildspalvas “Brighton Metallic Pens” (4 pildspalvu komplektu) 2013. gada 24. jūnijā
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;

Rīkā Dremel/BigQuery, izmantojot funkciju WHERE expr IN, tiek aktivizēta komanda JOIN un tiek piemēroti lieluma ierobežojumi. Proti, komandas JOIN labās puses (šajā gadījumā apmeklētāju skaita) lielumam ir jābūt mazākam par 8 MB. Rīkā Dremel tā tiek dēvēta par broadcast JOIN. Kad lielums pārsniedz 8 MB, jums ir jāaktivizē komanda shuffled JOIN, ko var izdarīt, izmantojot sintaksi JOIN EACH. Diemžēl to nevar paveikt, izmantojot funkciju IN, taču to pašu vaicājumu var pārrakstīt, izmantojot komandu JOIN.

Produkti, ko iegādājās klienti, kuri nopirka produktu A (uzlabotā e-komercija)

Šis ir līdzīgs iepriekšējam vaicājuma paraugam, taču to var izmantot uzlabotajai e-komercijai. Tiek izmantota arī funkcija TABLE_DATE_RANGE, lai veidotu datu vaicājumus par vairākām dienām.

Produkti, ko iegādājās klients, kurš nopirka produktu A (uzlabotā e-komercija)
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;

Vidējais lietotāja mijiedarbības gadījumu skaits pirms iegādes

Šis ir komandas JOIN() [...] ON vaicājuma piemērs, kas ir atkarīgs tikai no Analytics datiem.

Tālāk ir sniegts skripta piemērs jautājumam: “Kāds ir vidējais lietotāja mijiedarbības gadījumu skaits pirms iegādes?”

Lietotāja mijiedarbības gadījumu skaits pirms iegādes
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. Pirmajā rindiņā tiek veikta galvenā matemātiskā darbība, lai atrastu vidējo lietotāja mijiedarbības gadījumu skaitu katram produktam, un šis vaicājums saista divus apakšvaicājumus — Alias_Name_1 un Alias_Name_2.
  2. Alias_Name_1 tiek izmantots, lai izveidotu lauku, kurā visu produktam reģistrēto trāpījumu saskaitīšanai tiek izmantota apkopojuma funkcija SUM().
  3. Alias_Name_2 tiek izmantots, lai funkcija COUNT() atrastu trāpījumu skaitu, kas reģistrēts lietotājiem par katru produktu.
  4. Pēdējā rindiņā ir parādīts kopīgais lauks (hits.item.productSku), kas tiek izmantots abās datu kopās.

Tālāk ir sniegts piemērs vaicājumam “Kāds ir vidējais lietotāja mijiedarbību skaits pirms iegādes 2013. gada 10. septembrī?”.

Lietotāja mijiedarbības gadījumu skaits pirms iegādes 2013. gada 10. septembrī
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;

Katra produkta pārdoto krājumu procentuālā daļa

Šis ir vaicājuma piemērs, kurā tiek izmantoti ne tikai Analytics dati, bet arī citur iegūti dati. Apvienojot abas datu kopas, varat iegūt detalizētāku informāciju par lietotāju rīcību. Rīkā BigQuery varat izmantot citu pakalpojumu datus, taču ņemiet vērā, ka tādējādi pieaugs ikmēneša maksa par datu uzglabāšanu.

Tālāk ir sniegts skripta paraugs jautājumam: “Kāds ir produkta pārdoto krājumu procentuālais daudzums?”

Katra produkta pārdoto krājumu procentuālā daļa
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. Pirmajā rindiņā tiek izveidoti divi lauki: vienā laukā ir ietverti visi produktu ID, savukārt otrā laukā ir matemātiska darbība, kas parāda šim produkta ID atbilstošā pārdotā krājuma procentuālo daļu.
  2. Tā kā šis vaicājums ir atkarīgs no divām datu kopām, ir jāizmanto funkcija JOIN() ... ON. Šī komanda apvieno rindas no divām datu kopām, pamatojoties uz to kopīgo lauku. Šajā gadījumā šīs divas datu kopas ir [ ‘Imported_DataSet’ ] un ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] ir dati, kas nav iegūti pakalpojumā Analytics. Šī ir datu kopa, kurā ietverts metrikas lauks ar atlikušo krājuma daudzumu (Imported DataSet.’stock_left_field’) un produkta ID kategorijas lauks (Imported_DataSet.’productId_field’).
  4. ‘Alias_Name’ ir nosaukums, kas tiek piešķirts datiem, kuru iegūšanai izmantots pelēkais apakšvaicājums. Šajā apakšvaicājumā tiek izmantoti Analytics dati, lai uzzinātu kopējo katra produkta vienumu pārdoto skaitu.
  5. Pēdējā rindiņā tiek izmantots operators ON, lai parādītu kopīgo lauku abās datu kopās, kā arī to apvienošanas vietu.

Daudziem mainīgajiem šajā vaicājumā kā prefikss ir pievienots datu kopas nosaukums (piemēram, Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Tas ir nepieciešams, lai precizētu atlasīto lauku un skaidri norādītu, kurai datu kopai tas pieder.

Tālāk ir sniegts piemērs vaicājumam “Kāds ir produkta pārdoto krājumu procentuālais daudzums 2013. gada 28. jūlijā?”.

Katra produkta pārdoto krājumu procentuālā daļa 2013. gada 28. jūlijā
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;

Katra produkta ienesīgums

Tālāk ir sniegts skripta paraugs jautājumam “Kāds ir katra produkta ienesīgums?”.

Produkta ienesīgums
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. Pirmajā rindiņā ir ietverta matemātiska darbība, lai aprēķinātu katra produkta kopējo peļņu.
  2. Pelēkajā apakšvaicājumā tiek izmantoti dati, kas nav iegūti pakalpojumā Analytics; tajā tiek apkopoti dati par to, cik liela peļņa tiek gūta, pārdodot produktu.
  3. Sarkanais apakšvaicājums ir Analytics datu apakšvaicājums, kas tiks apvienots ar datiem, kas nav Analytics dati. Tādējādi tiek aprēķināts katra produkta pārdoto vienumu daudzums.
  4. Pēdējā rindiņā tiek izmantots operators ON, lai precizētu lauku, kas ir kopīgs abās datu kopās. Šajā gadījumā tas ir produkta ID numurs.

Tālāk ir sniegts piemērs vaicājumam “Kāds bija katra produkta ienesīgums 2013. gada 28. jūlijā?”.

Produkta ienesīgums 2013. gada 28. jūlijā
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 );

Peļņa tiek aprēķināta, nosakot atšķirību starp produkta pārdošanas cenu un ražošanas izmaksām. Šī informācija tiek glabāta datu kopā, kas nav iegūta pakalpojumā Google Analytics.

Katra produkta reālais ienesīgums (ņemot vērā atmaksas gadījumus)

Tālāk ir sniegts skripta paraugs jautājumam “Kāds ir katra produkta reālais ienesīgums?”.

Katra produkta faktiskā peļņa
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. Tas ir ļoti līdzīgs vaicājumam “Kāds ir katra produkta ienesīgums?”. Vienīgās atšķirības ir pelēkajā apakšvaicājumā (datu kopa, kas nav iegūta pakalpojumā Analytics) un pirmajā rindiņā (matemātiskā darbība reālās peļņas aprēķināšanai).
  2. Datu kopā, kas nav iegūta pakalpojumā Analytics, tiek aprēķināta arī kopsumma, kas iztērēta atmaksām (sarkanā apakšvaicājuma operatorā SELECT).
  3. Pēc tam tiek izpildīta 1. rindiņā norādītā matemātiskā darbība, lai noteiktu faktisko peļņu — no bruto peļņas atņemot atmaksai iztērētos ieņēmumus.

Lai iegūtu plašāku informāciju par vaicājumu, lūdzu, skatiet sadaļu par katra produkta ienesīgumu.

Tālāk ir sniegts piemērs vaicājumam “Kāds bija katra produkta reālais ienesīgums 2013. gada 28. jūlijā?”.

Produkta faktiskais ienesīgums 2013. gada 28. jūlijā
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 ) );

Aprēķinot faktisko peļņu, tiek ņemts vērā produkta ienesīgums pēc atmaksas gadījumu apstrādes. Lai aprēķinātu produkta kopējo atmaksu apmēru, izmantojiet tālāk minēto formulu.

produkta kopējais atmaksu apmērs = ( produkta cena + produktam piemērotās atmaksas apmērs ) * produktu skaits, par kuriem izsniegta atmaksa

Vai tas bija noderīgs?

Kā varam to uzlabot?

Vai nepieciešama papildu palīdzība?

Izmēģiniet norādītās nākamās darbības.

Publicēt jautājumu palīdzības kopienā Saņemt atbildes no kopienas dalībniekiem
Sazinieties ar mums Pastāstiet mums vairāk, un mēs jums palīdzēsim.
true
learning path
Izvēlieties savu apmācības ceļu

Skatiet vietni google.com/analytics/learn — tas ir jauns resurss, kas palīdzēs jums efektīvi izmantot platformu Google Analytics 4. Jaunajā vietnē ir iekļauti videoklipi, raksti un ceļvežu plūsmas, kā arī saites uz informāciju par Discord integrāciju ar Google Analytics un saites uz Google Analytics emuāru, YouTube kanālu un GitHub krātuvi.

Sāciet mācīties jau šodien!

Meklēšana
Notīrīt meklēšanu
Aizvērt meklēšanas lodziņu
Galvenā izvēlne
14259923681890270751
true
Meklēšanas palīdzības centrs
true
true
true
true
true
69256
false
false