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 the relationship between Visitors and Funnels 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. Each Visitor corresponds to a Customer in Able Dashboard.

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. Normalizations are applied to identifiers to remove non-meaningful characters. For example, dots are removed from parts of email addresses before the at mark, and phone numbers are trimmed to keep last ten digits.
• 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
• deal_value_converted - total value of the event in the funnel currency
• 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;

Optimizing queries and controlling BigQuery costs

With on-demand pricing, Google bills BigQuery usage primarily by the amount of data processed by queries. Storage and the real-time inserts performed by Able normally account for a small part of the bill. If BigQuery charges are higher than expected, the cause is almost always in the queries that run against the dataset – what they read and how often they run. The Google Cloud billing report provides a breakdown that can be used to confirm this.

BigQuery is a columnar store

A common expectation, carried over from row-oriented databases such as MySQL or PostgreSQL, is that a query reads entire rows, so the cost of a query is proportional to the total size of the table. A typical question arising from this is: "The events table isn't partitioned, so a query for a single month still reads the whole table, including the event_source column, which is very large because it stores full page URLs. Can the table be partitioned and the column trimmed to cut the scan costs?"

This isn't how BigQuery operates. BigQuery stores each column separately, and a query only reads the columns it references. In practice, this means:

• The size of the columns that a query doesn't reference is irrelevant. A query that filters by created_at and sums deal_value reads these two columns only. Large text columns such as event_source, custom_data, lead or client_ua cost nothing unless they are selected or used in a condition, so removing them from the table wouldn't make other queries cheaper.
• An estimate that multiplies the total table size by the number of queries significantly overstates the cost of queries that use a few columns. Conversely, SELECT * is the most expensive query to run, as it reads every column.
• WHERE and LIMIT reduce the number of rows returned, but not the amount of data read. The tables created by Able aren't partitioned or clustered, so each referenced column is read in full regardless of the date range requested. This is usually inexpensive for narrow columns such as created_at, visitor_id, event_type or deal_value.

See Overview of BigQuery storage for details of the storage model, and Query plan and timeline and the adjacent pages of Google's documentation for a practical guide to query optimization.

Check what is running and how often

Before optimizing individual queries, check where the volume comes from. High usage is frequently caused not by an expensive query, but by an inexpensive one that runs far more often than intended: a dashboard set to refresh automatically, a scheduled query, or a script or integration that polls the dataset around the clock. Because Able updates the dataset in real time, BigQuery generally can't serve such queries from its results cache, so each refresh is billed as a new query.

Review the job history in BigQuery Console, or query it directly to find which queries account for the most data billed, replacing the region with the region of your dataset:

SELECT
  user_email,
  query,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_billed) / POW(1024, 3), 2) AS gib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND
  job_type = 'QUERY'
GROUP BY user_email, query
ORDER BY gib_billed DESC
LIMIT 20;

If a single query dominates the list with a large number of runs, reducing its refresh frequency or switching off the process that runs it typically resolves the issue without any changes to the queries or the data.

Reducing the amount of data processed

If there is a genuine query pattern that is worth optimizing:

• Select only the columns that the report needs and avoid SELECT *, particularly in the custom queries used as data sources in Looker Studio and other BI tools, which re-run the query each time a report is viewed or refreshed.
• Check the estimate of the data that the query will process, which BigQuery Console displays in the query editor before the query is run, prior to saving the query in a dashboard or a schedule.
• For reports that frequently query the same subset of data, consider querying a smaller derived table instead of the raw tables – for example, a table populated by a scheduled query that holds only the recent or pre-aggregated data. Derived tables can be partitioned by date and clustered by the columns that your queries filter and join on. Which approach is suitable depends on the queries that you run.
• Create derived tables and views in a separate dataset rather than altering the tables maintained by Able. Able manages the schema of its dataset and occasionally adds new columns, and the dataset is recreated when the connection is re-established as described above.
• As a safety net, set the maximum bytes billed for a query or custom daily query quotas for the project. See Estimate and control costs in Google's documentation.