Kuidas kasutada nuppu SELECT FROM UNNEST, et analüüsida BigQuery for Analyticsis mitmeid parameetreid

(Mees, ma vajan oma blogipostituste jaoks lühemaid pealkirju)

Kuule, BigQuery-for-Google-Analytics-for-Firebase arendajad!

Meie eelmises blogipostituses näitasin teile, kuidas BigQuery funktsiooni UNNEST kasutada sündmuse parameetrite analüüsimiseks oma Google Analyticsis Firebase'i andmete jaoks.

Seda funktsiooni peate kasutama, kuna tavaliselt salvestatakse sündmuste parameetreid korduva kirjena, mida võite mõelda massiivina. Neid Tühistades saate selle massiivi lahti hajutada, paigutada iga üksiku sündmuse parameetri uude ritta ja seejärel kopeerida kõigi nende üksikute parameetrite algne rida. Sarnaselt sellele animatsioonile:

Nüüd on see tõesti kasulik, kui soovite analüüsi andmetes analüüsida ühte sündmuse parameetrit. Mis saab aga siis, kui soovite analüüsida kahte korraga?

Vaadakem näiteks meie näidismängu Flood-it - siin on avalik andmekogu, kui soovite seda ise proovida.

Kui peaksite vaatlema meie taseme_komplekti_kiirmängu sündmust, siis on meid huvitavad kaks parameetrit: väärtusparameeter, mis ütleb meile kasutaja lõppskoori (st mitu käiku kulus tahvli puhastamiseks) ja tahvli parameeter, mis tähistab tahvli suurust.

See mängija sai väikesel laual mängides tulemuse 18

Kuna teie skoor võib mängitava tahvli põhjal üsna erineda, on ilmselt mõistlik grupeerida meie sündmused lauaparameetri järgi, enne kui hakkame arvutama statistikat, nagu meie kasutajate keskmine tulemus.

Kui me lihtsalt tahaksime neid sündmuse parameetreid HAKKAMA, näiteks nii:

VALI param
FROM `firebase-public-project.analytics_153293282.events_20181003`,
UNNEST (event_params) AS-i param
WHERE event_name = "level_complete_quickplay"
JA (param.key = "value" VÕI param.key = "board")

Näete, et meil on kogu vajalik teave, kuid need ulatuvad eraldi ridadesse, mistõttu on nende analüüsimine keeruline.

Nii et üks lahendus on need sündmused lihtsalt ümber rühmitada, vaadates, millistel ridadel on sama kasutaja_pseudo_id ja sündmuse ajatempel. Kui kahel veerul on kahes reas samad väärtused, võime olla kindlad, et need kuuluvad täpselt samasse sündmusesse. Seega peame nende konsolideerimiseks lihtsalt natuke tööd tegema ...

VALI
  MAX (if (param.key = "value", param.value.int_value, NULL)) AS tulemus,
  MAX (if (param.key = "tahvel", param.value.string_value, NULL)) AS board_type
FROM (
  SELECT sündmuse_nimi, sündmuse_tunnuse tempel, kasutaja_pseudo_id, param
  FROM `firebase-public-project.analytics_153293282.events_20181003`,
  UNNEST (event_params) AS-i param
  WHERE event_name = "level_complete_quickplay"
  JA (param.key = "value" VÕI param.key = "board")
)
GROUP BY user_pseudo_id, event_timestamp

... ja siis läheme edasi ja analüüsime neid kahte parameetrit, nagu me tahaksime.

Kuid see on minu jaoks asjatult keeruline. Kas tõesti jaotame kõik oma parameetrid eraldi ridadeks ainult selleks, et mõni hetk hiljem need uuesti kokku ühendada? See häirib mind filosoofilisel tasandil ja ma arvan, et see on ka üsna ebaefektiivne.

VALI UNESTEST päästmiseks!

Õnneks on selle parandamiseks veel üks viis, kasutades nuppu VALI UNNESTIST. Kui kasutate funktsiooni SELECT FROM UNNEST, siis ütlete põhimõtteliselt: “Hei, ma tahan seda korduvat salvestust UNESTADA omaenda väikesesse ajutisse tabelisse. Kui olete selle teinud, siis minge edasi ja valige sellest üks rida ning pange see meie tulemustesse nii, nagu oleks see mingi muu väärtus. ”

Ma tean, et seda on alguses pisut raske heita, nii et vaatame meie näidet.

Esimene asi, mida ma teen, on eemaldada meie esialgne UNNESTi avaldus ja lihtsustada meie päringut, et vaadata kõiki meie taseme täielikke sündmusi.

SELECT sündmuse_nimi, sündmuse_tunnuse tempel, kasutaja_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

Järgmisena palun BigQuery'il valida veerg väärtus.int_value meie massiivi UNNESTed event_params alt, kus sündmuse parameetri võti võrdub "väärtus".

SELECT sündmuse_nimi, sündmuse_ajatempel, kasutaja_pseudo_id,
  (VALI väärtus.int_väärtus UNNESTist (sündmusparameetrid)
    KUS võti = "väärtus") AS-i tulemus
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

See, mis lõpuks juhtub, on midagi sellist nagu see animatsioon, kus laiendame event_params massiivi enda väikesesse ajutisse tabelisse, filtreerides ühe kirje, kus võti = "väärtus", ja haarates sealt väärtuse value.int_value.

Ja saate tulemusi, mis näevad välja natuke sellised.

Jällegi on meil andmed meie algsest sündmusest ja siis on iga rea ​​kõrval eraldatud ühe sündmuse parameetri väärtus.

Nüüd on kolm olulist märkust, mida peaksite meeles pidama, kui teete selliseid valikuid VALITUD UNNESTist:

Esiteks peate veenduma, et olete kogu SELECT FROM UNNEST kõne sulgudesse pannud. Kui te seda ei tee, paanib BigQuery tõsiselt, et teil on kaks VALI avaldust ja see ei käivita teie päringut.

Teiseks, niipalju kui ma oskan öelda, töötab see ainult siis, kui te igast VALI FESTIVALIKUST kõnest annate ainult ühe väärtuse.

Kolmandaks, saate seda tehnikat mitu korda kasutada samas SELECT-avalduses! Nüüd saame võtta oma eelmise valimislause ja lisada teisele kõnele VALI UNNESTist, et saada oma väärtuse parameetri kõrvale ka meie pardal olev parameeter ...

SELECT sündmuse_nimi, sündmuse_ajatempel, kasutaja_pseudo_id,
  (VALI väärtus.int_väärtus UNNESTist (sündmusparameetrid)
    KUS võti = "väärtus") AS-i tulemus,
  (VALI väärtus.string_väärtus FUNNESTIST (sündmusparameetrid)
    KUS võti = "juhatus") AS juhatuse_suurus
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "level_complete_quickplay"

… Ja siin on nad kõik kenasti samas reas korraldatud.

Siis saame edasi minna ja teha mis tahes analüüse, mida me tahame, ja meie hinded on lihtne jagada mängulaua suuruse järgi. Siin; lähme edasi ja arvutame välja iga mängulaua tüübi keskmine tulemus ja standardhälve:

SELECT AVG (score) AS keskmine, STDDEV (score) as std_dev, board_size
FROM (
  SELECT sündmuse_nimi, sündmuse_ajatempel, kasutaja_pseudo_id,
  (VALI väärtus.int_väärtus UNNESTist (sündmusparameetrid)
    KUS võti = "väärtus") AS-i tulemus,
  (VALI väärtus.string_väärtus FUNNESTIST (sündmusparameetrid)
    KUS võti = "juhatus") AS juhatuse_suurus
  FROM `firebase-public-project.analytics_153293282.events_20181003`
  WHERE event_name = "level_complete_quickplay"
)
GROUP BY juhatuse_suurus
Pole üllatav, et suurema tahvli tühjendamiseks kulub rohkem liigutusi.

Analüüsige koos ka sündmuse parameetreid ja kasutaja omadusi!

Seda meetodit SELECT FROM UNNEST saate kasutada ka sündmuse parameetrite ja kasutaja omaduste koos analüüsimiseks.

Näiteks kasutavad Flood-iti inimesed sündmust vieta_virtual_currency, et jälgida, kui kasutaja veedab ringi lõpus „lisaetapid“. Väärtuse parameeter aitab jälgida, mitu sammu nad sündmuse kohta kulutavad. Jälgime ka seda, kui palju täiendavaid samme kasutajale esialgu külvatakse, kui kasutaja omadus on esialgne_ekstra_tapp.

Ütleme nii, et tahame teada saada, kas on mingit seost selle vahel, mitu sammu kasutajale algselt antakse ja mitu sammu nad kulutavad sündmuse „kasuta täiendavaid samme” ajal. See hõlmab väärtusparameetri ja esialgse_ekstra_astme kasutaja omaduse koosvaatamist. Jällegi on mõlemad need väärtused korduvatesse kirjetesse koondatud, siis kuidas saaksime neid analüüsida?

Teoreetiliselt saaksite seda teha, ühendades kaks UNNEST-i kõnet niimoodi.

SELECT sündmuse_nimi, sündmuse_ajatempel, kasutaja_pseudo_id,
  param.value.int_value AS liigub_kasutatud,
  kasutajaprop.value.string_value AS algne_ekstra_ samm
FROM `firebase-public-project.analytics_153293282.events_20181003`,
UNNEST (event_params) kui param,
UNNEST (kasutaja_omadused) kasutajaprogrammina
WHERE event_name = "kuluta_virtuaal_valuutat"
JA param.key = "väärtus"
JA userprop.key = "basic_extra_steps"

See toimiks, kuid see toimiks nii, et kõigepealt TÜHJENDA iga rida teie kulutatava_vääring_valuuta sündmuse iga parameetri suhtes ja seejärel TÜHJENDADA igaüks neist ridadest kõigi meie salvestatavate kasutajaomaduste suhtes. See tähendab, et korrutame oma suure päringu andmete kogumi põhimõtteliselt iga rea ​​(parameetrite arv * kasutaja omaduste arv). See võib päris kiiresti suureks saada!

Veetsin selle animatsiooni jaoks rohkem aega, kui oleksin nõus tunnistama.

Võib-olla teeb BigQuery kapoti all natuke võlu, et seda optimeerida, kuid arvestades, et olen näinud, et BigQuery insenerid näevad iga kord, kui ma mainin UNNESTi kasutamist mitu korda, silmanähtavalt, et ma vingun, pole ma siin eriti veendunud.

Õnneks võime taaskord kutsuda VALI UNNESTIST meid siin aitama. Esiteks saan meie väärtusparameetri väärtuse, sarnaselt meie esimesele näitele:

SELECT sündmuse_nimi, sündmuse_ajatempel, kasutaja_pseudo_id,
  (VALI väärtus.int_väärtus UNNESTist (sündmusparameetrid)
    KUS võti = "väärtus") AS samme_kasutatud
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "kuluta_virtuaal_valuutat"

Ja siis saan ka meie kasutajaomandi väärtuse. (Pange tähele, et see väärtus on salvestatud stringina, nii et kavatsen selle täisarvu esitada)

SELECT sündmuse_nimi, sündmuse_ajatempel, kasutaja_pseudo_id,
  (VALI väärtus.int_väärtus UNNESTist (sündmusparameetrid)
    KUS võti = "väärtus") AS steps_used,
  CAST (
    (SELECT väärtus.string_value FROM UNNEST (kasutaja_omadused)
     WHERE klahv = "esialgne_ekstra_ samm")
   AS int64) AS esialgsed sammud
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "kuluta_virtuaal_valuutat"

… Ja nüüd on mul kõik need väärtused kohe üksteise kõrval samas reas. Hooray!

See kood pole mitte ainult tõhusam (ma arvan), vaid seda on ka lihtsam mõista.

Siit alates on neid parameetreid üsna lihtne analüüsida. Näeme, kui suur on meie keskmine lisatasemete väärtus iga kasutaja atribuudi tüübi korral…

VALI AVG (sammud_kasutatud) AS keskmine_sammud_kasutatud, algsed_tapid
FROM (
  SELECT sündmuse_nimi, sündmuse_ajatempel, kasutaja_pseudo_id,
    (VALI väärtus.int_väärtus UNNESTist (sündmusparameetrid)
      KUS võti = "väärtus") AS steps_used,
    CAST (
      (SELECT väärtus.string_value FROM UNNEST (kasutaja_omadused)
       WHERE klahv = "esialgne_ekstra_ samm")
     AS int64) AS esialgsed sammud
  FROM `firebase-public-project.analytics_153293282.events_20181003`
  WHERE event_name = "kuluta_virtuaal_valuutat"
)
KUS algne samm ei ole tühine
GROUP BY esialgsed sammud
TELLIMINE algsete sammude järgi

... või näeksime, kas nende kahe väärtuse vahel on mingisugust seost.

SELECT CORR (sammud_kasutatud, esialgsed_sammud) AS-i korrelatsioon
FROM (
SELECT sündmuse_nimi, sündmuse_ajatempel, kasutaja_pseudo_id,
  (VALI väärtus.int_väärtus UNNESTist (sündmusparameetrid)
    KUS võti = "väärtus") AS steps_used,
  CAST (
    (SELECT väärtus.string_value FROM UNNEST (kasutaja_omadused)
     WHERE klahv = "Init_extra_steps")
   AS int64) AS esialgsed sammud
FROM `firebase-public-project.analytics_153293282.events_20181003`
WHERE event_name = "kuluta_virtuaal_valuutat"
)
KUS algne samm ei ole tühine

Nii et te lähete, inimesed! Kui soovite kasutada rakendust UNNEST ja VALI UNNESTist, saate teha kiire töö kõigi nende korduvate kirjetega, mida Google Analytics for Firebase meeldib oma BigQuery skeemis kasutada. Ja nagu selgub, on tegemist sama tüüpi korduvate kirjetega, mida kuvatakse ka Crashlytics ja Cloud Messaging andmetes. Seega soovitan varuda aega nende tehnikatega harjumiseks, sest need muudavad teie elu kindlasti lihtsamaks.

Mida sa veel tahad teada BigQuery kasutamise kohta oma Firebase'i andmetega? Andke mulle allpool kommentaarides teada ja ma saan töötada veel ühe postituse kallal!