User
The mrt_global__user table provides a comprehensive view of individual beneficiaries data, including demographic information, activity status, and engagement metrics. It is designed to support analysis of user behavior and trends over time.
All individual users who received a grant, irrespective of their grant status, are stored here. Demographic information (address, activity) are purely declarative. They are registered in the user's first grant application process. Users are invited to update those information when applying for a new grant (ie : when 15-17 users turn 18), and they can update those at any time through their app.
Table description
| name | data_type | description |
|---|---|---|
| user_id | STRING | Unique identifier for a user. |
| user_department_code | STRING | Department code associated with the user's registered address. |
| user_department_name | STRING | Department name associated with the user's registered address. |
| user_postal_code | STRING | Postal code of the user's registered address. |
| user_city | STRING | City associated with the user's registered address. |
| user_activity | STRING | User's registered activity (student, apprentice, unemployed etc). Registered at first grant deposit and updated when the user applies for its GRANT_18. |
| user_civility | STRING | Registered civility of the user (male, female). |
| user_school_type | STRING | Type of school the user is enrolled in (public, private etc), for GRANT_15_17 users. |
| user_is_active | BOOLEAN | Boolean. Indicates if the user's account is currently active (the user can access it, irrespective of grant status). |
| user_age | INT64 | Current age of the user. |
| user_birth_date | DATE | Birth date of the user. |
| user_has_enabled_marketing_email | BOOLEAN | Indicates if the user has accepted to receive marketing emails. |
| user_has_enabled_marketing_push | BOOLEAN | Indicates if the user has accepted to received marketing push. |
| user_iris_internal_id | STRING | Internal IRIS identifier associated with the user's registered address. IRIS (Ilots Regroupés pour l'Information Statistique) are small, standardized geographic units used for detailed statistical analysis in France. |
| user_is_priority_public | BOOLEAN | Boolean. Indicates if the user considered as a pass Culture priority public (users that are either residing in a rural area, in a QPV or are not in education). |
| user_is_unemployed | BOOLEAN | Boolean. Indicates if the user is unemployed as per its registered activity. |
| user_is_in_education | BOOLEAN | Boolean. Indicates if the user is in education, based on their registered activity. According to the INSEE, a user is considered to be in education if they fall under one of the following categories: Middle school student (Collégien), High school student (Lycéen), University student (Étudiant), Apprentice (Apprenti), Work-study student (Alternant). |
| user_is_in_qpv | BOOLEAN | Boolean. Indicates if the user's registered address is in a priority neighborhood (QPV). |
| user_epci | STRING | EPCI code associated with the user's registered address. An EPCI is a French public body enabling municipalities to collaborate on shared local services and development. |
| user_density_label | STRING | String column.Density label (urban, rural) of the user's registered address. |
| user_city_code | STRING | The INSEE code of the city associated with the user's registered address. |
| user_macro_density_label | STRING | Macro density label of the user's registered address. |
| user_density_level | STRING | Integer column. Density level of the user's registered address. Ranges from 1 (highly urban) to 7 (highly rural). |
| user_region_name | STRING | Region name of the user's registered address. |
| user_academy_name | STRING | Academy name associated with the user's registered address. |
| user_humanized_id | STRING | Human-readable identifier for the user. |
| currently_subscribed_themes | STRING | Users themes subscribed. Users can subscribe to themes (cinema, music) to receive custom communication related to those themes. |
| is_theme_subscribed | BOOLEAN | Boolean. Indicates whether a user has subscribed to at least one theme. |
| first_deposit_creation_date | DATE | First deposit creation date. |
| first_deposit_type | STRING | Type of the user's first deposit (GRANT_18 or GRANT_15_17). |
| total_deposit_amount | NUMERIC | Total amount deposited by the user. |
| current_deposit_type | STRING | Type of the user's current deposit. |
| user_first_deposit_reform_category | STRING | The first deposit reform category associated with the user. |
| user_current_deposit_reform_category | STRING | The current deposit reform category associated with the user. |
| first_individual_booking_date | DATE | Date of the first individual booking. |
| total_non_cancelled_individual_bookings | INT64 | Total number of non-cancelled individual bookings made by the user. |
| total_non_cancelled_duo_bookings | INT64 | Total number of non-cancelled duo bookings made by the user. |
| total_free_bookings | INT64 | Total number of free bookings made by the user. |
| total_actual_amount_spent | NUMERIC | Total actual amount spent, reflecting real transactions. |
| total_theoretical_amount_spent | NUMERIC | Total theoretical amount that would have been spent based on expected transactions. |
| total_theoretical_digital_goods_amount_spent | NUMERIC | Total theoretical amount spent on digital goods by the user. |
| total_theoretical_physical_goods_amount_spent | NUMERIC | Total theoretical amount spent on physical goods by the user. |
| total_theoretical_outings_amount_spent | NUMERIC | Total theoretical amount spent on outings by the user. |
| total_last_deposit_digital_goods_amount_spent | NUMERIC | Total amount spent on digital goods from the last deposit. |
| total_last_deposit_theoretical_amount_spent | NUMERIC | Total theoretical amount spent from the last deposit. |
| total_deposit_actual_amount_spent | NUMERIC | Total actual amount spent from deposits. |
| last_deposit_amount | NUMERIC | Amount of the last deposit received by the user. |
| total_theoretical_remaining_credit | NUMERIC | Total theoretical remaining credit for the user. |
| user_creation_date | DATE | Date when the user account was created. |
| last_booking_date | DATE | Date of the last booking. |
| days_between_activation_date_and_first_booking_date | INT64 | Number of days between the user's activation date and their first booking date. |
| days_between_activation_date_and_first_booking_paid | INT64 | Number of days between the user's activation date and their first paid booking. |
| user_activation_date | DATE | Date when the user's account was activated. Corresponds to the first booking date for users in experiment phase, and to user creation date for all users after the experiment phase. |
| first_booking_type | STRING | Offer category of the user's first booking. |
| first_paid_booking_type | STRING | Offer category of the user's first paid booking. |
| total_grant_18_subcategory_booked | INT64 | The total number of subcategory booked on grant_18 deposit. |
| total_grant_15_17_subcategory_booked | INT64 | The total number of subcategory booked on grant_15_17 deposit. |
| user_suspension_reason | STRING | Reason for the user's suspension (upon user request, fraud suspicion etc). |
| first_deposit_amount | NUMERIC | Amount of the user's first deposit received. |
| last_deposit_expiration_date | DATE | Expiration date of the user's last deposit. |
| user_is_current_beneficiary | BOOLEAN | Boolean. Indicates if the user still has available grant to use. |
| user_seniority | INT64 | Days between user account creation date and current date. |
| last_recredit_date | DATE | The most recent date on which the beneficiary received a new recredit. |
| total_recredit | INT64 | The total number of times the beneficiary received a new recredit. |
| total_recredit_amount | FLOAT64 | The total amount that has been recredited to the beneficiary. |
| total_diversity_score | INT64 | The total diversity score of the user or the deposit |