Examples of queries query-examples
This section lists several commonly used examples to query Journey Step Events in Data Lake.
Make sure that the fields used in your queries have associated values in the corresponding schema.
What's the difference between id, instanceid and profileid
- id: unique for all the step event entries. Two different step events cannot have the same id.
- instanceId: instanceID is the same for all the step events associated to a profile within a journey execution. If a profile reenters the journey, a different instanceId will be used. This new instanceId will be same for all the step events of the reentered instance (from start to end).
- profileID: the profile’s identity corresponding to the journey namespace.
| note note |
|---|
| NOTE |
| For troubleshooting purposes, we recommend using journeyVersionID instead of journeyVersionName when querying journeys. Learn more about journey properties attributes in this section. |
Basic use cases/common queries common-queries
How many profiles entered a journey in a certain time frame
This query gives the number of distinct profiles that entered the given journey in the given time frame.
Data Lake query
| code language-sql |
|---|
|
Learn how to troubleshoot discarded event types in journey_step_events.
Which rule caused a profile to not enter into a given journey
This query returns the rejected ruleset and rule information when a profile is prevented from entering a journey due to capping or eligibility rules.
Example
| code language-sql |
|---|
|
How many errors occurred on each node of a specific journey for a certain amount of time
This query counts the distinct profiles that experienced errors at each node of a journey, grouped by node name. It includes all types of action execution errors and fetch errors.
Data Lake query
| code language-sql |
|---|
|
How many events were discarded from a specific journey in a certain time frame
This query counts the total number of events that were discarded from a journey. It filters for various discard event codes including segment export job errors, dispatcher discards, and state machine discards.
Data Lake query
| code language-sql |
|---|
|
What happens to a specific profile in a specific journey in a specific time frame
This query returns all the step events and service events for the given profile and journey for the specified time in chronological order.
Data Lake query
| code language-sql |
|---|
|
How much time elapsed between two nodes
These queries can be used, for example, to estimate the time spent in a wait activity. This allows you to make sure that the wait activity is correctly configured.
Data Lake query
| code language-sql |
|---|
|
Data Lake query
| code language-sql |
|---|
|
How to check the details of a serviceEvent
The Journey Step Events dataset contains all the stepEvents and serviceEvents. stepEvents are used in reporting, as they relate to activities (event, actions, etc.) of profiles in a journey. serviceEvents are stored in the same dataset, and they indicate additional information for debugging purposes, for example the reason for an experience event discard.
Here is an example of query to check the detail of a serviceEvent:
Data Lake query
| code language-sql |
|---|
|
Message/Action Errors message-action-errors
| accordion | ||||
|---|---|---|---|---|
| List of each error encountered in journeys | ||||
|
This query allows you to list each error encountered in journeys while executing a message/action. Data Lake query
Example
This query returns all the different errors that occurred while executing an action in a journey along with the count of how many times it occurred. |
Profile-based queries profile-based-queries
Find if a profile entered a specific Journey
This query checks whether a specific profile entered a journey by counting the events associated with that profile and journey combination.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The result should be greater than 0. This query returns the exact number of times a profile has entered a journey.
Find if a profile was sent a specific message
Method 1: if the name of your message is not unique in the journey (it is used at multiple places).
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The result should be greater than 0. This query only tells us whether the message action was successfully executed on the journey side.
Method 2: if the name of your message is unique in the journey.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns the list of all messages along with their count invoked for the selected profile.
Find all the messages a profile has received in the last 30 days
This query retrieves all successfully executed message actions for a specific profile within the last 30 days, grouped by message name.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns the list of all messages along with their count invoked for the selected profile.
Find all the journeys a profile has entered in the last 30 days
This query returns all the journeys that a specific profile has entered within the last 30 days, along with the entry count for each journey.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns the list of all journey names along with the number of times the queried profile entered the journey.
Number of profiles that qualified for a journey daily
This query provides a daily breakdown of the number of distinct profiles that entered a journey over a specified time period.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns, for the defined period, the number of profiles that entered the journey each day. If a profile entered via multiple identities, it will be counted twice. If reentrance is enabled, profile count might be duplicated across different days if it reentered the journey on different day.
Learn how to troubleshoot discarded event types in journey_step_events.
Queries related to the Read Audience read-segment-queries
Time taken to finish an audience export job
This query calculates the duration of an audience export job by finding the time difference between when the job was queued and when it finished.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns the time difference, in minutes, between when time the audience export job was queued and when it finally ended.
Number of profiles that got discarded by the journey because they were duplicates
This query counts the number of distinct profiles that were discarded due to instance duplication errors during the Read Audience activity.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns all the profile Ids that were discarded by the journey because they were duplicates.
Number of profiles that got discarded by the journey because of invalid namespace
This query returns the count of profiles that were discarded because they had an invalid namespace or missing identity for the required namespace.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns all the profile Ids that were discarded by the journey because they had an invalid namespace or no identity for that namespace.
Number of profiles that got discarded by the journey because of no identity map
This query counts the profiles that were discarded because they were missing an identity map required for journey execution.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns all the profile Ids that were discarded by the journey because the identity map was missing.
Number of profiles that got discarded by the journey because the journey was in test node and the profile was not a test profile
This query identifies profiles that were discarded when the journey was running in test mode but the profile did not have the testProfile attribute set to true.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns all the profile Ids that were discarded by the journey because the export job was run in test mode but the profile did not have the testProfile attribute set to true.
Number of profiles that got discarded by the journey because of an internal error
This query returns the count of profiles that were discarded due to internal system errors during journey execution.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns all the profile Ids that were discarded by the journey due to some internal error.
Overview of the Read Audience for a given journey version
This query provides a comprehensive overview of the Read Audience activity, including segment export job details, event codes, statuses, and profile counts for all stages of the audience export process.
Data Lake query
| code language-sql |
|---|
|
It will return all service events related to the given journey version. We can follow the chain of operations:
- topic creation
- export job creation
- export job termination (with metrics on exported profiles)
- worker processing termination
We can also detect issues such as:
- errors in topic or export job creation (including timeouts on audience export API calls)
- export jobs which can be stuck (case when for a given journey version, we do not have any event regarding the export job termination)
- worker issues, if we have received export job termination event but no worker processing termination one
IMPORTANT: if there is no event returned by this query, it may be due to one of the following reasons:
- the journey version has not reached the schedule
- if the journey version is supposed to have trigger the export job by calling the orchestrator, something went wrong on the upstream flow: issue on journey deployment, business event or issue with scheduler.
Get Read Audience errors for a given journey version
This query filters for specific error event codes related to Read Audience failures, such as topic creation errors, API call errors, timeouts, and failed export jobs.
Data Lake query
| code language-sql |
|---|
|
Get export job processing status
This query retrieves the processing status of audience export jobs, showing whether they succeeded or failed along with profile export metrics.
Data Lake query
| code language-sql |
|---|
|
If no record is returned, that means that either:
- an error has occurred during topic or export job creation
- the export job is still running
Get metrics on exported profiles, including discards and export job metrics for each export jobs
This query combines discarded profile counts with export job metrics to provide a complete view of audience export performance for each individual export job.
Data Lake query
| code language-sql |
|---|
|
Get aggregated metrics (audience export jobs and discards) on all export jobs
This query aggregates overall metrics across all export jobs for a given journey version, useful for recurring journeys or business event-triggered journeys with topic reuse.
Data Lake query
| code language-sql |
|---|
|
This query is different than the previous one.
It returns the overall metrics for a given journey version, regardless the jobs which can have run for it (in case of recurring journeys, business events triggered ones leveraging topic reuse).
Queries related to Audience Qualification segment-qualification-queries
Profile discarded because of a different audience realization than the one configured
This query identifies profiles that were discarded because their audience realization status did not match the journey’s Audience Qualification configuration (e.g., configured for “enters” but profile “exited”).
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
This query returns all the profile Ids that were discarded by the journey version due to wrong audience realization.
Audience Qualification events discarded by any other reason for a specific profile
This query retrieves all audience qualification or external events that were discarded for a specific profile due to internal service errors.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
This query returns all events (external events / audience qualification events) that were discarded because of any other reason for a profile.
Event-based queries event-based-queries
Check if a business event was received for a journey
This query counts the number of times a business event was received by a journey, grouped by date, within a specified time frame.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Check if an external event of a profile got discarded because no related journey was found
This query identifies when an external event for a specific profile was discarded because there was no active or matching journey configured to receive that event.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Learn how to troubleshoot discarded event types in journey_step_events.
Check if an external event of a profile got discarded because of any other reason
This query retrieves external events that were discarded for a specific profile due to internal service errors, along with the event ID and error code.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Learn how to troubleshoot discarded event types in journey_step_events.
Check the count of all the events discarded by stateMachine by errorCode
This query aggregates all events discarded by the journey state machine, grouped by error code to help identify the most common reasons for discards.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Learn how to troubleshoot discarded event types in journey_step_events.
Check all discarded events because reentrance was not allowed
This query identifies all events that were discarded because a profile attempted to reenter a journey when reentrance was not permitted in the journey configuration.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Learn how to troubleshoot discarded event types in journey_step_events.
Common journey-based queries journey-based-queries
Number of daily active journeys
This query returns a daily count of unique journey versions that had activity, helping you understand journey execution patterns over time.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
The query returns, for the defined period, the count of unique journeys that triggered each day. A single journey triggering on multiple days will be counted once per day.
Queries on journey instances journey-instances-queries
Number of profiles in a specific state a specific time
This query uses Common Table Expressions (CTEs) to identify profiles that are currently waiting at a specific node in a journey by finding profiles that passed through the node but have not yet proceeded to the next nodes.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
How many profiles exited the journey in the specific period of time
This query counts the journey instances that exited during a specified time period, including exits due to completion, errors, timeouts, or capping errors.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
How many profiles exited the journey in the specific period of time with node/status
This query provides a detailed breakdown of journey exits, showing the node name and exit status for each exited instance to help identify where and why profiles left the journey.
Data Lake query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Queries related to Custom Action performance metrics query-custom-action
Total number of successful calls, errors and requests per second of each endpoint over a specific time period
This query provides performance metrics for custom HTTP actions, including total calls, successful calls, error counts by type (4xx, 5xx, timeouts, capped), and throughput in requests per second for each endpoint.
Data Lake Query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Time series of successful calls, errors and throughput of each endpoint over a specific time period
This query provides the same performance metrics as the previous query but organized as a time series, showing how endpoint performance varies over time with minute-by-minute granularity.
Data Lake Query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Response latency of each endpoint at 50th, 95th, 99th and 99.9th percentile over a specific time period
This query calculates response time percentiles for custom action endpoints, helping you understand latency distribution and identify performance outliers at different percentile thresholds.
Data Lake Query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Time series of response latency percentiles of each endpoint over a specific time period
This query provides latency percentiles organized as a time series, allowing you to track how endpoint response times change over time at different percentile levels.
Data Lake Query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Waiting time in queue on throttled endpoints at 50th and 95th percentile over a specific time period
This query analyzes queue waiting times for throttled endpoints, showing the 50th and 95th percentile wait times to help you understand the impact of throttling on your custom actions.
Data Lake Query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Time series of queue waiting time percentiles for each throttled endpoint
This query provides queue waiting time percentiles as a time series, allowing you to monitor how throttling impacts wait times over time for each endpoint.
Data Lake Query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
Number of errors by type and code for a specific endpoint over a specific time period
This query provides a detailed breakdown of errors for a specific endpoint, grouped by error type and error code, including information about retry attempts.
Data Lake Query
| code language-sql |
|---|
|
Example
| code language-sql |
|---|
|
recommendation-more-help
b22c9c5d-9208-48f4-b874-1cefb8df4d76