Recurly to BigQuery Integration: 2 Easy Ways to Connect

• July 8th, 2022

Recurly to BigQuery FI

Recurly is a platform for subscription management and billing that gives you the ability to manage the billing and payment processes associated with your customers. It provides you with increased visibility into your subscription business, which in turn enables you to optimize your strategy for subscription growth and reduce the number of customers who leave without their consent.

By exporting data from Recurly, marketers have the ability to evaluate the efficacy of advertising on TikTok in comparison to the efficacy of advertising on other channels. This allows them to easily determine which campaigns bring in more revenue and how to distribute their advertising budget.

In this article, you will learn 2 methods to connect Recurly to BigQuery and the key features of both.

Table of Contents

What is Recurly?

Recurly to BigQuery: recurly logo
Image Source

Recurly is the subscription growth engine that helps global brands maximize the value of their subscribers over the course of their lifetime by leveraging their expertise, technology, and insights. They support Sling, Twitch, BarkBox, FabFitFun, Paramount, Lucid, and Sprout Social among their customers. These companies define their respective industries, and they collectively have more than 50 million active subscribers in more than 140 different currencies.

Recurly simplifies the management of recurring payments at scale by optimizing each stage of the subscription lifecycle, including customer acquisition, customer retention, and customer growth. 

Key Features of Recurly

  • Scale Globally: Change is constant when it comes to recurring payments, billing, and customer requirements. Recurly ensures that you are always one step ahead of the competition.
  • Adapt Quickly: Simple configuration of trials, plans, pricing, and promotions, as well as support for complex billing scenarios, including usage-based, quantity-based, and hybrid model options.
  • Proactive Thinking: Reduce churn with revenue recovery powered by machine learning, which both prevents and recovers lost revenue from failed transactions.
  • Effective Workflow: Integrate workflows without any hiccups by using an enterprise-grade platform that is user-friendly and contains applications for payment gateways, customer relationship management systems, enterprise resource planning systems, and more.
  • Security: You can put your trust in Recurly to provide you with up-to-date rules, regulations, and compliance standards covering areas such as fraud management and taxes.

What is Google BigQuery?

Recurly to BigQuery: bigquery logo
Image Source

Google BigQuery is a Data Warehouse hosted on the Google Cloud Platform that helps enterprises with their analytics activities. This Software as a Service (SaaS) platform is serverless and has outstanding data management, access control, and Machine Learning features (Google BigQuery ML). Google BigQuery excels in analyzing enormous amounts of data and quickly meets your Big Data processing needs with capabilities like exabyte-scale storage and petabyte-scale SQL queries.

Google BigQuery’s columnar storage makes data searching more manageable and effective. On the other hand, the Colossus File System of BigQuery processes queries using the Dremel Query Engine via REST. The storage and processing engines rely on Google’s Jupiter Network to quickly transport data from one location to another.

Key Features of Google BigQuery

  • Fully Managed: By offering serverless execution, Google BigQuery takes care of complicated setup and maintenance processes, including Server/VM Administration, Server/VM Sizing, Memory Management, etc.
  • Exceptional Performance: Due to the column-based design, Google BigQuery provides several advantages over traditional row-based storage, like higher storage efficiency and quicker ability to scan data.
  • Security: Google BigQuery offers Column-level protection, verifies identity and access status, and establishes security policies as all data is encrypted and in transit by default. Since it is a component of the Google Cloud ecosystem, it complies with security standards like HIPAA, FedRAMP, PCI DSS, ISO/IEC, SOC 1, 2, and 3.
  • Partitioning: Google BigQuery’s decoupled Storage and Computation architecture employs column-based segmentation to lower the quantity of data retrieved from discs by slot workers.

Why Connect Recurly to BigQuery?

Recurly automates existing processes to make them more streamlined and allow you to go to market at the speed you require. It was designed to be compatible with any existing technology stack, gateways, and ecosystems. Google BigQuery allows you to run SQL-like queries extremely quickly against petabytes of data by leveraging the processing power of Google’s underlying infrastructure. It is a scalable service that does not require any setup and provides you with real-time business insights regarding your data.

Explore These Methods to Connect Recurly and BigQuery

Recurly makes the management of recurring payments at a scale much simpler by optimizing each stage of the subscription lifecycle, including customer acquisition, customer retention, and customer growth. Also, BigQuery is a data warehouse known for ingesting data instantaneously and performing almost real-time analysis. When integrated together, moving data from Recurly to Bigquery could solve some of the biggest data problems for businesses. In this article, we have described two methods to achieve this:

Method 1: Connect Recurly to BigQuery using Hevo

Hevo, an Automated Data Pipeline, provides you a hassle-free solution to connect Recurly to BigQuery within minutes with an easy-to-use no-code interface. Hevo is fully managed and completely automates the process of not only loading data from Recurly. It can also enrich and transform the data into an analysis-ready form without having to write any code.

GET STARTED WITH HEVO FOR FREE

Method 2: Manually Connect Recurly to BigQuery

In this method, users will have to write custom codes to enable two processes, streaming data from Recurly and ingesting data into BigQuery. This method is suitable for users with a technical background.

Both the methods are explained below.

Methods to Connect Recurly to BigQuery

Recurly is a platform for subscription management and billing that gives you the ability to manage the billing and payment processes associated with your customers. It provides you with increased visibility into your subscription business, which in turn enables you to optimize your strategy for subscription growth and reduce the number of customers who leave without their consent.

With the Recurly to BigQuery integration, you can perform effective real-time automated processes, saving time when working on repetitive tasks. This integration is the ideal value addition for an e-commerce company or business owner who wants to improve operations, increase efficiency, and sync data throughout their workspace.

To connect Recurly to BigQuery, you can use two methods. You can either use Hevo to connect them both, or you can manually integrate Recurly and BigQuery.

These two methods are explained below:

Method 1: Connect Recurly to BigQuery using Hevo

Recurly to BigQuery: hevo banner
Image Source

Hevo provides Google Bigquery as a Destination for loading/transferring data from any Source system, which also includes Recurly . You can refer to Hevo’s documentation for Permissions, User Authentication, and Prerequisites for Google BigQuery as a destination here

Configure Recurly as a Source

Carry out the procedures listed below in order to configure Recurly as the Source in your Pipeline in Recurly to BigQuery Connection:

  • Step 1: In the Asset Palette, select the PIPELINES option.
  • Step 2: In the Pipelines List View, select the +CREATE button.
  • Step 3: Choose Recurly from the drop-down menu on the Select Source Type page to connect Recurly to BigQuery.
  • Step 4: Click the + ADD Recurly ACCOUNT button on the page that allows you to configure your Recurly account to perform Recurly to BigQuery Migration.
  • Name of Pipeline: A one-of-a-kind name for the Pipeline, with a maximum of 255 characters.
  • API Key: The private key that you generated within your Recurly account in order to grant Hevo access to read data.
  • EU Environment: (This environment is only visible to users who are logged in to the Hevo Europe region). If your Recurly account is located in the European Union (EU) region, you should enable this feature. Learn more about the regions that are supported by Recurly by reading the article titled “Data Hosting in Recurly.”
  • Historical Sync Duration: The amount of time over which the historical data must be ingested. Also known as “duration.” Default value: 3 Months.
  • Step 8: Just hit the TEST & CONTINUE button to connect Recurly to BigQuery.
  • Step 9: Move on to the next step, which is to configure the data ingestion and set up the Destination in Recurly to BigQuery Integration.

Configure BigQuery as a Destination

To configure BigQuery as a Destination in Recurly to BigQuery Connection, follow these steps:

  • In the Asset Palette, choose DESTINATIONS.
  • In the Destinations List View, click + CREATE.
  • Select Google BigQuery as the Destination type on the Add Destination page to connect Recurly to BigQuery.
  • Select the authentication method for connecting to BigQuery on the Configure your Google BigQuery Account page.
  • Perform one of the following for Recurly to BigQuery Connection:
    • To connect with a Service Account, follow these steps:
      • Attach the Service Account Key file.
      • Click on CONFIGURE GOOGLE BIGQUERY ACCOUNT.
    • To join using a User Account, follow these steps:
    • Click on + ADD A GOOGLE BIGQUERY ACCOUNT.
    • Sign in as a user with BigQuery Admin and Storage Admin permissions.
    • Provide Hevo access to your data by clicking Allow.
  • Configure your Google BigQuery Warehouse page with the following information:
    • Destination Name: Give your Destination a distinctive name.
    • Project ID: The BigQuery instance’s Project ID.
    • Dataset ID: The dataset’s name.
    • GCS Bucket: A cloud storage bucket where files must be staged before being transferred to BigQuery.
    • Enable Streaming Inserts: Select this option to stream data to your BigQuery Destination as it arrives from the Source, rather than loading it via a task according to a Pipeline schedule.
    • Sanitize Table/Column Names: Select this option to replace any non-alphanumeric characters and spaces in table and column names with an underscore (_).
    • Populate Loaded Timestamp: Enabling this option adds the __hevo_loaded_at_ column to the Destination Database, indicating the time when the Event was loaded to the Destination.
  • To test the connection, click TEST CONNECTION and then SAVE DESTINATION to finish the setup of Recurly to BigQuery Connection.

Here are more reasons to try Hevo:

  • Smooth Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to your schema in the desired Data Warehouse.
  • Exceptional Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Flexibility is designed for everyone.
  • Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
  • Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Try Hevo Today!

Get Started with Hevo for Free

Method 2: Manually Connect Recurly to BigQuery 

To manually connect Recurly to BigQuery, follow these steps:

Extracting the Data

The first step in Recurly to BigQuery Connection is extracting the data. Applications are able to interact directly with the Recurly platform thanks to its Application Programming Interface (API). In general, Recurly provides customers with the following options for integrating their services, which are as follows:

  • Hosted Payment Pages: Using this product, merchants can immediately begin subscribing to their customers with minimal effort and without requiring significant levels of technical expertise. This is made possible by the product’s cloud-based architecture.
  • js: It is a JavaScript library that can be used to integrate the features of Recurly in a secure manner into your own product.
  • API: An application programming interface (API) for the Web that makes it possible to integrate other software with Recurly by accessing all of its features.
Authentication of the Recurring API

In Recurly to BigQuery Connection, your API Key is used as the credential in Recurly’s implementation of HTTP Basic Authentication. All the data is transferred over a secure SSL channel. An illustration of how authentications are handled by Recurly is provided below, using the tool Curl:

curl -H 'Accept: application/xml' 
     -H 'X-Api-Version: 2.1' 
     -H 'Content-Type: application/xml; charset=utf-8' 
     -u [API Key]: https://[subdomain].recurly.com/v2/accounts

Recurly allows its users to make use of multiple Private API keys, each of which can be used to integrate third-party services by employing a set of credentials that are individual and controlled. With the following qualifications and restrictions:

Core and grandfathered obligations Plans with Recurly come with a total of 5 private API keys.

The professional plan includes ten API keys for your use.

Rate Limits

The following API rate limits are applied by default to newly created Recurly sites:

  • Sandbox sites: 400 requests/min. Every single request is factored into the overall rate limit.
  • Production sites: 1,000 requests/min. Only requests that use the GET method will count toward the rate limit.

Once your website enters production mode, Recurly will only rate limit GET requests instead of other types of requests. Your rate limit will not be affected by any new subscriptions, account modifications, or other requests that use the POST, PUT, or DELETE methods.

The calculation of the speed limit takes place over a slipping window of five minutes. This indicates that a production site could make 4,000 requests within one minute and not hit the rate limit provided that the site made fewer than 1,000 requests in the four minutes prior to the one minute in question.

The API will return the status code 429, which stands for “Too Many Requests“, in the event that a request makes it past the rate limit.

Preparing the Data

The next step in Recurly to BigQuery Connection is preparing the data. Before you load your data into BigQuery, you should make certain that it is already organized in a format that is compatible with BigQuery. If the API you pull data from returns XML, for instance, you will need to convert it into a serialization that Google BigQuery is familiar with first. At the moment, the following data formats are supported:

  • CSV
  • JSON

In addition to this, you need to make sure that the data types you are utilizing are supported by BigQuery, which includes the following categories of data types:

  • STRING
  • INTEGER
  • FLOAT
  • BOOLEAN
  • RECORD
  • TIMESTAMP

Loading the Data

The last step in Recurly to BigQuery Connection is loading the data. To begin using Google Cloud Storage, you must first upload all of your files to the service. There are several different ways that this can be accomplished. For instance, you can use the console directly as it is described here; however, you must remember to adhere to the recommended procedures at all times. 

One more alternative is to submit your information by using the JSON API. Once again, we see that APIs play a significant part in the process of extracting data from our data warehouse and loading data into it. It is the simplest possible scenario, and all that is required is one HTTP POST request made with a program such as CURL or Postman. It should look similar to this example.

POST /upload/storage/v1/b/myBucket/o?uploadType=media&name=myObject HTTP/1.1
Host: www.googleapis.com
Content-Type: application/text
Content-Length: number_of_bytes_in_file
Authorization: Bearer your_auth_token
your Recurly data

You should get a response from the server that looks something like the following if everything worked out as it was supposed to:

HTTP/1.1 200
Content-Type: application/json
{
  "name": "myObject"
}

With this, you have successfully performed Recurly to BigQuery Integration.

Conclusion

In this article, you understood the main features of Recurly and Google BigQuery and learned two methods to integrate Recurly to BigQuery. Google BigQuery allows you to analyze Recurly data to find meaningful insights to improve user experience.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo can help!

Visit our Website to Explore Hevo

Hevo will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. Hevo provides a wide range of sources – 150+ Data Sources (including 40+ Free Sources) – that connect with over 15+ Destinations. It will provide you with a seamless experience and make your work life much easier.

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 our unbeatable pricing that will help you choose the right plan for your business needs!

No-Code Data Pipeline for Google BigQuery