Expand my Community achievements bar.

workflow reminder issue on query

Avatar

Level 3

Hello

 

I am trying to send a reminder email 2 days after a recipient surbscribe to our newsletter.

 

1- from website, user enter email

2- message cnter process through transactionnal messages (evet type name: welcome)

3- I query on Archived events (maybe should not) to get all users created on on or after 2 days before and status equal to Sent and event type equal to 'welcome'

4- I am blocked here, cause I tryied a change dimension on recipient and 0 population get through the transition to the delivery, I have also tried to change the target mapping of th edelivery in the TO section, for Real time event (nms:rtEvent) and even Archived element (nms:eventHisto) but message got into conflict at the prepare step.  

 

rbiri_2-1694727595589.png

 

rbiri_0-1694727531368.pngrbiri_1-1694727557833.png

Have you ever tried to set up a reminder after a welcome email have been received and not open by user?

 

thanks

 

 

11 Replies

Avatar

Community Advisor

Hi @rbiri ,

 

Please use the OOTB Target Mapping named "Archived element" which has the Targeting Schema as nms:eventHisto to send the email , and also change dimension is not required as the Target Mapping Data will match the query's Data and try sending email to the Targeted recipients. PFA below screenshot of the Target Mapping to be used in your workflow.

 

LakshmiPravallika_0-1694761162373.png

 

Regards,

Pravallika.

 

Avatar

Level 3

Hello

 

You screenshot is blurry and I cant for some reason open it too, how do I get to this config?

Avatar

Community Advisor

Hi @rbiri ,

 

Please go to the folder "/Administration/Campaign Management/Target mappings/" Folder and select the "Archived element" Target Mapping in your Delivery activity and you can remove Change dimension activity in your workflow.

 

Regards,

Pravallika.

Avatar

Community Advisor

Hi @rbiri ,

To Send Reminder Email to the audience who have received the email (via transactional message), but haven't opened it, you can configure a workflow as below:

ParthaSarathy_0-1694791357185.png

 

Query:

Targeting Dimension: nms:eventHisto

Filtering Dimension: nms:eventHisto

ParthaSarathy_1-1694791575228.png

This condition results All audience who had received email 2 days back, and haven't opened it.

Deduplication: (If Required in your use case)

Select Temporary schema > Identify duplicate based on email > limit the count to 1

 

Recurring Delivery

To > select target mapping as 'Archived events'

ParthaSarathy_2-1694791782172.png

 

And the reason you get 0 as output for change dimension is, nms:eventHisto and nms:recipient table is linked with eventHisto's external Identifier and Recipient's account field.

If your SOAP request have externalId in <rtEvent/> tag, and if it match recipient record's @account field, then you will get output in change dimension.

 

<urn:domEvent>
<rtEvent type="welcome" email="partha@test.com" wishedChannel="0" externalId="123456" />
</urn:domEvent>

 

 

ParthaSarathy S

Avatar

Level 3

hello

 

Thank you for all the details provided, it is very helpful, I have tried your solution, but my question is.this complmentary with LakshmiPravallika solution where I need to update the target mapping of event history too?

cause I have been trying your solution with  same query, but I get an error on  delivery Recurring  when performing action 'Prepare' on delivery.

Is it mean I need to update archive element target mapping?

current config:

rbiri_3-1694808354513.png

 

Avatar

Community Advisor

@rbiri , you no need to modify target mapping. You need to call this 'Archived events' target mapping in your recurring delivery 'To' section. If you had already done this and still get error, then go to the delivery created, open it, goto Audit tab and send the screenshot of what error you receive.

Avatar

Level 3

here is the error received

 

rbiri_0-1694831635449.png

09/15/2023 4:11:38 PM	XSV-350122 An error occurred and the process has been stopped.
09/15/2023 4:11:38 PM	SOP-330011 Error while executing the method 'PrepareTargetImpl' of service 'nms:delivery'.
09/15/2023 4:11:38 PM	WDB-200001 SQL statement 'INSERT INTO wkDlv_2139245412T (, tsEvent, sTargetCode, iDeliveryId, iTargetDataId, sAddress, iFormat, dMessageWeight) (SELECT   R2.sAccount, TIMESTAMP WITH TIME ZONE '2023-09-15 16:11:38.854-04', E'__MAIN__', 2139245412, W0.iTargetDataId, E1.sEmail, E1.iEmailFormat, 5 FROM wkf2139244816_24_1_ALL W0, NmsEventHisto E1 LEFT JOIN NmsRecipient R2 ON (R2.sAccount = E1.**bleep**ternalId) WHERE ((E1.biEventHistoId = W0.biId)))' could not be executed.
09/15/2023 4:11:38 PM	PGS-220000 PostgreSQL error: ERROR:  syntax error at or near "," LINE 1: INSERT INTO wkDlv_2139245412T (, tsEvent, sTargetCode, iDeli...                                        ^ .

 

Avatar

Community Advisor

@rbiri , Ok this is expected error for 'Archived events' target mapping.

Modify the above provide workflow as below,

ParthaSarathy_0-1694832666422.png

 

(Replace Recurring delivery with JavaScript)

Query:

Add data > add @email in output column 

ParthaSarathy_1-1694832814701.png

 

Go to Query activity's Advanced tab > modify name as: queryArchivedEvent

 

Javascript&colon;

copy paste the below script, and not to modify any.

var query = xtk.queryDef.create( 
  <queryDef schema="temp:queryArchivedEvent" operation="select"> 
    <select>     
    <node expr="@email"/>
    </select>          
  </queryDef>); 
var operats = query.ExecuteQuery()
logInfo(operats)

for each ( var abc in operats){
var email = abc.@email;
var deliveryId = nms.delivery.SubmitNotification("emailArchivedEvent",<delivery>
            <targets fromExternalSource='true'>
              <externalSource>email|
                        {email}                        
              </externalSource>
            </targets>
          </delivery>);
}

 

Save the workflow.

 

Now go to any delivery template folder, create a new email delivery template with internal name "emailArchivedEvent"

 

Then, create a .csv file with below data,

ParthaSarathy_2-1694833405944.png

 

In delivery template Go to 'To' > select nms:recipient as target mapping, and follow the below set up but uploading the csv file,

email.png

configure email body and save the template.

 

Now run your workflow. You will receive the email.

 

Avatar

Level 3

hello @ParthaSarathy 

 

Thanks for the step provided, I have followed your solution, and it is working with that test user from the list..but I have run the query with real data, and the JS activity run into a conflict

 

rbiri_1-1695131352302.png

09/19/2023 9:44:36 AM	js	SCR-160012 Javascript&colon; error while evaluating script 'PWKF7159/js'.
09/19/2023 9:44:36 AM	js	SOP-330011 Error while executing the method 'SubmitNotification' of service 'nms:delivery'.
09/19/2023 9:44:35 AM	js	DLV-490118 Error during preparation. Please refer to the delivery action journal '2139249912' for more information.
09/19/2023 9:44:33 AM	js	Missing type definition in schema on attribute '@id' (line 110, document 'nms:seedMember'). Using 'string'.
09/19/2023 9:44:33 AM	js	Missing type definition in schema on attribute '@id' (line 92, document 'nms:seedMember'). Using 'string'.
09/19/2023 9:44:33 AM	js	Missing type definition in schema on attribute '@id' (line 81, document 'nms:seedMember'). Using 'string'.
09/19/2023 9:44:33 AM	js	<queryArchivedEvent-collection>   <queryArchivedEvent email="xxx.xxx@xxx.com"/>   <queryArchivedEvent email="xxx.xxx@xxx.com"/> </queryArchivedEvent-collection>
09/19/2023 9:44:33 AM		Workflow resumed')
09/19/2023 9:44:33 AM		Workflow 'PWKF7159' is being run

I wonder if there is something I need to change in the script above? like var email

rbiri_2-1695131552477.png

 

Avatar

Level 3

or do I need to change the file format ?

rbiri_3-1695131728319.png

 

Avatar

Administrator

Hi @ParthaSarathy,

Could you please help @rbiri further here with their query?

Thanks! 



Sukrity Wadhwa