{"id":61071,"date":"2021-05-27T16:04:00","date_gmt":"2021-05-27T15:04:00","guid":{"rendered":"https:\/\/www.artefact.com\/?post_type=news&p=61071"},"modified":"2024-09-20T17:45:44","modified_gmt":"2024-09-20T16:45:44","slug":"the-path-to-developing-a-high-performance-demand-forecasting-model-part-2","status":"publish","type":"blog","link":"https:\/\/www.artefact.com\/de\/blog\/the-path-to-developing-a-high-performance-demand-forecasting-model-part-2\/","title":{"rendered":"Der Weg zur Entwicklung eines leistungsstarken Nachfrageprognosemodells - Teil 2"},"content":{"rendered":"

Author<\/h2><\/div>\"Image\"

Rafaelle Aygalenq<\/h3><\/div>

Senior Data Scientist<\/p>\n<\/div><\/div><\/div><\/div><\/div>

<\/span><\/div>

Medium Blog by Artefact.<\/h3><\/div>

This article was initially published on Medium.com<\/strong>.
\nFollow us on our Medium Blog !<\/p>\n<\/div>

Read Our Article<\/span><\/a><\/div><\/div><\/div><\/div><\/div>

5 tips to better take promotional data into account<\/strong>
\nTL;DR<\/strong>
\nIn this following article of a large series of posts dedicated to demand forecasting, we will focus on how to model promotions, a key driver in sales forecasting, looking at what a typical promo dataset looks like, how features should be crafted, with a step-by-step example in Python, and how complex promotional granularity can be dealt with.<\/p>\n<\/div><\/div><\/div><\/div><\/div>

Context<\/h2><\/div>

When forecasting demand as retailers, we often can leverage several useful data sources such as historical sell-in, product and customer hierarchies, holidays, sell-out and promotions. It is important to pay particular attention to the latter as, in real life, promotional activity is often much more than just a dummy flag you should add as a feature to your model. It is a real complex business mechanism which can yield additional performance to your model if processed well.<\/p>\n<\/div>

Tips 1: Understanding promotions referentials<\/h2><\/div>

Sales dataset often do not include promo data. You need to plug in a specific referential into your training data. Promotional data often come as a set of columns, in the format of a promotion plan with promotion characteristics such as:<\/p>\n<\/div>

  • <\/i><\/span>
    \n

    SKU concerned by the promotion<\/p>\n<\/div><\/li>

  • <\/i><\/span>
    \n

    Retailer benefiting from the promotion<\/p>\n<\/div><\/li>

  • <\/i><\/span>
    \n

    Start and end dates<\/p>\n<\/div><\/li>

  • <\/i><\/span>
    \n

    Promotion ID (unique ID for identifying a promotion for a given SKU, retailer, start date, end date<\/em>)<\/p>\n<\/div><\/li>

  • <\/i><\/span>
    \n

    Promotion type (e.g. 3 for 2<\/em>)<\/p>\n<\/div><\/li><\/ul>

    \"\"<\/p>\n

    Example of promotion data<\/p>\n<\/div>

    Before starting the feature engineering and modeling parts, it is recommended to perform interviews with the business owners in order to understand how promotion are created and handled. Let\u2019s take the example of dates. In the case of a sell-in prediction, a promotion will be associated with several dates:<\/p>\n<\/div>

    • <\/i><\/span>
      \n

      official start and end dates for shipping orders from the distributor to the retailers (1)<\/p>\n<\/div><\/li>

    • <\/i><\/span>
      \n

      and official start and end dates of promotions set up in stores by retailers (2)<\/p>\n<\/div><\/li><\/ul>

      It is really important to understand which dates impact the most the target variable and to check with the business owners if there are any specificities to take into account (e.g. if some retailers may apply the promotions before or after the official start and end dates).<\/p>\n<\/div>

      Performing Exploratory Data Analysis (EDA) can help you understand the fluctuations and the impact promotions can have on the target variable. For example, below one can see that a particular type of promotion has a greater impact than others.<\/p>\n<\/div>

      \"\"<\/p>\n

      Example of the impact of two different promotions on the sales of a given product<\/p>\n<\/div>

      EDA can also be performed to validate insights previously highlighted by business teams. Below, it seems retailers are starting promotions well before the official start date.<\/p>\n<\/div>

      \"\"<\/p>\n

      Example of difference between official and real start date of promotions<\/p>\n<\/div>

      After the exploration, we need to process the raw promotion data in order for it to be used. The dates have first to be expanded and processed in order to have a continuous timeline. Some of them may need to be shifted based on what was found during the exploration and business interview phases.<\/p>\n<\/div>

      \"\"<\/p>\n

      Illustration of promotion data before and after preprocessing<\/p>\n<\/div>

      Tips 2: Choosing the right granularity<\/h2><\/div>

      As your EDA will show, promotions impacts differ greatly across products, retailers (or stores if you\u2019re working with sell-out data), types of promotions. Ideally, you\u2019d like to be as much precise as possible and keep a SKU x retailer x promotion type granularity.<\/p>\n<\/div>

      For example, you may have two different geographic granularity: retailer and warehouse (i.e. a warehouse contains severals retailers). By plotting your time-series at each granularity, you may find out that the impact of promotion is really visible at retailer level but seems smoothed for warehouses. This can be explained by the fact that not all retailers of a the warehouse are impacted by the promotion in the same way. Therefore in that example, it is preferable to work at the retailer granularity.<\/p>\n<\/div>

      \"\"<\/p>\n

      Difference of the promotion impact depending on the granularity<\/p>\n<\/div>

      Once the EDA has been done and the promotion data is at the right granularity, the objective is to create the most relevant features for future planned promotions for which we want to predict the associated sales.<\/p>\n<\/div>

      Tips 3: Crafting the right features<\/h2><\/div>

      People may think that just adding a dummy variable in your training dataset will be enough. This works to guide the model in understanding why demand or sales is higher at one point in time. However, it is a really poor way to model how promotion may impact sales. Typically, some promotion types may be more efficient than others, the impact of promotions may also be higher near the start date, lower after (as there are few people left that could benefit from the reduction).<\/p>\n<\/div>

      A more sophisticated feature we found useful when using boosting algorithms is to compute sales rolling means so as to give your model insights about how much each promo type was successful in the past.<\/p>\n<\/div>

      1. Theory<\/h3><\/div>

      The idea behind this kind of feature is to measure, for a given promotion, the average volume recently generated by \u201csimilar promotions\u201d in the past. We are going to compute the average historical sell-in on a similar scope (same promotion type, same SKU, same retailer) on a rolling window with a given horizon (e.g. on the 7 past days).<\/p>\n<\/div>

      \"\"<\/p>\n

      Example of a rolling mean with a 7-days window<\/p>\n<\/div>

      For this kind of features, a particular attention should be paid to data leakage, especially when setting the time horizon.<\/strong><\/p>\n<\/div>

      2. Python implementation<\/h3><\/div>

      Let\u2019s see how to implement a 7-days rolling mean feature step by step in Python. First of all, let\u2019s define our DataFrame with the following information:<\/p>\n<\/div>

      • <\/i><\/span>
        \n

        SKU<\/p>\n<\/div><\/li>

      • <\/i><\/span>
        \n

        Retailer<\/p>\n<\/div><\/li>

      • <\/i><\/span>
        \n

        Promotion type<\/p>\n<\/div><\/li>

      • <\/i><\/span>
        \n

        Promotion ID<\/p>\n<\/div><\/li>

      • <\/i><\/span>
        \n

        Date<\/p>\n<\/div><\/li>

      • <\/i><\/span>
        \n

        Sell-in (our target variable<\/em>)<\/p>\n<\/div><\/li><\/ul>

        \n

        # Initialize our example dataframe with 6 columns: sku, retailer, promotion type,
        \n# promotion id, date, sellin
        \ndf = pd.DataFrame(<\/p>\n

        )<\/p>\n

        # Initialize our horizon: 7-days rolling mean
        \nhorizon = 7<\/p>\n

        # Add a line \"in the future\" for which we want to forecast the sell-in (unknown for
        \n# now) and therefore for which we want to have a value for the rolling mean feature
        \ndf = df.append(
        \n,
        \nignore_index=True
        \n)<\/p>\n<\/div>\n<\/div>

        Once created, our DataFrame looks like this:<\/p>\n<\/div>

        \"\"<\/p>\n

        Initial DataFrame<\/p>\n<\/div>

        Next we will create two important columns : the start date of promotion and the rolling mean (empty for now<\/em>).<\/p>\n<\/div>

        \n

        # We create two new columns:
        \n# \u2013 the minimum promo date (start date based on promotion ID)
        \ndf = df.merge(
        \ndf.groupby([\"sku\", \"retailer\", \"promotion_id\"]).date.min()
        \n.reset_index()
        \n.rename(columns=),
        \non=[\"sku\", \"retailer\", \"promotion_id\"],
        \nhow=\"left\"
        \n)
        \ndf = df.sort_values(\"min_promo_date\")<\/p>\n

        # \u2013 the rolling mean feature, filled with NaN for the moment
        \ndf['promo_rolling_mean'] = np.nan<\/p>\n<\/div>\n<\/div>

        Now, the DataFrame should look like this:<\/p>\n<\/div>

        \"\"<\/p>\n

        Head of the DataFrame with the two new columns<\/p>\n<\/div>

        From there, we can start filling in the promo_rolling_mean column. Remember that the goal is to compute the mean of sell-in of previous similar promotions but the notion of similarity can be tricky<\/strong>. In the best case scenario, we have in our history a promotion with the same type, for the same retailer, for the same SKU. In worst case scenario, there is a new promotion with a new type for which we do not have any history for any SKU, any retailer. Therefore the idea is to define several levels of granularity for which we will see if we have a history and therefore a possibility to calculate a rolling mean, starting from the most granular level (e.g. SKU x retailer x promotion type) to the least granular level (e.g. SKU).<\/p>\n<\/div>

        For example, let\u2019s take SKU : 1, retailer : A, promotion type : 1, date : 2020\u201301\u201301. We look for a similar promotion in the past. Lucky for us, there has been a promotion with the same promotion type, for the same SKU, the same retailer (i.e. the most granular level) in 2019 (promotion_id = \u2018A1\u20132019\u2019). Thus we will take the mean of the sell-in for the 7 most recent dates where this kind of promotion happened. In other cases we may not find any match for this granularity so we will be looking for a match by SKU and promotion type only. Again, if there is no match, we will finally take the mean at the SKU level only.<\/p>\n<\/div>

        \n

        # Definition of granularity levels to compute the rolling means, from the most granular
        \n# to the less granular
        \nAGG_LEVELS = <\/p>\n

        # We iterate on the granularity levels (from the most granular to the less granular) in
        \n# order to compute the rolling mean on the most similar promotion for each row
        \nfor agg_level_number, agg_level_columns in AGG_LEVELS.items():<\/p>\n

        # Once the rolling mean feature is filled, we break from the loop
        \nif df[\"promo_rolling_mean\"].isna().sum() == 0:
        \nbreak<\/p>\n

        # (1) We aggregate our dataframe to the current granularity level
        \nagg_level_df = df.groupby([\"promotion_id\"] + agg_level_columns)
        \n.agg()
        \n.reset_index()
        \n.rename(columns=)
        \n.dropna(subset=[\"sellin\"])
        \n.sort_values(\"min_promo_date\")<\/p>\n

        # (2) We compute the rolling mean on the given horizon for the current granularity
        \n# level
        \nagg_level_df[\"sellin\"] = agg_level_df.groupby(agg_level_columns)
        \n.rolling(horizon, 1)[\"sellin\"]\n.mean()
        \n.droplevel(
        \nlevel=list(
        \nrange(len(agg_level_columns))
        \n)
        \n)<\/p>\n

        # (3) We merge the results with the main dataframe on the right columns and min promo
        \n# date. We use the merge_asof to only take rolling means computed for dates before each
        \n# observation date.
        \ndf = pd.merge_asof(
        \ndf,
        \nagg_level_df,
        \nby=agg_level_columns,
        \non=\"min_promo_date\",
        \ndirection=\"backward\",
        \nsuffixes=(None, f\"_\"),
        \nallow_exact_matches=False
        \n)<\/p>\n

        # We fill the feature with the rolling mean values for the current granularity level
        \ndf[\"promo_rolling_mean\"] = df[\"promo_rolling_mean\"].fillna(
        \ndf[f\"sellin_\"])<\/p>\n

        cols_to_keep = [
        \n\"sku\", \"retailer\", \"promotion_type\", \"promotion_id\", \"date\",
        \n\"sellin\", \"promo_rolling_mean\"
        \n]<\/p>\n

        df = df[cols_to_keep].sort_values(
        \nby=['sku', 'retailer', 'promotion_type', 'promotion_id', 'date'])<\/p>\n<\/div>\n<\/div>

        At the end of the for loop, as you merge this new feature with the train set, you must pay attention at data leakage and only take rolling means computed for dates before each observation date (the date for which you want to make forecasts). Here, we have decided to use the merge_asof method. This allows us to merge two datasets avoiding exact matches. The idea behind it is: do not take the exact date match (with the allow_exact_matches=False parameter), but take the previous ones (with the direction=\u201dbackward\u201d parameter).<\/p>\n<\/div>

        Here is what our dataset looks like with the rolling mean feature filled after this step:<\/p>\n<\/div>

        \"\"<\/p>\n

        Final DataFrame with the rolling mean feature<\/p>\n<\/div>

        We can first see that there are some missing values for the rolling mean feature at the top of the DataFrame. This is normal and is due to the fact that for the first rows, we do not have any history on any SKU, any retailer, therefore no possibility to compute a rolling mean. This is the only case where the rolling mean will be empty, any other case can be handled by the definition of the granularity aggregation levels.<\/p>\n<\/div>

        For example, for the specific row we have defined at the beginning (SKU: 1, Retailer A, Promotion type: 3, Date: 2020\u201306\u201301), the one for which we don\u2019t know the sell-in yet, the rolling mean value will be the mean of the sell-in for the most similar and recent promotion. In our case, there is no history for the promotion type 3 for the retailer A, but there is for retailer B. Therefore, the rolling mean value will be the mean of sell-in for SKU=1, Promotion type= 3, Retailer=B, here: mean([134, 146]) = 140.<\/p>\n<\/div>

        \"\"<\/p>\n

        Example for a new promotion type for a couple SKU x retailer<\/p>\n<\/div>

        This logic can be extended to several other cases that can be encountered in this type of projects. For example, an additional level could be created which would be the product family and which could be used in the case where there is no history for a given product. In that case we will take the average based on products belonging to the same family. It is therefore important to think about these levels of granularity and prioritize them according to your own definition of \u201cpromotion similarity\u201d which can be based on your EDA or business insights, for example.<\/p>\n<\/div>

        Instead of the rolling means, you can also measure the promotional uplift (i.e. the additional volume generated by a given promotion) for a given product and a given customer. The idea would be to compute a ratio between sales during a given promotion and the sales without any promotion instead.<\/p>\n<\/div>

        Tips 4: Dealing with big data<\/h2><\/div>

        Working at such a level of granularity can drastically increase complexity and the need for computational power. If like us you are dealing with hundreds of SKUs, retailers and several years of daily historical sell-in, it will be essential to find a way to parallelize computations. For example, if you have no need to get any information from other SKUs for your promotion features, you can partition your data on the sku column and use distributed computing. We found it useful to use Dask for this task:<\/p>\n<\/div>

        \n

        from dask import delayed, compute<\/p>\n

        def compute_rolling_mean(df):
        \n…
        \nreturn df<\/p>\n

        skus_list = set(df[‘sku’])
        \ndfs_with_promo = [
        \ndelayed(compute_rolling_mean)(df.loc[df.sku == sku]) for sku in skus_list
        \n]\ndf_final = pd.concat(compute(*dfs_with_promo), axis=0, ignore_index=True)<\/p>\n<\/div>\n<\/div>

        Tips 5: Taking into account demand transfers across products<\/h2><\/div>

        Don\u2019t forget that each SKU sales will be impacted by its promotions, but also by promotions from substitutable products, such phenomena is known as cannibalization. In order to have a performant model, it is mandatory to anticipate the potential downlift on some products resulting from cannibalization.<\/p>\n<\/div>

        \"\"<\/p>\n

        Cannibalization illustration<\/p>\n<\/div>

        In order to be able to model the phenomenon, we first have to detect the cannibalization relationships between products. Two main approaches can be distinguished:<\/p>\n<\/div>