PII table

Personally identifiable information (PII) is any data that could potentially identify a specific individual. PII data includes details like names, addresses, email addresses, and other profile attributes, but can also include attributes like a loyalty number, customer relationship management (CRM) system identifiers, and foreign keys in customer data.

A PII table collects all of the fields from the Unified Coalesced table that contain PII data, standardizes PII values (as necessary), and then allows querying against any combination of PII data while building segments in the visual Segment Editor.

You can add a PII table to your customer 360 database, and then use it to build queries to support various downstream use cases:

  1. Add PII table

  2. Add query

  3. Add data template

  4. Send results

Add table

Add a PII table using a SQL query that pulls all fields that contain customer profile data from the Unified Coalesced table.

SQL query for PII table

Use a SQL query similar to:

SELECT
  amperity_id AS amperity_id
  ,given_name AS given_name
  ,surname AS surname
  ,full_name AS full_name
  ,generational_suffix AS suffix
  ,email AS email
  ,CASE
    WHEN phone LIKE ''
      OR phone LIKE ' '
      OR phone IS NULL
    THEN NULL
    ELSE CONCAT('+1','',REGEXP_REPLACE(phone,'[^0123456789]',''))
  END AS phone
  ,address AS address
  ,address2 AS address_line_2
  ,city AS city
  ,CASE
    WHEN LENGTH(state) = 2
    THEN state
    ELSE NULL
  END AS state
  ,postal AS postal
  ,birthdate AS birthdate
  ,CASE
    WHEN gender = 'M'
    THEN 'm'
    WHEN gender = 'F'
    THEN 'f'
    ELSE NULL
  END AS gender
  ,company AS company
FROM Unified_Coalesced

The CASE statements do the following:

  • For phone numbers: ensure that only fields with actual phone numbers are added to the PII table, along with consistent formatting. Any phone number that is empty or does not contain a value will not be added.

  • For states: ensure that only values that are two characters (i.e. “WA”, “NY”, “CA”, etc.) are added to the PII table. Any state that is not two characters will not be added.

  • For gender: ensure that only M and F values are added to the table.

Tip

You may use any SQL you want to build this table. For example, you could convert all state values that are spelled out to a two character value. Downstream uses cases should be the main driver for how data is shaped in this table.

Add query

After the PII table is created you can build queries that return records from the PII table. For example, by using a PII table as part of a query, you can query against the widest possible range of email addresses to help identify the specific email address that is used by an individual on Facebook or Google.

To show matching records from a PII table

  1. From the Queries page click Create, and then select SQL Query. This opens the SQL Query Editor.

  2. Define the query using Presto SQL syntax.

  3. Click Run Query and debug any issues that may arise.

  4. Click Activate.

Add data template

A data template defines how columns in Amperity data structures are sent to downstream workflows. A data template is part of the configuration for sending query and segment results from Amperity to an external location.

Use data templates to enable the use of the PII table across a variety of downstream use cases and destinations. For example. you can run the same query against the PII table, and then send results to Facebook, Google, and Braze.

Send results

To send the query results that use fields in the PII table, make sure to use a data template that shapes the results to the destination’s field patterns and that the destination itself is configured correctly. After verifying that the results are sent to the destination correctly, add the workflow to an orchestration and apply a schedule.