Update Email Activity Data

Automatically extract contacts from email recipients and create relationship links

Overview

The Update Email Activity Data operation processes email activities in your Dataverse environment and automatically links them to contacts and referrals based on the email addresses in the "To Recipients" field. This eliminates manual data entry and ensures emails are properly associated with the correct individuals and referrals.

The operation:

Extracts email addresses from the torecipients field
Finds matching contacts in the system
Links emails to contacts via many-to-many relationships
Links emails to associated referrals
Populates family group and family group member fields
Updates mag_relationships JSON for comprehensive tracking

When to Use This Operation

Use This Operation When:

✅ After bulk email imports from Outlook or other email systems
✅ Email activities show no relationships even though recipients are in the system
✅ You need to auto-link historical emails to contacts and referrals
✅ Emails were created by integrations without relationship data
✅ You want to make emails searchable by individual or referral

Don't Use This Operation When:

❌ Emails don't have torecipients populated
❌ Email addresses don't match contact email addresses in Dataverse
❌ You need to link emails to entities other than contacts and referrals
❌ Emails are already correctly linked

Quick Start

Example 1: Process All Emails

.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --UpdateEmailActivityData `
  --EnvironmentUrl https://myorg.crm6.dynamics.com `
  --ClientId a1b2c3d4-e5f6-7890-1234-567890abcdef `
  --Secret YourClientSecretHere

Expected Output:

[2024-11-17 15:30:15.123] Starting email activity data update...
[2024-11-17 15:30:15.456] Retrieving email activities with recipients...
[2024-11-17 15:30:18.789] Found 8,450 email activities to process
[2024-11-17 15:30:19.012] Processing email 1/8,450...
[2024-11-17 15:30:19.345] Extracted 2 email addresses from torecipients
[2024-11-17 15:30:19.678] Found 2 matching contacts
[2024-11-17 15:30:19.901] Created 2 email-contact associations
[2024-11-17 15:30:19.234] Created 1 email-referral association
[2024-11-17 15:30:20.567] Processing email 2/8,450...
... (continues)
[2024-11-17 15:42:35.890] Email activity data update completed
[2024-11-17 15:42:35.123] Total emails processed: 8,450
[2024-11-17 15:42:35.456] Total contacts linked: 15,230
[2024-11-17 15:42:35.789] Total referrals linked: 7,820

How It Works

Processing Steps

Retrieve Emails: Queries all email activities with non-null torecipients field
Extract Email Addresses: Parses the torecipients field to extract individual email addresses
Find Matching Contacts: For each email address:
- Searches contact.emailaddress1, emailaddress2, emailaddress3
- Retrieves contact ID, name, family group ID
- Excludes contacts with "QEC" in their name
Retrieve Referrals: For each matching contact, finds associated active referrals
Create Relationships:
- Creates records in mag_emailcontact (email-to-contact N:N table)
- Creates records in mag_emailreferral (email-to-referral N:N table)
Update Email Fields:
- Sets mag_familygroupmember to first matched contact
- Sets mag_familygroup to contact's family group
- Populates mag_familygroupmembers with all matched contacts
- Populates mag_inboundreferrals with all matched referrals
- Builds mag_relationships JSON with Individual and Referral data

Email Address Parsing

The operation handles various recipient formats:

Supported Formats:

john.doe@example.com
"John Doe" <john.doe@example.com>
John Doe (john.doe@example.com)
john.doe@example.com; jane.smith@example.com (semicolon-separated)
john.doe@example.com, jane.smith@example.com (comma-separated)

Extracted: john.doe@example.com, jane.smith@example.com

QEC Contact Exclusion

The operation automatically excludes Quality Evaluation Committee (QEC) contacts:

Contacts with "QEC" in their name are skipped
Emails to QEC contacts won't be linked

Note

This is a business rule to prevent QEC-related administrative emails from polluting contact/referral relationship data.

Single Referral Limitation

Important: The operation only processes emails where the matched contacts have exactly one active referral in common.

Example Scenarios:

✅ Linked:

john.doe@example.com → Contact A → Referral #123
Email linked to Contact A and Referral #123

❌ Not Linked:

john.doe@example.com → Contact A → Referral #123, Referral #456
Multiple referrals - email skipped for referral linking

Tip

If you need to handle multiple referrals per contact, you may need to manually link emails or customise the operation.

Parameters

Required Parameters

`--UpdateEmailActivityData`

Flag to enable the operation.

Example:

--UpdateEmailActivityData

`--EnvironmentUrl` (-e)

The URL of your Dataverse environment.

Example:

--EnvironmentUrl https://myorg.crm6.dynamics.com

`--ClientId` (-c)

The Azure AD application (client) ID for authentication.

Example:

--ClientId a1b2c3d4-e5f6-7890-1234-567890abcdef

Optional Parameters

`--Secret` (-s)

The client secret for authentication. If omitted, uses the default secret.

Example:

--Secret YourClientSecretHere

Caution

Never commit secrets to source control.

Real-World Scenarios

Scenario 1: Link Historical Emails After Outlook Sync

Situation: You've synchronised 5,000 emails from Outlook to Dataverse. The emails have recipient addresses but aren't linked to any contacts or referrals.

Solution:

.\WhanauTahi.Xpm.Tooling.CLI.exe datamanipulation `
  --UpdateEmailActivityData `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:DATAVERSE_SECRET

# Check the log for results
$logFile = Get-ChildItem ".\Logs\DataManipulation_*" | Sort-Object LastWriteTime -Descending | Select-Object -First 1
Get-Content $logFile | Select-String "Total contacts linked", "Total referrals linked"

Expected Outcome:

Emails now show related contacts in the "Related Contacts" grid
Emails now show related referrals in the "Related Referrals" grid
mag_familygroupmember field populated with primary contact
mag_relationships JSON contains Individual and Referral IDs
Emails are searchable from Contact and Referral views

Scenario 2: Automated Nightly Processing

Situation: Your organisation uses email tracking, and new emails sync to Dataverse throughout the day. You want to automatically link new emails every night.

Solution:

Create a scheduled PowerShell script:

# nightly-email-sync.ps1
$ErrorActionPreference = "Stop"

# Retrieve secret from Azure Key Vault or secure storage
$env:CLIENT_SECRET = Get-Secret -Name "DataverseClientSecret"

# Run email processing
& "C:\Tools\WhanauTahi.Xpm.Tooling.CLI.exe" datamanipulation `
  --UpdateEmailActivityData `
  -e https://prod.crm6.dynamics.com `
  -c a1b2c3d4-e5f6-7890-1234-567890abcdef `
  -s $env:CLIENT_SECRET

# Archive log
$logFile = Get-ChildItem ".\Logs\DataManipulation_*" | Sort-Object LastWriteTime -Descending | Select-Object -First 1
Copy-Item $logFile "\\fileserver\logs\email-sync-$(Get-Date -Format 'yyyy-MM-dd').log"

# Send summary email
$summary = Get-Content $logFile | Select-String "Total emails processed", "Total contacts linked", "Total referrals linked"
Send-MailMessage -To "admin@example.com" -Subject "Email Sync Complete" -Body ($summary -join "`n")

Schedule with Windows Task Scheduler:

Trigger: Daily at 11:00 PM
Action: Run PowerShell script
Conditions: Run only when computer is idle

Performance & Scalability

Processing Characteristics

Sequential Processing: Processes emails one at a time
Email Address Matching: Searches contact table for each email address
Relationship Creation: Creates N:N records in batches

Typical Performance

Emails to Process	Avg Recipients/Email	Estimated Time	Throughput
1,000	1-2	1-3 minutes	5-15 emails/sec
5,000	1-2	5-15 minutes	5-15 emails/sec
10,000	1-2	10-30 minutes	5-15 emails/sec

Note

Performance varies based on:

Number of recipients per email
Number of contacts in the system
Network latency
Dataverse API throttling

Optimisation Tips

1. Filter Emails Before Processing

If you only need to process recent emails, filter them manually first:

-- In Power Apps Advanced Find or SQL
SELECT emailid, torecipients FROM email 
WHERE createdon > '2024-10-01' 
AND torecipients IS NOT NULL
AND mag_familygroupmember IS NULL

Then process only those emails (requires code modification to accept email IDs as input).

2. Run During Off-Peak Hours

Schedule processing for evenings or weekends to avoid API throttling.

3. Monitor Progress

# In another PowerShell window while processing
Get-Content ".\Logs\DataManipulation_*" -Wait -Tail 20

Troubleshooting

Issue: "No matching contacts found"

Cause: Email addresses in torecipients don't match any contacts' email fields.

Solution:

Verify email addresses in Dataverse:

SELECT TOP 100 contactid, fullname, emailaddress1, emailaddress2, emailaddress3 
FROM contact 
WHERE emailaddress1 LIKE '%@example.com%'

Check for formatting differences:
- Extra spaces
- Uppercase vs lowercase
- Missing @ symbol
Ensure contacts have populated email fields before running operation

Issue: "QEC contacts excluded"

Cause: Contact name contains "QEC" and is automatically excluded.

Solution: This is intentional. QEC contacts are excluded by design. If you need to include them, the code needs modification.

Issue: "No referrals linked"

Cause: Contacts have multiple active referrals or no referrals.

Solution: The operation only links when there's exactly one active referral per contact. Review referral data:

SELECT contactid, COUNT(*) AS ReferralCount
FROM mag_referral
WHERE statecode = 0  -- Active
GROUP BY contactid
HAVING COUNT(*) > 1

Issue: "torecipients field is empty"

Cause: Email activities don't have recipient data.

Solution: Ensure emails are properly synchronised from Outlook or other email systems with recipient information intact.

Output & Logs

Log File Location

Logs\DataManipulation_2024-11-17_15-30-15-123.log

Log File Contents

[2024-11-17 15:30:15.123] Starting email activity data update...
[2024-11-17 15:30:15.456] Retrieving email activities with recipients...
[2024-11-17 15:30:18.789] Found 8,450 email activities to process
[2024-11-17 15:30:19.012] Processing email 12345678-1234-1234-1234-123456789012
[2024-11-17 15:30:19.345] To Recipients: john.doe@example.com; jane.smith@example.com
[2024-11-17 15:30:19.678] Extracted email addresses: john.doe@example.com, jane.smith@example.com
[2024-11-17 15:30:19.901] Found 2 matching contacts
[2024-11-17 15:30:19.234] Contact: John Doe (23456789-2345-2345-2345-234567890123)
[2024-11-17 15:30:19.567] Contact: Jane Smith (34567890-3456-3456-3456-345678901234)
[2024-11-17 15:30:19.890] Creating mag_emailcontact associations...
[2024-11-17 15:30:19.123] Created 2 email-contact associations
[2024-11-17 15:30:19.456] Retrieving referrals for contacts...
[2024-11-17 15:30:19.789] Found 1 common referral
[2024-11-17 15:30:19.012] Creating mag_emailreferral association...
[2024-11-17 15:30:19.345] Created 1 email-referral association
[2024-11-17 15:30:19.678] Updating email fields...
[2024-11-17 15:30:19.901] Email updated successfully
... (continues for all emails)
[2024-11-17 15:42:35.890] Email activity data update completed
[2024-11-17 15:42:35.123] Total emails processed: 8,450
[2024-11-17 15:42:35.456] Total contacts linked: 15,230
[2024-11-17 15:42:35.789] Total referrals linked: 7,820
[2024-11-17 15:42:35.012] Skipped (QEC contacts): 120
[2024-11-17 15:42:35.345] Skipped (multiple referrals): 340
[2024-11-17 15:42:35.678] Skipped (no matching contacts): 1,200

Understanding the Results

Success Indicators:

✅ "Total emails processed" matches expected count
✅ "Total contacts linked" shows reasonable number
✅ "Total referrals linked" is less than or equal to contacts linked
✅ Emails display relationships in Dataverse

Warning Signs:

⚠️ "Skipped (no matching contacts)" is high - Email addresses may not match
⚠️ "Skipped (multiple referrals)" is high - Contacts have multiple active referrals
⚠️ "Total referrals linked: 0" - No contacts have single active referrals

Update Email Activity Data

Overview

When to Use This Operation

Use This Operation When:

Don't Use This Operation When:

Quick Start

Example 1: Process All Emails

How It Works

Processing Steps

Email Address Parsing

QEC Contact Exclusion

Note

Single Referral Limitation

Tip

Parameters

Required Parameters

--UpdateEmailActivityData

--EnvironmentUrl (-e)

--ClientId (-c)

Optional Parameters

--Secret (-s)

Caution

Real-World Scenarios

Scenario 1: Link Historical Emails After Outlook Sync

Scenario 2: Automated Nightly Processing

Performance & Scalability

Processing Characteristics

Typical Performance

Note

Optimisation Tips

Troubleshooting

Issue: "No matching contacts found"

Issue: "QEC contacts excluded"

Issue: "No referrals linked"

Issue: "torecipients field is empty"

Output & Logs

Log File Location

Log File Contents

Understanding the Results

See Also

`--UpdateEmailActivityData`

`--EnvironmentUrl` (-e)

`--ClientId` (-c)

`--Secret` (-s)