Profiles schema

A Localytics Profile is a user-level record. Your app-scoped profiles are available in the app_profiles table and are available for you to query against and combine with your usage data. Similarly, your org_scoped profiles can be found in the org_profiles table. The schema for each is based on an Entity–attribute–value model data model and looks like this:


Column Name Type Description Example  
hashed_customer_id VARCHAR(40) hashed customer_id 36d96f319a44eb2d1ea199ersdfds155fbca3f403c37  
profiledb_id NUMBER(38,0) profile app/org key, look up in profiles_databases 43567  
attribute_key VARCHAR(128) Attribute key “Last Session Date”  
attribute_type VARCHAR(1) Attribute type s-> String, d-> Date, i-> Int  
attribute_value VARCHAR(255) String value of attribute (any type)    
attribute_value_str VARCHAR(255) Value if attribute_type = ‘s’ (string) “Red Sox”  
attribute_value_int NUMBER(38,0) Value if attribute_type = ‘i (int) 10  
attribute_value_dt DATE Value if attribute_type = ‘d’ (date) “2017-01-10”  
created_at TIMESTAMP_LTZ(9) Timestamp attribute created Thu, 02 Jun 2016 21:08:28 +0000  


Each row of this table is at the hashed_customer_id & attribute_key & profiledb_id level. Each profiledb_id corresponds to either a single app (in app_profiles) or a single org (in org_profiles). The unhashed customer_id for each customer is the value of the _ll.raw_customer_id attribute.

For optimal performance when querying app_profiles, it’s critical that you always filter by profiledb_id (corresponding to a single app or organization) in your WHERE clause.

Let’s say you are interested in exploring the app-scoped profiles for your app “NewsPulse”. We can find that appropriate profiledb_id for our NewsPulse app by querying the profile_databases lookup table:

Name SOURCE_ID SOURCE_TYPE PROFILEDB_ID
NewsPulse 12345 LocalyticsApplication 43567
1StopShop 67723 LocalyticsApplication 23443
Localytics 52352 Organization 12331
… … … …

For example, if you have a single customer Profile for in your NewsPulse app where the latest_city is New York, you would find a corresponding row for that customer where ATTRIBUTE_KEY='latest_city' and ATTRIBUTE_VALUE_STR='New York'. To query for a count of customers whose latest city is New York, you could write:

SELECT count(hashed_customer_id) FROM
FROM app_profiles
AND attribute_key = 'latest_city'
AND attribute_value_str = 'New York'
WHERE profiledb_id = 43567;

The customer_id for each customer is the value of the _ll.raw_customer_id attribute. To get a list of customer_id’s, you could write the following:

SELECT attribute_value as customer_id
FROM app_profiles
WHERE attribute_key = '_ll.raw_customer_id'
AND profiledb_id = 43567 
AND hashed_customer_id in 
    (SELECT hashed_customer_id
    FROM app_profiles
    WHERE profiledb_id = 43567 
    AND attribute_key = 'latest_city'
    AND attribute_value_str = 'New York');