I have an event (âSubscribedâ) to which I want to add a count property based on RRID. If itâs the first event, the count property should be set to 1. If there are already existing events with the same RRID, the count should reflect the respective occurrence. I thought to use JQL, can you help me create the script
Hi Tal, 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 achieve your goal of adding a count property to the "Subscribed" event based on the RRID using JQL, you can write a custom JQL script. The script will iterate over the events, group them by the RRID, and then assign a count to each event based on its occurrence order.Here's a JQL script that should help you accomplish this: LBKSfunction main() { return Events({ from_date: "2023-01-01", // Adjust the date range as needed to_date: "2023-12-31", event_selectors: [{ event: "Subscribed" }] }) .groupBy(["properties.RRID"], mixpanel.reducer.count()) .map(function(event) { return { event: event.name, RRID: event.key[0], count: event.value }; }); }LBKSThis script does the following: 1. Fetches "Subscribed" events within a specified date range.
2. Groups the events by the "RRID" property.
3. Uses the mixpanel.reducer.count() to count occurrences of each RRID.
4. Maps the result to include the event name, RRID, and the count.You can adjust the date range in the Events() function to suit your needs. This script will give you a count of how many times each RRID has appeared in the "Subscribed" events.For more information on JQL and how to use it, you can refer to the JQL documentation.
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 add the count as a property to each "Subscribed" event, you can modify the JQL script to include the count in the event properties. Here's how you can do it: LBKSfunction main() { return Events({ from_date: "2023-01-01", // Adjust the date range as needed to_date: "2023-12-31", event_selectors: [{ event: "Subscribed" }] }) .groupBy(["properties.RRID"], function(accum, events) { events.forEach(function(event, index) { event.properties.count = index + 1; // Assign count based on occurrence accum.push(event); }); return accum; }, []) .map(function(event) { return { event: event.name, properties: event.properties }; }); }LBKSThis script does the following: 1. Fetches "Subscribed" events within a specified date range.
2. Groups the events by the "RRID" property.
3. Iterates over each group of events, assigning a count property to each event based on its order of occurrence.
4. Returns the modified events with the new count property included in their properties.This way, each "Subscribed" event will have a count property that reflects its occurrence order among events with the same RRID. For more details on using JQL, you can refer to the JQL documentation.
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! đ
I'll help you modify the JQL script to return only the events where the count is 1 (first occurrence of each RRID) along with all their properties. Here's how you can do it: LBKSfunction main() { return Events({ from_date: "2023-01-01", // Adjust the date range as needed to_date: "2023-12-31", event_selectors: [{ event: "Subscribed" }] }) .groupBy(["properties.RRID"], function(accum, events) { // Only push the first event (count = 1) for each RRID if (events.length > 0) { events[0].properties.count = 1; accum.push(events[0]); } return accum; }, []); }LBKSThis script: 1. Fetches all "Subscribed" events within your specified date range
2. Groups them by the "RRID" property
3. For each group of events with the same RRID, it only keeps the first event (count = 1)
4. Adds a "count" property with value 1 to these events
5. Returns these first-occurrence events with all their original properties intactThe result will be a collection of "Subscribed" events where each represents the first occurrence of a unique RRID, with all their original properties plus the new count property.For more information on JQL and its capabilities, you can refer to the JQL documentation.
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! đ
I understand you're having issues with the groupBy function in the JQL script. Let's troubleshoot the problem.The issue might be related to how we're using the groupBy function. According to the Mixpanel documentation, there are specific ways to use this function correctly.Let's revise the script based on the proper usage of groupBy in JQL: ```function main() {
return Events({
from_date: "2023-01-01", // Adjust the date range as needed
to_date: "2023-12-31",
event_selectors: [{ event: "Subscribed" }]
})
.groupByUser(["properties.RRID"], function(state, events) {
// Initialize state if undefined
state = state || {};
// Process events in temporal order
for (var i = 0; i < events.length; i++) {
var event = events[i];
var rrid = event.properties.RRID;
// If this is the first occurrence of this RRID
if (!state[rrid]) {
state[rrid] = true;
event.properties.count = 1;
return event;
}
}
return null;
})
.filter(function(result) {
return result !== null;
});
}```The issue might be that we were using groupBy incorrectly. According to the <https://docs.mixpanel.com/docs/reports/apps/jql#groupbykeys-reducer-functions|JQL documentation>, the reducer function in groupBy has a specific signature and behavior.Alternatively, we could try using groupByUser which guarantees that the reducer function will process all events of a single user in temporal order, as shown in the JQL documentation.If you're still experiencing issues, it might help to: 1. Check if the "RRID" property exists in your events
2. Verify the date range to ensure events are being captured
3. Try a simpler query first to confirm basic functionalityLet me know if this revised approach helps or if you're still encountering issues.
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! đ
There are two main ways to merge events based on properties in Mixpanel:
1. Using Custom Events
You can create a virtual event that combines multiple events filtered by specific properties: 1. Expand the Event dropdown in an Insights, Funnels, or Retention report 2. Select the plus button to the right of the search bar 3. Select the events and properties you want to include 4. Name your custom event and click SaveThis creates a virtual event that combines the selected events with the property filters you specified. You can then use this custom event in your reports as if it were a regular event.Custom Events documentation provides more details on this feature.
2. Using Lexicon to Merge Events
If you're a project owner, you can permanently merge events in Lexicon: 1. Navigate to Lexicon 2. Select the events you want to merge 3. Click the "Merge" icon that appears 4. In the "Merge Events" window, specify which event should be the new unique event 5. Click MergeThis is useful when you have similar events with different names (like "Purchase" on iOS and "purchase item" on Android) that you want to treat as a single event.Lexicon documentation provides more details on merging events.Both methods allow you to combine events based on properties, but Custom Events is more flexible for ad-hoc analysis while Lexicon merging is a more permanent solution that affects all reports.
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.