Expand my Community achievements bar.

SOLVED

how to generate a report of deliveries (by type: email, sms, push)?

Avatar

Level 5

Does anyone know how to create this table using a workflow? 


+------------+---------+------+-----------+
| month       | emails | push | sms |
+-----------+--------+------+-------------+
| august       |     120 |     15 |  10 |
+-----------+--------+------+-------------+
| september |     233 |     12 | 23 |
+-----------+--------+------+------------+

Topics

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hello @ogonzalesdiaz ,

ofcourse here is my take on the problem:

  • create query on nms delivery table, minimum setup should be:
    Marcel_Szimonisz_2-1698259975348.png

     

  • Click on Add data... -> Data linked to filtering dimension  Data of filtering dimension -> add contact date([scheduling/@contactDate]) and channel(@messageType) click Finish

Marcel_Szimonisz_0-1698258247214.png

 

  • Now the magic part begins.
  • Open Edit additional data..  ->Click on Advanced settings and select option Disable automatic adding of the primary keys of the targeting dimension

    Marcel_Szimonisz_1-1698258484739.png
  • Group by Channel and Add expression for contact date YearAndMonth([scheduling/@contactDate]) give it alias e.g. @yearAndMonth and check the "Group" on the field settings 

  • Check enumeration for channel - for your scenario is 0 is for email , 41 and 42 for push IOS and android  and 1 is for sms

 

 

 

 

 

<enumeration basetype="byte" default="mail" label="Channel" name="messageType">
    <help>Type of messages to send.</help>
    <value desc="Email channel" img="nms:email.png" label="Email" name="mail" value="0"/>
    <value desc="Mobile channel (SMS)" img="nms:mobile.png" label="Mobile (SMS)"
           name="sms" value="1"/>
    <value desc="Telephone channel" img="nms:phone.png" label="Phone" name="phone"
           value="2"/>
    <value desc="Direct mail channel" img="nms:paper.png" label="Direct Mail" name="paper"
           value="3"/>
    <value desc="Fax channel" img="nms:fax.png" label="Fax" name="fax" value="4"/>
    <value applicableIf="HasPackage('nms:centralLocal')" desc="Agency channel" img="nms:agency.png"
           label="Agency" name="agency" value="5"/>
    <value applicableIf="HasPackage('nms:social')" desc="Facebook channel" img="nms:facebook.png"
           label="Facebook" name="facebook" value="20"/>
    <value applicableIf="HasPackage('nms:social')" desc="Twitter Channel" img="nms:twitter.png"
           label="Twitter" name="twitter" value="25"/>
    <value applicableIf="HasPackage('nms:mobileApp')" desc="iOS applications channel"
           img="nms:ios.png" label="iOS channel" name="ios" value="41"/>
    <value applicableIf="HasPackage('nms:mobileApp')" desc="Android applications channel"
           img="nms:android.png" label="Android channel" name="android" value="42"/>
    <value applicableIf="HasPackage('nms:line') OR HasPackage('nms:lineV2')" desc="LINE channel"
           img="nms:line.png" label="LINE" name="line" value="61"/>
    <value desc="Other channel" img="nms:other.png" label="Other" name="other" value="120"/>
    <!-- value 127 (a.k.a all channels) is already defined in other schemas (i.e nms:operation, nms:remaHypothesis, nms:typologyRule)-->
    <!-- NEO-34473: make sure enum value is in the range of one byte signed-->
    <value desc="Undefined channel" img="nms:other.png" label="Undefined" name="undefined"
           value="126"/>
  </enumeration>

 

 

 

 

  • Add new expressions for each channel
    • for email -> sum(Iif(ToInteger(@messageType)=0, 1, 0))
    • for sms -> sum(Iif(ToInteger(@messageType)=1, 1, 0))
    • for push -> sum(Iif(ToInteger(@messageType)=41, 1, iif(ToInteger(@messageType)=42,1,0)))
  • When done it should look like
    Marcel_Szimonisz_4-1698260125058.png

     

  • With month names you can follow the iif  loginc on each month 


Results:

 

Marcel_Szimonisz_5-1698260243095.png

 

 

Marcel Szimonisz

MarTech Consultant
for more tips visit my blog
https://www.martechnotes.com/

View solution in original post

9 Replies

Avatar

Correct answer by
Community Advisor

Hello @ogonzalesdiaz ,

ofcourse here is my take on the problem:

  • create query on nms delivery table, minimum setup should be:
    Marcel_Szimonisz_2-1698259975348.png

     

  • Click on Add data... -> Data linked to filtering dimension  Data of filtering dimension -> add contact date([scheduling/@contactDate]) and channel(@messageType) click Finish

Marcel_Szimonisz_0-1698258247214.png

 

  • Now the magic part begins.
  • Open Edit additional data..  ->Click on Advanced settings and select option Disable automatic adding of the primary keys of the targeting dimension

    Marcel_Szimonisz_1-1698258484739.png
  • Group by Channel and Add expression for contact date YearAndMonth([scheduling/@contactDate]) give it alias e.g. @yearAndMonth and check the "Group" on the field settings 

  • Check enumeration for channel - for your scenario is 0 is for email , 41 and 42 for push IOS and android  and 1 is for sms

 

 

 

 

 

<enumeration basetype="byte" default="mail" label="Channel" name="messageType">
    <help>Type of messages to send.</help>
    <value desc="Email channel" img="nms:email.png" label="Email" name="mail" value="0"/>
    <value desc="Mobile channel (SMS)" img="nms:mobile.png" label="Mobile (SMS)"
           name="sms" value="1"/>
    <value desc="Telephone channel" img="nms:phone.png" label="Phone" name="phone"
           value="2"/>
    <value desc="Direct mail channel" img="nms:paper.png" label="Direct Mail" name="paper"
           value="3"/>
    <value desc="Fax channel" img="nms:fax.png" label="Fax" name="fax" value="4"/>
    <value applicableIf="HasPackage('nms:centralLocal')" desc="Agency channel" img="nms:agency.png"
           label="Agency" name="agency" value="5"/>
    <value applicableIf="HasPackage('nms:social')" desc="Facebook channel" img="nms:facebook.png"
           label="Facebook" name="facebook" value="20"/>
    <value applicableIf="HasPackage('nms:social')" desc="Twitter Channel" img="nms:twitter.png"
           label="Twitter" name="twitter" value="25"/>
    <value applicableIf="HasPackage('nms:mobileApp')" desc="iOS applications channel"
           img="nms:ios.png" label="iOS channel" name="ios" value="41"/>
    <value applicableIf="HasPackage('nms:mobileApp')" desc="Android applications channel"
           img="nms:android.png" label="Android channel" name="android" value="42"/>
    <value applicableIf="HasPackage('nms:line') OR HasPackage('nms:lineV2')" desc="LINE channel"
           img="nms:line.png" label="LINE" name="line" value="61"/>
    <value desc="Other channel" img="nms:other.png" label="Other" name="other" value="120"/>
    <!-- value 127 (a.k.a all channels) is already defined in other schemas (i.e nms:operation, nms:remaHypothesis, nms:typologyRule)-->
    <!-- NEO-34473: make sure enum value is in the range of one byte signed-->
    <value desc="Undefined channel" img="nms:other.png" label="Undefined" name="undefined"
           value="126"/>
  </enumeration>

 

 

 

 

  • Add new expressions for each channel
    • for email -> sum(Iif(ToInteger(@messageType)=0, 1, 0))
    • for sms -> sum(Iif(ToInteger(@messageType)=1, 1, 0))
    • for push -> sum(Iif(ToInteger(@messageType)=41, 1, iif(ToInteger(@messageType)=42,1,0)))
  • When done it should look like
    Marcel_Szimonisz_4-1698260125058.png

     

  • With month names you can follow the iif  loginc on each month 


Results:

 

Marcel_Szimonisz_5-1698260243095.png

 

 

Marcel Szimonisz

MarTech Consultant
for more tips visit my blog
https://www.martechnotes.com/

Avatar

Level 5

Hi @Marcel_Szimonisz , why the first query has a : sent proof == No? 

Sometimes, our deliveries sent a proof before sending the message to final users. 

Is the first query: 

1.- Excluding all the deliveries that used a proof before sending the final message? Or
2.- Is excluding all the proofs only?

Avatar

Community Advisor

Hello @ogonzalesdiaz,

I did small type between those two conditions put OR instead of AND.

  • @fcp - excludes proofs
  • contact date is empty excludes "pencil deliveries" that are created when you are using the Delivery activity where you set action and delivery is created from template.. and everytime you run it in proof mode it creates deliveries that never run and are in edit status. so this query excludes them

 

Marcel

Avatar

Level 5

hi @Marcel_Szimonisz , I don't find the fields: 

I'm querying nms:delivery, but I don't find "contact date" or "channel". 

Please, could you elaborate on where to find them?

ogonzalesdiaz_0-1698342673644.png



ogonzalesdiaz_1-1698342763716.png

 

 

Avatar

Level 5

Hi @Marcel_Szimonisz , how do you : 

  • Group by Channel and add expression for contact date YearAndMonth([scheduling/@contactDate]) give it alias e.g. @yearAndMonth and check the Group 

Thank you.

Avatar

Community Advisor

there is checkbox group

Marcel_Szimonisz_0-1698422331147.png

and for adding expression you need to click on field and then on right you have this blue icon.

Marcel_Szimonisz_1-1698422356077.png

 

 

Avatar

Level 5

Thanks @Marcel_Szimonisz , last question: 

In your last image I don't see the channel field, but only email, sms, push. 

Should I remove it? Why was it added in the first place?

Ty,

ogonzalesdiaz_0-1698423602118.png

 

Avatar

Community Advisor

Im not grouping by channel. it seems as i was changing my original answer i said to group by channel. the final settings should loook like below

Marcel_Szimonisz_0-1698425540571.png