What are Mail Merge Reports?

“Mail Merge Reports” are a powerful concept in PushMetrics that allows you to build scalable reporting and alerting workflows that send personalized messages to audiences of any size, while only maintaining one report template.

This is very useful if you want to:

  • send charts or dashboards to multiple people with different filters applied for each person,
  • monitor KPIs across many different dimensional breakdowns and then send alerts to the right audience,
  • send reports only if certain data conditions are met,
  • send transactional emails or Slack messages with values from a SQL query injected to the message.

How do Mail Merge Reports work?

You can combine a list of parameters (such as email addresses, names, regions, KPIs, etc.) with a report template (containing message, recipients, charts, dashboards, CSV exports, etc.).

The list of parameters can be provided via a static JSON array, or it can be loaded dynamically via a SQL query.

For each JSON object or for each row returned by the SQL query, one message is being sent.

How To Create a Mail Merge Report

On the reports page or in the side navigation click on "Create New Mail Merge Report" and give it a name.

Loading static parameters via JSON

Static parameters are entered manually in JSON format. Every object will result in one report message being sent where each parameter is replaced with the corresponding value.
You can create as many different parameters and name them as you like.

Example JSON:

[
    {
        "email_subject": "Monthly Revenue for John",
        "client_name": "John Doe",
        "email_recipients": "john.doe@pushmetrics.io",
        "slack_recipients": "John Doe (U4FPP5UU8)",
        "filtername": "Region",
        "filtervalue": "Asia"
    },
    {
        "email_subject": "Monthly Revenue for Dave",
        "client_name": "Dave Doe",
        "email_recipients": "dave.doe@pushmetrics.io",
        "slack_recipients": "general (C4FPP5UU8)",
        "filtername": "Region",
        "filtervalue": "North America"
    }
]


Slack Recipients:

Notice that in order to send dynamically to different Slack channels or recipients,  the format needs to be like this: channel name (channel ID) - e.g. general (C4FPP5UU8) .
Luckily, we have a built-in auto-completion for Slack channels in the JSON editor:


Loading static parameters via SQL Query

You can also load parameters in a fully dynamic way via a SQL query.

The basic idea is: Every row returned by the query is going to result in one report message. And every column will be turned into a parameter that can be used as a "merge field".

This has a few powerful implications:

  1. You can pull a distribution list dynamically — at runtime — from your database.
  2. If no rows are returned, nothing is sent.
  3. Only returned rows produce a message
    You can turn a normal report into an alert rule just by adding a WHERE  or HAVING clause to the query. This way you can implement any alerting logic that can be put into a SQL statement
  4. Parameters can serve as meta data to filter charts & dashboards
    You can pull filter values via SQL and then apply them to charts, tables & dashboards to send out personalized data visualizations .
  5. The message can be tweaked depending on the data
    You can bring in conditional formatting and even change entire sentences depending on KPI values. This enables a high degree of personalization and relevance. You can do this by using CASE  statements in the query or {% if %}  functions in the message template.

Prerequisites:
To load parameters via SQL query you need to have
a) a SQL database connection and
b) a saved query that returns the parameters

Once you select a query from the list of saved queries, the query is executed with LIMIT 1  applied to fetch the column names. If the query returns data, the column names are made available as auto-complete parameters throughout the report. We will also display a preview below the query.

If no data is returned at this point, you can still use the parameters but you will need to type them yourself. 


Building the report template

In the next step, you use the variables all across the report to customize:

  • attachments,
  • recipients,
  • file names,
  • subject line,
  • email body,
  • etc.

Templating Syntax

The general syntax to use parameters in the report template is {{ parameter_goes_here }}  and is following Jinja templating syntax.

For example, if your JSON or SQL has a parameter named first_name  you can apply it like this: {{ first_name }} 

Please make sure you don't have spaces in your parameter names.

You can also use more advanced options to dynamically change the message. For example:

Loops

{% for parameter in range(parameter) %} ... {% endfor %}
 

IF statements
{% if parameter > 100 %} ... {% else %} ... {% endif %} 

We also expose a few date functions:

  • {{ds}}  ==> today's date
  • {{ts}}   ==> current timestamp
  • {{yesterday_ds}}  ==> yesterday's date
  • {{tomorrow_ds}}   ==> tomorrow's date
  • {{ ds | date_format('Today is %d, %b %Y') }}  
  • {{ ds | date_add(7) }}   ==> adds 7 days to today's date
  • {{ ds | date_subtract(3) }}   ==>  today's date - 3 days

Current Limitations:

  • Please note that we currently limit mail merge reports to 100 objects/rows by default, i.e. max. 100 messages will be generated with one mail merge report. Contact us if you need more. 
  • Execution of mail merge reports (especially with SQL) can take a while. Don't be surprised if it takes 5 minutes or more until you receive the messages. We're working on speeding up the process.
Did this answer your question?