# DDA 300: Audience Overlaps with Data Distiller ## Getting Started Every day, a snapshot of the Profile attributes for every merge policy is exported to the data lake. These system datasets are hidden by default but are accessible by toggling these datasets in the data catalog. These datasets contain information about the profile attributes, the identity map, and the segment membership as reflected at the time of creating the snapshot. The examples below show how you can explore this dataset to understand identity composition and even create exotic segment overlaps. To first access, the Profile Snapshot datasets, navigate to Datasets and click on the filter to show system datasets. Turning this filter on will show system datasets along with others. You need to search for "profile attribute" to filter down the list. Profile attribute snapshot datasets are exported for every merge policy in the system. In the example below, there are 3 merge policies and hence there are 3 datasets. These datasets will typically look like the following unless their name has been changed: `profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903`

Even if the name has been changed, you should be able to query the dataset: ```sql SELECT * FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` The columns you will see in the result will look like the following. If you see *identityMap* and *segmentMembership* fields, then you are on the right track.

Scrolling to right, you will see *identityMap* field and then the *segmentMembership*.

There are some other interesting datasets that are generated on a daily basis by the system. Explore some of these on your own. ```sql select * from br_segment_destination; ```

This will give you the list of destinations that have been mapped to a segment. Note that each row contains one such mapping and that a single destination can have multiple segments and vice versa. Also, note that *segmentnamespace* refers to the source of the segment i.e. whether it was created in AEP or elsewhere. You may also see a dataset that contains the segment information. Search for a dataset that has segment definition in the schema type and you should be able to locate this dataset ```sql select * from profile_segment_definition_6591ba8f_1422_499d_822a_543b2f7613a3 ``` The results of the query look like the following:

Segment ID to segment mapping raw table.

And finally the destinations ID to account name mapping is available in this system dataset: ```sql select * from dim_destination; ```

Destination ID to Destination Account Name mapping

Let us explore another dataset that gives us information about what identities are available for mapping to a destination. ```sql select * from br_namespace_destination order by destinationID ``` The result is:

Source identity namespaces that could map to the destination identity namespace.

The result shows that for a given target identity (namespace) available in the destination, there are multiple source identity options, many of which are not used (*isMapped = false*). These identities were sourced by the profiles that were part of the audience which in turn was powered by the identity graph. {% hint style="info" %} **Tip:** Note that if an identity is missing at the source which is mapped to a destination field, that identity is dropped from being sent. There are other identities of the same profile that may have non-zero values and they will get sent to the destination. Activation to a destination is essentially a process of identity disaggregation. {% endhint %} {% hint style="warning" %} **Warning:** The Profile attribute export dataset does not contain profiles that have more than 50 identities. In order to avoid losing these profiles, you need to contact Adobe to configure the identity graph so that it retains the most recent identities belonging to a cookie namespace. {% endhint %} Just for fun, let us see what fields are being used in our destinations in my test environment: ```sql select * from br_namespace_destination where isMapped='true' order by destinationID ``` The results are:

Deestination identity fields being used in my environment.

## Find Merge Policies in the Snapshot The following tables are created from the Profile Attribute Snapshot in the reporting star schema for the Real-Time Customer Profiles. We will write queries against these tables to get answers that will be {% code fullWidth="false" %} ```sql SELECT * FROM adwh_dim_merge_policies; ``` {% endcode %}

{% hint style="info" %} Note that there is no table name to merge policy mapping in this star schema. You need to run the first query to determine the merge policy and identify the *dataset\_id*. Use that to search the dataset name in Univeral Search at the top and not local dataset search. {% endhint %}

Copy the *dataset_id* from the query result

You can get the *dataset\_id* to the *dataset name* by typing the following and browsing down the list of results: ```sql SHOW TABLES ```

Another approach would be to copy and paste that into the search bar at the top in the AEP UI.

Dataset name is available for dataset id typed into Univeersal search

The dataset name will reveal itself. Click on the dataset to get the table name which you will need as the table name for all your queries.

You should be able to start exploring the profile snapshot datasets immediately. {% code overflow="wrap" %} ```sql SELECT * FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %}

{% code overflow="wrap" %} ```sql SELECT person.name.lastName FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903 ``` {% endcode %}

## **Retrieve Segment Information** ```sql SELECT * FROM adwh_dim_segments; ```

## **Count Profiles by Merge Policy** Merge policy is an "MDM-like" feature that lets you prioritize attribute records whenever there is a conflict i.e. either use one dataset over the other or use the timestamp to resolve that conflict. Mostly, you will see that timestamp precedence is used as the source of truth is typically a CRM system that is evolving over time, and new attribute records with updates get added to the Real-Time Customer Profile. The new records need to take precedence. {% code overflow="wrap" %} ```sql SELECT COUNT(identityMap) FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %}

If you navigate to the Profiles->Overview page, for the same merge policy, you will see the same count. Note that we had chosen ***Default Time-based*** merge policy in this example in our environment.

Profiles Overview page has merge policy filter that gives you the same count.

## **Use EXPLODE to Separate Identities in Separate Rows** {% code overflow="wrap" %} ```sql SELECT Segmentmembership, explode(identitymap) from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %}

{% hint style="warning" %} This splitting into rows destroys Identity map association. {% endhint %} Before you extract the identity information, you need to understand the map structure. Use the to\_json feature in Data Distiller to get in place information about the schema structure without having to go to the Schemas UI screen. This is an extremely important feature with deeply nested data structures and maps. {% code overflow="wrap" %} ```sql SELECT to_json(identityMap) FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %} If you execute the following, you will see the following:

What you see above is the identity map structure. The map has the identity namespace (email, crmid) as the unique key to index the values. Note that this is an array of values. If you need to extract the identities, you will need to use the following code: {% code overflow="wrap" %} ```sql SELECT identityMap.email.id FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %} The results look like the following:

Typically we would explode this array {% code overflow="wrap" %} ```sql SELECT explode(identityMap.email.id) FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %}

Exploding the array helps you extract the identities.

But there is a problem - plain vanilla "explode" will get rid of rows that do not have any identity values. That is a problem because the absence of an identity is a signal and is possibly associated with other identities that the person may have. Let us fix the issue by using *explode\_outer* from Data Distiller: {% code overflow="wrap" %} ```sql SELECT explode_outer(identityMap.email.id) AS email FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903 ``` {% endcode %}

explode_outer retains the nulls for the identity namespaces

But just doing an *explode\_outer* on a single identity namespace is of no use. We have destroyed the association between identities within that same namespace. Let us generate a synthetic UUID for each profile row to keep track of our identities as we explode them. If we do this right, we will generate a table of UUIDs and related identities as a columnar table that gives us leverage to use it for a variety of operations. If we take the first element of the email namespace and concatenate that with the first element of the crmid namespace, we are guaranteed a unique identifier. If not, it would mean that two rows in the profile export dataset have that identity in common. The identity stitching in Real-Time Customer Profile should have taken care of it and merged it into a single row. Let us now generate the unique UUIDs along with the schema-friendly identity map structure {% code overflow="wrap" %} ```sql SELECT concat(identityMap.email.id[0],identityMap.crmid.id[0]) AS unique_id,to_json(identityMap) from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903 ``` {% endcode %} The results will look like:

Generate the unique ID without llosing identity associations.

But *concat* string function does a poor job at concatenating NULL values. We have to remedy that with COALESCE: {% code overflow="wrap" %} ```sql SELECT COALESCE(identityMap.email.id[0],identityMap.crmid.id[0]) as unique_id,to_json(identityMap) from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903 ``` {% endcode %} This works and you will get:

COALESCE function retrieves the first non-zero value in a list and is a good choice for a unique ID for the profile in our example.

If there are two identity namespaces, then the *explode\_outer* operator works on each, one at a time. Make sure you remove the *to\_json* as we do not need it anymore: {% code overflow="wrap" %} ```sql SELECT unique_id, explode_outer(identityMap.email.id) AS email, identityMap.crmid.id AS crmid FROM (SELECT coalesce(identityMap.email.id[0],identityMap.crmid.id[0]) AS unique_id,identityMap from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) ``` {% endcode %} You will get:

*email* identities have been separated into separate rows without breaking profile association.

We need to explode this one more time for *crmid* and we should get: {% code overflow="wrap" %} ```sql SELECT unique_id, email, explode_outer(crmid) as crmid FROM ( SELECT unique_id, explode_outer(identityMap.email.id) AS email, identityMap.crmid.id AS crmid FROM (SELECT coalesce(identityMap.email.id[0],identityMap.crmid.id[0]) AS unique_id,identityMap from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) ) ``` {% endcode %} The results would be:

Identity lookup table in relational form

Using this table, I can do some very interesting analysis. For example, we can look at the histogram of email IDs in the system: {% code overflow="wrap" %} ```sql SELECT bucket, count(unique_id) FROM( SELECT unique_id, count(CASE WHEN email IS NULL THEN 0 ELSE email END) as bucket FROM( SELECT unique_id, email, explode_outer(crmid) as crmid FROM ( SELECT unique_id, explode_outer(identityMap.email.id) AS email, identityMap.crmid.id AS crmid FROM (SELECT coalesce(identityMap.email.id[0],identityMap.crmid.id[0]) AS unique_id,identityMap from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) )) GROUP BY unique_id ORDER BY bucket ASC) GROUP BY bucket ``` {% endcode %} The answer looks like this:

Why are we seeing 976 as a bucket and 0 counts for unique\_ids? The sum of 8225 and 976 adds up to the number of profiles. Why did we even get this result in the first place? Is there something wrong with the way the SQL is written or is there something more profound happening in the identity graph? Still cannot figure it out? What if the *email* and *crmid i*dentities were non-existent? You have null rows in the table that you should clean before you do any analytics. For the identity lookup use case which is the goal of this section, it would not matter, ## **Extract Identities Without Breaking Segment Membership Association** In our example, we will be simplifying the use case since we do not have multiple identities. We will be just using the first element to create a simple map. {% code overflow="wrap" %} ```sql SELECT identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %}

{% code overflow="wrap" %} ```sql SELECT Segmentmembership, identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %}

## Expand Segment Membership {% code overflow="wrap" %} ```sql SELECT explode(Segmentmembership), email, crmid FROM (SELECT Segmentmembership, identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903)sq ``` {% endcode %}

## Create Segment Membership, Email, and CRMID Triples {% code overflow="wrap" %} ```sql SELECT key AS segment_id, email, crmid FROM ( SELECT explode(value), email, crmid FROM ( SELECT explode(Segmentmembership), email, crmid FROM (SELECT Segmentmembership, identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) ) ); ``` {% endcode %}

## **Resolve Segment ID to Segment Name** The segment\_id needs to be resolved to a segment name. Note that I have to name the subqueries in order to reference them in the INNER JOIN clause. I am also creating a unique UID by concatenating the ID values {% code overflow="wrap" %} ```sql SELECT segment_name, email, crmid, concat(email, crmid) AS UID FROM ( SELECT key AS segment_id, email, crmid FROM ( SELECT explode(value), email, crmid FROM ( SELECT explode(Segmentmembership), email, crmid FROM (SELECT Segmentmembership, identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) ) ) ) AS A INNER JOIN adwh_dim_segments AS B ON A.segment_id=B.segment; ``` {% endcode %}

## **Write Query Result to Data Lake** Materialize this table to the data lake. Use the DROP TABLE clause to wipe out an existing table {% code overflow="wrap" %} ```sql DROP TABLE IF EXISTS segment_data; CREATE TABLE segment_data AS (SELECT segment_name, email, crmid, concat(email, crmid) AS UID FROM ( SELECT key AS segment_id, email, crmid FROM ( SELECT explode(value), email, crmid FROM ( SELECT explode(Segmentmembership), email, crmid FROM (SELECT Segmentmembership, identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) ) ) ) AS A INNER JOIN adwh_dim_segments AS B ON A.segment_id=B.segment); ``` {% endcode %}

Explore what is contained in the dataset. We should see 13K rows ```sql SELECT * FROM segment_data; ```

Identify records that have NULL crmid values ```sql SELECT * FROM segment_data WHERE crmid IS NULL; ```

## **Audience Size Calculation** Here we will count the number of people vs what I see in the UI. There are some segments that are zero and that do not agree with UI. What do you think happened? ```sql SELECT segment_name, count(UID) FROM segment_data GROUP BY segment_name ```

Do not materialize the dataset. Instead, let us fix this with COALESCE {% code overflow="wrap" %} ```sql SELECT segment_name, count(DISTINCT UID) FROM (SELECT segment_name, email, crmid, concat(COALESCE(email, ''), COALESCE(crmid, ''))AS UID FROM ( SELECT key AS segment_id, email, crmid FROM ( SELECT explode(value), email, crmid FROM ( SELECT explode(Segmentmembership), email, crmid FROM (SELECT Segmentmembership, identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) ) ) ) AS A INNER JOIN adwh_dim_segments AS B ON A.segment_id=B.segment) GROUP BY segment_name; ``` {% endcode %}

We still do not materialize because we need to check the count. It should be 8,225 in our case. {% code overflow="wrap" %} ```sql SELECT count(DISTINCT UID) FROM (SELECT segment_name, email, crmid, concat(COALESCE(email, ''), COALESCE(crmid, '')) AS UID FROM ( SELECT key AS segment_id, email, crmid FROM ( SELECT explode(value), email, crmid FROM ( SELECT explode(Segmentmembership), email, crmid FROM (SELECT Segmentmembership, identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) ) ) ) AS A INNER JOIN adwh_dim_segments AS B ON A.segment_id=B.segment); ``` {% endcode %}

The above query result does not agree with what we see below. Where is the difference? Exploding *segmentMembership* eliminated unsegmented profiles. {% code overflow="wrap" %} ```sql SELECT COUNT(identityMap) FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903; ``` {% endcode %}

{% code overflow="wrap" %} ```sql SELECT COUNT(identityMap) FROM profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c WHERE Segmentmembership IS NULL903 ``` {% endcode %}

If you add 6500 (profiles attached to at least one segment)+1725 (profiles not attached to any segment), you get 8225 which is our count. ## **Generate Segment Name to Identities Table** {% code overflow="wrap" %} ```sql DROP TABLE IF EXISTS segment_data; CREATE TABLE segment_data AS ( SELECT segment_name, email, crmid, concat(COALESCE(email, ''), COALESCE(crmid, '')) AS UID FROM ( SELECT key AS segment_id, email, crmid FROM ( SELECT explode(value), email, crmid FROM ( SELECT explode(Segmentmembership), email, crmid FROM (SELECT Segmentmembership, identitymap.email.id[0] AS email, identitymap.crmid.id[0] AS crmid from profile_attribute_cd03f195_66b5_4a62_a6f9_c606e951c903) ) ) ) AS A INNER JOIN adwh_dim_segments AS B ON A.segment_id=B.segment); ``` {% endcode %}

Let us create some analytical queries {% code overflow="wrap" %} ```sql SELECT segment_name, count(DISTINCT UID), COUNT(DISTINCT email) AS count_email, COUNT(DISTINCT crmid) AS count_crmid FROM segment_data GROUP BY segment_name; ``` {% endcode %}

Double-check: ```sql SELECT * FROM segment_data; ```

```sql SELECT COUNT(DISTINCT UID) FROM segment_data; ```

## Generate Emails Associated with a Segment Name {% code overflow="wrap" %} ```sql SELECT segment_name, array_agg(email) as email_list, array_agg(crmid) as crm_list FROM segment_data GROUP BY segment_name; ``` {% endcode %}

## Generate Segments Associated with an Email Address ```sql SELECT email, array_agg(segment_name) FROM segment_data GROUP BY email ```

## 3 Segment Overlap {% code overflow="wrap" %} ```sql SELECT * FROM (SELECT UID, array_agg(segment_name) AS seg_array FROM segment_data GROUP BY UID) WHERE array_contains(seg_array, 'United States') AND array_contains(seg_array, 'Saurabh New') AND array_contains(seg_array, 'Winter wear') ``` {% endcode %}

## 4 Segment Overlap {% code overflow="wrap" %} ```sql SELECT * FROM (SELECT UID, array_agg(segment_name) AS seg_array FROM segment_data GROUP BY UID) WHERE array_contains(seg_array, 'United States') AND array_contains(seg_array, 'Saurabh New') AND array_contains(seg_array, 'Winter wear') AND array_contains(seg_array, 'Male Gender Test Segment') ``` {% endcode %} Even before we typed this query, we should have been able to predict the kinds of answers we would have gotten. The lowest-sized audience will give you the upper limit on the overlap. Can you figure out which of these segments is that? However, if we execute the query, we see the not-so-surprising result.