Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kylekerrigan
Frequent Visitor

Mailchimp API; URL with Dynamic ID

Longtime forum reader, first-time forum poster… these forums have helped me out so much in the last 2 years.

My objective: create a data model with "Click details" for each "Campaign" by making iterative calls to Mailchimp's API.

 

Mailchimp's Marketing API documentation prescribes this URL "https://{server}.api.mailchimp.com/3.0/reports/{campaign_id}/click-details?" for obtaining the data I need for each Campaign.

To obtain a list of {campaign_id}, I can use this URL and query: "https://{server}.api.mailchimp.com/3.0/campaigns?count=500&offset=0"

let
Source = Json.Document(Web.Contents("https://{server}.api.mailchimp.com/3.0/campaigns?count=500&offset=0")),
campaigns = Source[campaigns],
#"Converted to Table" = Table.FromList(campaigns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"ID Table" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"campaign_id"})
in
#"ID Table"

 

My attempts:

1) Referencing this forum post ("How to pass a value from one table iteratively to another API request in power bi"), I used the above query ("ID Table") and tried this:

let
Source = campaign_list_test,
#"CampaignID" = Source[campaign_id],

QueryAPI = (campaign_id)=>

let
Source1 = Json.Document(Web.Contents("https://[server}.api.mailchimp.com/3.0/reports/"& campaign_id & "/click-details?")),
urls_clicked = Source1[urls_clicked]

in
urls_clicked,

AddColumn = Table.AddColumn(Source,"NewColumnAPI", each QueryAPI(#"CampaignID"))
in
AddColumn

// I receive an error.

example1.png"Expression.Error: We cannot apply operator & to types Text and List."

 

2) Referencing this forum post  ("Nested API Calls") and reading the BI Accountant's blog on webscrapping multiple pages, I attempted to transform the query to a function, but I custom function generated the same data as the {campaign_id} referenced in the parameter she prescibes to create. That is, every Table has the same data.

example2.png

3) Referencing multiple forum posts, I attempted to create a Custom Column to reference the {campaign_id}. No luck.

example3.png example4.png

 

I welcome any help and advice someone can provide. Thanks in advance!

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Please see this video that covers this scenario.

(3) Power BI - Tales From The Front - REST APIs - YouTube

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3

RM7 -

Thanks for your reply! I figured out the pagination and transformations I would need a little while ago. PBI used to maintain connectors for Mailchimp and other services, but they ended some of those partnerships some time before my original post.

But, your timing impeccable: we purchased a Windsor.ai account just days before your reply, so that we could easily connect to Mailchimp, Meta, Meltwater, etc.  So far, it's been a great service.

 

Again, thanks for your reply.

 

KK

mahoneypat
Employee
Employee

Please see this video that covers this scenario.

(3) Power BI - Tales From The Front - REST APIs - YouTube

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat Thank you.  Excellent video.  Your pagination explanation is helpful, and I've already used it today on another REST API data source that I've struggled to iterate.  I could not get your Invoke Customer Function method to work for my data source; similar to the BI Accountant's approach (referenced above), the custom  returns the same Table of data for each [campaign_id].  However, your video still led me to the solution.

 

I found the problem and solved it two ways. I need my [campaign_id] values to be changed to type 'Text'.  (1) I changed it at the top of the column; (2) it worked when I used Text.From([campaign_id]) within the concatenation of the URL.

(1) example5.png (2)example6.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors