How to load data from ActiveCampaign to Google BigQuery

Access your data on ActiveCampaign

The first step in loading your ActiveCampaign data to any data warehouse is accessing and extracting it.

ActiveCampaign has a well-designed API structured around REST, HTTP, and JSON, sufficient for interacting with the platform programmatically. API endpoint URLs are organized around resources, such as connections or deals.

The available resources include the following:

1. Users
2. Deal
3. Pipelines
4. Deal Stages
5. Deal Tasks
6. Deal Task Types
7. Organizations
8. Connections
9. E-commerce Customers
10. E-commerce Orders
    

You need to consider the following factors while dealing with the ActiveCampaign API:

1. Rate limits: There is no restriction regarding rate limits in v3 of the API, currently in beta. However, the older version describes the rate limits as five requests per second per account.
2. Authentication: You can authenticate on ActiveCampaign with simple token authentication using the user's API key in the request header.
3. Pagination: API endpoints that return a collection of items are always paginated. The number of results to display can vary with a maximum value of 100.

Transform and prepare your ActiveCampaign data for Google BigQuery

After you have accessed data on ActiveCampaign, you will have to transform it based on two main factors:

1. The limitations of the database that is going to be used
2. The type of analysis that you plan to perform

Each system has specific limitations on the data types and data structures that it supports. If you want to push data into BigQuery, you can send nested data like JSON directly. When dealing with tabular data stores like Microsoft SQL Server, however, this is not an option. Instead, you will have to flatten out your data before loading it into the database.

Also, you have to choose the right data types. Again, depending on the system you will send data to and the data types that the API exposes, you will have to make the right choices. These choices are important because they can limit your queries' expressivity and limit your analysts on what they can do directly out of the database.

Load data from ActiveCampaign to Google BigQuery

If you want to load any data from ActiveCampaign to BigQuery, you have to use one of the following supported data sources.

1. Google Cloud Storage
2. Sent data directly to BigQuery with a POST request
3. Google Cloud Datastore Backup
4. Streaming insert
5. App Engine log files
6. Cloud Storage logs

From the above list of sources, 5 and 6 are not applicable in our case.

For Google Cloud Storage, you first have to load your data into it. There are a few options on how to do this. For example, you can use the console directly as described here and do not forget to follow the best practices.

Another option is to post data through the JSON API. As we see again, APIs play an important role in both the extraction and the loading of data into our data warehouse. It's just a matter of one HTTP POST request using a tool like CURL or Postman in its simplest case.

After you have loaded data into Cloud Storage, you have to create a Load Job for BigQuery to load every data into it. This Job should point to the source data in Cloud Storage that have to be imported. You can do this by providing source URIs that point to the appropriate objects.

The best way to load data from ActiveCampaign to Google BigQuery

In this post, we barely scraped the surface of what you can do with BigQuery and how to load any data into it. In reality, things can get even more complicated if you want to integrate data coming from different sources.

Instead of writing, hosting, and maintaining a flexible data infrastructure, RudderStack can handle everything automatically for you.

With one click, RudderStack integrates with your sources or services, creates analytics-ready data, and syncs your ActiveCampaign to BigQuery right away.

Help your marketing and executive team take ownership of the insights that live in your ActiveCampaign email marketing platform to transform the performance and ROI of your campaigns.