Create a View in Google BigQuery

How do I easily query all the Funnel data in BigQuery, and how do I reformat the data to make sense to me?

Ilona Norman avatar
Written by Ilona Norman
Updated over a week ago

Once you have data flowing from Funnel to BigQuery you may want to tweak the format of the data to make it fit better with your intended queries. By creating a View in BigQuery you can set up some defaults for yourself that you will not have to repeat in all of your queries later. Some examples of formatting that may be useful for you are:

  • Combine all the data from the separate monthly tables into one logical view

  • Include only a smaller selection of metrics, to make it easier to find what is relevant to you

  • Change the names of the technical identifiers in the sourceType column to be more human readable.

Let Funnel create a view

For an export in Funnel click 'Show advanced settings' and enable 'Let Funnel create and manage a view' and Funnel will create a view that combines the data from all the monthly tables.

Manually create a view in BigQuery

In order to create a view in BigQuery start at https://bigquery.cloud.google.com/ and hit the "Compose new " button and set up a SQL query that will match your needs.

An example query to get you started may be this:

WITH ad_platforms AS

  (SELECT "twitter-demo" AS sourceTypeId, "Twitter" AS sourceTypeName

  UNION ALL

  SELECT "bing-demo" AS sourceTypeId, "Bing" AS sourceTypeName

  UNION ALL

  SELECT "adwords-demo" AS sourceTypeId, "Google" AS sourceTypeName)

SELECT date, if(sourceTypeName is not null, sourceTypeName, sourceType), sourceName, common_campaign as campaign, common_cost as cost, common_clicks as clicks, common_impressions as impressions

FROM `funnel-example.funnel_demo.funnel_data_*`

LEFT OUTER JOIN ad_platforms a on a.sourceTypeId = sourceType

The example above will combine data from all monthly tables, change the names of some ad platforms, convert the cost to "dollars and cents", and filter out only a few relevant metrics. 

When you have found a query that fits your needs click "Save View" and pick a name for this new virtual table. Remember to avoid names that start with "funnel_data_" (or what ever table name standard you use) to not break the wildcard select.

After your view is set up you should be able to write your queries using this view as a regular table. It will always be up-to-date with the underlying data.

Did this answer your question?