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.

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.

And, as with any code, you must maintain it once you’ve written it. You may need to change the script if Marketo changes its API or if the API sends a field with a datatype your code doesn’t understand. You will have to if your users require somewhat different information.

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

Hevo helps you directly transfer data from various sources like Marketo to BigQuery Database, Business Intelligence tools, Data Warehouses, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Sign up here for a 14-day free trial!

The following steps can be implemented to implement Marketo to BigQuery using Hevo:

• Configure Source: Connect Hevo Data with Marketo providing a unique name for your Pipeline, along with details about your Data Source.

• Integrate Data: Establish a connection to Google BigQuery by providing information about your BigQuery Dataset and its credentials such as authorized account, destination name, dataset id, and project id. 

Here are more reasons to try Hevo:

• Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
• Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
• Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
• Scalable Infrastructure: Hevo has in-built integrations for 100+ sources that can help you scale your data infrastructure as required.
• Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within Data Pipelines.
• Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

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!

Visit our Website to Explore Hevo

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. Hevo Data with its strong integration with 100+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about Marketo to BigQuery! Let us know in the comments section below!