BigQuery Power Tips

Recently, a Confection customer shared two power tips with us. Use them to make the most of your Confection<>BigQuery integration.

Read More

Subscribe

We'll send updates directly to your email inbox.

  • Hidden
 

Learn more about marketing disruptions involving cookies, cross-domain scripts, and device IDs. Get tips for using your Confection account effectively. Read customer experiences.

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.

Continue Reading

Subscribe

Educating yourself about privacy first disruptions and opportunities? Keeping an eye on the future of cookies, cross-domain scripts, and device IDs? We can help. Subscribe, and we’ll send content like this directly to your email inbox.

  • Hidden

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.

Get Started

Live Dataflow
Learn More

Next Steps

Confection collects, stores, and distributes data in a way that's unaffected by client-side disruptions involving cookies, cross-domain scripts, and device IDs. It's also compliant with global privacy laws so it’s good for people too.

See Confection in Action (Risk Free)

Get Started (Risk Free)

"I want to see a demo."

Let’s Go

"How does privacy first affect me?"

Find Out

"I have some questions."

Contact Us

"I want to see this."

Share It

"How much does this cost?"

View Pricing Guide

Discussion

Questions or comments? Leave them below.

Today is your data independence day.

Get started. Risk free.