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

[god_prophet]

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


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

[Marcel_Szimonisz · Accepted solution]

Hello @god_prophet ,

ofcourse here is my take on the problem:

• create query on nms delivery table, minimum setup should be:
  
• Click on Add data... -> Data linked to filtering dimension  Data of filtering dimension -> add contact date([scheduling/@contactDate]) and channel(@messageType) click Finish

• 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
  
  

• 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
  
• With month names you can follow the iif  loginc on each month 

Results:

Marcel Szimonisz

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

[god_prophet]

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?

[Marcel_Szimonisz]

Hello @god_prophet,

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

[god_prophet]

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?

[Marcel_Szimonisz]

ah its data of filtering dimension sorry

[god_prophet]

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.

[Marcel_Szimonisz]

there is checkbox group

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

[god_prophet]

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,

[Marcel_Szimonisz]

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