The guide will go through the following steps in order for you to set up an export from Funnel to Google BigQuery and into your visualization tool.
BigQuery is a marketing data warehouse, in simple terms, you can think of it as a combination of a spreadsheet and a database management system, such as MySQL. BigQuery is a cloud service provided by Google which means you do not need to install anything, you need to signup and start using it.
Getting started with BigQuery is quite cheap, as you can signup for free. BigQuery follows a query-based pricing model which is tied more directly to your usage. So if you have small to medium reporting needs the costs should be manageable easily.
1. Setting up a new BigQuery instance
1.1 Select or create a new project
You start with going to the Google Cloud Platform. If this is the first time ever, then you have to select a country and agree to terms of service.
After that either create a new project if it's your first time by clicking on Create Project and following the instructions. Or if you have used the platform before, proceed by selecting an existing project which you want to use for your BigQuery.
1.2 Navigate to BigQuery
After that, navigate to BigQuery – you can use either the search bar or find it manually in the left menu. Below is what BigQuery looks like on your first visit.
Name of your Google Cloud Project which is selected now
SANDBOX means that you’re using a sandbox account, which does not require you to enter payment information. This free tier option grants you 10 GB of active storage and 1 TB of processed query data per month. Using this account, your tables will expire in 60 days
Name of all the BigQuery projects you have access to. You will use this id in Funnel when setting up an export.
Query window where you can use SQL to explore your data
1.3 Billing accounts and their limitations
To start using BigQuery for funnel data you have to enable billing as it has limitations on the amount of data it can store, and the tables expire after a few days. To remove the sandbox limits, you can upgrade your project and then remove or update the expiration time for your resources. To manage BigQuery quotas, consider setting up cost controls.
1.4 BigQuery usage and pricing
Bigquery charges based on usage and storage and by default when you start is called on the demand analysis pricing. You can read more about pricing on this page
We have seen that the pricing is not that high for smaller Funnel use-cases with a few ad platforms and a web tracking tool connected to Funnel. This cost can be controlled and limited by setting daily quotas in BigQuery.
1.5 [Optional] Setting up your own dataset
If you don't mind Funnel creating datasets in your BigQuery project then you can skip this step.
To configure Funnel to export the data to a specific dataset you can follow Google BigQuery Dataset configuration steps highlighted, and then use that in your Funnel export.
2. Product Tour: Export Funnel data to BigQuery
Once you have set up your Google Cloud Console and enabled billing you are ready to start using Funnel's export. You can follow the product tour below which explains how to achieve it in 5 mins.
2.1 [Optional] If you want your tables to be partitioned
What are Partitioned Google BigQuery tables and how you can configure them through Funnel.
2.2 [Optional] BigQuery summary table export
If you want to get an overview of historical jobs and the latest status of your exports, you can also export a Google BigQuery summary table.
3. Connecting Google BigQuery to your favorite visualization tool (using a BQ view)
When Funnel sends data to BigQuery it is sent in monthly chunks and each month is stored in its own table. When visualizing data in a dashboard is usually over multiple months, Funnel is therefore also exporting a view. A view is a pre-defined query from underlying tables, the view Funnel export combines the monthly tables and makes it possible to work with all the data as it would have been exported in one table.
When BigQuery is connected with a visualization tool can that be considered as a live connection. The data in the visualization is the same as it looks in BigQuery and there are no delays.
3.1 Connect Data Studio to BigQuery
Select BigQuery as a Connector on the create new data source page in Data Studio.
Select your project and connect the view.
3.2 Connect Power BI Desktop with BigQuery
Start by opening Power BI Desktop and click on Get Data in the top left corner.
The next steps on how to connect Bigquery with Power BI can be found in this article from Microsoft. Please select the view when connecting which data you like to work within Power BI.
3.3 Other tools (Tableau, Qlik, Looker)
Most visualization tools have a direct integration with BigQuery making it easy to set up the connection.