Overview

Since 2021 US advertisers have spent over $50B on Facebook and Instagram Ads, making up about 25%, on average, of an advertiser's budget. It's an ace in the hole when it comes to driving eCommerce purchases or mobile app signups. But because Facebook Ads is so reliant on machine learning, sending back conversions signals is a crucial part of the platform's effectiveness.

As you probably know, the performance of the pixels that traditionally provide that data has deteriorated in recent years. Pixels don't include events from users with ad blockers, using Incognito mode, or who opt out of cookies and pixels with GDPR / CCPA pop-ups.  

Missing events affect: 

1. The effectiveness of Facebook's ad targeting.
2. The reliability of Facebook's reporting.
3. The ability to retarget customers who have made purchases in your store.

This recipe will focus on sending order data from Shopify to Facebook's Conversions API. While theoretically you could also send other events, such as 'Add to Carts', in reality it can be difficult to know you had an Add to Cart if the user opted out of cookies. Your web analytics tool, such as Google Analytics or Snowplow, was probably blocked by the same lack of consent that blocked the FB pixel. On the other hand, it's easy to know the customer ID and order ID of all your users that made a purchase!  

While a lot of projects using tools like dbt and Census are fine being triggered once a day, CAPI is one that you probably want to fire in more frequent batches. This is especially true if you're not using a pixel (though it's perfectly fine to run both, as long as you include an order ID to de-dupe them). Because of this, it's recommended to adjust the sync of your ETL jobs (for example from Fivetran's Shopify connector) as frequently as your plan allows, and to run the data on order data that hasn't gone through dbt batch processing yet.

Complete control of your conversion data

Many ecommerce platforms let you set up the Conversions API with native integration or plugins, but there are reasons some companies may not want to:

1. Your legal team wants to have greater control over what is sent to Facebook. For example, they may want to leave out users who have filled out a 'Do Not Sell My Data' form. Or they may want to send some user fields but not all, since everything that is sent to Facebook needs to be included in the Privacy Policy.
2. You want to send events that Shopify doesn't. They only send a purchase, but you may have a free trial or newsletter signup on your site (note: this isn't covered in this recipe as it requires more data than just the Shopify connector).
3. You want to have control over what metadata is sent. For example, one company I worked with sent a combination of product tags as the content_category, which the default Shopify implementation would not know it should do.

Syncing conversions from trusted and cleansed first-party data is the only way that you can ensure that you are sending exactly what you want to send to optimize you Facebook Ads targeting.

Use Case

The Facebook Conversion API is very particular about how it wants to receive conversion data. Your data will need some transformations so that everything is lowercase and stripped of special characters. We can also bring in additional product information with a join to product detail tables, and get a phone number from a customers table because many ecommerce platforms (including Shopify) don't store phone on the order billing address.

Pixels send one event per order, not one event per line item, so we'll want to do the same thing with CAPI. This requires us to turn all the products that were ordered into an array, using either listagg or BigQuery's array_agg. Both the hashed and unhashed versions of the formatted fields are included, so you can verify with plain text what names and addresses look like after being transformed. 

Here's the code (shown with Shopify data imported into BigQuery with Fivetran):

<!--MAKE SURE TO CLOSE pre formatting -->

<pre><code class="language-sql">

-- FACEBOOK

WITH consolidated_products

AS (

 SELECT o.id,

  'Product' AS content_type,

  array_agg(l.variant_id) AS content_ids,

  array_agg(l.name) AS content_name,

  array_agg(p.product_type) AS content_category

 --'['''||listagg(l.variant_id,''',''')||''']' as content_ids,

 -- '['''||listagg(distinct l.name,''',''')||''']' as content_name

 FROM my_store.shopify.

 ORDER o

 LEFT JOIN my_store.shopify.order_line l ON o.id = l.order_id

 LEFT JOIN my_store.shopify.product p ON l.product_id = p.id

 GROUP BY 1

 )

SELECT o.id,

 'website' AS action_source,

 'Purchase' AS event_name,

 o.created_at AS event_time,

 o.current_total_price AS value,

 o.browser_ip,

 o.currency,

 regexp_replace(translate(lower(o.billing_address_city), 'ůțąðěřšžųłşșýźľňèéëêēėęàáâäæãåāîïíīįìôöòóœøōõûüùúūñńçćč', 'utaoerszutssyzlneeeeeeaaaaaaaaiiiiiioooooooouuuuunnccc'), '[^a-z]', '') AS city,

 sha256(regexp_replace(translate(lower(o.billing_address_city), 'ůțąðěřšžųłşșýźľňèéëêēėęàáâäæãåāîïíīįìôöòóœøōõûüùúūñńçćč', 'utaoerszutssyzlneeeeeeaaaaaaaaiiiiiioooooooouuuuunnccc'), '[^a-z]', '')) AS city_hash,

 lower(o.billing_address_country_code) AS country,

 sha256(lower(o.billing_address_country_code)) AS country_hash,

 sha256(lower(o.email)) AS email_hash,

 regexp_replace(lower(o.billing_address_first_name), '[~!@#$%^&amp;*()_\\-\\+={\\[\\]}|:;,&lt;.&gt;?/0-9]', '') AS first_name,

 regexp_replace(lower(o.billing_address_last_name), '[~!@#$%^&amp;*()_\\-\\+={\\[\\]}|:;,&lt;.&gt;?/0-9]', '') AS last_name,

 sha256(regexp_replace(lower(o.billing_address_first_name), '[~!@#$%^&amp;*()_\\-\\+={\\[\\]}|:;,&lt;.&gt;?/0-9]', '')) AS first_name_hash,

 sha256(regexp_replace(lower(o.billing_address_last_name), '[~!@#$%^&amp;*()_\\-\\+={\\[\\]}|:;,&lt;.&gt;?/0-9]', '')) AS last_name_hash,

 replace(c.phone, '+', '') AS phone,

 sha256(replace(c.phone, '+', '')) AS phone_hash,

 lower(billing_address_province_code) AS STATE,

 sha256(lower(billing_address_province_code)) AS state_hash,

 left(billing_address_zip, 5) AS zip_code, --FB doesn't want 9 digit zip codes

 sha256(left(billing_address_zip, 5)) AS zip_hash,

 cp.content_ids,

 cp.content_name,

 cp.content_type,

 cp.content_category

FROM my_store.shopify.ORDER o

LEFT JOIN consolidated_products cp ON o.id = cp.id

LEFT JOIN my_store.shopify.customer c ON o.customer_id = c.id

</code></pre>