Coupler.io Blog

Connect Stripe to BigQuery in an automated and manual way

  • Piotr Malek
    • How to connect Stripe to BigQuery

    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:

    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 look into automating the process. With Coupler.io, you can set up the connection within minutes and have the desired data loaded into BigQuery with no code. Simple as that.

    That’s all! If you’re interested in exporting data from Stripe to other destinations, check out our recent guide on how to connect Stripe to Power BI.

    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:

    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:

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

    SELECT
  id,
  date,
  total,
  due_date,
  paid as is_paid
FROM
  invoices

    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.

    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
    }
  }
}

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

    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.

    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.

    For more on this topic, check our article on how to connect connect Stripe to Looker Studio.

    Automate data export with Coupler.io

    Get started for free
    Exit mobile version