Back to Blog

Connect Stripe to BigQuery in an automated and manual way

Stripe offers limited analytical capabilities so it’s no wonder many choose to export their financial data outside of the platform. BigQuery is a common destination for such data, both for those that seek more extensive analytical features and others who would gladly just back up their financial data. 

As you can expect, there’s more than one way to connect Stripe to BigQuery. In fact, there are at least half a dozen methods you could get working for you, depending very much on your skillset, environment, and requirements. I picked three of them that I thought were the most viable – they don’t require an extensive setup and most can be customized to fit your project.

Methods to load data from Stripe to BigQuery

In this article, I’m going to cover the following ways to connect Stripe to BigQuery:

  • Fully automated export to BigQuery using a third-party solution – saves plenty of time, gives complete control over the exported data, and also requires no coding. Comes with a separate subscription.
  • Manual exports from Stripe – feasible for one-time exports, and doesn’t require any technical skills. Otherwise quite limiting and repetitive.
  • Stripe Sigma and webhooks – can be automated and customized to your liking, requires an additional subscription fee, and is not suitable for non-technical users.

I’ll cover each of these methods in the following chapters.

Automated Stripe to BigQuery integration

If you plan to repeatedly fetch data from Stripe to BigQuery, you should probably look into automating the process. A very popular choice among Stripe users is using third-party apps like Coupler.io that you can set up within minutes and have the desired data loaded into BigQuery with no code. Simple as that.

Coupler.io is an all-in-one data automation and analytics platform that allows for fetching data from 60+ apps, including Airtable, QuickBooks, Xero, Salesforce, Facebook Ads, and many more. BigQuery is one of four available destinations while others include Google Sheets, Excel, and Looker Studio. Coupler.io lets you blend data from different apps, transform it, and load it to either destination on a schedule you choose.

Let’s see it in action as I connect Stripe to BigQuery.

  • If you don’t have one yet, create a Coupler.io account. A free 14-day trial is available, no credit card is required.
  • Pick Stripe as a source app and BigQuery as a destination.
  • Following the wizard, connect your Stripe account and choose the data entity you want to fetch. Don’t worry – if you want to fetch multiple entities, it will be as simple later as duplicating this source and tweaking some settings.
2 invoices export
  • Create some additional filters if you’d like. You’ll then see the preview of your data – it may take a moment or two to load if you have plenty of data to fetch.
  • In the Transform module, you can add or remove columns, create new calculated fields, apply filters, sorting, and more. Tweak the data to your liking and hit Proceed.
3 transform module
  • Next, set up BigQuery as a destination for your data. You’ll need to:
    • create a service account with the permissions BigQuery Data Editor and BigQuery Job User.
    • Create a new key for this account and save it as JSON.
    • Upload the JSON file to Coupler.io. More on this setup in the Coupler.io knowledge base.
  • Back in Coupler.io, choose the dataset and table for your import. Specify the schema or have it auto-detected.
  • Then, run the importer. And here’s a sample invoices export sent from Stripe to BigQuery:
4 stripe export in bq
  • Before you dive into your data, it’s worth automating the imports so that the data refreshes at the schedule you choose. To do that, jump back to Coupler.io and enable Automatic data refresh. Set up a schedule and save. 
5 import schedule

That’s all!

Manual Stripe to BigQuery export

Stripe offers a basic ability to export particular data entities as .csv files. You can then upload such a file to Google Cloud Storage and BigQuery. This approach will work fine if you need to analyze some specific type of data, for example, the list of invoices from a specific period. The drawback of this method is that it requires plenty of effort every time you export. What’s more, you can’t export different types of data at once nor customize the exported data (e.g. fetch only invoices worth $1,000 or more).
Follow these steps to manually connect Stripe to BigQuery:

  • Sign in to Stripe and jump to the category you want to export.
  • Click the Export button to the right and adjust the criteria. If you don’t want to fetch all columns, you can customize that too. Then, click the Export button and the .csv file will be saved.
6 export customers
  • Then, open the BigQuery console and click to create a table for any of the existing or new datasets. In the Create table from menu pick Upload. Find your file and change the file format to CSV. Specify the schema or have BQ auto detect it and then click Create table.
7 upload csv

If you prefer, you can load the file to Google Cloud Storage first. Then, load it to BigQuery using the following syntax:

LOAD DATA OVERWRITE {{your dataset}}.{{your table}}
FROM FILES (
  Format = 'CSV',
  Uris = ['gs://bucket/path/file.csv']);

Stripe Sigma and webhooks for automated exports to BigQuery

Stripe has its own product called Stripe Sigma that’s meant to help you access your data. You can use SQL to extract the type of data you need and build reports or push it further into tools like BigQuery with Python, for example. Sigma is a paid add-on, review the handy pricing calculator for more about the estimated costs you would incur. 
To load data from Stripe to BigQuery using Sigma, you’ll need to do the following:

  • Sign in to Stripe Sigma and schedule a recurring SQL query.
  • Configure a webhook to receive the results of a query.
  • Write a script that will load the payload to BigQuery.

That’s it in a nutshell. Now let’s go to step-by-step instructions:

  • Sign in to Stripe and navigate to the Developers section. 
  • In the API keys section, create a secret key and save it for later use.
  • In BigQuery, create a service account with the following permissions: BigQuery Data Editor and BigQuery Job User. Create also a new key for this account and save it as a JSON file.
  • Back in Stripe navigate to More -> Sigma. Start a free trial if you haven’t used the service yet.
  • Once your data is ready, click on Scheduled Queries and add a new one. Write an SQL that will retrieve the desired data from Stripe, following the table schema you’ll see to the left. For example, the following retrieves code for some vital invoice details:
SELECT
  id,
  date,
  total,
  due_date,
  paid as is_paid
FROM
  invoices
  • Run the code to make sure it returns the right information. If so, save it and hit the Schedule button to choose how often the code should run, for example, daily.
8 schedule
  • You can include one or more subscribers that will be sent a CSV file with the results of a query, every time it runs. Technically such a person could then upload a file to BigQuery but this would create unnecessary workload. For that reason, it’s better to either:
    • send the query results to BigQuery over API or 
    • use webhooks to retrieve the query results file and then load it into BigQuery. 
  • I’ll show you the webhooks method but both are perfectly viable.

When a scheduled query runs, Stripe sends the sigma.scheduled_query_run.created event. Setting up a webhook to listen to this event, you’ll be able to retrieve its payload which also features a path to a file with query results.

  • To set up a webhook in Stripe, jump to the Developers tab, then Webhooks and click + Add endpoint. Set it up to listen to sigma.scheduled_query_run.created and provide a link to your endpoint.

Here’s what a payload of sigma.scheduled_query_run.created event looks like:

{
  "object": "event",
  "pending_webhooks": 2,
  "created": 1502793192,
  "type": "sigma.scheduled_query_run.created",
  "livemode": true,
  "request": null,
  "data": {
    "object": {
      "id": "sqr_Jxhi5BvNIytZtVT0XQG4",
      "object": "scheduled_query_run",
      "status": "completed",
      "data_load_time": 1504356600,
      "file": {
        "id": "{{ FILE ID }}",
        "object": "file",
        "url": "https://files.stripe.com/v1/files/{{ FILE ID }}/contents",
        "created": 1507842188,
        "purpose": "sigma_scheduled_query",
        "size": 55035,
        "type": "csv"
      },
      "title": "Invoices load",
      "sql": "SELECT id, date, total, due_date, paid as is_paid FROM invoices",
      "created": 1524754194,
      "result_available_until": 1505393633,
      "error": null,
      "livemode": true
    }
  }
}
  • Notice the URL to the query results that resides at a data.object.file.url subfield. You can retrieve it using the curl command, inserting your API key. For example:

curl https://files.stripe.com/v1/files/{{ FILE ID }}/contents -u sk_live_123456789:

  • Alternatively, write a script that will listen to this event and load the payload to BigQuery. Here’s an example in Flask:
from flask import Flask, request
import stripe
from google.cloud import bigquery

from google.oauth2 import service_account
credentials = service_account.Credentials.from_service_account_file(
'path/to/file.json') # path to your BQ service account fee

app = Flask(__name__)
stripe.api_key = 'YOUR_STRIPE_SECRET_KEY'  # Replace with your actual Stripe secret key
bigquery_client = bigquery.Client(project='YOUR_PROJECT_ID', credentials=credentials)  # Replace with your actual BigQuery project ID

@app.route('/webhook', methods=['POST'])
def webhook():
    event = stripe.Event.construct_from(request.json, stripe.api_key)
    
    if event.type == 'sigma.scheduled_query_run.created':
        file_url = event.data.object.file.url
        
        # Load the data from the file into BigQuery
        job_config = bigquery.LoadJobConfig(
            source_format=bigquery.SourceFormat.CSV,
            skip_leading_rows=1,
            autodetect=True,
        )
        
        load_job = bigquery_client.load_table_from_uri(
            file_url,
            'YOUR_DATASET.YOUR_TABLE',  # Replace with your actual BigQuery dataset and table name
            job_config=job_config,
        )
        
        load_job.result()  # Wait for the job to complete
    
    return 'Your Stripe data is now in BigQuery!', 200

if __name__ == '__main__':
    app.run(port=5000)

Be sure to insert your API Key and the required BQ details in the respective places in the code. Add also a path to the JSON file you saved moments ago. For example, if you saved the JSON key file as my_key.json in the same directory as your Python script, you can replace 'path/to/service-account-key.json' with 'my_key.json'. For automating this Stripe to BigQuery script, you can set up, for example, a cron job.

Note: You can load data from Stripe to a BigQuery table also without Stripe Sigma. You may still want to rely on webhooks but rather than listen to sigma.scheduled_query_run.created event and fetch results of queries, you would listen to particular events, such as invoice_created. Then, simply load their payload to BigQuery with a script similar to what I put above. This method isn’t as convenient as its predecessor but can certainly be more cost-efficient.

How to connect Stripe to BigQuery – recap

There are certainly more ways to connect Stripe to BigQuery but the three I mentioned should do the job for virtually everyone. Each approach is different so let’s have a quick recap so you can choose the most suitable one.

  • If you’re looking for a simple yet powerful solution, Coupler.io will work perfectly. It’s suitable for non-technical audiences but thousands of engineers and analysts also use it with lots of success. It’s also arguably the simplest way to automate Stripe to BigQuery exports.
  • If you need a simple one-time method to export some data from Stripe, check if the manual approach will do the job for you. It’s quick to set up and doesn’t require any technical expertise.
  • If you prefer to write code, then combining Stripe Sigma and Webhooks or relying solely on the latter will come in handy. This solution gives you all the flexibility you need but is naturally more complex and time-consuming both in terms of initial setup and code maintenance.

By the way – if you need a custom solution and none of these methods work for you, then chances are we can help you out. Coupler.io offers data analytics consultancy services through which we help businesses make sense of their data. This can involve, for example, connecting Stripe data to the information flowing from the other apps you use, setting up automation, data alerts, or interactive dashboards in the tools of your choice. 

If this sounds like something you would like to take advantage of, then feel free to schedule a free consultation to talk about your project.
Thanks for your time!

Streamline data analytics & reporting

Streamline your data analytics & reporting with Coupler.io!

Coupler.io is an all-in-one data analytics and automation platform designed to close the gap between getting data and using its full potential. Gather, transform, understand, and act on data to make better decisions and drive your business forward!

  • Save hours of your time on data analytics by integrating business applications with data warehouses, data visualization tools, or spreadsheets. Enjoy 200+ available integrations!
  • Preview, transform, and filter your data before sending it to the destination. Get excited about how easy data analytics can be.
  • Access data that is always up to date by enabling refreshing data on a schedule as often as every 15 minutes.
  • Visualize your data by loading it to BI tools or exporting it directly to Looker Studio. Making data-driven decisions has never been easier.
  • Easily track and improve your business metrics by creating live dashboards on your own or with the help of our experts.

Try Coupler.io today at no cost with a 14-day free trial (no credit card required), and join 700,000+ happy users to accelerate growth with data-driven decisions.

Start 14-day free trial
  • Piotr Malek

    Technical Content Writer on Coupler.io who loves working with data, writing about it, and even producing videos about it. I’ve worked at startups and product companies, writing content for technical audiences of all sorts. You’ll often see me cycling🚴🏼‍♂️, backpacking around the world🌎, and playing heavy board games.

Back to Blog

Comments are closed.

Focus on your business
goals while we take care of your data!

Try Coupler.io