Advanced Power Audience SQL Query

Overview

This document walks through creating a more advanced power audience SQL query.

Instructions

Make sure you are following power audience SQL requirements.

We'll switch over to the Cirque du Soleil client for this, we'll be selecting from multiple tables and joining the data so it is accessible as a single audience.

The Power Audience we created has three attributes: email_address, gender, and location.
Our criteria are that we only want to select past buyers from the Toronto market, but we also need their gender which doesn't exist within the same table.
Thus, we join the two tables on email_address and select the required columns. Here's what that query might look like:

CODE

SELECT created_at, 
       CURRENT_TIME() as updated_at, 
       sort_order, 
       past_buyers.attr_email_address, 
       past_buyers.attr_market AS attr_location, 
       customer_gender.attr_gender
FROM 
       ( 
              select      * 
              from   { crm-f548532e-fb95-4312-a6d1-1b1a164d1603 } 
              WHERE  attr_market ilike '%toronto%') AS past_buyers 
LEFT JOIN 
       ( 
              SELECT      * 
              FROM   { crm-4575c5a6-173b-45db-84a1-85cf6c2f37e0 } 
              WHERE  attr_gender = 'male' ) AS customer_gender 
ON     
       customer_gender.attr_email_address = past_buyers.attr_email_address 
)

Save and preview to make sure your queries are running correctly.

As you can see from the above query, we have fulfilled all our requirements:

We supply the 3 necessary base columns: created_at, updated_at and sort_order.
We transform the attr_market to attr_location which is what our power audience column header is.
We also return the attribute columns with attr_ - prepended attr_email_address, attr_gender, and attr_location.