I'm having trouble merging multiple HTTP calls into single, coherent objects

Questions
,
1

I’m having a bit of trouble structuring a workflow. I’m working with a Constant Contact API, and my goal is to find a specific email campaign and put the statistics for that campaign into a Google Sheet.

The way the API works is like this:

  • A call to get the campaign returns the following information:

    • campaign_id
    • name
    • other miscellaneous info I don’t need

  • To get the stats specific to a campaign, I call the stats API call with the above campaign_id. The response contains:

    • campaign_id
    • stats array with open, click, bounce, etc. info

  • To get link and bounce information, I first call the API with the campaign_id and get this information back:

    • campaign_id
    • campaign_activities array containing a campaign_activity_id

  • Then I call the specific API call for bounces links information with the campaign_activity_id as a parameter, and I get:

    • Bounce API:
      • campaign_activity_id
      • a tracking_activities array with:
        • campaign_activity_id
        • email_address
        • first_ and last_name
        • other stuff
    • Links API:
      • campaign_activity_id
      • a link_click_counts array with:
        • link_url
        • unique_clicks
        • other stuff

My problem is that in my Google Sheets, I want to put all that information in three sheets:

  • Email campaign stats
  • Email bounces information (email, name, and bounce reason of each email bounce)
  • Links (URL, number of clicks)
  • The date and name of the campaign on each line of each report (so the information associated with the first call)

I’m trying to take all of the information above and put end up with one object at the end of my workflow that contains all the information. I run this report once a week, and since we send more than one emial per week, I should end up with as many objects as there were email campaigns in the preceding week.

The problem is that I can’t figure out what combination of merge/code node/other will allow be to have a single run with multiple object that I can then parse one by one and put the information in the Google Sheet.

My flow looks a bit like this:

  • Find last week’s campigns
  • Get campaign details
  • For each campaign
    • Get sends report
    • Get bounce report
    • Get links report
  • Merge the data (merge node)

But when I merge, I always end up with multiple runs, some of which only contain campaign_activity_id, so I can’t insert the campaign name directly.

I’ve tried various combinations of append and combine, but I can never end up with something that looks like

{

  "campaigns": [
    {  
	  "campaign_id": "id",
	  "name": "name",
	  "links": [
		{
		  "link_url": "link",
		  "clicks": "clicks"
		},…
	  ],
	  "bouces": [
		{
		  "email": "email",
		  "bounce_reason": "reason"
		},…
	  ],
	},…
	]
}

(the JSON could be a bit wonky, here).

How should I approach this? I feel like I’m not taking the right strategy.

Thanks,

L

P.S. AS I was about to post I saw this: Merging http requests results in multiple subsequent executions I will take a look at it tomorrow, but maybe that’s what I’m supposed to do?