Mandatory Dataset

the following core dataset files are mandatory for building the yieldigo instance and must be provided in the structure described below each table indicates the column name in the file, the data type, and a description primary keys (unique values) are highlighted in bold categories to enable accurate product categorization, a complete list of all product categories must be provided, including both the category names and their hierarchical position within the category tree only one category structure can be implemented — either a tree or a horizontal structure if multiple category structures exist on the client’s side, the client must designate one primary structure to be used for product classification column data type description category id string unique category identifier name string name of the category parent id string identifier of the parent category leave empty for root categories data checks all rows with an empty parent id represent top level categories categories must form a tree like structure, i e an acyclic graph a category tree typically consists of 3–6 levels the category hierarchy must be unambiguous — if multiple structures exist, a single primary structure must be chosen each category must contain at least some products other options for export an alternative approach to exporting categories is the horizontal structure products a complete list of all products included in the analysis must be provided this dataset should contain all essential information for each product, including the product name, description, and any other relevant metadata multipacks if each multipack's pricing is independent of its products, then each multipack should have its unique identifier however, if the pricing of a multipack depends on the products it contains, then multipacks should not be included in the product table column data type description product id integer/string unique product identifier name string name of the product status integer 0/6/9 0 active 6 temporarily not active (seasonal) 9 delisted leaf category id integer identifier of the lowest level category in the category structure (leaf node) vat decimal value added tax optional columns column data type description unit size decimal the product size can be expressed in kilograms, grams, or liters, but the same unit must be used consistently across the entire lowest level category unit count integer for multipacks, this value specifies the number of individual products contained in one pack data checks each leaf category id must reference a leaf node — i e the last and most detailed level of the category tree active products are defined as items that have been sold in the recent past or are newly listed unit size represents the expected measurement unit within a given category for example, do not mix grams and kilograms in case of discrepancies, all values should be converted to kilograms the prices file should contain entries for the majority of active products other options for export products, brands, and suppliers may be combined into a single table, but only if each product has exactly one brand and one supplier assigned if a product can be associated with multiple brands or suppliers, this information should be provided in separate files (see sections brands and suppliers) possible modifications status – a binary field (true/false) can be used custom data – a column for any additional information the client would like to display on the reporting page stores this table is mandatory and may only be omitted if a single price applies to the entire market column data type description store id string unique store identifier name string name of the store optional columns column data type description city string city of the store (used for sourcing local weather data) zones a pricing zone is defined as a group of stores that share the same regular sale price this table is mandatory and may only be omitted if there is a single pricing zone, i e all stores use the same price list column data type description store id string unique store identifier zone id string zone identifier zone name string name of the zone all stores within a single zone must share the same price (or be intended to share it in the future) each existing store must be assigned to exactly one zone data checks store id must not contain any whitespace characters each store must be assigned to a zone other options for export export zone id and zone name as additional columns in the stores file export zone id as a new column in the stores file the zones file must contain both zone id and zone name actual prices provide the current valid regular prices for each zone and product do you maintain multiple “layers” of price lists? for example, can promotional and regular prices be valid at the same time? please provide only regular prices in the data you send what is your primary data source for margin calculation? is it the purchase price, the cost price, or the stock price? it is important to keep this choice consistent over time how is the cost price calculated? do you apply retroactive adjustments (e g back bonuses)? can promotional and regular purchase prices be distinguished? if yes, please compute the cost price using only the regular purchase prices all sale prices must exclude promotional prices column data type description product id string product identifier zone id string zone identifier regular price decimal actual regular shelf selling price, incl vat cost decimal cost price, excl vat optional columns column data type description regular price detail text any additional information related to the regular price, such as its source, tax rates, or other relevant context cost detail text any additional information related to the cost, such as its source, calculation logic, or other relevant context data checks the majority of active products must have an entry in the prices file all values in the zone id column must exist in the zones file prices must not include promotional prices the majority of products should have a positive margin other options for export export is promo price as a new boolean column in the prices file this column enables the export of actual promotional prices it should be used in cases where the regular price is not available during promotions, and the cost price may vary significantly sales the sales files must contain all sales transactions ideally, they should cover three years of sales history , including daily increments for the most recent days for the first iteration , please provide two months of sales data only this smaller dataset will be sufficient to verify the format and identify potential data issues for sales data, we distinguish between two types historical data – exported as monthly files in the format sales yyyymm csv (e g , sales 201901 csv, sales 201902 csv) incremental data (daily/weekly) – exported as files in the format sales yyyymm csv (monthly) or sales yyyymmdd csv (daily / starting day of the week) deletion of records – clarify whether sales records can disappear (e g , visible one day, missing the next) consistency of records is required full day data – the system requires complete sales data for each day it is preferable to provide data that is two days old but complete, rather than incomplete data from the previous day state of orders (e shops) – define which orders should be treated as final sales, ensuring they are stable and unlikely to be canceled vouchers, discounts, and promotions – specify which activities affect the regular sale price and how these can be identified in the data returns – returns must not be included in the dataset column data type description store id string store identifier product id string product identifier date date date of the sale (in e shops, ordering date) price type integer 0 regular sales (standard price) 2 global promotion (discounted price available to all customers leaflets, special offers) 3 individual promotion (targeted coupons, etc ) 4 end of life sellout (clearance) 5 expiration markdown 6 multibuy discount (discount when buying more than x units) 99 other rare case 1 regular sales individual price (vip customer discount, golden card) quantity decimal sales volume may contain decimal values in the case of weighted goods price decimal sale price (incl vat) in the case of aggregated data, use the weighted average (weighted by quantity) cost price decimal cost price (excl vat) for the transaction this can be either the cost/stock price or the purchase price from the supplier in the case of aggregated data, use the weighted average (weighted by quantity) this value is used to calculate profitability in sales reports and charts and should match the definition currently applied in your systems the column must always be filled, never null or negative; 0 is acceptable optional columns column data type description usage vat decimal vat is applied at the time of sale necessary if vat is used basket id string basket identifier does not have a relation to other tables necessary for basket size insights and analysis promo type id string promotion type identifier relates to the table of promotion types that is imported separately do not fill if the promotion ids column is filled, as that carries more details and this field would be redundant recommended if promotion ids cannot be provided promotion ids string list of promotion identifiers pipe delimited when multiple stacked promotions were applied at the same time, e g "38123|23231" relates to the table of promotions history that is imported separately necessary for the full functionality of promotion analytics shelf price decimal the price shown on the price tag that all customers see this is the standard price available to everyone, without any basket discounts / employee discounts, or other privileges important for improving price elasticity estimation if customers commonly pay less than the price they see and primarily decide upon timestamp date sales date and time in the format hh\ mm\ ss markdown management module batch id string batch identifier markdown management module customer id string customer identifier does not have a relation to other tables segment id string customer segment to which the customer belonged relates to the table of segments that is imported separately allows to see historical sales charts per segment and plan segment specific promotions bulk tier limit decimal quantity tier limit that defines when the discounted price applies multibuy module data checks price and shelf price must always include vat (if vat is applied) cost price must always exclude vat no duplicate records are allowed in the file promo type id should contain only values from a predefined, limited set of options zero values in the quantity, price, or cost price columns must be checked for validity transactions with significantly negative margins must be reviewed — they are typically caused by promotions or data errors other options for export export aggregated by day/store/product/price type/promo type within a single day, multiple rows may exist — one for regular sales and additional rows for each price type id or promo type id this option is not recommended because it restricts access to certain product features cross check please provide aggregated transaction data once the sales file structure has been confirmed and valid transaction history has been delivered this is a one time export table used to verify data quality after the sales history is provided if inconsistencies are found between this manually exported data and the data exported automatically, further investigation will be required to identify and resolve the discrepancies the purpose of this table is to validate automatically exported data and cross check sold quantities, revenues, and margins aggregation must be performed at the top category level at least 12 months of history must be provided column data type description month string month in yyyy mm format (e g , 2019 01) category id string category identifier store id string store identifier if store id is not provided, the data will be considered applicable to all stores within the defined category category name string name of category store name string name of store total quantity decimal regular + promo quantity total revenue decimal regular + promo revenue, excl vat total profit decimal regular + promo profit computed as (total revenue excl vat total costs, excl vat) promo quantity decimal promo quantity promo revenue decimal promo revenue, excl vat promo profit decimal promo profit computed as (promo revenue excl vat promo costs, excl vat) data checks total quantities, revenue, and profit should correspond to expectations total quantities contain either the total number of products or the weighted goods that are not counted in promo share should correspond to expectations