Create A Weekly Subscriber Overview Notification Email – Customer Insights Journeys

Have you ever wanted just a simple weekly subscriber overview notification to know how many people are subscribed, with the number of new subscribers, how many unsubscribed and perhaps the number of new contacts/leads and maybe even information on the number of form submissions or journeys that were live for the previous week. You might even have your own set of information you want to track. You could even show a little list of all of the new Contacts subscribed too.

This is the end result we will work towards.

The first thing we need is a way to define WHO should be sent the email. In order to send emails, they need to go to a Contact or Lead. Contact makes the most sense so make sure the users who should get the email actually have a Contact record with their email address. No, we can’t send to a User unfortunately. Once you have the records, the way you access them is up to you, but I am going for a simple method of adding them to a Marketing List. That Marketing List can then be used to add each Contact linked to it through a Journey.

Next we need a Trigger that can be used to pass through information about the number of records found or created from the previous week. Create a new trigger and use the action of ‘When a customer interacts with a website/app’. This option then gives us the ability to add a set of attributes. It’s up to you to decide what you want to include in the email. In my example I am going to show a variety of values, so each of those will be the data type of Number. Then I want to show a date range which will be text, and will also generate a table of Contacts who submitted the subscription form (Contact List below) which also needs to be text. Save and publish the trigger.

Next, we need an email which will be used to pass values from the trigger. This is entirely up to you how you set it up, but the dynamic text needs to be mapped to each value from the Trigger like this. You can use custom font and divide it up however you see fit.

The red arrow image at the top, along with the inline condition is using logic that if the Growth field is greater than zero, the logic is positive and will show an up green arrow, otherwise it would be negative so will show the red down error. This uses a variation on the image by taking advantage of the conditional content functionality.

Finally, we need a flow to make it all run. You can run this as often as you would like, but I am going to run it every Monday at 9 AM.

If you don’t want to add any kind of list at the end of the email (to show a list of Contacts or Leads) you don’t need these next two actions. These will be used to get values (First Name, Last Name, Email) to then add to a table (the Array variable), and then format the HTML/CSS needed to pass back in through the trigger.

Next is really up to you and what you want to show. For each count of records, you will need a List rows step from the Dataverse connector. You will need to figure out the query to find the right records. For example, getting all of the Contact Point Consent records for a specific Purpose where the Contact Point Type is email, and the Consent Type is Opted In could be done like this. You can then modify the query for subsequent List rows steps to find those Opted Out or Opted In where the Created On date range is within a specific week.

Add in each of your List rows steps. Name them with something simple but easy to differentiate so we can easily count the number of rows found to add to the trigger in a moment. Now if you want to put together a list of records, either Contacts who submitted a form, or new Leads or something else, make sure you have a List rows step to find them first. Then add in an Apply to each step so for each record you append it to your array (use Append to array variable action) variable like this. This will form part of the structure of a table. The data-label part is needed so that we can then include CSS to make sure it is formatted one way on mobile and another way on desktop.

Next we need to add a Set variable action to join all of the values from the array in to a single string. We need this so we can pass it through as HTML to our email trigger. Your expression will be like this, where ‘Contact List’ is whatever you named your array variable.

join(variables('Contact List'), '')

Now you need a Compose step to add some styling to the table of records. First you will add in some CSS, then you will add in some HTML to start and end the table. This is an important step to make sure the table of records you have created (using the steps above) uses custom styling. Otherwise it will use the formatting from your email or, worse, will change all of the formatting of your email instead. This is what my Compose step looks like. Where you see @{variables(‘HTML Rows’)}, that needs to be the name of the variable from the step above. Feel free to use this as a starting point and change the colours to match your own branding.

<style>
.custom-table {
  border: 1px solid #1C6EA4;
  background-color: #FFFFFF;
  width: 100%;
  text-align: left;
  border-collapse: collapse;
  font-family: "Exo 2", sans-serif;
}
.custom-table td, .custom-table th {
  border: 1px solid #AAAAAA;
  padding: 3px 2px;
}
.custom-table tbody td {
  font-size: 13px;
  font-family: "Exo 2", sans-serif;
}
.custom-table thead {
  background: #225ea2;
  border-bottom: 2px solid #444444;
}
.custom-table thead th {
  font-size: 15px;
  font-weight: bold;
  color: #FFFFFF;
  border-left: 2px solid #D0E4F5;
  font-family: "Exo 2", sans-serif;
}
.custom-table thead th:first-child {
  border-left: none;
}
@media only screen and (max-width: 768px) {
  .custom-table { border: 0; }
  .custom-table thead { display: none; }
  .custom-table tr { display: block; margin-bottom: 1rem; border: 1px solid #ccc; padding: 10px; }
  .custom-table td {
    display: flex;
    justify-content: space-between;
    align-items: center;
    padding: 8px 10px;
    border: none;
    border-bottom: 1px solid #eee;
    font-size: 14px;
  }
  .custom-table td::before {
    content: attr(data-label);
    font-weight: bold;
    flex: 1;
    color: #225ea2;
  }
}
</style>
<table class="custom-table">
  <thead>
    <tr>
      <th>First Name</th>
      <th>Last Name</th>
      <th>Email</th>
    </tr>
  </thead>
  <tbody>
    @{variables('HTML Rows')}
  </tbody>
</table>

Now we need a step to find the Contacts that you added to your Marketing list. Note that if you don’t want to use a Marketing list, you need to come up with some other way to find the Contacts that should receive the notification. If it’s just one person, you could always hard code the GUID to send that person through your journey. For the Marketing List, we want to find all Marketing List Members where the list id equals the GUID from your Marketing List. Of course, many ways you can find that id by using a List rows step to look for the Marketing List with a specific name. Take your pick how you find this.

For each record found in the List rows step above, we need to fire the trigger with their information linked to it so we send them through the related Journey. Add in a Perform an unbound action step and then find the Action name which is msdynmkt_ and then the name of the trigger you created. If you did a formatted HTML table, add the Output from your final Compose step in to that value. For any variables you are capturing the number of records, that needs to be an expression that finds the length of the related List rows step. In the example below this would give the number of rows found for the list rows step that I renamed to Find Total Subscribers.

length(outputs('Find_Total_Subscribers')?['body/value'])

The last two items need to be the GUID for the Contact you want to send through the Journey. In the case of getting the members from a marketing list, use the Entity value (which is the Contact GUID). Save your flow and run it to make sure it runs without any errors.

Finally, create a Journey which will be Trigger based and will fire on your new custom trigger. Add in your email that uses all of the values from the trigger and then publish it. Off it goes ! Your marketing contacts will receive a nice looking email with all of the stats from the previous week. Depending on what email client they view it in, the formatting of the email should look good.

On a desktop the KPI’s will look like this (section with 2 columns for each row).

And the list of records in a table look like this:

And because columns are wrapped within a section on a mobile, we see the KPI’s like this from a phone.

And the list of records is formatted nicely so we can see the information clearly.

Original Post http://meganvwalker.com/weekly-subscriber-overview-notification/