how to join events to mp_people_data_raw ?
Hi Nave L., 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
In Mixpanel, there are several ways to join events with user profile data (mp_people_data_raw). Let me explain the main approaches:
Using JQL (Javascript Query Language)
You can use Mixpanel's JQL to join events and user profiles using the join()
function: LBKSfunction main() { return join( Events({ from_date: "2016-01-01", to_date: "2016-06-01", }), People(), { type: "inner", // You can use "full", "left", "right" or "inner" selectors: [{selector: 'properties["some_property"] == "value" and user["some_profile_property"] > value'}] } ); }LBKS
The join function accepts an optional third argument with join options including join type and selectors to filter the joined data. JQL: Create custom queries using Javascript code
Natural Joining in Mixpanel's Interface
In Mixpanel's interface, events are automatically joined to user profiles based on the distinct_id
field. Events are joined to user profiles on event.distinct_id = user_profile.distinct_id
. This happens automatically when you're using Mixpanel's reports and you don't need to explicitly define the join.
Data Model Understanding
According to Mixpanel's data model:
- Events describe actions that take place within your product
- User Profiles hold state about a user
- The join between them happens on the distinct_id fieldAs described in the documentation: "User profiles are joined to events on event.distinct_id = user_profile.distinct_id
." Data Model: How Mixpanel data is organizedIf you're working with exported data in your own data warehouse, you would join the events table with the mp_people_data_raw table using the distinct_id field as the join key.Is there a specific context or environment where you're trying to perform this join?
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.
im asking for snowflake data using sql
Let me check the knowledge base for you again. One moment! 📚
I understand you're looking to join Mixpanel events with user profile data in Snowflake using SQL. Here's how you can do that: When working with Mixpanel data exported to Snowflake, you'll need to join the events table with the people table using the distinct_id field. However, you should also consider using the identity mappings table to get the resolved distinct_id when available.Here's an example SQL query that joins events with user profiles in Snowflake: LBKSSELECT COALESCE(mappings.resolved_distinct_id, events.distinct_id) AS resolved_distinct_id, events.**, people.** FROM <DB_NAME> .<SCHEMA_NAME> .MP_MASTER_EVENT events LEFT JOIN <DB_NAME> .<SCHEMA_NAME> .MP_IDENTITY_MAPPINGS_DATA mappings ON events.distinct_id = mappings.distinct_id LEFT JOIN <DB_NAME> .<SCHEMA_NAME> .MP_PEOPLE_DATA_RAW people ON COALESCE(mappings.resolved_distinct_id, events.distinct_id) = people.distinct_id WHERE -- Add your filters here TO_DATE(CONVERT_TIMEZONE('UTC','<PROJECT_TIMEZONE> ', events.time)) > = TO_DATE('2023-01-01') AND TO_DATE(CONVERT_TIMEZONE('UTC','<PROJECT_TIMEZONE> ', events.time)) <= TO_DATE('2023-01-31')LBKS
You'll need to replace:
- <DB_NAME>
with your Snowflake database name
- <SCHEMA_NAME>
with your schema name
- <PROJECT_TIMEZONE>
with your Mixpanel project timezoneThis query:
1. Joins events with the identity mappings table to get the resolved distinct_id when available
2. Then joins with the people data using the resolved distinct_id (or the original distinct_id if no mapping exists)For accessing nested JSON data in the people table, you can use Snowflake's JSON functions. For example: LBKSSELECT people.DATA:$email::string as email, people.DATA:$name::string as name FROM <DB_NAME> .<SCHEMA_NAME> .MP_PEOPLE_DATA_RAW peopleLBKS
Snowflake provides more details on querying Mixpanel data in Snowflake.Is there a specific analysis you're trying to perform with this join?
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.