You are viewing documentation for an older version.

Click here to view the latest documentation.

Entity Traits 360 / Feature Table

Once you have done identity stitching to unify the identity of your users across all the cross-platforms, you can evaluate and maintain the required features/traits for each identified user in a entity traits 360.

This guide provides a detailed walkthrough on how to use a PB project and create output tables in a warehouse for an entity traits 360 model.

Prerequisites

Familiarize yourself with:

• A basic Profile Builder project by following the Profile Builder CLI steps.
• Structure of a Profile Builder project and the parameters used in different files.
• Identity Stitching model as Entity Traits 360 reuses its output to extract the required features/traits.

Entity Traits 360 Models

Once traits are defined on an entity, we need the means to use them.

A primary application to send them to the downstream destinations. The destination could either be the Activation API or via any of the rETL destinations that RudderStack supports. Each such destination requires data in the form of a table with an ID column and 1 or more feature columns. This is possible using Entity Traits 360.

Entity Traits 360 provides a way to access entity features based on any of the given ID types, including the entity main id as the identifier column. It creates a view which will have all (or a specified set of) features on that entity from across the project.

To configure the creation of a specific set of traits 360 models, add serve_traits section in the entity. You need to provide a list of ID types as id_served. Optionally, you can also give a name which specifies the name of the generated model. If you don’t specify a name, it will create the model with a default name. By default, it will add all available features on the entity into the view.

If you want finer control, you can also include or exclude any features from any models by defining a custom entity traits 360 model and add the reference to the serve_traits section like model_served: models/name_of_custom_traits_360_model. See below on more examples of this.

Default entity traits 360 model

pb_project.yaml:

...
entities:
  - name: user
    id_types:
      - user_id
    serve_traits:
      - id_served: user_id
        name: user_id_stitched_features
        # This will add an entity-traits-360 model with user_id as the identifier with model name user_id_stitched_features.
        # It will contain all the available features.

Custom entity traits 360 model:

This is an example of custom entity traits 360 model. Here we are including / excluding features from models of choice.

pb_project.yaml:

...
entities:
  - name: user
    id_types:
      - user_id
    serve_traits:
      - id_served: user_id
        model_served: models/cart_entity_traits_360

models/profiles.yaml:

models:
  - name: cart_entity_traits_360
    model_type: entity_traits_360
    model_spec:
      validity_time: 24h # 1 day
      entity_key: user
      id_served: user_id
      feature_list:
        - from: packages/pkg/models/cart_table # a table created by package
          include: ["*"] # will include all the traits
        - from: models/user_var_table
          include: ["*"]
          exclude: [cart_quantity, purchase_status] # except two, all the other traits will be included
        - from: models/sql_model
          include: [lifetime_value] # will include only one trait

Sample project

This sample project uses the output of an identity stitching model as an input to create a entity traits 360. The following sections describe how to define your PB project files:

Project detail

The pb_project.yaml file defines the project details such as name, schema version, connection name and the entities which represent different identifiers.

You can define all the identifiers from different input sources you want to stitch together as a user_main_id:

You need to add main_id to the list only if you have defined main_id_type: main_id in the ID stitcher buildspec.

# Project name
name: sample_id_stitching
# Project's yaml schema version
schema_version: 49
# Warehouse connection
connection: test
# Allow inputs without timestamps
include_untimed: true
# Folder containing models
model_folders:
  - models
# Entities in this project and their ids.
entities:
  - name: user
    id_types:
      - main_id # You need to add ``main_id`` to the list only if you have defined ``main_id_type: main_id`` in the id stitcher buildspec.
      - user_id # one of the identifier from your data source.
      - email
# lib packages can be imported in project signifying that this project inherits its properties from there
packages:
  - name: corelib
    url: "https://github.com/rudderlabs/profiles-corelib/tag/schema_{{best_schema_version}}"
    # if required then you can extend the package definition such as for ID types.

Input

The input file file includes the input table references and corresponding SQL for the above-mentioned entities:

inputs:
- name: rsIdentifies
  contract: # constraints that a model adheres to
    is_optional: false
    is_event_stream: true
    with_entity_ids:
      - user
    with_columns:
      - name: timestamp
      - name: user_id
      - name: anonymous_id
      - name: email
  app_defaults:
    table: rudder_events_production.web.identifies # one of the WH table RudderStack generates when processing identify or track events.
    occurred_at_col: timestamp
    ids:
      - select: "user_id" # kind of identity sql to pick this column from above table.
        type: user_id
        entity: user # as defined in project file
        to_default_stitcher: true
      - select: "anonymous_id"
        type: anonymous_id
        entity: user
        to_default_stitcher: true
      - select: "lower(email)" # can use sql.
        type: email
        entity: user
        to_default_stitcher: true
- name: rsTracks
  contract:
    is_optional: false
    is_event_stream: true
    with_entity_ids:
      - user
    with_columns:
      - name: timestamp
      - name: user_id
      - name: anonymous_id
  app_defaults:
    table: rudder_events_production.web.tracks # another table in WH maintained by RudderStack processing track events.
    occurred_at_col: timestamp
    ids:
      - select: "user_id"
        type: user_id
        entity: user
        to_default_stitcher: true
      - select: "anonymous_id"
        type: anonymous_id
        entity: user
        to_default_stitcher: true

Model

Profiles entity traits 360 model lets you define the specific features/traits you want to evaluate from the huge spread of scattered data in your warehouse tables.

A sample profiles.yaml file specifying a entity traits 360 model (user_profile):

var_groups:
  - name: first_group
    entity_key: user
    vars:
      - entity_var:
          name: first_seen
          select: min(timestamp::date)
          from: inputs/rsTracks
          where: properties_country is not null and properties_country != ''
      - entity_var:
          name: last_seen
          select: max(timestamp::date)
          from: inputs/rsTracks
      - entity_var:
          name: user_lifespan
          select: '{{user.Var("last_seen")}} - {{user.Var("first_seen")}}'
          description: Life Time Value of a customer
      - entity_var:
          name: days_active
          select: count(distinct timestamp::date)
          from: inputs/rsTracks
          description: No. of days a customer was active
      - entity_var:
          name: campaign_source
          default: "'organic'"
      - entity_var:
          name: user_rank
          default: -1
      - entity_var:
          name: campaign_source_first_touch
          select: first_value(context_campaign_source)
          window:
              order_by:
                  - timestamp asc
              partition_by:
                  - main_id
          from: inputs/rsIdentifies
          where: context_campaign_source is not null and context_campaign_source != ''
      - input_var:
          name: num_c_rank_num_b_partition
          select: rank()
          from: inputs/tbl_c
          default: -1
          window:
            partition_by:
              - '{{tbl_c}}.num_b'
            order_by:
              - '{{tbl_c}}.num_c asc'
          where: '{{tbl_c}}.num_b >= 10'
      - entity_var:
          name: min_num_c_rank_num_b_partition
          select: min(num_c_rank_num_b_partition)
          from: inputs/tbl_c

entity_var

The entity_var field provides inputs for the entity traits 360 model. This variable stores the data temporarily, however, you can choose to store its data permanently by specifying the name in it as a feature in the features key.

input_var

The syntax of input_var is similar to entity_var, with the only difference that instead of each value being associated to a row of the entity traits 360, it’s associated with a row of the specified input. While you can think of an entity_var as adding a helper column to the entity traits 360, you can consider an input_var as adding a helper column to the input.

window

In window option, main_id is not added by default, it can be any arbitrary list of columns from the input table. So if a feature should be partitioned by main_id, you must add it in the partition_by key.

Output

After running the project, you can view the generated material tables.

A sample output containing the results in Snowflake:

Window functions

A window function operates on a window (group) of related rows. It performs calculation on a subset of table rows that are connected to the current row in some way. The window function has the ability to access more than just the current row in the query result.

The window function returns one output row for each input row. The values returned are calculated by using values from the sets of rows in that window. A window is defined using a window specification, and is based on three main concepts:

• Window partitioning, which forms the groups of rows (PARTITION BY clause)
• Window ordering, which defines an order or sequence of rows within each partition (ORDER BY clause)
• Window frames, which are defined relative to each row to further restrict the set of rows (ROWS specification). It is also known as the frame clause.

Snowflake does not enforces users to define the cumulative or sliding frames, and considers ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING as the default cumulative window frame. However, you can override this by defining the frame manually.

On the Redshift aggregate window function list given below, specify the frame_clause while using any function from the list:

• AVG
• COUNT
• CUME_DIST
• DENSE_RANK
• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• LISTAGG
• MAX
• MEDIAN
• MIN
• NTH_VALUE
• PERCENTILE_CONT
• PERCENTILE_DISC
• RATIO_TO_REPORT
• STDDEV_POP
• STDDEV_SAMP (synonym for STDDEV)
• SUM
• VAR_POP
• VAR_SAMP (synonym for VARIANCE)

On the Redshift ranking window functions given below, do not specify the frame_clause while using any function from the list:

• DENSE_RANK
• NTILE
• PERCENT_RANK
• RANK
• ROW_NUMBER

When using a window function, use frame_clause carefully. While It is not very critical for Snowflake, using it incorrectly in Redshift can lead to errors.

Example of using frame_clause:

- entity_var:
    name: first_num_b_order_num_b
    select: first_value(tbl_c.num_b) # Specify frame clause as aggregate window function is used
    from: inputs/tbl_c
    default: -1
    where: tbl_c.num_b >= 10
    window:
        order_by:
        - tbl_c.num_b desc
        frame_clause: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- entity_var:
    name: first_num_b_order_num_b_rank
    select: rank() # DO NOT specify frame clause as ranking window function is used
    window:
        partition_by:
        - first_num_b_order_num_b > 0
        order_by:
        - first_num_b_order_num_b asc

Note how frame_clause is specified in first entity_var and not in the second one.

Feature Table Model (legacy)

Profiles Feature table model lets you define the specific features/traits you want to evaluate from the huge spread of scattered data in your warehouse tables.

A sample profiles.yaml file specifying a feature table model (user_profile):

models:
  - name: user_profile
    model_type: feature_table_model
    model_spec:
      validity_time: 24h # 1 day
      entity_key: user
      features:
        - user_lifespan
        - days_active
        - min_num_c_rank_num_b_partition

Model specification fields

Questions? Contact us by email or on Slack