Generating Reports on Last Triggered Event Dates
Is it possible to look at the last date an event was triggered and breakdown a report with that date? e.g.. for a login event, how would we show the date of the last login triggered?
Hi Sean Y., 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
Based on the provided knowledge sources, there isn't specific information about breaking down a report by the last date an event was triggered. However, I can provide some relevant information about breakdowns and event analysis in Mixpanel that might be helpful:Mixpanel allows you to break down reports by various properties and time-based factors. According to the Mixpanel Reports Overview, you can use the Breakdown feature to group results of your metrics by a property or cohort:"Breakdown groups the results of your metrics in Insights, Funnels, Flows, or Retention reports by a property or cohort, allowing you to examine which properties or cohorts have a significant impact on your query."While this doesn't directly address breaking down by the last event date, it suggests that you might be able to use a custom property to achieve something similar.Additionally, Mixpanel's JQL (JavaScript Query Language) offers more advanced querying capabilities. As mentioned in the JQL documentation, you can use the
groupByUser()function to analyze events for each user:"A very common use-case forgroupByUser()is to compute some property of each user based on their behavior."You might be able to use JQL to create a custom query that finds the last login date for each user and then use that information in your analysis.However, to get a definitive answer on whether you can break down a report specifically by the last date an event was triggered, you would need to consult Mixpanel's more detailed documentation or reach out to their support team. The provided knowledge sources don't contain enough information to fully answer your specific question about showing the date of the lastlogintriggered in a breakdown.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.
Sean Y.··Could you provide an example JQL query for what I'd like to do?
Let me check the knowledge base for you again. One moment! đź“š
Certainly! I can provide you with an example JQL query that demonstrates how to find the last date a 'login' event was triggered for each user. Here's an example based on the information from the JQL documentation:
LBKSfunction main() { return Events({ from_date: "2023-01-01", to_date: "2023-12-31", event_selectors: [{event: "login"}] }) .groupByUser(function(state, events) { if (events.length > 0) { // Get the time of the last login event var lastLoginTime = events[events.length - 1].time; // Convert to a Date object and format as ISO string var lastLoginDate = new Date(lastLoginTime).toISOString().split('T')[0]; return {last_login_date: lastLoginDate}; } return state; }); }LBKSThis query does the following:1. It selects all 'login' events for the year 2023 (you can adjust the date range as needed). 2. It groups the events by user usinggroupByUser(). 3. For each user, it finds the last login event (which will be the last event in the array due to temporal ordering). 4. It extracts the date from the timestamp and formats it as a YYYY-MM-DD string. 5. It returns an object with the last login date for each user.The result will be a collection of objects, each representing a user who logged in during the specified period, with their last login date.Note that this query gives you the raw data. To create a breakdown or visualization based on this data, you might need to process the results further or use Mixpanel's visualization features in conjunction with this JQL query.Remember that JQL queries are subject to certain limitations, as mentioned in the Query API documentation:- There's a rate limit of 60 queries per hour and a maximum of 5 concurrent queries. - Queries will timeout after 2 minutes of run-time. - There's a 5 GB limit on data that can be processed in a single query, and a 2 GB limit on the resulting output data.If you need to analyze a very large dataset or create more complex visualizations, you might need to combine this JQL approach with other Mixpanel features or export the data for further processing.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, it is possible to use JQL to create a custom user property that shows a user's last login date. However, the process involves a few steps and requires using both JQL and Mixpanel's Custom Properties feature. Here's how you can approach this:1. First, you would use JQL to compute the last login date for each user. 2. Then, you would use Mixpanel's Custom Properties feature to create a new user property based on this JQL query.Let's break this down:1. JQL Query: You can use a JQL query similar to the one I provided earlier to compute the last login date for each user.2. Custom Property: According to the Custom Properties documentation, you can create custom properties that calculate new properties on the fly. To create a custom property for the last login date:1. Click the plus button to the right of the search bar in a report. 2. Select "User Property" to open the property builder. 3. Give your property a name (e.g., "Last Login Date"). 4. In the formula bar, you would need to use a combination of Mixpanel's formula language and potentially a reference to your JQL query.However, it's important to note that there are some limitations:1. As stated in the Custom Properties documentation, "the formula used to compose your custom property can't be longer than 20,000 characters."2. The Custom Properties feature doesn't directly support using full JQL queries. Instead, it uses a simpler formula language.3. For complex calculations like finding the last login date, you might need to use Mixpanel's "Borrowed Properties" feature, which is mentioned in the Custom Properties documentation: "Borrowed property creation is limited to Admin & Owner Roles only... A project can have a maximum of 20 borrowed properties."Given these limitations, creating a custom user property for the last login date might require a combination of approaches:1. Use JQL to calculate the last login dates. 2. Export this data and import it back into Mixpanel as a user property. 3. Then use the Custom Properties feature to reference and potentially manipulate this imported property.For the most accurate and up-to-date method to achieve this, I recommend reaching out to Mixpanel's support team. They can provide guidance on the best way to implement this specific use case within the current capabilities of the platform.Custom Properties: Calculate new properties on the fly
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.
- Sean Y.··
function main() { return Events({ from_date: "2023-01-01", to_date: "2025-12-31", event_selectors: [{event: "login"}] }) .groupByUser(function(state, events) { if (events.length > 0) { // Get the time of the last login event var lastLoginTime = events[events.length - 1].time; // Convert to a Date object and format as ISO string var lastLoginDate = new Date(lastLoginTime).toISOString().split('T')[0]; return {last_login_date: lastLoginDate}; } return state; }); }I updated the code to work with my Mixpanel instance. Instead of showing distinct ID in the first column of the query results. How would I display email instead? kapa.ai
Let me check the knowledge base for you again. One moment! đź“š
To display the email instead of the distinct ID in the first column of your query results, you'll need to modify your JQL query to include the email property. Here's an updated version of your code that should accomplish this:
LBKSfunction main() { return Events({ from_date: "2023-01-01", to_date: "2025-12-31", event_selectors: [{event: "login"}] }) .groupByUser(["properties.email"], function(state, events) { if (events.length > 0) { // Get the time of the last login event var lastLoginTime = events[events.length - 1].time; // Convert to a Date object and format as ISO string var lastLoginDate = new Date(lastLoginTime).toISOString().split('T')[0]; return {last_login_date: lastLoginDate}; } return state; }); }LBKSThe key change here is in thegroupByUser()function. We've added["properties.email"]as the first argument. This tells Mixpanel to include the email property in the grouping key.According to the JQL documentation:"It is possible to compute more fine-grained aggregation withgroupByUser()by providing a list of additional keys in the first argument. User distinct_id always remains the implicit first key."By adding"properties.email"to the grouping keys, you're telling Mixpanel to use the email as part of the grouping key. This should result in the email being displayed in the output instead of the distinct ID.However, please note a few important points:1. This assumes that the email is stored as an event property named "email". If it's stored under a different name or as a user profile property, you'll need to adjust the property name accordingly.2. If some events don't have an email property, those rows will have a null or empty value for the email.3. If you're using Mixpanel's Simplified ID Merge system, make sure that the email property is consistently set across all events for each user.If you find that this doesn't work as expected, you might need to check how email data is stored in your Mixpanel instance and adjust the property reference accordingly.JQL documentationkapa.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.