ePrivacy and GPDR Cookie Consent by Cookie Consent Skip to main content

Loader ME to CDP

Loader ME to CDP extracts raw customer events from the Meiro Events (ME) database, transforms the payload based on payload transformation definitions, and uploads the transformed customer events to a CDP database. 

Data In/Data Out

Data In

The component loads its configuration from the /data/config.json file.

Data Out If the dry run mode is activated, then after applying Payload Transformation Definitions, results are saved into the CSV file located at /data/out/tables/events.csv.

Learn more: about the folder structure here.

Parameters

Credentials

Meiro Events Database

Знімок екрана 2023-02-23 о 17.33.23.png

Host

(required)

The host address of the Meiro Events database where it is running.

Port

(required)

The port number used to connect to the Meiro Events database.

User

(required)

The username or user ID used to authenticate and authorize access to the Meiro Events database.

Database

(required)

The name of the Meiro Events database to connect to.

Password

(required)

The secret string of characters used to authenticate and authorize access to the Meiro Events database.
Meiro CDP Login

Знімок екрана 2023-02-23 о 17.50.58.png

URL

(required)

URL of CDP instance

Username

(required)

Email used for logging in to CDP instance. 


Warning: user has to have ACL permissions to create customer events; otherwise, the component fails.

Password

(required)

Password for CDP login

Dry run

(optional)

If the parameter is set to true, then the component runs in dry run mode.

In dry run mode, the component connects to the ME database and executes event queries based on Event Definitions. It then applies Payload Transformation Definitions and saves the result into the CSV file located at /data/out/tables/events.csv.


If the parameter is set to false, then the component runs in wet run mode.

In wet run mode, the component uploads extracted and transformed customer events directly to the CDP database.

First run time window

(required)

A parameter in natural language format (e.g., "30 seconds ago") is used as a reception time filter during the first extraction of raw customer events. The component saves  timestamp of the previous execution and uses it as the reception time filter for subsequent extractions in an infinite loop.


Remember: the first run time should be as small as possible so that the component does not need to scan huge data volumes from the ME database.

Example of valid configurations:

{
    "parameters": {
        "credentials": {
            "me_db": {
                "host": "me.meiro.io",
                "port": "1234",
                "user": "meiro",
                "database": "meiro_events",
                "#password": "sessame"
            },
            "cdp": {
                "url": "https://cdp.meiro.io/",
                "username": "cdp@meiro.io",
                "#password": "sessame"
            }
        },
        "dry_run": false,
        "first_run_time_window": "30 seconds ago",
      "events": []
    }
    }

Events

Below is an array of definitions used for the extraction raw customer events from the ME database and transformation of customer event payload.

Знімок екрана 2023-02-23 о 18.09.16.png

Enabled

(required)

True/False - if event definition should be included in the component's run

Schema

(required)

ME database schema where customer event is stored

Table name 

(required)

Table name where customer event is stored

Source id 

(required)

Source ID that event is assigned (must correspond with source_id defined in CDP instance)

Type 

(required)

Event type (must correspond with type defined in CDP instance)

Version

(required) 

Event version (must correspond with version defined in CDP instance)

Event time 

(required)

SQL definition of event_time extraction from ME Database

User identifier 

(required)

SQL definition of user_identifier extraction ME Database

Event filter

(required)

SQL definition of customer_event extraction from ME Database

Payload 

(required)

SQL definition of payload extraction from ME Database

Unnest payload array 

(optional)

SQL definition for array of payload extraction from ME Database (example of usage below)

Example of the event definition without unnest_payload_array specification:

{
  "enabled": true,
  "schema": "events",
  "table_name": "events",
  "source_id": "me_web",
  "event_type": "me_page_view",
  "version": "1-0-0",
  "event_time": "payload ->> 'timestamp'",
  "user_identifier": "payload ->> 'user_id'",
  "event_filter": "payload ->> 'type' = 'page_view'",
  "payload": "payload",
  "payload_transformation_definitions": []
}
Example of definition translated into following SQL query:
WITH events_events AS (
    SELECT
            reception_time as "reception_time",
            payload as "payload"
    FROM events.events
    WHERE reception_time >= {{some_timestamp}}::timestamp
)
SELECT
        'me_web' as "source_id",
        'me_page_view' as "type",
        '1-0-0' as "version",
        payload ->> 'timestamp' as "event_time",
        payload ->> 'user_id' as "user_identifier",
        payload as "payload"
FROM events_events
WHERE payload ->> 'type' = 'page_view'

Where {{some_timestamp}} will be replaced by the current timestamp.

Example of the event definition with unnest_payload_array specification:

{
  "enabled": true,
  "schema": "events",
  "table_name": "magnews_events",
  "source_id": "mn",
  "event_type": "mn_sent",
  "version": "1-0-0",
  "event_time": "reception_time",
  "user_identifier": "payload ->> 'MESSAGE.TO'",
  "event_filter": "payload ->> 'EVENT.TYPE' = 'Sent'",
  "payload": "payload",
  "unnest_payload_array": "payload -> 'json' -> 'data'",
  "payload_transformation_definitions": []
}
Example of definition translated into following SQL query:
WITH events_magnews_events AS (
    SELECT
            reception_time as "reception_time",
            payload as "payload"
    FROM events.magnews_events
    WHERE reception_time >= {{some_timestamp}}::timestamp
),
unnested AS (
  SELECT 
    reception_time as "reception_time", 
    jsonb_set(
      jsonb_set(
        jsonb_set(
          jsonb_set(
            jsonb_set(
              jsonb_set(
                jsonb_array_elements(payload -> 'json' -> 'data'), 
                '{method}', 
                payload -> 'method'
              ), 
              '{cookies}', 
              payload -> 'cookies'
            ), 
            '{headers}', 
            payload -> 'headers'
          ), 
          '{endpoint}', 
          payload -> 'endpoint'
        ), 
        '{parsed_url}', 
        payload -> 'parsed_url'
      ), 
      '{remote_addr}', 
      payload -> 'remote_addr'
    ) as "payload" 
  FROM 
    events_magnews_events 
  WHERE 
    "reception_time" >= '2023-01-01 00:00:00' :: timestamp
) 
SELECT 
  'mn' as "source_id", 
  '1-0-0' as "version", 
  'mn_sent' as "type", 
  (reception_time):: timestamp as "event_time", 
  (payload ->> 'MESSAGE.TO'):: text as "user_identifier", 
  payload as "payload" 
FROM 
  unnested 
WHERE 
  payload ->> 'EVENT.TYPE' = 'Sent'

Component translates query using jsonb_array_elements SQL function to explode an array of payloads.

It also appends default fields: method,cookies,headers,endpoint,parsed_url,remote_attr into the payload so all data might be used in event_filter or in payload_transformation processing.

Transformation definitions

This section describes how the extracted raw customer events undergo transformation using JSON payloads. Key features of transformation:

  1. The order of transformations is important, as each transformation is applied one by one to the payload in the respective order.
  2. All JSON (payload) paths must be defined using dot notation (e.g., payload.payload.email).
  3. The component supports data transformation only in key: value object. It does not support analyzing and iterating throw array objects.
  4. The user must ensure that a new payload path is possible to create. Example of the newly created path:

Знімок екрана 2023-02-24 о 07.53.25.png

It is not possible to create new_path_incorrect, because email key is already occupied by string object. It is not possible to create another nested object, and the component ends up with an error.


Allowed payload transformations

The ME to CDP component supports several transformations, which might be used in any order and as many times as you want to reach the required result of the payload. All transformations could be disabled using: 

{
  "enabled": false
}

Rename key

This feature allows the component to extract a value from a specified old path, create a new path, place the extracted value in the new path, and delete the old path. This helps organize and restructure the data within the payload, improving data quality and consistency.

Знімок екрана 2023-02-24 о 08.09.41.png

Example of "Rename key"

Знімок екрана 2023-02-24 о 08.10.20.png


Value mapping

The component first extracts the value from the target_old_value path. It then searches for a matching value in the value_maps array, comparing the extracted value with the old value from each value_map. If a match is found, the component takes the corresponding new value from the value_map, creates a new path named target_new_value, and places the new value from the value_map there. This process is similar to a join operation in a database.

Example of "Value mapping"

Знімок екрана 2023-02-24 о 08.13.08.png


Enrich pop up web banners

The component first extracts the banner_id value from  id_path. It then searches for matching banner_name from the refreshed web banner data downloaded from the CDP instance. If a match is found, the component creates a new path called name_path in the payload and places the banner_name value there.

Знімок екрана 2023-02-24 о 08.26.28.png

Example of "Enrich pop up web banners"

Знімок екрана 2023-02-24 о 08.24.43.png


Enrich embedded web banners

Same logic as in Enrich pop up web banners applied to payloads where embedded web banners data must be enriched.

image.png


Vocative from first name

This feature is designed for projects based in the Czech Republic, where vocative forms of names are commonly used. The component extracts the first name from the specified path using the first_name_path. Then tries to use vocative library to transform first_name. The transformed name is then placed in a new path specified by result_path.

image.png

Example of "Vocative from first name"

image.png


Gender estimate from first name

This functionality is mainly for projects in the Czech Republic because it uses a vocative library that works mostly with first names in the Czech calendar. Component extracts first_name from first_name_path path. Then try to use vocative library to estimate gender from first_name. It creates a new path in result_path and places estimated gender there.

image.png

Example of "Gender estimate from first name"

image.png


Replace string

Component extracts string object from string_path path. Then apply python replace method on an extracted object with old and new as an argument. It creates a new path  result_path and places transformed string there.

image.png

Example of "Replace string"

image.png

 
Strip string

Component extracts string object from string_path path. Then apply the python replace method on an extracted object with old and new as an argument. It creates a new path  result_path and places transformed string there.

image.png

Example of "Strip string"

image.png

 
Lower string

Component extracts string object from string_path path. Then applies the python lower method on an extracted object without any arguments. It creates a new path in result_path and places transformed string there.

image.png

Example of "Lower string"

image.png

 
Validate email

Component extracts string object from string_path path. Then applies the python lower method on an extracted object without any arguments. It creates a new path in result_path and places transformed string there.

image.png

Example of "Validate email"

image.png

 
Delete from path

Component checks if delete_path exists and removes it.

image.png

Example of "Delete from path"

image.png