Această funcție este disponibilă doar în Google Analytics 360, care face parte din Google Marketing Platform. Aflați mai multe despre Google Marketing Platform. |
Acest articol conține exemple de construire a interogărilor pentru datele Analytics pe care le exportați în BigQuery. Am creat un exemplu de set de date pe care puteți exersa câteva dintre interogările de aici.
În acest articol:- Optimizarea interogărilor
- Sfaturi și cele mai bune practici
- Exemple de interogări simple
- [Valoare] totală pe [parametru]
- Rata medie de respingere pe [parametru]
- Numărul mediu de vizualizări de pagină de produs în funcție de tipul cumpărătorului
- Numărul mediu de tranzacții pe cumpărător
- Suma medie de bani cheltuită pe sesiune
- Succesiunea accesărilor (analiza căilor)
- Parametrii personalizați multipli la nivel de accesare sau sesiune
- Exemple de interogări avansate
- Produsele achiziționate de clienții care au achiziționat Produsul A (comerț electronic clasic)
- Produsele achiziționate de clienții care au achiziționat Produsul A (comerț electronic optimizat)
- Numărul mediu de interacțiuni ale utilizatorilor înainte de achiziție
- Procentul vândut din stoc pentru fiecare produs
- Profitabilitatea fiecărui produs
- Profitabilitatea reală a fiecărui produs
Optimizarea interogărilor
Fiecare interogare executată consumă o parte din volumul lunar de procesare a datelor. Dacă selectați câmpuri neesențiale, cantitatea datelor de procesat va crește și, prin urmare, veți utiliza un procent mai mare din volumul lunar de procesare a datelor. Interogările optimizate vor folosi mai eficient volumul lunar de procesare a datelor de care dispuneți.
Aflați mai multe despre prețuri.
Selectați numai elementele de care aveți nevoie
Când formulați o interogare, selectați câmpurile relevante cu ajutorul funcției SELECT. Evitați selectarea unor câmpuri neesențiale pentru a reduce volumul datelor procesate și timpul necesar pentru procesarea interogării.
Exemplu: evitați utilizarea operatorului metacaracter
Formă incorectă: utilizează operatorul metacaracter |
---|
SELECT * |
Formă mai eficientă: utilizați numele câmpurilor pentru a evita procesarea inutilă a datelor |
---|
SELECT câmp1, câmp2 |
Permiteți stocarea în memoria cache
Atunci când este posibil, nu folosiți funcții drept câmpuri. Funcțiile (cum ar fi NOW()
(acum) sau TODAY()
(astăzi)) generează rezultate variabile, ceea ce previne stocarea interogărilor în memoria cache și obținerea mai rapidă a rezultatelor. Folosiți ore și date precise.
Folosiți tabele intermediare pentru interogările secundare folosite frecvent
Dacă utilizați frecvent o anumită interogare ca interogare secundară, o puteți salva sub forma unui tabel intermediar dând clic pe Salvați ca tabel deasupra rezultatelor interogării. Apoi, veți putea specifica tabelul respectiv în secțiunea FROM
(Din) a interogării dvs., ceea ce va reduce atât cantitatea datelor de procesat, cât și timpul necesar pentru procesare.
Folosirea unui tabel intermediar |
---|
SELECT câmp1, câmp2 |
Remedierea erorilor legate de interogări
BigQuery remediază erorile din cod pe măsură ce îl scrieți. În fereastra de creare, depanarea este indicată chiar sub interogare. Depanarea este, de asemenea, disponibilă prin intermediul API cu marcajul dryRun.
Interogările valide au un indicator verde care puteți da clic pentru a vedea cantitatea de date procesată de către interogare. Această caracteristică vă oferă posibilitatea de a optimiza datele înainte de a executa interogarea și de a evita astfel procesarea inutilă a datelor.
Interogările nevalide au un indicator roșu pe care puteți da clic pentru a vedea informații privind eroarea și pentru a găsi rândul și coloana pe care se află eroarea. În exemplul de mai jos, declarația GROUP BY (Grupare după) este necompletată, iar eroarea este evidențiată.
Sfaturi și cele mai bune practici
Folosirea exemplului de set de date
Următoarele exemple folosesc setul de date eșantion Google Analytics.
Pentru a folosi interogările cu propriile date, înlocuiți numele proiectului și al setului de date din exemple cu propriile nume pentru proiect și setul de date.
Folosirea SQL standard vs. SQL vechi
BigQuery acceptă două dialecte SQL:
Migrarea la SQL standard explică diferențele dintre cele două dialecte.
SQL standard este acum dialectul SQL preferat pentru interogarea datelor stocate în BigQuery.
Consultați Activarea SQL standard pentru informații despre activarea SQL standard în IU, CLI, API BigQuery sau în orice interfață folosită.
Cea mai ușoară modalitate de a începe este să includeți comentariul „standardSQL” în partea de sus a interogărilor SQL standard, așa cum vedeți în exemplele următoare.
Cu SQL vechi, datele din Google Analytics 360 sunt transmise într-un nou tabel în fiecare zi. Pentru a interoga mai multe tabele simultan, puteți să separați prin virgule numele acestora ori să folosiți funcția de metacaracter pentru tabele TABLE_DATE_RANGE
sau mai multe funcții TABLE_DATE_RANGE
separate prin virgulă, ca în exemplele următoare.
Interogarea mai multor tabele
Următoarele exemple vă prezintă interogările SQL standard și SQL vechi pentru aceleași date.
3 zile
SQL standard
3 zile, folosind UNION ALL |
---|
#standardSQL |
SQL vechi
3 zile, folosind nume de tabele separate prin virgulă |
---|
SELECT |
Ultimele 1095 de zile
SQL standard
Ultimele 1095 de zile, folosind _TABLE_SUFFIX |
---|
#standardSQL |
SQL vechi
Ultimele 1095 de zile, folosind TABLE_DATE_RANGE |
---|
SELECT |
Ultimele 36 de luni
SQL standard
Ultimele 36 de luni, folosind _TABLE_SUFFIX |
---|
#standardSQL |
SQL vechi
Ultimele 36 de luni, folosind TABLE_DATE_RANGE |
---|
SELECT |
Ultimii 3 ani
SQL standard
Ultimii trei ani, folosind _TABLE_SUFFIX |
---|
#standardSQL |
SQL vechi
Ultimii 3 ani, folosind TABLE_DATE_RANGE |
---|
SELECT |
Un anumit interval de date
SQL standard
Un anumit interval de date, folosind _TABLE_SUFFIX |
---|
#standardSQL |
SQL vechi
Un anumit interval de date, folosind TABLE_DATE_RANGE |
---|
SELECT |
Ultimii 3 ani, plus datele de astăzi (din cursul zilei)
SQL standard
Ultimii 3 ani, plus datele de astăzi (din cursul zilei), folosind UNION ALL & _TABLE_SUFFIX |
---|
Notă: acest exemplu de interogare nu va funcționa cu setul de date public Google Analytics, deoarece nu există un tabel din cursul zilei. |
#standardSQL |
SQL vechi
Ultimii 3 ani, plus datele de astăzi (din cursul zilei), folosind mai multe TABLE_DATE_RANGE |
---|
Notă: acest exemplu de interogare nu va funcționa cu setul de date public Google Analytics, deoarece nu există un tabel din cursul zilei. |
SELECT |
Exemple de interogări simple
Această secțiune explică modul de construire a unor interogări simple folosind valori și parametri din eșantionul de date din Google Analytics.
[Valoare] totală pe [parametru]?
Mai jos sunt exemple de scripturi pentru întrebarea: Care este numărul total de tranzacții generate pe fiecare browser de dispozitiv în iulie 2017?
SQL standard
Numărul total de tranzacții pe fiecare browser de dispozitiv în iulie 2017 |
---|
#standardSQL |
SQL vechi
Numărul total de tranzacții pe fiecare browser de dispozitiv în iulie 2017 |
---|
SELECT |
Rata de respingere medie pe [parametru]?
Rata de respingere reală este definită ca procent al vizitelor cu o singură vizualizare de pagină. Mai jos sunt exemple de scripturi pentru întrebarea: Care a fost rata reală de respingere pe fiecare sursă de trafic?
SQL standard
Rata de respingere pentru fiecare sursă de trafic în iulie 2017 |
---|
#standardSQL |
SQL vechi
Rata de respingere pentru fiecare sursă de trafic în iulie 2017 |
---|
SELECT |
Numărul mediu de vizualizări de pagină de produs în funcție de tipul de cumpărător (cumpărători în comparație cu non-cumpărători)
Mai jos sunt exemple de scripturi pentru întrebarea: Care a fost numărul mediu de vizualizări de pagină de produs pentru utilizatorii care au făcut o achiziție în iulie 2017?
SQL standard
Numărul mediu de vizualizări de pagină de produs pentru utilizatorii care au făcut o achiziție în iulie 2017 |
---|
#standardSQL |
SQL vechi
Numărul mediu de vizualizări de pagină de produs pentru utilizatorii care au făcut o achiziție în iulie 2017 |
---|
SELECT |
Mai jos sunt exemplele de scripturi pentru întrebarea: Care a fost numărul mediu al vizualizărilor de pagină de produs pentru utilizatorii care nu au făcut o achiziție în iulie 2017?
SQL standard
Numărul mediu al vizualizărilor de pagină de produs pentru utilizatorii care nu au făcut o achiziție în iulie 2017 |
---|
#standardSQL |
SQL vechi
Numărul mediu al vizualizărilor de pagină de produs pentru utilizatorii care nu au făcut o achiziție în iulie 2017 |
---|
SELECT |
Numărul mediu de tranzacții pe cumpărător
Mai jos sunt exemple de scripturi pentru întrebarea: Care a fost suma totală a tranzacțiilor pe utilizator care a făcut o achiziție în iulie 2017?
SQL standard
Numărul mediu de tranzacții pe utilizator care a făcut o achiziție în iulie 2017 |
---|
#standardSQL |
SQL vechi
Numărul mediu de tranzacții pe utilizator care a făcut o achiziție în iulie 2017 |
---|
SELECT |
Suma medie de bani cheltuită pe sesiune
Mai jos sunt exemple de scripturi pentru întrebarea: Care este suma medie de bani cheltuită pe sesiune în iulie 2017?
SQL standard
Suma medie cheltuită pe sesiune în iulie 2017 |
---|
#standardSQL |
SQL vechi
Suma medie cheltuită pe sesiune în iulie 2017 |
---|
SELECT |
Succesiunea accesărilor
Mai jos sunt exemple de scripturi pentru întrebarea: Care este secvența paginilor vizualizate?
SQL standard
Secvența paginilor vizualizate de utilizatori în iulie 2017 |
---|
#standardSQL |
SQL vechi
Secvența paginilor vizualizate de utilizatori în iulie 2017 |
---|
SELECT |
În această interogare, limitați tipurile de accesări la cele de tip PAGE
(Pagină) pentru a evita afișarea interacțiunilor de tip eveniment sau tranzacție. Fiecare rând al rezultatului reprezintă o vizualizare de pagină și este determinat de ordinea standard a câmpurilor din propoziția SELECT
(Selectare)
Parametrii personalizați multipli la nivel de accesare sau sesiune
parametru personalizat la nivel de accesare |
---|
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time, |
parametru personalizat la nivel de sesiune |
---|
SELECT fullVisitorId, visitId, |
În fiecare interogare:
Propoziția SELECT
(Selectare) trimite o interogare pentru câmpurile relevante cu parametri și valori.
Funcția MAX
:
- returnează parametrul personalizat sub formă de coloană nouă. Puteți repeta funcția în așa fel încât să returneze mai mulți parametri personalizați sub formă de coloane noi.
- funcțiile
WITHIN hits
șiWITHIN RECORD
evaluează condiția din câmpurile repetate din BigQuery; - condiția din
MAX
este evaluată pentru fiecare parametru personalizat, dar returneazăNULL
pentru parametrii care nu suntindex=1 (hits)
orindex=2 (sessions)
; - returnează valoarea maximă, care este valoarea Parametrului personalizat 1 pentru accesări sau a Parametrului personalizat 2 pentru sesiuni întrucât toate celelalte valori sunt
NULL
.
Exemple de interogări avansate
După ce v-ați familiarizat cu interogările simple, puteți crea interogări folosind funcțiile și caracteristicile avansate din BigQuery.
Produsele achiziționate de clienții care au achiziționat Produsul A (comerț electronic clasic)
Puteți vedea mai jos o schemă de script pentru întrebarea: Ce alte produse sunt achiziționate de clienții care achiziționează produsul A?
produsele achiziționate de un client care achiziționează produsul A (comerț electronic clasic) |
---|
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity |
- Pe primul rând, selectați toate celelalte articole achiziționate de un utilizator; funcția agregată
COUNT()
se utilizează pentru calcularea cantității din fiecare produs achiziționat. Rezultatul este afișat într-un câmp marcatquantity
, iar articolul asociat din câmpul produsului va avea etichetaother_purchased_products
. - În interogarea secundară afișată cu gri, selectați numai utilizatorii unici (
fullVisitorId
) care au efectuat tranzacții (totals.transactions>=1
), precum și, în timpul unei tranzacții, au achiziționat produsul A (WHERE hits.item.productName CONTAINS 'Nume produs A'
).
Regulile (propozițiile WHERE
și AND
) din interogarea de la nivelul superior (de culoare verde) ignoră valorile de la hits.item.productName
care sunt nule și care conțin produsul A.
Iată un exemplu pentru interogarea: Dacă un client achiziționează Pixuri metalice Brighton – set de 4, ce alt(e) produs(e) a cumpărat?
produsele achiziționate de un client care achiziționează „Pixuri metalice Brighton (set de 4)” pe 24 iunie 2013 |
---|
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity |
În Dremel/BigQuery, utilizarea WHERE expr IN
declanșează acțiunea JOIN și aplică restricții de dimensiune, în special pentru dimensiunea părții din dreapta a acțiunii JOIN (în acest caz, numărul de vizitatori), care trebuie să fie mai mică de 8 MB. În Dremel, această acțiune este numită broadcast JOIN. Când dimensiunea depășește 8 MB, este necesar să declanșați o acțiune shuffled JOIN, folosind sintaxa JOIN EACH. Din păcate, aceasta nu poate fi realizată cu operatorul IN, însă aceeași interogare poate fi rescrisă cu ajutorul unei funcții JOIN.
Produsele achiziționate de clienții care au achiziționat Produsul A (comerț electronic optimizat)
Aceasta este similară schemei de interogare anterioare, dar funcționează pentru comerțul electronic optimizat. În plus, utilizează TABLE_DATE_RANGE
pentru a interoga date din mai multe zile.
produsele achiziționate de un client care achiziționează produsul A (comerț electronic optimizat) |
---|
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity |
Numărul mediu de interacțiuni ale utilizatorilor înainte de achiziție
Acesta este un exemplu de interogare a unei comenzi JOIN() [...] ON
, care depinde numai de datele Google Analytics.
Puteți vedea, mai jos, schema de script pentru întrebarea Care este numărul mediu de interacțiuni ale utilizatorilor de dinaintea unei achiziții?
numărul de interacțiuni ale utilizatorilor înainte de o achiziție |
---|
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number |
- Prima linie efectuează operațiunea matematică principală pentru a determina numărul mediu interacțiuni ale utilizatorilor pe produs, iar interogarea efectuează o operație de alăturare a celor două interogări secundare numite „Alias_Name_1” și „Alias_Name_2”.
- Interogarea secundară „Alias_Name_1” este folosită pentru a genera un câmp care utilizează funcția agregată
SUM()
pentru a însuma toate accesările înregistrate pentru un produs. - Interogarea secundară „Alias_Name_2” se utilizează pentru a determina numărul de accesări efectuate de utilizatori pentru fiecare produs, utilizând funcția
COUNT()
. - Ultimul rând arată câmpul comun (
hits.item.productSku
) folosit de cele două seturi de date la îmbinare.
Iată un exemplu pentru interogarea: Pe 10 septembrie 2013, care este numărul mediu de interacțiuni ale utilizatorilor înainte de o achiziție?
numărul de interacțiuni ale utilizatorilor înainte de o achiziție pe 10 septembrie 2013 |
---|
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number |
Procentul vândut din stoc pentru fiecare produs
Acesta este un exemplu de interogare care nu depinde numai de date Google Analytics, ci și de date din afara programului. Prin combinarea celor două seturi de date, puteți începe să înțelegeți comportamentul utilizatorilor la un nivel mai segmentat. Puteți importa în BigQuery date din afara programului Google Analytics, însă rețineți că li se va aplica taxa lunară de stocare a datelor.
Puteți vedea, mai jos, o schemă de script pentru întrebarea Ce procent din stoc a fost vândut pentru fiecare produs?
procentul vândut din stoc pentru fiecare produs |
---|
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold |
- Primul rând generează două câmpuri: unul conține toate codurile de produs, iar celălalt aplică operația matematică prin care se va determina procentul vândut din stoc pentru codul de produs respectiv.
- Întrucât această interogare depinde de două seturi de date, trebuie să folosiți funcția
JOIN() ... ON
. Această comandă îmbină rândurile din cele două seturi de date pe baza câmpului comun dintre acestea. În acest caz, cele două seturi de date sunt[ ‘Imported_DataSet’ ]
și'Alias_Name'
. [ ‘Imported_DataSet’ ]
reprezintă datele din afara programului Google Analytics. Acesta este setul de date care conține câmpul cu valoarea stocului rămas (Imported DataSet.'stock_left_field'
) și câmpul cu parametrul codului de produs (Imported_DataSet.'productId_field'
).'Alias_Name'
este numele atribuit datelor returnate de interogarea secundară afișată cu gri. Această interogare secundară folosește datele Google Analytics pentru a determina cantitatea totală de articole vândute pentru fiecare produs.- Ultimul rând folosește propoziția
ON
pentru a afișa câmpul comun al celor două seturi de date și secțiunea în care cele două seturi de date sunt unite.
Multe dintre variabilele din această interogare au numele setului de date atașat ca prefix la numele acestora (de exemplu, Set_de_date_importat.'productId_field', Alias_Name.quantity_sold
). Acest lucru are rolul de a indica în mod clar câmpul selectat și setul de date căruia îi aparține acesta.
Iată un exemplu pentru interogarea: Ce procent din stoc a fost vândut pe produs pe 28 iulie 2013?
procentajul vândut din stoc pentru fiecare produs în 28 iulie 2013 |
---|
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold |
Profitabilitatea fiecărui produs
Puteți vedea, mai jos, o schemă de script pentru întrebarea Care este profitabilitatea fiecărui produs?
profitul în funcție de produs |
---|
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit |
- Primul rând conține operația matematică necesară pentru a calcula profitul total obținut pentru fiecare produs.
- Interogarea secundară gri folosește date din afara programului Google Analytics și culege informații despre profitul obținut la vânzarea unui produs.
- Interogarea secundară roșie este asociată datelor Google Analytics și va fi combinată cu datele din afara programului. Aceasta calculează cantitatea de articole vândute pentru fiecare produs.
- Ultimul rând folosește propoziția
ON
pentru a indica în mod clar câmpul comun al celor două seturi de date. În acest caz, este vorba despre codul produsului.
Iată un exemplu pentru interogarea Care a fost profitabilitatea fiecărui produs pe 28 iulie 2013?
profitul pe produs în 28 iulie 2013 |
---|
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit |
Profitul se calculează prin identificarea diferenței dintre prețul la care este vândut produsul și costul de fabricare a acestuia. Aceste informații sunt stocate în setul de date non-Google Analytics.
Profitabilitatea reală a fiecărui produs (luând în considerare rambursările)
Puteți vedea mai jos o schemă de script pentru întrebarea: Care este profitabilitatea reală a fiecărui produs?
profitul real în funcție de produs |
---|
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit |
- Aceasta este o interogare foarte similară cu Care este profitabilitatea fiecărui produs? Singurele diferențe constau în setul de date din afara programului Google Analytics, din interogarea secundară gri, și în operația matematică de calculare a profitului real, de pe primul rând.
- În setul de date din afara programului Google Analytics, calculați și suma totală cheltuită pentru rambursări (în propoziția
SELECT
din interogarea secundară roșie). - Apoi veți face o operație matematică pe rândul 1 pentru a afla profitul real, prin scăderea sumei cheltuite pentru rambursări din profitul brut.
Pentru mai multe informații cu privire la interogare, consultați secțiunea privind profitabilitatea fiecărui produs.
Iată un exemplu pentru interogarea Care a fost profitabilitatea reală a fiecărui produs pe 28 iulie 2013?
profitul real pe produs în 28 iulie 2013 |
---|
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit |
Profitul real ține cont de profitabilitatea unui produs după ce se iau în calcul produsele rambursate. Pentru a calcula venitul total rambursat pentru un produs:
venitul total rambursat pentru un produs = (prețul produsului + prețul de livrare rambursat al produsului) * cantitatea de produse rambursate