BigQuery

Overview

Connection Type:

Server-side

Installation Instructions

1. Create a BigQuery Table for your Converge Data

Log into your Google Cloud console at console.cloud.google.com
Choose your relevant Google Cloud Project from the top bar. If you do not have a Google Cloud Project yet, you can create one following the instructions here.
From the top search bar, search for bigquery and pick the BigQuery service.
In the BigQuery Explorer, click on Add.
From the side-pane, choose Google Cloud Storage, this will open the Create table modal.
In the Create table modal, choose Empty table as a Source. Create a new Data set and call it converge_data, in the Table-field choose a name for your table, e.g. events.

In the Schema section, flip the switch to Edit as text and paste the schema below. Click on Create Table after.

bq-5

JSON

[
  {
    "name": "id",
    "type": "STRING",
    "mode": "REQUIRED",
    "description": "Event ID"
  },
  {
    "name": "timestamp",
    "type": "DATETIME",
    "mode": "REQUIRED",
    "description": "Timestamp when the event occurred"
  },
  {
    "name": "event_name",
    "type": "STRING",
    "mode": "REQUIRED"
  },
  {
    "name": "profile_id",
    "type": "INTEGER",
    "mode": "REQUIRED",
    "description": "Profile ID"
  },
  {
    "name": "event_properties",
    "type": "JSON",
    "mode": "NULLABLE",
    "description": "Properties of the event"
  },
  {
    "name": "profile_properties",
    "type": "JSON",
    "mode": "NULLABLE",
    "description": "Properties of the profile this event belongs to"
  },
  {
    "name": "session_id",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Session Id of the session this event belongs to"
  },
  {
    "name": "session_properties",
    "type": "JSON",
    "mode": "NULLABLE",
    "description": "Properties of the session this event belongs to"
  },
  {
    "name": "first_touch_session_id",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Session Id of the first touch as per a first touch attribution model"
  },
  {
    "name": "first_touch_properties",
    "type": "JSON",
    "mode": "NULLABLE",
    "description": "Properties of the first touch as per a first touch attribution model"
  },
  {
    "name": "last_touch_session_id",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Session Id of the last touch as per a last touch attribution model"
  },
  {
    "name": "last_touch_properties",
    "type": "JSON",
    "mode": "NULLABLE",
    "description": "Properties of the last touch as per a last touch attribution model"
  },
  {
    "name": "first_touch_paid_session_id",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Session Id of the first paid touch as per a first paid touch attribution model"
  },
  {
    "name": "first_touch_paid_properties",
    "type": "JSON",
    "mode": "NULLABLE",
    "description": "Properties of the first paid touch as per a first paid touch attribution model"
  },
  {
    "name": "last_touch_paid_session_id",
    "type": "STRING",
    "mode": "NULLABLE",
    "description": "Session Id of the last paid touch as per a last paid touch attribution model"
  },
  {
    "name": "last_touch_paid_properties",
    "type": "JSON",
    "mode": "NULLABLE",
    "description": "Properties of the last paid touch as per a last paid touch attribution model"
  }
]

Great, now you have a table for your Converge Events, follow the guide below to set up a connection that streams Converge events into this BigQuery Table

2. Stream Converge Data into your BigQuery table

The first thing we need to do in Google Cloud is set up a service account. Start by going to the sidebar menu and choosing IAM and admin > Service accounts.
Click on Create Service Account.
Fill in service account name, e.g. converge-bigq-account and click on Create and continue.

bq-s-3

Under Grant this service account access to the project, choose the BigQuery Data Owner scope and click on Done.

bq-s-4

Pick your newly created service account from the list of Service Accounts and choose Manage keys.

bq-s-5

Click on Add Key and Create new key. In the popup choose JSON and click on Create. This will download a file that ends with .json.

bq-s-6

Open your Converge Dashboard at app.runconverge.com and navigate to the Connections Tab. Click on
Create New Connection
.

bq-s-7

Give your connection a name, choose BigQuery from the app list. Drop your .json key file in here and fill in the Dataset ID and Table ID you created as per above. Then click on “Create Connection”.

bq-s-8

Done 🎉.

Multi-touch attribution support

Converge preprocesses all events and enriches them with all available profile, session and attribution information to make your attribution data warehouse queries as simple as possible.

Profile Information

Every event that is forwarded includes all available information Converge has on that profile until that moment, ranging from $ip_address all the way to $email.

Events that typically do not have an $email property associated with them (e.g. $page_load) will still contain this property if we were able to identify this profile before the $page_load happened.

Profile Merging

Converge may combine multiple profiles into one if there is an overlap in aliases. This means that the profile_id field in your events table may become out of date. Under the ‘Advanced’ tab of the BigQuery connection configuration, you can configure a table to stream these profile merges to. You can create this table as follows:

Under your Converge dataset (converge_data), create a new table, e.g. profile_merges.

In the Schema section, flip the switch to Edit as text and paste the schema below. Click on Create Table after.

JSON

[
  {
    "name": "timestamp",
    "type": "DATETIME",
    "mode": "REQUIRED",
    "description": "Timestamp when the event occurred"
  },
  {
   "name": "old_profile_id",
   "type": "INTEGER",
   "mode": "REQUIRED",
   "description": "Old Profile ID that was merged"
  },
  {
    "name": "new_profile_id",
    "type": "INTEGER",
    "mode": "REQUIRED",
    "description": "New Profile ID resulting from the merge"
  }
]

To use this table, it’s important to note that profiles maybe merged multiple times. For example profile A and B could be mapped to C as follows:

(A, C)
(B, C) Later on, profile C could be merged into profile D:
(C, D) In this case, profiles A, B, and C, would all be mapped to D. To deal with this, we recommend creating a (materialized) view that recursively resolves these mappings. Here’s an example query to achieve this:

SQL

WITH RECURSIVE profile_merges AS (
  SELECT 
    timestamp, 
    old_profile_id, 
    new_profile_id, 
    ARRAY[old_profile_id] AS visited_profiles,
    1 AS depth,
    FALSE AS is_circular_reference
  FROM 
    `your-project-name.your-dataset-name.profile_merges`
  UNION ALL
  SELECT 
    pmr.timestamp, 
    pm.old_profile_id,
    pmr.new_profile_id, 
    ARRAY_CONCAT(pm.visited_profiles, [pmr.new_profile_id]) AS visited_profiles,
    pm.depth + 1 AS depth,
    IF(pmr.new_profile_id IN UNNEST(pm.visited_profiles), TRUE, pm.is_circular_reference) AS is_circular_reference
  FROM 
    profile_merges pm
  JOIN 
    `your-project-name.your-dataset-name.profile_merges` pmr
  ON 
    pm.new_profile_id = pmr.old_profile_id
  WHERE
    pmr.timestamp > pm.timestamp
    AND NOT pm.is_circular_reference
)
SELECT 
  old_profile_id, 
  MAX_BY(new_profile_id, depth) AS new_profile_id
FROM 
  profile_merges
GROUP BY 
  old_profile_id;

Pre-processed Attribution

Converge automatically attributes all events (e.g. Placed Order, Add To Cart, etc.) based on the most popular attribution models.

First Touch: Attributes full credit for conversion to the initial touchpoint in the customer journey.
First Touch Paid Only: Credits only to the first paid interaction for the conversion; exclusive to paid channels.
Last Touch: Assigns full credit for the conversion to the final touchpoint in the customer journey.
Last Touch Paid Only: Attributes conversion solely to the last paid interaction; focuses on the final touchpoint within paid channels.

Session Information for custom multitouch attribution models

Converge sends over all sessions with their attribution information (e.g. utm_source, utm_campaign, etc.) so you can assign custom credit to each of these touchpoints — allowing you to create your fully custom multitouch attribution model.

An example query

As an example, the following query on top of the Converge Table gives you the number of times an SKU was ordered:

SQL

SELECT
  string(item.sku) sku, count(*) order_count
FROM `your-project-name.your-dataset-name.your-table-name`
LEFT JOIN unnest(json_extract_array(event_properties.items,'$')) item
WHERE event_name = 'Placed Order'
GROUP BY sku
ORDER BY order_count DESC

Make sure you replace your-project-name, your-dataset-name and your-table-name with your appropriate values.

Converge functionality

This integration supports the following Converge connection functionality.

Converge Feature	Supported
Custom Events
Filters
Server-side Conversions

Event Mapping

Converge automatically sends all implemented events from the Converge Spec and any custom events to BigQuery.

Converge Event Name	BigQuery Name
$page_load	`$page-load`
Viewed Product	`Viewed Product`
Viewed Collection	`Viewed Collection`
Added To Cart	`Added To Cart`
Started Checkout	`Started Checkout`
Added Payment Info	`Added Payment Info`
Placed Order	`Placed Order`
Started Subscription	`Started Subscription`
Received Refund	`Received Refund`

Getting started

Sources

Connections

Attribution

Insights & Dashboards

Ad Platforms

Settings

Overview

Installation Instructions

Multi-touch attribution support

Profile Information

Profile Merging

Pre-processed Attribution

Session Information for custom multitouch attribution models

An example query

Converge functionality

Event Mapping

Getting started

Sources

Connections

Attribution

Insights & Dashboards

Ad Platforms

Settings

​Overview

​Installation Instructions

​Multi-touch attribution support

​Profile Information

​Profile Merging

​Pre-processed Attribution

​Session Information for custom multitouch attribution models

​An example query

​Converge functionality

​Event Mapping

Overview

Installation Instructions

Multi-touch attribution support

Profile Information

Profile Merging

Pre-processed Attribution

Session Information for custom multitouch attribution models

An example query

Converge functionality

Event Mapping