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:
```
function 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]
}
}
You 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.
