Kitewheel ID Manager: Analytics Data Model for Data Lake

The Kitewheel data lake extracts the following files in JSON format:

Table

Description

Extract Type

interactions

Records all interactions with the identifiers the interactions are created with and context (with object ID)

Incremental

journey_steps

Records all journey steps with the identifiers the journey steps are created with and context (with object ID)

Incremental

profiles

Identifiers, attributes and list of journey steps and interactions. The created at and last updated at timestamps.

Full

identifier_map

Identifier map of all identifiers types to the currently merged KWID

Full

Interactions

Each object contains a unique identifier, the set of identifier and identifier type pairs that were used to record the interaction, a timestamp (UTC), the name of the interaction and channel, the engagement score and any context that was provided.

1 2 3 4 5 6 7 8 9 10 { "object_id" : "12343435263637571253566127", "identifiers": [ {"idType" : "email", "identifier" : "me@me.com"}, ... ], "timestamp": "2019-11-13T18:47:17.405Z", "channelTypeId": "123456", "channelTypeText": "web", "interactionTypeId": "123456", "interactionTypeText": "view", "context": {...} }

Journey Steps

Each object contains a unique identifier, the set of identifier and identifier type pairs that were used to record the journey step, a timestamp (UTC), the name of the Journey step and any context that was provided.

1 2 3 4 5 6 7 8 { "object_id" : "12343435263637571253566127", "identifiers": [ {"idType" : "email", "identifier" : "me@me.com"}, ... ], "timestamp": "2019-11-13T18:47:17.405Z", "journeyStepId": "1234567982", "journeyStepText": "web", "context": {...} }

Profiles

Each profile object contains all of the identifier and identifier type pairs associated with that profile, the attributes given to that profile a list of journey step and interaction object identifiers. [ Do we need these?]

1 2 3 4 5 6 7 { "object_id" : "98743435263656571253286128", "identifiers": [ {"idType" : "KWID", "identifier" : "1234343526337571253566127"}, ... ], "attributes": {...}, "created_at" : "2018-11-13T18:47:17.405Z", "updated_at" : "2020-11-13T18:47:17.405Z" }

Identifier Map

Every object is a mapping from a (identifier, identifier type) pair to the earliest generated KWID of the current profile that this has been merged down to.

1 2 3 4 5 { "identifier" : "me@me.com", "idType" : "email", "object_id" : "1223712376" }

S3 Bucket

Bucket Name: kwdl<organization_id>-<HASH>

File System Organization:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 S3_BUCKET └── kw-datalake └── PROD ├── org_1234 │   ├── identifier_map │   │   └── 2021 │   │   └── 10 │   │   └── 21 │   │   ├── identifier_map_000000.json.gz │   │   └── identifier_map_000001.json.gz │   └── profiles │   └── 2021 │   └── 10 │   └── 21 │   ├── profiles_000000.json.gz │   └── profiles_000001.json.gz └── proj_5678 ├── interactions │   └── 2021 │   └── 10 │   └── 21 │   ├── interactions_000000.json.gz │   └── interactions_000001.json.gz └── journey_steps └── 2021 └── 10 └── 21 ├── journey_steps_000000.json.gz └── journey_steps_000001.json.gz

 

Redshift

The data can also be made available in Redshift.

Table Layout

Database: kwdl<organization_id>

Schema:

org_<organization_id> contains tables profiles and identifier_map

All projects within an organization will share the same pool of customer profiles (much like they do in Journey Discovery Analytics 2.0).

proj_<project_id> contains tables interactions and journey_steps

Each individual project will be separated into different schemas corresponding to the project_id. Because queries will need to cross schemas, best practice for queries going forward is to use explicit schemas.

Example:

Organization ID = 2056157604056401234

Project ID = 2170658008919444321

DB Name = kwdl2056157604056401234

Profile Data:

org_2056157604056401234.profiles

org_2056157604056401234.identifier_map

Journey Data

proj_2170658008919444321.interactions

proj_2170658008919444321.journey_steps