Sticky MPKNOWLEDGEDROP

Filter users in JQL by using customized cohorts

  • 22 January 2020
  • 0 replies
  • 240 views
Filter users in JQL by using customized cohorts
Userlevel 5
Badge +4

Every week, we will release tips to help you get the most out of Mixpanel. Want to see more? Click here to see other #mpknowledgedrop articles.

 

To get started, the not so great news is that cohorts are not supported directly in JQL. The parameter "filter_by_cohort" is an option available on our engage API but not part of what you can choose in the selector when requesting the data in JQL. 

 

However there are times when using cohorts is really helpful, so here are a few workarounds to define cohorts in JQL:

 

A. Depending on your query, you could just factor the cohort logic into your main query. This might or might not be feasible (easy to implement) depending on the cohort definition and date range. 

 

B. A slight variation of the suggestion above, although it is not documented, you can technically do an initial JQL request to get a set of users (imagine the cohort you need), and then make sure that your main JQL script only evaluates users in that previous set of IDs. To write this simple JQL script, we have shared a step by step guide on how to set this up below. 

 

1st step:

Let’s say we have a saved Cohort that we want to see in JQL. Our cohort example is made up of users that did App Open at least 1 time since July 17, 2019. This cohort will work to see in JQL since the definition and date range is fairly simple. e129NVUodeDKca2bi3rk4bEWvqsi_0aqRNRPkyUzDSswcupG5YZlE3taJmQypHHun_Asw4fLNDd-UGkvuGVjB-6OC0Fq-_B_PU80LXGg-GnparNQSsQ3-O0vhDSBdbtmdBiTnRSq

2nd step:

we need the logic for the JQL query. Below is an example of a JQL where you could query those users, and then run a separate query that only evaluates those users (even on a different time range): 

 

var today = new Date().toISOString().substr(0,10);



function main() {

  // --- start of the subquery to get matching users on the cohort

  // the query should return just a list of distinct IDs that match

  var sub = Events({

    from_date: '2019-07-17',

    to_date:   today,

    event_selectors: []

  }).groupByUser(mixpanel.reducer.null())

  // --- end of the subquery to get matching users on the cohort

  

  return join(

    Events({

      from_date: '2019-11-01',

      to_date:   '2019-11-01'

    }), sub, //including that subquery as part of the join for the query will run

  )

  .reduce(mixpanel.reducer.count())

}

The query first gets all qualifying users, and then, it runs a separate query of all events sent on Nov 1st for only those users. Think about it as 2 separate queries, the first one needing to return a set of distinct IDs as the key, and in the second, you can do the logic that you wanted to do for your own query. 

 

When we run the JQL query, we get the count below:

GJeH9LIMi_QWVHZu4Qg0nymClTtLCkfVcKiCVxL9GJqGqH5EYI-Xi_a1gpYdAsbupHyWpBAecuNhHMkX7-tkpAtw1fYaGNKS4i6D874d03WayDNAA19I8D1moQpb5ARRCFtXfh0T

 

If we run a similar query on Insights, this is what we get:

XSophfhqtNIAIZvkoweW8qJbP1EPHKDPjh6aVHnF6vRgKavRBRNIG0zE0mH-tOw_rE0f-Cu3UJM0_WYLdV4SNOOzBkNmpQuMv7n0XJRC2Wrl29-q4dBPEIu9Ud-Yt83c_V49Rroy

 

You can see the numbers from the JQL query match exactly in Insights when you do a similar query: 86,088.

 

Some things to keep in mind:

A note while running this query is JQL still has restrictions on memory and execution time (2 minutes). Running 2 big queries side by side might end up failing depending on the logic, so keep this in consideration.

 

Additionally, being able to do a query within a query and the param "behavior_name" is something we don’t have publicly available in our documentation since it makes it more likely that you can run into the limitations( like the 2-minute execution time boundary). 

 

Our full API documentation for JQL can be found here: https://mixpanel.com/help/reference/jql/api-reference

 

We hope this workaround helps you find value when you are wanting to run queries with Cohorts in JQL!


0 replies

Be the first to reply!

Reply