Poradnik korzystania z narzędzia BigQuery

Ta funkcja jest dostępna tylko w Analytics 360, części Google Marketing Platform.
Więcej informacji o Google Marketing Platform

W tym artykule podajemy przykłady formułowania zapytań operujących na danych Analytics wyeksportowanych do narzędzia BigQuery. Udostępniliśmy przykładowy zbiór danych, na którym możesz poćwiczyć zapytania opisane w tym artykule.

Tematy w tym artykule:

Optymalizacja zapytań

Każde wykonane zapytanie wlicza się do miesięcznego limitu przetwarzania danych. Jeśli wybierasz nieistotne pola, zwiększasz liczbę danych, które muszą być przetworzone, i w efekcie zużywasz więcej limitu miesięcznego niż to konieczne. Zoptymalizowane zapytania umożliwiają efektywne wykorzystanie miesięcznego limitu przetwarzania danych.

Więcej informacji o cenach.

Wybieranie tylko potrzebnych pól

Formułując zapytanie, wybieraj tylko ważne pola w ramach wyrażenia SELECT. Pomijanie nieistotnych pól ogranicza ilość danych i czas potrzebny na przetworzenie zapytania.

Przykład: unikaj używania operatora wieloznacznego

Nieprawidłowa forma: korzystanie z operatora wieloznacznego
SELECT *
FROM [nazwa tabeli];

 

Lepsza forma: korzystanie z nazw pól, by uniknąć niepotrzebnego przetwarzania
SELECT field1, field2
FROM [nazwa tabeli];

Zezwolenie na buforowanie

Jeśli to możliwe, unikaj używania funkcji jako pól. Funkcje (np. NOW() i TODAY()) zwracają wyniki zmiennych, które uniemożliwiają buforowanie zapytań i ich szybszy zwrot. Zamiast tego użyj określonych godzin i dat.

Obecnie wyniki z pamięci podręcznej nie są obsługiwane w przypadku wysyłania zapytań do wielu tabel przy użyciu symboli wieloznacznych. Jest tak nawet po zaznaczeniu pola wyboru „Użyj wyników z pamięci podręcznej”. Jeśli będziesz wielokrotnie wysyłać to samo zapytanie z symbolami wieloznacznymi, poniesiesz opłatę za każde jego wysłanie. Więcej informacji

Stosowanie zaawansowanych tabel do często używanych podzapytań

Jeśli zauważysz, że często używasz określonego zapytania jako podzapytania, możesz je zapisać w zaawansowanej tabeli, klikając Save as Table (Zapisz jako tabelę) nad wynikami zapytania. Możesz następnie odwoływać się do tej tabeli w sekcji FROM zapytania, co ograniczy ilość danych do przetworzenia i czas całej operacji.

korzystanie z zaawansowanej tabeli
SELECT field1, field2
FROM [Nazwa zbioru danych.nazwa tabeli];

Debugowanie zapytań

Narzędzie BigQuery debuguje kod podczas jego tworzenia. W oknie tworzenia zapytania wynik debugowania jest widoczny poniżej zapytania. Debugowanie jest też dostępne poprzez interfejs API za pomocą flagi dryRun.

Prawidłowe zapytania mają zielony wskaźnik, który po kliknięciu wyświetla liczbę danych przetwarzanych przez zapytanie. Ta funkcja umożliwia optymalizację danych przed uruchomieniem zapytania, by uniknąć niepotrzebnego przetwarzania danych.

Query Debugging - Success

 

Nieprawidłowe zapytania mają czerwony wskaźnik, który po kliknięciu wyświetla informacje o błędzie oraz wskazuje wiersz i kolumnę z błędem. W poniższym przykładzie wyrażenie GROUP BY jest puste, a błąd jest zaznaczony.

Query Debugging - Error

 

Porady i sprawdzone metody

Korzystanie z próbnego zbioru danych

W poniższych przykładach korzystamy z próbnego zbioru danych Google Analytics.

Aby użyć tych zapytań w odniesieniu do własnych danych, wystarczy zmienić nazwy projektów i zbiorów danych w przykładach na własne.

Korzystanie ze standardowej wersji SQL w porównaniu z korzystaniem ze starszej wersji SQL

BigQuery obsługuje dwie wersje SQL:

W artykule Przejście na standardową wersję SQL (w języku angielskim) wyjaśniamy różnice między tymi dwiema wersjami.

Standardowa wersja SQL jest obecnie preferowaną wersją SQL do obsługi zapytań na danych przechowywanych w BigQuery.

Z artykułu Jak włączyć standardową wersję SQL (w języku angielskim) dowiesz się, jak uruchomić tę wersję w interfejsie użytkownika BigQuery, interfejsie wiersza poleceń, interfejsie API lub dowolnym innym interfejsie, z którego korzystasz.

Najłatwiej jest zacząć od umieszczenia komentarza „standardSQL” nad zapytaniami w standardowej wersji SQL – tak jak w poniższych przykładach.

W przypadku starszej wersji SQL dane Google Analytics 360 są przesyłane codziennie do nowej tabeli. Aby wysłać zapytanie do wielu tabel, możesz rozdzielić przecinkami ich nazwy, skorzystać z funkcji symboli wieloznacznych dla tabel TABLE_DATE_RANGE lub użyć wielu funkcji TABLE_DATE_RANGE rozdzielanych przecinkami jak w poniższych przykładach.

Wysyłanie zapytań do wielu tabel

W poniższych przykładach pokazujemy, jak wyglądają zapytania w standardowej wersji SQL i w starszej wersji SQL dotyczące tych samych danych.

3 dni

Standardowa wersja SQL
3 dni z zastosowaniem operatora „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
Starsza wersja SQL
3 dni z zastosowaniem nazw tabel rozdzielanych przecinkami
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

 

Ostatnie 1095 dni

Standardowa wersja SQL
Ostatnie 1095 dni z zastosowaniem funkcji _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
Starsza wersja SQL
Ostatnie 1095 dni z zastosowaniem funkcji 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

 

Ostatnie 36 miesięcy

Standardowa wersja SQL
Ostatnie 36 miesięcy z zastosowaniem funkcji _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
Starsza wersja SQL
Ostatnie 36 miesięcy z zastosowaniem funkcji 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

 

Ostatnie 3 lata

Standardowa wersja SQL
Ostatnie 3 lata z zastosowaniem funkcji _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
Starsza wersja SQL
Ostatnie 3 lata z zastosowaniem funkcji 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

 

Konkretny zakres dat

Standardowa wersja SQL
Konkretny zakres dat z zastosowaniem funkcji _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
Starsza wersja SQL
Konkretny zakres dat z zastosowaniem funkcji 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

Ostatnie 3 lata plus dzisiejsze dane (w ciągu dnia)

Standardowa wersja SQL
Ostatnie 3 lata plus dzisiejsze dane (w ciągu dnia) z zastosowaniem operatora „UNION ALL” i funkcji _TABLE_SUFFIX
Uwaga: to przykładowe zapytanie nie działa z publicznym zestawem danych Google Analytics, ponieważ obecnie nie ma tabeli danych częściowych.
#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

Starsza wersja SQL

Ostatnie 3 lata plus dzisiejsze dane (w ciągu dnia) z zastosowaniem wielu funkcji TABLE_DATE_RANGE
Uwaga: to przykładowe zapytanie nie działa z publicznym zestawem danych Google Analytics, ponieważ obecnie nie ma tabeli danych częściowych.
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

 

 

Przykłady podstawowych zapytań

W tej sekcji pokazujemy, jak tworzyć podstawowe zapytania na podstawie danych i wymiarów z przykładowych danych Analytics.

Łącznie [dane] na [wymiar]?

Poniżej znajdziesz przykładowe skrypty dla zapytania: Jaka jest łączna liczba transakcji wygenerowanych na przeglądarkę urządzenia w lipcu 2017 r.?

Standardowa wersja SQL

Łączna liczba transakcji wygenerowanych na przeglądarkę urządzenia w lipcu 2017 r.
#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

Starsza wersja SQL

Łączna liczba transakcji wygenerowanych na przeglądarkę urządzenia w lipcu 2017 r.
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

 

 

Średni współczynnik odrzuceń na [wymiar]?

Rzeczywisty współczynnik odrzuceń jest określony jako odsetek wizyt z pojedynczą odsłoną. Poniżej znajdują się przykładowe skrypty dla zapytania: Jaki był rzeczywisty współczynnik odrzuceń na źródło wizyt?

Standardowa wersja SQL

Współczynnik odrzuceń na źródło wizyt w lipcu 2017 r.
#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

Starsza wersja SQL

Współczynnik odrzuceń na źródło wizyt w lipcu 2017 r.
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

 

 

Średnia liczba odsłon strony produktu według typu kupującego (kupujący a użytkownicy, którzy niczego nie kupili)

Poniżej znajdziesz przykładowe skrypty dla zapytania: Jaka jest średnia liczba odsłon strony produktu w przypadku użytkowników, którzy dokonali zakupu w lipcu 2017 r.?

Standardowa wersja SQL

Średnia liczba odsłon strony produktu w przypadku użytkowników, którzy dokonali zakupu w lipcu 2017 r.
#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 )

Starsza wersja SQL

Średnia liczba odsłon strony produktu w przypadku użytkowników, którzy dokonali zakupu w lipcu 2017 r.
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 )

 

 

Poniżej znajdziesz przykładowe skrypty dla zapytania: Jaka jest średnia liczba odsłon strony produktu w przypadku użytkowników, którzy nie dokonali zakupu w lipcu 2017 r.?

Standardowa wersja SQL

Średnia liczba odsłon strony produktu w przypadku użytkowników, którzy nie dokonali zakupu w lipcu 2017 r.
#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 )

Starsza wersja SQL

Średnia liczba odsłon strony produktu w przypadku użytkowników, którzy nie dokonali zakupu w lipcu 2017 r.
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 )

 

 

Średnia liczba transakcji na kupującego

Poniżej znajdziesz przykładowe skrypty dla zapytania: Jaka jest średnia łączna liczba transakcji na użytkownika, który dokonał zakupu w lipcu 2017 r.?

Standardowa wersja SQL

Średnia łączna liczba transakcji na użytkownika, który dokonał zakupu w lipcu 2017 r.
#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 )

Starsza wersja SQL

Średnia łączna liczba transakcji na użytkownika, który dokonał zakupu w lipcu 2017 r.
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 )

 

 

Średnia kwota wydana podczas sesji

Poniżej znajdziesz przykładowe skrypty dla zapytania: Jaka jest średnia kwota wydana podczas sesji w lipcu 2017 r.?

Standardowa wersja SQL

Średnia kwota wydana podczas sesji w lipcu 2017 r.
#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 )

Starsza wersja SQL

Średnia kwota wydana podczas sesji w lipcu 2017 r.
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 )

 

 

Kolejność działań

Poniżej znajdziesz przykładowe skrypty dla zapytania: Jaka jest kolejność wyświetlanych stron?.

Standardowa wersja SQL

Kolejność stron wyświetlanych przez użytkowników w lipcu 2017 r.
#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

Starsza wersja SQL

Kolejność stron wyświetlanych przez użytkowników w lipcu 2017 r.
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

W tym zapytaniu ograniczasz typ działań do stron (PAGE), by uniknąć wyświetlania innych interakcji, np. zdarzeń czy transakcji. Każdy wiersz wyniku odpowiada odsłonie, a jego zawartość wyświetla się w domyślnej kolejności pól zastosowanej w wyrażeniu SELECT.

 

 

Wiele wymiarów niestandardowych na poziomie działania lub sesji

wymiar niestandardowy na poziomie działania
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

 

wymiar niestandardowy na poziomie sesji
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

W każdym zapytaniu:

Zapytania o wyrażenie SELECT dla pól odpowiedniego wymiaru i danych

Funkcja MAX:

  • Wyświetla wymiar niestandardowy w nowej kolumnie. Możesz powtarzać tę funkcję, by wyświetlać wiele wymiarów niestandardowych w nowych kolumnach.
  • Funkcje WITHIN hits i WITHIN RECORD oceniają warunek wewnątrz pól powtarzanych w BigQuery.
  • Stan wewnątrz MAX jest oceniany dla każdego wymiaru niestandardowego, ale w przypadku wymiarów innych niż index=1 (działania) lub index=2 (sesje) wyświetla NULL.
  • Wyświetla maksymalną wartość, która jest wartością wymiaru niestandardowego 1 dla działań lub wymiaru niestandardowego 2 dla sesji, gdyż wszystkie inne wartości są NULL.

Przykłady zaawansowanych zapytań

Po zapoznaniu się z prostymi zapytaniami możesz zacząć tworzyć zapytania za pomocą zaawansowanych funkcji dostępnych w narzędziu BigQuery.

Produkty kupione przez klientów, którzy nabyli produkt A (klasyczny e-commerce)

Poniżej podajemy szkielet skryptu zapytania: Jakie inne produkty kupują klienci, którzy nabyli produkt A?

produkty kupione przez klienta, który nabył produkt A (klasyczny e-commerce)
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. W pierwszym wierszu wybierasz wszystkie inne produkty kupione przez użytkownika, a funkcja zbiorcza COUNT() służy do liczenia innych nabytych produktów. Następnie wynik wyświetla się w polu oznaczonym jako quantity z powiązanym produktem w polu oznaczonym jako other_purchased_products.
  2. W podzapytaniu w kolorze szarym wybierasz tylko unikalnych użytkowników (fullVisitorId), którzy zrealizowali transakcję (totals.transactions>=1) i kupili produkt A (WHERE hits.item.productName CONTAINS nazwa produktu A).

Reguły (wyrażenia WHERE i AND) w głównym zapytaniu (w kolorze zielonym) odrzucają wartości z pola hits.item.productName, które są puste lub zawierają produkt A.

Oto przykład zapytania: Jeśli klient kupił zestaw 4 metalowych długopisów, jakie inne produkty kupił?

produkty kupione przez klienta, który nabył metalowe długopisy (Brighton Metallic Pens (Set of 4)) w dniu 24 czerwca 2013 r.
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;

W narzędziu Dremel/BigQuery korzystanie z wyrażenia WHERE expr IN powoduje wywołanie funkcji JOIN i stosowanie ograniczeń rozmiaru. Rozmiar po prawej stronie polecenia JOIN (w tym przypadku jest to liczba użytkowników) nie może przekraczać 8 MB. W narzędziu Dremel funkcja z takim ograniczeniem nosi nazwę JOIN rozgłoszeniowego. Jeśli rozmiar przekracza 8 MB, musisz wywołać funkcję JOIN z przetasowaniem, korzystając ze składni JOIN EACH. Niestety nie można tego zrobić za pomocą wyrażenia IN, ale możesz przepisać to samo zapytanie, używając w jego miejsce polecenia JOIN.

Produkty kupione przez klientów, którzy nabyli produkt A (ulepszone e-commerce)

Jest to przykład podobny do poprzedniego szkieletu zapytania, tyle że działa w przypadku ulepszonego e-commerce. Również korzysta się tu z funkcji TABLE_DATE_RANGE do objęcia zapytaniem danych z wielu dni.

produkty kupione przez klienta, który nabył produkt A (ulepszony e-commerce)
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;

Średnia liczba interakcji użytkownika przed zakupem

To jest przykład zapytania polecenia JOIN() [...] ON, które zależy tylko od danych Google Analytics.

Poniżej podajemy szkielet skryptu zapytania: Jaka jest średnia liczba interakcji użytkownika przed zakupem?

liczba interakcji użytkownika przed zakupem
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. W pierwszym wierszu wykonywane jest główne działanie matematyczne, które znajduje średnią liczbę interakcji użytkownika przypadających na produkt. W tym zapytaniu utworzyliśmy połączenie pomiędzy dwoma podzapytaniami o nazwach „Alias_Name_1” i „Alias_Name_2”.
  2. Podzapytanie „Alias_Name_1” służy do utworzenia pola, które korzysta z funkcji zbiorczej SUM() do zsumowania wszystkich działań zarejestrowanych w przypadku danego produktu.
  3. Podzapytanie „Alias_Name_2” służy do ustalenia liczby działań użytkowników za pomocą funkcji COUNT().
  4. Ostatni wiersz wyświetla wspólne pole (hits.item.productSku) udostępniane dwóm połączonym zbiorom danych.

Oto przykład zapytania: Jaka jest średnia liczba interakcji użytkownika w dniu 10 września 2013 r. przed zakupem?

liczba interakcji użytkownika w dniu 10 września 2013 r. przed zakupem
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;

Odsetek sprzedanych zasobów na produkt

To jest przykład zapytania, które zależy nie tylko od danych Analytics, ale również od danych spoza tej usługi. Dzięki połączeniu obu zbiorów danych możesz analizować zachowania użytkowników na znacznie bardziej szczegółowym poziomie. Możesz wprawdzie importować do narzędzia BigQuery dane spoza Analytics, ale pamiętaj, że będzie to wpływać na miesięczną opłatę za przechowywanie danych.

Poniżej przedstawiamy szkielet skryptu zapytania: Jaki odsetek zasobu danego produktu został sprzedany?

odsetek sprzedanych zasobów na produkt
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. Jedno pole zawiera wszystkie identyfikatory produktów, a drugie to działanie matematyczne, które wskaże odsetek sprzedanych zasobów w przypadku danego identyfikatora produktu.
  2. To zapytanie zależy od 2 zbiorów danych, więc musisz użyć funkcji JOIN() ... Polecenie to łączy wiersze z 2 zbiorów danych na podstawie pola wspólnego dla nich obu. W tym przypadku chodzi o zbiory [importowany_zbiór_danych] i Alias_Name.
  3. [Importowany_zbiór_danych] zawiera dane spoza Analytics. Znajduje się w nim pole danych informujące o pozostałych zasobach (Imported DataSet.’stock_left_field’) i pole wymiaru identyfikatora produktu (Imported_DataSet.’productId_field’).
  4. Alias_Name to nazwa przypisana danym zwracanym przez podzapytanie w kolorze szarym. To podzapytanie korzysta z danych Analytics do określania łącznej liczby sprzedanych sztuk danego produktu.
  5. W ostatnim wierszu znajduje się wyrażenie ON, które wskazuje pole wspólne dla obu zbiorów danych, będące miejscem ich połączenia.

Wiele zmiennych w tym zapytaniu ma nazwę zbioru danych dołączoną jako prefiks (np. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Służy to wskazaniu, które pole wybierasz i do którego zbioru danych ono należy.

Poniżej przedstawiamy przykład zapytania: Jaki odsetek zasobu danego produktu został sprzedany w dniu 28 lipca 2013 r.?

odsetek sprzedanych zasobów na produkt 28 lipca 2013 r.
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;

Dochodowość poszczególnych produktów

Poniżej przedstawiamy szkielet skryptu zapytania: Jaka jest dochodowość poszczególnych produktów?

zysk z produktu
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. W pierwszym wierszu znajduje się działanie matematyczne do obliczania łącznego zysku dla poszczególnych produktów.
  2. Zapytanie podrzędne w kolorze szarym korzysta z danych spoza Analytics, które informują o zysku ze sprzedanego produktu.
  3. Zapytanie podrzędne w kolorze czerwonym dotyczy danych Analytics, a wyniki jego działania zostaną połączone z danymi spoza Analytics. Oblicza ono liczbę sprzedanych sztuk poszczególnych produktów.
  4. W ostatnim wierszu znajduje się wyrażenie ON, które wskazuje pole wspólne dla obu zbiorów danych. W tym przypadku jest to liczba identyfikatorów produktu.

Poniżej przedstawiamy przykład zapytania: Jaka była dochodowość poszczególnych produktów w dniu 28 lipca 2013 r.?

zysk z produktu w dniu 28 lipca 2013 r.
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 );

Zysk oblicza się przez ustalenie różnicy między ceną sprzedaży produktu a kosztem jego wytworzenia. Ta informacja jest przechowywana w zbiorze danych spoza Google Analytics.

Rzeczywista dochodowość poszczególnych produktów (z uwzględnieniem zwrotów)

Poniżej przedstawiamy szkielet skryptu zapytania: Jaka jest faktyczna dochodowość poszczególnych produktów?

rzeczywisty zysk z produktu
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. To zapytanie jest podobne do zapytania Jaka jest dochodowość poszczególnych produktów? Jedyne różnice są związane ze zbiorem danych spoza Analytics w zapytaniu podrzędnym w kolorze szarym i operacją matematyczną w pierwszym wierszu, która służy do obliczania faktycznego zysku.
  2. W przypadku zbioru danych spoza Analytics obliczasz dodatkowo łączną kwotę wydaną na zwroty (w wyrażeniu SELECT w zapytaniu podrzędnym w kolorze czerwonym).
  3. Aby następnie obliczyć faktyczny zysk, przeprowadzasz w pierwszym wierszu działanie matematyczne polegające na odjęciu od zysku brutto kwoty wydanej na zwroty.

Więcej informacji o tym zapytaniu znajdziesz w sekcji o dochodowości poszczególnych produktów.

Poniżej przedstawiamy przykład zapytania: Jaka była faktyczna dochodowość poszczególnych produktów w dniu 28 lipca 2013 r.?

faktyczny zysk z produktu w dniu 28 lipca 2013 r.
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 ) );

Faktyczny zysk podaje dochodowość produktu po uwzględnieniu zwróconych sztuk. Aby obliczyć łączne przychody ze zwrotu w przypadku danego produktu:

łączna kwota za zwroty produktów = (cena produktu + zwrot kosztów dostarczenia zwracanego produktu) * liczba zwróconych produktów

Czy to było pomocne?

Jak możemy ją poprawić?

Potrzebujesz dodatkowej pomocy?

Wykonaj te czynności:

Zadaj pytanie na forum pomocy Uzyskaj odpowiedzi od członków społeczności
Kontakt z nami Przekaż więcej informacji, byśmy mogli Ci pomóc
true
learning path
Wybierz swoją ścieżkę szkoleniową

Odwiedź google.com/analytics/learn – nowe źródło materiałów, które pomogą Ci w pełni wykorzystać potencjał Google Analytics 4. Ta nowa witryna zawiera filmy, artykuły i przewodniki, a także linki do zasobów dotyczących Google Analytics, takich jak kanały na Discordzie i w YouTube, blog i repozytorium GitHub.

Już dziś zacznij pogłębiać swoją wiedzę

Szukaj
Wyczyść wyszukiwanie
Zamknij wyszukiwanie
Menu główne
2513749362533224078
true
Wyszukaj w Centrum pomocy
true
true
true
true
true
69256
false
false