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:

direktesiden direkte etterfulgt av en direkteavspilling
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 navn klientypene basert på kombinasjonen av plattform og app-id.

navn	app-id	plattform
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

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.

Noen app-er vil beholde samme sessionId, som identifiserer et besøk, men få en 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:

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.)
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 viser 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.

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,          

         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. Den kan være lurt å fjerne den og bruke noe annet for å hente kanalnavn.

Resten av spørringen oppretter en rekke boolske kolonner for å til slutt velge ut radene som skal være tellende, og returnerer resultatet. Den første delen legger til kanal basert på hva den finner i sti-kolonnen. Deretter definerer den 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,
         REGEXP_EXTRACT(sti, r'\((.+?)\)') 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. 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.

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

Name		Name	Last commit message	Last commit date
Latest commit History 6 Commits
direktesider.org		direktesider.org
direktesider.sql		direktesider.sql
readme.org		readme.org

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Direktesider

Spørringen

About

Releases

Packages

nrkno/direktesideinnsikt

Folders and files

Latest commit

History

Repository files navigation

Direktesider

Spørringen

About

Resources

Stars

Watchers

Forks

Releases

Packages 0

Packages