Advanced Topis / 

BigQuery Connector

BigQuery Connector

Connect Able to Google BigQuery to obtain full access to raw tracking data and use them to build reports in BI applications such as in Google Data Studio and Google Sheets.

This is useful to implement custom multi-touch reporting or run queries using historical first-party data not available in other analytics platforms.

For a simpler way to create reports in Google Data Studio using Google Analytics built-in attribution see our integrations with Universal Analytics and GA4.

To connect, open Able Dashboard, open "BigQuery" tab and follow the steps to connect Able to your Google Cloud account and choose Google Cloud project to create a database for syncing the data to.

After connection, Able will create a new BigQuery dataset named according to the selected funnel UUID. The dataset will conform to the following schema.

BigQuery Schema

The data is organized in the following way.

Visitors belong to the Funnel. Each Visitor represents a single unique customer. In Able BigQuery connector each dataset has data for a single funnel and this relationship is effectively unused.

Visitor Keys belong the Visitors. Visitor keys are unique identifiers used to identify a customer and link customer activity (Events) across platforms.

Events are events that happen to a Visitor. PageViews and Purchases are examples of the supported events.

Visitor Key reference

  • id
  • visitor_id - identifies Visitor
  • created_at - date and time when the identifier was first seen by Able
  • key_type - one of the supported key types, see Supported Visitor Keys section in REST API Reference.
  • value, origin_value - normalized key value and the value originally received by Able
  • prev_visitor_ids - if two or more Visitors were merged after link between them was established, will indicate a list of previous Visitor identifiers

Events reference

  • id
  • visitor_id - Visitor that generated this event
  • created_at - time when the event was processed
  • custom_data - Arbitrary fields sent with the event. Used when an outbound integration supports non-standard fields that are passed using this attribute.
  • deal_value, deal_currency - total value of the event
  • description - event description, used in place of the purchased item name when deal_items aren't specified
  • event_source - URL for web events, name of the integration for server-to-server and offline events
  • event_type - PageView, CompleteRegistration, Purchase etc. Supported event types depend on the integration and generally follow Facebook standard events names.
  • message - lead form message text
  • referrer_url, utm_campaign, utm_content, utm_medium, utm_source, utm_term - attribution fields for the Event. Each Event belonging to a Visitor can have different attribution. Used to implement custom multitouch attribution in the reports.
  • order_id - order id
  • deal_items_sku - comma-separated list of the SKUs in the purchase
  • lead_first_name, lead_last_name, lead_company, lead_country, lead_industry - standard lead parameters. A Visitor may have different values of these parameters for different Events, for example, if a contact form was completed twice by different contacts belonging to the same customer/Visitor.
  • client_ip, client_ua - details of the browser for web events
  • prev_visitor_ids - if two or more Visitors were merged after link between them was established, will indicate a list of previous Visitor identifiers

Query samples

Select campaign sales grouped by date

For example, to report on sales originating from 10955985580 campaign from the funnel/dataset 'funnel_mark', use the following query:

SELECT
  /*e.visitor_id, e.order_id,*/
  SUM(e.deal_value),
  /*e.description,*/
  DATE(first_touch.created_at) AS date/*,
  first_touch.utm_source,
  first_touch.utm_medium,
  first_touch.utm_campaign,
  first_touch.referrer_url*/
FROM funnel_mark.events AS e
INNER JOIN funnel_mark.visitors AS v ON (e.visitor_id = v.id)
LEFT JOIN (
  SELECT
    *,
    ROW_NUMBER()
  OVER(PARTITION BY visitor_id ORDER BY id) AS row_num 
  FROM funnel_mark.events AS e2
  WHERE
      (referrer_url IS NOT NULL AND referrer_url != '') OR
      (utm_source IS NOT NULL AND utm_source != '')
) AS first_touch ON (first_touch.visitor_id = e.visitor_id)
WHERE
  e.event_type='Purchase'
AND
  first_touch.row_num=1 /* Use first known attribution event */
AND
  first_touch.utm_campaign = '10955985580' /* Campaign example */
GROUP BY date;

In this query, WHERE condition applies three conditions:

  1. e.event_type = ‘Purchase’ selects only Purchase events
  2. first_touch.row_num = 1 means that the first-touch attribution is used for determining customer source (takes attribution from the first known event)
  3. first_touch.utm_campaign =  '10955985580' filters by campaign. You can use utm_source or landing_page alternatively here for example – or remove third condition altogether to see total sales for which we track sources. (Remove   WHERE (referrer_url IS NOT NULL AND referrer_url != '') OR (utm_source IS NOT NULL AND utm_source != '') to see total sales we get from Stripe regardless of whether the source is known.)