Retrieve First Event by User with JQL: User ID and Timestamp Guide
I am trying to get the first event for each user using JQL. I want the user_id and timestamp for each user and event. How can I do it?
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. ThegroupByUser()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::LBKSLBKS```LBKS{ key:: [user's distinct_id], value: { user_id: [user's distinct_id], time: [timestamp of first event], event_name: [name of first event] } }```You can adjust the date range in theEvents()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.