360 Values blocklist¶

A 360 values blocklist contains values that should be included in the Stitch process, but excluded from the Customer 360 table. This process prevents data that is unusable in queries, segments, and downstream processes from being added to the customer 360 database.

Before beginning this process it is important to have a good understanding of the Merged Customers table.

Identify values to blocklist¶

Values that are useful for identity resolution can be useless for queries, segments, and other downstream workflows. For example: identity resolution may be able to associate an old and unused email address to an individual (who has many email addresses). However, if an email is sent to that email address, it will bounce and it is useless for an email campaign.

There are many ways to identify these values, and then provide them to Amperity. For example:

A customer may be able to provide a file that can be added to Amperity as a data source that is maintained via a feed. This approach works well when the customer has this data already in a state where it’s actively maintained. (e.g. an automated email bounces log)
Identifying this data via a query in Amperity, and then sending the results of that query to the Amperity SFTP site, from which it can be reloaded as a data source that is maintained via a feed, and so on.

Extend Merged_Customers¶

Add common table expressions (CTEs) for PII semantics.
Find the Unified_Preprocessed section.
Add unified 360 values to the blocklist.

Add CTEs for PII semantics¶

Additional common table expressions (CTEs) must be added to the Merged Customers table to support the 360 values blocklist processes. The recommended location for these CTEs is directly after the Field_Priority table. The recommended naming scheme for these CTEs is semantic_name_blocklist. (e.g. an email address blocklist CTE would be named Email_Blocklist)

In each of these blocklist CTEs we need to have the same common elements.

The PK for the row in Unified Coalesced table that has a value that needs to be blocklisted.
A column that contains a “True” boolean value for later reference.
A LEFT JOIN between the Unified Coalesced table and the source of blocklisted data where an equal comparison can be made.

Note

Use the LOWER() and TRIM() functions to ensure that strings have the greatest likelihood of matching. When working with semantics like phone or address more complex formatting may be required.

Use the following template to create a blocklist:

WITH Semantic_360_Values_Blocklist AS (
  SELECT
    uc.pk
    ,TRUE AS Semantic_360_Values_Blocklist
  FROM Unified_Coalesced AS uc
  INNER JOIN column_name AS alias ON LOWER(TRIM(uc.email)) = LOWER(TRIM(alias.column_name))
)

and then modify it for any PII semantic that appears in the Unified_Structs sections of the Merged Customers table.

Change Semantic_ to the a PII name, such as Email_, Phone_ or Address_.
Change column_name to the name of the column that contains the 360 blocklist values.
Change alias to represent the alias for column_name.

For example, a 360 values blocklist for email that removes email addresses that have bounced. The list of email addresses that bounced are available in a table named Campaign_Bounced.

WITH Email_360_Values_Blocklist AS (
  SELECT
    uc.pk
    ,TRUE AS Email_360_Values_Blocklist
  FROM Unified_Coalesced AS uc
  INNER JOIN Campaign_Bounced AS cb ON LOWER(TRIM(uc.email)) = LOWER(TRIM(cb.Bounced_Email))
)

If there is more than one source of bounced email addresses, extend the common table expression for each source, and then use a UNION statement to combine them into the same 360 values blocklist. For example, a source named CampaignMonitor_Bounced and a source named Mailchimp_Bounced:

WITH Email_360_Values_Blocklist AS (
  SELECT
    uc.pk
    ,TRUE AS Email_360_Values_Blocklist
  FROM Unified_Coalesced AS uc
  INNER JOIN CampaignMonitor_Bounced AS cmb ON LOWER(TRIM(uc.email)) = LOWER(TRIM(cmb.Bounced_Email))
  UNION
  SELECT
    uc.pk
    ,TRUE AS Email_360_Values_Blocklist
  FROM Unified_Coalesced AS uc
  INNER JOIN Mailchimp_Bounced AS mcb ON LOWER(TRIM(uc.email)) = LOWER(TRIM(mcb.Email_Bounced))
)

Find Unified_Preprocessed¶

Typically the code for the 360 blocklist code should be added after any final cleanup is done in the Unified_Preprocessed CTE.

Locate the Unified_Preprocessed section within the Merged Customers table:

,Unified_Preprocessed AS (
  SELECT
    *
  FROM Unified_Coalesced uc
  LEFT JOIN Source_Priority sp ON sp.sp_datasource = uc.datasource
)

You will not make any changes to the Unified_Preprocessed section. The common table expressions for semantics and for Unified_360_Values_Blocklist are added above and below this section.

Add unified values blocklist¶

The Unified_Values_Blocklist common table expression identifies the values to be removed from the customer 360 database. A CASE statement examines the primary key for each row. When the primary key matches the primary key associated with PII data that should be added to the unified values blocklist, the PII data is updated to be NULL. When the primary key does not match, the PII data is passed through.

Below the Unified_Preprocessed section, add the following unified values blocklist template:

WITH Unified_Values_Blocklist AS (
  ,Unified_Values_Blocklist AS (
    SELECT
      -- Fields to which blocklist values ARE NOT applied
      amperity_id
      ,ck
      ,ucl.pk
      ,mc.amperity_id
      ,given_name
      ,surname
      ,full_name
      ,email
      ,phone
      ,address
      ,address2
      ,city
      ,state
      ,country
      ,postal
      ,birthdate
      ,gender
      -- Fields to which blocklist values ARE applied
      ,CASE
        WHEN sgl.Semantic_Values_Blocklist
        THEN NULL
        ELSE ucl.email
      END AS email
    FROM Unified_Coalesced AS ucl
    LEFT JOIN Semantic_Values_Blocklist AS sgl ON sgl.pk = ucl.pk
)

and then modify the list of fields to which blocklist values ARE NOT applied so that any fields to which blocklist values ARE applied are removed, and then update the CASE statement to match the semantic values blocklist template.

Review the list below “– Fields to which blocklist values ARE NOT applied” to remove PII fields that do not have a blocklist.
For each field that does have a blocklist, add a CASE statement below “– Fields to which blocklist values ARE applied”.
Update the LEFT JOIN to match the blocklisted semantic, with one LEFT JOIN per blocklisted semantic.

For example, a unified values blocklist that removes bad email addresses:

WITH Unified_Values_Blocklist AS (
  ,Unified_Values_Blocklist AS (
    SELECT
      -- Fields to which blocklist values ARE NOT applied
      amperity_id
      ,ck
      ,ucl.pk
      ,mc.amperity_id
      ,given_name
      ,surname
      ,full_name
      ,phone
      ,address
      ,address2
      ,city
      ,state
      ,country
      ,postal
      ,birthdate
      ,gender
      -- Fields to which blocklist values ARE applied
      ,CASE
        WHEN egl.Email_Values_Blocklist
        THEN NULL
        ELSE ucl.email
      END AS email
    FROM Unified_Coalesced AS ucl
    LEFT JOIN Email_Values_Blocklist AS egl ON egl.pk = ucl.pk
)

The results of this table are passed to the Unified_Structs section of the Merged Customers table. Values that are NULL are skipped in favor of more complete data to ensure that NULL values are added to the customer 360 database.

If there is more than one semantic blocklist, extend the common table expression by updating the list of fields to which blocklist values ARE NOT applied, adding a CASE statement for each blocklisted semantic, and adding a LEFT JOIN for each blocklisted semantic. For example, email addresses and phone numbers:

WITH Unified_Values_Blocklist AS (
  ,Unified_Values_Blocklist AS (
    SELECT
      -- Fields to which blocklist values ARE NOT applied
      amperity_id
      ,ck
      ,ucl.pk
      ,mc.amperity_id
      ,given_name
      ,surname
      ,full_name
      ,address
      ,address2
      ,city
      ,state
      ,country
      ,postal
      ,birthdate
      ,gender
      -- Fields to which blocklist values ARE applied
      ,CASE
        WHEN egl.Email_Values_Blocklist
        THEN NULL
        ELSE ucl.email
      END AS email
      ,CASE
        WHEN epl.Phone_Values_Blocklist
        THEN NULL
        ELSE ucl.phone
      END AS phone
    FROM Unified_Coalesced AS ucl
    LEFT JOIN Email_Values_Blocklist AS egl ON egl.pk = ucl.pk
    LEFT JOIN Phone_Values_Blocklist AS epl ON epl.pk = ucl.pk
)