Skip to content
Products
/
/
/
Push Events Table
Last updated

Push Events Table

Beta Feature

Mobile Push is currently in beta. The schema and implementation details described in this document are provided as examples and may change. You should design your own table schema based on your specific tracking requirements and business needs.

Overview

When you implement Mobile Push notifications with Engage Studio, your mobile app tracks user interactions and sends event data to Treasure Data. This data is stored in a dedicated table (recommended name: push_events) within your Treasure Data database.

This document describes a sample schema, event types, and best practices for working with push notification event data. Note: The schema provided here is an example. You should customize it based on your application's requirements and data governance policies.

Table Schema

Sample Table Structure

Customize for Your Needs

The table structure shown below is a sample implementation. Design your own schema based on:

  • Your tracking requirements
  • Data governance and privacy policies
  • Analytics and reporting needs
  • Compliance requirements (GDPR, CCPA, etc.)
Column NameData TypeRequiredDescription
timeINTEGERYesUnix timestamp (seconds) when the event occurred
typeSTRINGYesEvent type: delivery, open, dismiss, link_open, deeplink_open, token_register
td_campaign_idSTRINGYesCampaign identifier from Engage Studio (passed from notification payload)
platformSTRINGYesMobile platform: android or ios
fcm_tokenSTRINGNoFirebase Cloud Messaging device token (required for token_register events)
user_idSTRINGNoUser identifier (e.g., customer ID, email hash) if user is logged in
valueSTRINGNoAdditional data (e.g., URL for link_open or deeplink_open)
device_modelSTRINGNoDevice model (optional enhancement)
os_versionSTRINGNoOperating system version (optional enhancement)
app_versionSTRINGNoApplication version (optional enhancement)

Sample Table Creation Query

-- Sample push_events table creation (customize for your needs)
CREATE TABLE IF NOT EXISTS mobile.push_events (
  time INTEGER,
  type STRING,
  td_campaign_id STRING,
  platform STRING,
  fcm_token STRING,
  user_id STRING,
  value STRING,
  device_model STRING,
  os_version STRING,
  app_version STRING
)

Event Types

1. Delivery Event

Logged when a push notification is successfully delivered and displayed to the user.

Event Type: delivery

Sample Event:

{
  "time": 1734134400,
  "type": "delivery",
  "td_campaign_id": "cmp_20251214_promo",
  "platform": "android",
  "user_id": "user_12345"
}

When Tracked:

  • Android: When onMessageReceived() is called in MyFirebaseMessagingService
  • iOS: When willPresent notification is called in UNUserNotificationCenterDelegate

2. Open Event

Logged when a user taps on the notification to open the app.

Event Type: open

Sample Event:

{
  "time": 1734134410,
  "type": "open",
  "td_campaign_id": "cmp_20251214_promo",
  "platform": "ios",
  "user_id": "user_12345"
}

When Tracked:

  • Android: When notification is tapped and MainActivity receives PushAction.OPEN intent
  • iOS: When didReceive response is called with UNNotificationDefaultActionIdentifier

3. Dismiss Event

Logged when a user dismisses (swipes away) the notification without opening it.

Event Type: dismiss

Sample Event:

{
  "time": 1734134420,
  "type": "dismiss",
  "td_campaign_id": "cmp_20251214_promo",
  "platform": "android",
  "user_id": "user_12345"
}

When Tracked:

  • Android: When deleteIntent is triggered in notification
  • iOS: When didReceive response is called with UNNotificationDismissActionIdentifier

Logged when a user taps on a web URL link from the notification.

Event Type: link_open

Sample Event:

{
  "time": 1734134430,
  "type": "link_open",
  "td_campaign_id": "cmp_20251214_promo",
  "platform": "android",
  "user_id": "user_12345",
  "value": "https://example.com/promo?utm_campaign=promo"
}

When Tracked:

  • Android: When action button with PushAction.ACTION_LINK is tapped
  • iOS: When action button with identifier ACTION_OPEN_LINK is tapped

Note: The value field contains the full URL that was opened.

Logged when a user taps on an app deeplink from the notification.

Event Type: deeplink_open

Sample Event:

{
  "time": 1734134440,
  "type": "deeplink_open",
  "td_campaign_id": "cmp_20251214_promo",
  "platform": "ios",
  "user_id": "user_12345",
  "value": "myapp://product/12345"
}

When Tracked:

  • Android: When action button with PushAction.ACTION_DEEPLINK is tapped
  • iOS: When action button with identifier ACTION_OPEN_DEEPLINK is tapped

Note: The value field contains the deeplink URI.

6. Token Register Event

Logged when the app registers or updates the FCM device token with Treasure Data.

Event Type: token_register

Sample Event:

{
  "time": 1734134400,
  "type": "token_register",
  "fcm_token": "dQw4w9WgXcQ:APA91bF...",
  "platform": "android",
  "user_id": "user_12345",
  "app_version": "1.2.3",
  "os_version": "14",
  "device_model": "Pixel 7"
}

When Tracked:

  • Android: When onNewToken() is called in MyFirebaseMessagingService
  • iOS: When didReceiveRegistrationToken is called in MessagingDelegate
  • Also triggered on app launch to ensure token is up-to-date

Note:

  • For logged-in users, user_id should be included
  • For anonymous users, user_id can be null — it will be associated later when the user logs in

User ID Association

Linking Tokens to Users

The token_register event is crucial for linking device tokens to user identities. Here's how the association works:

Scenario 1: User Already Logged In

When a user installs the app and is already logged in (or logs in immediately):

{
  "type": "token_register",
  "fcm_token": "ABC123...",
  "user_id": "user_12345",
  "platform": "android"
}

The token is immediately associated with the user.

Scenario 2: Anonymous User (Not Logged In)

When a user installs the app but doesn't log in yet:

{
  "type": "token_register",
  "fcm_token": "ABC123...",
  "user_id": null,
  "platform": "android"
}

The token is registered but not yet linked to a user.

Scenario 3: User Logs In Later

When the previously anonymous user logs in, send a new token_register event:

{
  "type": "token_register",
  "fcm_token": "ABC123...",
  "user_id": "user_12345",
  "platform": "android"
}

Treasure Data can now link all previous events with this fcm_token to user_12345.

Name Resolution Query

To resolve anonymous tokens to users, use this query:

WITH latest_tokens AS (
  SELECT
    fcm_token,
    user_id,
    time,
    ROW_NUMBER() OVER (PARTITION BY fcm_token ORDER BY time DESC) AS rn
  FROM mobile.push_events
  WHERE type = 'token_register'
    AND user_id IS NOT NULL
)
SELECT
  fcm_token,
  user_id
FROM latest_tokens
WHERE rn = 1

Analytics Queries

Campaign Performance Overview

Get delivery, open, and click rates for all campaigns:

SELECT
  td_campaign_id,
  COUNT(CASE WHEN type = 'delivery' THEN 1 END) AS deliveries,
  COUNT(CASE WHEN type = 'open' THEN 1 END) AS opens,
  COUNT(CASE WHEN type = 'link_open' OR type = 'deeplink_open' THEN 1 END) AS clicks,
  COUNT(CASE WHEN type = 'dismiss' THEN 1 END) AS dismissals,
  ROUND(100.0 * COUNT(CASE WHEN type = 'open' THEN 1 END) / NULLIF(COUNT(CASE WHEN type = 'delivery' THEN 1 END), 0), 2) AS open_rate_pct,
  ROUND(100.0 * COUNT(CASE WHEN type = 'link_open' OR type = 'deeplink_open' THEN 1 END) / NULLIF(COUNT(CASE WHEN type = 'delivery' THEN 1 END), 0), 2) AS click_rate_pct
FROM mobile.push_events
WHERE time >= CAST(strftime('%s', 'now', '-30 days') AS INTEGER)
GROUP BY td_campaign_id
ORDER BY deliveries DESC

Platform Comparison

Compare performance between Android and iOS:

SELECT
  platform,
  COUNT(CASE WHEN type = 'delivery' THEN 1 END) AS deliveries,
  COUNT(CASE WHEN type = 'open' THEN 1 END) AS opens,
  ROUND(100.0 * COUNT(CASE WHEN type = 'open' THEN 1 END) / NULLIF(COUNT(CASE WHEN type = 'delivery' THEN 1 END), 0), 2) AS open_rate_pct
FROM mobile.push_events
WHERE td_campaign_id = 'cmp_20251214_promo'
GROUP BY platform

Time-to-Open Analysis

Analyze how quickly users open notifications:

WITH delivery_times AS (
  SELECT
    td_campaign_id,
    user_id,
    fcm_token,
    time AS delivery_time
  FROM mobile.push_events
  WHERE type = 'delivery'
),
open_times AS (
  SELECT
    td_campaign_id,
    user_id,
    fcm_token,
    time AS open_time
  FROM mobile.push_events
  WHERE type = 'open'
)
SELECT
  CASE
    WHEN (o.open_time - d.delivery_time) < 60 THEN '< 1 minute'
    WHEN (o.open_time - d.delivery_time) < 300 THEN '1-5 minutes'
    WHEN (o.open_time - d.delivery_time) < 3600 THEN '5-60 minutes'
    WHEN (o.open_time - d.delivery_time) < 86400 THEN '1-24 hours'
    ELSE '> 24 hours'
  END AS time_bucket,
  COUNT(*) AS count
FROM delivery_times d
JOIN open_times o
  ON d.td_campaign_id = o.td_campaign_id
  AND d.user_id = o.user_id
  AND d.fcm_token = o.fcm_token
GROUP BY time_bucket
ORDER BY MIN(o.open_time - d.delivery_time)

Identify which links receive the most clicks:

SELECT
  td_campaign_id,
  value AS clicked_url,
  COUNT(*) AS click_count,
  COUNT(DISTINCT user_id) AS unique_users
FROM mobile.push_events
WHERE type IN ('link_open', 'deeplink_open')
  AND value IS NOT NULL
GROUP BY td_campaign_id, value
ORDER BY click_count DESC
LIMIT 20

Daily Active Tokens

Track daily active device tokens:

SELECT
  DATE(time, 'unixepoch') AS event_date,
  platform,
  COUNT(DISTINCT fcm_token) AS active_tokens
FROM mobile.push_events
WHERE time >= CAST(strftime('%s', 'now', '-30 days') AS INTEGER)
  AND type IN ('delivery', 'open', 'link_open', 'deeplink_open')
GROUP BY event_date, platform
ORDER BY event_date DESC, platform

User Engagement Funnel

Analyze the engagement funnel for a specific campaign:

WITH funnel_data AS (
  SELECT
    user_id,
    MAX(CASE WHEN type = 'delivery' THEN 1 ELSE 0 END) AS delivered,
    MAX(CASE WHEN type = 'open' THEN 1 ELSE 0 END) AS opened,
    MAX(CASE WHEN type IN ('link_open', 'deeplink_open') THEN 1 ELSE 0 END) AS clicked
  FROM mobile.push_events
  WHERE td_campaign_id = 'cmp_20251214_promo'
  GROUP BY user_id
)
SELECT
  SUM(delivered) AS total_delivered,
  SUM(opened) AS total_opened,
  SUM(clicked) AS total_clicked,
  ROUND(100.0 * SUM(opened) / NULLIF(SUM(delivered), 0), 2) AS open_rate_pct,
  ROUND(100.0 * SUM(clicked) / NULLIF(SUM(opened), 0), 2) AS click_through_rate_pct
FROM funnel_data

Data Retention and Archival

Partition Strategy

For large-scale deployments, consider partitioning the table by date:

-- Create monthly partitioned tables
CREATE TABLE mobile.push_events_202512 AS
SELECT * FROM mobile.push_events
WHERE time >= 1733011200 AND time < 1735689600

Archive Old Events

Archive events older than 90 days to a separate table:

CREATE TABLE mobile.push_events_archive AS
SELECT * FROM mobile.push_events
WHERE time < CAST(strftime('%s', 'now', '-90 days') AS INTEGER)

Then delete from the main table:

DELETE FROM mobile.push_events
WHERE time < CAST(strftime('%s', 'now', '-90 days') AS INTEGER)

Best Practices

1. Use Dedicated Table for Push Events

Recommended: Store push notification events in a separate push_events table.

Reason: Push events have a different schema from general app events (e.g., event_app), making it easier to query and analyze.

2. Include User ID When Available

Always include user_id in events when the user is logged in. This enables:

  • User-level analytics
  • Cross-device tracking
  • Personalized campaign optimization

3. Batch Event Uploads

Upload events in batches (up to 500 events per request) to:

  • Reduce network overhead
  • Improve app performance
  • Ensure reliable delivery even with poor network conditions

4. Implement Retry Logic

If event upload fails:

  • Keep events in local queue
  • Retry with exponential backoff
  • Limit maximum retry attempts (e.g., 3-5 times)

5. Monitor Event Quality

Regularly check for:

  • Missing required fields (td_campaign_id, platform, type)
  • Duplicate events (same td_campaign_id + user_id + fcm_token + type + time)
  • Abnormal event patterns (e.g., opens without deliveries)

6. Add Custom Fields as Needed

Enhance the schema with additional fields relevant to your business:

  • device_model: Track performance by device type
  • os_version: Identify OS-specific issues
  • app_version: Correlate events with app releases
  • location: Geo-based analytics (ensure privacy compliance)

Security and Privacy

Data Protection

  • Do not log PII: Avoid storing email addresses, phone numbers, or other personally identifiable information in events
  • Hash user IDs: Consider hashing user IDs before sending to Treasure Data
  • Use write-only keys: Mobile apps should use write-only API keys, never master keys
  • Encrypt in transit: All data uploads use HTTPS

GDPR/Privacy Compliance

If your app serves users in privacy-regulated regions:

  • Obtain user consent before tracking events
  • Provide opt-out mechanisms
  • Implement data deletion workflows
  • Document data retention policies
Previous page
Next page