MotherDuck

Sync custom events from MotherDuck to OneSignal to trigger automated Journeys and personalized messaging campaigns based on user behavior.

Overview

The OneSignal + MotherDuck integration enables automatic syncing of custom events from your MotherDuck databases to OneSignal to trigger automated messaging campaigns and Journeys based on user behavior.

MotherDuck is a DuckDB-in-the-cloud service that provides fast OLAP (Online Analytical Processing) capabilities with the simplicity of SQL.

---

Requirements

• Access to Custom Events (currently in beta)
• Updated Account Plan (not available on free apps).

MotherDuck

• MotherDuck account with database access
• Service token for authentication
• Database containing event data
• Tables or views with structured event information

---

Setup

1. Create MotherDuck service token

   Generate an access token for OneSignal to connect to MotherDuck:

   1. Log in to the MotherDuck Web UI at app.motherduck.com
   2. Click your profile in the top-left corner
   3. Navigate to Settings > General > Access Tokens
   4. Click Create Token
   5. Set expiration date (or leave unlimited)
   6. Copy the generated service token
2. Prepare your event data

   Ensure your MotherDuck database contains properly structured event tables:

   -- Example event table structure
   CREATE TABLE user_events (
       event_name VARCHAR,
       user_id VARCHAR,
       event_timestamp TIMESTAMP,
       event_properties JSON,
       session_id VARCHAR
   );
   

3. Connect to OneSignal

   In OneSignal, go to Data > Integrations and click Add Integration.

   Select MotherDuck and provide:

   • Service Token: Token from Step 1
   • Database Name: Your MotherDuck database name
   • Connection String: md:your_database_name
4. Configure data sync

   Select the tables or write custom SQL queries to define which event data to sync:

   SELECT
       event_name,
       user_id,
       event_timestamp,
       event_properties
   FROM user_events
   WHERE event_timestamp >= CURRENT_DATE - INTERVAL 7 DAYS
   

---

Event data mapping

Map your to OneSignal’s custom events format:

OneSignal Field	Description	Required
name	event_name	Event identifier	Yes
external_id	user_id	User identifier	Yes
timestamp	event_timestamp	When event occurred	No
properties	event_data	No

Example Event Query

-- Optimized event query for OneSignal sync
SELECT
    event_name,
    user_id,
    event_timestamp,
    {
        'source': 'motherduck',
        'session_id': session_id,
        'device_type': device_type,
        'value': event_value
    }::JSON as event_properties
FROM analytics.user_events
WHERE event_timestamp >= CURRENT_TIMESTAMP - INTERVAL 1 DAY
ORDER BY event_timestamp DESC

---

Processing Modes

Table Mode

Sync entire tables directly from your MotherDuck database. OneSignal will automatically map columns to event fields.

SQL Query Mode

Write custom DuckDB SQL queries to transform and filter your event data:

-- Advanced event aggregation
SELECT
    'daily_summary' as event_name,
    user_id,
    DATE_TRUNC('day', event_timestamp) as event_timestamp,
    {
        'total_events': COUNT(*),
        'unique_sessions': COUNT(DISTINCT session_id),
        'last_activity': MAX(event_timestamp)
    }::JSON as event_properties
FROM user_events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL 7 DAYS
GROUP BY user_id, DATE_TRUNC('day', event_timestamp)

---

Limitations

• Query complexity affects sync performance
• Large result sets may impact sync speed
• JSON parsing requires proper column typing

---

FAQ

How do I optimize query performance in MotherDuck?

Use DuckDB’s columnar storage advantages by selecting only needed columns and applying filters early in your queries.

Can I sync from multiple MotherDuck databases?

Yes, you can create separate integrations for each MotherDuck database in your account.

---