Solved

JQL - Join and Map get all events

  • 23 October 2020
  • 3 replies
  • 52 views

Badge

Found the following to join and map but its only showing one event per user and I want all events per user but only pulling back certain columns. What do I need to change?

 

function main() {
  return join(
    Events({
      from_date: "2020-10-7",
      to_date: "2020-10-15",
    }),
    People(),

    {
      type:"inner",
      selectors:[{selector:
        '"&AID" in properties["$current_url"] or properties["revenue"] > 0'}]
    }
    )
     .groupByUser(mixpanel.reducer.any()) //Only picks up first event for user 
    
      .map(function(tuple){
        return{
          "name": tuple.value.event.name,
          "distinct_id": tuple.value.event.distinct_id,
          "time": tuple.value.event.time,
          "bundle": tuple.value.event.properties.bundle,
          "revenue": tuple.value.event.properties.revenue,
          "event.userProfileId": tuple.value.event.properties.UserProfileId,
          "user.distinct_id": tuple.value.user.distinct_id,
          "user.InitialCobrand": tuple.value.user.properties.InitialCobrand,
          "event.$current_url": tuple.value.event.properties.$current_url
          
        };
      });
    
  }
 

icon

Best answer by glencwillis 27 October 2020, 00:50

Hi Kelly,

The reason you are seeing only one event per user is due to the fact that you’re using a .groupByUser with the reducer.any() function, which will return a single value per user.

You will want to remove the .groupByUser line entirely, and remove the “.value” from each value within the dictionary as well.  This will then return the full dataset.

Glen

View original

3 replies

Badge

Hi Kelly,

The reason you are seeing only one event per user is due to the fact that you’re using a .groupByUser with the reducer.any() function, which will return a single value per user.

You will want to remove the .groupByUser line entirely, and remove the “.value” from each value within the dictionary as well.  This will then return the full dataset.

Glen

Badge

Thanks, that’s exactly what I needed!

Fixed query: 

//Join events with user data//
function main() {
  return join(
    Events({
      from_date: "2020-9-20",
      to_date: "2020-10-22",
    }),
    People(),

    {
      type:"inner",
      selectors:[{selector:
        '"&AID" in properties["$current_url"] or properties["revenue"] > 0'}]
    }
    )
  //Only pull back certain columns//
      .map(function(tuple){
        return{
          "name": tuple.event.name,
          "distinct_id": tuple.event.distinct_id,
          "time": tuple.event.time,
          "bundle": tuple.event.properties.bundle,
          "revenue": tuple.event.properties.revenue,
          "event.$current_url": tuple.event.properties.$current_url
          
        };
      });
    
  }

Userlevel 6
Badge +3

Thanks for letting us know @kelly! So glad that you got the query up and running. 

Reply