Retrieve First Event by User with JQL: User ID and Timestamp Guide

To get the first event for each user using JQL in Mixpanel, including the user_id and timestamp, you can use the groupByUser() transformation. Here's an example of how you can achieve this: LBKSfunction main() { return Events({ from_date: "2016-01-01", to_date: "2016-12-31" // Adjust date range as needed }) .groupByUser(function(state, events) { if (!state || events[0].time < state.time) { return { user_id: events[0].distinct_id, time: events[0].time, event_name: events[0].name }; } return state; }); }LBKSThis query does the following: 1. It fetches events for a specified date range. 2. The groupByUser() transformation groups events by user. 3. For each user, it keeps track of the earliest event, storing the user_id (as distinct_id), timestamp, and event name.The result will be a collection of objects, each representing a user's first event with the following structure: LBKS{ key: [user's distinct_id], value: { user_id: [user's distinct_id], time: [timestamp of first event], event_name: [name of first event] } }LBKSYou can adjust the date range in the Events() function to match your desired time period.<https://docs.mixpanel.com/docs/reports/apps/jql|JQL: Create custom queries using Javascript code> provides more details on using JQL and its transformations.Remember that JQL queries have certain limitations, such as a 2-minute timeout and a 5 GB limit on processed data per query. If you're dealing with a large dataset, you might need to consider alternative approaches or break your query into smaller parts.

kapa.ai is still learning and improving, please let me know how I did by reacting below.

ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

1 comment