How To Create Data Studio Dashboards With MailChimp Data

MailChimp one of the popular email service provider for small businesses if not the most popular email suite around. Its interface is easy to use, simple to set up and its integration with sites like WordPress, Drupal and Joomla and even Facebook has made it a popular option for many. However, the one thing MailChimp does lack is support for in-depth reporting and getting the data out of MailChimp to analyse it can become a manually taxing process, until now.

For more on free email suite options for small businesses check out our article 5 Free Email Suite Options

Thanks to the Julian at MeasureSchool and Ben Collins they've come up with an interesting workaround using the MailChimp API and Google Sheets to extract the data with an app script.

Resources needed to perform this work around

• A new Google Sheet
• The API script You can find the API code below in its Github repository

Step by Step Summary

To recap here is a step by step guide on how to extract the data

Pulling your email campaign data

1. Open new sheet
2. Add title
3. Rename sheet 'Campaign Analysis'
4. Setup sheet with headings
5. Open script editor
6. Rename project
7. Clear out boilerplate code
8. Add opening comments
9. Navigate to Mailchimp
10. Add API key to script
11. Add listID to script
12. Copy in first basic script
13. Run script
14. Review data in Logger to see what's there
15. Add section to print only the names
16. Review data in Logger to see what's there
17. Change script to put everything into array
18. Review data in Logger to see what's there
19. Print data to Sheet
20. Add query function and basic chart
21. Add menu to run from Sheet
22. Run onOpen to add menu to sheet
23. Schedule report to run 1/week

Pulling your database growth data

1. Add new sheet called 'List Growth'
2. Setup sheet with headers
3. Add new function list growth
4. Add to menu
5. Run script
6. Add query function and basic chart
7. Schedule report to run 1/month

Video Summary

If you're too lazy or too pressed for time to read you can check out the measure school video by Ben Collins

Uploading your sheet into Data Studios

Now that you have the data feeding into a Google sheet, you can either stop there and create your reports in Google Sheets or kick it up a notch and link it to Data Studios.

Tip! You will need to adjust the headers and not have duplicate fields as in the mailchimp API template sheet for Data Studios to read and accept your data fields

• Open up data studios
• Click data sources on the left-hand side
• Click the plus icon on the bottom right of your screen
• Select Google Sheets as your source
• Select the MailChimp API sheet you edited previously
• Select the tab you would like to use, ie Campaign analysis
• And click connect

If done correctly you will be taken to the dashboard creation panel and now you're on your way to creating a MailChimp dashboard in data studios. It may be a bit taxing to set up but once you have it running this reporting set up can save email marketers a lot of time and is well worth the good old college try.

Microsoft alternative with Power BI

If you're not attached to Google Data studios and would like an easier option, you can check out Power BI which is a Microsoft product that already provides Mailchimp and Google analytics integration via API. Making it an easier option to connect both sources and create valuable email dashboards.

Check out Power BI here

Contact us

If you want to know more about reporting dashboards don’t be shy we’re happy to assist. Simply contact us here

Source: Measureschool and benlcollins

Tags: Data Analysis, email marketing, Google Data Studio