# 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 Name | Data Type | Required | Description |
|  --- | --- | --- | --- |
| `time` | INTEGER | Yes | Unix timestamp (seconds) when the event occurred |
| `type` | STRING | Yes | Event type: `delivery`, `open`, `dismiss`, `link_open`, `deeplink_open`, `token_register` |
| `td_campaign_id` | STRING | Yes | Campaign identifier from Engage Studio (passed from notification payload) |
| `platform` | STRING | Yes | Mobile platform: `android` or `ios` |
| `fcm_token` | STRING | No | Firebase Cloud Messaging device token (required for `token_register` events) |
| `user_id` | STRING | No | User identifier (e.g., customer ID, email hash) if user is logged in |
| `value` | STRING | No | Additional data (e.g., URL for `link_open` or `deeplink_open`) |
| `device_model` | STRING | No | Device model (optional enhancement) |
| `os_version` | STRING | No | Operating system version (optional enhancement) |
| `app_version` | STRING | No | Application version (optional enhancement) |


### Sample Table Creation Query


```sql
-- 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:**


```json
{
  "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:**


```json
{
  "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:**


```json
{
  "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`


### 4. Link Open Event

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

**Event Type:** `link_open`

**Sample Event:**


```json
{
  "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.

### 5. Deeplink Open Event

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

**Event Type:** `deeplink_open`

**Sample Event:**


```json
{
  "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:**


```json
{
  "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):


```json
{
  "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:


```json
{
  "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:


```json
{
  "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:


```sql
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:


```sql
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:


```sql
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:


```sql
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)
```

### Most Clicked Links

Identify which links receive the most clicks:


```sql
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:


```sql
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:


```sql
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:


```sql
-- 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:


```sql
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:


```sql
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


## Related Documentation

- [Mobile Push Setup](/products/marketing-cloud/engage-studio/channels/mobile-push/mobile-push-setup) - Configure Firebase and Treasure Data integration
- [Android Developer Guide](/products/marketing-cloud/engage-studio/channels/mobile-push/developer-guide-android) - Implement push notifications on Android
- [iOS Developer Guide](/products/marketing-cloud/engage-studio/channels/mobile-push/developer-guide-ios) - Implement push notifications on iOS
- [Campaign Creation](/products/marketing-cloud/engage-studio/channels/mobile-push) - Create and send Mobile Push campaigns