How to send out Hull Events to BigQuery using Outgoing WebhooksUpdated 09/09/2020

This guide will show you how to get selected Hull Events from relevant Users into BigQuery table using BigQuery Streaming API.

Sending events like that allows to analyze events across users. All events data will be available in BigQuery including all propertie and context parameters. On the other hand, the only User information which will be sent is just internal Hull user_id. That allows to group events together but won't allow to read any user attributes in the queries.

Limitations

Before proceeding please be aware that this is very basic events setup and that your are good with following limitations:

• only events data will be sent, no user attributes will be available
• user merges are not supported. If an user is merged in Hull this won't be reflected in BigQuery

Create a new BigQuery table to store events

1. Create a new dataset if needed
2. Run this query to create table:

    CREATE TABLE hull_bigquery_exporter_demo.hull_events
    (
        event_id STRING NOT NULL,
        event_name STRING,
        created_at TIMESTAMP,
        properties STRING,
        event_source STRING,
        event_type STRING,
        session_id STRING,
        context STRING,
        user_id STRING,
    inserted_at INT64
    );

Create new Google Function

1. Go to https://console.cloud.google.com/functions/list
2. Give it a recognizable name - it will be part of the Function HTTP address
3. Define http trigger with unauthorized access (basic authorization will be done in function code itself)
4. Add environment variables in the advanced section:
   • SECRET - create a unique string to authorize calls
   • DATASET_ID - the dataset name create in the step before
   • TABLE_ID - the dataset name create in the step before

5. Go to the code editor and paste index.js and package.json content from the gist below:

   • https://gist.github.com/michaloo/6d7b60cbcc9e58cc026719d4f435fedb Embedded content: https://gist.github.com/michaloo/6d7b60cbcc9e58cc026719d4f435fedb

6. Deploy the function

Install Outgoing User Webhooks connector

1. Go to connector gallery
2. Pick and install Outgoing User Webhooks connector
3. Go to connector settings:
   • paste the Google Function url to the request field
   • add "x-secret" header with secret key defined in Google Function environmental variable
   • whitelist users which events should go out (you can use All Users here)
   • whitelist all events which should be send out to BigQuery
4. Save the settings

It's ready

The setup is done. Every new event matching the whitelists in Outgoing Webhooks Connector will be sent out and inserted into hull_events table in BigQuery using Streaming API.

Event Deduplication

If you have a setup where you could receive the same event multiple times, you may want to avoid seeing that event multiple times. With the following query pattern you can create a view that only shows the "latest" event given a event_id (which we deduplicate on)

CREATE OR REPLACE VIEW hull-developer.hull_bigquery_exporter_demo.hull_events_deduplicated
AS 
WITH
  cte AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY inserted_ad DESC) AS rownum,
    *
  FROM
    `hull-developer.hull_bigquery_exporter_demo.hull_events`)
SELECT
  *
FROM
  cte
WHERE
    rownum=1;

FAQ

How to query event properties?

Event properties and context parameters are stored in BigQuery table as stringified JSON here is a basic query showing how properties from such string column can be extracted:

SELECT
  JSON_EXTRACT(properties, '$.foo') AS json_text_string
  FROM `project-name.hull_bigquery_exporter_demo.hull_events`
  LIMIT 1000