Workflows · Do Tasks · do_analytics_sessionization

task:
    type: do_analytics_sessionization
    start_date: today -3 days

transforms:
    # IP address filter
    - type: filter
        field: ip_address.raw
        values:
        - ::ffff:127.0.0.1 # Locahost IPv6
        - 127.0.0.1 # Locahost IPv4
        - 123.4.56.0 # We receive a hit every minute from this IP address
    
    # Development URL's
    - type: filter_regexp
        field: page.url.raw
        values:
        - file:///.*
        
    # The following filter EXCLUDES hits from the bot filter that have account_id=osb.direct
    - type: bot_filter_exception
        field: system.account_id
        values:
        - osb.direct

    load:
        table_id: hits_v5
        table_id_trash: trash_v5
        table_id_user_history: user_history_v5
        table_id_profiles: profiles_v5
        dataset_id: osb_analytics

    # Which ids are stitching keys?
    ids:
        - key: ga
          regexp: ^GA[0-9]\\.[0-9]\\.[0-9]+\\.[0-9]+$
          stitching: no
        - key: email
          regexp: ^[0-9a-f]{64}$
          stitching: yes
        - key: account_id
          regexp: ^(tvg_)*[a-z0-9]{32}$
          stitching: yes

    # The validation below checks if all hits up to and including yesterday are sessionized
    validate:
        - query: |
                    SELECT DATE(DATETIME(tstamp, 'Europe/Amsterdam')) AS date_local,
                        COUNT(CASE WHEN session.number IS NULL THEN 1 END) AS hits_no_session,
                        COUNT(distinct CASE WHEN session.number IS NULL THEN user_id END) AS users_no_session,
                        COUNT(CASE WHEN session.number IS NOT NULL THEN 1 END) AS hits_session
                    FROM `{{ load.project_id }}.{{ load.dataset_id }}.{{ load.table_id }}` h
                    WHERE DATE(tstamp, 'Europe/Amsterdam') = CURRENT_DATE('Europe/Amsterdam')-1
                    AND hit_type <> 'ids'
                    AND coalesce(event.interaction, TRUE) = TRUE
                    GROUP BY 1
            type: number
            operator: '=='
            value: 0
            field: hits_no_session
        - type: number
            operator: '>'
            value: 0
            field: hits_session
                

transforms:
    # IP address filter
    - type: filter
      exclude: yes
      field: ip_address.raw
      values:
        - ::ffff:127.0.0.1 # localhost IPv6
        - 127.0.0.1 # Locahost IPv4
    
    # Test browsers (e.g. HeadlessChrome)
    - type: filter_regexp
      exclude: yes
      field: user_agent.raw
      values:
        - .*HeadlessChrome.*
    
    # The following filter EXCLUDES hits from the bot filter that have account_id=osb.direct
    - type: bot_filter_exception
        field: system.account_id
        values:
        - osb.direct