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
- Reformat the Funnel monetary values, that are multiplied by 100000 by default.
- Change the names of the technical identifiers in the sourceType column to be more human readable.
Note that Funnel can create a view for you. 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.
In order to create a view in BigQuery start at https://bigquery.cloud.google.com/ and select one of your
funnel_overview_ tables. Hit the "Query Table" 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
SELECT "bing-demo" AS sourceTypeId, "Bing" AS sourceTypeName
SELECT "adwords-demo" AS sourceTypeId, "Google" AS sourceTypeName)
SELECT date, if(sourceTypeName is not null, sourceTypeName, sourceType), sourceName, dim_1bca8omvo_936e as campaign, common_cost/1000000 as cost, common_clicks as clicks, common_impressions as impressions
LEFT OUTER JOIN ad_platforms a on a.sourceTypeId = sourceType
To run the query, first click "Show Options" and make sure the option labeled "Use Legacy SQL" is not enabled.
The example above will combine data from all monthy 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_overview" to not break the wildcard select from the original tables.
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.