There are times when relevant data doesn't make it into Mixpanel or even changes after being sent to Mixpanel, which makes it difficult to answer specific behavioral questions. Lookup tables is feature that allows for Mixpanel customers to enrich their events and profile properties within Mixpanel with additional data with a simple CSV upload or via APIs.
A Lookup table is a collection of lookup profiles. A Lookup profile is a single profile that is mapped to a single event or profile property value in Mixpanel. The event or profile property that the Lookup table is mapped to is known as the base property. This base property acts as the join key, the primary identifier of the lookup table which is used to join against the mapped Mixpanel property.
Lookup Tables takes data from an uploaded CSV file and generates a mapping between the content in your CSV file and your data in Mixpanel through the base property.
When querying a Lookup table, Mixpanel will examine the event or profile property value, search for that Lookup profile in your table and return the queried Lookup profile property. A Lookup profile property is a property on a single lookup profile.
Imagine that you're an e-commerce site. You might use Lookup tables to:
- Enrich your property values - add additional data to your existing Mixpanel data to answer questions you couldn't before with what you had historically tracked
- Change property values - generate mappings between old property values and newer property values so that your analyses always reflect how you currently like to reference those property values
- Filter by a long list of values - rather than manually selecting individual items in a filter, use a Lookup Table to add a new property and apply a filter based on that new property
Uploading a Lookup Table
You can upload a Lookup Table from Lexicon, or directly upload a Lookup Table from your Insights, Funnels, Flows and Retention report. Directly uploading a lookup table in a report creates a temporary mapping, allowing you to test out the lookup table without making that mapping available for all users in the project.
Map a Lookup Table from Lexicon
To map a lookup table from Lexicon, navigate to the Lookup Tables tab and click Import. Under the dropdown menu, click Lookup Table
Click Import CSV on the widget that appears and select the lookup table CSV file from your local machine.
(Optional: click Select a property to select what event / profile property your table should map to. You can also choose to map your table to a property after you've uploaded from the Events Property Tab or User Profile Properties tab in Lexicon.)
Map a Lookup Table from a Report
To map to a lookup table from a report, start by navigating to one of the core reports and applying a filter / breakdown to the property you would like to enrich.
Click on the Overflow menu that appears on the property and click Map Lookup Table:
Select a lookup table that you have access to from the list of existing lookup tables that have been uploaded or click Import CSV to upload a new lookup table. Click Apply when your upload is complete:
Querying with a Lookup Table property
Once your Lookup table has been uploaded and mapped to a property, your Lookup table properties will appear in your filter / breakdown options as <base property> -> <Lookup profile property name>. You can search for your lookup table properties by the base property:
or directly by the property name:
You can now use these Lookup Table properties to answer behavioral questions you may not have been able to previously:
For more information on how to structure your CSVs for upload and to gain a more in-depth understanding of Lookup Tables, visit our Lookup Tables article.