I believe timeseries() is one of the most powerful and insightful "magical" function in the CRM Analytics. As per documentation, timeseries function crunches your data and selects the forecasting model that gives the best fit. It also detects seasonality in your data and consider periodic cycles when predicting what your data will look like in the future. Out of the box, you may create the timeseries forecasting on any column of the dataset on the Year and Month level for any given interval of time in the future. Although, the function DOES allow the use of Weekly interval (i.e. Year-Week) for the forecasting/ prediction but it has some additional limitations, which I am going to discuss in another article.
The video snippet below demonstrates the use of timeseries function on an existing dataset column:
The timeseries function can also be used directly in the SAQL of any given query (if you are a SAQL fan like me), but it is available to use via UI in the Compare Table widget. A potential limitation of the timeseries function in the dataset explorer (UI) is that it does not allow the use of a formula column within the same compare table regardless of the position (before or after the source columns).
A very simple use case for the above scenario is to compute forecasted/ predicted values of a calculation like Opportunity Won Rate, Quota Achievement etc. using a timeseries function and display the result on a timeline chart. In the example below, I calculated a simple percentage of the Monthly Unit Sold by their respective monthly targets as Target Achievement. See below that the column selection dropdown of the timeseries function does not include the newly created Rate column as a choice to generate the timeseries prediction.
In order to display the timeseries prediction using a formula requires a two step update and conversion to the SAQL mode.
Step 1: Create your Compare Table with all the columns and formula columns you need. This will provide you with the formula needed to be added in the SAQL. In this step I added the two existing dataset columns and created a formula column that shows the Quota Achievement Rate in percent format.
Step 2: Since my newly created formula field is not available in the drop down list of the column selection, I used any existing column to drive the predicted values using the timeseries.
Step 3: Now convert the query to SAQL mode
Step 4: Find the formula syntax and generate the computed value in the first "foreach generate" statement and give it a meaningful name.
Step 5: Replace the column used in the timeseries with the formula field (as generated in the previous step) and remove/ comment unnecessary statements and additional fields
Step 6: Convert the widget from Compare Table to a Timeline chart and adjust corresponding properties like axis, and predictive line to show reference.
Here you go. In a simple two part SAQL update approach, your predicted values are generated on any given custom formula. Enjoy!!
Comments