Question

Get counts of various events per use email in one JQL query

  • 29 July 2020
  • 1 reply
  • 243 views

Hey there,

I’m having some issues counting different types of events within one JQL query. My goal is to have an output with a bunch of columns with the user properties (email, country etc) concatenated with columns counting the total number of specific events each user did. It seems to mostly work, except the mixpanel.reduce.sum() function seems to be returning 0 for each column. If I use a .count() it works, but then I’m unable to filter by the even name.

 

Here’s what I have so far:

function main() {
var joinedTable = join(
Events({
from_date: "2019-10-01",
to_date: "2020-02-28",
}),
People(),
{
type:'inner'
}
)

return (
joinedTable.groupBy(["user.properties.$email","user.properties.$last_seen","user.properties.$country_code","user.properties.$region","user.properties.$city","user.properties.first_seen"], [ mixpanel.reducer.sum(event => event.name == "platform-login"? 1: 0), mixpanel.reducer.sum(event => event.name == "platform-analysis"? 1: 0), mixpanel.reducer.sum(event => event.name == "demographics-open"? 1: 0)])

.map(function(tuple){
return {
"email": tuple.key[0],
"first seen": tuple.key[5],
"last seen": tuple.key[1],
"country": tuple.key[2],
"region": tuple.key[3],
"city":tuple.key[4],

"platform-login count": tuple.value[0],
"platform-analysis count": tuple.value[1],
"demographics-open count": tuple.value[2]
}
})
);
}

 


1 reply

Badge

Hi!

 

There’s a small change that will fix the reducer functions here.  Since you’re looking at a joined table, you’ll need to take that into account within the reducer function as well:

mixpanel.reducer.sum(table => table.event.name == "platform-login"? 1: 0)

 

Hope this helps!

Reply


Mixpanel