How to create a Facebook custom audience from Shopify

Facebook custom audiences are great for retargeting and creating lookalikes of your best customers, but updating them manually is unsustainable.  You could create audiences based on the pixel on your website instead, but that has a number of limitations like:

1. Missing data from ad blockers, GDPR/CCPA opt outs and Incognito mode, unless you also implement the Facebook Conversions API.
2. Cookie-based audiences can only look back a up to 180 days, but you may want to retarget customers from prior years.
3. It can’t apply advanced logic, such as ‘I want customers who have spent $50 in this category, and $300 overall’.

This recipe will show you how to you create audiences based on customer data in your data warehouse. For the purposes of this example, we'll use standard fields exactly how they’re brought in by the Fivetran Shopify connector. Unless you're using Shopify and Fivetran, you're data will look different but this should still be relevant with a few tweaks to match the terminology in your orders, products, and customers tables.  If you know a bit of SQL or can borrow a data team member’s time for even a few minutes, you should be up and running with Facebook Custom Audiences in no time!

This recipe lets you filter on the following parameters:

1. Specific kinds of products: their title, handle (ie yourstore.com/products/handle), vendor, and type.
2. How recently the customer made an order in that product/type.
3. How much the customer spent on the specific product/type.
4. How much the customer has spent on your store in general.
5. Whether your customer accepts e-mail marketing.

‍

The Facebook Marketing API is very particular about how it wants to receive customer data. Your data will need some transformations so that everything is lowercase and stripped of special characters. It's best practice to create a view of your Customers table that transforms.

When you do this yourself, your imagination should be your guide as to what dimensions you use to define your audience and what criteria you use to segment them. With Census you can easily create multiple audiences based on a single base audience with Census Segments.

Let's make an audiences!

This transformation does three things

1. Joins orders to line items to products and pulls in customers who have ordered specific kinds of products.
2. Joins customers to addresses, hashing PII to make your legal team happy and cleaning city names to make the Facebook API happy.  They want São Paulo to be saopaulo.
3. Selects a final list of customers who meet a minimum threshold of spend in the target products as well as total spend across all products on your store.

WITH relevant_orders
AS (
 SELECT customer_id,
  sum((l.price * l.quantity)) AS target_spent
 FROM FIVETRAN.SHOPIFY."ORDER" o
 LEFT JOIN FIVETRAN.SHOPIFY.ORDER_LINE l ON o.id = l.order_id
 LEFT JOIN FIVETRAN.SHOPIFY.PRODUCT p ON l.product_id = p.id
 WHERE o.created_at > CURRENT_DATE () - 30
 --just comment out the filters you don't want to use
 --and p.title in ('')
 --and p.vendor in ('')
 --and p.handle in ('')
 --and p.product_type in (‘’)
 GROUP BY 1
 ),
customers
AS (
 SELECT c.id,
  --in BigQuery, use sha256(lower(c.email)) instead of sha2
  sha2(lower(c.email), 256) AS email_hash,
  sha2(regexp_replace(lower(c.first_name), '[~!@#$%^&*()_\\-\\+={\\[\\]}|\:;''",<.>?/0-9]', ''), 256) AS first_name_hash,
  sha2(regexp_replace(lower(c.last_name), '[~!@#$%^&*()_\\-\\+={\\[\\]}|\:;''",<.>?/0-9]', ''), 256) AS last_name_hash,
  sha2(replace(c.phone, '+', ''), 256) AS phone_hash,
  c.total_spent,
  lower(a.country_code) AS country_code,
  lower(a.province_code) AS province_code,
  regexp_replace(translate(lower(c.city), 'ůțąðěřšžųłşșýźľňèéëêēėęàáâäæãåāîïíīįìôöòóœøōõûüùúūñńçćč', 'utaoerszutssyzlneeeeeeaaaaaaaaiiiiiioooooooouuuuunnccc'), '[^a-z]', '') AS city,
  left(zip, 5) AS zip_code --FB doesn't want 9 digit zip codes
 FROM FIVETRAN.SHOPIFY.CUSTOMER c
 LEFT JOIN FIVETRAN.SHOPIFY.CUSTOMER_ADDRESS a ON c.id = a.customer_id
 WHERE a.is_default = true
 )
SELECT email_hash,
 first_name_hash,
 last_name_hash,
 phone_hash,
 target_spent,
 total_spent,
 country_code,
 province_code,
 city,
 zip_code
FROM customers c
INNER JOIN relevant_orders o ON c.id = o.customer_id
WHERE target_spent > 50
 AND total_spent > 100
 --maybe you don't want to pay for FB remarketing ads for people on your email list...
 AND c.accepts_marketing = false

Facebook Ads

Shopify