Solved

JQL: Iterate over one event collection to populate the return data of another

  • 11 April 2019
  • 1 reply
  • 106 views

Userlevel 2
Badge

I need to pull out the email property from one event and return it with the information of another. The thing that ties them together is that the distinct_id is the same for both.

For various reasons it is not possible to use People(). I've written the below JQL query that does not work, but illustrates what I would like to do.

In short, I would like to grab all the 'Install Attributed' events, find a 'Viewed Market Screen' event with the same distinct_id, pull out the $email property and return that along with the 'Install Attributed' event data.

Any ideas on how to accomplish, or if it is not possible?

var attributionParams = {
  from_date:'2019-04-09',
  to_date:'2019-04-10',
  event_selectors: [
    {event: 'Install Attributed'},
  ]
};
var sessionParams = {
  from_date:'2019-04-09',
  to_date:'2019-04-10',
  event_selectors: [
    {event: 'Viewed Market Screen'},
  ]
};
function main() {
  var attributionEvents = Events(attributionParams);
  return attributionEvents.map((e)=>{
    var id = e.distinct_id;
    var sessionEvents = Events(sessionParams).filter((s)=>{s.distinct_id == e.distinct_id});
    var email = sessionEvents.length > 0 ? sessionEvents[0].properties.$email:'unknown';
    return { email, event: e};
  });
}

icon

Best answer by evanmoss 15 April 2019, 21:44

Hi jsawyer,


You'll need to use a custom .groupByUser reducer to aggregate the data you want on a per-user basis, then use .map and .flatten afterwards to get the format right. If you use .groupByUser like this:


  .groupByUser(function(state, events) {
    state = state || {events: []};
    _.each(events, function(event) {
      if (event.name == 'Viewed Market Screen')
        state.email = event.properties.email; //or whatever your email property is called
      else if (event.name == 'Install Attributed')
        state.events.push({name: event.name, time: event.time});
    });
    return state;
  })


You'll end up with a collection where each item looks like this:

{
    "key": [
      "1563ee62-c5fe-4107-a927-fc05612e8470"
    ],
    "value": {
      "events": [
        {
          "name": "Complete Chat",
          "time": 1551409896000
        },
        {
          "name": "Complete Chat",
          "time": 1551409896000
        },
        {
          "name": "Complete Chat",
          "time": 1551409896000
        }
      ],
      "email": "hound.arrow@hotmail.com"
    }
  }


You can then use .map() and .flatten() to get a collection representing each event with the email property added in:


  .map(function(user) {
    return _.map(user.value.events, function(event) {
      event.email = user.value.email;
      return event;
    });
  })
  .flatten();


You can modify the .groupByUser to add more information for each event if you'd like.


Hope that helps.


Thanks,

Evan


View original

1 reply

Badge +1

Hi jsawyer,


You'll need to use a custom .groupByUser reducer to aggregate the data you want on a per-user basis, then use .map and .flatten afterwards to get the format right. If you use .groupByUser like this:


  .groupByUser(function(state, events) {
    state = state || {events: []};
    _.each(events, function(event) {
      if (event.name == 'Viewed Market Screen')
        state.email = event.properties.email; //or whatever your email property is called
      else if (event.name == 'Install Attributed')
        state.events.push({name: event.name, time: event.time});
    });
    return state;
  })


You'll end up with a collection where each item looks like this:

{
    "key": [
      "1563ee62-c5fe-4107-a927-fc05612e8470"
    ],
    "value": {
      "events": [
        {
          "name": "Complete Chat",
          "time": 1551409896000
        },
        {
          "name": "Complete Chat",
          "time": 1551409896000
        },
        {
          "name": "Complete Chat",
          "time": 1551409896000
        }
      ],
      "email": "hound.arrow@hotmail.com"
    }
  }


You can then use .map() and .flatten() to get a collection representing each event with the email property added in:


  .map(function(user) {
    return _.map(user.value.events, function(event) {
      event.email = user.value.email;
      return event;
    });
  })
  .flatten();


You can modify the .groupByUser to add more information for each event if you'd like.


Hope that helps.


Thanks,

Evan


Reply