Profile Stitching rules

General information

Here you can find examples of Profile Stitching rules possible to set in Business Explorer in Setup -> Identity Stitching tab (find more here about it).

Main idea of setting up PS rule is setting up the path in the payload to the PS identifier for the particular source and type of customer event using JSON Functions and Operators in PostgreSQL syntax. So, if your identifier is email and the payload looks like

{
	"email": "example@test.com",
	"activity": "campaign opened",
    "campaign_name": "Test Campaign"
}

then your rule will be:

payload ->> 'email'

The value can be cleaned or decoded if it is required for the business case using common PostgreSQL functions and expressions.

Remember: Often it is more efficient to set up more complex PS rule to clean and unify identifiers, than to do the same in ETL before loading the data.

Warning: Make sure your Identity Stitching graph is correct and you understand the format of the PS identifiers before setting up PS rules.

Examples

Nothing complicated, just get PS identifier:

payload ->> 'identifier'

Make sure that your Emails are case unified:

LOWER(TRIM(payload ->> 'email'))

Use regexp if you want to avoid stitching some not valuable values:

CASE 
	WHEN payload @@ '$.device_id like_regex "^[^1-9a-zA-Z]+$"'THEN NULL
	ELSE payload ->> 'device_id'
END

Trim the ID:

TRIM(payload ->> 'phone')

Or us it all together:

case 
	when lower(payload ->> 'id') in ('none', '0', 'null', '') then NULL
	else rtrim(payload ->> 'id', '.0')
end

Arrays are supported as well:

jsonb_array_elements_text(payload -> 'list_ids')

And even encoding is possible:

encode(sha256((payload->>'id_to encode')::bytea), 'hex')

Warning: Always test the rules you are planning to implement on relevant data sample before implementing it.