Question

user email addresses sorted by last time they opened a campaign

  • 10 December 2021
  • 0 replies
  • 8 views

I am trying to export a list of users including their email address sorted by who most recently opened a campaign email.

The below code appears to work but when I do an export it throws an error.  This seems to be because eventually after about 1800 lines r.value.user.properties.$email stops returning the email address.  However when I look up the corresponding distinct_id it of course has an email. To me this is very mysterious.

If I swap the groupByUser reducer our for mixpanel.reducer.any() the export works but I am getting the first event for each user, not the last.

Pretty sure I am making some bad assumptions about how the max_by reducer works.

Any help would be appreciated.

 

function main() {
  return join(
    Events({
      from_date: "2020-12-06",
      to_date: "2021-12-09",
      event_selectors: [{event:'$campaign_open'}] 
    }),
    People(), 
    {
      type: 'inner',
      selectors:[{
        selector: 'user["$unsubscribed"] == undefined and properties["type"] == "email"'
      }]
    }
  )
  .groupByUser(mixpanel.reducer.max_by('event.properties.$mp_api_timestamp_ms'))
  .sortDesc('value.event.properties.$mp_api_timestamp_ms')
  .map(r => ({
    distinct_id: r.key,
    email: r.value.user.properties.$email,
    lastOpened: r.value.event.properties.$mp_api_timestamp_ms
  }));
}


0 replies

Be the first to reply!

Reply