Tracking Comms Cadence Across Manual and Automated Messages

Thanks for your suggestion, Deepanshu!

I was able to figure out an alternative way using Braze Queries as well! The query below analyzes message volume per user for Email, Push, and In-App over the past 30 days. It groups users into buckets based on their message counts relative to percentile thresholds — median (50th), 75th, and 90th percentiles. For each bucket, it summarizes the number of users, as well as the average, minimum, and maximum messages received, and provides a sample of 5 users per bucket per channel. In case this might be helpful for you as well:

WITH email AS (
  SELECT 
    EXTERNAL_USER_ID,
    COUNT(DISTINCT DISPATCH_ID) AS message_count,
    'Email' AS channel
  FROM USERS_MESSAGES_EMAIL_SEND_SHARED
  WHERE TIME > (DATE_PART(EPOCH_SECOND, CURRENT_TIMESTAMP()) - 2592000)
  AND EXTERNAL_USER_ID IN ()
  GROUP BY EXTERNAL_USER_ID
),
inapp AS (
  SELECT 
    EXTERNAL_USER_ID,
    COUNT(*) AS message_count,
    'In-App Message' AS channel
  FROM USERS_MESSAGES_INAPPMESSAGE_IMPRESSION_SHARED
  WHERE TIME > (DATE_PART(EPOCH_SECOND, CURRENT_TIMESTAMP()) - 2592000)
  AND EXTERNAL_USER_ID IN ()
  GROUP BY EXTERNAL_USER_ID
),
push AS (
  SELECT 
    EXTERNAL_USER_ID,
    COUNT(DISTINCT DISPATCH_ID) AS message_count,
    'Push Notification' AS channel
  FROM USERS_MESSAGES_PUSHNOTIFICATION_SEND_SHARED
  WHERE TIME > (DATE_PART(EPOCH_SECOND, CURRENT_TIMESTAMP()) - 2592000)
  AND EXTERNAL_USER_ID IN ()
  GROUP BY EXTERNAL_USER_ID
),
all_messages AS (
  SELECT * FROM email
  UNION ALL
  SELECT * FROM inapp
  UNION ALL
  SELECT * FROM push
),
stats AS (
  SELECT 
    channel,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY message_count) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY message_count) AS p75,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY message_count) AS p90
  FROM all_messages
  GROUP BY channel
),
buckets AS (
  SELECT 
    m.channel,
    m.EXTERNAL_USER_ID,
    m.message_count,
    s.median,
    s.p75,
    s.p90,
    CASE 
      WHEN m.message_count <= s.median THEN 'Up to median'
      WHEN m.message_count <= s.p75 THEN 'Above median up to 75th percentile'
      WHEN m.message_count <= s.p90 THEN 'Above 75th percentile up to 90th percentile'
      ELSE 'Above 90th percentile'
    END AS bucket
  FROM all_messages m
  JOIN stats s ON m.channel = s.channel
),
bucket_counts AS (
  SELECT 
    channel,
    bucket,
    COUNT(*) AS user_count,
    AVG(message_count) AS avg_messages,
    MIN(message_count) AS min_messages,
    MAX(message_count) AS max_messages
  FROM buckets
  GROUP BY channel, bucket
)
-- Final output: bucket stats + sample users per bucket
SELECT 
  b.channel,
  b.bucket,
  bc.user_count,
  bc.avg_messages,
  bc.min_messages,
  bc.max_messages,
  b.EXTERNAL_USER_ID,
  b.message_count
FROM buckets b
JOIN bucket_counts bc 
  ON b.channel = bc.channel AND b.bucket = bc.bucket
QUALIFY ROW_NUMBER() OVER (PARTITION BY b.channel, b.bucket ORDER BY b.EXTERNAL_USER_ID) <= 5
ORDER BY b.channel, b.bucket, b.EXTERNAL_USER_ID;