Data Model: How Mixpanel data is organized

Overview

Mixpanel data is stored and isolated within a project. At this time, you cannot query data across multiple projects. Mixpanel supports a few different categories of data that can be used for analysis: events, user profiles, group profiles, and lookup tables. In data warehouse parlance, events make up the fact table while user profiles, group profiles, and lookup tables are dimension tables.

Event Property vs User Profile Property

An event property is a detail about an event. Event properties are incredibly important, as they provide the necessary context about events to ensure valuable analytics. Properties also facilitate the dissection of data, allowing for more detailed insight into event-driven data. Once tracked, events and their properties are immutable, meaning they cannot be changed.

Profile Properties give you detail about a certain user overall. In other words, Profile Properties describe your users as they exist in this moment. A user profile property could be a static value, such as a first name, or be something more likely to change, like the date of last login or the number of songs a user has played.

For example, while an Event Property or Super Property tells you whether a user had a paid or free account over time when a user did specific actions, a Profile Property would simply show you currently if they are paid or free.

Example

Imagine you work on a music streaming product and you want to answer questions like:

• What are the most popular songs and artists this week?
• What is the distribution of number of songs played per week by user?
• Which experiment performed better in an A/B test to drive a higher conversion rate from Free to Premium accounts?

You want to analyze uniques by both users and accounts so you create a group key for account_id. You also want to augment your events with details about songs being played so you create a “Songs” lookup table and specify the join key as song_id. Your data model will look like this:

Your Mixpanel data is made up of events and profiles, each of which is comprised of properties. Events are data points in a time-series database. Profiles are key-value stores.

Anatomy of an Event

The following event represents the fact that user john.doe@gmail.com played the song_id of 0wwPcA6wtMf6HUMpIRdeP7 on Tuesday, September 29, at 2020 8:42:11 PM GMT on a machine with IP 203.0.113.9.

{
  // The name of your event
  "event": "Played Song", 
 
   // A dictionary of properties to hold metadata about your event
  "properties": { 
 
    // The Mixpanel token associated with your project. You can find your Mixpanel token
    // in the project settings dialog in the Mixpanel app. Events without a valid token
    // will be ignored.
    "token": "6972694d809c7390676a138834f8c890",
 
    // The time an event occurred. If present, the value should be a Unix timestamp
    // (milliseconds since UTC epoch). If this property is not included in your request,
    // Mixpanel will use the time the event arrives at the server. If you're using our mobile SDKs,
    // it will be set automatically for you.
    "time": 1601412131000,
 
    // An IP address string (e.g. "127.0.0.1") associated with the event. This is
    // used for adding geolocation data to events, and should only be required if
    // you are making requests from your backend. If IP is absent (and ip=1 is not
    // provided as a URL parameter), Mixpanel will ignore the IP address of the request.
    "ip": "203.0.113.9",
 
    // A unique identifier for the event. Mixpanel uses this to deduplicate events in the case
    // of retries or network failures. Events with the same (event, time, distinct_id, $insert_id)
    // are considered duplicates. 
    // We recommend generating UUIDs for $insert_ids. Our SDKs do this automatically.
    "$insert_id": "5d958f87-542d-4c10-9422-0ed75893dc81",
 
    // The value of distinct_id will be treated as a string, and used to uniquely 
    // identify a user associated with your event. If you provide a distinct_id property
    // with your events, you can track a given user through funnels and distinguish 
    // unique users for retention analyses. You should always send the same distinct_id
    // when an event is triggered by the same user.
    "distinct_id": "john.doe@gmail.com",
 
    // You can also specify any arbitrary properties you want to track as metadata
    // about the event
    "app_version": "1.1.34.694.gac68a2b3",
    "song_id": "0wwPcA6wtMf6HUMpIRdeP7"
}

User Profiles, Group Profiles & Lookup Tables

All three are key/value stores that augment your event data with additional metadata about entities. The differences are whether the join key is customizable and whether events are copied and indexed by the join key.

User profiles are joined to events via distinct_id which is the default indexing for Events.

Group profiles are joined to events via an event property you specify as a group key. Once you create a new group key, we will add an additional index for your events on that property. This allows you to do funnels or retention by that property instead of by distinct_id.

Lookup tables are joined to events and user profiles using the join key that you specify. Unlike group profiles, your events are not indexed by the join key. You can use lookup table properties to do filtering, breakdowns, etc but you can’t do things like funnels analysis using the join key for uniques. Note: Currently, group profile properties cannot be lookup table keys.