How to Setup Stripe Tableau Integration in Real Time

Do you wish to analyze your payments data but find it difficult to set up Stripe Tableau integration? If yes, then you’ve come to the right place.

This article will give a step-by-step guide on setting up Stripe Tableau integration so that a proper analysis of your business performance can be performed.

Pre-requisites

1. Working knowledge of Stripe.
2. Working knowledge of Tableau.
3. An active Stripe account.
4. Tableau installed on the host workstation.

Introduction to Stripe

1. Stripe is a payment gateway tool that is primarily used by businesses across the world to accept payments online.
2. It has a suite of applications that allows businesses to collect revenue, manage frauds, and expand internationally.
3. These applications are built on a cloud-based infrastructure that provides reliability, scalability, and security.

Introduction to Tableau

• Tableau is easy to set up and use, allowing integration from various sources like file systems such as CSV, Excel, etc, relational databases such as Oracle, SQL Server, DB2, etc, or cloud warehousing systems such as Google BigQuery, Windows Azure, etc.

Methods to Set up Stripe Tableau Integration

• Method 1: Manually Exporting Data
• Method 2: Using Cloud Data Warehouses

Method 1: Manually Exporting Data

• Various data attributes that are allowed to be exported on Stripe are description, time of creation, amount, refunds (if any), currency, the amount after currency conversion, refund amount after currency conversion, fee, tax, customer ID, etc.

This method can be implemented using the following steps:

• Step 1: Exporting Data from Stripe
• Step 2: Importing Stripe Data into Tableau as CSV Files

The first step of the process would involve exporting the required data from Stripe. After logging in, select the “Payments” option on the left panel of the Stripe dashboard. The user can also choose to export data on the customer details or balance as per the requirements of the analysis.

Select the “Export” option on the upper right corner, as shown above.

Select the required filters and select “Export.” The required data will be sent to the registered email address as a CSV file that can be downloaded.

Step 2: Importing Stripe Data into Tableau as CSV Files

Once the CSV has been downloaded, the next step is to connect that file to the Tableau workspace. Upon opening the Tableau desktop application, there will be few options to open specific types of files. The left panel would have another option called “More,” which allows the user to choose from various possible file types.

This will open a file selector using which the file type is to be selected as “Character Delimited file (*.csv)” and the CSV file exported from Stripe has to be selected.

Once the right file is selected and opened in Tableau, any required analysis can be conducted.

Method 2: Using Cloud Data Warehouses

• In this method, all the required data will be moved from Stripe to a centralized cloud data warehousing platform.
• Tableau will then be connected to this data warehousing platform allowing the user to conduct the required analysis. 
• Although any cloud data warehouse can be used as per the choice of the user, Google BigQuery will be used for this example. 

This method can be implemented by using the following steps:

• Step 1: Retrieving Stripe API Keys
• Step 2: Extracting Data from Stripe
• Step 3: Loading Stripe Data into a Data Warehouse (Using Google BigQuery as an Example here)
• Step 4: Connecting the Data Warehouse to Tableau

Step 1: Retrieving Stripe API Keys

• Stripe has its own set of developer tools, documentation for which can be accessed here. It also provides a REST API that allows accessing, storing, and retrieving data. Any data returned by this API is in the JSON format.
• Stripe users can access their APIs by logging on to Stripe and selecting “API Keys” under the “Development” options on the right panel of the dashboard, as seen in the screenshot below:

User will only be able to see the API keys if it has a developer or administrator access. The user also has to ensure that the account is in live mode and not on test mode. The test mode can be turned off using the toggle button on the left panel. If these conditions are met, the user will see something like the screenshot below:

The publishable and secret keys have to be copied and saved in a safe location.

Step 2: Extracting Data from Stripe

• Any call to the Stripe API is made over HTTPS for security reasons.
• Stripe API allows basic CRUD operations over all its core resources that use data such as charges, balance, refund, customer details, etc. 
• This example makes use of curl to access the API.
• The following operations can be performed to extract data using the Stripe API:

curl https://api.stripe.com/v1/charges?limit=3 
   -u sk_test_BQokikJOvBiI2HlWgH4olfQ2:

The response would look similar to the following:

{
  "object": "list",
  "url": "/v1/charges",
  "has_more": false,
  "data": [
    {
      "id": "ch_17SY5f2eZvKYlo2CiPfbfz4a",
      "object": "charge",
      "amount": 500,
      "amount_refunded": 0,
      "application_fee": null,
      "balance_transaction": "txn_17KGyT2eZvKYlo2CoIQ1KPB1",
      "captured": true,
      "created": 1452627963,
      "currency": "usd",
      "customer": null,
      "description": "thedude@grepinnovation.com Account Credit",
      "destination": null,
      "dispute": null,
      "failure_code": null,
      "failure_message": null,
      "fraud_details": {
      }, …….

The customer object would look similar to the following:

{
  "id": "sub_7hy2fgATDfYnJS",
  "object": "subscription",
  "application_fee_percent": xxxx,
  "cancel_at_period_end": false,
  "canceled_at": xxxx,
  "current_period_end": 1455306419,
  "current_period_start": 1452628019,
  "customer": "cus_7hy0yQ55razJrh",
  "discount": xxxx,
  "ended_at": xxxx,
  "metadata": {
  },
  "plan": {
    "id": "gold2132",
    "object": "plan",
    "amount": 2000,
    "created": 1386249594,
    "currency": "usd",
    "interval": "month",
    "interval_count": 1,
    "livemode": false,
    "metadata": {
    },
    "name": "Gold ",
    "statement_descriptor": null,
    "trial_period_days": null
  },
  "quantity": 1,
  "start": 1452628019,
  "status": "active",
  "tax_percent": null,
  "trial_end": null,
  "trial_start": null
}

• Stripe Developer Tools also allow the creation of Webhooks giving users the option to stream data.
• An event can be set which could be anything from a new payment received or a certain minimum amount of transactions that have been completed.
• As soon as that event occurs, Stripe will push the required data to the Webhook.

Step 3: Loading Stripe Data into a Data Warehouse (Using Google BigQuery as an Example here)

1. Google BigQuery supports only 6 data types i.e. string, integer, float, boolean, record, and timestamp, and 2 data formats i.e. CSV and JSON.
2. The user has to ensure that the data is in the right format before it can be stored in Google BigQuery.
3. The data can be loaded in Google BigQuery by first loading it into the Google Cloud Platform using the JSON API from where a load job can be created for Google BigQuery.
4. To load the data into Google Cloud Platform, the following operations have to be performed:

POST /upload/storage/v1/b/myBucket/o?uploadType=media&name=myObject HTTP/1.1 Host: www.googleapis.com Content-Type: application/text Content-Length: 
number_of_bytes_in_file
 	Authorization: Bearer 
your_auth_token your Stripe data

The response would look similar to the following:

HTTP/1.1 200 Content-Type: application/json { "name": "myObject" }

The data has now been successfully loaded on Google BigQuery. Although Google BigQuery has been used for this example, the user can choose to use a warehouse of choice.

For more information on Data Migration on Connect Stripe to Redshift, refer to this article or for Stripe to Snowflake ETL refer to this article.

Step 4: Connecting the Data Warehouse to Tableau

1. This is the final process in setting up Stripe Tableau integration. Once all required data has been moved to Google BigQuery, it has to be connected to Tableau so that the required analysis can be conducted on the data.
2. Upon opening Tableau on the host workstation, the user will be given an option to connect to a data source. Upon selecting More” on the left panel, the user has to select “Google BigQuery,” as shown in the screenshot below:

For Tableau to connect to the data warehouse, it’ll require access to the Google account associated with the BigQuery data source. This would require the user to sign in to their Google account.

Upon successful sign-in, the user has to accept the terms and conditions and allow Tableau to access the account. Upon successfully establishing a connection to the Google BigQuery data source, the user will see a screen like this:

The user can select the desired schema on the left panel in the schema dropdown list and conduct the required analysis on it. This example shows how Google BigQuery can be connected to Tableau.

Limitations of Setting up Stripe Tableau Integration via a Data Warehouse

• The operations are too technical to be performed by someone who doesn’t have a technical background or the right experience.
• Requires a cloud data warehousing system to be set up, which might not be available.
• Since all possible data from Stripe is being transferred to the data warehouse every time, it’ll create duplicate data that has to be identified and removed manually to conduct analysis properly.

Hevo focuses on three simple steps to get you started

1. Connect
2. Integrate
3. Visualize

As can be seen, you are simply required to enter the corresponding credentials to implement this fully automated data pipeline without using any code.

Conclusion

• This article shows various methods of setting up Stripe Tableau integration to conduct an in-depth analysis of payments and customer data from Stripe to make the right business decision.
• The process can either be done manually every time analysis has to be conducted or automated.
• Discover effective strategies for streaming Stripe data and optimizing your payment data workflows. If you want to consolidate your Stripe data in a data warehouse, then try Hevo. Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.
  

FAQ on Stripe Tableau Integration