BigQuery Connector

For a simpler way to create reports in Looker 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 - JSON field with all lead parameters present in the Event
• 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

Able CDP occasionally updates the BigQuery schema. If one of the fields listed above is missing in your BigQuery dataset, try the following:

1. Open Able Dashboard, select "BigQuery", press "Delete" ("X") button in the list of Established Connections and delete the connection. This will stop the real-time updates to Google BigQuery.
2. Open BigQuery Console. Press three dots next to the dataset, connection to which you've disabled, and select "Delete".
3. Press "Connect" button in the "BigQuery" tab of Able Dashboard to re-establish connection. Able CDP will recreate the dataset with the latest schema version and will populate it with historical data. A full sync normally takes under one hour, after which entire Able CDP dataset becomes available in BigQuery with the latest schema.

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.)

List customers with recent purchases (similar to Customers-Purchases in Able Dashboard)

List customers who had recent event of the selected type and their attribution sources, ordering them by the time of last purchase.

SELECT
  e.visitor_id, e.order_id,
  ROW_NUMBER() OVER (PARTITION BY e.visitor_id ORDER BY e.id DESC)
    AS rev_event_count,
  e.deal_value,
  e.deal_currency,
  DATE(e.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 */
QUALIFY
/* Return only one row per customer that
   corresponds to the last event matching query condition */
  rev_event_count=1
ORDER BY date DESC;

List recent purchases attributed to the customer source

Dates are per first-touch attribution; replace first_touch.created_at with e.created_at to display purchase date instead of acquisition date.

SELECT
  e.visitor_id, e.order_id,
  e.deal_value,
  e.deal_currency,
  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 */
ORDER BY date DESC;

Get total customer LTV to date with attribution

Ordered by acquisition date

SELECT
  e.visitor_id,
  SUM(e.deal_value),
  e.deal_currency,
  MIN(DATE(first_touch.created_at)) AS date,
  ANY_VALUE(first_touch.utm_source) AS utm_source,
  ANY_VALUE(first_touch.utm_medium) AS utm_medium,
  ANY_VALUE(first_touch.utm_campaign) AS utm_campaign,
  ANY_VALUE(first_touch.referrer_url) AS 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 */
GROUP BY e.visitor_id, e.deal_currency
ORDER BY date DESC;

Export recent purchases with click ids

Replace gclid to another type of click id such as msclkid or fbp as desired.

SELECT
  e.visitor_id, e.order_id,
  e.deal_value,
  e.deal_currency,
  e.created_at AS event_date,
  first_touch.created_at AS click_date,
  first_touch.value AS click_id
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%%`.visitor_keys AS e2
  WHERE
      key_type = 'gclid'
) 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 click id */
ORDER BY event_date DESC;