Every business relies on a number of IT systems for daily operations. Each of these systems contains valuable data that is essential for tracking success metrics, forecasting future trends, monitoring daily operations—and most importantly, steering businesses on the proper course. 

However, creating data transformation pipelines to integrate data into a cloud data warehouse for analysis is challenging. In fact, the process of developing and maintaining even a single data pipeline for a data warehouse like BigQuery can result in enormous delays, data compliance issues, coding difficulties, and IT department burdens—not to mention the financial cost. 

This is where Integrate.io can help. As a powerful, easy-to-use data integration-as-a-service platform, Integrate.io is a Google Cloud Ready - BigQuery partner that empowers everyone—regardless of their tech experience—to quickly and affordably develop custom data pipelines without writing a single line of code.  

In this blog, we’ll see how Integrate.io and Google BigQuery work together—and how Xenon Partners uses both technologies to power their web and sales funnel analytics.

Solution Overview

BigQuery empowers AI-enabled business intelligence tools to operate at scale while running fast SQL queries across large datasets. Integrate.io offers no-code/low-code tools for channeling information into BigQuery via data integration pipelines—and it will even Reverse ETL the data back out again. Together, BigQuery and Integrate.io form a powerful, flexible, and highly affordable data management and analytics solution. 

Integrate.io data pipelines are capable of (1) pulling data from diverse systems, (2) transforming it for compliance, compatibility, or readability purposes; (3) loading the data into BigQuery or another data warehouse; and (4) serving the data back out to operational systems via Reverse ETL or API. With the capacity to perform all or any combination of these functions in a single data pipeline, Integrate.io serves the widest range of use cases for ETL, ELT, CDC, Reverse ETL, and API Management. 

In other words, Integrate.io functions as a universal bridge to provide BigQuery with all of the large-volume, high-quality data that accurate business intelligence requires.  

As a Google Cloud Ready – BigQuery partner, Integrate.io has completed rigorous data integration tests and exceeded all of the integration standards of Google’s engineering team partners. This means that Integrate.io and BigQuery are fully compatible and available to each platform’s respective customers.

Integrate.io offers the following core features to Google BigQuery users:

    • Hundreds of pre-built connectors: Integrate.io connectors extract information from the most popular platforms, databases, and data warehouses and load it into BigQuery.
    • REST API connector: The REST API connector allows Integrate.io to quickly connect with any source/destination with a REST API.  
    • 15 drag-and-drop transformation components: Drag-and-drop data transformations allow users to set up in-pipeline transformations—such as FILTER, JOIN, SELECT, LIMIT, and CLONE—without writing a single line of code. 
    • SQL scripting editor and native scripting language: The Integrate.io proprietary scripting language empowers advanced users to code custom data transformations with 200+ pre-built functions.
    • Cutting-edge security, encryption, and data compliance feature: Integrate.io allows users to mask, encrypt, and/or scrub PII and PHI data to adhere to SOC 2, SOX, PCI, GDPR, CCPA, HIPAA, and SSL/TLS standards.
    • Unlimited user support: All Integrate.io users enjoy free and unlimited support from a dedicated integration specialist—who is personally assigned to their account. Accessible via phone, email, and video conference, each account's integration specialist is trained to help non-tech-savvy customers achieve the most sophisticated data integration goals.
  • Additional Integrate.io features: Additional features include unlimited scaling for fluctuating requirements; a point-and-click no-code/low-code dashboard for building and managing data pipelines; real-time monitoring and progress updates for ongoing tasks; automatic email updates/alerts; and two-way Salesforce-to-Salesforce integrations that can extract Salesforce data, transform/enrich the data, and upload it back into Salesforce. 

Ultimately, Integrate.io does not just empower users to create powerful and sophisticated information lifecycle management (ILM) data integration pipelines. The platform is also easy-to-use with a point-and-click interface, which empowers inexperienced users to develop data pipelines in minutes. 

Finally, Integrate.io's per-connector billing structure offers a predictable, easy-to-budget schedule that bills exactly the same each month unless users add or remove data connections. These features—along with its virtually limitless scalability—make Integrate.io affordable for enterprises and SMBs alike. 

How Xenon Partners’ Integrate.io Data Pipelines Work

To understand how an Integrate.io data pipeline works, this section looks at two pipelines that Xenon Partners, a tech private equity firm, has in production.

Xenon Partners leverages multiple pipelines that (1) extract data from operational data sources, (2) transform the information for compatibility and data compliance purposes, and (3) load it into Google BigQuery. From there, the Looker BI platform connects to Google BigQuery, analyzes the data, and displays key business metrics via colorful, interactive visuals and graphs (see image below). 

thumbnail image

Xenon Partners created the following pipelines using Integrate.io without writing a single line of code:

Example 1: Sales Funnel Analytics

The first data pipeline extracts, prepares, and loads sales funnel data into Google BigQuery—in order to calculate the metrics in the second column from the left (see image below). These figures represent the number of leads in each stage of a sales funnel (i.e. Leads, Demos, PQLs, etc.). They also show the lead conversion rates from one stage to the next. 

thumbnail image

To calculate the numbers in the left-hand part of the above-mentioned column (114, 2, 17, etc.), Integrate.io sends “deal data” from Pipedrive CRM to BigQuery. In the process, the data is transformed to calculate which deals are in which stage of the sales funnel. 

The Integrate.io data pipeline requires the following key steps to achieve this:

  1. Pull deal data from Pipedrive CRM.
  2. Pull data about each deal from another Pipedrive endpoint via a “Curl” function. 
  3. Enrich the deal data from Step 1 with the data from Step 2 to add more details about each deal.
  4. Filter for data about the specific stages in the dashboard (i.e. Lead, Demo, etc.).
  5. Use case statements to apply conditional logic based on values passing through the pipeline.
  6. Aggregate to retrieve the most recent timestamps in-stage. 
  7. Use more case statements to create two new fields that assign values based on the MAX timestamps created in the previous step. These final calculations reveal which stage each deal is in and when it moved into that stage.

Here is the Integrate.io visual interface showing the data pipeline for this use case:

thumbnail image

Example 2: 

Xenon Partners leverages the following data integration pipeline to provide data for the metrics on the left-hand column in the image below (0.9%, 5.3%, etc.), Including the data to calculate the “Visitors” number at the top of that column. 

This data pipeline begins by extracting the “deal data” from the two BigQuery tables created in the previous pipeline. Then, it transforms the data to develop the metrics. 

The Integrate.io data pipeline requires the following key steps to achieve this:

  1. Pull deal data from the two BigQuery tables created for the previous pipelines.
  2. Use a SUM aggregation function to calculate the number of deals per stage and visitors to the website (within a four-week period).
  3. Calculate the number of deals that move from Visitor to Lead, Lead to Demo, and so on.

Here is the Integrate.io visual interface showing the data pipeline for this use case:

thumbnail image

With these data pipelines in place, Xenon Partners is currently monitoring the above-calculated conversion rates for eight 4-week-long periods. The data pipelines allow BigQuery and Looker to calculate clear and actionable metrics on the success of sales funnel campaigns for the previous four weeks—and compare those metrics to the previous 4 weeks, and so on, going back up to 32 weeks.

One of the most exciting aspects of Integrate.io is that Xenon Partners can build data pipelines like this in just a few hours with minimal overhead costs related to development and maintenance. In fact, Xenon Partners is leveraging Integrate.io to achieve better data integration results than they would achieve with a more difficult-to-use and expensive data integration platform.

Conclusion 

As a Google Cloud Ready - BigQuery partner, Integrate.io features powerful tools to develop and maintain sophisticated data integration pipelines for virtually any use case at virtually any scale. Best of all, the platform offers all users unlimited access to a dedicated team of data integration specialists. These specialists are always available to help users create custom data pipelines that extract, transform, load, and Reverse ETL data to and from Google BigQuery.

If you’d like to leverage Integrate.io to overcome your data integration bottlenecks, schedule an Intro Call and sign up for a free trial of the platform now!

Corresponding Author.

thumbnail image

Jobin George

Staff Solutions Architect - Data & Analytics at Google