How do I connect Klipfolio and Google BigQuery?

You can set up a connection between Klipfolio and BigQuery to track your data on a dashboard. Use the steps in this article to learn how set up that connection.

What is Klipfolio?

Klipfolio integrates with many cloud services and tools to create beautiful data visualizations.

How do I get started with Google BigQuery?

You have the following option for visualizing your data on a dashboard in Klipfolio:

Use the Connector Gallery
The Connector Gallery helps you get your data fast with pre-built queries. Use this article as a guide to build your own Google BigQuery data source.

If you want to build a data source entirely from scratch, use the Advanced section below.

Authenticate with Google Bigquery

You can choose to connect with Google BigQuery using the Connector Gallery or the Klip Gallery in Klipfolio. Either way, the first thing you will be asked to do is authenticate and create a handshake connection between your Klipfolio and Google accounts.

  • Click Connect to Google BigQuery.
  • Enter your Google login credentials.
  • Click Next Step.
    Note: You now have an OAuth Token for your Connected Account. You can manage your tokens in the Account section under Connected Accounts.

Prerequisite: Find your Project ID

To get the right data from Google BigQuery, you'll need to provide your Project ID and use it in Klipfolio.

To find your Project ID, follow these instructions in the Google BigQuery API documentation.

Or, you can use the pre-built Quickbooks query Projects to get a list of projects and their corresponding ID's. Simply choose Projects from the list of query options.

Advanced: Build a data source yourself

Klipfolio connects to hundreds of services in the cloud that have a REST API. If you take the time to learn how to get data into Klipfolio using APIs, your possibilities are endless. Here's a quick and simple overview video on APIs and Klipfolio:

  Klipfolio API's 101

If APIs are a bit too tricky for you, enlist a data analyst or developer to help you.

If you want to edit a Google BigQuery data source that you already created, check out How to edit or reconfigure a data source, and get step-by-step instructions.

Google BigQuery quick reference table

Authentication OAuthToken Authentication
API Documentation Google BigQuery API documentation

 

Advanced query examples

First, In the Google Developer Console, make sure that you have Google BigQuery API enabled. Then, refer to the Google BigQuery API Documentation to write the query that brings back the data you need.

Now set your query parameters in Klipfolio:

At URL, enter
https://www.googleapis.com/bigquery/v2/projects/YourProjectID​/queries

For POST queries only: At Body, enter your query.

Here are two examples:

{
'query':'SELECT status FROM testdata.tes5 WHERE status = "Received";'
}

and,

{
"query":"SELECT s.date AS d, xp.GROUP AS g, SUM(sessions) AS num_sessions FROM route_sessions.all_sessions s INNER JOIN route_sessions.route_stats xp ON s.entity_id == xp.entity_id WHERE s.date >= '2015­11­05 00:00' AND s.date '2015­12­03 00:00' AND s.medium == 'organic' and s.lang = 'es' GROUP BY d,g ORDER BY d,g"
}

Once you are done your query, click Get Resource.

Related Links