Question

How to sum a number property by unique property by unique user?

U
Badge

I have an event that goes off when a song is played, and it has the songId and the durationInSeconds. I want to a number of unique users that have played a unique song for more than 60s so effectively I want to calculate the sum of the durationInSeconds property across unique songIds and across unique Users.

How can I do this?

To give more context we are defining our active users as users that have play the same song for a cumulative amount 1 min, so hence why we want to have the sum of a single song per user calculated.

7 replies

M
Rank
Userlevel 2
Badge +1

Hi!  One quick pointer here 

 

You could find the number of users that have played a certain song for more than a minute by setting up a cohort like this: 

 

You could then save this cohort, and add it as a filter to any report.  Would this work to help answer your question? 

U
Badge

Hi!  One quick pointer here 

 

You could find the number of users that have played a certain song for more than a minute by setting up a cohort like this: 

 

You could then save this cohort, and add it as a filter to any report.  Would this work to help answer your question? 

Sort of but how do you make sure that it is 1 min total for the same song which could be different. In this example you have to call out a specific song but what I want this calculation for whichever song just that it is the same song that cumulatively adds up to min. Does that make sense?

 

i did try this example already but didn’t know how to make sure I didn’t filer by a certain song as I’m not trying to filter by a particular song just trying to filter by the same song across multiple events.  

U
Badge

@mattvanwinkle Any other ideas? This won’t work for me. 

U
Badge

@Jaz-CommunityManager is it possible to remove this as the answer? This suggestion doesn’t answer my question. I can’t use it. 

Jaz-CommunityManager
Rank
Userlevel 5
Badge +5

Hi @univoicer no problem, I’ll remove the answer. You might be able to query the song in insights report then filter by this cohort to see which song was most popular for at least 1 minute of listening?

 

Community Manager
U
Badge

What would help is if there was a way to make sure all events had the same property value set. Basically if there was an option in that cohort where you could select the value is “same” or something. You have a selection for property is set but that just means there is a property value in there which is always the case. 
 

is there a way I can do this via JQL?

U
Badge

@Jaz-CommunityManager @mattvanwinkle so it won’t help because the goal is I’m trying to obtain an adoption metric and that is active users / target users. For us active users is defined as a user playing the same song for more than 1 minute but not continuously. We are a language learning app through music so it is pretty important to know that they are sticking to the same song to learn. To us users that have listening to the same song for a total of 1min are closer to being retained. Or at least this is our hypothesis that we want to measure to see how true it is. 
 

i can use your idea to query a few songs to test out this metric but would be good to see this across the board regardless of the particular song. 
 

can I add another property somehow? I really didn’t want to send another mixpanel event because I feel I have all the information I need in this event. Just need a way to make sure a property stays the same value across events and then add another property up to match 60s. 

Reply


Community Terms of UseCookie settings