Just kidding, Power Automate is always enough...if you're comfortable with code. I'm getting there, but there's still ways around for those times that I'm not. Sometimes, those workarounds involve stepping outside of Power Automate.
I have a client who is using MailChimp to send email blasts to leads in the days preceding and following a trade show. I've constructed a pretty cool Power BI report, and keep that posted as a tab in Microsoft Teams, but they won't go looking for it unless I remind them it's there. In today's challenge, I wanted to use Power Automate to send an alert or email with some details of the campaign and a link to the Power BI report.
The problem is that I wanted to trigger from a new Campaign being sent, then delay 24 hours, and then send the email/alert...and the MailChimp triggers for Power Automate are (currently), "When a Member has been added to a list" and "When a new list is created". There is nothing about when a new campaign is sent. From reading around a little and watching some videos that I don't quite understand, one option is to create some kind of a webhook that will trigger the flow using MailChimp's (very robust) API. That might be the next challenge, but I wanted to get this done and that meant using the tools I know now.
If you're unfamiliar with IFTTT or Zapier, both are great tools for the "maker", and both have some drawbacks. I keep both (along with Power Automate) in my problem-solving toolkit, so that when I run into a connector in PA / Flow that doesn't do what I need, I'll check the other tools to see what they offer. I've always had an idea that I could bridge the two, and this was a great opportunity to put that to the test. Let's dive in.
Zapier's interface is pretty simple and each connector walks you through a sort of 'wizard' to get where you want. In this case, I chose the MailChimp "New Campaign" trigger, and filtered on a status of "sent". This would let me grab all of the relevant campaign information and pass it to my 'bridge' app for Flow to pick up as a trigger.
On that, I was completely expecting to be able to use an Excel table; and while I am firmly on Team Microsoft, I have to quietly wonder why there are no triggers in Excel for Power Automate / Flow (Zapier currently has 4). Hopefully, there will be some in PA / Flow soon.
My backup was a SharePoint List, since I knew Power Automate could trigger off of a new item there. But Zapier does not offer a SharePoint connector yet, so I wouldn't be able to send the data. I had considered sending an email and parsing the body in the flow, but ultimately landed on posting a message to Teams since Zapier could do that, and Power Automate could trigger off of the new message. I created a message-drop channel and then started formatting the message in Zapier. My original intent was to follow this format and hope that I could Select the campaign ID field on the Flow side, but I found that it wasn't quite enough. It took some digging, but I found that if I added { "FieldValues": { and the end brackets around the body, it would act like a JSON object in the flow and let me parse out the fielded data just like anything else.
This pushes a message into Teams that looks like this, but don't worry, Power Automate does some pretty cool stuff with it.
Before we get into the flow side of things, we need to make sure we're set up on the MailChimp API. Since there are so many walk-through's on that, the short version is to go to your account page, click on "Extras", and then choose "API Keys". From that dashboard, you can create keys, as well as manage existing ones. Be sure to pay attention to the last part of the key (after the last dash), we'll need that for the URL later:
The plan for my Flow is to trigger off of the Teams post, wait 24 hours, the go grab a campaign report from MailChimp and then send that information along with a link to the full Power BI report to the stakeholders. Looking at the documentation for the MailChimp API, I can see the URL I want to GET the report is: https://usX.api.mailchimp.com/3.0/reports/ (where the usX is the data center assigned in your API key. For me, the actual URL will look like this: https://us12.api.mailchimp.com/3.0/reports/ with the campaign ID at the end because I want a single, specific report (more on that later).
On the Power Automate side, I have my Teams trigger for when a new post is added to my Bridge Channel. And then I initialize a string variable to store the message content and parse later.
This is how the Teams post looks inside the variable once we set it:
And because we wrapped everything with the "FieldValues" bit, Flow is treating it like a JSON object (apologies if I'm using the wrong terminology), and we can follow this with "Delay" and "Parse JSON" actions.
We'll take the output from a sample run to use as the schema (like we did here) and that will not only make it easy for us to grab the CampaignID, but we'll also have simple access to other fields, if needed.
After the Delay and Parse actions (shown below), we want to use the HTTP action with a GET (just like in the Yelp calls) to get the single MailChimp report.
It took me a long while to figure out how to manage the authorization on the API key for the GET. It seems each site handles things a little different, and all of the examples referenced curl, which I know nothing about...yet.
I finally figured out/found a link that showed the key "Authorization" along with the value of "apikey <API_Key>" and that helped me get where I needed to be.
As with anything else, it's important to not lose hope when learning something new. The answers are out there and it's just a matter of hunting them down with the right query.
You can see below that I added the dynamic value, CampaignID, to the end of the URI so MailChimp would only return the data for a single campaign.
MailChimp will return the report in a JSON format, so we'll use the same Parse JSON technique to get the fields we like, and drop those in the email:
Bonus Material: Because I know that MailChimp offers a sub-report with location data, and the URI is very close to the same thing...I don't have to re-type everything into a new HTTP GET action step, I can just use the Copy/Paste from within Flow and alter the second HTTP action.
Then Insert a New Step and click on "My clipboard"
Then edit the new HTTP action to grab the second set of data.
Here is the entire flow for your reference:
This one was fun for several different reasons, and I hope you find it useful in your endeavors.