Feature Development

var_groups:
  - name: vars
    entity_key: session
    vars:
        - entity_var:
            name: campaign_source_first_touch
            select: first_value(context_campaign_source) # Window functions are supported in entity_vars, as long as the value is unique for a given user id
            window:
            # All the window functions in entity_vars are partitioned by main_id by default. It can take only order_by as a parameter.
                order_by:
                # In this example, we take the oldest occurence of the campaign source, so we sort the rows by timestamp in ascending order
                    - timestamp asc
            from: inputs/rsIdentifies
            where: context_campaign_source is not null and context_campaign_source != ''
        - entity_var:
            name: first_seen_tracks
            select: min(timestamp::date) # Get oldest timestamp from the tracks table
            from: inputs/rsTracks
            description: First seen timestamp from tracks table.
            is_feature: false
        - entity_var:
            name: first_seen_identifies
            select: min(timestamp::date)
            from: inputs/rsIdentifies
            description: First seen timestamp from identifies table
            is_feature: false
        - entity_var:
            # Once min timestamps from both tracks and identifies are defined, we pick the earliest timestamp of both here.
            # The prev two are temp features used to derive this feature.
            name: first_seen_date
            select: to_date(least(coalesce('{{session.Var("first_seen_tracks")}}','{{session.Var("first_seen_identifies")}}'), coalesce('{{session.Var("first_seen_identifies")}}','{{session.Var("first_seen_tracks")}}')
        - entity_var:
            name: orders_completed_in_past_365_days
            select: count( * ) # SQL function to count all rows from tracks table where event_type = 'order_completed'
            from: inputs/rsTracks
            where: event = 'order_completed' and datediff(day, date(timestamp), current_date()) <= 365
            description: Number of orders completed in the past 365 days.
        - entity_var:
            name: n_active_days_total
            select: count(distinct(timestamp::date)) # SQL function to first convert timestamp to date, then count the distinct dates
            from: inputs/rsTracks # Refering to the tracks table defined in the inputs.yaml file
            description: Number of days since the user first visited the app.