BigQuery

We use BigQuery (BQ) often to spot check & find more information about the performance of our notifications.

For the sake of example, all the queries on this page will be to the handshake-staging environment, but feel free to swap that out for hs-engineering-production or EU equivalents.

Notifications Data Service (NDS)

NDS is where we store data regarding all notifications that flow through notification services.

NDS actually uses Spanner, but the Spanner data gets backed up to BigQuery daily, so all NDS data can be found in BQ (with a maximum of 1 day delay) under the notifications_data_service dataset.

General notification data

The instructions below are for emails, but you can swap out the email_notifications table for push_notifications or in_app_notifications, and most everything stays the same (check out the BQ table schemas to compare/contrast with more granularity).

SELECT *
FROM `handshake-staging.notifications_data_service.notifications` AS n
JOIN `handshake-staging.notifications_data_service.email_notifications` AS e
ON n.notification_id = e.notification_id
WHERE n.created_at > '2025-11-06' -- utilizing partitions
  AND n.notification_name = {notification name} -- e.g. "profile_views"
  -- other common filters:
  -- AND n.notification_id = {id} -- if you're looking for a particular notification by id; common in error tracing
  -- AND n.user_id = {user id} -- if you're looking into a particular user
  -- AND e.failed_at is null

Data re: push notifications & tokens

Of the 3 channels, push notifications are distinct in that they don't just send to a user id or an email address; they require push notification tokens, which are constantly rotated by the mobile clients themselves.

Here's how I might interact with the push_notification_tokens table:

To find all active push tokens for a given user id:

SELECT *
FROM `handshake-staging.notifications_data_service.push_notification_tokens`
WHERE user_id = {user id}
AND deregistered_at IS NULL

To analyze push tokens & push notifications together:

SELECT *
FROM `handshake-staging.notifications_data_service.push_notifications` AS n
JOIN `handshake-staging.notifications_data_service.push_notification_tokens` AS t
ON n.push_notification_token_id = t.push_notification_token_id
WHERE n.created_at > '2025-11-06'
AND t.user_id = {user id}

Downstream metrics

The lifecycle_communication_messages table holds downstream metrics re: all notifications (think: clicks, opens, unsubscribes, attributable sessions). This includes notifications that do not send through the notification services (those that send through Iterable, for example).

Please note: opens and clicks can be mis-reported due to email spam filters which automatically open and click on all links. Use this as directional/informational only and verify using other metrics (such as sessions).

Utilize the boolean columns (clicked, opened, delivered, unsubscribed) to see various key metrics. For example, to check click through rates:

SELECT
  COUNT(*) as total_delivered,
  COUNTIF(clicked = true) as total_clicked,
  (CAST(COUNTIF(clicked = true) as float64) / CAST(COUNT(*) as float64)) as ctr
FROM `handshake-staging.handshake_derived.lifecycle_communication_messages`
WHERE TIMESTAMP_TRUNC(sent_at, DAY) >= TIMESTAMP("2025-11-01")
  AND tag = {notification name}
  AND delivered = true

As another example, here's a query that we ran to see how many notifications per user there were in a given time frame:

SELECT
  user_id,
  COUNT(*) AS sent_per_user
FROM `handshake-staging.handshake_derived.lifecycle_communication_messages`
WHERE TIMESTAMP_TRUNC(sent_at, DAY) >= TIMESTAMP("2025-11-09")
AND tag = 'favorited_employer_approve_event'
GROUP BY user_id
ORDER BY sent_per_user DESC

Experimentation

If you're running an experiment, and want to see experiment data, you can query the segment dataset:

SELECT * 
FROM `handshake-staging.segment.hs_experiment_events`
WHERE time_stamp > {recent date} -- e.g. '2025-11-06'
  AND event_name like '$%impression%'
  AND experiment_name = {experiment toggle} -- e.g. "experiment-notifications-new-followers-in-app-cta"