How-to join two audience lists together using Power Audiences

While technically you can use a variety of SQL functions to join two lists, this explainer articulates a simple, consistent approach to reduce SQL errors when creating power audiences.

For the initial migration – make sure internal_id has been removed and replaced with sort_order.

Instructions

1. Use the following SELECT / FROM / WHERE clauses to specify attributes of interest when combining two lists.

   

   1. LEFT JOIN is possible but not recommended. This is because it pulls in more data than is needed and is an overall slower operation, thus slowing down activation times.
      

2. SELECT clause explained

   1. The SELECT clause specifies what columns you are pulling from each list.

      

   2. created_at and sort_order are automatically generated for each list. You will need to specify them from one of the tables, though it does not matter which one.

      

   3. NOW() as updated_at

      1. updated_at is an automatically generated attribute for the Power Audience list.

      2. Make sure to include NOW() as updated_at in your SQL to avoid an error

         

   4. Key Attribute

      1. If you’re combining two lists, there will be a key attribute you index on to combine (usually email).

      2. If you want to rename an attribute to be different in the Power Audience from what it was called in its original list, use as to specify that name change

         

   5. You will need to specify from which list each attribute is being referenced

      1. e.g. not attr_myAttribute but list1.attr_myAttribute

         

3. FROM clause explained

   1. Use the FROM clause to declare from which list where attributes of interest are coming from

      

   2. You can use the CRM List Reference button to easily find the right lists to put in the { }

      

   3. Whatever you name your lists here will need to be referenced for each attribute in your SELECT clause
      

4. WHERE clause explained

   1. This clause specifies your key attribute(s) that overlap in the two tables you are combining

      

   2. Simply set your key attribute from both columns equal to each other

   3. You can also use the WHERE clause to build segments into your Power Audience (e.g. filtering by purchasers of specific product categories or last purchase date).

You should now understand the logic behind the SQL requirements of Power Audiences.

Deprecated Functions

1. internal_id used to be the method for identifying the key attribute shared between lists. This logic has been removed. Use the attribute that determines the uniqueness of your audience instead.

   1. For CRM or Transaction lists, this is the first email, phone, or mobile_id attribute

   2. For Data lists this is the combination of the unique_id_attributes that were specified

2. external_id has also been removed