Query for number of SMS and Direct mail messages sent

Question

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

IvanMi1 · Accepted Answer

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.

Mohan_Dugganab · Answer

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-query-examples

Cadastre-se

Fazer o login com rede social

Bem-vindo

Fazer o login com rede social