Sync Office 365 users with a MailChimp List

Sync Office 365 Users With MailChimp

Office 365 services change at a rapid pace, with new features added each week. Getting the word out to your users about these changes can be difficult, especially if not all changes and features will apply to everyone.

To help solve this problem, we’re using this PowerShell script. It collects user and license info from all customer Office 365 tenants, and signs them up to a MailChimp List. In this case, the list is called ‘Office 365 Tips’.

It also creates list segments based on the licenses that your users have, allowing you to filter your user base and send out communications when a new feature is released for that license type.

For example, you can let all the Project Online users know when something cool has been released for Project Online, or tell the Business Premium users to get onto Microsoft Teams, or let the Exchange Online Plan 1 users know that they’re missing out on Microsoft Teams and they should upgrade 🙂

You can run the script before each mailout to ensure that it stays in sync. If a user’s details change, they’ll be updated in MailChimp. If a MailChimp list member is no longer licensed in Office 365, they’ll be unsubscribed from the list

Prerequisites

  • To set this up, you’ll need a mailchimp account and delegated access to your customers’ tenants.
  • You’ll also need permission to contact these users. If you’re a managed service provider, this might be an inclusion for your customer contracts.
  • If you’re running the single Office 365 tenant version of this script, you’ll need the admin details for that tenant.

Running the script to sync customers’ Office 365 users with MailChimp

  1. Sign into MailChimp
  2. Click your account name on the top left, then click AccountMailChimp Account Settings
  3. Click Extras, then API keys.MailChimp Extras API Keys
  4. Click Create A KeyCreate MailChimp API Key
  5. Copy the API Key value and paste it into Notepad for later. Take note of the data center value at the end. In our case it’s us14.Create New API Key In MailChimp
  6. You should also see it in the URL of the current page.Data Center Location In MailChimp URL
  7. Copy and paste the script at the bottom of this page into Visual Studio Code.
  8. Save the file as a Powershell (.ps1) file
  9. Update the script with a username and apiKey. The username can actually be whatever you like, I’ve just set it to be gcits.Add MailChimp API Key To Script
  10. Update the baseUri variable with the dc details of your MailChimp accountAdd MailChimp Base URI To Script
  11. Update the listInfo object with the correct values for your business.Complete MailChimp List Details
  12. Press F5 to run the script and sign in using your Office 365 credentials with delegated access to your customers’ tenants. If you’re running the single tenant version of this script, just sign in with your Office 365 admin credentials.Sign Into Office 365
  13. The script will then create a MailChimp list with the required merge fields and collect the information on your licensed users. It’ll use their license info to build a collection of license types to create MailChimp List Segments.
  14. The Office 365 license segments are then added to the MailChimp list, and the list is checked for existing members. All Office 365 users that don’t exist in the list are added to a collection for upload in batches of 100. Adding Users Office 365 To MailChimp List
  15. If existing user details have changed, the users are added to a collection for upload in batches of 100 too.
  16. If an existing MailChimp list member is no longer licensed in Office 365, they’ll be unsubscribed from the list.Unsubscribing Users From MailChimp List
  17. Once completed, you can switch over to MailChimp and see your new list.View New MailChimp List
  18. Open the list to view your Office 365 user information.MailChimp Users
  19. All license types will be added as segments that you can use to filter and contact your users.Office 365 License Segments In MailChimp List

Download scripts to sync your own or customers’ Office 365 users with a MailChimp List

The on-page script below is for Microsoft Partners who manage multiple Office 365 tenants for their customers. If you’d like to run this script against a single tenant, you can download first script below. Just remember to save it as a PowerShell (.ps1) file first.

Complete script to sync customers’ Office 365 users with a MailChimp list

$user = "anythingGoesHere"
$apiKey = "APIKeyGoesHere"
$pair = "${user}:${apiKey}"
$bytes = [System.Text.Encoding]::ASCII.GetBytes($pair)
$base64 = [System.Convert]::ToBase64String($bytes)
$basicAuthValue = "Basic $base64"
$Headers = @{
    Authorization = $basicAuthValue 
}
$baseUri = "https://[DC].api.mailchimp.com"
$listInfo = @{
    listName            = "Office 365 Tips"
    company             = "GCITS"
    address1            = "38 Myeerimba Parade"
    address2            = " "
    city                = "Tweed Heads"
    state               = "NSW"
    zip                 = "2485"
    country             = "Australia"
    phone               = "+61755999888"
    permission_reminder = "You are recieving this email because you are a client of GCITS"
    from_name           = "Elliot Munro from GCITS"
    from_email          = "elliot@gcits.com"
    subject             = " "
    language            = "en"
}
$Office365members = @()
Connect-MsolService

function Ensure-MailChimpList ($ListName) {
    $lists = Invoke-RestMethod -URI $baseUri/3.0/lists?offset=0"&"count=100 -Method Get -Headers $Headers
    if ($lists.lists.name -notcontains $ListName) {
        $list = New-MailChimpList -ListInfo $listInfo
        return $list
    }
    else {
        $list = $lists.lists | Where-object {$_.name -contains $listName}
        return $list
    }
}


function New-MailChimpList ($ListInfo) {
    $listBody = @{
        name                = $listInfo.listName
        contact             = @{
            company  = $listInfo.company
            address1 = $listInfo.address1
            address2 = $listInfo.address2
            city     = $listInfo.city
            state    = $listInfo.state
            zip      = $listInfo.zip
            country  = $listInfo.country
            phone    = $listInfo.phone
        }
        permission_reminder = $listInfo.permission_reminder
        campaign_defaults   = @{
            from_name  = $listInfo.from_name
            from_email = $listInfo.from_email
            subject    = $listInfo.subject
            language   = $listInfo.language
        }
        email_type_option   = $false
    }

    $listBody = $listBody | ConvertTo-Json

    $newList = Invoke-RestMethod -URI $baseUri/3.0/lists -Method POST -Headers $Headers -Body $listBody
    return $newList
}

function Get-MailChimpListSegments {
    $segments = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/segments?offset=0"&"count=200 -Method Get -Headers $Headers
    return $segments
}

function Get-MailChimpListMergeFields {
    $mergeFields = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/merge-fields -Method Get -Headers $Headers
    return $mergeFields
}

function New-MailChimpListMergeField ($Name, $Type, $Tag) {
    $merge_field = @{
        name = $Name
        type = $Type
        tag  = $Tag
    }
    $merge_field = $merge_field | ConvertTo-Json
    Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/merge-fields -Method POST -Headers $Headers -Body $merge_field
}

function New-MailChimpListBatch ($MemberBatch) {

    $body = @{
        members         = $MemberBatch
        update_existing = $true
    }
    
    $body = $body | ConvertTo-Json -Depth 10

    $batchresult = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId -Method POST -Headers $Headers -Body $body
    return $batchresult
}
function New-MailChimpListMember ($email, $FirstName, $LastName, $Company, $Licenses) {
    
    $merge_fields = @{
        FNAME    = $FirstName
        LNAME    = $LastName
        COMPANY  = $Company
        LICENSES = $Licenses
    }

    
    $member = @{
        email_address = $email
        status        = "subscribed"
        merge_fields  = $merge_fields
    }
    return $member
}
function Update-MailChimpListMember ($ExistingMember, $Office365Member) {
    
    $merge_fields = @{
        FNAME    = $Office365Member.FirstName
        LNAME    = $Office365Member.LastName
        COMPANY  = $Office365Member.Company
        LICENSES = $Office365Member.Licenses
    }

    
    $member = @{
        email_address = $Office365Member.Email
        status        = $existingMember.Status
        merge_fields  = $merge_fields
    }
    return $member
}

function Unsubscribe-MailChimpListMember ($ExistingMember) {

    $member = @{
        email_address = $ExistingMember.Email
        status        = "unsubscribed"
    }
    return $member

    #Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/members/$($existingMember.SubscriberHash) -Method PATCH -Headers $Headers -Body $member
}

function Get-ExistingMailChimpListMembers {
    $existingMembers = $null
    $existingmembers = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/members?offset=0"&"count=100 -Method Get -Headers $Headers
    for ($i = 100; $i -le $existingMembers.total_items; $i += 100) {
        $members = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/members?offset=$i"&"count=100 -Method Get -Headers $Headers
        $existingMembers.members += $members.members
    }
    return $existingMembers
}

function New-LicenseSegment ($SkuPartNumber) {
    $conditions = @()
    
    $conditionproperty = @{
        condition_type = "TextMerge"
        field          = "LICENSES"
        op             = "contains"
        value          = $SkuPartNumber.TrimStart("License: ")
    }
    $conditions += $conditionproperty
    
    $segment = @{
        name    = $SkuPartNumber
        options = @{
            match      = "any"
            conditions = $conditions
        }
    }
    
    $segment = $segment | ConvertTo-Json -Depth 10
    
    $newSegment = Invoke-RestMethod -URI $baseUri/3.0/lists/$listId/segments -Method POST -Headers $Headers -Body $segment
    return $newSegment
}

function Create-ExistingMemberCollection ($ExistingMembers) {
    
    $existingMemberCollection = @()
    foreach ($existingmember in $existingmembers) {
        
        $memberObject = New-Object PSObject -Property @{
            Licenses       = $existingmember.merge_fields.LICENSES
            FirstName      = $existingmember.merge_fields.FNAME
            LastName       = $existingmember.merge_fields.LNAME
            Company        = $existingmember.merge_fields.COMPANY
            Email          = $existingmember.email_address
            SubscriberHash = $existingmember.id
            Status         = $existingmember.status
        }
        $existingMemberCollection += $memberObject
    }
    

    return $existingMemberCollection
}

function Create-UploadBatch ($collection) {
    # Upload the collection in batches of 100
    $counter = [pscustomobject] @{ Value = 0 }
    $batchsize = 100
    $batches = $collection | Group-Object -Property { [math]::Floor($counter.Value++ / $batchSize) }
    foreach ($batch in $batches) { New-MailChimpListBatch -MemberBatch $batch.Group}
}


# Check whether list exists and create it if it doesn't
$listID = (Ensure-MailChimpList -ListName $listInfo.listName).id

# Check for current merge fields in list
Write-Host "Retrieving Merge Fields"
$mergeFields = Get-MailChimpListMergeFields
$updateSegments = $false

$requiredMergeFields = "Company", "Licenses"
foreach ($requiredMergeField in $requiredMergeFields) {
    if (!$mergeFields.merge_fields.Name.contains($requiredMergeField)) {
        Write-Host "Creating Merge Field: $requiredMergeField" -ForegroundColor Yellow
        $newMergeField = New-MailChimpListMergeField -Name $requiredMergeField -Type text -Tag $requiredMergeField.ToUpper()
    }
}


# Retrieve existing segments, then filter by license related segments
$existingSegments = Get-MailChimpListSegments
$existingLicenseSegments = $existingSegments | Where-Object {$_.segments.name -match "License: "}
$requiredSegments = @()

# Get all customers from Office 365. Add a 'Where-Object {$_.name -match "CustomerName" -or $_.name -notmatch "CustomerName"}' filter here to include or exclude specific customers.
$customers = Get-MsolPartnerContract

# Create collection containing required info for all users in all tenants
foreach ($customer in $customers) {
    Write-Host "Retrieving users and license info for $($customer.name)" -ForegroundColor Blue
    $users = $null
    $users = Get-MsolUser -TenantId $customer.TenantId | Where-Object {$_.islicensed}
    $company = (Get-MsolCompanyInformation -TenantId $customer.TenantId).DisplayName
    foreach ($user in $users) {
        [string[]]$userLicenses = $null
        foreach ($license in $user.Licenses.AccountSku.SkuPartNumber) {
            $userLicenses += "'$license'"
        }
        $userLicensesString = $userLicenses -join ","
        

        # Check if these licenses already have segments created or queued
        foreach ($license in $userlicenses) {
            $expectedSegmentName = "License: $license"
            if ($existingLicenseSegments.segments.Name -notcontains $expectedSegmentName -and $requiredSegments -notcontains $expectedSegmentName) {
                $requiredSegments += $expectedSegmentName
                Write-Host "Adding $expectedSegmentName to required segments list. Update pending." -ForegroundColor Yellow
                $updateSegments = $true
            }
        }

        $firstName = $user.FirstName
        if (!$user.FirstName) {
            $firstName = $user.DisplayName.Split(" ")[0]
        }
        $lastName = $user.LastName
        if (!$user.lastName -and !$user.firstname) {
            $lastName = $user.DisplayName.Split(" ") | Where-Object {$_ -notcontains $user.DisplayName.Split(" ")[0]}
            $lastName = $lastName -join " "
        }
        elseif (!$user.lastName -and $user.firstname) {
            $lastname = " "
        }
        elseif (!$user.LastName -and !($user.DisplayName.Split(" ") | Where-Object {$_ -notcontains $user.DisplayName.Split(" ")[0]})) {
            $lastname = " "
        }
        
        $email = $user.UserPrincipalName
        $memberObject = New-Object PSObject -Property @{
            Licenses  = $userLicensesString
            FirstName = $firstName
            LastName  = $lastName
            Company   = $company
            Email     = $email
        }
        $Office365members += $memberObject
    }
}
Write-Host "Retrieved $($office365Members.count) licensed users from $($customers.Count) customers" -ForegroundColor Green
# If an update is required, add new segments
if ($updateSegments) {
    Write-Host "New segments required: $requiredSegments" -ForegroundColor Green
    # Create new license segments

    if ($requiredSegments.count -gt 0) {
        foreach ($requiredSegment in $requiredSegments) {
            Write-Host "Creating new segment - $requiredSegment" -ForegroundColor Blue
            $newSegment = New-LicenseSegment -SkuPartNumber $requiredSegment
        }
    }
}

# Check if members exist in list
# Build list of existing members

Write-Host "Getting existing members"

$existingMembers = (Get-ExistingMailChimpListMembers).members

$newMemberCollection = @()

# Check if Office 365 users already exist in mailchimp. If they don't, add them to a collection for upload
foreach ($member in $Office365members) {
    if ($existingMembers.email_address -notcontains $member.Email) {
        Write-Host "Adding $($member.firstName) $($member.lastName) to new subscriber collection" -ForegroundColor Green
        $newMemberObject = New-MailChimpListMember -email $member.Email -FirstName $member.FirstName -LastName $member.LastName -Licenses $member.Licenses -Company $member.Company
        $newMemberCollection += $newMemberObject
    }
}

Create-UploadBatch -collection $newMemberCollection

# Retrieve updated list of existing members

$existingMembers = (Get-ExistingMailChimpListMembers).members
$existingMemberCollection = Create-ExistingMemberCollection -ExistingMembers $existingMembers

# Update users when details have changed.

$updatedMemberCollection = @()
foreach ($existingMember in $existingMemberCollection) {
    $office365Member = $null
    $office365Member = $Office365members | Where-Object {$_.Email -contains $existingMember.Email}
    if ($office365member) {
        if ($office365Member.FirstName -notmatch $existingMember.FirstName -or `
                $office365Member.LastName -notmatch $existingMember.LastName -or `
                $office365Member.Licenses -notmatch $existingMember.Licenses -or `
                $office365Member.Company -notmatch $existingMember.Company) {
            
            Write-Host "Updating record for $($existingMember.Email)" -ForegroundColor Yellow
            $updatedMemberObject = Update-MailChimpListMember -ExistingMember $existingMember -Office365Member $office365Member
            $updatedMemberCollection += $updatedMemberObject
            
        }
    }
}

Create-UploadBatch ($updatedMemberCollection)

# Unsubscribe users who no longer exist in Office 365

$unsubscribedMemberCollection = @()
foreach ($existingMember in $existingMemberCollection) {
    if ($Office365members.Email -notcontains $existingmember.Email -and $existingmember.status -notcontains "unsubscribed") {
        Write-Host "$($existingmember.Email) is no longer licensed in Office 365, unsubscribing." -ForegroundColor Red
        $unsubscribedMemberObject = Unsubscribe-MailChimpListMember -ExistingMember $existingMember
        $unsubscribedMemberCollection += $unsubscribedMemberObject 
    }
}

Create-UploadBatch ($unsubscribedMemberCollection)                                                                                                                                                                                                                                                                                                                                                                                                                                                       

Was this article helpful?

Related Articles