Expand my Community achievements bar.

Adobe Journey Optimizer Community Lens 6th edition is out.

Query for number of SMS and Direct mail messages sent


Level 1

Hi all,


Can anyone please help me with the query for the number of SMS and Direct Mail messages sent for a month?


Topics help categorize Community content and increase your ability to discover relevant content.

2 Replies



You can query it on the following lines 

SELECT DATE_FORMAT(timestamp, 'yyyy-MM') AS rolluptimestamp, SUM( CASE WHEN _experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus = 'sent' THEN 1 ELSE 0 END) AS deliveredcount FROM cjm_message_feedback_event_dataset WHERE _experience.customerjourneymanagement.messageprofile.channel._id = 'https://ns.adobe.com/xdm/channels/sms' GROUP BY  DATE_FORMAT(timestamp, 'yyyy-MM') ORDER BY rolluptimestamp desc;

Alter the channel Id accordingly for direct mail as https://ns.adobe.com/xdm/channels/direct-mail

 Reference - https://experienceleague.adobe.com/en/docs/journey-optimizer/using/data-management/datasets/datasets... 


Employee Advisor

You can do this as a query as stated below, but more easily, couldn't you just use the built-in channel reports? With the added benefit of being able to export these reports.