GSP407

Opis

BigQuery to ekonomiczna, w pełni zarządzana analityczna baza danych Google typu NoOps. Z BigQuery możesz przeszukiwać wiele terabajtów danych bez konieczności zarządzania infrastrukturą czy wyznaczania administratora bazy danych. Usługa BigQuery opiera się na języku SQL i jest dostępna w modelu płatności według wykorzystania. Pracując w BigQuery, możesz skoncentrować się na analizowaniu danych i wyciąganiu z nich znaczących wniosków.

Do tabeli w BigQuery został wczytany najnowszy zbiór danych e-commerce zawierający miliony rekordów Google Analytics ze sklepu Google Merchandise Store. W tym module wykorzystasz kopię tego zbioru danych. Przygotowaliśmy przykładowe scenariusze, w których przyjrzysz się danym i poznasz sposoby usuwania zduplikowanych informacji. Moduł poprowadzi Cię potem przez dalszą analizę danych.

Aby zrozumieć przedstawione zapytania BigQuery umożliwiające analizę danych i móc z nimi poeksperymentować, zajrzyj do informacji o składni zapytań BigQuery.

Cele

W tym module za pomocą BigQuery:

• uzyskasz dostęp do zbioru danych e-commerce,
• przeanalizujesz metadane zbioru danych,
• usuniesz zduplikowane wpisy,
• napiszesz i uruchomisz zapytania.

Konfiguracja i wymagania

Zanim klikniesz przycisk Rozpocznij moduł

Zapoznaj się z tymi instrukcjami. Moduły mają limit czasowy i nie można ich zatrzymać. Gdy klikniesz Rozpocznij moduł, na liczniku wyświetli się informacja o tym, na jak długo udostępniamy Ci zasoby Google Cloud.

W tym praktycznym module możesz spróbować swoich sił w wykonywaniu opisywanych działań w prawdziwym środowisku chmury, a nie w jego symulacji lub wersji demonstracyjnej. Otrzymasz nowe, tymczasowe dane logowania, dzięki którym zalogujesz się i uzyskasz dostęp do Google Cloud na czas trwania modułu.

Do ukończenia modułu potrzebne będą:

• dostęp do standardowej przeglądarki internetowej (zalecamy korzystanie z przeglądarki Chrome).

Uwaga: uruchom ten moduł w oknie incognito lub przeglądania prywatnego. Dzięki temu unikniesz konfliktu między swoim kontem osobistym a kontem do nauki, co mogłoby spowodować naliczanie oddatkowych opłat na koncie osobistym.

• Odpowiednia ilość czasu na ukończenie modułu – pamiętaj, że gdy rozpoczniesz, nie możesz go wstrzymać.

Uwaga: jeśli masz już osobiste konto lub projekt w Google Cloud, nie używaj go w tym module, aby uniknąć naliczania opłat na koncie.

Rozpoczynanie modułu i logowanie się w konsoli Google Cloud

1. Kliknij przycisk Rozpocznij moduł. Jeśli moduł jest odpłatny, otworzy się wyskakujące okienko, w którym możesz wybrać formę płatności. Po lewej stronie znajduje się panel Szczegóły modułu z następującymi elementami:

   • przyciskiem Otwórz konsolę Google Cloud;
   • czasem, który Ci pozostał;
   • tymczasowymi danymi logowania, których musisz użyć w tym module;
   • innymi informacjami potrzebnymi do ukończenia modułu.

2. Kliknij Otwórz konsolę Google Cloud (lub kliknij prawym przyciskiem myszy i wybierz Otwórz link w oknie incognito, jeśli korzystasz z przeglądarki Chrome).

   Moduł uruchomi zasoby, po czym otworzy nową kartę ze stroną logowania.

   Wskazówka: otwórz karty obok siebie w osobnych oknach.

   Uwaga: jeśli pojawi się okno Wybierz konto, kliknij Użyj innego konta.

3. W razie potrzeby skopiuj nazwę użytkownika znajdującą się poniżej i wklej ją w oknie logowania.

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

   Nazwę użytkownika znajdziesz też w panelu Szczegóły modułu.

4. Kliknij Dalej.

5. Skopiuj podane niżej hasło i wklej je w oknie powitania.

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

   Hasło znajdziesz też w panelu Szczegóły modułu.

6. Kliknij Dalej.

   Ważne: musisz użyć danych logowania podanych w module. Nie używaj danych logowania na swoje konto Google Cloud. Uwaga: korzystanie z własnego konta Google Cloud w tym module może wiązać się z dodatkowymi opłatami.

7. Na kolejnych stronach wykonaj następujące czynności:

   • Zaakceptuj Warunki korzystania z usługi.
   • Nie dodawaj opcji odzyskiwania ani uwierzytelniania dwuskładnikowego (ponieważ konto ma charakter tymczasowy).
   • Nie rejestruj się w bezpłatnych wersjach próbnych.

Poczekaj, aż na karcie otworzy się konsola Google Cloud.

Uwaga: aby wyświetlić menu z listą produktów i usług Google Cloud, w lewym górnym rogu kliknij Menu nawigacyjne.

Zadanie 1. Przypnij projekt z modułu w BigQuery

W tej sekcji dodasz projekt data-to-insights do zasobów środowiska.

1. Kliknij Menu nawigacyjne > BigQuery.

Otworzy się okno „Witamy w usłudze BigQuery w konsoli Cloud”.

Uwaga: zawiera ono link do krótkiego wprowadzenia i informacji o aktualizacjach interfejsu.

2. Kliknij Gotowe.

Publiczne zbiory danych BigQuery nie są domyślnie wyświetlane w internetowym interfejsie BigQuery. Aby otworzyć projekt zawierający publiczne zbiory danych, skopiuj tekst „data-to-insights”.

3. Kliknij + Dodaj > Oznacz projekt według nazwy gwiazdką i jako nazwę projektu ustaw data-to-insights. Kliknij GWIAZDKĘ.

W sekcji Eksplorator pojawi się teraz projekt data-to-insights.

Zadanie 2. Przeanalizuj dane e-commerce i znajdź zduplikowane rekordy

Scenariusz: Twój zespół analityków danych wyeksportował do BigQuery logi Google Analytics dotyczące witryny e-commerce i utworzył nową tabelę obejmującą wszystkie nieprzetworzone dane sesji użytkowników tej witryny.

Przeanalizuj dane w tabeli all_sessions_raw:

1. Kliknij ikonę Rozwiń węzeł obok projektu data-to-insights, aby go rozwinąć.
2. Rozwiń ecommerce.
3. Kliknij all_sessions_raw.

W panelu po prawej stronie otworzy się sekcja z 3 widokami danych z tabeli. Będą to:

• karta Schemat: nazwa pola, typ, tryb i opis; ograniczenia logiczne używane do porządkowania danych;
• karta Szczegóły: metadane tabeli;
• karta Podgląd: podgląd tabeli.

4. Kliknij kartę Szczegóły, aby wyświetlić metadane tabeli.

Pytania:

Wyszukaj zduplikowane wiersze

Zapoznanie się z próbką danych może pomóc Ci zorientować się w zawartości zbioru danych.

1. Aby wyświetlić przykładowe wiersze z tabeli bez użycia SQL, kliknij kartę Podgląd.

2. Przewiń i przejrzyj wiersze. Nie ma tu jednego pola, które unikalnie identyfikuje wiersz, dlatego do znalezienia zduplikowanych wierszy potrzebujesz zaawansowanej logiki.

3. Użyjesz poniższego zapytania. Wykorzystuje ono funkcję SQL GROUP BY dla każdego pola i zlicza (COUNT) wiersze, w których we wszystkich polach powtarzają się jednakowe wartości:

• Jeśli każde pole jest unikalne, COUNT zwraca 1, ponieważ nie ma żadnych innych grupowań wierszy o takich samych wartościach we wszystkich polach.
• Jeśli jest więcej wierszy o takich samych wartościach we wszystkich polach, zostaną one zgrupowane, a wartość zwrócona przez funkcję COUNT będzie większa od 1.

Ostatnia część zapytania to filtr agregacji używający klauzuli HAVING, aby wyświetlić tylko te wyniki, dla których liczba duplikatów (COUNT) jest większa od 1. Dlatego liczba rekordów z duplikatami będzie taka sama jak liczba wierszy w powstałej tabeli.

4. Skopiuj poniższe zapytanie i wklej je do edytora, a potem je URUCHOM, aby określić, które rekordy są zduplikowane we wszystkich kolumnach.

#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;

Uwaga: nawet jeśli w Twoich zbiorach danych stosowany jest unikalny klucz, przed rozpoczęciem analizy warto potwierdzić unikalność wierszy za pomocą funkcji COUNT oraz klauzul GROUP BY i HAVING.

Kliknij Sprawdź postępy, aby zobaczyć, jak Ci poszło. Wyszukanie zduplikowanych wierszy

Przeanalizuj nową tabelę all_sessions

W tej sekcji będziesz używać zdeduplikowanej tabeli o nazwie all_sessions.

Scenariusz: Twój zespół analityków danych udostępnił Ci poniższe zapytanie, a eksperci w dziedzinie schematów wskazali kluczowe pola, które zgodnie z Twoim schematem muszą być unikalne dla każdego rekordu.

1. Uruchom zapytanie, aby potwierdzić brak duplikatów – tym razem w tabeli all_sessions:

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # ID unikalnego użytkownika strony visitId, # jeden użytkownik może odwiedzić stronę kilka razy date, # data sesji jako ciąg znaków YYYYMMDD time, # czas odwiedzin na stronie (może wynosić 0 dla wielu sesji na użytkownika) v2ProductName, # nie jest unikalny, ponieważ produkt może mieć warianty, np. koloru productSKU, # unikalny dla każdego produktu type, # użytkownik może odwiedzać strony i aktywować zdarzenia (nawet równocześnie) eCommerceAction_type, # mapuje się na ‘dodaj do koszyka', ‘zakup ukończony' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # przychód z zamówienia transactionId, # unikalny identyfikator przychodu z transakcji 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 # znajdź duplikaty

Zapytanie zwróci 0 rekordów.

Uwaga: w SQL możesz użyć klauzuli GROUP BY lub ORDER BY dla indeksu kolumny. Możesz więc napisać na przykład GROUP BY 1 zamiast GROUP BY fullVisitorId.

Zadanie 3. Napisz podstawowe zapytanie SQL dla danych e-commerce

W tej sekcji wykonasz zapytanie pozwalające wydobyć ze zbioru danych e-commerce określone statystyki.

Napisz zapytanie, które wyświetli łączną liczbę unikalnych użytkowników

Twoje zapytanie powinno zliczać łączną liczbę wyświetleń produktów (product_views) i liczbę unikalnych użytkowników (fullVisitorID).

1. Kliknij ikonę „+” (Utwórz nowe zapytanie).
2. Napisz w edytorze to zapytanie:

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

3. Aby mieć pewność, że składnia jest poprawna, sprawdź, czy działający w czasie rzeczywistym walidator zapytań pokazuje zieloną ikonę potwierdzenia.
4. Kliknij Uruchom. Przejrzyj wyniki, aby sprawdzić liczbę unikalnych użytkowników.

Wyniki:

5. Teraz napisz zapytanie, które wyświetli łączną liczbę unikalnych użytkowników (fullVisitorID) według witryny odsyłającej (channelGrouping):

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

Wyniki:

6. Napisz zapytanie, które wyświetli alfabetyczną listę wszystkich unikalnych nazw produktów (v2ProductName):

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

Wskazówka: w SQL klauzula ORDER BY domyślnie porządkuje wyniki rosnąco (ASC) od A do Z. Jeśli chcesz uzyskać odwrotną kolejność, użyj klauzuli ORDER BY nazwa_pola DESC.

Wyniki:

To zapytanie zwraca łącznie 633 produkty (wiersze).

7. Napisz zapytanie, które wyświetli listę 5 najczęściej wyświetlanych produktów (product_views) dla wszystkich użytkowników (w tym ludzi, który wyświetlili ten sam produkt więcej niż raz). Twoje zapytanie będzie zliczało, ile razy dany produkt (v2ProductName) został wyświetlony (product_views), uporządkuje listę malejąco i wyświetli 5 pierwszych wpisów:

Wskazówka: w Google Analytics użytkownik może „wyświetlić” produkt podczas następujących typów interakcji: „page” (strona), „screenview” (wyświetlenie ekranu), „event” (zdarzenie), „transaction” (transakcja), „item” (element), „social” (społecznościowe), „exception” (wyjątek), „timing” (czas). Na nasze potrzeby zastosuj tylko filtr dla typu „PAGE” (strona).

#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;

Wyniki:

8. Bonus: teraz doprecyzuj zapytanie tak, aby nie zliczało już wielokrotnie wyświetleń, w przypadku których jeden użytkownik wyświetlił określony produkt wiele razy. Wszystkie wyświetlenia produktu przez jednego użytkownika powinny być zliczane jako jedno wyświetlenie:

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

Wskazówka: aby podzielić złożone zapytanie na kilka kroków, możesz użyć klauzuli SQL WITH. Tutaj najpierw tworzymy zapytanie, które znajduje każdy unikalny produkt dla użytkownika i zlicza go jednokrotnie. Potem drugie zapytanie przeprowadza agregację dla wszystkich użytkowników i produktów.

Wyniki:

9. Następnie rozszerz poprzednie zapytanie tak, aby uwzględnić w nim łączną liczbę różnych zamówionych produktów i łączną liczbę zamówionych produktów (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;

Wyniki:

Pytania:

10. Rozszerz zapytanie tak, aby uwzględniało średnią liczbę produktów w zamówieniu (łączna liczba zamówionych sztuk / łączna liczba zamówień lub 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;

Wyniki

Pytanie:

Największą średnią liczbę produktów w zamówieniu (avg_per_order) wynoszącą 9,38 szt. na zamówienie odnotował produkt o nazwie „22 oz YouTube Bottle Infuser”.

Kliknij Sprawdź postępy, aby zobaczyć, jak Ci poszło. Napisanie podstawowego zapytania SQL dla danych e-commerce

Gratulacje!

Gratulacje! W tym module wykorzystaliśmy BigQuery, aby wyświetlić dane i wykonać na nich zapytania, a przez to uzyskać wgląd w różne aspekty związane z marketingiem produktów. Wiesz już, jak uzyskać dostęp do zbioru danych e-commerce, przeanalizować metadane zbioru danych, usunąć zduplikowane wpisy oraz napisać i uruchomić zapytania.

Kolejne kroki / Więcej informacji

• Dowiedz się więcej o publicznych zbiorach danych BigQuery.
• Masz konto Google Analytics i chcesz wysyłać zapytania do własnych zbiorów danych w BigQuery? Postępuj zgodnie ze wskazówkami zawartymi w przewodniku po eksportowaniu projektów.
• Dowiedz się 15 niesamowitych rzeczy o BigQuery (artykuł w języku angielskim).
• Aby dowiedzieć się więcej o BigQuery, zajrzyj do tych modułów:
  • Naprawianie częstych błędów SQL w BigQuery
  • Dane pogodowe w BigQuery

Szkolenia i certyfikaty Google Cloud

…pomogą Ci wykorzystać wszystkie możliwości technologii Google Cloud. Nasze zajęcia obejmują umiejętności techniczne oraz sprawdzone metody, które ułatwią Ci szybką naukę i umożliwią jej kontynuację. Oferujemy szkolenia na poziomach od podstawowego po zaawansowany prowadzone w trybach wirtualnym, na żądanie i na żywo, dzięki czemu możesz dopasować program szkoleń do swojego napiętego harmonogramu. Certyfikaty umożliwią udokumentowanie i potwierdzenie Twoich umiejętności oraz doświadczenia w zakresie technologii Google Cloud.

Ostatnia aktualizacja instrukcji: 2 kwietnia 2024 r.

Ostatni test modułu: 2 kwietnia 2024 r.

Copyright 2024 Google LLC. Wszelkie prawa zastrzeżone. Google i logo Google są znakami towarowymi Google LLC. Wszelkie inne nazwy firm i produktów mogą być znakami towarowymi odpowiednich podmiotów, z którymi są powiązane.