Skip to content

Latest commit

 

History

History
252 lines (230 loc) · 17 KB

direktesider.org

File metadata and controls

252 lines (230 loc) · 17 KB

Direktesider

I forbindelse med innsiktsarbeidet knyttet til direktesidene har jeg skrevet spørringen som dokumenteres i dette repoet, for å hjelpe til med å finne noen svar. Spørsmålene dreier seg i hovedsak om hvor mange besøk som er innom direktesidene, hvor mange besøk som har en direkteavspilling og hvordan direkteavspillingen ble startet (fra forsiden, direktesiden, eller et annet sted), og hvor mange som er innom tv-guiden.

For å besvare disse spørsmålene tar spørringen for seg sekvenser på to til tre skjermvisninger og avgjør om sekvensen er blant dem som besvarer et av spørsmålene eller ikke. En sekvens som besvarer et spørsmål, kaller jeg en målsekvens. Deretter teller den opp antall besøk som har hatt en målsekvens. Siden det kan flere ulike sekvenser i løpet av ett besøk, velger spørringen den mest relevante sekvensen. Dette er sekvensen som definerer besøket. La meg eksemplifisere med to sekvenser bestående av:

  1. direktesiden direkte etterfulgt av en direkteavspilling
  2. direktesiden direkte etterfulgt av noe annet enn en direkteavspilling (eller tv-guide)

Dersom et besøk inneholdt den første sekvensen vil besøket defineres av denne og telle med for denne sekvensen i opptellingen. Det samme vil gjelde dersom besøket både hadde den første og den andre sekvensen. Til slutt, et besøk som ikke hadde inneholdt den første sekvensen, men inneholdt den andre ville defineres av den andre sekvensen, og telle med for den i opptellingen.

Spørringen

Den første delen av spørringen setter navnet på klientypene basert på kombinasjonen av plattform og app-id.

navnapp-idplattform
LGno.nrk.accedo.tv.lgtv
Gammel Tizenno.nrk.accedo.tv.tizentv
Accedo wsno.nrk.accedo.tv.workstationtv
iOSno.nrk.nrktvappmob
Apple TVno.nrk.nrktvapptv
Android mobno.nrk.tvmob
Android TVno.nrk.tvtv
Tizenno.nrk.tv.smart-tvtv
webno.nrk.tv.webweb
Lage en funksjon som lager en tabell av arrayen i klienttype_og_navn.
WITH
KOMBINASJONER_OG_NAVN AS (
  SELECT *
    FROM UNNEST(
      ARRAY<STRUCT<
      navn STRING, appId STRING, platform STRING>>[
        ('LG', 'no.nrk.accedo.tv.lg', 'tv'),
        ('Gammel Tizen', 'no.nrk.accedo.tv.tizen', 'tv'),
        ('Accedo ws', 'no.nrk.accedo.tv.workstation', 'tv'),
        ('iOS', 'no.nrk.nrktvapp', 'mob'),
        ('Apple TV', 'no.nrk.nrktvapp', 'tv'),
        ('Android mob', 'no.nrk.tv', 'mob'),
        ('Android TV', 'no.nrk.tv', 'tv'),
        ('Tizen', 'no.nrk.tv.smart-tv', 'tv'),
        ('web', 'no.nrk.tv.web', 'web')])),

Avhenging av app vil navnet på skjermvisningen i noen tilfeller ligge i feltet page.path og andre tilfeller i mobileViewName. Det første er tilfellet for no.nrk.tv.smart-tv og no.nrk.tv.web. I denne delen av spørringen limes navnet på skjermvisningen sammen med kanalnavnet dersom kanalen er på formen nrk1_*, nrk1_*, nrktv[0-9] eller nrk[0-9]. Dette inkluderer NRK1, NRK2, NRK3, NRK Super, NRK tegnspråk og ekstrakanalene. Kanalnavnet finnes i page.path- eller content.id-verdien når det er snakk om en visning av direktesidene, epg-/tv-guide-sidene og avspillersidene. En IF-funksjon velger om informasjonen skal hentes fra page.path eller mobileViewName på bakgrunn av app-id-en, og legger det inn i samme kolonne, skjerm.

Lage en funksjon for å hente ut app-id-ene som henter skjermvisningsnavnet fra page.path.

Noen app-er vil beholde samme sessionId, som identifiserer et besøk, men få en NULL-verdi i previousViewId. Dette fører til en diskontinuitet i sekvensen av skjermvisninger, og jeg deler derfor besøket opp i delbesøk etter hver NULL-verdi i en previousViewId ved hjelp av sub_sesjon.

VIEWID_OG_DIREKTESIDE AS (
  /* Denne spørringen samler opp dataene som trengs for å senere kunne bygge stiene som 
     identifiserer turer innom direktesidene.
     Forklaring til feltene
       - navn           : navn på kombinasjonen av plattform og appId
       - viewId og      : gjør det mulig å koble sammen sekvenser av skjermvisninger
         previousViewId 
       - skjerm         : kombinerer mobileViewName eller page.path eventuelt med en kanal,
                          og bearbeider page.path til å ligne mer på mobileViewName,se merknad.
       - sessionId      : identifiserer besøket
       - firstTimeStamp : brukes til å beregne hvor langt ut i besøket skjermvisningen fant sted
       - sub_sesjon     : deler et besøk opp i underbesøk basert på at previousViewId er NULL
       
     Merknad: Kanal er basert på conten.id. Regexp-en filtrerer bort verdier som ikke er av
       typen nrk1, nrk2, nrktv4, nrk_tegnspråk.  
       */
  SELECT DISTINCT IFNULL(navn, 'Annet') navn, viewId, previousViewId,
         IF(appId IN ('no.nrk.tv.smart-tv', 'no.nrk.tv.web'),
           CASE
             WHEN SPLIT(page.path, '/')[SAFE_OFFSET(1)] = 'direkte' AND SPLIT(page.path, '/')[SAFE_OFFSET(3)] = 'avspiller' THEN 
               CASE -- Deler opp tilfellene der vi skal ha med kanal
                 WHEN SPLIT(page.path, '/')[SAFE_OFFSET(2)] LIKE 'nrk1_%' THEN 'avspiller (nrk1)'
                 WHEN REGEXP_CONTAINS(SPLIT(page.path, '/')[SAFE_OFFSET(2)], r'^nrk(s.+|_|tv\d|\d)+') THEN CONCAT('avspiller (', SPLIT(page.path, '/')[SAFE_OFFSET(2)] , ')')
               END
             WHEN SPLIT(page.path, '/')[SAFE_OFFSET(1)] = 'direkte' THEN 
               CASE
                 WHEN SPLIT(page.path, '/')[SAFE_OFFSET(2)] LIKE 'nrk1_%' THEN 'direkte (nrk1)'
                 WHEN REGEXP_CONTAINS(SPLIT(page.path, '/')[SAFE_OFFSET(2)], r'^nrk(s.+|_|tv\d|\d)+') THEN CONCAT('direkte (', SPLIT(page.path, '/')[SAFE_OFFSET(2)] , ')')
                 ELSE 'direkte'
               END
             WHEN SPLIT(page.path, '/')[SAFE_OFFSET(1)] = 'epg' THEN 
               CASE
                 WHEN SPLIT(page.path, '/')[SAFE_OFFSET(2)] LIKE 'nrk1_%' THEN 'epg (nrk1)'
                 WHEN REGEXP_CONTAINS(SPLIT(page.path, '/')[SAFE_OFFSET(2)], r'^nrk(s.+|_|tv\d|\d)+') THEN CONCAT('epg (', SPLIT(page.path, '/')[SAFE_OFFSET(2)] , ')')
                 ELSE 'epg'
               END
             WHEN SPLIT(page.path, '/')[SAFE_OFFSET(1)] != '' THEN SPLIT(page.path, '/')[SAFE_OFFSET(1)]
             WHEN SPLIT(page.path, '/')[SAFE_OFFSET(1)] = '' THEN 'forsiden'
           END,
           CONCAT(mobileViewName,
             CASE
               WHEN content.id LIKE 'nrk1_%' THEN ' (nrk1)'
               WHEN REGEXP_CONTAINS(content.id, r'^nrk(_|tv\d|\d)+') THEN CONCAT(' (', content.id, ')')
               ELSE ''
             END)
         ) skjerm,
         sessionId, firstTimeStamp, 
         COUNTIF(previousViewId IS NULL) OVER(PARTITION BY sessionId ORDER BY firstTimeStamp) sub_sesjon,
    FROM `nrk-datahub.snowplow_processed.views_v02`
         LEFT JOIN KOMBINASJONER_OG_NAVN USING(appId, platform)
   WHERE partitionDate = CURRENT_DATE - 2
     AND nrkService = 'nrktv'
     AND platform != 'pc'
     AND appId NOT IN ('no.nrk.nrktvapp.swift', 'no.nrk.goldendelicious.cdntest', 'no.nrk.NRK-Super')),

I neste del av spørringen beregnes det hvor lang tid det tar fra første skjermvisning i besøket til gjeldende skjermvisning.

/*
   Her beregnes det hvor lang tid det tar fra første skjermvisning i besøket til den gjeldende
   skjermvisningen.
   - delta_t        : tiden det tar fra besøket startet til denne sidevisningen
*/
TIDER AS (
  SELECT *,
         TIMESTAMP_DIFF(firstTimestamp, MIN(firstTimestamp) OVER(PARTITION BY sessionId, sub_sesjon), SECOND) delta_t
    FROM VIEWID_OG_DIREKTESIDE),

Neste del av spørringen bearbeider sekvenser på to til tre skjermvisninger. Disse sekvensene blir kodet inn i to kolonner, Fra og Til. Denne bearbeidingen skal hjelpe meg med å identifisere følgende tilfeller:

  1. Besøk som er innom en direkteavspilling, og om de kom dit fra
    1. direktesidene
    2. forsiden
    3. tv-guiden
    4. noe annet (kategorisider, etc.)
  2. Besøk som er innom direktesidene og derfra går til
    1. tv-guiden
    2. direkteavspilling
    3. noe annet (bounce)

Dersom besøket starter med en direkteavspilling eller en tv-guide-skjermvisning, viser jeg det ved å si at Fra er lik “Sesjonsstart”. I tillegg vises hva som følger etter en tv-guide-skjermvisning som enten “tv-guide (kanalnavn) -> avspiller”, “tv-guide (kanalnavn) -> bounce” eller “tv-guide (kanalnavn)”. Bounce vil si at neste skjermvisning har navigert bort fra tv-guide, og er ikke en avspilling. “tv-guide (kanalnavn)” vil si at neste skjermvisning også var tv-guide, og jeg har ikke tatt meg bryet med å finne ut hva som har skjedd videre nedover sekvensen av skjermvisninger.

ALLE_STIER AS (
  /* Spørringen kobler sammen tre skjermvisninger som kommer etter hverandre (stier) og
     identifiserer tilfeller som er innom direktesiden. Disse grupperes på grunnlag av om 
     neste skjermvisning var avspilling/tv-guide eller noe annet (bounce). I tillegg kate-
     goriseres stiene som en ønsketur eller en bomtur avhenging av om andre skjermvisning
     var avspilling/tv-guide eller ikke.
     Forklaring av nye felter:
     - app             : oversetter appId til et mer forståelig navn
     - sti             : kategoriserer sekvenser av tre skjermvisninger
     - tur             : ønsketur eller bomtur
     - tid_til_steg1-3 : tiden fra start av besøket til direkte-skjermvisning osv. */
  SELECT fv.sessionId, sub_sesjon, navn, fv.skjerm fskjerm, av.skjerm askjerm, tv.skjerm tskjerm,
         CASE
           WHEN av.skjerm IS NULL THEN CONCAT(fv.skjerm, ' -> bounce')
           WHEN tv.skjerm IS NULL THEN CONCAT(fv.skjerm, ' -> ', av.skjerm, ' -> bounce')
           ELSE CONCAT(fv.skjerm, ' -> ', av.skjerm, ' -> ', tv.skjerm)
         END sti,
         CASE
           WHEN REGEXP_CONTAINS(fv.skjerm, r'avspiller \(.+\)') THEN 'Sesjonsstart'
           WHEN REGEXP_CONTAINS(fv.skjerm, r'direkte') THEN 'direktesiden'
           WHEN REGEXP_CONTAINS(fv.skjerm, r'forsiden|frontpage') THEN 'forsiden'
           WHEN REGEXP_CONTAINS(fv.skjerm, r'(tv-guide|epg)( \(.+\))?') THEN 'Sesjonsstart'
           ELSE 'annet'
         END Fra,          
         CASE
           WHEN REGEXP_CONTAINS(fv.skjerm, r'(tv-guide|epg)( \(.+\))?') AND REGEXP_CONTAINS(av.skjerm, r'avspiller \(.+\)') THEN 'tv-guide -> avspiller'
           WHEN REGEXP_CONTAINS(fv.skjerm, r'(tv-guide|epg)( \(.+\))?') AND NOT REGEXP_CONTAINS(av.skjerm, r'(tv-guide|epg)( \(.+\))?') THEN 'tv-guide -> bounce'
           WHEN REGEXP_CONTAINS(av.skjerm, r'(tv-guide|epg)( \(.+\))?') AND REGEXP_CONTAINS(tv.skjerm, r'avspiller \(.+\)') THEN 'tv-guide -> avspiller'
           WHEN REGEXP_CONTAINS(av.skjerm, r'(tv-guide|epg)( \(.+\))?') AND NOT REGEXP_CONTAINS(tv.skjerm, r'(tv-guide|epg)( \(.+\))?') THEN 'tv-guide -> bounce'
           WHEN REGEXP_CONTAINS(fv.skjerm, r'avspiller \(.+\)') THEN 'direkteavspilling'
           WHEN REGEXP_CONTAINS(av.skjerm, r'avspiller \(.+\)') THEN 'direkteavspilling'
           WHEN REGEXP_CONTAINS(av.skjerm, r'(tv-guide|epg)( \(.+\))?') THEN 'tv-guide'
           ELSE 'bounce'
         END Til,          
	 CASE
           WHEN REGEXP_CONTAINS(fv.skjerm, r'avspiller \(.+\)') THEN REGEXP_EXTRACT(fv.skjerm, r'\((.+)\)')
           WHEN REGEXP_CONTAINS(av.skjerm, r'avspiller \(.+\)') THEN REGEXP_EXTRACT(av.skjerm, r'\((.+)\)')
           WHEN REGEXP_CONTAINS(tv.skjerm, r'avspiller \(.+\)') THEN REGEXP_EXTRACT(tv.skjerm, r'\((.+)\)')
           WHEN REGEXP_CONTAINS(fv.skjerm, r'.+ \(.+\)') THEN REGEXP_EXTRACT(fv.skjerm, r'\((.+)\)')
           ELSE REGEXP_EXTRACT(av.skjerm, r'\((.+)\)')
	 END kanal,
         fv.firstTimeStamp tid_skjerm1, av.firstTimeStamp tid_skjerm2,
         fv.delta_t tid_til_steg1,
         av.delta_t tid_til_steg2,
    FROM TIDER fv
         LEFT JOIN (SELECT sessionId, previousViewId, firstTimeStamp, viewId, skjerm, delta_t FROM TIDER) av ON fv.viewId = av.previousViewId AND fv.sessionId = av.sessionId
         LEFT JOIN (SELECT sessionId, previousViewId, viewId, skjerm, delta_t FROM TIDER) tv ON av.viewId = tv.previousViewId AND fv.sessionId = tv.sessionId),

Kolonnen sti er en konkatenering av en skjermvisningsekvens på tre skjermvisninger. Den brukes kun for å hente ut kanalnavnet, og er blitt brukt til debuging under skrivingen av spørringen. Det kan være lurt å fjerne den og bruke noe annet for å hente kanalnavn. Dette gjør jeg også i den nyeste verjsonen av spørringen, men jeg lar sti fortsatt ligge i spørringen.

Resten av spørringen oppretter en rekke boolske kolonner for å til slutt velge ut radene som skal være tellende, og returnerer resultatet. Dette gjøres ved å definere en kolonne hovedmål som er sann hvis Til er “direkteavspilling”, “tv-guide -> avspillier”, eller “tv-guide”. Deretter definerer den en kolonnne alternativt mål som er sann hvis Fra er “direktesiden” og Til er “bounce” eller Til er “tv-guide -> bounce”.

GOALS AS (
  SELECT DISTINCT sessionId, navn, Fra, Til, tid_til_steg1, tid_skjerm1, sub_sesjon, kanal,
         Til IN ('direkteavspilling', 'tv-guide -> avspiller', 'tv-guide') `hovedmål`,
         (Fra = 'direktesiden' AND Til = 'bounce') OR Til = 'tv-guide -> bounce' `alternativt mål`
    FROM ALLE_STIER
),

Den neste delen definerer en boolsk kolonne oppnådd hovedmål som er sann for alle rader i et besøk dersom hovedmålet er blitt oppnådd. Den definerer også en boolsk kolonne goal1 som er sann hvis tidsmålet tid_skjerm1 for en gitt kombinasjon av et besøk, hovedmål og alternativt mål er lik minimumsmålet for denne kombinasjonen. På denne måten identifiseres det første tilfellet som oppfyller hovedmålet eller alternativ mål.

FLAGGET AS (
  SELECT DISTINCT sessionId, navn, Fra, Til, tid_til_steg1, `hovedmål`, `alternativt mål`,
         LOGICAL_OR(`hovedmål`) OVER(PARTITION BY sessionId, sub_sesjon) `oppnådd hovedmål`,
         tid_skjerm1 = MIN(tid_skjerm1) OVER(PARTITION BY sessionId, sub_sesjon, `hovedmål`, `alternativt mål`) goal1
    FROM GOALS),

Videre oppretter denne delen en kolonne flagg som er sann for det første tilfellet av hovedmål eller alternativt mål, der hovedmålet trumfer alternativt mål. Dermed vil et besøk som oppfyller begge målene telle som et besøk som oppfyller hovedmålet. Besøk hvor ingen av målene er oppnådd tas ikke med til slutt.

FLAGGET2 AS (
  SELECT DISTINCT *,
         (`oppnådd hovedmål` AND `hovedmål` AND goal1) OR (NOT `oppnådd hovedmål` AND `alternativt mål` AND goal1) flagg
    FROM FLAGGET)

Her beregnes reulstatet. Ved å filtrere på Til og Fra kan man beregne hvor stor andel som har vært innom direktesidene uten å gjøre en direkteavspilling eller besøke tv-guiden, eller hvor stor andel som har hatt en direkteavspilling fra ulike innganger. For å få en oversikt over hvilke kanaler som spilles av, kan man kommentere ut Fra og Til, og kommentere inn kanal. Samtidig må man filtrere på Fra = 'direktesiden'

SELECT DISTINCT navn, Fra, Til, --kanal
       COUNT(sessionId) OVER(PARTITION BY navn, Fra, Til/*, kanal*/) `Antall besøk`,
       COUNT(sessionId) OVER(PARTITION BY navn, Fra, Til/*, kanal*/) / COUNT(sessionId) OVER(PARTITION BY navn) `Andel av alle besøk`,
  FROM FLAGGET2
 WHERE flagg 
  --  AND Til = 'direkteavspilling'
  --  AND Fra = 'direktesiden'
ORDER BY 1, 2, 3