Connect Power BI to Mailchimp

A user guide to Analytics for MailChimp – Power BI App

In this user guide, you learn how to use the Power BI app, Analytics for Mailchimp (previously known as “Mailchimp Email Marketing”). The app is available on Microsoft AppSource here. You start with a 30-day trial and continue to a monthly or annual subscription plan here.

Analytics for Mailchimp is a Power BI app that connects to your Mailchimp account and analyzes the performance of your campaigns. An active Mailchimp account is needed to import the data. You can explore the various pages on the left sidebar to analyze different aspects of your campaign, including campaign performance, subscriber growth rate, tagged audiences, unsubscription reasons, member analytics, and much more. The app enables interactive views on crucial email marketing KPIs such as open rates, CTR (Clickthrough Rate), and CTOR (Click-to-Open Rate).


The app can act as a Mailchimp connector. Its dataset can be available from new Power BI reports and Excel workbooks. You can even build a composite model that connects to the dataset and brings additional data sources into the same Power BI report.

What’s new in version 1013

  • A new 30-day trial without refresh limits: During the trial, you can load all your Mailchimp data (unlike the previous version that loaded limited data only). You automatically get a 30-day trial after you start refreshing the app. There is no need to enter any email address. When the trial is over you can subscribe here and activate the app.
  • New page: Subscriber Growth Rates: Analyze how your audience grows with month-over-month and year-over-year calculations and an improved calendar table that includes weeks.
  • Faster refresh time for key tables:
  • Improved filtering logic: In the previous version, you needed to apply fixed dates to load data. In this version, you can load all data or keep the data fresh by selecting how many months to load.
  • Improved normalized contacts: We added custom fields for job title, company, address fields, age, and gender.


Important notice:

If you use a previous version of the app (Mailchimp Email Marketing), try the new version as a new app before you override the old version. The new version has no free mode. Unless you subscribe to the paid version, it will stop refreshing after 30 days. The price is as low as $4/month for small businesses with 1,000 contacts. We offer discounted prices for large audiences or annual subscriptions.

We do not collect your customer data and have no access to the Mailchimp API key. However, we do collect account-related information to support you with hassle-free trial activation and subscription management. The information includes:

  • Your Mailchimp account name and id (not your access key)
  • The total number of subscribers
  • Your Mailchimp user’s email address, first and last name
  • Your Mailchimp pricing plan type (“monthly”, “pay_as_you_go”, or “forever_free”)

Additional features in Analytics for Mailchimp:

  • Email Activity page and measures: The lowest grain of email activity is available with the resolution of active contacts and the campaigns they opened including the count of opens, clicks, and bounces per active contact and campaign.
  • Member Analysis: Analyze the audience by members’ open rates, CTR, and a 5-star rating.
  • Members: A page for export scenarios. All members including the merged fields: company, job title, gender, age, and activity fields count for opens, open rate, count of clicks, and click rate (CTR).
  • Members – Decomposition Tree: A page for member analysis using a decomposition tree.
  • High customizations using Power BI: The report can be edited in Power BI service or connected live from another report in Power BI Desktop, and Excel.

Here is a public version of the app using a real Mailchimp account with masked data:

Setting up the app

Login to your Power BI account and install the app on Microsoft AppSource. When your new app is ready, click Go to App. You will also notice the new Analytics for Mailchimp tile on the Apps view (And we hope you like the new logo).

On the app, you will notice the notification bar. Click Connect your data.

In Connect to Analytics for Mailchimp, enter the Domain Name and API Key from your Mailchimp account. In the next section, you will learn how to find these parameters on Mailchimp.

How to find the Domain Name parameter on Mailchimp?

Login to your Mailchimp page (https://login.mailchimp.com). Copy the preifx of the domain name that is followed by “.admin.mailchimp.com” from the URL address bar. For example, in the highlighted screenshot, I have us18 as the domain name prefix. Do not copy the entire URL or hostname, only the the prefix until the first dot.

How to get Api Key parameter on Mailchimp?

Follow the instructions on Mailchimp (as published here):

  1. Login to Mailchimp. Click your profile icon and choose Profile.
  2. Click the Extras drop-down then choose API keys.
  3. In the Your API Keys section, click Create A Key.
  4. Name your key. Be descriptive, so you know what app uses that key.
  5. Click Generate Key.
  6. Once we generate your key, click Copy Key to Clipboard. Save your key someplace secure–you won’t be able to see or copy it again. If you lose this key, you’ll need to generate a new key and update any integration that uses it.
  7. Click Done.

Please note that the role of the user who generated the API key determines access to each endpoint. To learn more about user level permissions, visit Manage User Levels in Your Account.

Our app invokes read-only API calls to your Mailchimp account and will access the following endpoints:

  • root (to read the account information)
  • lists
  • lists/{list_id}/members
  • campaigns
  • campaigns/{campaign_id}/content
  • reports/{campaign_id}/unsubscribed
  • reports/{campaign_id}/email-activity

It is sufficient to use the Mailchimp Manager role to create the API key. Our app needs to access the lists endpoint which cannot be accessible by Authors or Viewes.

A word about data security and privacy: The API key you enter in the Analytics for Mailchimp App do not leave your Semantic Model in Power BI, and we cannot have any access to that API key. As the owner of the Power BI workspace of the app, you are the only perosn with access to the workspace anb the Semantic Model that stores the API Key in its settings. Please make sure that the access to the Power BI workspace is resticted.

Parameters to parameters to connect the app

After entering your domain name prefix (e.g. us18) in the Domain Name box and the API Key in API Key box, scroll down to enter the other parameters.

The following table summarizes the parameters you’ll need to configure. You can change the parameters later in the Power BI workspace (More details on how to set the parameters in the workspace are desribed in the last section of this user guide).

Mask PII

The Mask PII parameter helps to mask PII data (personal identifiable information) of your contacts. When set to TRUE, all email addresses and other personal identifiable information of your Mailchimp audience members will be masked. When set to FALSE, you can access contacts information including email, name, and other fields. In that case, you can control who in your organization can access that ifnromation using the Power BI built-in access control features of workspaces and apps.

Subscription Email

Keep the Subsciption Email parameter as none or enter your email address. After the trial, enter the same email address you used in your app subscription. Learn more about the subscription options here. By entering your email address, you agree that DataChant will send you infrequent transactional emails related to the app. Emails may include trial related notification, failures to refresh due to changes or errors in the subscription, new features and bug fixes.

Import Content

The next parameter is Import Content. We recommend that you start with FALSE as the value to get your data from Mailchimp faster. The last two report pages of Analyics for Mailchimp (Content Analysis and Content Search) analyze the campaigns’ email body and calculate the number of words and links in the campaign emails. We are also using a WordCloud visual for the email body plain text. To turn on the content import needed for these two report pages, enter TRUE as the value for Import Content. Note: When set to TRUE, the dataset’s refresh time will significantly increase – especially when you have many campaigns – because the app extracts the body of the email per campaign. If you wish to use this feature, but need to reduce the refresh time, the next parameter, Campaigns Sent Last N Months, can help you to reduce the import to the campaigns that were sent in the last months defined.

Campaigns Sent Last N Months

Enter -1 to load all campaigns. The previous version of the app had a differenrt parameter, Campaigns Sent Since. It required that you to set a fixed date to ensure the app imports only recent campaigns since the defined date. We improved the app and the new parameter allows you to load all campaigns data by entering -1. However, if you have large volumnes of data on Mailchimp and you reach a timeout due to the Power BI service refresh limits, you can enter the number of months you want to load. For exmaple, you can enter 12 to define a rolling 12 months of campaign data. Do not use negative numbers to limit the months. Negative numbers will act as -1 and load all campaign data.

Note: This parameter impacts the imports of lists and members. Lists will be loaded only if they have active campaigns in the rolling period that is defined by this parameter. Unsubscription and email activity data are specific to campaigns will be affected as well by this rolling time period.

New Members Last N Months

Enter -1 to load all members. The previous version of the app had a differenrt parameter, Members Modified Since. It required that you to set a fixed date to ensure the app imports only members with status changes since the defined date. The new parameter, New Members Last N Months, allows you to load all members data by entering -1. However, if you have large volumnes of data on Mailchimp and you reach a timeout due to the Power BI service refresh limits, you can enter the number of months of new members to load. For exmaple, you can enter 3 to load only members that signed up in a rolling 3 months time period. Do not use negative numbers to limit the months. Negative numbers will act as -1 and load all campaign data.

Email Activity Last N Months

Mailchimps provides us access to Email Acitivy report API. It contains records with high resolution on your audience activity per contact and campaign. Each record in the Email Activity table in our app includes the campaign ID, member ID and the number of email opens, clicks, and bounces that the contact made in specific campaign. Importing this data can be slow. This parameter helps you to narrow down the amount of months of activity to import. We recommend that you start with a lower number. For exmaple, you can enter 3 to define a 3 months rolling period. After the refresh is successful you can consider to increase the number. If you don’t have high volume of activity, you can enter -1 to load all the email activity records. Negative numbers will act as -1 and load all the email activity records.

Note: The Email Activity Last N Months parameter can only impact the rolling months for the Email Activity table – not any other tables. For all other tables, the app will use the rolling months that were defined by the other Last N Months parameters. For example, defining 3 for Email Activity Last N Months and -1 for New Members Last N Months will allow you to keep a rolling 3 months for the email activity data and load all members data in other tables. The combination between these parameters can help you avoid timeouts when work with large volumn of Mailchimp data.

Email Activity rolling months are also impacted by Campaigns Sent Last N Months. The data in Email Activity table will be limited to campaigns that were sent in the rolling period that is defined by Campaigns Sent Last N Months.

Merge Tag parameters (optional)

If you use custom fields to track your members’ company name, job title, gender, age, or one other custom field, you can enter the names of your merge tags in the parameters below. Analytics for Mailchimp app will use the merge tags to find the relevant fields and will include the data in the report and corrsponding Members and Lists table. For example, if you have on Mailchimp a field with the merge tag COMPANY, when you enter COMPANY in the Company Merge Tag parameter of our app, it will identify members with company values and add them into the Company column of the app’s Members and Lists table. Now you will be able to include the Coompany field in existing or new visuals of the app.

Merge Tag parameterDescription
Company Merge TagThe merge tag you use for field used for the company of your contacts. For example COMPANY.
Job Title Merge TagThe merge tag you use for field used for the job title of your contacts. For example JOBTITLE.
Gender Merge TagThe merge tag you use for field used for the gender of your contacts. For example GENDER.
Age Merge TagThe merge tag you use for field used for the age of your contacts. For example AGE.
Other Merge TagIf you need to include another custom field, you can enter the merge tag code from Mailchimp that represents this field., for example: MERGE5. We will read the values of that custom field and load the information in the “Other Field” column of the Members and Lists table. To ignore this data you can add a merge tag you don’t use.

Note: The app handles address infromation and include their related fields in Members and Lists table. The address fields are recognized by identifying the ADDRESS merge tag. You don’t need to define it as a Merge Tag parameter.

License Key

The License Key parameter is needed after your 30 days trial is over. You can access the parameters Once you subscribe to one of the plans, you will receive an email with the license key. Enter that key in the License Key parameter.

Unsubscription Last N Months

Mailchimps provides us access to unsubsribes report API. It contains records with high resolution on your audience unsubcribe events per campaign and member. Each record in the Unsubscription table in our app includes the campaign ID, unsubscribing member ID and the reason they unsusbcribe. Importing this data can be slow. This parameter helps you to narrow down the amount of months of unsubscription events. We recommend that you start with a lower number. For exmaple, you can enter 3 to define a 3 months rolling period. After the refresh is successful you can consider to increase the number. If you don’t have high volume of campaigns, you can enter -1 to load all unsubsciption events. Negative numbers will act as -1 and load all the events.

Note: The Unsubscription Last N Months parameter can only impact the rolling months for the Unsubscription table – not any other tables. For all other tables, the app will use the rolling months that were defined by the other Last N Months parameters. For example, defining 3 for Unsubscription Last N Months and -1 for New Members Last N Months will allow you to keep a rolling 3 months for the unsubscription data and load all members data in other tables. The combination between these parameters can help you avoid timeouts when work with large volumn of Mailchimp data.

Unsubscription rolling months are also impacted by Campaigns Sent Last N Months. The data in will be limited to campaigns that were sent in the rolling period that is defined by Campaigns Sent Last N Months.

Connecting the app – Last two steps

After you enter all parameters, click Next. In the next dialog box, make sure that the Authentication method is set to Anonymous and that the Privacy level setting for this data source is Organizational as shown in the screenshot below. Then, click Sign in and continue.

In the final step, make sure that Authentication method is set to Anonymous and that Privacy level setting for this data source is Organizational as shown in the screenshot below. Then, click Sign in and connect.

First time refresh

You will now see that the app Refresh is now in progress notification. The refresh can take time if you use large audiences and campaigns. Once the refresh completes you will find your Mailchimp data loaded in the report instead of the demo version.

Explore the various pages on the left sidebar to analyze different aspects of your email campaigns.

Campaign Performance is the first page. It summarizes the performance of your campaigns. Subscriber Growth Rate page allows you to analyze the growth rates of your audience with month-over-month and year-over-year measurements. Performance Analysis page analyzes campaigns by their open rates, CTR and CTOR. Right-clicking on any box on the scatter charts, and selecting Drill through in the shortcut menu, and then click on Selected Campaign will navigate to the relevant campaign on a dedicated page. You can always go directly to the Campaign View page and select your preferred campaign for analysis using the Filters on the right pane

The Tagged Audience page allows you to analyze your Mailchimp audience members by their associated tags over time. On the Unsubscription Reasons page, you can analyze the main reasons why your audience unsubscribe to campaigns.

The app provides interactive exploration capabilities including right-clicking on a campaign, selecting Drill through in the shortcut menu, and then clicking the Selected Campaign will navigate to the relevant campaign page. In the drill-through page of the selected campaigns, you can click the back arrow button to go back to the previous page. You can always go directly to the Campaign View page and select your preferred campaign for analysis using the Filters on the right pane.

Member Analysis page helps you analyze your audience by members’ open rates, CTR, and 5-stars rating. Email Activity: allows you to analyze members’ activity by campaigns. Select a specific campaign or member and see their activity over time. This page is impacted by the Email Activity Last N Months parameter. The Members page can be used for export scenarios. You can your marketing data analsysts access to the app and they can export the members including address and the custom fields you use for company, job title, gender, age, or one other custom field you defined. The Members – Decomposition Tree page can be used for member analysis using a decomposition tree.

In the Content Analysis page, you can analyze the effect of subject length, the number of words, and the number of links on open rates, CTR, and CTOR. By default, you will have the last two scatter charts empty. To be able to load this data in the report refresh, enter TRUE as the value of Import Content parameter. As noted earlier, when set to TRUE, the refresh time will significantly increase as the app extracts more content per campaign.

Finally, on the Content Search page, you can search for specific keywords in the campaigns’ email bodies, and explore the relevant campaigns. When the Import Content parameter is FALSE, you will be able to search for keywords in the email subject line only.

Manage the app

When you subscribe to the Analyics for Mailchimp Power BI app you can take advantages of built-in Power BI features and controls. This app was built as a certified Microsoft Power BI template app. As the installer of this Power BI template app you can customize the report, set a schedule refresh, adjust the paramaters and publish the app to your own internal audience. Learn more about the capabilities of Power BI template apps here. Once you installed the app, it acts like any other app on Power BI and you can interanlly publish it. To learn about managing and sharing of apps on Power BI go here.

Setting the license key and changing the parameters

Entering the license key when the trial ends or changing the values of the parameters can be done from the workspace of the app. To find it, click Workspaces on Power BI left bar and select Analytics for Mailchimp (you might have renamed it during the installation, so you can look for the logo if you cannot find it). In the workspace page, select the Mailchimp semantic model, and click the elipsis.

In the Settings page, you can find the parameters section and the of the app.

Once you subscribe to the app, you can enter the subscription email address in the Subscription Email box of the Parameters section.

We hope you enjoy the 30 day trial. If you need any assistance, check out our support page.