How to Join Two Lists with Power Audiences

The power in Power Audiences is the ability to join two lists from the Lists section using SQL.

This gives you the ability to combine CRM data with transaction data to get a fuller picture of an audience of interest and be able to serve them the most relevant ads.

Getting Started

1. You will want to reference the headers for the two (or more) lists that you will be joining

   1. You will want to make sure there’s a customer ID (usually email) in both lists that will be your key attribute when combining the lists

   2. You will also need to declare the headers for the Power Audience, so referencing the lists to combine will help identify which attributes are most relevant for the Power Audience you’d like to create.

The below example shows how to join a transaction list containing purchase information and user email with a data list containing product information.

Instructions

1. Navigate to your client and the Power Audiences section in Audiences

   

2. Click the “Create New Power Audience” button and enter a name and description for your power audience

   

3. Enter the attributes associated with your power audience

   

4. Enter the SELECT clause of your SQL (or any/all clauses except the tables you need to select from) NOTE: sort_order must be present in the outer SQL statement and each attribute defined in the audience (except created_at and sort_order) should be prefixed by “attr_”

   

5. Click the “CRM List Reference” button to see references to the audience tables

   

6. Click the link and paste (Mac: ⌘+V, Windows: CTRL+V) the table into the FROM clause

   

7. Repeat as necessary for other tables.

   

8. Specify the name of each table

   1. In this case, the data table is named ‘data’ and the transaction table is named ‘txn’.

9. Add table names to each attribute to indicate where each column is coming from.

   1. Please note: Because sort_order and created_at automatically exist for each audience list, you must specify one of your tables for these, though it does not matter which one.

10. Add a WHERE clause to indicate overlapping columns in your two reference tables

11. Click Save & Preview to view the results

    1. Please note: This may take some time depending on your table sizes.

       

Nice! You now have a Power Audience that is the combination of these two lists.

From this specific Power Audience, you could then create audience segments targeting purchasers of specific products using 'product_name' or who purchased a certain amount using ‘total’.

Related articles