Solved

JQL - Filter events for first 7 days after user property

  • 30 April 2020
  • 2 replies
  • 85 views

Badge

Hi, 

I’m pretty new working with JQL but I have made some headway.  I’m looking for a way to pull out events that occurred X days(in this case 7 days) after a Database Create Date which is a user property.  

I was able to put together some code that got me pretty close, I just can’t get to the point where I filter the time in JQL.  Right now I export this and work with the csv to get the information I would like.  Any help would be appreciated.

 

//Dates to look at for Events and DB Created date.
var params = {
  event_start_date: "2020-02-20",
  event_end_date: "2020-03-20",
  DB_start_date: "2020-02-20",
  DB_end_date: "2020-03-27"
}

// Show all events except for “$campaign_delivery” and “$campaign_open” for users with specified DB Create date
function main() {
  const events = join(
      Events({
        from_date: params.event_start_date,
        to_date: params.event_end_date
      }),
      People());
  return events.filter(function(tuple) {
    return tuple.event && tuple.user &&
        tuple.event.name !== “$campaign_delivery" &&
        tuple.event.name !== “$campaign_open" &&
        tuple.user.properties["Database Create Date"] >= new Date(params.DB_start_date) &&
        tuple.user.properties["Database Create Date"] <= new Date(params.DB_end_date) ;
  })

//Map events and properties

 .map(function(tuple) {
    const events = join(
      Events({
        from_date: "2020-02-20",
        to_date: "2020-03-27"
      }),
      People());
    return {
      'Event Name': tuple.event.name,
      'Company ID': tuple.user.properties["Company ID"],
      'Event Date': (new Date(tuple.event.time)).toDateString(),
      'Database Create Date': tuple.user.properties["Database Create Date"]
    };
  })

//Order to display
  _.groupBy("event.name", "user.properties.Company ID", mixpanel.numeric_bucket(mixpanel.daily_time_buckets), mixpanel.reducer.count())
}

icon

Best answer by ian 30 April 2020, 20:04

Hi @newman ,

 

You may just need to add a filter after the first join, prior to the mapping.  Try something along these lines:

//Dates to look at for Events and DB Created date.
var params = {
event_start_date: "2020-02-20",
event_end_date: "2020-03-20",
DB_start_date: "2020-02-20",
DB_end_date: "2020-03-27"
}

// Show all events except for “$campaign_delivery” and “$campaign_open” for users with specified DB Create date
function main() {
const events = join(
Events({
from_date: params.event_start_date,
to_date: params.event_end_date
}),
People());
return events.filter(function(tuple) {
return tuple.event && tuple.user &&
tuple.event.name !== "$campaign_delivery" &&
tuple.event.name !== "$campaign_open" &&
tuple.user.properties["Database Create Date"] >= new Date(params.DB_start_date) &&
tuple.user.properties["Database Create Date"] <= new Date(params.DB_end_date) ;
})
.filter(function(tuple) {
return tuple.event && tuple.user &&
tuple.event.time > new Date(tuple.user.properties.$created).getTime() && tuple.event.time < new Date(tuple.user.properties["Database Create Date"]).getTime() + 7 * 24 * 60 * 60 * 1000;
})
.map(tuple => {
return {
'Event Name': tuple.event.name,
'Company ID': tuple.user.properties["Company ID"],
'Event Date': new Date(tuple.event.time).toDateString(),
'Database Create Date': new Date(tuple.user.properties["Database Create Date"]).toDateString()
}
})

 

View original

2 replies

Userlevel 1
Badge +1

Hi @newman ,

 

You may just need to add a filter after the first join, prior to the mapping.  Try something along these lines:

//Dates to look at for Events and DB Created date.
var params = {
event_start_date: "2020-02-20",
event_end_date: "2020-03-20",
DB_start_date: "2020-02-20",
DB_end_date: "2020-03-27"
}

// Show all events except for “$campaign_delivery” and “$campaign_open” for users with specified DB Create date
function main() {
const events = join(
Events({
from_date: params.event_start_date,
to_date: params.event_end_date
}),
People());
return events.filter(function(tuple) {
return tuple.event && tuple.user &&
tuple.event.name !== "$campaign_delivery" &&
tuple.event.name !== "$campaign_open" &&
tuple.user.properties["Database Create Date"] >= new Date(params.DB_start_date) &&
tuple.user.properties["Database Create Date"] <= new Date(params.DB_end_date) ;
})
.filter(function(tuple) {
return tuple.event && tuple.user &&
tuple.event.time > new Date(tuple.user.properties.$created).getTime() && tuple.event.time < new Date(tuple.user.properties["Database Create Date"]).getTime() + 7 * 24 * 60 * 60 * 1000;
})
.map(tuple => {
return {
'Event Name': tuple.event.name,
'Company ID': tuple.user.properties["Company ID"],
'Event Date': new Date(tuple.event.time).toDateString(),
'Database Create Date': new Date(tuple.user.properties["Database Create Date"]).toDateString()
}
})

 

Badge

Thanks @ian! That seems to have done the trick.  I appreciate your help on this.  

Reply