Tutorials｜BigQuery Connector

Creation Date: October 31, 2023

Created By: Crescender Lab

💁‍♀️ Advantage

Effective analysis of cross-channel marketing performance
Achieving personalized cross-channel communication
Creating multi-dimensional, precise audience groups
Integrating a 360º consistent customer profile

1. What is BigQuery Data Exporter？

Let's start with a simple understanding of what BigQuery is. BigQuery is a cloud-based data analysis service introduced by Google Cloud. It can handle data analysis operations at a scale of petabytes (PB). Some of the services you use in your daily life, like Google Search and Google Ads, rely on BigQuery as a core technology for data processing and analysis. BigQuery also comes with built-in machine learning capabilities, allowing users to perform more in-depth data analysis according to their needs.

4 main advantage of BigQuery

Fast: BigQuery is incredibly speedy. It can retrieve or analyze data at the scale of terabytes or petabytes in just seconds.
Versatile: It supports a variety of Business Intelligence (BI) tools and allows integration with third-party applications to enhance data analysis.

In addition to the above two major advantages, BigQuery uses SQL-like syntax to make it easier for users to query data. Data access is also encrypted to strengthen the security mechanism. As for the DR (Disaster Recovery) capability that everyone is most concerned about, BigQuery provides a wide range of data. Copy function to avoid the risk of data loss.

Source：CloudMile-What is BigQuery? Different from traditional warehousing, 4 major advantages help you analyze big data!

▶️ Setting steps (taking Emarsys as an example)

If any data-related platform receives BQ data, it can be connected to the Crescendo Labs BQ Connector. Here are six common platform connection instructions for reference:
BI system: Power BI, Tableau, Metabase

CDP: Emarsys, Treasure data, Insider

ℹ️ Please confirm that you have already obtained the following data from Crescendo Lab Team before proceeding with the setup on your existing platform:GCP project idDataset idClient email addressSecret.json file

# Log-in Emarsys Marketing Platform

2. Select "Relational Data" from the Add-ons menu

3. In "Relational Data," choose "Connections"

4. Click Create Connection

5. Choose BigQuery

6. Enter the four data values provided by Crescendo Lab

7. After entering the data, click on "Save"

ℹ️ How to create Segment Templates in Emarsys to retrieve data, please refer to：Creating relational segments from Emarsys

⛔️ Considerations

Requires proficiency in SQL programming language
Data synchronization direction is one-way from MAAC → BQ
Transaction tracking data is limited to Web GA data from domains already integrated with the MAAC platform
Daily usage limit per client: 500 GB

ℹ️ BigQuery Connector Daily Usage Limit: 500 GB

Definition of the 500GB limit

When running a query, if BQ estimates that the required data volume exceeds 500GB, the system will block the query and prompt you to adjust the query conditions (e.g., narrowing the range or reducing the data size).

If the estimated data volume is below 500GB, the query will run successfully. Even if the actual fetched data exceeds 500GB, the query will still return results successfully.

Reset time

The daily usage limit resets at midnight Pacific Time (UTC-7). Please pay attention to time zone differences when planning queries to avoid exceeding the daily limit.

▶️ Explanations of the relevant data types provided by BigQuery

Currently, using BigQuery, you can access contact attributes, labels, open rates, click data, and consumption behavior data collected from Crescendo Lab's MAAC product database.

ℹ️ Update frequency

Attribute profile data: Updated daily at 00:00 AM
Event-type data: Real-time updates (with a potential 1-2 minute delay in case of system overload)

ℹ️ Data record starting point

Profile attribute data: accumulated since the brand’s MAAC backend activation
Event data: available starting from the activation of BigQuery Connector / CDH webhook for the brand

8. Attribute profile data - Contacts

Clients can filter out contacts by the date of contact added to MAAC, the date will align with the contacts created time on MAAC UI

9. Attribute profile data - Tags

10. Event-type data - Transactions

⚠️ Consumption behavior (Transaction) tracking data is only available through domain Web GA data that has been connected to the MAAC platform.

11. Event-type data - Message send

12. Event-type data - Message open

13. Event-type data - Message click

Each message open and message click are independent BQ events.
If you need to retrieve data on message opens and click interactions when pushing messages using the Open API, please make sure to create an event ID.
In MAAC, different functionalities generate click interaction data. If you use the same UTM, it will result in two separate data entries (with different campaign names).

14. Event-type data - Prize send

15. Event-type data - Prize send

16. Event-type data - Prize redeem

17. Event-type data - Contact Profile Customer ID update

18. Event-type data - Contact tag add

19. Event-type data - Contact tag remove

⚠️ The engagement history data is collected after the connector is established

Created with Tango.us