Leads Data Sync

Syncs Marketing Cloud events and data models for Leads, using Lambda and SQS

AWS Flow

• HOW DO WE HANDLE MULTPLE BUs?

• sqs queue for processing events (and upserting related models) clicks-to-process

• sqs queue for events that need to be flagged as processed clicks-processed

• lamba that runs on an interval to process events

  • load events from data extension
  • pushed events to processing queue

• lamba that triggers off the clicks-to-process queue

  • upserts models and click events

• lamba that listens to the clicks-processed queue

  • update data extension rows to mark the events as processed

To dos

• add queue trigger on time interval
• deploy leads graph and leads manage
• handle final event sync from last event date
• send test email using new format, and process clicks
• add click log external key and object id env vars
• ensure events will work with current data model
• disable click redirect URLs
• fix email click data (remove sub+send) and change index back
• determine how to handle url acknowledgements
• determine how to handle EmailSendUrl models
• add lt.ack to new link tracking
• combine n with $size: guids in report processing
• add day, send, url, sub unique index (with filter on sub + send) on email-click-events
• add send and sub values to all previous clicks
• update email-click-events unique index to use partial filter exp on job, usr
• update category upsert to use parentId
• ad request id pagination support to queue events
• update HTML link tracking to use new URL format
• need to determine how to handle multiple BUs
• links that do not qualify (missing ack and urlId) will get stuck - need to mark as processed
• need to contact erik to fix primary key of data exteionsion due to update errors
• for now mark bad processed updates in mongo and exclude later
• the sql query for the click log does not account for all email types!!! based on name

Event Modeling

// 9.1 million, so far
// largest `n` is 770
const newEvent = {
  _id: ObjectId(),
  day: ISODate("2020-09-04T00:00:00.000-05:00"),
  job: ObjectId("5f47eddbc46f3dfbd6c7750c"),
  url: ObjectId("5f47e1c107809f52ef34357c"),
  usr: ObjectId("5e73b74e86dc47a32b5c0199"),
  n: 3,
  // correspond to each unique event
  guids: [],
  last: ISODate("2020-09-04T08:35:29.919-05:00"),
};