Transfer · Do Tasks · do_capture_sessionization_v5

Purpose

The do_capture_sessionization_v5 task is used to sessionize the Capture data in the analytical database.

Example usage

task:
    type: do_capture_sessionization_v5
    slack_channel: osb-platform-alerts
    slack_on_failure: yes
    start_date: today -3 days
    # end_date: today +1 days

extract:
    custom_where: | 
                    AND user_agent.is_crawler <> TRUE 

load:
    table_id: hits3_1
    table_id_longterm: hits3_longterm
    table_id_trash: trash3
    table_id_user_history: user_history
    table_id_profiles: profiles
    dataset_id: osb_capture

    # Include / exclude hits from sessionization (but leave them in the hits table)
    # where:
    #   - field: user_agent.is_crawler
    #     operator: '!='
    #     value: yes

    # Which ids are stitching keys?
    stitching:
      - key: email
        regexp: ^[0-9a-f]{64}$

    # Which ids are whitelisted in profiles table?
    ids:
      - ga
      - xandr
      - email
                

Root properties

Properties: Task

propertytyperequireddescription
typeenumeratoryesSet to do_capture_sessionization_v5
start_daterelative or absolute date or date & timeyesStart date of the period that will be selected in the datasource. Can be filled with an absolute or relative date. Read more about relative date and time here. It is used by this task to scan the hits table for unsessionized hits. The start_date is then corrected to timestamp of the earliest non-sessionized hit. This is done to reduce data scanning to a minimum.

Properties: Extract

propertytyperequireddescription
custom_wherestringnoString will be added to the query that selects the hits that will be sessionized. Use this to customize the selection, e.g. for ignoring hits from a certain IP address or particular user agent.

transforms

The `transforms` part of the configuration enables you to manipulate data before you load it in your data lake. You have to add all transform as an array.

Example usage

transforms:
    # IP address filter
    - type: filter
      exclude: yes
      field: ip_address.raw
      values:
        - ::ffff:127.0.0.1 # Locahost IPv6
        - 127.0.0.1 # Locahost IPv4
    
    # Test browsers (e.g. HeadlessChrome)
    - type: filter_regexp
      exclude: yes
      field: user_agent.raw
      values:
        - .*HeadlessChrome.*
    
    # Development URL's
    - type: filter_regexp
      field: page.url.raw
      exclude: yes
      values:
        - file:///.*

Transform types

Below are the types of transforms. You have to add them as an array. Below is an explanation per transform on how to use it exactly.

transform: filter

Filter out the hits that match the string filter and move them to the `trash` table.

propertytyperequireddescription
typestringnoSet to filter
fieldstringnoName of the table field. Only works on fields at root level.
valuesarray of stringsnoFilter values. If filter matches positive on any of the values, the complete hit is moved to the trash table. The value of the system.status field will be 1 and the run_id will be put in the system.run_id.

transform: filter_regexp

Filter out the hits that match the regular expression filter and move them to the `trash` table.

propertytyperequireddescription
typestringyesSet to filter
fieldstringyesName of the table field. Only works on fields at root level.
valuesarray of regular expressionsyesFilter regular expression values. If filter matches positive on any of the values, the complete hit is moved to the trash table. The value of the system.status field will be 2 and the run_id will be put in the system.run_id.

Properties: Deduplicate

propertytyperequireddescription
typeenumerator(redo,reset)noredo = Use if you want to redo the sessionization partially. In combination with a start_date in task it will first undo the sessionization of the previous runs and the re-sessionize the hits as usual. reset = Use if you want to completely redo the sessionization. It will drop the profiles, sessions, user_profiles and trash tables before undoing all sessionization.
start_daterelative or absolute date or date & timeyesStart date of the period that will be selected in the datasource. Can be filled with an absolute or relative date. Read more about relative date and time here. It is used to determine the run_id that sessionized the hit at the start_date. All hits from that run_id and the run_ids after will be re-sessionized. Handle with care. It might lead to new numbers in some cases, especially with changed filters.

Properties: Load

propertytyperequireddescription
project_idstringnoBigQuery only. Contains the ID of the project that holds all tables.
dataset_idstringnoBigQuery only. Contains the ID of the dataset that has all the tables.
table_idstringyesBigQuery only. Contains the ID of the table that holds the (short-term) hits. This table is time partitioned by the hour. It contains hits including and later than 160 days ago.
table_id_longtermstringyesBigQuery only. Contains the ID of the table that holds the longterm hits. It is time partitioned by day. It contains hit up till (and does not include) 160 days ago.
table_id_trashstringyesBigQuery only. Contains the ID of the trash table. Holds all the hits that matched the filters.
table_id_user_historystringyesBigQuery only. Contains the ID of the user history table. Contains the last session number per cookie of the last X runs.
table_id_profilesstringyesBigQuery only. Contains the ID of the profiles table.
site_idstringnoOnly sessionizes hits that have this site_id. Leaves all other hits in the hits table. It does not move hits to the trash. For that purpose, you should use a filter.
stitchingarray of objectsnoUse key, include and regexp. Stitch users on these keys. Only consider key values that match the regexp.
idsarray of stringsnoIds to add to the profiles table.