You're right, manually collecting these email interaction details from the Customer Insights - Journeys UI is inefficient for any significant volume of emails. The good news is that this data is stored within Dataverse (the underlying data platform) and can be accessed programmatically or through reporting tools.
Here's a breakdown of how you can access this data and some potential approaches:
Understanding Where the Data is Stored:
The "Delivery and Interaction details" you see are aggregated views of various interaction events related to your sent marketing emails. These events are stored in several Dataverse tables. The key tables you'll be interested in are:
msdyncrm_emailopens (Email Opens): Records each time a recipient opens the email.
msdyncrm_emailclicks (Email Clicks): Records each time a recipient clicks on a link within the email.
msdyncrm_emaildeliveries (Email Deliveries): Records successful email deliveries to recipients.
msdyncrm_emailhardbounces (Email Hard Bounces): Records permanent delivery failures.
msdyncrm_emailsoftbounces (Email Soft Bounces): Records temporary delivery failures.
msdyncrm_emailunsubscribes (Email Unsubscribes): Records when recipients unsubscribe via the email link.
msdyncrm_emailsent (Email Sent): Records each attempt to send an email to a recipient.
msdyncrm_emailforwards (Email Forwards): Records when recipients forward the email.
msdyncrm_emailrenderings (Email Renderings): Records when the email content is rendered by the recipient's email client.
msdyncrm_emailspamcomplaints (Email Spam Complaints): Records when recipients mark the email as spam.
Key Linkage:
The crucial field that links these interaction records back to the specific sent marketing email is often a lookup field to the msdyncrm_marketingemail entity (Marketing Email). You'll need to filter these interaction tables based on the specific Marketing Email record(s) you're interested in.
Ways to Get This Data:
- Using Advanced Find (for basic, manual extraction):
- Go to Advanced Find in Dynamics 365.
- Look for each of the interaction entities listed above (e.g., "Email Opens").
- Use the "Regarding (Marketing Email)" field to filter records related to a specific Marketing Email. You'll need to know the name or ID of the email.
- You can then export the results to Excel.
- Repeat this process for each interaction type. This is still manual but allows you to get the raw data.
- Using FetchXML Builder (for more structured queries):
- The FetchXML Builder is a tool within the XrmToolBox (a free community tool) that allows you to build complex FetchXML queries against Dataverse.
- You can create FetchXML queries that:
- Retrieve the
msdyncrm_marketingemail entity.
- Use linked entities to join the interaction tables (e.g.,
msdyncrm_emailopens, msdyncrm_emailclicks).
- Aggregate the counts of each interaction type for each email.
- You can then export the FetchXML results to Excel or use it in other reporting tools.
- Using Power BI (for interactive dashboards and reporting):
- Connect Power BI to your Dynamics 365/Dataverse environment using the Dataverse connector.
- You can then:
- Bring in the
msdyncrm_marketingemail entity.
- Bring in the various interaction entities.
- Create relationships between
msdyncrm_marketingemail and the interaction entities using the "Regarding (Marketing Email)" lookup.
- Build measures and visualizations to display the delivery and interaction metrics per email. This is the most scalable and user-friendly approach for ongoing analysis.
- Using the Dynamics 365 Web API (for programmatic access):
- If you need to automate the extraction of this data for integration with other systems, you can use the Dynamics 365 Web API.
- You can query the
msdyncrm_marketingemail entity and use $expand to retrieve related interaction records. For example:
- Replace
[YourOrgURI] with your Dynamics 365 instance URL.
- Adjust the API version as needed.
- The
$select parameter specifies the fields from the Marketing Email entity you want.
- The
$expand parameter retrieves related records from the interaction entities and uses $count=true to get the number of records for each interaction type.
- Using Azure Data Lake Storage Gen2 (if configured):
- If you have configured Customer Insights - Journeys to export interaction data to Azure Data Lake Storage Gen2, you can access the raw data files there and use tools like Azure Synapse Analytics or Power BI to query and analyze it.
Getting Details Per Email:
All of the above methods allow you to filter or group the data by the msdyncrm_marketingemail entity, effectively giving you the analytics per email.
Getting Details Per Segment (Indirectly):
To get analytics per segment, you would need to link the msdyncrm_marketingemail entity to the msdyncrm_segment entity. The primary link here is usually through the Customer Journey. A Marketing Email is sent as part of a Customer Journey, and the Journey targets a specific Segment.
You would need to:
- Identify the Customer Journey that sent a particular Marketing Email. The
msdyncrm_marketingemail entity has a lookup field to the msdyncrm_customerjourney entity.
- The
msdyncrm_customerjourney entity has a lookup field to the msdyncrm_segment entity (Segment).
You can then join these entities in your queries or reports to analyze the email interaction data based on the segment the Journey targeted.
In Summary:
The data you see in the "Delivery and Interaction details" section is stored in various Dataverse interaction entities linked to the msdyncrm_marketingemail entity. You can access this data using:
- Advanced Find: For basic, manual extraction.
- FetchXML Builder: For more structured queries and exports.
- Power BI: For interactive dashboards and ongoing analysis.
- Dynamics 365 Web API: For programmatic access and integration.
- Azure Data Lake Storage Gen2: If configured for data export.
To get analytics per segment, you'll need to join the Marketing Email entity with the Customer Journey and then the Segment entities. Power BI is generally the most powerful and user-friendly tool for this type of multi-entity analysis and reporting.
Share
Report
6
All responses (
Answers (
