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

How to do Quality Checking on Profile Stitching

Often times, it is not easy to see problems with profile stitching until it has finished running on a large portion of the dataset. This is why it is necessary to do quality checks on the data before loading the entire dataset. This article explains how to do so.

Before looking into the database though, try looking at the Analytics tab on Business Explorer about suspicious profiles as an initial check. There, suspicious profile entities with events >= 10,000 will be listed down, which can help identify if the stitching rules in place are over-stitching customer events together, and you can drill down into the database with those particular customer entities to investigate. Look into the edge cases to see what situations might arise that lead to problematic stitching rules.

In general, these are the steps you want to follow:

  1. Load sample data: load 1 week of data (adjust this to be more or less depending on the data volume) from all sources
  2. Set up profile stitching as per defined from the profile stitching graph
  3. Run the set of queries below
  4. Summarize the output for review
  5. Adjust the stitching rules or add additional cleaning steps during data processing if found to be necessary. Refer to things to look out for in profile stitching rules here.

List of Queries

Number of stitched entities

- A rough gauge on number of customers 

Average number of events per entity

- Dependent on the type of events

SELECT sum(customer_events_count)::numeric / COUNT(*)::numeric as event_num_avg
FROM public.customer_attributes;

Check the largest entities

- 100 biggest entities for false positive stitching and catching edge cases. For example, customers stitched together because their emails are blank or whitespace, or generic emails like abc@gmail.com, foo@bar.com are used.

SELECT customer_entity_id, customer_events_count FROM public.customer_attributes ca
GROUP BY 1
ORDER BY 2 DESC
LIMIT 100;

Number of entities from different sources

- Check if the number of entities make sense. For example, entities from web traffic should be > EDM sources > Order transactions

SELECT source_id, count(*)
FROM (
	SELECT customer_entity_id, unnest(sources) source_id
	FROM customer_attributes ca 	
     ) foo
GROUP BY 1;

Number of unique identifiers per entity 

- Ideally, each entity should have 5 or less of each identifier (5 devices, 5 emails, 5 phone numbers).

-  The attributes in this query like  mx_email_list  and mx_meiro_user_id_list needs to be built beforehand, according to your stitching rules, they do not come by default. This query will take some time to run.

select 
customer_entity_id, 
customer_events_count, 
array_length(mx_email_list, 1) as emails,
array_length(mx_meiro_user_id_list, 1) as meiro_user_ids
from customer_attributes ca 
group by customer_entity_id
order by customer_events_count desc
limit 10;

Histogram of events per customer entity

SELECT customer_events_count, count(*) customer_entities
FROM (
      SELECT customer_entity_id, customer_events_count
      FROM customer_attributes ca
      GROUP BY 1
     ) foo
GROUP BY 1
ORDER BY 1

Check if all events have identifiers

When both cdp_ps.customer_events_to_process and cdp_ps.prep are empty: check count(*) from cdp_ps.matching = count(*) from cdp_ce.customer_events

Edge cases

A lot of the time, there are problems that cannot be identified by these basic queries, for these edge cases, check this article.


Back to top