Breadcrumbs

Advanced Guide: Join Two Lists with Power Audiences

This guide covers how to join a Transaction List and a Data List using 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.

Screen Shot 2022-07-14 at 3.03.09 PM.png


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

    Screen Shot 2022-07-14 at 3.03.22 PM.png


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

    Screen Shot 2022-07-14 at 3.03.49 PM.png


  3. Enter the attributes associated with your power audience

    Screen Shot 2022-07-14 at 3.04.19 PM.png

  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_”

    Screen Shot 2022-07-14 at 3.05.01 PM.png


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

    Screen Shot 2022-07-14 at 3.05.08 PM.png


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

    Screen Shot 2022-07-14 at 3.05.17 PM.png


  7. Repeat as necessary for other tables.

    Screen Shot 2022-07-14 at 3.05.08 PM.png


  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.

      Screen Shot 2022-07-14 at 3.06.36 PM.png

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’.