Delete member tag

[mtuchi]

Background, context, and business value

See readme

The specific request, in as few words as possible

Job 1 - GET Campaign Members Deleted records in Salesforce created since the last run
Object: Campaign Members Deleted
Query:

SELECT Nome_da_Campanha__c, Nome_da_Tag__c, Nome_do_Membro_de_Campanha__c, Email__c FROM Deleted_Campaign_Member__c WHERE CreatedDate > {LastSyncTime}

Job 2 - Remove member tags from Mailchimp
POST /lists/{list_id}/members/{subscriber_hash}/tags
API endpoint: Add or remove member tags
Path parameters: list_id and subscriber_hash (string type)
Body parameters: tags (object[name;status = "inactive""])

Documentation:

• API Documentation: https://mailchimp.com/developer/marketing/api/list-member-tags/
• Data flow diagram: https://lucid.app/lucidchart/d5d0a341-19eb-4d1b-81b0-391097534e61/edit?page=0_0&invitationId=inv_1ba5d9c2-bdda-4d97-b3a9-3ae3bc5f14c1#
• Mapping Specs: https://docs.google.com/spreadsheets/d/1cIF5MY7-W4sfsiAiAMEE-t_MPns9VCEApciJrDgOxYc/edit#gid=684042192

Expected data volumes

See diagram here

[mtuchi]

The problem

The Mailchimp Marketing API has a limit of 10 concurrent connections for your API key.
The current implementation for Delete member tags will experience this limit if we have more than 10 records that needs to be deleted.

Proposed solution

We can solve the API limit issue by using batch operation for deleting members tags in bulk and use batch webhooks to received response for enqueue batch request

Delete member tags in bulk

• Trigger: Every 30 mins

• Job 1 GET Campaign Members Deleted records in Salesforce created since the last run

  • job code

   //Setup lastRunDatetime r
   fn(state=>state)
   
   // Get deleted campaign members from Salesforce
   query(...);
   
   // map deleted members to operation chunks
   fn((state) => {
   const deletedCampaignMembers = state.references[0]["records"];
   const operations = [];
   
   for (const member of deletedCampaignMembers) {
     operations.push({
       method: "POST",
       path: `/lists/a4e7ea0abc/members/${member.Email__c}/tags`,
       operation_id: `${member.Email__c}`, // Unique operation id, To make this unique we should append a number 
       # the operation_id is optional, but can be useful when you want to compare completed batch results 
       body: JSON.stringify({
         is_syncing: true,
         subscriber_hash: member.Email__c,
         tags: [{ name: member.Nome_da_Tag__c, status: "inactive" }],
       }),
     });
   }
   // Chunk operations to 500 records
   const chunkedOperations = chunk(operations, 500);
   return {
     ...state,
     references: [],
     chunkedOperations,
   };
  });
  

• Job 2 Remove member tags from Mailchimp

  • POST /batches
  • payload {"operations":[]}
  • job code

  each(
      'operationsBatch[*]',
      post('/batches', state => ({
        operations: state.data,
      }))
    );
  

  Confirm deleted member tags
  Trigger: Incoming webhook message on openfn.org

  • Job 1 - Look for failed operations and send an email to admin

Pros

• A more performant and robust workflow

Cons

• We have to design a workflow for listening completed batch webhooks
• Write job code for checking failed operations

Read more about running batch operations here - https://mailchimp.com/developer/marketing/guides/run-async-requests-batch-endpoint/#make-a-batch-operations-request