Data driven X+O insights with Qualtrics and SAP IBP – Part 4
Using Experience data (X – data) in Qualtrics Experience Management to perform Demand Sensing in SAP Integrated Business Planning with Historical Sales Orders (O – Data)
Traditionally demand sensing is performed in SAP Integrated Business Planning using historical sales orders, shipments and forecasts based on a planning level in an area. However, it is also possible to include up to 8 additional signals during this calculation. Here, we included the product satisfaction score which was collected from Qualtrics Core-XM via a Survey. We also defined the SAP IBP instance with new forecast models to consider these additional signals. In this blog we describe how this additional signal from Qualtrics (X-Data) was extracted, transformed and loaded into SAP IBP. We also see how this additional signal along with Sales orders (O-Data) are used by the demand sensing algorithms.
Data Extraction, Transformation and Loading into SAP IBP
After Survey responses are collected as raw data in Qualtrics Core-XM, they are then analyzed using tools available in XM Suite. There could be different ways of analyzing this data. There are multiple tools available such as TextIQ, PredictIQ or StatisIQ for different purposes. With TextIQ, it is possible to parse unstructured textual inputs into meaningful insights. Stats iQ is a powerhouse statistical tool that can be appreciated by novice and expert analysts alike. Predict iQ analyses your respondents’ survey responses and embedded data in order to predict when a customer will eventually churn (abandon the company). Once a churn prediction model is configured in Predict iQ, newly collected responses will be evaluated for how likely the respondent is to churn, allowing you to be proactive in your company’s customer retention. We recommend taking a closer look into the Qualtrics Suite of IQ tools for a better understanding of these capabilities. For this simple use case, we considered the generic reporting done by Qualtrics Core-XM. We score the responses, breaking them to weekly reports and exported them as a CSV file. Now we prepare this file to be digested by SAP IBP.
Step 1 – Choose the Target system
We used the SAP Cloud Platform Integration – Data Services for this purpose to push data into SAP Integrated Business Planning. CPI-DS in short has a native support to read the staging tables in SAP IBP.
Fig 1. Staging tables from SAP IBP available in CPI-DS for loading data
We create a simple project after selecting the target SAP IBP configuration.
Step 2 – Create a data flow
Inside the project create a new data flow by adding a new target object with the respective staging table. Here we create a data source using a file adapter. We select the source file which was exported from Qualtrics Core-XM which contains the product satisfaction score as a weekly report.
Step 3 – Add the necessary transformations
The weekly report which was exported from Qualtrics Core-XM is a CSV file. We select only the mean, timestamp and the product code in to the mapping area. It is also possible to include additional information from other sources at this stage. After data is extracted from the CSV file, we modified the time stamp into a YYYY-MM-DD format using string parsing the CSV value from the file. We also mapped the product ID, location ID and customer ID into the target staging table. The important step here is to map the mean value from the CSV file into the product satisfaction score which was defined as a key figure in the planning area. One can also generate the Lag and insert it as a key figure at this stage. The final data flow was looking like the following
Fig 2. Data flow in SAP CPI-DS which contains the different stages of data processing from Qualtrics into SAP-IBP
Step 4 – Execute the data flow
Once the flow is done, it can be scheduled to import the CSV data from Qualtrics into the staging tables of SAP IBP. The Data Integration Jobs monitoring application can be watched for the current state of the processing. If everything went fine, data would have been extracted, transformed and loaded into the staging tables. After which, the post processing jobs would start to move the data into the respective planning area and the key figures.
At this stage, we also need historical sales order together with lag for the specific product which we surveyed in the previous steps. This is a standard practice which is done on every planning process using SAP IBP. We assume this is done already on your system. For example purposes, we simulated the sales orders and lag for the same product.
Step 5 – Check for the data.
It is easy to cross check if the data was loaded properly in SAP IBP in multiple ways. One of the easiest method is to open the Analysis application in SAP IBP and build a chart by selecting the Product satisfaction key figure for the planning area we used for importing the data.
Fig 3. Imported data as Product Satisfaction Score key figure in SAP IBP.
Step 6 – Analysis
SAP IBP also supports a Microsoft Excel Add-In for running different planning algorithms. One of them is doing demand sensing using Statistical Forecasting. For this, we open Excel with the plugin loaded. We connect to the SAP IBP system and the respective planning area which was used to import the data. Under the Application Jobs, we could run a Statistical Forecasting for the planning area. Here we select the time period as “Day” and then select the Forecast Model to be used as the one which we created in the previous blog as demand sensing with additional downstream signal – DemandSensingWithPSS.
Schedule to run this and observe the result of the analysis. It is also possible to prognose the demand sensing calculation and its effects with respect to a negative or positive satisfaction scores. It is also possible to normalize the product satisfaction score to the historical sales order quantity and observe the results. The way data is analyzed with different strategies highly depends on the nature of the data and the use case. We used a demand sensing template DS-10 from the best practices guide to run the forecast.
Fig 4. Using the Excel Plugin for Demand Sensing with Product Satisfaction Score
Once this forecasting is scheduled, the results can be viewed in the resulting workbook or plotted.
Fig 5. Demand sensing done without the external signal.
You could see in the above Fig 5, that Demand Plan for this product is assuming it is coming back down from a peak period of sales back to lower levels. In recently, the actual history of requested quantities / orders from customers have had peak period that we had trouble forecasting. Demand Sensing, being fed only this information, understood the patterns and the fact that we have been under-forecasting this product in our Demand Plan. So, it is now correcting the Demand Plan by correcting for the under-forecasting trend and the predicted sales pattern. Demand Sensing also points to demand going down to lower levels but at a slower pace than the Demand Plan. Demand Sensing will help us protect our sales! Now lets observe what happens to Demand Sensing when we add the extra signal from Qualtrics XM-Core based product satisfaction score.
Fig 6. Demand sensing done with the external signal.
If we look at our average Product Satisfaction score history in recent weeks (Weeks 52, 1 – 3), we see that we have enjoyed periods of high satisfaction from our customers. When we add this to the Demand Sensing mode, the story changes: Demand Sensing automatically learns the correlation between Product Satisfaction score and actual demand in the market. It responds to this and suggests that we increase our demand forecast further for the near future: demand is not predicted to fall back to low-levels we have seen in history but is predicted to stay quite high due to the market feedback and how this has meant higher sales in the past. Without this information, we could have under-forecasted our demand. But by keeping the pulse of the customers and arming Demand Sensing with this information, we are better prepared for the future.
The methods shows in this Blog series can be applied to any score or experience data as long it gives a meaningful insight. We showed in this series of blogs the different stages of how experience data from Qualtrics Core-XM was integrated and digested by Demand sensing algorithms in SAP Integrated Business Planning along with operational data. It is up to the readers to consider data from their survey and operational data to take it to the next step for a real-life situation. The methods implemented in this series of blogs for integrating Qualtrics Core-XM for demand sensing in SAP IBP is very generic.The same methods can be used for other planning scenarios in SAP IBP. We hope this series helps you to make your first steps in the X-O journey to make data driven decisions.
Special thanks to all members in Qualtrics Solution management team and SAP IBP development teams for sharing their insights and bringing us up to speed in this opportunity.