Identity Stitching Setup

Learn from this article:

Identity stitching configuration

Attributes calculation configuration

How to run attributes from scratch

How to run identity stitching from scratch

How to force cache update

Identity stitching configuration

Identity stitching rules can be configured in Meiro Business Explorer under the Identity Stitching setting. The prerequisties to set up identity stitching rules are:

• Sources and events need to be defined
• Stitching categories need to be created.

Refer to this documentation on Identity Stitching in Meiro Business Explorer for more details. The basic steps are:

1. Go to the Identity Stitching tab
2. Create new stitching categories based on the events that have been set up.
3. Add rules for each category for all events that the rule applies to. (eg. the "stitching_email" category may apply to web form submissions and login events)
4. Refer to this documentation for examples of stitching rules you would typically create.

Know More: For instances that have multiple domains, (eg. different web properties), it is recommended to have Cross-domain tracking to track the same web visitor across different sites. In this case, the "inbound_user_ids" field in the payload will be used for identity stitching, it is an array containing a list of meiro cookies.

Example identity stitching rule to stitch Meiro User ID across domains.

json_array_elements_text(
	json_build_array(
    	payload->>'user_id',
    	payload->'inbound_user_ids'->>0
    	)
    )

Best Practice: For Meiro User ID, always name this stitching category 1PT Meiro Web ID

Attributes calculation configuration

attributes table is in public schema

Basic principle of attributes calculation is to define the attributes' definition. To help with that, we have prepared a few basic types of attributes. If pre-defined attribute types do not cover what you need, you can create custom ones.

Apart from pre-defined typed attributes, there is one more distinction. The way how attributes are calculated is that for the attribute query calculating the value only events belonging to the customer entity are available.

Create attribute definitions in Meiro Business Explorer in the Administration/Entities/Attributes tab, more details here.

You will define the attribute definition under the "definition" section in the attribute setup after clicking on "Create Attribute".

The fields in configuration stand for:

sources, types, version	a filter for customer events. Filters only customer events you want the attribute to be calculated on. If empty, it means "all" (default empty)
type	type of calculation (see below, required)
value	value extracted from payload, usually PostgreSQL JSON value expression (required)
outer_value	value extracted from value (one above) - used for multi level extraction (default "value")
weight	weight of value, used for most_frequent, least_frequent calculation types if one customer event contains value with its weight (default 1)
outer_weight	weight for the value extracted from value (default 1)
filter	additional filter you can apply on the customer events, any valid PostgreSQL expression (default empty)
outer_filter	filter applied on the value extracted from value (default empty)

Predefined calculation types:

• avg
• first
• last
• least_frequent
• list (list of all unique values)
• max
• min
• most_frequent
• num_of_unique (number of unique values)
• sum
• value (first value we find - no ordering)
• custom

If you want to define your own custom attribute, create a custom SQL query, with "base" as the table like so (note that comments are not allowed in the definition): 

SELECT count(*) AS value FROM base

Where base is a table available in the query that holds all customer events for the customer entity that is being calculated. It has a header: id, source_id, type, version, event_time, payload

Please beware that the custom query MUST return only 1 column with a name value. Do not forget that, please.

The query must return always 1 column, can return 0 to N rows.

Context queries

However, there are attributes that do require some context about other customer entities, like engagement attributes. For this case, there is a concept of context table available to your query, which behaves like a key-value store holding results of queries ran on the whole customer base.

Imagine you need information about the 0.9 percentile of customer events count on the whole customer entities base. In contex which is defined like this:

CREATE TABLE public.context (
    key text PRIMARY KEY,
    value text,
    query text,
    modified timestamp without time zone
);
CREATE UNIQUE INDEX context_pkey ON cdp_attr.context(key text_ops);

You can define a key maximum_perc_all, with query:

with tmp as (
	select max(value) as value, ntile * 10 as percentile
    from (select customer_events_count as value, ntile(10) over (order by customer_events_count) as ntile
          from public.customer_attributes
          where customer_events_count is not null) as buckets
    group by 2
    order by 2
)
select jsonb_object(array_agg(value)::text[], array_agg(percentile)::text[])::text as value
from tmp

Once you that, next time the component runs and it gets to the attribute calculation, before it starts with actual calculation for each customer entity, it will run the query and store the result of it in the valuecolumn. Hence, you can use these pre-calculated values in your custom queries in attributes definition, for example like this:

SELECT count(*)/(SELECT value FROM public.context WHERE key = 'maximum_perc_all') AS value FROM base

All values for context are updated before attribute calculation runs if they are older than 24 hours or if have not been calculated yet.

Meta attributes

Have you found yourself in a situation where you have a bunch of attributes all using same or similar logic? Something like common clean up on events before extracting a value, repeating and copy pasting the logic from one attribute to five or ten more attributes? And then when you found a problem in that cleaning logic you had to change it in all ten attributes? Well, not anymore. 

If you have common thing you would like to use in multiple attributes (for example calculating all bought products and their prices, then using this information to get attributes like average price per product, total quantity, total price, average number of products per transaction...), you can define it as meta attribute (CTE, or common table expression) that you can use in any other attribute calculation. Speed and convenience, yay!

Refer to the documentation here on how to set up meta attributes. Basically you can treat them as a "table" that will be usable in attribute definitions later on. Here are some examples:

-- meta attribute
-- ID: products_bought
-- definition:
select 
	payload::jsonb->>'product_id', 
    payload::jsonb->>'quantity', 
    payload::jsonb->>'price'
from base
where type = 'purchase' and event_time > NOW() - '30 days'::interval

-- attribute
-- ID: avg_price_per_product
-- definition (custom query):
select avg(price) from products_bought

-- attribute
-- ID: sum_price
-- definition (custom query):
select sum(price) from products_bought;

-- attribute
-- ID: sum_quantity
-- definition (custom query):
select sum(quantity) from products_bought;