Skip to main content
Sign in

Part 3: How to build a Query for BigQuery

Erik van Dorp
  • Updated

This support article shows you how to build a Query using the Cervinodata Query builder.

Before you start

  • You need to have completed part 1 and part 2 of this 3-part set up process

Tip: it helps to have Cervinodata open in one browser tab and your Google BigQuery project in another browser tab. 

Step 1: In Cervinodata

  • Log into Cervinodata https://app.cervinodata.com/login
  • In the left menu, under DELIVER > Google BigQuery, click "Build a query"
  • You are now in the Query builder page. All your saved queries will be shown here.

Cervinodata_BigQuery_builder_overview.png

  • Click "the button Add Query". The query builder will open.

Screenshot_2020-07-02_at_16.46.54.png

 

Select Organisation

  • First, choose one or more Organisations.
    • Accounts are grouped by Organisations.
    • If you did not create Organisations or if you did not connect one or more accounts to an organisation, don't worry. The "Default Organisation" is connected to all accounts that are not connected to other Organisations.
    • The Query builder will select all the specified data of all the switched ON accounts connected to the selected Organisation.
    • If you want to group accounts by Organisation, go to the "Create Organisation" page in Cervinodata.

Select data type

  • Now select the data type.

Select_data_BigQuery_builder.png

Select platform(s)

  • When you have chosen Advertising data in the previous step, you need to know the following:
    • When you only select one ad platform, for instance Facebook Ads, all metrics that are in the data model related to Facebook Ads will be included (see full list here) 
    • When you choose multiple ad platforms, the query builder will take only take into account the generic cross-platform metrics: impressions, media spend, clicks, conversions, revenue (as measured by the platform).

Select_platforms_Google_BigQuery_builder.png

(Optional) Select campaign group(s)

The next step is optional. In this step you can filter your query results by one or more campaign groups. Campaign groups are powerful filters that allow you to group specific campaigns from multiple ad platforms together in a group. See more about that here: How to build a campaign group

  • If you only want your query result to include data from the campaign groups you have created, select the campaign group(s) here. 
  • If you want all the available data from the campaigns from the connected accounts, you can skip this part.

Filter_by_campaigns_BigQuery_builder.png

Select data granularity & date filter

  • Specify if you want your data grouped by day, week or month. Data grouped by day will result in a larger result table (approximately 30 times larger than grouping per month)
  • Specify the start data to be included in your selection.

copy_SQL_BigQuery_builder.pngFinal step in Cervinodata: Copy your query

  • Once you have done all the previous steps in the query builder, you can copy the query.
  • If you want, you can SAVE the Query for later use.

In Google BigQuery

  • Open the other tab (with BigQuery) or go to your BigQuery project
  • Paste the BigQuery into your BigQuery project. Tip: Make sure you are in the BigQuery project you have connected Cervinodata to, otherwise the query will return an error.
  • Run the Query
  • You can now save the query by clicking "Save view" (see screenshot below) or schedule this query to run periodically. See schedule query for more information about scheduling BigQuery queries.

save_bigQuery_query_builder.png

That's it. If you need any help, feel free to schedule some time with our support team to help you out. support@cervinodata.com

Next read

 

 

Related articles