Lets say I have an event create_post with properties id and tags. I also have an event like_post event with properties id. How would I add a filter for like_post events that are liking posts that were created with tags containing “foo”? Essentially I would be doing a simple join in SQL.
Hi Ashton C., 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 the filtering you're describing, you can use Mixpanel's Borrowed Properties feature. This feature allows you to "borrow" a property from one event and use it to analyze another event, which is similar to performing a join in SQL. Here's how you can set it up: 1. First, create a borrowed property for the like_post event that borrows the tags property from the create_post event: - Go to Lexicon in Mixpanel
- Create a new borrowed property for the like_post event
- Set it to borrow the tags property from the create_post event, using the id property to match the events2. Once you've created this borrowed property, you can use it to filter like_post events in your analyses: - In your report, select the like_post event
- Add a filter for the borrowed tags property
- Set the filter to "contains" "foo"This setup will effectively filter like_post events to only those where the corresponding create_post event had "foo" in its tags property.It's important to note that Borrowed Properties have a 7-day lookback window, so this will only work for like_post events that occur within 7 days of the corresponding create_post event Borrowed Properties.Also, keep in mind that there's a limit of 20 borrowed properties per project, so use them judiciously Borrowed Properties.This approach allows you to perform a join-like operation in Mixpanel without needing to write SQL or modify your event tracking.
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, if you need a lookback period longer than 7 days, JQL (JavaScript Query Language) is indeed an option you can use in Mixpanel. JQL allows for more complex and customized queries, including the ability to join events over longer time periods. Here's how you could approach this using JQL: 1. You can use JQL to create a custom query that joins the create_post and like_post events based on the id property, without the 7-day limitation of Borrowed Properties.2. Here's an example of how you might structure such a query: LBKSfunction main() { return join( Events({ from_date: "2023-01-01", to_date: "2023-12-31", event_selectors: [{event: "create_post"}] }), Events({ from_date: "2023-01-01", to_date: "2023-12-31", event_selectors: [{event: "like_post"}] }) ) .filter(function(joined) { return joined.event.name == "like_post" && joined.joined_events[0].properties.id == joined.event.properties.id && joined.joined_events[0].properties.tags.indexOf("foo") > -1; }) .groupBy(["properties.id"], mixpanel.reducer.count()); }LBKSThis query joins create_post and like_post events, filters for like_post events where the corresponding create_post event had "foo" in its tags, and then counts these events grouped by the post id Query JQL.However, it's important to note a few limitations and considerations when using JQL: 1. JQL queries have a rate limit of 60 queries per hour and a maximum of 5 concurrent queries Custom JQL Query.2. Queries will timeout after 2 minutes of run-time Custom JQL Query.3. 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 Custom JQL Query.4. JQL is in maintenance mode, meaning Mixpanel is not investing in it further JQL: Create custom queries using Javascript code.Given these limitations, if you need to perform this kind of analysis frequently or at scale, Mixpanel recommends using their Data Pipelines add-on to export your events to your data warehouse for more flexible analysis 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.
