Let’s create a nice Report for the business teams to be able to analyze their deliveries with the Label, the Subject, the UTM Codes, the delivery indicators (sent/received/failed) and the tracking indicators (open/click/transactions).

Objective

We will be achieving the following:

Workflow setup

Create a Report Workflow like below:

Also, create 2 workflow variables in Workflow Properties > Variables

  • startDate (Datetime)
  • endDate (Datetime)

The JS activity

We are going to rely on 2 the context vars we just created, with ctx.vars:

loadLibrary('vendor:moment')
// set default date
ctx.vars.startDate = moment().startOf('month').format('YYYY-MM-DD');
ctx.vars.endDate = moment().endOf('month').format('YYYY-MM-DD');

See this tutorial to install the library vendor:moment can be installed

The Query

In the query, remove proofs via @FCP = No, ensure you have a contact date is not empty and Contact date should be:

  • on or after ToDate($([vars/startDate])) if length($([vars/startDate]) ) > 0 AND
  • on or before ToDate($([vars/endDate])) if length($([vars/endDate]) ) > 0

Also, order by Contact Date ASC.

The Page

Create a container with 2 inputs Date and a Link Refresh:

Then, the table should look as follow:

Fix timezone

Add a new JS activity before the Query:

loadLibrary('vendor:moment')
// set default start/end date to first/last day of today's month
// we have to set 10AM to be sure for the timezone
ctx.vars.startDate = moment(ctx.vars.startDate.toString()).hour(10).format('YYYY-MM-DD HH:mm:ss');
ctx.vars.endDate = moment(ctx.vars.endDate.toString()).hour(10).format('YYYY-MM-DD HH:mm:ss');