Skip to content

Category: Power Platform

Understand email statistics in ClickDimensions

Posted in ClickDimensions, Dynamics 365, and Power Platform

Email statistic calculation

If you’ve ever wonder why the count of your marketing lists members differs from the email statistic that ClickDimensions has sent, then the following list will help you to understand how ClickDimensions calculates the email delivery.

Email statistic report from ClickDimensions
  • + LISTED RECIPIENTS
    • + Recipients – leads, contacts and accounts that are connected to the ‘cdi_emailsend’ as recipient, directly or via marketing lists
    • – Duplicate email addresses over the recipients
  • – EXCLUDED RECIPIENTS
    • + Recipients without an email address
    • + Email addresses that are already blocked
      • + Email addresses with a previous hard bounce
        • + DNS failures
        • + Generic Bounces: No RCPT
        • + Invalid Recipients
      • + Email addresses with 4 soft bounces within a 90-day period
      • + Email addresses that were blocked in a previous email
    • + Recipients with unsubscribed email addresses
      • + Recipients ‘donotemail’ setting is ‘Do not allow’
      • + Recipients ‘donotbulkemail’ setting is ‘Do not allow’
      • + Recipients whose email address is listed in the ‘Unsubscribe’ records
      • + Recipients whose email address is opted-out from the related subscription list
      • + Recipients whose email address have no opt-in for the related subscription list (only in opt-in model)
    • + Suppressed recipients
      • + Email addresses from leads, contacts and accounts that are connected to the ‘cdi_emailsend’ via a suppressed marketing list
  • = MESSAGES SENT
  • – BOUNCES
    • + Hard bounces
      • + DNS Failures
      • + Generic Bounces: No RCPT
      • + Invalid Recipients
    • + Soft bounces
  • = EMAILS DELIVERED

Queries for email statistic in D365CE

This list contains the OData 4.0 queries to collect all the data for email delivery and interactions for reporting purposes.

Email statistic data  stored in D365CE
GET [Organization URI]/api/data/v9.1/
  • Excluded recipients
    cdi_excludedemails?$filter=_cdi_emailsendid_value eq {ID}
  • Recipients without an email address
    cdi_excludedemails?$filter=_cdi_emailsendid_value eq {ID} and cdi_reason eq 4
  • Recipients with already blocked email addresses
    cdi_excludedemails?$filter=_cdi_emailsendid_value eq {ID} and cdi_reason eq 5
  • Recipients with unsubscribed email addresses
    cdi_excludedemails?$filter=_cdi_emailsendid_value eq {ID} and (cdi_reason eq 1 or cdi_reason eq 7
  • Recipients that are connected via a suppressed marketinglist
    cdi_excludedemails?$filter=_cdi_emailsendid_value eq {ID} and cdi_reason eq 6
  • Messages sent
    cdi_sentemails?$filter=_cdi_emailsendid_value eq {ID}
  • Hard bounces
    cdi_emailevents?$filter=_cdi_emailsendid_value eq {ID} and (cdi_type eq 10 or cdi_type eq 15 or cdi_type eq 9
  • Soft bounces
    cdi_emailevents?$filter=_cdi_emailsendid_value eq {ID} and cdi_type eq 3
  • Emails delivered
    cdi_sentemails?$filter=_cdi_emailsendid_value eq {ID} and cdi_bouncescount lt 1 and cdi_deliveriescount gt 0
  • Interactions
    cdi_sentemails?$filter=_cdi_emailsendid_value eq {ID} and (cdi_clickscount gt 0 or cdi_openscount gt 0)
  • Unique opens
    cdi_sentemails?$filter=_cdi_emailsendid_value eq {ID} and (cdi_openscount gt 0)
  • Total opens
    cdi_emailevents?$filter=_cdi_emailsendid_value eq {ID} and cdi_type eq 2
  • Unique clicks
    cdi_sentemails?$filter=_cdi_emailsendid_value eq {ID} and (cdi_clickscount gt 0)
  • Total clicks
    cdi_emailevents?$filter=_cdi_emailsendid_value eq {ID} and cdi_type eq 4
  • Unsubscribes
    cdi_emailevents?$filter=_cdi_emailsendid_value eq {ID} and cdi_type eq 6

Inferred opens

An inferred open is counted when a recipient clicks a link in an email or unsubscribes, but does not download the images (and also the tracking pixel) in the email.

The unique opens in the email statistics in Dynamics 365 CE do not include inferred opens.
The ClickDimensions email report includes the inferred opens in its unique opens count.


KPIs calculation from email statistic values

Basics for marketeers but maybe not for Dynamics 365 folks and of course it is also handy to have all in one place.

  • Bounce rate
    Bounces / Message sent * 100
  • Soft bounce rate
    Soft bounces / Message sent * 100
  • Hard bounce rate
    Hard bounces / Message sent * 100
  • Delivery rate
    Deliveries / Messages sent * 100
  • Interaction rate
    Interactions / Messages sent * 100
  • Open rate
    Unique opens / Messages sent * 100
  • Click rate
    Unique clicks / Messages sent * 100
  • Unsubscribe rate
    Unsubscribes / Messages sent * 100

ClickDimensions Release Notes – All-In-One Edition

Posted in Dynamics 365, ClickDimensions, and Power Platform

ClickDimensions has a monthly release cycle with a yearly major release. Surely you can imagine that most of the customers don’t prefer the automatic update and of course nobody makes manually updates on a regular basis.
The result of this combination is that I get often the question from customers: “What has changed since our last update?”.

The release notes of ClickDimensions are public available as single articles, starting from Feb 2018 – v9. These article contain the changes for each single version.

ClickDimensions Release Notes

Therefore, I started my own document that combines these releases notes articles, and I want to share it with you.

Features of my ClickDimensions Release Notes All-In-One Edition

Or in other words: “Benefits of bringing them in all together in an Excel file.”

  • Filter by date (EU release) or ClickDimensions version
  • Filter by ClickDimensions feature (Campaingautomation, Email, Analytics, etc.)
  • Filter by type (new features, improvements, changes and fixes)
  • Filter by update type (instant update or solution update required)
  • Search for a defect number
  • Versions that are not supported anymore are greyed out

RetrieveTotalRecordCount Bookmarklet

Posted in Dynamics 365, and Power Platform

Counting records in CRM can be tricky and time consumption when there are more than 5.000 records in the table. There are several ways and tools to do this that are already well described in the community.

When you are fine with the fact that the numbers you receive are static from a count last night, you can use the RetrieveTotalRecordCount function.
There are descriptions in the community on how to use it, too:

I combined both and created a bookmarklet, so that you don’t need to to memorize the url path or modify a bookmark everytime when you are in a different system or need another table (entity).

The RetrieveTotalRecordCount Bookmarklet

Create a normal bookmark in you browser and replace its url with the following code:

javascript:function proc(){var r = JSON.parse(this.responseText).EntityRecordCountCollection,t = [];for (let i = 0; i < r.Keys.length; i++) {t.push(r.Keys[i]+"="+r.Values[i])}prompt("Result", t.join(","))} etn = prompt("EntitySchemaName as CSV?", "account,contact").split(",").map((str) => str.replace(/\s/g,''));var oReq = new XMLHttpRequest();oReq.addEventListener("load", proc);oReq.open("GET","api/data/v9.1/RetrieveTotalRecordCount(EntityNames=['"+etn.join("', '")+"'])");oReq.send();

You can use it at any place inside of D365 CE. You only need to write in a SchemaName of an entity (table) or several, comma separated SchemaNames and it answers you with the EntitySetName and the number of records in it (counted last night).

EntitySetName Bookmarklet

Posted in Dynamics 365, Power Automation, and Power Platform

Almost six years after my last post about bookmarklets, I’m proud to present you a new bookmarklet for Dynamics 365 CE.

It can be used to retrieve the EntitySetName for for an entity (table).

In case you don’t know what the EntitySetName, here is what Microsoft writes about it.

This value is used in the resource path for this entity in the Web API. For custom entities, you can change the name of the entity set used. By default it is the same as the LogicalCollectionName.

Source: Microsoft Docs

In other words, you need when you use the WebApi or the Common Data Service (current environment) connector in PowerAutomate.

The EntitySetName Bookmarklet

Create a normal bookmark in you browser and replace its url with the following code:

javascript: etn=prompt("SchemaName?","account");var xhr=new XMLHttpRequest;xhr.open("GET","/api/data/v9.0/EntityDefinitions(LogicalName='"+etn+"')?$select=EntitySetName",!0),xhr.onload=function(t){4===xhr.readyState&&200===xhr.status&&prompt("EntitySetName:",JSON.parse(xhr.responseText).EntitySetName)},xhr.send();

You can use it at any place inside of D365 CE. You only need to write in the SchemaName of an entity (table) and it answers you with the EntitySetName from the EntityDefinitions.

Limit ClickDimensions star rating

Posted in ClickDimensions, Dynamics 365, and Power Platform

The ClickDimensions star rating has by default 10 options .

Here is how you can limit the star rating options only with CSS if you don’t need all options.

Limit star rating

Limit all star ratings in a survey

Paste the following in the code editor of the survey and customize the number of stars you want to have to limit the options all star ratings.

:root {
  --number-of-stars: 3;
}

#clickdimensionsForm div[id^='stars-wrapper'] {
  max-width: calc(207px - (191px - (var(--number-of-stars) * 16px)))
}

Limit a single star rating in a survey

Paste the following in the code editor of the survey and customize the number of stars you want to have and use the ID of the desired star rating to limit the options.

:root {
  --number-of-stars: 3;
}

#stars-wrapper-f_f6e0ec97b339e911a838000d3ab0d281 {
  max-width: calc(207px - (191px - (var(--number-of-stars) * 16px)))
}
Find ID to limit star rating
The ID of a survey question will change if you do not transport them with the ClickDimensions Export/Import