By default, Confection stores data for 24 hours. During that timeframe, it’s available in your account to query and send anywhere via your account API, one of our lowcode/nocode connectors, and/or our native integrations.

For Google Cloud users, we also offer a native BigQuery storage integration. If you activate this option, Confection will migrate your account data there as a JSON object that includes a UUID and all the data associated with it.

Each month, you can write up to 10 GB of data into BigQuery and make 1TB worth of queries for no charge. This gives users an affordable long-term storage option.

To set up your Confection<>BigQuery integration, please see this quick start guide. If you've already set this up, keep reading to discover two power tips from one of our customers.

Feed Any Endpoint Forever

With a simple Zapier recipe, feed long-term UUID-based data to any number of different endpoints. Use a simple conditional statement to route events or lead data to custom audiences, remarketing campaigns, CRMs -- the list goes on and on.

You can also listen for BigQuery rows that contain new information about existing UUIDs. For example, you might have a long-term attribution window. Use this recipe to write additional intel to a CRM or custom audience when new data is available (even if it takes months).

Zapier doesn't allow the sharing of Zaps that contain their code action. As a workaround, the video should help you get up and running with both scenarios.

Here's the JS for the Zapier Code step:

let PARSED = JSON.parse(inputData.RAW);
output = [{PARSED}];

And here's the SQL WHERE clause in the "Find Row(s) in Google BigQuery" step:

WHERE uuid = '%UUID HERE%'

It's populated by the Parsed UUID variable from the second, "Run Javascript in Code by Zapier" step.

Learn more about getting started with Confection on Zapier here. Learn more about writing data from Confection to BigQuery here.

Powerful Routing and Segmentation

Most Confection customers route data from one domain into their accounts. Some of our customers, however -- especially our agency customers -- manage multiple domains with one account. As the first video shows, parsing and routing data from multiple domains is easy using Confection's URL endpoint. However, if you're a large agency with many customers, you may not want to spend money on Zap tasks each time anything happens to any domain. You may also want to bill individual customers for Zap tasks or you might be using a customer's Zapier account.

In these cases, agencies may want to create individual tables for each client account and query those using individual Zaps or Zapier accounts. Doing so is relatively easy in BigQuery.

Here's the SQL query for the first step in the video:

SELECT * FROM `%PROJECT-NAME.confection.TABLE%` WHERE CONTAINS_SUBSTR ( data , '%RELEVANT DATAPOINT%' )

For example, if you your project is named abc-123, you're using Confection's default BigQuery table, and you want to create a table for data coming from xyz.com, your SQL query would read:

SELECT * FROM `abc-123.confection.leads` WHERE CONTAINS_SUBSTR ( data , 'xyz.com' )

In the scheduling step, we suggest selecting "Append to table." This will add to the historical data that already exists in the domain-specific table. If you'd prefer to drop all existing data, select "Overwrite table."

Questions? Have your own ideas? Schedule a meeting now.