Istruzioni su BigQuery

Questa funzionalità è disponibile solo in Analytics 360, che fa parte di Google Marketing Platform.
Ulteriori informazioni su Google Marketing Platform.

Questo articolo contiene esempi di come creare query dei dati di Analytics esportati in BigQuery. Abbiamo reso disponibile un set di dati campione per consentirti di esercitarti con alcune delle query di questo articolo.

Contenuti di questo articolo:

Ottimizzazione delle query

Ogni query eseguita contribuisce alla tua quota di elaborazione dati mensile. Se selezioni campi estranei, aumenti la quantità di dati che devono essere elaborati e, di conseguenza, utilizzi una quantità maggiore del necessario della tua quota mensile. Le query ottimizzate fanno un uso efficiente della quota di elaborazione dati mensile.

Scopri di più sui prezzi.

Seleziona solo il necessario

Quando formuli una query, seleziona i campi pertinenti nell'istruzione SELECT. Non richiamando campi estranei, riduci la quantità di dati e il tempo necessario per l'elaborazione della query.

Esempio: evita di utilizzare l'operatore carattere jolly

Forma non corretta: uso dell'operatore carattere jolly
SELECT *
FROM [table name];

 

Forma migliore: uso dei nomi dei campi per evitare un'elaborazione non necessaria
SELECT field1, field2
FROM [nome tabella];

Consenti la memorizzazione nella cache

Se possibile, evita di utilizzare le funzioni per i campi. Le funzioni (ad esempio NOW() o TODAY()) restituiscono risultati variabili, impedendo che le query vengano memorizzate nella cache e, di conseguenza, che vengano restituite più rapidamente. Utilizza invece ore e date specifiche.

Al momento, i risultati memorizzati nella cache non sono supportati per le query su più tabelle che utilizzano un carattere jolly anche se è selezionata l'opzione Utilizza risultati memorizzati nella cache. Se si esegue la stessa query con caratteri jolly più volte, ti viene addebitato un importo per ogni query. Scopri di più

Utilizza tabelle intermedie per sottoquery comuni

Se ti accorgi di utilizzare ripetutamente una determinata query come sottoquery, puoi salvarla come tabella intermedia facendo clic su Save as Table (Salva come tabella) sopra i risultati delle query. Successivamente, puoi fare riferimento a questa tabella nella sezione FROM della query, riducendo in tal modo la quantità di dati da elaborare e il tempo necessario per l'elaborazione.

Utilizzo di una tabella intermedia
SELECT field1, field2
FROM [Dataset name.table name];

Debug delle query

BigQuery esegue il debug del tuo codice man mano che lo scrivi. Nella finestra di composizione, il debug è indicato subito sotto la query. Il debug è disponibile anche tramite l'API con flag dryRun.

Le query valide hanno un indicatore verde su cui puoi fare clic per visualizzare la quantità di dati elaborati dalla query. Questa funzione ti offre l'occasione per ottimizzare i dati prima di eseguire la query, in modo da evitare un'elaborazione di dati non necessaria.

Query Debugging - Success

 

Le query non valide sono contrassegnate con un indicatore rosso, su cui puoi fare clic per visualizzare le informazioni relative all'errore e individuare la riga e la colonna in cui l'errore si verifica. Nell'esempio seguente, l'istruzione GROUP BY è vuota e l'errore è evidenziato.

Query Debugging - Error

 

Suggerimenti e best practice

Utilizzare il set di dati di esempio

Negli esempi seguenti viene utilizzato il set di dati di esempio di Google Analytics.

Per utilizzare le query sui tuoi dati, sostituisci i nomi del progetto e del set di dati negli esempi con quelli del tuo progetto e set di dati.

Utilizzare l'SQL standard rispetto all'SQL precedente

BigQuery supporta due dialetti SQL:

L'articolo sulla migrazione all'SQL standard spiega le differenze tra i due dialetti.

L'SQL standard è attualmente il dialetto SQL preferito per eseguire query sui dati archiviati in BigQuery.

Consulta l'articolo sull'attivazione dell'SQL standard per informazioni su come attivarlo nella UI, nell'interfaccia a riga di comando, nell'API o in qualsiasi altra interfaccia che utilizzi per BigQuery.

Il modo più semplice per iniziare è includere il commento "standardSQL" all'inizio delle query SQL standard, come illustrato nei seguenti esempi.

Con l'SQL precedente, i dati di Google Analytics 360 vengono trasferiti in una nuova tabella ogni giorno. Per eseguire una query su più tabelle contemporaneamente, puoi separare i nomi delle tabelle con una virgola, utilizzare la funzione carattere jolly nella tabella TABLE_DATE_RANGE oppure utilizzare più funzioni TABLE_DATE_RANGE separate da virgola come negli esempi riportati di seguito.

Eseguire query su più tabelle

Gli esempi seguenti mostrano query in SQL standard e in SQL precedente per gli stessi dati.

3 giorni

SQL standard
3 giorni utilizzando 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 precedente
3 giorni utilizzando nomi delle tabelle separati da virgola
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

 

Ultimi 1095 giorni

SQL standard
Ultimi 1095 giorni utilizzando _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 precedente
Ultimi 1095 giorni utilizzando 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

 

Ultimi 36 mesi

SQL standard
Ultimi 36 mesi utilizzando _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 precedente
Ultimi 36 mesi utilizzando 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

 

Ultimi 3 anni

SQL standard
Ultimi 3 anni utilizzando _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 precedente
Ultimi 3 anni utilizzando 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

 

Intervallo di date specifico

SQL standard
Intervallo di date specifico utilizzando _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 precedente
Intervallo di date specifico utilizzando 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

Ultimi 3 anni oltre ai dati di oggi (giornalieri)

SQL standard
Ultimi 3 anni oltre ai dati di oggi (giornalieri) utilizzando UNION ALL e _TABLE_SUFFIX
Nota: questa query di esempio non funzionerà con il set di dati pubblici di Google Analytics perché al momento non esiste una tabella giornaliera.
#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 precedente

Ultimi 3 anni oltre ai dati di oggi (giornalieri) utilizzando più TABLE_DATE_RANGE
Nota: questa query di esempio non funzionerà con il set di dati pubblici di Google Analytics perché al momento non esiste una tabella giornaliera.
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

 

 

Esempi di query di base

Questa sezione illustra come costruire query di base utilizzando metriche e dimensioni tratte dai dati Analytics di esempio.

Totale [metrica] per [dimensione]?

Di seguito sono riportati script di esempio per la domanda Qual è il numero totale di transazioni generate per browser di dispositivo a luglio 2017?

SQL standard

Transazioni totali per browser di dispositivo a luglio 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 precedente

Transazioni totali per browser di dispositivo a luglio 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

 

 

Frequenza di rimbalzo media per [ dimensione ]?

La frequenza di rimbalzo effettiva è definita come la percentuale di visite con una sola visualizzazione di pagina. Di seguito sono riportati script di esempio per la domanda Qual è stata la frequenza di rimbalzo effettiva per sorgente di traffico?

SQL standard

Frequenza di rimbalzo per sorgente di traffico a luglio 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 precedente

Frequenza di rimbalzo per sorgente di traffico a luglio 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

 

 

Numero medio di visualizzazioni di pagine di prodotto per tipo di acquirente (acquirenti e non acquirenti)

Di seguito sono riportati script di esempio per la domanda Qual è stato il numero medio di visualizzazioni di pagine di prodotto per gli utenti che hanno effettuato un acquisto a luglio 2017?

SQL standard

Numero medio di visualizzazioni di pagine di prodotto per gli utenti che hanno effettuato un acquisto a luglio 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 precedente

Numero medio di visualizzazioni di pagine di prodotto per gli utenti che hanno effettuato un acquisto a luglio 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 )

 

 

Di seguito sono riportati script di esempio per la domanda Qual è stato il numero medio di visualizzazioni di pagine di prodotto per gli utenti che non hanno effettuato un acquisto a luglio 2017?

SQL standard

Numero medio di visualizzazioni di pagine di prodotto per gli utenti che non hanno effettuato un acquisto a luglio 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 precedente

Numero medio di visualizzazioni di pagine di prodotto per gli utenti che non hanno effettuato un acquisto a luglio 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 )

 

 

Numero medio di transazioni per acquirente

Di seguito sono riportati script di esempio per la domanda Qual è stata la media delle transazioni totali per utente che ha effettuato un acquisto a luglio 2017?

SQL standard

Numero medio di transazioni per utente che ha effettuato un acquisto a luglio 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 precedente

Numero medio di transazioni per utente che ha effettuato un acquisto a luglio 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 )

 

 

Importo medio di denaro speso per sessione

Di seguito sono riportati script di esempio per la domanda Qual è l'importo medio speso per sessione a luglio 2017?

SQL standard

Importo medio speso per sessione a luglio 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 precedente

Importo medio speso per sessione a luglio 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 )

 

 

Sequenza di hit

Di seguito sono riportati script di esempio per la domanda Qual è la sequenza di pagine visualizzate?

SQL standard

Sequenza di pagine visualizzate dagli utenti a luglio 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 precedente

Sequenza di pagine visualizzate dagli utenti a luglio 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

In questa query, limiti i tipi di hit a PAGE per evitare di vedere le interazioni evento o transazione. Ogni riga del risultato rappresenta una visualizzazione di pagina ed è visualizzata in base all'ordine predefinito dei campi nell'istruzione SELECT.

 

 

Dimensioni personalizzate multiple a livello di hit o sessione

Dimensione personalizzata a livello di hit
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

 

Dimensione personalizzata a livello di sessione
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

In ogni query:

L'istruzione SELECT ricerca i campi dimensione e metrica pertinenti.

La funzione MAX:

  • Restituisce la dimensione personalizzata come nuova colonna. Puoi ripetere la funzione per restituire più dimensioni personalizzate come colonne.
  • WITHIN hits e WITHIN RECORD valutano la condizione all'interno dei campi ripetuti in BigQuery.
  • La condizione all'interno di MAX viene valutata per ogni dimensione personalizzata, ma per qualsiasi dimensione diversa da index=1 (hit) o index=2 (sessioni), restituisce NULL.
  • Restituisce il valore massimo, ossia il valore della Dimensione personalizzata 1 per gli hit o della Dimensione personalizzata 2 per le sessioni, in quanto tutti gli altri valori sono NULL.

Esempi di query avanzate

Ora che hai acquisito familiarità con le query semplici, puoi costruire query utilizzando le funzioni avanzate disponibili in BigQuery.

Prodotti acquistati dai clienti che hanno acquistato il prodotto A (e-commerce classico)

Di seguito è riportata una bozza di script per la domanda Quali altri prodotti vengono acquistati dai clienti che acquistano il prodotto A?

Prodotti acquistati da un cliente che acquista il prodotto A (e-commerce classico)
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. Nella prima riga, selezioni tutti gli altri articoli acquistati da un utente e la funzione aggregata COUNT() è utilizzata per calcolare la quantità di ciascun altro articolo acquistato. Il risultato viene quindi visualizzato in un campo con etichetta quantity, con l'articolo associato nel campo prodotto con etichetta other_purchased_products.
  2. Nella sottoquery in grigio, selezioni solo gli utenti unici (fullVisitorId) che hanno eseguito transazioni (totals.transactions>=1) e che, durante una transazione, hanno acquistato il prodotto A (WHERE hits.item.productName CONTAINS 'Product Item Name A').

Le regole (istruzioni WHERE e AND) nella query di livello superiore (verde) ignorano i valori in hits.item.productName, che sono nulli e contengono il prodotto A.

Ecco un esempio relativo alla query Se un cliente acquista Penne Brighton in metallo - Gruppo di 4, quali altri prodotti ha acquistato?

Prodotti acquistati da un cliente che acquista "Penne Brighton in metallo - Gruppo di 4" il 24 giugno 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;

In Dremel/BigQuery, l'uso di WHERE expr IN attiva un JOIN e l'applicazione di limiti di dimensioni; in particolare, la dimensione del lato destro del JOIN (in questo caso il numero di visitatori) deve essere minore di 8 MB. In Dremel, è definito JOIN di trasmissione. Se la dimensione supera gli 8 MB, devi attivare un JOIN casuale, operazione eseguibile mediante la sintassi JOIN EACH. Purtroppo, non è possibile eseguirla utilizzando IN, ma la stessa query può essere riscritta con un JOIN.

Prodotti acquistati dai clienti che hanno acquistato il prodotto A (e-commerce avanzato)

Il seguente script è simile alla bozza di query precedente ma funziona per il metodo E-commerce avanzato. Utilizza anche l'istruzione TABLE_DATE_RANGE per eseguire query sui dati nell'arco di più giorni.

Prodotti acquistati da un cliente che acquista il prodotto A (e-commerce avanzato)
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;

Numero medio di interazioni utente prima dell'acquisto

Questo è un esempio di una query di un comando JOIN() [...] ON, che dipende solo dai dati di Analytics.

Di seguito è riportata una bozza di script per la domanda Qual è il numero medio di interazioni utente prima di un acquisto?

Numero di interazioni utente prima di un acquisto
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. Nella prima riga viene eseguita l'operazione matematica principale per trovare le interazioni utente medie per prodotto; inoltre, questa query crea un join tra due sottoquery denominate "Alias_Name_1" e "Alias_Name_2".
  2. "Alias_Name_1" è utilizzata per produrre un campo che utilizza la funzione aggregata SUM() per sommare tutti gli hit registrati per un prodotto.
  3. "Alias_Name_2" è utilizzata per trovare il numero di hit effettuati dagli utenti per prodotto utilizzando la funzione COUNT().
  4. L'ultima riga mostra il campo comune (hits.item.productSku) ai due set di dati del join.

Ecco un esempio relativo alla query Qual è il numero medio di interazioni utente effettuate il 10 settembre 2013 prima di un acquisto?

Numero di interazioni utente effettuate il 10 settembre 2013 prima di un acquisto
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;

Percentuale di articoli a magazzino venduti per prodotto

Questo è un esempio di query che non dipende solo dai dati di Analytics, ma anche da dati di provenienza diversa. Combinando entrambi i set di dati, puoi iniziare a capire il comportamento degli utenti a un livello più dettagliato. Puoi importare in BigQuery dati non provenienti da Analytics, ma tieni presente che questo incide sugli addebiti mensili per l'archiviazione dati.

Di seguito è riportata una bozza di script per la domanda Qual è la percentuale di articoli a magazzino venduti per prodotto?

Percentuale di articoli a magazzino venduti per prodotto
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 prima riga genera due campi: uno contiene tutti gli ID prodotto, mentre l'altro esegue un'operazione matematica che mostra la percentuale di articoli a magazzino venduti per quell'ID prodotto.
  2. Poiché questa query dipende da due set di dati, devi utilizzare la funzione JOIN() ... ON Questo comando unisce le righe dei due set di dati in base al campo in comune tra i due set. In questo caso, i due set di dati sono [ "Imported_DataSet" ] e "Alias_Name".
  3. [ ‘Imported_DataSet’ ] rappresenta i dati non Analytics. Contiene il campo metrica relativo alla quantità di articoli a magazzino rimasti (Imported DataSet.’stock_left_field’) e il campo dimensione relativo all'ID prodotto (Imported_DataSet.’productId_field’).
  4. "Nome_alias" è il nome assegnato ai dati restituiti dalla sottoquery in grigio. Questa sottoquery utilizza dati di Analytics per trovare la quantità totale di articoli venduti per prodotto.
  5. L'ultima riga utilizza l'istruzione ON per mostrare il campo comune tra i due set di dati e il punto in cui quei set di dati sono uniti.

A molte delle variabili di questa query è associato il nome del relativo set di dati sotto forma di prefisso (ad es. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Questo ti consente di capire chiaramente quale campo stai selezionando e a quale set di dati appartiene.

Ecco un esempio relativo alla query Qual è la percentuale di articoli a magazzino venduti per prodotto il 28 luglio 2013?

Percentuale di articoli a magazzino venduti per prodotto il 28 luglio 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;

Redditività di ogni prodotto

Di seguito è riportata una bozza di script per la domanda Qual è la redditività di ogni prodotto?

Profitto per prodotto
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 prima riga contiene l'operazione matematica per calcolare il profitto totale realizzato per ogni prodotto.
  2. La sottoquery in grigio utilizza dati di provenienza diversa da Analytics e raccoglie i dati sull'entità del profitto realizzato dalla vendita di un prodotto.
  3. La sottoquery rossa utilizza i dati di Analytics, che vengono uniti ai dati di provenienza diversa. Calcola la quantità di articoli venduti per prodotto.
  4. L'ultima riga utilizza l'istruzione ON per indicare il campo in comune tra i due set di dati. In questo caso, è il numero di id prodotto.

Ecco un esempio relativo alla query Qual era la redditività di ogni prodotto il 28 luglio 2013?

Profitto per prodotto il 28 luglio 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 );

Il profitto viene calcolato facendo la differenza tra il prezzo di vendita del prodotto e il suo costo di fabbricazione. Queste informazioni vengono memorizzate nel set di dati non Google Analytics.

Redditività effettiva di ogni prodotto (che tiene conto dei rimborsi)

Di seguito è riportata una bozza di script per la domanda Qual è la redditività effettiva di ogni prodotto?

Profitto effettivo per prodotto
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. Questa è una query molto simile a Qual è la redditività di ogni prodotto? Le uniche differenze stanno nel set di dati non Analytics nella sottoquery in grigio e nell'operazione matematica per il calcolo del profitto effettivo nella prima riga.
  2. Nel set di dati non Analytics viene anche calcolato l'importo totale speso per i rimborsi (nell'istruzione SELECT della sottoquery in rosso).
  3. Successivamente, esegui un'operazione matematica alla riga 1 per trovare il profitto effettivo, sottraendo le entrate spese per i rimborsi dal profitto lordo.

Per scoprire di più sulla query, consulta la sezione sulla redditività di ogni prodotto.

Ecco un esempio relativo alla query Qual è stata la redditività effettiva di ogni prodotto il 28 luglio 2013?

Profitto effettivo per prodotto il 28 luglio 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 ) );

Il profitto effettivo considera la redditività di un prodotto dopo avere tenuto conto dei prodotti rimborsati. Per calcolare le entrate totali rimborsate per un prodotto:

entrate totali rimborsate per un prodotto = (prezzo del prodotto + prezzo di spedizione rimborsato del prodotto) * quantità di prodotti rimborsati

È stato utile?

Come possiamo migliorare l'articolo?

Hai bisogno di ulteriore assistenza?

Prova i passaggi successivi indicati di seguito:

Pubblica una domanda nella community di assistenza Ricevi risposte dai membri della community
Contattaci Dacci ulteriori informazioni e potremo aiutarti
true
learning path
Scegli il tuo percorso di apprendimento

Consulta google.com/analytics/learn, una nuova risorsa che ti aiuta a ottenere il massimo da Google Analytics 4. Il nuovo sito web include video, articoli e procedure guidate e fornisce link a varie fonti di informazioni su Google Analytics, come Discord, blog, canale YouTube e repository GitHub.

Inizia a imparare

Ricerca
Cancella ricerca
Chiudi ricerca
Menu principale
14729473552909562927
true
Cerca nel Centro assistenza
true
true
true
true
true
69256
false
false