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> 👋

In Mixpanel, there are several ways to join events with user profile data (mp_people_data_raw). Let me explain the main approaches:

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'}] } ); }LBKSThe 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

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.

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?

ℹ️ 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 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')LBKSYou'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 peopleLBKSSnowflake provides more details on querying Mixpanel data in Snowflake.Is there a specific analysis you're trying to perform with this join?

ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.