Manual de BigQuery

Aquesta funció només està disponible a Analytics 360, que forma part de Google Marketing Platform.
Obteniu més informació sobre Google Marketing Platform.

Aquest article conté exemples de com podeu crear consultes de les dades de Google Analytics que exporteu a BigQuery. A més, disposeu d'un conjunt de dades de mostra per practicar amb les consultes de l'article.

Contingut d'aquest article:

Optimització de les consultes

Cada consulta que executeu contribueix a la vostra assignació mensual de tractament de dades. Si seleccioneu camps superflus, augmenteu la quantitat de dades que s'han de tractar i, com a resultat, utilitzeu més assignació mensual de la necessària. Les consultes optimitzades fan un ús eficient de l'assignació mensual de tractament de dades.

Obteniu més informació sobre els preus.

Seleccionar només el que es necessita

Quan formuleu una consulta, seleccioneu els camps pertinents de la instrucció SELECT. Si no crideu camps superflus, reduïu la quantitat de dades i el temps necessari per tractar la consulta.

Exemple: no utilitzeu l'operador de comodí

Forma incorrecta: utilitzar l'operador de comodí
SELECT *
FROM [table name];

 

Forma adequada: utilitzar noms de camps per evitar el tractament innecessari
SELECT field1, field2
FROM [table name];

Permetre que es desin dades a la memòria cau

Sempre que sigui possible, eviteu utilitzar funcions com a camps. Les funcions (com ara NOW() o TODAY()) ofereixen resultats variables, que impedeixen que les consultes s'emmagatzemin a la memòria cau i, per tant, ofereixin una resposta més ràpida. És millor que utilitzeu hores i dates específiques.

Actualment, els resultats desats a la memòria cau no són compatibles amb les consultes en diverses taules en què s'utilitza un comodí, encara que estigui marcada l'opció Utilitza els resultats desats a la memòria cau. Si executeu la mateixa consulta comodí diverses vegades, se us facturarà cada consulta. Més informació

Fer servir taules intermèdies per a les subconsultes més utilitzades

Si detecteu que sovint utilitzeu una consulta concreta com a subconsulta, podeu desar-la com a taula intermèdia fent clic a Save as a Table (Desa com una taula) al damunt dels resultats de la consulta. A continuació, podeu fer referència a aquesta taula a la secció FROM de la consulta, cosa que reduirà tant la quantitat de dades que s'han de tractar com el temps necessari per fer-ho.

Utilitzar una taula intermèdia
SELECT field1, field2
FROM [Dataset name.table name];

Depuració de consultes

BigQuery depura el codi mentre el creeu. A la finestra de creació del codi, la depuració s'indica just a sota de la consulta. La depuració també es pot dur a terme a través de l'API amb la marca dryRun.

Les consultes vàlides tenen un indicador verd en el qual podeu fer clic per veure la quantitat de dades que han tractat. Aquesta funció us dona l'oportunitat d'optimitzar les dades abans d'executar la consulta, de manera que pugueu evitar que es tractin dades innecessàries.

Query Debugging - Success

 

Les consultes no vàlides tenen un indicador vermell en el qual podeu fer clic per veure informació sobre l'error i trobar la línia i la columna en què es produeix. A l'exemple següent, la instrucció GROUP BY és buida, i s'assenyala l'error.

Query Debugging - Error

 

Consells i pràctiques recomanades

Utilitzar el conjunt de dades de mostra

Els exemples següents utilitzen el conjunt de dades de mostra de Google Analytics.

Per utilitzar aquestes consultes amb les vostres dades, només cal que substituïu el nom del projecte i del conjunt de dades dels exemples pels noms reals.

SQL estàndard vs. SQL heretat

BigQuery és compatible amb dos dialectes d'SQL:

L'article sobre la migració a SQL estàndard explica les diferències entre tots dos dialectes.

Actualment, el dialecte SQL estàndard és el preferit per consultar dades emmagatzemades a BigQuery.

Llegiu l'article sobre l'activació d'SQL estàndard per obtenir informació sobre com podeu activar SQL estàndard a la interfície d'usuari (IU), a la interfície de línia d'ordres (CLI) o a l'API de BigQuery, o en qualsevol interfície que utilitzeu.

La manera més senzilla de començar és incloure el comentari "standardSQL" a la part superior de les consultes en SQL estàndard, tal com es mostra als exemples següents.

Amb SQL heretat, les dades de Google Analytics 360 es transfereixen cada dia a una taula nova. Per consultar diverses taules alhora, podeu separar els noms de les taules amb comes, utilitzar la funció de comodí de taula TABLE_DATE_RANGE o bé fer servir diverses funcions TABLE_DATE_RANGE separades per comes, tal com es mostra als exemples següents.

Consultar diverses taules

Els exemples següents mostren consultes de les mateixes dades en SQL estàndard i SQL heretat.

3 dies

SQL estàndard
3 dies amb 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
SQL heretat
3 dies amb noms de taula separats per comes
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

 

Els 1.095 darrers dies

SQL estàndard
Els 1.095 darrers dies amb _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
SQL heretat
Els 1.095 darrers dies amb 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

 

Els 36 darrers mesos

SQL estàndard
Els 36 darrers mesos amb _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
SQL heretat
Els 36 darrers mesos amb 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

 

Els 3 darrers anys

SQL estàndard
Els 3 darrers anys amb _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
SQL heretat
Els 3 darrers anys amb 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

 

Interval de dates específic

SQL estàndard
Interval de dates específic amb _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
SQL heretat
Interval de dates específic amb 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

Els 3 darrers anys més les dades d'avui (intradia)

SQL estàndard
Els 3 darrers anys més les dades d'avui (intradia) amb UNION ALL i _TABLE_SUFFIX
Nota: aquesta consulta d'exemple no funcionarà amb el conjunt de dades públic de Google Analytics perquè actualment no hi ha cap taula intradiària.
#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

SQL heretat

Els 3 darrers anys més les dades d'avui (intradia) amb diferents TABLE_DATE_RANGE
Nota: aquesta consulta d'exemple no funcionarà amb el conjunt de dades públic de Google Analytics perquè actualment no hi ha cap taula intradiària.
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

 

 

Exemples de consultes bàsiques

En aquesta secció s'explica com es construeixen consultes bàsiques mitjançant mètriques i dimensions d'exemples de dades d'Analytics.

Total de [mètrica] per [dimensió]?

A continuació es mostren scripts d'exemple per a la pregunta: Quantes transaccions en total es van generar per navegador de dispositiu el juliol de 2017?

SQL estàndard

Total de transaccions per navegador de dispositiu el juliol de 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

SQL heretat

Total de transaccions per navegador de dispositiu el juliol de 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

 

 

Percentatge de rebots mitjà per [dimensió]?

El percentatge de rebots real es defineix com el percentatge de visites amb una sola visualització de pàgina. A continuació es mostren scripts d'exemple per a la pregunta: Quin va ser el percentatge de rebots real per font del trànsit?

SQL estàndard

Percentatge de rebots per font del trànsit el juliol de 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

SQL heretat

Percentatge de rebots per font del trànsit el juliol de 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

 

 

Nombre mitjà de visualitzacions de pàgina de producte per tipus de comprador (compradors vs. no compradors)

A continuació es mostren scripts d'exemple per a la pregunta: Quin va ser el nombre mitjà de visualitzacions de pàgina de producte per part dels usuaris que van fer una compra el juliol de 2017?

SQL estàndard

Nombre mitjà de visualitzacions de pàgina de producte per part dels usuaris que van fer una compra el juliol de 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 )

SQL heretat

Nombre mitjà de visualitzacions de pàgina de producte per part dels usuaris que van fer una compra el juliol de 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 )

 

 

A continuació es mostren scripts d'exemple per a la pregunta: Quin va ser el nombre mitjà de visualitzacions de pàgina de producte per part dels usuaris que no van fer cap compra el juliol de 2017?

SQL estàndard

Nombre mitjà de visualitzacions de pàgina de producte per part dels usuaris que no van fer cap compra el juliol de 2017
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

SQL heretat

Nombre mitjà de visualitzacions de pàgina de producte per part dels usuaris que no van fer cap compra el juliol de 2017
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

Nombre mitjà de transaccions per comprador

A continuació es mostren scripts d'exemple per a la pregunta: Quin va ser el total mitjà de transaccions per cada usuari que va fer una compra el juliol de 2017?

SQL estàndard

Nombre mitjà de transaccions per cada usuari que va fer una compra el juliol de 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 )

SQL heretat

Nombre mitjà de transaccions per cada usuari que va fer una compra el juliol de 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 )

 

 

Import de diners mitjà gastat per sessió

A continuació es mostren scripts d'exemple per a la pregunta: Quin va ser l'import de diners mìtjà gastat per sessió el juliol de 2017?

SQL estàndard

Import de diners mitjà gastat per sessió el juliol de 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 )

SQL heretat

Import de diners mitjà gastat per sessió el juliol de 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 )

 

 

Seqüència de peticions de fitxer

A continuació es mostren scripts d'exemple per a la pregunta: Quina és la seqüència de pàgines visualitzades?

SQL estàndard

Seqüència de pàgines visualitzades pels usuaris el juliol de 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

SQL heretat

Seqüència de pàgines visualitzades pels usuaris el juliol de 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

En aquesta consulta, limiteu els tipus de peticions de fitxer a PAGES per evitar veure interaccions d'esdeveniment o de transacció. Cada línia del resultat representa una visualització de pàgina i es mostra segons l'ordre predeterminat dels camps de la instrucció SELECT.

 

 

Diverses dimensions personalitzades al nivell de petició de fitxer o de sessió

Dimensió personalitzada al nivell de petició de fitxer
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

 

Dimensió personalitzada al nivell de sessió
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

En totes dues consultes:

La instrucció SELECT consulta els camps de dimensió i de mètrica rellevants.

La funció MAX:

  • Torna la dimensió personalitzada en una columna nova. Podeu repetir la funció per mostrar diverses dimensions personalitzades en columnes noves.
  • WITHIN hits i WITHIN RECORD avaluen la condició dins de camps repetits a BigQuery.
  • La condició dins de MAX s'avalua per a cada dimensió personalitzada, però en el cas de les que no són index=1 (hits) o index=2 (sessions), torna NULL.
  • Torna el valor màxim, que és el valor de la dimensió personalitzada 1 per a les peticions de fitxer o de la dimensió personalitzada 2 per a les sessions, ja que la resta de valors són NULL.

Exemples de consultes avançades

Ara que ja us heu familiaritzat amb les consultes senzilles, podeu crear consultes amb les funcions avançades i amb les funcions disponibles a BigQuery.

Productes adquirits pels clients que han comprat el producte A (comerç electrònic clàssic)

A continuació es mostra un script bàsic per a la pregunta: Quins altres productes han adquirit els clients que han comprat el producte A?

Productes adquirits per un client que ha comprat el producte A (comerç electrònic clàssic)
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. A la primera línia, seleccioneu tots els altres articles que ha comprat un usuari, i la funció de suma COUNT() es fa servir per calcular la quantitat de cada article comprat. A continuació, el resultat es mostra en un camp etiquetat com a quantity, amb el seu article associat al camp de producte etiquetat com a other_purchased_products.
  2. A la subconsulta en gris, seleccioneu només els usuaris únics (fullVisitorId) que han realitzat transaccions (totals.transactions>=1) i que durant una transacció han comprat el producte A (WHERE hits.item.productName CONTAINS 'nom del producteProduct Item Name A').

Les regles (instruccions WHERE i AND) de la consulta de nivell superior (en verd) ometen els valors de hits.item.productName, que són nuls i contenen el producte A.

A continuació es mostra un exemple de la consulta següent: si un client compra un paquet de quatre bolígrafs metàl·lics Brighton, quins altres productes ha comprat?

Productes adquirits per un client que va comprar un paquet de quatre bolígrafs metàl·lics Brighton el 24 de juny de 2013
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton 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;

A Dremel/BigQuery, l'ús de WHERE expr IN activa una ordre JOIN, i s'apliquen restriccions de mida; concretament, la mida del costat dret de JOIN (en aquest cas, el nombre de visitants) ha de ser inferior a 8 MB. A Dremel, això s'anomena broadcast JOIN. Quan la mida supera els 8 MB, heu d'activar una ordre shuffled JOIN, cosa que es pot fer amb la sintaxi JOIN EACH. Malauradament, no es pot fer mitjançant IN, però la mateixa consulta es pot tornar a escriure amb una ordre JOIN.

Productes adquirits pels clients que han comprat el producte A (comerç electrònic millorat)

L'script d'aquesta consulta és semblant al de la consulta anterior, però es pot utilitzar amb el comerç electrònic millorat. A més, fa servir la funció TABLE_DATE_RANGE per consultar dades corresponents a diversos dies.

Productes adquirits per un client que ha comprat el producte A (comerç electrònic millorat)
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;

Nombre mitjà d'interaccions d'usuari abans d'una compra

A continuació es mostra un exemple d'una consulta amb una ordre JOIN() [...] ON, que només depèn de les dades d'Analytics.

A continuació es mostra un script bàsic per a la pregunta: Quin és el nombre mitjà d'interaccions d'usuari abans d'una compra?

Nombre d'interaccions d'usuari abans d'una compra
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. La primera línia duu a terme l'operació matemàtica principal per calcular el nombre mitjà d'interaccions d'usuari per producte, i aquesta consulta crea una unió entre dues subconsultes anomenades ‘Alias_Name_1’ i ‘Alias_Name_2’.
  2. ‘Alias_Name_1’ s'utilitza per crear un camp que fa servir la funció de suma SUM() per sumar totes les peticions de fitxer registrades d'un producte.
  3. ‘Alias_Name_2’ s'utilitza per calcular el nombre de peticions de fitxer que fan els usuaris per producte, mitjançant la funció COUNT().
  4. L'última línia inclou el camp comú (hits.item.productSku) que comparteixen els dos conjunts de dades combinats.

A continuació es mostra un exemple de la consulta següent: el 10 de setembre de 2013, quina va ser la mitjana d'interaccions d'usuari abans d'una compra?

Nombre d'interaccions d'usuari el 10 de setembre de 2013 abans d'una compra
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;

Percentatge d'inventari venut per producte

A continuació teniu un exemple d'una consulta que depèn tant de les dades que són d'Analytics com de les que no ho són. La combinació d'ambdós conjunts de dades permet començar a conèixer el comportament de l'usuari en un nivell més segmentat. Tot i que podeu importar les dades que no són d'Analytics a BigQuery, tingueu en compte que s'inclouran a la capacitat d'emmagatzematge de dades mensual que teniu assignada.

A continuació es mostra un script bàsic per a la pregunta: Quin percentatge d'inventari s'ha venut per producte?

Percentatge d'inventari venut per producte
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. La primera línia genera dos camps: un conté tots els identificadors de producte i l'altre és una operació matemàtica que mostra el percentatge d'inventari venut corresponent a cada identificador de producte.
  2. Com que aquesta consulta depèn de dos conjunts de dades, heu d'utilitzar la funció JOIN() ... ON. Aquesta ordre combina les files dels dos conjunts de dades basant-se en el camp que tenen en comú. En aquest cas, els dos conjunts de dades són [ ‘Imported_DataSet’ ] i ‘Alias_Name’.
  3. El conjunt de dades [ ‘Imported_DataSet’ ] és el que no conté dades d'Analytics. Aquest és el conjunt de dades que conté el camp de mètrica per saber la quantitat d'inventari disponible (Imported DataSet.’stock_left_field’) i el camp de dimensió d'identificador de producte (Imported_DataSet.’productId_field’).
  4. ‘Nom de l'àlies’ és el nom assignat a les dades que s'obtenen amb la subconsulta en gris. Aquesta subconsulta utilitza dades d'Analytics per calcular la quantitat total d'articles venuts per producte.
  5. L'última línia utilitza la instrucció ON per mostrar el camp comú entre els dos conjunts de dades i on s'uneixen.

Moltes de les variables d'aquesta consulta tenen el nom del seu conjunt de dades adjuntat com a prefixos (p. ex., Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). D'aquesta manera queda clar quin camp esteu seleccionant i es fa explícit a quin conjunt de dades pertany.

A continuació es mostra un exemple de la consulta següent: quin percentatge d'inventari es va vendre per producte el 28 de juliol de 2013?

Percentatge d'inventari venut per producte el 28 de juliol de 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;

Rendibilitat de cada producte

A continuació es mostra l'script bàsic corresponent a la pregunta: Quina és la rendibilitat de cada producte?

Benefici per producte
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. La primera línia inclou l'operació matemàtica que permet calcular el benefici total que s'obté de cada producte.
  2. La subconsulta en gris utilitza dades que no són d'Analytics, que permeten calcular els beneficis que s'obtenen de la venda d'un producte.
  3. La subconsulta en vermell utilitza les dades d'Analytics, que s'afegiran a les que no són d'Analytics. Calcula la quantitat d'articles venuts per producte.
  4. L'última línia utilitza la instrucció ON per aclarir quin camp comparteixen els dos conjunts de dades. En aquest cas, és el número d'identificació del producte.

A continuació es mostra un exemple de la consulta següent: quina va ser la rendibilitat de cada producte el 28 de juliol de 2013?

Benefici per producte el 28 de juliol de 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 );

El benefici es calcula a partir de la diferència entre el preu de venda i el cost de fabricació del producte. Aquesta informació s'emmagatzema al conjunt de dades que no pertany a Google Analytics.

Rendibilitat real de cada producte (tenint en compte els reembossaments)

A continuació es mostra l'script bàsic corresponent a la pregunta: Quina és la rendibilitat real de cada producte?

Benefici real per producte
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. Aquesta consulta és molt semblant a: quina és la rendibilitat de cada producte? Les úniques diferències es troben en el conjunt de dades que no són d'Analytics de la subconsulta en gris i en l'operació matemàtica de la primera línia que calcula el benefici real.
  2. Al conjunt de dades que no són d'Analytics, també calculeu l'import total de diners que heu invertit en reembossaments (a la instrucció SELECT de la subconsulta en vermell).
  3. A continuació, dueu a terme una operació matemàtica a la línia 1 per calcular el benefici real, restant del benefici brut l'import invertit en reembossaments.

Per obtenir més informació sobre la consulta, llegiu la secció sobre la rendibilitat de cada producte.

A continuació es mostra un exemple de la consulta següent: quina va ser la rendibilitat real de cada producte el 28 de juliol de 2013?

Benefici real per producte el 28 de juliol de 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 ) );

El benefici real té en compte la rendibilitat d'un producte un cop considerat l'import dels reembossaments. Per calcular l'import de reembossament total d'un producte:

Total d'ingressos per reembossaments d'un producte = (preu del producte + preu d'enviament del reembossament del producte) * quantitat de productes reembossats

Ha estat útil?

Com ho podem millorar?

Necessites més ajuda?

Prova aquests passos:

Fes una publicació a la comunitat d'ajuda Obtén respostes dels membres de la comunitat
Contacta amb nosaltres Proporciona'ns més informació i t'ajudarem
true
learning path
Trieu un itinerari d'aprenentatge propi

Consulteu google.com/analytics/learn, un recurs nou per ajudar-vos a treure el màxim profit de Google Analytics 4. El lloc web nou inclou vídeos, articles i fluxos guiats, i proporciona enllaços a Discord, al blog, al canal de YouTube i al repository de GitHub de Google Analytics.

Comenceu a aprendre avui mateix!

Cerca
Esborra la cerca
Tanca la cerca
Menú principal
12679552490721688287
true
Cerca al Centre d'ajuda
true
true
true
true
true
69256
false
false