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

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).

The 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 this:

	"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.


  • 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:

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

Trim the ID:

TRIM(payload ->> 'phone')

Or use it all together:

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

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.