App Data Feeds: Metric Calculations

Definitions and SQL for the flow, network request, custom and session metrics in the Conviva Connect App Flow Data Feed, plus common measures from the App Event Data Feed.

Use these definitions and queries to compute App metrics from the App Flow Data Feed and the App Event Data Feed. Metric names match the names in Pulse.

Every query reads the CSV form, where the JSON columns arrive as strings. In Parquet and Avro, replace each JSON_EXTRACT_SCALAR call with a map lookup such as MetricsInt['success'] and drop the CAST.

Three rules apply to every query on this page. Filter to the row kind the metric is defined on. Exclude the -1 and -2 duration sentinels from every sum, average and ratio. Keep a decimal literal such as 100.0 or 1.0 in every ratio, because Trino, Presto, Hive and Spark truncate integer division. See The JSON columns for the row kinds and sentinels.

Flow metrics

Flow metrics are defined on standard flows, the rows that carry both success and total_duration_ms. Conviva writes each flow instance once, when it closes, so count instances by when they started and recompute a period as later files arrive. Build your first App pipeline, step 5 explains why.

Metric	Definition	From the feed
Flow Init	The number of flow instances that started.	`COUNT(*)`
Flow Complete	The number of flow instances that reached the complete event, however long they took.	Rows where `total_duration_ms` is 0 or more
Flow Success	The number of flow instances that completed within the flow's configured success threshold.	`SUM(success)`
Flow Avg Duration	The average time from the initial event to the complete event, over completed instances.	`AVG(total_duration_ms)`, excluding `-1` and `-2`
Flow Error	The number of configured error events during the flow. Optional.	`SUM(error)`

WITH m AS (
  SELECT
    Flow,
    DATE_TRUNC('day', FROM_UNIXTIME(StartTimeStampMs / 1000)) AS start_day,
    CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.success')           AS BIGINT) AS success,
    CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.error')             AS BIGINT) AS error_count,
    CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.total_duration_ms') AS BIGINT) AS duration_ms
  FROM app_flow_raw
  WHERE JSON_EXTRACT_SCALAR(MetricsInt, '$.success') IS NOT NULL
    AND JSON_EXTRACT_SCALAR(MetricsInt, '$.total_duration_ms') IS NOT NULL
)
SELECT
  start_day,
  Flow,
  COUNT(*)                                             AS flow_init,
  SUM(CASE WHEN duration_ms >= 0 THEN 1 ELSE 0 END)    AS flow_complete,
  SUM(success)                                         AS flow_success,
  SUM(COALESCE(error_count, 0))                        AS flow_error,
  AVG(CASE WHEN duration_ms >= 0 THEN duration_ms END) AS flow_avg_duration_ms
FROM m
GROUP BY start_day, Flow
ORDER BY start_day, Flow;

Flow Complete and Flow Success differ when a flow completes after its success threshold. That instance has a duration of 0 or more, so it counts toward Flow Complete, but it carries success of 0 and failure of 1. failure is 1 whenever success is 0, so SUM(failure) counts the instances that didn't succeed, whether they completed late or timed out.

In the delivered sample, three of the seven standard flows completed, and all three completed within their threshold. The other four carry -1, so they count toward Flow Init only.

Network request metrics

When network request metrics are enabled for a flow, its standard flow rows carry four more keys in MetricsInt: network_request_success_count, network_request_failure_count, network_request_success_duration_ms and network_request_failure_duration_ms. The two duration keys hold the total time of the requests, so divide by the matching count for an average. Confirm the key names in your feed with your Conviva Representative.

Metric	Definition	From the feed
Flow Network Request Count	The number of network requests during the flow.	`network_request_success_count` plus `network_request_failure_count`
Flow Network Request Failure	The number of network requests during the flow that received a failure response.	`network_request_failure_count`
Flow Network Request Avg Duration	The average network request response time during the flow.	`network_request_success_duration_ms` plus `network_request_failure_duration_ms`, divided by the request count

WITH m AS (
  SELECT
    Flow,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.network_request_success_count')       AS BIGINT), 0) AS ok_count,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.network_request_failure_count')       AS BIGINT), 0) AS fail_count,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.network_request_success_duration_ms') AS BIGINT), 0) AS ok_ms,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.network_request_failure_duration_ms') AS BIGINT), 0) AS fail_ms
  FROM app_flow_raw
  WHERE JSON_EXTRACT_SCALAR(MetricsInt, '$.success') IS NOT NULL
    AND JSON_EXTRACT_SCALAR(MetricsInt, '$.total_duration_ms') IS NOT NULL
)
SELECT
  Flow,
  SUM(ok_count) + SUM(fail_count)                                        AS flow_network_request_count,
  SUM(fail_count)                                                        AS flow_network_request_failure,
  1.0 * (SUM(ok_ms) + SUM(fail_ms)) / NULLIF(SUM(ok_count) + SUM(fail_count), 0) AS flow_network_request_avg_duration_ms,
  1.0 * SUM(ok_ms) / NULLIF(SUM(ok_count), 0)                            AS avg_successful_request_ms
FROM m
GROUP BY Flow;

For the average time of successful requests only, divide network_request_success_duration_ms by network_request_success_count, as in the last column. NULLIF returns null for a flow with no requests instead of failing on a zero denominator. The delivered sample has no network request keys, so this query returns zero counts and null averages for it.

Custom metrics

Each custom metric you configure in Conviva arrives as its own rows in the Flow Feed, with Flow set to the metric's name. Filter on that name.

Conversion metric

A conversion metric carries success and failure and no duration. Its rate is the share of attempts that converted:

SELECT
  Flow,
  COUNT(*)                                         AS attempts,
  SUM(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.success') AS BIGINT)) AS conversions,
  100.0 * SUM(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.success') AS BIGINT)) / COUNT(*) AS conversion_rate_pct
FROM app_flow_raw
WHERE Flow = 'Checkout Conversion'
GROUP BY Flow;

Duration metric

A duration metric carries total_duration_ms only, and Conviva emits it only for positive durations. The >= 0 condition below is a guard, so the query stays correct if a sentinel ever appears.

SELECT
  Flow,
  COUNT(*) AS measured,
  AVG(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.total_duration_ms') AS BIGINT)) AS avg_duration_ms
FROM app_flow_raw
WHERE Flow = 'Video Attempt to Manifest Request'
  AND CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.total_duration_ms') AS BIGINT) >= 0
GROUP BY Flow;

In the delivered sample, Video Attempt to Manifest Request averages 648 ms over its one row.

Event aggregation metric

An event aggregation metric aggregates a configured event as a Count, a Sum or an Average. A Count writes one integer key named after the metric. Add the values up, because a row can carry a count greater than 1:

SELECT
  Flow,
  SUM(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$["Video Attempt"]') AS BIGINT)) AS video_attempts
FROM app_flow_raw
WHERE Flow = 'conviva_video_attempt'
GROUP BY Flow;

In the delivered sample, the two conviva_video_attempt rows give 2 video attempts. A key that contains a space needs bracket syntax in the JSON path, as shown. In Parquet and Avro, use MetricsInt['Video Attempt'].

Sum results arrive in MetricsFloat. An Average writes two keys: <name>_count in MetricsInt and <name>_value in MetricsFloat, where _value is already the average for that row. To combine rows, weight each value by its count. This example uses a metric named Search Latency:

SELECT
  Flow,
  SUM(CAST(JSON_EXTRACT_SCALAR(MetricsFloat, '$["Search Latency_value"]') AS DOUBLE)
      * CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$["Search Latency_count"]') AS BIGINT))
    / NULLIF(SUM(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$["Search Latency_count"]') AS BIGINT)), 0) AS search_latency_avg
FROM app_flow_raw
WHERE Flow = 'Search Latency'
GROUP BY Flow;

Averaging the _value column directly gives every row equal weight, however many events it covers, and returns the wrong average.

Session metrics

Rows whose Flow value is session describe app sessions, one row per session. An app session closes after 90 seconds with no user interaction. Session rows carry these keys in MetricsInt, and your feed can carry more, depending on what's enabled for your account:

Key	Definition
`app_startup_count`	The number of app launches in the session.
`app_startup_time_ms`	Total time of the app startups in the session. Two startups of 100 ms and 200 ms give 300 ms.
`user_active_time_ms`	Time the user was active in the session.
`page_load_count`	The number of document loads in the session.
`page_load_time_ms`	Total time of the page loads in the session.
`screen_load_count`	The number of `conviva_screen_view` events in the session with a screen load time greater than 0 and up to 60 seconds.
`screen_load_time_ms`	Total time of the screen loads in the session.
`network_request_success_count`	The number of network requests in the session that received a success response code, 100 to 399.
`network_request_failure_count`	The number of network requests in the session that received a failure response: a 4xx or 5xx code, 0, or no code.
`network_request_success_time_ms`	Total time of the successful network requests in the session.
`network_request_failure_time_ms`	Total time of the failed network requests in the session.

The time keys hold totals for the session, so divide each by its matching count for an average. Session network request times end in _time_ms, while the flow network request keys end in _duration_ms.

WITH s AS (
  SELECT
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.app_startup_count')   AS BIGINT), 0) AS startups,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.app_startup_time_ms') AS BIGINT), 0) AS startup_ms,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.user_active_time_ms') AS BIGINT), 0) AS active_ms,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.page_load_count')     AS BIGINT), 0) AS page_loads,
    COALESCE(CAST(JSON_EXTRACT_SCALAR(MetricsInt, '$.page_load_time_ms')   AS BIGINT), 0) AS page_load_ms
  FROM app_flow_raw
  WHERE Flow = 'session'
)
SELECT
  COUNT(*)                                        AS app_sessions,
  SUM(startups)                                   AS app_startups,
  1.0 * SUM(startup_ms) / NULLIF(SUM(startups), 0)   AS avg_app_startup_time_ms,
  1.0 * SUM(active_ms) / NULLIF(COUNT(*), 0)         AS avg_active_time_per_session_ms,
  1.0 * SUM(page_load_ms) / NULLIF(SUM(page_loads), 0) AS avg_page_load_time_ms
FROM s;

The delivered sample has no session rows, so this query returns zero sessions for it.

Event Feed measures

The Event Feed has no metric columns. You compute counts, rates and time spans from the event rows, grouped by ECOSessionId for a per-session view.

Events and frustration signals per session:

SELECT
  ECOSessionId,
  COUNT(*)                                                              AS event_count,
  COUNT(DISTINCT EventName)                                             AS distinct_events,
  SUM(CASE WHEN EventCategory = 'Frustration Signal' THEN 1 ELSE 0 END) AS frustration_events,
  MAX(EventTimeMs) - MIN(EventTimeMs)                                   AS first_to_last_event_ms
FROM app_event_raw
GROUP BY ECOSessionId;

The share of sessions with no Media event, and the average time from a session's first event to its first Media event:

WITH s AS (
  SELECT
    ECOSessionId,
    MIN(EventTimeMs)                                           AS first_event_ms,
    MIN(CASE WHEN EventCategory = 'Media' THEN EventTimeMs END) AS first_media_ms
  FROM app_event_raw
  GROUP BY ECOSessionId
)
SELECT
  COUNT(*)                                                                   AS sessions,
  SUM(CASE WHEN first_media_ms IS NULL THEN 1 ELSE 0 END)                    AS sessions_without_media,
  100.0 * SUM(CASE WHEN first_media_ms IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS pct_sessions_without_media,
  AVG(first_media_ms - first_event_ms)                                       AS avg_time_to_first_media_ms
FROM s;

In the delivered sample, three of the five sessions have no Media event. A session with a single event returns 0 in the first query's time span, so filter on event_count when you need sessions with activity.

The mix of events by category and name:

SELECT
  EventCategory,
  EventName,
  COUNT(*)                                 AS events,
  COUNT(DISTINCT ECOSessionId)             AS sessions,
  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_all_events
FROM app_event_raw
GROUP BY EventCategory, EventName
ORDER BY events DESC;

Next steps

Schema and column dictionary for every field in both feeds.
Build your first App pipeline to load both feeds end to end.