BigQuery is a serverless, fully managed, petabyte-scale data warehouse hosted by Google Cloud. Configure Google BigQuery as one of ChartMogul’s advanced destinations to export MRR movements, customers, custom attributes and tags to BigQuery tables. From there, you can create your own views and combine datasets for further analysis.

Here's what we cover in this article:

• Creating a Google Cloud project
• Creating a service account
• Creating a Cloud Storage bucket
• Assigning permissions
• Allow listing IP addresses
• Adding Google BigQuery as a destination

Resources and further reading:

• Destinations
• Datasets in Destinations

Before you begin

Please review the following details before completing the steps we outline in this article.

• ChartMogul sends data to BigQuery using the provided Google Cloud Storage bucket as a staging area. It first uploads data using the parquet data storage format parquet files to the bucket and then moves the data into BigQuery tables. ChartMogul refreshes (overwrites) the data in these tables as part of its daily sync.
• If you have already configured Google Cloud Storage as a destination, we recommend creating and configuring a new bucket for your BigQuery destination.

Step 1 — Create a Google Cloud project

If you don’t have a Google Cloud project for your BigQuery cluster and Cloud Storage bucket, create one:

1. Within Google Cloud Platform, navigate to IAM and Admin > Create a Project.
2. Enter a Project name.
3. Optionally, click Browse in the Location field to select your Google Workspace organization.
4. Click Create.

Step 2 — Create a Cloud Storage service account

Then, create a service account that will have permission to access the bucket. After creating the service account, you’ll download the account’s private key as a JSON file, which you’ll need when creating the destination in ChartMogul.

1. Within Google Cloud Platform, use the Select a project drop-down to choose the project for your BigQuery cluster and Cloud Storage bucket (the one you created in the previous step or an existing project).
2. Navigate to IAM and Admin > Service Accounts.
3. Click + Create Service Account.
   
4. Enter a name for the service account, e.g., ChartMogul, in the Service account name field.
5. Optionally, adjust the automatically generated Service account ID and describe what the service account will do in the Service account description field.
6. Click Create and Continue.
7. Then, within Grant this service account access to your project, select BigQuery Job User in the Role field.
8. Click + Add Another Role.
9. Select BigQuery Data Owner in the Role field.
10. Click Done.
11. Once Google has created the account, find it in the Service accounts table, click the vertical ellipsis Actions icon and select Manage keys. Next, click Add Key > Create a new key.
    
12. With JSON selected as the Key type, click Create. Your browser should then download the private key as a JSON file.
13. Finally, navigate to Details and note your email, as you'll need this when setting up permissions.

Step 3 — Create a Cloud Storage bucket for ChartMogul

Next, create a new Cloud Storage bucket:

1. Within Google Cloud Platform, navigate to Cloud Storage and click + Create Bucket.
2. Name your bucket according to Google’s bucket naming guidelines. Click Continue.
3. Within Choose where to store your data, select a Location Type (Multi-region, Dual-region or Region) and your bucket’s geographic location from the Location drop-down menu. Click Continue.
4. Optionally, adjust the settings within Choose a storage class for your data to control your costs for storage, retrieval and operations. Click Continue.
5. Within Choose how to control access to objects, restrict your data from being publicly accessible by checking Enforce public access prevention for this bucket. Click Continue.
   
6. If needed, configure data protection tools within Choose how to protect your data.
7. Click Create.

Step 4 — Assign permissions to the bucket

Now, grant the service account access to the Cloud Storage bucket:

1. Within Bucket details, navigate to Permissions.
   
2. Click Grant Access.
3. In the New principals field, enter the email address associated with the service account you created in Step 2. You’ll find the email in your service account’s Details section.
4. Select Storage Object Admin from the Role drop-down.
5. Click Save.

Step 5 — Allow listing IP addresses

If your Google Cloud account restricts access to specific IP addresses, configure network access:

1. Navigate to Security > Access Context Manager.
2. Click + Create Access Level.
3. Enter an Access level title.
4. Within Conditions, click + IP subnetworks section and add the following entries:
   • 3.248.174.47/32
   • 99.80.87.208/32
   • 34.248.167.65/32
   
5. Click Save. Note the access level name.
6. Navigate to VPC Service Controls and click + New Perimeter.
7. Enter a Perimeter Title.
8. Navigate to Restricted Services.
9. Click ADD SERVICES.
10. Search and select BigQuery API and click ADD BIGQUERY API.
    
11. Navigate to Access Levels and select the name of the access level you configured and click Ok. Finally, click Create Perimeter to complete network access.

Step 6 — Add Google BigQuery as a destination

Finally, add Google BigQuery as a destination in ChartMogul:

1. Within ChartMogul, navigate to Settings & Data > Destinations.
2. Click Add Destination and select Google BigQuery.
3. Enter a unique name for your destination in the Name field.
4. Choose how often you'd like ChartMogul to export MRR movement data to your bucket, either:
   • Daily (9:00 UTC each day),
   • Weekly (9:00 UTC each Sunday) or
   • Monthly (9:00 UTC on the first day of each month).
5. Choose the datasets you’d like to export:
   • Source Data
     • Contact Custom Attributes
     • Contacts
     • Currency Rates
     • Custom Attributes
     • Customers
     • Notes and Call Logs
     • Invoice Line Items
     • Invoices
     • Plans
     • Opportunities
     • Opportunity Custom Attributes
       
     • Subscription Events
     • Tags
     • Tasks
     • Transactions
     • Data Sources
   • Calculated Data
     • Customers
     • MRR Movements
   
6. ChartMogul will create its own BigQuery dataset with the selected tables. Specify where you want to store the dataset using the Location drop-down. Learn more about BigQuery locations.
7. If your organization has a dataset naming convention, add it in the Dataset Prefix field. If left blank, ChartMogul creates a dataset on your behalf.
8. Enter the name of your bucket as it appears on your Google Cloud Storage account in the Bucket name field.
9. Upload the service account's private key JSON file your browser downloaded in Step 1.
10. Click Save and Test Connection.

ChartMogul will send test data and, if successful, add BigQuery as a new destination. ChartMogul will send actual data to BigQuery with the next scheduled export depending on your Update Frequency setting.