Event Triggers

Event triggers are retrieved periodically from the event trigger database using a SQL query. The records that are retrieved via this query will then trigger messages if a flow has been set up to use that trigger.

Create an event trigger query

  1. From the Simon left navigation, expand datasets then click datasets.
  2. Click Create Dataset.
  3. Select Event Triggered then click Next.
  4. Name your dataset based on the name you would like for your trigger.
  5. Choose Database Query as the source.
  6. Choose Simon Signal Triggered Events as the database
  7. Use email as identifier.
724724

Dataset settings for an event trigger query.

  1. The query editor loads (see below). Once you've created your query, save the dataset. This automatically creates an event trigger for that dataset using the dataset name. That trigger will be available when you create a new event-triggered flow.

View sample events

Once the query editor loads, it will have a sample query with the required fields and basic structure of the query, including the cursors. For example:

SELECT identity,
  identity_type,
  ts,
  event_type,
  event_id,
  event_raw
FROM kateandweston.event_triggers
WHERE event_type='replace_me'
AND ts > {cursor_start_time}
AND ts <= {cursor_end_time};

By clicking Validate you can execute the query to retrieve a sample of records. For more information about working with Datasets in general, see: https://docs.simondata.com/docs/creating-and-updating-datasets

To simply retrieve all instances of a particular event within the cursor window, just replace the ‘replace_me’ with the event type.

📘

Querying for custom events

Custom events will be prepended with ‘custom_’. I.e. if I have a custom event named ‘booking_completed’ I will query for ‘custom_booking_completed’.

Event Trigger Query Structure

In order for the records to trigger a message, the query needs to account for certain criteria:

  • Fields: the query returns a specific set of fields in a pre-defined order. See more in query fields.
  • Cursors: the query uses cursors, which are updated each time the query runs in order to always retrieve the new set of records. See more below.
  • Logic: the query logic retrieves only the events that meet the desired criteria

Query fields

The sample query contains the required fields for the query:

  • identity: (in most cases this will be email)
  • ts: event timestamp (retrieved directly from the event)
  • event_type: event type (retrieved directly from the event)
  • event_id: unique event identifier used for deduplication (retrieved directly from the event)
  • event_raw: the event payload that will be used if any of the event payload is used as content in the message.

In most cases these values will be retrieved directly from the events database. In rare instances, the contents can be manipulated in the trigger query. See more information about Logic, below.

Cursors

The cursors are required and are provided in the sample query. There's no need to modify these.

AND ts > {cursor_start_time}
AND ts <= {cursor_end_time};
  • The cursors provide the time boundaries for the events that will be retrieved each time the query runs: it selects events where the ts (timestamp) is after the last triggered send and before or at the time of the next triggered send.
  • Cursors are updated each time the query runs, ensuring we only select new events.
  • You can use the cursors in other parts of the query, e.g if you want to do a look-back for against another event category in relation to the cursor time.

Logic

The trigger query provides the opportunity to introduce different kinds of logic, such as filtering events, searching for combinations of events or taking action on the event payload.

Filtering based on other events

A common use case in the event trigger query is to look for a combination of events within a certain time period, most frequently the absence of a cancelling event. One example is sending a message to every contact who started the checkout process (has the custom_checkout event), except those that have recently completed a booking (have a custom_booking event in the prior 24 hours). This query provides an example of this kind of logic, using a triggering event (custom_checkout), a cancelling event (custom_booking) and a look-back window based on the cursor.

SELECT e.identity as email, e.ts, e.event_type, e.event_id, e.event_raw
FROM simondata.event_triggers e
LEFT JOIN
(
SELECT identity, ts
FROM simondata.event_triggers other
WHERE other.event_type  = 'custom_booking'
AND other.ts > {cursor_start_time} - 86400
) past_cancelling_events
ON e.identity = past_cancelling_events.identity 
WHERE past_cancelling_events.identity is null
AND e.event_type='custom_checkout'
AND e.ts > {cursor_start_time}
AND e.ts <= {cursor_end_time};

Filtering based on the event payload

The event_raw column contains the raw payload from the event in JSON format. Fields in the payload can be accessed using PostgresSQL JSON syntax.

Example: filtering based on an event payload value:

SELECT identity as email, ts, event_type, event_id, event_raw
FROM sample.event_triggers
WHERE event_type = ‘registration'
AND event_raw->'properties'->'properties'->>'source' = 'new_lead'
AND ts >= {cursor_start_time}
AND ts <= {cursor_end_time}

Did this page help you?