Recurly to BigQuery Integration: 2 Easy Ways to Connect

Unlock the full potential of your Recurly data by integrating it seamlessly with BigQuery. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

Connecting Recurly to BigQuery helps businesses easily export their subscription and billing data for in-depth analysis. Recurly is a reliable platform for managing subscriptions and payments, giving companies the tools they need to streamline billing processes and better understand customer behavior.

With BigQuery’s powerful data analysis tools, companies can explore their data to uncover trends, improve strategies, and reduce customer churn. This integration provides a simple way to gain valuable insights into subscription performance, helping businesses make data-driven decisions to drive growth.

What is Recurly?

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. 

What is Google BigQuery?

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.

Why Connect Recurly to BigQuery?

• Simplifies Processes: Recurly helps automate and streamline subscription management, making it easier to operate.
• Works with Your Setup: It’s designed to fit right in with your existing tools, systems, and payment gateways.
• Fast Data Queries: BigQuery lets you run quick queries on huge amounts of data using Google’s infrastructure.
• Scalable: BigQuery grows with your data needs, with no setup required.
• Real-Time Insights: It gives you immediate insights into your data, helping you make decisions faster.

Method 1: Connect Recurly to BigQuery using Hevo

Step 1: Configure Recurly as a Source

To configure Recurly as the Source in your Pipeline in Recurly to BigQuery Connection:

• In the Asset Palette, select the PIPELINES option.
• In the Pipelines List View, select the +CREATE button.
• Choose Recurly from the drop-down menu on the Select Source Type page to connect Recurly to BigQuery.
• 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.

• Just hit the TEST & CONTINUE button to connect Recurly to BigQuery.
• Move on to the next step, which is to configure the data ingestion and set up the Destination in Recurly to BigQuery Integration.

Step 2: 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.
  • 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.

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 your fingertip. 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.

Method 2: Manually Connect Recurly to BigQuery 

To manually connect Recurly to BigQuery, follow these steps:

Step 1: 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.

Step 2: 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

Step 3: 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 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!

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc.

Want to take Hevo for a spin?Explore our 14-day free trial and experience the feature-rich Hevo suite first hand.

FAQs