Schema · Entity relationship diagram
Sephora tables and relationships
Daily Sephora sell-out by product (EAN x channel x country) and by store, in local currency with Sephora's monthly USD and EUR rates, from Sephora's Lumena brand-partner reporting API. One connection covers every region you're enrolled in: US, Canada, UK and EU.
sephora- Daily sell-out by product (EAN × channel × country) and by store, in local currency and USD, plus Sephora's monthly KPI reports: loyalty clients, category benchmarks, sephora.com traffic, stock and purchase orders. Every table is keyed on
key_id, the grain columns joined with|. A report your brand has no access to is skipped. - One connection covers every region the brand is enrolled in;
countryholds Sephora's country code (US, CA, GB). Use the revenue excluding tax columns: including tax overstates UK sales by the 20% VAT. - Sephora restates its history with every batch and can renumber keys (an EAN, a department). Each batch replaces its own slice of the table (
_slice: country and day, month, or the whole country for 12-month snapshots), so old keys never double count. In S3, keep the rows of the latestpipeline_run_atper_slice. - Column suffixes:
_12mrolling 12 months,_m/_m1the month,_ytdyear to date,_y1the same period a year earlier,_frat the current fixed rate. Rows withAllin channel, department, category or subcategory are Sephora subtotals.
Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.
Table reference
11 tables · click a table for its columnsdaily_sellout_product24 columns · Sell-outshow in diagram
SKU-level daily sell-out. Report: sellout_perf_kpi_by_channel_product_daily Grain: country x channel x ean_code x transaction_date key_id = country|channel|ean_code|transaction_date; _slice = country|transaction_date
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| brand | varchar(64) | ||
| region | varchar(16) | ||
| country | varchar(2) | ||
| channel | varchar(16) | ||
| ean_code | varchar(20) | ||
| transaction_date | date | ||
| local_currency | varchar(3) | ||
| units_sold | decimal(18,4) | ||
| units_sold_y1 | decimal(18,4) | ||
| revenue_incl_tax_local | decimal(18,4) | ||
| revenue_excl_tax_local | decimal(18,4) | ||
| revenue_incl_tax_local_y1 | decimal(18,4) | ||
| revenue_excl_tax_local_y1 | decimal(18,4) | ||
| fx_rate_usd | decimal(18,8) | ||
| fx_rate_eur | decimal(18,8) | ||
| revenue_incl_tax_usd | decimal(18,4) | ||
| revenue_excl_tax_usd | decimal(18,4) | ||
| revenue_incl_tax_usd_y1 | decimal(18,4) | ||
| revenue_excl_tax_usd_y1 | decimal(18,4) | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| key_idPrimary key. | varchar(128) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|transaction_date; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|transaction_date; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
daily_sellout_store27 columns · Sell-outshow in diagram
Store-level daily sell-out. Report: sellout_perf_kpi_by_retail_channel_daily Grain: country x channel x store_code x department x transaction_date (no EAN at this level) key_id = country|channel|store_code|department|transaction_date; _slice = country|transaction_date store_name arrives truncated (~25 chars, store number stripped); store_display_name = "<store number> <name>" (~30 chars, also cut).
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| brand | varchar(64) | ||
| region | varchar(16) | ||
| country | varchar(2) | ||
| channel | varchar(16) | ||
| store_name | varchar(128) | ||
| store_display_name | varchar(128) | ||
| store_code | varchar(16) | ||
| department | varchar(32) | ||
| transaction_date | date | ||
| local_currency | varchar(3) | ||
| units_sold | decimal(18,4) | ||
| units_sold_y1 | decimal(18,4) | ||
| revenue_incl_tax_local | decimal(18,4) | ||
| revenue_excl_tax_local | decimal(18,4) | ||
| revenue_incl_tax_local_y1 | decimal(18,4) | ||
| revenue_excl_tax_local_y1 | decimal(18,4) | ||
| fx_rate_usd | decimal(18,8) | ||
| fx_rate_eur | decimal(18,8) | ||
| revenue_incl_tax_usd | decimal(18,4) | ||
| revenue_excl_tax_usd | decimal(18,4) | ||
| revenue_incl_tax_usd_y1 | decimal(18,4) | ||
| revenue_excl_tax_usd_y1 | decimal(18,4) | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| key_idPrimary key. | varchar(128) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|transaction_date; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|transaction_date; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
client_turnover_and_activity77 columns · Loyalty clientsshow in diagram
Sephora client (loyalty member) activity and revenue, rolling 12 months, by category. Report: client_turnover_and_activity Snapshot: _slice = country; each batch replaces the country. _12m = rolling 12 months, _y1 = the year before, _fr_y1 = the year before at the current fixed FX rate. key_id = brand|country|channel|department|category|subcategory category/subcategory/department/channel value 'All' rows are Sephora subtotals: do not add them to detail rows.
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| region | varchar(16) | ||
| country | varchar(2) | ||
| department | varchar(64) | ||
| category | varchar(64) | ||
| subcategory | varchar(64) | ||
| brand | varchar(64) | ||
| channel | varchar(16) | ||
| local_currency | varchar(3) | ||
| active_clients_nb_12m | bigint | ||
| active_clients_nb_12m_y1 | bigint | ||
| quantity_12m | bigint | ||
| quantity_12m_y1 | bigint | ||
| revenue_incl_tax_12m | decimal(18,4) | ||
| revenue_incl_tax_12m_y1 | decimal(18,4) | ||
| revenue_excl_tax_12m | decimal(18,4) | ||
| revenue_excl_tax_12m_y1 | decimal(18,4) | ||
| revenue_incl_tax_eur_12m | decimal(18,4) | ||
| revenue_incl_tax_eur_12m_y1 | decimal(18,4) | ||
| revenue_excl_tax_eur_12m | decimal(18,4) | ||
| revenue_excl_tax_eur_12m_y1 | decimal(18,4) | ||
| revenue_incl_tax_usd_12m | decimal(18,4) | ||
| revenue_incl_tax_usd_12m_y1 | decimal(18,4) | ||
| revenue_excl_tax_usd_12m | decimal(18,4) | ||
| revenue_excl_tax_usd_12m_y1 | decimal(18,4) | ||
| transactions_nb_12m | bigint | ||
| transactions_nb_12m_y1 | bigint | ||
| lapsed_nb_12m | bigint | ||
| lapsed_nb_12m_y1 | bigint | ||
| reactivated_nb_12m | bigint | ||
| reactivated_nb_12m_y1 | bigint | ||
| retained_nb_12m | bigint | ||
| retained_nb_12m_y1 | bigint | ||
| repurchaser_nb_12m | bigint | ||
| repurchaser_nb_12m_y1 | bigint | ||
| new_mbr_nb_12m | bigint | ||
| new_mbr_nb_12m_y1 | bigint | ||
| new_mbr_revenue_excl_tax_12m | decimal(18,4) | ||
| new_mbr_revenue_excl_tax_12m_y1 | decimal(18,4) | ||
| new_mbr_revenue_excl_tax_eur_12m | decimal(18,4) | ||
| new_mbr_revenue_excl_tax_eur_12m_y1 | decimal(18,4) | ||
| new_mbr_revenue_excl_tax_eur_12m_fr_y1 | decimal(18,4) | ||
| new_mbr_revenue_excl_tax_usd_12m | decimal(18,4) | ||
| new_mbr_revenue_excl_tax_usd_12m_y1 | decimal(18,4) | ||
| new_mbr_revenue_excl_tax_usd_12m_fr_y1 | decimal(18,4) | ||
| new_mbr_revenue_incl_tax_12m | decimal(18,4) | ||
| new_mbr_revenue_incl_tax_12m_y1 | decimal(18,4) | ||
| new_mbr_revenue_incl_tax_eur_12m | decimal(18,4) | ||
| new_mbr_revenue_incl_tax_eur_12m_y1 | decimal(18,4) | ||
| new_mbr_revenue_incl_tax_eur_12m_fr_y1 | decimal(18,4) | ||
| new_mbr_revenue_incl_tax_usd_12m | decimal(18,4) | ||
| new_mbr_revenue_incl_tax_usd_12m_y1 | decimal(18,4) | ||
| new_mbr_revenue_incl_tax_usd_12m_fr_y1 | decimal(18,4) | ||
| repurchaser_revenue_excl_tax_12m | decimal(18,4) | ||
| repurchaser_revenue_excl_tax_12m_y1 | decimal(18,4) | ||
| repurchaser_revenue_excl_tax_eur_12m | decimal(18,4) | ||
| repurchaser_revenue_excl_tax_eur_12m_y1 | decimal(18,4) | ||
| repurchaser_revenue_excl_tax_eur_12m_fr_y1 | decimal(18,4) | ||
| repurchaser_revenue_excl_tax_usd_12m | decimal(18,4) | ||
| repurchaser_revenue_excl_tax_usd_12m_y1 | decimal(18,4) | ||
| repurchaser_revenue_excl_tax_usd_12m_fr_y1 | decimal(18,4) | ||
| repurchaser_revenue_incl_tax_12m | decimal(18,4) | ||
| repurchaser_revenue_incl_tax_12m_y1 | decimal(18,4) | ||
| repurchaser_revenue_incl_tax_eur_12m | decimal(18,4) | ||
| repurchaser_revenue_incl_tax_eur_12m_y1 | decimal(18,4) | ||
| repurchaser_revenue_incl_tax_eur_12m_fr_y1 | decimal(18,4) | ||
| repurchaser_revenue_incl_tax_usd_12m | decimal(18,4) | ||
| repurchaser_revenue_incl_tax_usd_12m_y1 | decimal(18,4) | ||
| repurchaser_revenue_incl_tax_usd_12m_fr_y1 | decimal(18,4) | ||
| revenue_excl_tax_eur_12m_fr_y1 | decimal(18,4) | ||
| revenue_excl_tax_usd_12m_fr_y1 | decimal(18,4) | ||
| revenue_incl_tax_eur_12m_fr_y1 | decimal(18,4) | ||
| revenue_incl_tax_usd_12m_fr_y1 | decimal(18,4) | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
client_top10_skus19 columns · Loyalty clientsshow in diagram
Top SKUs by client activity, rolling 12 months. Report: client_top10_skus Snapshot: _slice = country; each batch replaces the country. key_id = brand|country|channel|department|category|subcategory|ean_code category/subcategory/department/channel value 'All' rows are Sephora subtotals: do not add them to detail rows.
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| region | varchar(16) | ||
| country | varchar(2) | ||
| channel | varchar(16) | ||
| brand | varchar(64) | ||
| department | varchar(64) | ||
| ean_codeJoins to daily_sellout_product.ean_code (Same EAN code (leading 0 of a 13-digit EAN dropped: 12-digit UPC).). | varchar(20) | FK | Joins to daily_sellout_product.ean_code (Same EAN code (leading 0 of a 13-digit EAN dropped: 12-digit UPC).). |
| active_clients_nb_12m | bigint | ||
| quantity_12m | bigint | ||
| transactions_nb_12m | bigint | ||
| first_purchase_nb_12m | bigint | ||
| repurchasers_nb_12m | bigint | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| last_refresh_date | date | ||
| category | varchar(64) | ||
| subcategory | varchar(64) | ||
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
client_number_growth16 columns · Loyalty clientsshow in diagram
Active clients per month (_m) and the same month a year earlier (_m_y1). Report: client_number_growth Monthly: _slice = country|YYYY-MM of transaction_month. key_id = brand|country|channel|department|category|subcategory|transaction_month category/subcategory/department/channel value 'All' rows are Sephora subtotals: do not add them to detail rows.
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| brand | varchar(64) | ||
| region | varchar(16) | ||
| country | varchar(2) | ||
| channel | varchar(16) | ||
| department | varchar(64) | ||
| category | varchar(64) | ||
| subcategory | varchar(64) | ||
| active_clients_nb_m | bigint | ||
| active_clients_nb_m_y1 | bigint | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| transaction_month | date | ||
| transaction_month_identifier | integer | ||
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of transaction_month; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of transaction_month; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
client_benchmark_by_brand19 columns · Benchmarksshow in diagram
Brand client growth vs the category benchmark (GREEN/GREY/RED indicators). Report: client_benchmark_by_brand Monthly: _slice = country|YYYY-MM of reference_month. No technicalOperationDate in this report (loaded_at NULL). key_id = brand|country|channel|department|category|subcategory|reference_month category/subcategory/department/channel value 'All' rows are Sephora subtotals: do not add them to detail rows.
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| reference_month | date | ||
| reference_month_identifier | integer | ||
| brand | varchar(64) | ||
| region | varchar(16) | ||
| country | varchar(2) | ||
| channel | varchar(16) | ||
| department | varchar(64) | ||
| category | varchar(64) | ||
| subcategory | varchar(64) | ||
| brand_growth_m1 | decimal(18,4) | ||
| brand_growth_12m | decimal(18,4) | ||
| brand_growth_ytd | decimal(18,4) | ||
| bench_indicator_m1 | varchar(8) | ||
| bench_indicator_12m | varchar(8) | ||
| bench_indicator_ytd | varchar(8) | ||
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of reference_month; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of reference_month; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sellout_benchmark_by_brand23 columns · Benchmarksshow in diagram
Brand sell-out growth and rank vs the category benchmark. Report: sellout_benchmark_by_brand Monthly: _slice = country|YYYY-MM of reference_month. key_id = brand|country|channel|department|category|subcategory|reference_month category/subcategory/department/channel value 'All' rows are Sephora subtotals: do not add them to detail rows.
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| reference_month | date | ||
| reference_month_identifier | integer | ||
| brand | varchar(64) | ||
| region | varchar(16) | ||
| country | varchar(2) | ||
| channel | varchar(16) | ||
| department | varchar(64) | ||
| category | varchar(64) | ||
| subcategory | varchar(64) | ||
| bench_indicator_m1 | varchar(8) | ||
| bench_indicator_12m | varchar(8) | ||
| bench_indicator_ytd | varchar(8) | ||
| brand_growth_m1 | decimal(18,4) | ||
| brand_growth_12m | decimal(18,4) | ||
| brand_growth_ytd | decimal(18,4) | ||
| brand_rank_m1 | bigint | ||
| brand_rank_12m | bigint | ||
| brand_rank_ytd | bigint | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of reference_month; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of reference_month; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
web_events_top_n_products29 columns · sephora.comshow in diagram
sephora.com product-page traffic, add-to-cart and orders for the top products; _m1 = the month, _ytd = year to date. Report: web_events_topN_products Monthly: _slice = country|YYYY-MM of event_month. Traffic-only rows have no currency. key_id = brand|country|local_currency|department|category|subcategory|ean_code|product_designation|event_month category/subcategory/department/channel value 'All' rows are Sephora subtotals: do not add them to detail rows.
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| event_month | date | ||
| brand | varchar(64) | ||
| department | varchar(64) | ||
| category | varchar(64) | ||
| subcategory | varchar(64) | ||
| ean_codeJoins to daily_sellout_product.ean_code. | varchar(20) | FK | Joins to daily_sellout_product.ean_code. |
| product_designation | varchar(128) | ||
| region | varchar(16) | ||
| country | varchar(2) | ||
| local_currency | varchar(3) | ||
| pdp_visits_nb_m1 | bigint | ||
| pdp_page_views_nb_m1 | bigint | ||
| add_to_cart_nb_m1 | bigint | ||
| orders_nb_m1 | bigint | ||
| revenue_incl_tax_m1 | decimal(18,4) | ||
| revenue_incl_tax_eur_m1 | decimal(18,4) | ||
| revenue_incl_tax_usd_m1 | decimal(18,4) | ||
| pdp_visits_nb_ytd | bigint | ||
| pdp_page_views_nb_ytd | bigint | ||
| add_to_cart_nb_ytd | bigint | ||
| orders_nb_ytd | bigint | ||
| revenue_incl_tax_ytd | decimal(18,4) | ||
| revenue_incl_tax_eur_ytd | decimal(18,4) | ||
| revenue_incl_tax_usd_ytd | decimal(18,4) | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of event_month; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of event_month; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
web_events_materials29 columns · sephora.comshow in diagram
sephora.com traffic by device and platform, by category; _m1 = the month, _m1_y1 = a year earlier. Report: web_events_materials Monthly: _slice = country|YYYY-MM of event_month. Traffic-only rows have no currency. key_id = brand|country|local_currency|device|platform|department|category|subcategory|event_month category/subcategory/department/channel value 'All' rows are Sephora subtotals: do not add them to detail rows.
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| event_month | date | ||
| brand | varchar(64) | ||
| department | varchar(64) | ||
| category | varchar(64) | ||
| subcategory | varchar(64) | ||
| device | varchar(32) | ||
| platform | varchar(32) | ||
| country | varchar(2) | ||
| local_currency | varchar(3) | ||
| region | varchar(16) | ||
| pdp_visits_nb_m1 | bigint | ||
| pdp_page_views_nb_m1 | bigint | ||
| add_to_cart_nb_m1 | bigint | ||
| orders_nb_m1 | bigint | ||
| revenue_incl_tax_m1 | decimal(18,4) | ||
| revenue_incl_tax_eur_m1 | decimal(18,4) | ||
| revenue_incl_tax_usd_m1 | decimal(18,4) | ||
| pdp_visits_nb_m1_y1 | bigint | ||
| pdp_page_views_nb_m1_y1 | bigint | ||
| add_to_cart_nb_m1_y1 | bigint | ||
| orders_nb_m1_y1 | bigint | ||
| revenue_incl_tax_m1_y1 | decimal(18,4) | ||
| revenue_incl_tax_eur_m1_y1 | decimal(18,4) | ||
| revenue_incl_tax_usd_m1_y1 | decimal(18,4) | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of event_month; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of event_month; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
stock_insight_monthly45 columns · Supplyshow in diagram
Monthly stock position by EAN and storage type: quantity, value (EUR/USD/local), inbound, rolling COGS, out-of-stock counts. Report: supply_kpi_stock_insight_by_country_material_monthly Monthly: _slice = country|YYYY-MM of date_month. key_id = brand|country|department|ean_code|storage_type_label|sale_status_code|date_month
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| date_month | date | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| country | varchar(2) | ||
| local_currency | varchar(3) | ||
| region | varchar(16) | ||
| department | varchar(64) | ||
| brand | varchar(64) | ||
| ean_codeJoins to daily_sellout_product.ean_code. | varchar(20) | FK | Joins to daily_sellout_product.ean_code. |
| storage_type_label | varchar(64) | ||
| sale_status_code | varchar(8) | ||
| status_designation | varchar(64) | ||
| total_stock_quantity | bigint | ||
| total_stock_value_eur | decimal(18,4) | ||
| total_stock_value_usd | decimal(18,4) | ||
| total_stock_value_local_currency | decimal(18,4) | ||
| inbound_quantity | bigint | ||
| inbound_amount_eur | decimal(18,4) | ||
| inbound_amount_usd | decimal(18,4) | ||
| inbound_amount_local_currency | decimal(18,4) | ||
| rolling_cogs_local_currency | decimal(18,4) | ||
| rolling_cogs_eur | decimal(18,4) | ||
| rolling_cogs_usd | decimal(18,4) | ||
| total_stock_quantity_y1 | bigint | ||
| total_stock_value_eur_y1 | decimal(18,4) | ||
| total_stock_value_usd_y1 | decimal(18,4) | ||
| total_stock_value_local_currency_y1 | decimal(18,4) | ||
| inbound_quantity_y1 | bigint | ||
| inbound_amount_eur_y1 | decimal(18,4) | ||
| inbound_amount_usd_y1 | decimal(18,4) | ||
| inbound_amount_local_currency_y1 | decimal(18,4) | ||
| rolling_cogs_local_currency_y1 | decimal(18,4) | ||
| rolling_cogs_eur_y1 | decimal(18,4) | ||
| rolling_cogs_usd_y1 | decimal(18,4) | ||
| financial_mtd_rate_eur | decimal(18,8) | ||
| financial_mtd_rate_usd | decimal(18,8) | ||
| variant_count_by_month | bigint | ||
| out_of_stock_count_by_month | bigint | ||
| financial_mtd_rate_eur_y1 | decimal(18,8) | ||
| financial_mtd_rate_usd_y1 | decimal(18,8) | ||
| variant_count_by_month_y1 | bigint | ||
| out_of_stock_count_by_month_y1 | bigint | ||
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of date_month; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of date_month; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
orders_insight_monthly25 columns · Supplyshow in diagram
Monthly Sephora purchase orders to the brand by EAN: ordered, received, newness, delivered on time. Report: supply_kpi_orders_insight_by_country_material_monthly Monthly: _slice = country|YYYY-MM of date_month. key_id = brand|country|department|ean_code|storage_type_label|sale_status_code|date_month
Primary key: key_id
| Column | Type | Key | Description |
|---|---|---|---|
| date_month | date | ||
| loaded_atWhen the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | timestamp | When the file was loaded. Here: Sephora's batch load time (technicalOperationDate). | |
| country | varchar(2) | ||
| region | varchar(16) | ||
| department | varchar(64) | ||
| brand | varchar(64) | ||
| ean_codeJoins to daily_sellout_product.ean_code. | varchar(20) | FK | Joins to daily_sellout_product.ean_code. |
| storage_type_label | varchar(64) | ||
| sale_status_code | varchar(8) | ||
| status_designation | varchar(64) | ||
| country_worldwide_brand_label | varchar(64) | ||
| ordered_quantity | bigint | ||
| received_quantity | bigint | ||
| ordered_quantity_newness_material | bigint | ||
| received_quantity_newness_material | bigint | ||
| received_quantity_delivered_on_time | bigint | ||
| ordered_quantity_y1 | bigint | ||
| received_quantity_y1 | bigint | ||
| ordered_quantity_newness_material_y1 | bigint | ||
| received_quantity_newness_material_y1 | bigint | ||
| received_quantity_delivered_on_time_y1 | bigint | ||
| key_idPrimary key. | varchar(256) | PK | Primary key. |
| pipeline_run_at | timestamp | ||
| _sliceThe slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of date_month; each Sephora batch replaces the slices it restates. | varchar(32) | The slice a delivered file replaces (date, file or "all"). Here: country|YYYY-MM of date_month; each Sephora batch replaces the slices it restates. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |