How to get a report of latest event of user before they churned
How to get a report of latest event of user before they churned
How to get a report of latest event of user before they churned
Hi Yanisa S., Please stand by as I search the Mixpanel knowledge base for an AI-powered answer. I normally take about 15 seconds or so. In case AI cannot answer your question, feel free to reach out to Mixpanel support at mixpanel.com/get-support. If you have follow-up questions for this thread, please tag me again with your question: @kapa.ai <follow-up question> 👋
Feel free to ask a follow-up question once I've answered by mentioning me with @kapa.ai
To get a report of the latest event a user performed before they churned in Mixpanel, you can use a combination of cohort analysis and flows reporting: 1. Define a "Churned" Cohort: First, create a cohort of users who meet your definition of churn (e.g., users who have not performed a key event, such as a purchase, in the last 12 months). You can do this using the cohort builder in Mixpanel by setting filters that match your churn criteria. Once defined, save this cohort for use in reports. This approach is used by companies like Zalora to analyze churned users and their behaviors leading up to churn How Zalora supercharged its churn analytics.2. Analyze User Flows Leading to Churn: With your "churned" cohort, use the Flows report to visualize the sequence of events users performed before they entered the churned state. In the Flows report, you can anchor the flow on the churn-defining event (or the absence of a key event) and view the events that occurred immediately before this point. This will show you the most common latest events performed by users before they churned Build User Flows.3. View Individual User Profiles: For more granular analysis, you can look at individual user profiles within the churned cohort. The user profile page in Mixpanel allows you to see the event feed for a specific user, including the most recent events before they churned Define User Cohorts.Summary of Steps: - Create a "churned" cohort based on your churn definition. - Use the Flows report to see what events occurred immediately before churn. - Optionally, review individual user profiles for detailed event history.This approach enables you to identify the latest actions users took before churning, helping you understand potential causes and intervene to reduce future churn.
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.
Let me check the knowledge base for you again. One moment! 📚
Yes, you can query the latest event of each user in Mixpanel without manually viewing each user profile. The recommended approach is to use JQL (JavaScript Query Language), which allows you to write a custom query to fetch the most recent event for every user.Here’s an example JQL query that returns the latest event for each user: LBKSfunction main() { return Events({ from_date: "2023-01-01", to_date: "2023-12-31" }) .groupBy(["distinct_id"], mixpanel.reducer.max("time")) .map(function(item) { return { distinct_id: item.key[0], latest_event_time: item.value }; }); }LBKS- This query fetches events in the specified date range, groups them by user (distinct_id), and finds the event with the maximum timestamp (i.e., the latest event) for each user.
- You can further customize the output to include event names or properties as needed.Note: JQL is being deprecated and will be removed after December 31, 2025. For long-term solutions, consider using the Export API or Data Pipelines for similar raw data exports Mixpanel JQL Alternatives.If you need to combine event data with user profile properties, you can use the join() function in JQL to merge events and profiles Combining Events and Profiles.Summary:
You do not need to view each user profile manually; you can use JQL to programmatically retrieve the latest event for each user in one query.
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.
Let me check the knowledge base for you again. One moment! 📚
You can use JQL (JavaScript Query Language) in Mixpanel by writing custom queries and running them through the Mixpanel interface or via the JQL API endpoint. Specifically: - Mixpanel UI: JQL queries can be written and executed directly within the Mixpanel web application under the JQL section.
- JQL API: You can send your JQL scripts to the /jql API endpoint using tools like curl or programmatically via HTTP POST requests. The API accepts your script and optional parameters, and returns the results in JSON format. For example, you can use a curl command to post your script to [https://mixpanel.com/api/query/jql](https://mixpanel.com/api/query/jql) Custom JQL Query.Note: JQL is currently in maintenance mode and will be deprecated after December 31, 2025. Mixpanel recommends using alternative methods for new projects JQL: Create custom queries using Javascript code.
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.
Let me check the knowledge base for you again. One moment! 📚
To select the user ID and the latest event (created between 2025-01-01 and 2025-06-30) for users in a specific cohort using JQL, you need to: 1. Filter events within the date range.
2. Filter users who are in the specific cohort.
3. For each user, select their latest event.Assuming you have a user property (e.g., inCohort == "yes") that marks cohort membership (as is the case when you create a cohort via CSV or with a cohort property), you can use the following JQL: LBKSfunction main() { return Events({ from_date: "2025-01-01", to_date: "2025-06-30" }) .filter(function(event) { // Only include users in the cohort return event.properties.inCohort == "yes"; }) .groupBy(["distinct_id"], function(events) { // Find the latest event for each user var latest = events[0]; for (var i = 1; i < events.length; i++) { if (events[i].time > latest.time) { latest = events[i]; } } return latest; }) .map(function(item) { return { user_id: item.key[0], latest_event: item.value }; }); }LBKSNotes:
- Replace inCohort and "yes" with the actual property and value that identifies your cohort, as defined when you created the cohort (for example, via CSV upload or cohort builder) Creating a Cohort via a CSV.
- This script returns an array of objects, each containing the user ID and their latest event object within the specified date range.If your cohort is defined differently (e.g., by a different property or using a cohort export), adjust the filter accordingly.JQL is being deprecated after December 31, 2025. For long-term solutions, consider using the Export API or Data Pipelines JQL: Create custom queries using Javascript code.
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.
Let me check the knowledge base for you again. One moment! 📚
If you have already created a cohort named [Month 4] Churned users, you can use this cohort directly in your JQL query. However, the knowledge sources do not provide a direct method to reference a named cohort within JQL by its cohort name. Instead, JQL typically filters users based on user properties or by joining with user profiles that have cohort membership properties set (such as when a cohort is created via CSV import or when a property is set to indicate cohort membership) Creating a Cohort via a CSV.If your cohort membership is reflected as a user property (for example, a property like ["[Month 4] Churned users"] == true or similar), you can filter for that property in your JQL. Otherwise, there is no documented way in the provided sources to reference a cohort by name directly in JQL.Summary:
- If your cohort adds a user property to profiles, filter for that property in your JQL.
- If not, the knowledge sources do not provide a way to reference a cohort by name in JQL.If you need to export the list of users in the cohort, you can do so via the Cohort Builder and then use that list to set a property or filter in JQL Creating a Cohort via a CSV.I'm sorry, but the knowledge sources do not contain enough information to show how to directly use a named cohort (like [Month 4] Churned users) in a JQL query. If you have a user property indicating cohort membership, you can use that in your JQL filter.
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.