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:

1. Keep a record of which data you deleted.
2. Add a filter to your Loomi BigQuery reporting that excludes that data.

Contact your CSM if you need help or further clarification.

Duplicate campaign status events

Email providers occasionally send the same delivery status webhook twice for a single email, usually less than a second apart. Both copies have the same status, timestamp, and properties. This is expected behavior, not a data error.

Bloomreach removes these duplicates from campaign reports in the app. Loomi BigQuery keeps every webhook it receives, so the campaign event table can contain duplicate rows. As a result, delivery counts in Loomi BigQuery can be higher than the counts in the app.

Duplicates can affect the delivered, soft_bounced, hard_bounced, and complained delivery statuses. They don't affect opened or clicked events.

If you need exact delivery counts, deduplicate campaign events on status, timestamp, and properties before you count them:

SELECT status, COUNT(*) AS event_count
FROM (
  SELECT DISTINCT
    internal_customer_id,
    timestamp,
    raw_properties.status AS status,
    TO_JSON_STRING(raw_properties) AS raw_properties_json
  FROM `gcloudltds.<DATASET_ID>.campaign`
  WHERE timestamp >= TIMESTAMP('<START_DATE>')
    AND raw_properties.status IN ('delivered', 'soft_bounced', 'hard_bounced', 'complained')
)
GROUP BY status

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:

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_id

For 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.