Table: Global Booking
The mrt_global__booking table is designed to store comprehensive information about individual bookings.
Currently, only users with current grant or prior grant history can make a booking. All indvidual bookings are stored, irrespective of booking status. A booking is made by a user (identified via a user_id), using its grant (identified by a deposit_id) towards an offer (identified by an offer_id) hosted by a cultural venue (identified by a venue_id).
Table description
| name | data_type | description |
|---|---|---|
| booking_id | STRING | Unique identifier for a booking. |
| booking_creation_date | DATE | Date when the booking was created. |
| booking_created_at | DATETIME | Timestamp when the booking was created. |
| booking_quantity | INT64 | Quantity of offer booked. Can be 1, or 2 if booking was made using the duo option. |
| booking_amount | NUMERIC | Total amount for the booking for one quantity. |
| booking_status | STRING | Current status of the booking. |
| booking_is_cancelled | BOOLEAN | Boolean. Indicates if the booking is cancelled. |
| booking_is_used | BOOLEAN | Boolean. Indicates if the booking has been used. |
| booking_cancellation_date | DATETIME | Date when the booking was cancelled. |
| booking_cancellation_reason | STRING | Reason for booking cancellation. |
| user_id | STRING | Unique identifier for a user. |
| user_age_at_booking | INT64 | The age of the user at the time of booking, calculated as the difference between the booking date and the user's date of birth. |
| deposit_id | STRING | Unique identifier for the deposit. |
| deposit_type | STRING | Type of the deposit, can be GRANT_18, GRANT_15_17, GRANT_17_18. |
| deposit_reform_category | STRING | Categorizes deposits following the reform, allowing distinction between 17 and 18-year-old beneficiaries for the new GRANT_17_18 credit. This field also differentiates between deposits granted before and after the reform. Values are : 15_17_pre_reform, 18_pre_reform, 18_experiment_phase, 17_post_reform, 18_post_reform. |
| reimbursed | BOOLEAN | Boolean. Indicates if the booking amount was reimbursed or not. |
| booking_intermediary_amount | NUMERIC | The amount of the booking multiplied by the booking quantity. This field is used when we calculate the revenue. |
| booking_rank | INT64 | Rank of the booking in the user's booking history. |
| booking_used_date | DATETIME | Date when the booking was used. |
| booking_used_recredit_type | STRING | The type of recredit associated with the booking, linking a booking to a specific user cohort. |
| stock_beginning_date | DATETIME | Timestamp of the beginning of the event. Only for event offers. |
| stock_id | STRING | Unique identifier for the stock. |
| offer_id | STRING | Unique identifier for the offer. |
| offer_name | STRING | Name of the offer as it appears in the application. |
| venue_name | STRING | Name of the venue. |
| venue_label | STRING | Label of the venue. The label is appended by the Ministry as token of quality and standing in its category. |
| venue_type_label | STRING | Type of the venue ('Musée', 'Cinéma','Librairie', etc). Selected by the partner in a drop-down list. |
| venue_id | STRING | Unique identifier for the venue. |
| venue_postal_code | STRING | Postal code of the venue. |
| venue_department_code | STRING | Department code of the venue. |
| venue_region_name | STRING | Region name where the venue is located. |
| venue_city | STRING | City where the venue is located. |
| venue_epci | STRING | EPCI code of the venue. |
| venue_density_label | STRING | Detailed density label of the venue's location. |
| venue_macro_density_label | STRING | Macro density label of the venue's location (urbain, rural) |
| venue_academy_name | STRING | Academy of the venue. |
| offerer_id | STRING | Unique identifier of the offerer. |
| offerer_name | STRING | Name of the offerer. |
| partner_id | STRING | Unique identifier of the partner. |
| offer_subcategory_id | STRING | Identifier for the subcategory of the offer. |
| physical_goods | BOOLEAN | Indicates if the offer includes physical goods. |
| digital_goods | BOOLEAN | Indicates if the offer includes digital goods. |
| event | BOOLEAN | Indicates if the offer is an event. |
| offer_category_id | STRING | Identifier for the category of the offer. |
| user_postal_code | STRING | Postal code of the user's registered address. |
| user_department_code | STRING | Department code associated with the user's registered address. |
| user_region_name | STRING | Region name of the user's registered address. |
| user_city | STRING | City associated with the user's registered address. |
| 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_academy_name | STRING | Academy name associated with the user's registered address. |
| user_density_label | STRING | String column.Density label (urban, rural) of the user's registered address. |
| user_macro_density_label | STRING | Macro density label of the user's registered address. |
| user_creation_date | DATETIME | Date when the user account was created. |
| 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_age | NUMERIC | Current age of the user. |
| user_birth_date | DATETIME | Birth date of the user. |
| user_is_active | BOOLEAN | Boolean. Indicates if the user's account is currently active (the user can access it, irrespective of grant status). |
| user_is_in_qpv | BOOLEAN | Boolean. Indicates if the user's registered address is in a priority neighborhood (QPV). |
| user_is_unemployed | BOOLEAN | Boolean. Indicates if the user is unemployed as per its registered activity. |
| 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). |
| item_id | STRING | Identifier for the item associated with the offer used internally by the data science team. |
| same_category_booking_rank | INT64 | Rank of the user booking within the same offer_category. |
| user_booking_rank | INT64 | Rank of the user booking. |
| 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. |
| venue_iris_internal_id | STRING | Internal IRIS identifier for the venue. |
| offer_url | STRING | URL to the offer. |
| venue_department_name | STRING | Department name where of the venue. |
| venue_density_level | STRING | ID of the density level of the venue (cf venue_density_label). |
| venue_is_permanent | BOOLEAN | Indicates if the venue is permanent. A permanent venue is a venue that can receive public permanently, that can propose offers, and that is managed by the partner. Permanent venues exemple : a library, a cinema. Non permanent venues example : a public garden that hosted a festival once, or a theater that hosted a concert once - it can be permanent for the partner who owns the theater, but not for the partner who is hosted once in this place. |
| offer_sub_type_label | STRING | Deprecated: Defines the offer sub genre for music, books and movies. Defines the |
| offer show sub type for shows. | ||
| user_activation_date | DATETIME | 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. |
| 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). |
| first_deposit_creation_date | DATETIME | First deposit creation date. |
| user_density_level | STRING | Integer column. Density level of the user's registered address. Ranges from 1 (highly urban) to 7 (highly rural). |
| isbn | STRING | ISBN of the book, if applicable. |
| user_department_name | STRING | Department name associated with the user's registered address. |
| venue_is_virtual | BOOLEAN | Indicates if the venue is virtual. Each offerer has a virtual venue, it is only used to publish digital offers. |
| offer_type_label | STRING | Deprecated: Defines the offer genre for music, books and movies. Defines the offer |
| show type for shows. | ||
| diversity_score | INT64 | A score assigned to a booking based on its rank and entity type, with a multiplier applied |
| is_local_authority | BOOLEAN | Indicates if the offerer is a local authority or not. |