How to Migrate Marketo to BigQuery | 2 Easy Methods

Marketo is a well-known Marketing Automation tool that helps businesses automate and track marketing engagement, tasks, and workflow. With its clever automation, it can handle all of your marketing campaigns and generate quality leads for you. Marketo Reports also makes it simple to track your marketing campaign by offering a variety of visualizations in the form of reports and dashboards.

Due to its unequaled query performance, Google BigQuery has become an internationally trusted Cloud Data Warehouse & Analytics solution. On-demand scalability, low price, and capacity to handle variable workloads make it a trustworthy and secure Cloud platform for businesses of all sizes.

This article talks about the different methods you can use to transfer data from Marketo to BigQuery seamlessly. It also gives a brief introduction to Marketo and Google BigQuery before diving into the Marketo to BigQuery data transfer methods.

What is Marketo?

Developed in 2006, Adobe Marketo is a very popular Software-as-a-Service(SaaS) based marketing automation software. 

Adobe has developed Marketo to help businesses automate and measure marketing engagement tasks and workflows. 

Marketo monitors automation tasks like email marketing, lead management, revenue attribution, account-based marketing, customer-based marketing, and more.

Key Features of Marketo

• Engagement Engine: With the engagement engine, Marketo allows businesses to conduct research and gain meaningful insights into customer data. 

• Account-Based Marketing: This feature in Marketo allows businesses to develop an intelligent list of characters that characterize the personas they want to target. 

• Lead Nurturing: Marketo helps businesses segment their leads based on marketing personas, target industries, or interactions taking place. 

What is Google BigQuery?

Developed in 2010, Google BigQuery is a highly scalable, multi-cloud data warehouse with a built-in query engine. It is also known as the fully managed data warehouse that can scale and analyze petabytes of data within seconds.

Google BigQuery is called SQL-based Data Warehouse as a Service (DWaaS) with zero infrastructure management. Since it is a fully managed data warehouse, BigQuery does not require any upfront hardware provisioning or management.

Key Features of Google BigQuery

• BigQuery BI Engine: BigQuery consists of the BI engine, which helps businesses process large datasets with sub-second query response time and high concurrency. 

• Machine Learning: Google BigQuery enables businesses to create machine learning models using SQL queries. It supports different machine learning models such as Logistic Regression, Binary Logistic Regression, K-means clustering, Multi-Class Regression, and more.

• Real-time Analytics: BigQuery keeps you up-to-date with real-time transfer and analytics. 

• User Friendly: You can store and analyze the data in Google BigQuery with simple clicks. It consists of an easy-to-understand interface, which provides simple instructions at every step to set up your data warehouse quickly.

Methods to Connect Marketo to BigQuery  

Method 1: Connect Marketo to BigQuery via CSV Files/APIs 

Step 1: Exporting Data from Marketo

Exporting data from Marketo is the first step in Marketo to BigQuery data transfer. For users that want to export data programmatically, Marketo offers two types of REST APIs. Marketo personal records and associated object types, such as Opportunities and Companies, can be retrieved via Lead Database APIs. Asset APIs make marketing collateral and workflow records available to the public.

Visit the Admin panel and select LaunchPoint to utilize the APIs. Get an authorization token by creating a new service. Then select the API that corresponds to the data you require. For example, you could use Receive /rest/v1/opportunities.json to GET a list of opportunities. To customize the data Marketo returns, you can add a half-dozen alternative parameters.

Step 2: Sampling and Preparing Marketo Data

Marketo’s data is returned in JSON format. An example of data returned by an opportunities query is as follows:

{  
   "requestId":"e42b#14272d07d78",
   "success":true,
   "result":[  
      {  
         "seq":0,
         "marketoGUID":"dff23271-f996-47d7-984f-f2676861b5fa ",
         "externalOpportunityId":"19UYA31581L000000",
         "name":"Chairs",
         "description":"Chairs",
         "amount":"1604.47",
         "source":"Inbound Sales Call/Email"
      },
      {  
         "seq":1,
         "marketoGUID":"dff23271-f996-47d7-984f-f2676861b5fc ",
         "externalOpportunityId":"29UYA31581L000000",
         "name":"Big Dog Day Care-Phase12",
         "description":"Big Dog Day Care-Phase12",
         "amount":"1604.47",
         "source":"Email"
      }
   ]
}

You’ll need to construct a schema for your data tables if you don’t already have one for storing the data you obtain. Then, for each value in the response, you must identify a predefined datatype (INTEGER, DATETIME, etc.) and create a table to receive it. Marketo’s documentation should list the fields and datatypes available by each endpoint.

The fact that the records fetched from the source may not always be “flat” – some of the objects may really be lists – further complicates things. This means you’ll almost certainly need to create more tables to account for the variable cardinality of each record.

Step 3: Loading Data into Marketo

Google has a page that explains how to load data into BigQuery. To upload data to your datasets and establish schema and data type metadata, use the bq command-line tool, particularly the bq load command. The Quickstart guide for bq can teach you how to use it. Iterate through the process until you’ve loaded all of your tables into BigQuery.

Step 4: Maintaining Marketo Data

You might have transferred data from Marketo to BigQuery but it’s not a good idea to duplicate all of your data every time your records are updated. This would be a terribly slow and resource-intensive procedure.

Instead, select important fields that your script may use to save its progress through the data and return to them as it searches for updated data. For this, auto-incrementing fields like updated at and created at are ideal. You can set up your script as a cron job or continuous loop to acquire fresh data as it occurs in Marketo once you’ve added this functionality.

Limitations of manually connecting Marketo to BigQuery

The following are some of the drawbacks of manually connecting Marketo to BigQuery:

• Creating Pipeline: Building an in-house data pipeline necessitates a great deal of skill, time, and people, as well as a high risk of error.
• Time-Consuming: Building a data pipeline in-house takes a lot of time, effort, and labor, and there’s a lot of room for error.
• Hard Coding: Analysts must build code and manage infrastructure, yet they cannot get data within hours due to hard coding.
• Unreliable Application: You may never know whether or not a third-party application is trustworthy.

Method 2: Replicate Marketo to BigQuery using Hevo’s No-code Data Pipeline

Step 1: Configure Marketo as Source

Step 2: Configure Google BigQuery as the Destination

Migrating Data from Marketo to BigQuery: Best Practices to Follow

Below are the best practices to follow while migrating Data from Marketo to BigQuery:

• Apply the Principle of Least Privilege by granting API users only the permissions and workspaces needed to fulfill their assigned tasks. You’re effectively allowing the service using that API user to change/read your Marketo instance’s data in more ways than you initially planned by granting them more access than they need.
• Use specialized API users and API services for different integrations to enable fine-grained control/access over permissions and to aid in the auditing of API usage over time.
• Make concurrent API calls only if your use case absolutely requires it. Concurrent calls from a single service/integration could rapidly exceed the concurrency limit of 10 calls shared by all integrations.
• Nested and repeated data is one of the most significant ETL recommended practices for Google BigQuery. When the data is denormalized, Google BigQuery performs best. Denormalize the data instead of retaining relationships and take advantage of nested and repeated fields. Avro, Parquet, ORC, and JSON (newline delimited) formats support nested and repeated fields. STRUCT is the type that can be used to represent a nested object, while ARRAY is the type that can be used to represent a repeated value.
• Google BigQuery data is encrypted by default, and GCP manages the keys. Customers can also use the Google KMS service to handle keys.

Conclusion

In this article, you got a glimpse of how to connect Marketo to BigQuery after a brief introduction to the salient features, and use cases. The method talked over in this article is using API Migration. The process can be a bit difficult for beginners. Moreover, you will have to update the data each and every time it is updated and this is where Hevo saves the day!

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources such as Marketo and a wide variety of Desired Destinations such as Google BigQuery, with a few clicks. Try a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also, check out our unbeatable pricing to choose the best plan for your organization.

Frequently Asked Questions