Connecting to MailChimp

Hi Anonymous  SilacGlasses 

The first thing you need to do is to create an API key from inside your Mailchimp account.  There are some instructions on how to do this here

https://mailchimp.com/developer/guides/marketing-api-quick-start/

Once you have that you can use this query to check that everything is set up correctly

let
    dc = "us1",
    api_url = "https://" & dc & ".api.mailchimp.com/3.0/ping",
    ClientID = "xxxxxxxx",
    apikey = "xxxxxxxxxxxxxxxxxxxx-us1",
    
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & apikey), BinaryEncoding.Base64),

    data= Json.Document(Web.Contents(api_url,
   [ 
     Headers = [#"Authorization"=EncodedCredentials]
   ]
   )
   )

in
    data

Obviously you have to insert your own API key into that query. 

Two things to note: the value for dc (data center) is particular to each Mailchimp account.  Mine happens to be us1, you need to make sure yours is entered.  You should see the dc value at the end of your API key.  Secondly, the value for ClientID is irrelevant - it can be any string.  MC just uses the API key to authenticate.

If everything is set up correctly, you should receive a response from Mailchimp that everything is chimpy!

You can now make authenticated requests to their API.  Exactly how you get what you want is set out in the API documentation here

https://mailchimp.com/developer/api/marketing/

https://mailchimp.com/developer/api/transactional/

But for example, if you want to get information on all the lists in your account you can use this query (note that the api_url is different to the first query)

let
    dc = "us1",
    api_url = "https://" & dc & ".api.mailchimp.com/3.0/",
    ClientID = "xxxxxxxx",
    apikey = "xxxxxxxxxxxxxxx-us1",
    
    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & apikey), BinaryEncoding.Base64),
    
    path = "lists/",

    data= Json.Document(Web.Contents(api_url,
   [ 
     RelativePath = path,
     Headers = [#"Authorization"=EncodedCredentials]
   ]
   )
   )

in
    data

This returns a record that can be drilled down into to give information such as this

Enjoy.

Phil

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.