BigQuery

Get your data into Google BigQuery.

Getting Converge data into BigQuery
1. Creating a BigQuery Table
2. Streaming Converge Data into BigQuery
An example query

1. Getting Converge data into BigQuery

a) Creating a BigQuery Table for Converge

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

[
  {
    "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

b) Streaming Converge Data into your BigQuery table

First thing we need to do in Google Cloud is setting 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.
Under Grant this service account access to the project, choose the BigQuery Data Owner scope and click on Done.
Pick your newly created service account from the list of Service Accounts and choose Manage keys.
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.
Open your Converge Dashboard at app.runconverge.com and navigate to the Connections Tab. Click on Create New Connection.
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”.
Done 🎉.

2. An example query

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

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 approppriate values.

BigQuery

Table of contents

1. Getting Converge data into BigQuery

a) Creating a BigQuery Table for Converge

b) Streaming Converge Data into your BigQuery table

2. An example query