BigQuery is a serverless, fully managed, petabyte-scale data warehouse hosted by Google Cloud. ChartMogul allows you 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 service account
• Creating a Cloud Storage bucket
• Assigning permissions
• Allow listing IP addresses
• Adding Google BigQuery as a destination

Before you begin

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

• You'll need to have a Google Cloud project for your BigQuery cluster and Cloud Storage bucket. Please review the steps to create a Google Cloud project.
• You'll need to select a location for your BigQuery dataset. Learn more about dataset locations.
• 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.
• You'll need to choose a name for your Cloud Storage bucket. Please review Google's bucket naming guidelines as part of this.

Step 1 — Create a Cloud Storage service account

First, create a service account that will have permission to access the bucket. 

1. Within Google Cloud Platform, navigate to IAM and Admin > Service Accounts.
2. Click + Create Service Account.
   
3. Enter a name for the service account, e.g., ChartMogul, in the Service account name field.
4. Enter an identifier for the account in the Service account ID field.
5. Enter a description of 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 ellipse 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 2 — 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 and click Continue.
3. Within Choose how to control access to objects, restrict your data from being publicly accessible by checking Enforce public access prevention for this bucket.
   
4. Complete any additional setup options you may need, then click Create.

Step 3 — Assign permissions to the bucket

1. Within Bucket details, navigate to Permissions .
   
2. Click + Add.
3. In the New principals field, enter the email address associated with the service account you created in Step 1.
4. Select Storage Object Admin from the Role drop-down.
5. Click Save.

Step 4 — Allow list IP addresses

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 5 — Add Google BigQuery as a destination

Finally, add Google BigQuery as a destination in ChartMogul:

1. Within ChartMogul, navigate to Data Platform > Destinations.
2. Click Add a 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
     • Contacts
     • Currency Rates
     • Custom Attributes
     • Customers
     • Invoice Line Items
     • Invoices
     • Plans
     • Subscription Events
     • Tags
     • Transactions
     • Data Sources
   • Calculated Data
     • Customers
     • MRR Movements
   
6. Choose your dataset location from the Location drop-down.
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 perform a test connection and, if successful, add BigQuery as a new destination.