Empowering Lytics customers with real-time insights through BigQuery continuous queries

Authors: Kelsey Abegg Lytics & Jobin George Google

Lytics leverages Google Cloud’s BigQuery to enhance its Customer Data Platform (CDP) capabilities through both real-time and batch data processing. Granular event data in BigQuery allows for ease of auditing, querying, and reporting through other data visualization tools. BigQuery integration enables scalable, secure, and efficient customer data management, crucial in today’s personalized marketing landscape. Lytics can pull and pass data to BigQuery seamlessly and utilizes BigQuery with Lytics Cloud Connect to manage zero-copy use cases effectively. This setup allows the data to be used for enrichment, activation, reporting, and more. This collaboration simplifies data management and streamlines the overarching marketing strategy, enabling businesses to centralize their data and reduce complexity. But ingesting changes in BigQuery data into Lytics for such use cases has been not easy as we have to rely on batch sweeps keeping track of already ingested data. This is where our latest collaboration with Google BigQuery Comes into the picture, Introducing: BigQuery continuous queries.

What is BigQuery continuous queries

BigQuery continuous queries provide customers with the ability to run continuously processing SQL statements that can analyze, transform, and externally replicate data as new events arrive into BigQuery in real-time. This approach extends BigQuery’s real-time capabilities to handle continuous streams of input, analysis, and output; which can be leveraged to construct event-driven actionable insights, real-time machine learning inference, operational reverse ETL pipelines, and more.You can insert the output rows produced by a continuous query into a BigQuery table or export them to Pub/Sub or Bigtable.

Why is continuous data important?

Continuous data enables businesses to maintain a comprehensive view necessary for delivering relevant, timely experiences aligned with customer interests and behaviors. The net benefit is increased customer satisfaction and loyalty. However, real-time capabilities are often seen as too expensive or difficult to enable. This perception can be attributed to several key challenges:

1. Technology Limitations: Historically, some of the key contributors of customer data simply could not support a continuous feed of information.
2. Data Velocity and Volume: Handling the large amount of data that arrives quickly is complex. Systems must process this data fast for immediate actions like personalizing customer interactions or adjusting marketing campaigns.
3. Integration Complexity: It takes time to seamlessly integrate multiple data sources and tools, each with its data format and protocols, into a cohesive system that supports real-time operations.
4. Latency Issues: Even slight delays can make real-time data less useful, affecting decisions crucial for tasks like ad placements or customer responses. Reducing these delays is essential.
5. Data Quality and Consistency: Maintaining accuracy and uniformity of real-time data, which often comes from various sources, is challenging but necessary for effective decision-making.
6. Scalability and Flexibility: As business needs and data volumes grow, systems must scale without losing performance, adapting to new marketing channels and technologies.

Addressing these challenges requires a strategic data management approach emphasizing speed, integration, and quality. Solutions that enhance data interoperability, reduce latency and ensure scalability are vital for businesses looking to capitalize on the benefits of real-time data in the martech space.

Solution: Continuous queries on Google Cloud

Lytics utilizes Google Cloud’s BigQuery and continuous queries feature to enable instant data processing. This solution simplifies overall data management by centralizing data into a single source, allowing Lytics to instantly update customer data as new information streams in. This centralization facilitates real-time adjustments to customer profiles and immediate actionability. Key features of this system include:

• Real-Time Data Processing: BigQuery’s continuous queries instantly process incoming data streams from a unified source, enhancing the speed and efficiency of data management.
• Scalable Infrastructure: Backed by BigQuery’s robust serverless infrastructure, continuous queries can handle massive volumes of data with high throughput and low latency.
• Enhanced Security and Compliance: With data centralized in Google Cloud, the platform adheres to stringent data security and privacy standards, simplifying compliance management.

This approach boosts the efficiency of real-time data processing and streamlines security and scalability by consolidating data management into a single, robust system.

Immediate value

The key drivers for integrating Lytics with Google Cloud’s BigQuery through continuous queries focus sharply on delivering immediate value by addressing two critical aspects:

Engaging Anonymous Profiles: Immediate processing of interactions with anonymous users is essential. This capability allows companies to engage effectively with users who have not yet provided identifiable information. By leveraging real-time data, businesses can tailor experiences from the first interaction, significantly increasing the chances of conversion and engagement. Immediate data processing ensures that every interaction with an anonymous profile is maximized for potential conversion before the user disengages.

Managing Consent Preferences: In today’s privacy-focused marketing environment, managing consent preferences in real time is not just an operational necessity but a legal requirement. Real-time processing allows companies to adjust their engagement strategies instantly as users update their consent preferences, ensuring compliance and maintaining user trust. This dynamic approach to consent management prevents the misuse of data and adheres to global privacy regulations, which can vary significantly and change frequently.

The integration of Lytics and BigQuery effectively unblocks significant value by enabling businesses to act on data at the moment it is most relevant. This real-time capability transforms how companies approach both anonymous users and consent management, providing them with the agility to make swift decisions that are crucial for personalized marketing and compliance. Without this advanced data workflow, businesses would rely on slower, batch-processed data updates, which could lead to missed opportunities and potential non-compliance with privacy laws. Thus, the partnership between Lytics and BigQuery directly contributes to creating immediate and impactful value, streamlining operations, and enhancing customer experiences.

Solution Architecture

Below is a diagram that illustrates how Lytics integrates with Google Cloud’s BigQuery and harnesses the power of continuous queries to enhance its capabilities as a Customer Data Platform (CDP):

Data Flow Steps

A specific query is issued in “continuous mode,” which captures data relevant to the customer’s profile update. Pub/Sub is the export method configured to a topic.

1. Data Change: New data enters BigQuery and is immediately captured by the continuous query.
2. Data Transfer: A Pub/Sub event is triggered, notifying Lytics of the updates to the table.
3. Profile Updates: Customer profiles within Lytics are updated in real time as the new data is processed.
4. Actionable Insights: The updated profiles are utilized to deliver personalized experiences and actively drive customer engagement. 

This architecture enables Lytics to efficiently process and utilize real-time data, ensuring that customer interactions are based on the most current information available. Thus, it enhances the overall effectiveness of marketing strategies.

Steps to implement continuous queries integration with Lytics

Setting up and running BigQuery continuous queries

At the time this blog was written, the continuous queries feature is in preview and subjected to the “Pre-GA Offerings Terms”. To enroll in the continuous queries preview, fill out the request form.

1) As Lytics would be using a service account to connect and consume data from pub/sub, customers can configure a single service account for running continuous queries and consume from Pub/Sub by assigning relevant permissions to the user. You can configure the service account with permissions listed here. Make sure you create a JSON key to configure the connection in Lytics in the later step.

2) To run continuous queries, BigQuery requires a slot reservation with a “CONTINUOUS” assignment type. Follow steps here if you are not sure how to create a reservation

3) Navigate to the  Pub/Sub topic page and click on “Create Topic” button on the top center of the page, and provide a name (say ‘continuous_query_topic’, also create a default subscription if needed)

4) Navigate to BigQuery service page and design the query as an export to pub/sub 

In the More Settings as shown below, select the query mode as continuous query and in the query settings select the service account created above to run the query. You can also choose the timeout required if any. 

EXPORT DATA
OPTIONS (
format = ‘CLOUD_PUBSUB’,
uri = ‘https://pubsub.googleapis.com/projects//topics/continuous_query_topic’
) AS
(
<your query>
);

Before we execute the query, make sure the below steps are done to ensure data continuously generated can be captured by Lytics Pub/Sub connector.

Configuring Lytics to receive continuous queries updates via pub/sub connector

5. Navigate to Lytics UI → Conductor→ Schema→ Fields, Click on the ‘Create New’ button on the center of the page. You will configure what new profile fields you want to populate with data from the continuous query. 

6. Navigate to Lytics UI → Conductor→ Schema→ Mappings, Click on the ‘Create New’ button on the center of the page. You will then want to select what field you want the map the data into and then create the mapping. 

7. Navigate to Lytics UI → Conductor→ Pipeline → Sources, click on the ‘Create New’ button on the center of the page. Choose “Google Cloud” as the provider and then Pub/Sub: Import Data as the job type

8. Click on ‘create new Authorization’ and select ‘Cloud Pub/Sub Service Account JWT’ as authorization method and click next.

9. On the next page, provide a label for the connection, a description and upload the JSON Key required to authenticate, which was created in the first step.

Click Save authorization and continue. 

10. In the configure source page, provide a label, description and name of the stream pub/sub ingress should send events to, followed by Pub/Sub topic created and default subscription name which was created and click complete.

11. Navigate to Lytics UI → Decision Engine→ Audiences, click on the ‘Create New’ button on the center of the page. Select Custom Rule and Filter on the Profile Attributes you would like to Segment on. 

Conclusion

Our partnership with Google just got even more powerful, BigQuery continuous queries now empower you to stream real-time data directly into Lytics, unlocking solutions that were previously not possible. This opens a world of exciting new opportunities for our shared customers. If you would like to get started, try our offering on Google Cloud Marketplace or via Private Instance and Private Cloud deployments on Google Cloud.

Guest co-author

Jobin George, Staff Technical Architect, Google

Jobin George is a Staff Technical Architect at Google, where he transforms how key customers and partners work with data. His expertise in large-scale Data & Analytics solutions fuels his thought leadership and technical guidance. Known for his strategic and collaborative approach, he works closely with clients to address their unique challenges and architect solutions that drive success.

LinkedIn