Reconcile Loomi BigQuery data
Counts in Loomi BigQuery don't always match what you see in Marketing app. Deleted events, duplicate campaign status events, and customer merges are handled differently in Loomi BigQuery than in the app. Use the sections below to account for each difference in your queries and reports.
Deleted events
Event tables in Loomi BigQuery load incrementally and never delete data. When you delete events in the Marketing app, they stay in Loomi BigQuery, so your BigQuery data can differ from what you see in the app.
This is by design. Loomi BigQuery is long-term storage with a 10-year rolling window (9 years in the past, 1 year in the future), so it keeps data even after you delete it in the app.
To exclude invalid, bugged, or redundant events you deleted in the app:
- Keep a record of which data you deleted.
- Add a filter to your Loomi BigQuery reporting that excludes that data.
Contact your CSM if you need help or further clarification.
Table_system_delete_event_typeIn releases before version 1.160 (Nov 2019), the information about deleted data was stored in Loomi BigQuery in the table
_system_delete_event_type. You could use this table to filter only data that haven't been deleted from Marketing app. However, this table isn't updated anymore, after release of a feature Delete events by filter.
Duplicate campaign status events
Email providers can send the same delivery status notification for an email more than once, usually less than a second apart. The copies have the same customer, status, timestamp, and properties. Only ingest_timestamp differs. The share of duplicated events can vary by project and over time.
Marketing shows each of these events once. Loomi BigQuery isn’t deduplicated, so the campaign table can contain both copies. As a result, delivery counts in Loomi BigQuery can be higher than in Marketing, and delivered can be higher than enqueued, which gives a delivery rate above 100%.
Duplicates can affect statuses reported by the email provider: delivered, soft_bounced, hard_bounced, complained, and preblocked (delivery statuses). Statuses that Bloomreach tracks itself, such as enqueued, suppressed, and enqueue_failed, and the opened and clicked events aren’t affected.
To get exact counts, remove duplicates before you count. Because ingest_timestamp differs between the copies, don’t include it when you deduplicate, and don’t use SELECT DISTINCT *. Deduplicating on the customer, timestamp, status, campaign, and action is enough. It also reads much less data than comparing all properties with TO_JSON_STRING(raw_properties).
SELECT status, COUNT(*) AS event_count
FROM (
SELECT DISTINCT
internal_customer_id,
timestamp,
raw_properties.status AS status,
raw_properties.campaign_id AS campaign_id,
raw_properties.action_id AS action_id
FROM `gcloudltds.<DATASET_ID>.campaign`
WHERE timestamp >= TIMESTAMP('<START_DATE>')
AND raw_properties.action_type = 'email'
AND raw_properties.status IN ('enqueued', 'delivered', 'soft_bounced',
'hard_bounced', 'complained', 'preblocked')
)
GROUP BY status
ORDER BY event_count DESCThe query counts email campaign events. To include transactional emails, use raw_properties.action_type IN ('email', 'transactional_email'). To get the delivery rate, divide the delivered count by the enqueued count.
Merging customers
Every time a customer uses a different browser or device to visit your website, they're considered as separate and non-related entities. However, once the customer identifies (through registering or logging in their account for example), those 2 profiles are merged into one. In this way, customer activity can be tracked across multiple browsers and devices.
Until the customers are merged, there's no record of the first or the second customer in the customers_id_history table.
At the moment of the merge, the following information is stored in the table:
| Example value | |
|---|---|
internal_customer_id | customer_on_device_1 |
past_id | customer_on_device_2 |
This means that the customer that was tracked on device 2 (customer_on_device_2) is merged into the customer on device 1 (customer_on_device_1), and both customers together are now considered as a single merged customer.
It's important to work with merged customers when you analyze the event data to get all events generated by the customer_on_device_2 and customer_on_device_1 assigned to a single customer. Use the following query to work with merged customers:
SELECT Ifnull(b.internal_customer_id, a.internal_customer_id) AS merged_id,
a.internal_customer_id AS premerged_id
FROM `gcloudltds.exp_558cba14_8a46_11e6_8da1_141877340e97_views.session_start` a
LEFT JOIN `gcloudltds.exp_558cba14_8a46_11e6_8da1_141877340e97_views.customers_id_history` b
ON a.internal_customer_id = b.past_idFor each internal_customer_id in the event table (session_start in this case), if there's a merge available in the customers_id_history, internal_customer_id will be mapped to the merged customer. As a result, analysis can be now done on merged_id, all session_starts created historically by either customer_on_device_2 or customer_on_device_1 will have merged_id = customer_on_device_1.
Learn more about the customer tables.
Updated 20 days ago

