CPC definition 

Target:

The target represents the data we are trying to optimize, in this case the unit price of a CPC (Customer Product Combination).

The unit price we use in our models is the result of several computation made in our data preparation steps, we could summarize it as follows :

• Gathering and aggregation of the CPC forecasted sales and volumes on the next 12 months.
  • Computation of the resulting forecasted unit price
• Gathering of the CPC last invoiced price from the forecast data.
• Gathering and aggregation of the CPC historical sales and volumes on the last 12 months.
  • Computation of the resulting historical unit price
• Gathering of the CPC last invoiced price from the historical data.
• Select the first non-null unit price following this order :
  • Forecasted unit price
  • Last invoice price from forecasts
  • Last invoice price from historical data
  • Historical unit price

Note : as of now, this unit price includes all costs : fixed and variable.

More details are available below on how these computations are included in our global data preparation flow.

Price drivers:

To select the final list of the most relevant price drivers, we collected, built and tested more than 50 features:

These price drivers are coming from several data sources described below.

Forecasts and historical data

The main data source we are currently using is the Pricing Data Lake in Big Query, especially the two following datasets :

• V_FACT_sales_forecast_enriched_current : Forecasts data.
• V_FACT_sales_history_cpc_last12months : Historical data for the past 12 months.

These datasets include :

• Sales and volume measures that are also used to generate the unit price used as a target (see dedicated § above) for our models.
• Dimensions used as features / price drivers (see dedicated § above).

You will below the details of the processing steps for these data.

Forecast data cleaning and aggregation (1)

In this first step, we perform a bit of data filtering and cleaning and then aggregate the forecast data at a CPC level.

Data filtering and cleaning :

1. Columns renaming to match the names used in our backend.
2. Filter on the right GBU (through gbu_id field).
3. Filter on the last running month (running_dt field).
4. Filter forecasts of current and next 11 months (forecast_dt field) => this is mandatory as we will then sum the sales and volumes at a CPC level and want to stay on a 12 months basis.
5. Delete records with null customer and product keys (shipto_customer_id / material_id for Novecare).
6. Generate our CPC identifier (shipto_customer_id / material_id for Novecare).
7. Keep only the columns we need.

CPC aggregation :

The data we get from the data lake is at a CPC + distribution channel + month level. This means that each distribution channel for a given CPC can have its own value for some dimensions (especially incoterms, group of activity and enterprise segment).
Given the fact that we only want one record by CPC, we will keep the values associated to the highest amount of sales, following these steps :

1. Compute total sales by distribution channel for every CPC (new field total_sales_by_channel).
2. Sort the data by cpc, total_sales_by_channel (highest first) and month (closest first).
3. Aggregate the sorted data by CPC :
   1. keeping the first non-null value for all the dimensions and last_invoice_price 
   2. summing the measures (volume and sales)

The output of this first step therefore contains the forecasted data aggregated at a CPC level, which means we always get 1 record for each CPC.

• The dimensions displayed are the ones of the closest forecasted month for the distribution channel with the most sales.
• The volume and sales measures are summed over the 12 first months of forecasts.
• The unit price is not yet computed at this stage.

Historical data cleaning (2)

This is the equivalent of the forecast data cleaning step.

Note that :

• This time, we separate the aggregation step as we need the cleaned data at a CPC + month level in order to compute sales evolution features (TODO : add doc link).
• We do not need to filter months as it is already done by the Data Lake.

Data filtering and cleaning :

1. Columns renaming to match the names used in our backend.
2. Filter on the right GBU (through gbu_id field).
3. Delete records with null customer and product keys (shipto_customer_id / material_id for Novecare).
4. Generate our CPC identifier (shipto_customer_id / material_id for Novecare).
5. Keep only the columns we need.

The output of this first step contains the historical data aggregated at a CPC + incoterms + month level, which means we always get 1 record for each CPC and month.

Historical CPC aggregation (3)

The data we get from the data lake is at a CPC + incoterms + month level. Each incoterms for a given CPC can have its own value for some dimensions
Given the fact that we only want one record by CPC, we will keep the values associated to the highest amount of sales, following these steps :

1. Compute total sales by incoterms for every CPC (new field total_sales_by_incoterms).
2. Sort the data by cpc, total_sales_by_incoterms (highest first) and month (closest first).
3. Aggregate the sorted data by CPC :
   1. keeping the first non-null value for all the dimensions and last_invoice_price 
   2. summing the measures (volume and sales)

Historical and forecast data merge (4)

ICM measures

Unavailable for now in the Data Lake.

List of "dimensions" used as features + global list of fields we use from data lake ?

- In the forecast dataset, these features were aggregated by CPC to retain the earliest available data (Group recipe 1).

- The same features in the historical dataset were aggregated by CPC to retain the latest available data (Group recipe 2).

- In the Python recipe, the two dataset were combined to fill empty values of forecast data by historical data and to generate the features dataset. As a result, the main data source for all these dimensions remain the forecasts, historical data being only used as fillers.

Manual inputs (update the doc + process)

- Product groupings are collected from this GSheet which is supplied by the business. As long as the product group that are in general used as hard-boundaries to limit the comparable sets, the file also contains a few other columns that may be used as features for some of the product families.

- The manual region mapping for each product family comes from this GSheet and is also supplied by the business.

- In the Join recipe, both datasets are added to the feature dataset obtained in the previous steps.

Product characteristics

- Both product composition files are provided in this GSheet file for now. This source will have to be industrialized through the data lake.

• The PC_composition_file_sql dataset contains the product composition at a "EHS_Product" level of granularity.
• The PC_matserial_mapping_sql dataset contains the mapping between the "EHS_Product" and the material codes we are using in the rest of the project.

- In the Python recipe, we compute the product composition features at a product level by aggregating together the different substances having the same component type. 

- The Join recipe adds the material code to the dataset using the "EHS_Product" key mapping.

Contracts

Note : Contracts data are not currently used as features because too few CPCs are under contract in the data we use. 

- Both contracts files are provided in this  GSheet file for now. This source will have to be industrialized through the data lake if used in the future.

• The Contract_Product_dataset_sql dataset contains the link between a contract and the products it is applied on. When a contract does not have any product linked, we consider that it applies to all of the products sold to the customer.
• The Contract_dataset_sql dataset contains the contract information at a customer level.

- In the first prepare recipe, we only keep the contracts with a "Signed" status as the other ones are not considered active.

- We then group the dataset by CPC to only keep one contract if several are active. We currently do not have a rule to define priorities so we keep one randomly.