GSP407

Panoramica

BigQuery è il database di analisi NoOps a basso costo e completamente gestito di Google. Con BigQuery puoi interrogare molti terabyte di dati senza dover gestire alcuna infrastruttura o aver bisogno di un amministratore del database. BigQuery utilizza SQL e sfrutta i vantaggi offerti dal modello di pagamento a consumo. Inoltre, ti permette di concentrarti sull'analisi dei dati per trovare insight significativi.

È disponibile un nuovo set di dati di e-commerce che contiene milioni di record di Google Analytics per il Google Merchandise Store caricati in una tabella di BigQuery. In questo lab utilizzerai una copia di questo set di dati. Vengono forniti scenari di esempio che ti permettono di esaminare i dati e scoprire vari modi per rimuovere le informazioni duplicate. Il lab prevede quindi un'ulteriore analisi dei dati.

Per seguire e sperimentare le query BigQuery fornite per analizzare i dati, consulta la documentazione di riferimento per la sintassi delle query di BigQuery.

Obiettivi

In questo lab utilizzerai BigQuery per:

• Accedere a un set di dati di e-commerce
• Controllare i metadati del set di dati
• Rimuovere le voci duplicate
• Scrivere ed eseguire query

Configurazione e requisiti

Prima di fare clic sul pulsante Avvia lab

Leggi le seguenti istruzioni. I lab sono a tempo e non possono essere messi in pausa. Il timer si avvia quando fai clic su Avvia lab e ti mostra per quanto tempo avrai a disposizione le risorse Google Cloud.

Con questo lab pratico avrai la possibilità di completare le attività in prima persona, in un ambiente cloud reale e non di simulazione o demo. Riceverai delle nuove credenziali temporanee che potrai utilizzare per accedere a Google Cloud per la durata del lab.

Per completare il lab, avrai bisogno di:

• Accesso a un browser internet standard (Chrome è il browser consigliato).

Nota: utilizza una finestra del browser in incognito o privata per eseguire questo lab. Ciò evita eventuali conflitti tra il tuo account personale e l'account Studente, che potrebbero causare addebiti aggiuntivi sul tuo account personale.

• È ora di completare il lab: ricorda che, una volta iniziato, non puoi metterlo in pausa.

Nota: se hai già un account o un progetto Google Cloud personale, non utilizzarlo per questo lab per evitare addebiti aggiuntivi al tuo account.

Come avviare il lab e accedere alla console Google Cloud

1. Fai clic sul pulsante Avvia lab. Se devi effettuare il pagamento per il lab, si apre una finestra popup per permetterti di selezionare il metodo di pagamento. A sinistra, trovi il riquadro Dettagli lab con le seguenti informazioni:

   • Il pulsante Apri console Google Cloud
   • Tempo rimanente
   • Credenziali temporanee da utilizzare per il lab
   • Altre informazioni per seguire questo lab, se necessario

2. Fai clic su Apri console Google Cloud (o fai clic con il tasto destro del mouse e seleziona Apri link in finestra di navigazione in incognito se utilizzi il browser Chrome).

   Il lab avvia le risorse e apre un'altra scheda con la pagina di accesso.

   Suggerimento: disponi le schede in finestre separate posizionate fianco a fianco.

   Nota: se visualizzi la finestra di dialogo Scegli un account, fai clic su Usa un altro account.

3. Se necessario, copia il Nome utente di seguito e incollalo nella finestra di dialogo di accesso.

   {{{user_0.username | "Username"}}}

   Puoi trovare il Nome utente anche nel riquadro Dettagli lab.

4. Fai clic su Avanti.

5. Copia la Password di seguito e incollala nella finestra di dialogo di benvenuto.

   {{{user_0.password | "Password"}}}

   Puoi trovare la Password anche nel riquadro Dettagli lab.

6. Fai clic su Avanti.

   Importante: devi utilizzare le credenziali fornite dal lab. Non utilizzare le credenziali del tuo account Google Cloud. Nota: utilizzare il tuo account Google Cloud per questo lab potrebbe comportare addebiti aggiuntivi.

7. Fai clic nelle pagine successive:

   • Accetta i termini e le condizioni.
   • Non inserire opzioni di recupero o l'autenticazione a due fattori, perché si tratta di un account temporaneo.
   • Non registrarti per le prove gratuite.

Dopo qualche istante, la console Google Cloud si apre in questa scheda.

Nota: per visualizzare un menu con un elenco di prodotti e servizi Google Cloud, fai clic sul menu di navigazione in alto a sinistra.

Attività 1: blocca il progetto del lab in BigQuery

In questa sezione devi aggiungere il progetto data-to-insights alle risorse dell'ambiente.

1. Fai clic sul menu di navigazione > BigQuery.

Si aprirà una finestra con il messaggio Ti diamo il benvenuto in BigQuery sulla console Cloud.

Nota: la finestra del messaggio Ti diamo il benvenuto in BigQuery sulla console Cloud fornisce un link alla guida rapida e agli aggiornamenti dell'interfaccia utente.

2. Fai clic su Fine.

Per impostazione predefinita, i set di dati pubblici di BigQuery non vengono visualizzati nell'interfaccia utente web di BigQuery. Per aprire il progetto di set di dati pubblici, copia "data-to-insights".

3. Fai clic su + Aggiungi > Aggiungi un progetto a Speciali per nome, poi imposta il nome su data-to-insights. Fai clic su AGGIUNGI A SPECIALI.

Ora nella sezione Explorer è elencato il progetto data-to-insights.

Attività 2: esplora i dati di e-commerce e identifica i record duplicati

Scenario: il tuo team di analisti di dati ha esportato dei log di Google Analytics per un sito di e-commerce in BigQuery e ha creato una nuova tabella di tutti i dati non elaborati delle sessioni visitatore dell'e-commerce.

Esplora i dati della tabella all_sessions_raw:

1. Fai clic sull'icona Espandi nodo accanto a data-to-insights per espandere il progetto.
2. Espandi ecommerce.
3. Fai clic su all_sessions_raw.

Nel riquadro di destra si apre una sezione che fornisce tre viste dei dati della tabella:

• Scheda Schema: nome campo, tipo, modalità e descrizione; i vincoli logici utilizzati per organizzare i dati
• Scheda Dettagli: metadati della tabella
• Scheda anteprima: anteprima della tabella

4. Fai clic sulla scheda Dettagli per visualizzare i metadati della tabella.

Domande:

Identifica le righe duplicate

Vedere una quantità di dati di esempio può farti capire meglio che cosa è incluso nel set di dati.

1. Per visualizzare in anteprima le righe di esempio dalla tabella senza utilizzare SQL, dai clic sulla scheda anteprima.

2. Scorri e controlla le righe. Non esiste un campo singolo che identifichi in modo univoco una riga, perciò è necessaria una logica avanzata per identificare le righe duplicate.

3. La query che userai (di seguito) applica la funzione SQL GROUP BY a ogni campo e conteggia (COUNT) le righe che hanno valori identici in ogni campo:

• Se ogni campo è univoco, il valore di COUNT restituisce 1 poiché non esistono altri raggruppamenti di righe con lo stesso identico valore per tutti i campi.
• Se esistono più righe con gli stessi valori per tutti i campi, vengono raggruppate e il valore di COUNT sarà maggiore di 1.

L'ultima parte della query include un filtro di aggregazione mediante HAVING, al fine di mostrare solo i risultati che hanno un conteggio (COUNT) di duplicati maggiore di 1. Ne consegue che il numero di record con duplicati sarà uguale al numero di righe nella tabella risultante.

4. Copia e incolla la query seguente nell'Editor di query, poi esegui (RUN) la query per trovare i record duplicati in tutte le colonne.

#standardSQL SELECT COUNT(*) as num_duplicate_rows, * FROM `data-to-insights.ecommerce.all_sessions_raw` GROUP BY fullVisitorId, channelGrouping, time, country, city, totalTransactionRevenue, transactions, timeOnSite, pageviews, sessionQualityDim, date, visitId, type, productRefundAmount, productQuantity, productPrice, productRevenue, productSKU, v2ProductName, v2ProductCategory, productVariant, currencyCode, itemQuantity, itemRevenue, transactionRevenue, transactionId, pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type, eCommerceAction_step, eCommerceAction_option HAVING num_duplicate_rows > 1;

Nota: nei tuoi set di dati, anche se disponi di una chiave univoca, è comunque utile confermare l'unicità delle righe con COUNT, GROUP BY e HAVING prima di iniziare l'analisi.

Fai clic su Controlla i miei progressi per verificare l'obiettivo. Identifica le righe duplicate

Analizza la nuova tabella all_sessions

In questa sezione utilizzerai una tabella deduplicata chiamata all_sessions.

Scenario: il tuo team di analisti di dati ti ha fornito questa query e gli esperti degli schemi hanno identificato i campi chiave che devono essere univoci per ciascun record in base al tuo schema.

1. Esegui la query per confermare che non esistono duplicati, questa volta nella tabella all_sessions:

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # the unique visitor ID visitId, # a visitor can have multiple visits date, # session date stored as string YYYYMMDD time, # time of the individual site hit (can be 0 to many per visitor session) v2ProductName, # not unique since a product can have variants like Color productSKU, # unique for each product type, # a visitor can visit Pages and/or can trigger Events (even at the same time) eCommerceAction_type, # maps to ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # revenue of the order transactionId, # unique identifier for revenue bearing transaction COUNT(*) as row_count FROM `data-to-insights.ecommerce.all_sessions` GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12 HAVING row_count > 1 # find duplicates

La query non restituisce nessun record.

Nota: in SQL, puoi raggruppare o ordinare l'indice della colonna utilizzando, ad esempio, GROUP BY 1 invece di GROUP BY fullVisitorId.

Attività 3: scrivi una query SQL di base per i dati di e-commerce

In questa sezione eseguirai una query per ottenere insight sul set di dati di e-commerce.

Scrivi una query che mostri il totale dei visitatori unici

La query determina le visualizzazioni totali mediante il conteggio di product_views e il numero di visitatori unici mediante il conteggio di fullVisitorID.

1. Fai clic sull'icona "+" (Crea nuova query).
2. Scrivi questa query nell'editor:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;

3. Per assicurarti che la sintassi sia corretta, verifica che per lo strumento di convalida delle query in tempo reale sia visualizzato il segno di spunta verde.
4. Fai clic su Esegui. Leggi i risultati per visualizzare il numero di visitatori unici.

Risultati:

5. Ora scrivi una query che mostri i visitatori unici totali (fullVisitorID) in base al sito di riferimento (channelGrouping):

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;

Risultati:

6. Scrivi una query che elenchi tutti i nomi prodotto unici (v2ProductName) in ordine alfabetico:

#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName

Suggerimento: in SQL, la clausola ORDER BY segue per impostazione predefinita l'ordine crescente (ASC) dalla A alla Z. Se vuoi ottenere l'ordinamento inverso, prova a usare ORDER BY field_name DESC.

Risultati:

Questa query restituisce un totale di 633 prodotti (righe).

7. Scrivi una query per elencare i cinque prodotti con il maggior numero di visualizzazioni (product_views) da parte di tutti i visitatori, compresi quelli che hanno visualizzato lo stesso prodotto più di una volta. La tua query conta il numero di volte in cui un prodotto (v2ProductName) è stato visualizzato (product_views), organizza l'elenco in ordine decrescente ed elenca le prime cinque voci:

Suggerimento: in Google Analytics, un visitatore può "visualizzare" un prodotto durante questi tipi di interazione: 'page', 'screenview', 'event', 'transaction', 'item', 'social', 'exception', 'timing'. Per i nostri scopi, applicheremo il filtro sono per il tipo 'PAGE'.

#standardSQL SELECT COUNT(*) AS product_views, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Risultati:

8. Bonus: ora perfeziona la query in modo da non contare più due volte le visualizzazioni del prodotto per i visitatori che hanno visualizzato un prodotto molte volte. Ogni visualizzazione distinta del prodotto dovrebbe contare solo una volta per visitatore:

WITH unique_product_views_by_person AS ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

Suggerimento: puoi usare la clausola SQL WITH per suddividere una query complessa in vari passaggi. In questo caso, creiamo prima una query che trova ogni prodotto unico per visitatore e lo conta una volta. La seconda query esegue quindi l'aggregazione di tutti i visitatori e i prodotti.

Risultati:

9. Ora espandi la query precedente in modo da includere il numero totale di prodotti distinti ordinati e il numero totale di unità totali ordinate (productQuantity):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Risultati:

Domande:

10. Espandi la query in modo da includere la quantità media del prodotto per ordine (numero totale di unità ordinate/numero totale di ordini o SUM(productQuantity)/COUNT(productQuantity)):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Risultati

Domanda:

L'infusore per bottiglie YouTube da 22 once ha registrato il valore avg_per_order più alto con 9,38 unità per ordine.

Fai clic su Controlla i miei progressi per verificare l'obiettivo. Scrivi una query SQL di base per i dati di e-commerce

Complimenti!

Complimenti! In questo lab hai utilizzato BigQuery per visualizzare ed eseguire query sui dati in modo da ottenere insight significativi su vari aspetti del marketing del prodotto. Hai imparato ad accedere a un set di dati di e-commerce, esaminare i metadati del set di dati, rimuovere voci duplicate e scrivere ed eseguire query.

Passaggi successivi/Scopri di più

• Esplora i set di dati pubblici di BigQuery.
• Hai un account Google Analytics e vuoi eseguire query sui tuoi set di dati in BigQuery? Segui questa guida all'esportazione.
• Dai uno sguardo a questo articolo su 15 incredibili aspetti di BigQuery che probabilmente non conosci.
• Per scoprire di più su BigQuery, puoi seguire questi lab:
  • Risoluzione dei problemi causati dagli errori comuni di SQL con BigQuery
  • Dati meteorologici in BigQuery

Formazione e certificazione Google Cloud

… per utilizzare al meglio le tecnologie Google Cloud. I nostri corsi ti consentono di sviluppare competenze tecniche e best practice per aiutarti a metterti subito al passo e avanzare nel tuo percorso di apprendimento. Offriamo vari livelli di formazione, dal livello base a quello avanzato, con opzioni di corsi on demand, dal vivo e virtuali, in modo da poter scegliere il più adatto in base ai tuoi impegni. Le certificazioni ti permettono di confermare e dimostrare le tue abilità e competenze relative alle tecnologie Google Cloud.

Ultimo aggiornamento del manuale: 2 aprile 2024

Ultimo test del lab: 2 aprile 2024

Copyright 2024 Google LLC Tutti i diritti riservati. Google e il logo Google sono marchi di Google LLC. Tutti gli altri nomi di società e prodotti sono marchi delle rispettive società a cui sono associati.