Query for number of SMS and Direct mail messages sent

[Neeharika_Shetty]

Hi all,

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

[dugganab]

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... 

[Ivan_Mironchuk]

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.