Loomi BigQuery data schema

Loomi BigQuery is a set of tables in the Google BigQuery (GBQ) dataset that is kept up to date using regular loads. A BigQuery project always contains 2 types of tables: event tables and customer tables. Both store your tracked data in a properties field, with types mapped from Marketing.

Event tables

• One table per event type (for example, session_start, item_view).
• Loaded incrementally: new rows added with each load.
• Contains tracked data in the same structure as Marketing.

Properties field: All tracked data stored as a properties record accessible in SQL:

SELECT properties.utm_campaign FROM 
`gcloudltds.exp_558cba14_8a46_11e6_8da1_141877340e97_views.session_start`

The first 3 columns in each event table are:

To query those fields, no prefix needs to be used:

SELECT internal_customer_id FROM
`gcloudltds.exp_558cba14_8a46_11e6_8da1_141877340e97_views.session_start`

Customer tables

• Loaded daily using full load: tables rebuilt from scratch with latest data.
• Three main tables: customers_properties, customers_id_history, customers_external_ids.

customers_properties: Main customer table with the same structure as Marketing.

All tracked data are stored as a “properties” record and can be accessed in SQL in the following way:

SELECT properties.last_name FROM
`gcloudltds.exp_558cba14_8a46_11e6_8da1_141877340e97_views.customers_properties`

customers_id_history: Tracks customer merges. Created only when customer merges exist in your project. Learn how to work with merged customers.

• past_id: ID of merged customer.
• internal_customer_id: ID of customer that absorbed the merge.

Until customers are merged, there's no record in this table.

customers_external_ids: Maps internal customer IDs to external IDs.

• id_value: External customer ID value.
• id_name: Type of external ID (for example, cookie, email).

Properties schema

Both event and customer tables contain a properties field (BigQuery nested record). Field types map from Marketing types:

date and datetime properties will be converted correctly only if their value is Unix timestamp in seconds.

list type is stored in BigQuery in a JSON serialized form.

In addition to properties field, customers, and event tables also contain raw_properties field. Properties in raw_properties field aren't converted according to Marketing schema, all of them are BigQuery STRING type. This is useful for cases when conversion in properties doesn't return expected results (the returned value is null).