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:
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
);
Go to the code editor and paste index.js and package.json content from the gist below:
Deploy the function
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.
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;
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