ML Models : opportunity scoring

Hello, 

lately, I had a look at the ML models and wonder why the opportunity scoring remains at zero since the scoring widget was added : 

The OOB ML Model (Predictive Opportunity Scoring) stop with this error :

Can't automatically determine type for expression(s): StartDateLastStage. Generated query: SELECT [O].[Id], [O].[CreatedOn], [OIS].[StartDateLastStage], [O].[TypeId], [O].[StageId], [O].[AccountId], [O].[CategoryId], [O].[MoodId], [O].[IsPrimary], [O].[PartnerId], [O].[Budget], [O].[Amount], [O].[SourceId], [O].[ResponsibleDepartmentId], [O].[LeadTypeId], [A].[OwnershipId], [A].[IndustryId], [A].[TypeId] [AccountTypeId], [A].[AccountCategoryId], [A].[EmployeesNumberId], [A].[AnnualRevenueId], DATEDIFF(DAY, [O].[CreatedOn], [OIS].[CloseDate]) [DaysInFunnel], DATEDIFF(DAY, [OIS].[StartDateLastStage], [OIS].[CloseDate]) [DaysInLastStage], ( SELECT COUNT(*) FROM [dbo].[OpportunityContact] [OC] WITH(NOLOCK) WHERE [OC].[OpportunityId] = [O].[Id]) [OpportunityContactsNumber], ( SELECT COUNT(*) FROM [dbo].[Contact] [C] WITH(NOLOCK) WHERE [A].[Id] = [C].[AccountId]) [AccountContactsNumber], ( SELECT COUNT(*) FROM [dbo].[Lead] [L] WITH(NOLOCK) WHERE [L].[CreatedOn] < [OIS].[CloseDate] AND [L].[QualifiedAccountId] = [A].[Id]) [AccountLeadsTotalNumber], ( SELECT COUNT(*) [cnt] FROM [dbo].[Activity] [Act] WITH(NOLOCK) INNER JOIN [dbo].[ActivityResult] [Ar] WITH(NOLOCK) ON ([Act].[ResultId] = [Ar].[Id]) WHERE [Act].[OpportunityId] = [O].[Id] AND [Ar].[Id] = @P1 AND [Act].[TypeId] = '{E3831DEC-CFC0-DF11-B00F-001D60E938C6}') [OpportunityMeetingsTotal], ( SELECT COUNT(*) [cnt] FROM [dbo].[Activity] [Act] WITH(NOLOCK) INNER JOIN [dbo].[ActivityResult] [Ar] WITH(NOLOCK) ON ([Act].[ResultId] = [Ar].[Id]) WHERE [Act].[OpportunityId] = [O].[Id] AND [Ar].[Id] = @P2 AND [Act].[TypeId] = '{E1831DEC-CFC0-DF11-B00F-001D60E938C6}') [OpportunityCallsTotal], ( SELECT COUNT(*) [cnt] FROM [dbo].[Activity] [Act] WITH(NOLOCK) INNER JOIN [dbo].[ActivityResult] [Ar] WITH(NOLOCK) ON ([Act].[ResultId] = [Ar].[Id]) WHERE [Act].[AccountId] = [O].[AccountId] AND [Act].[CreatedOn] < [OIS].[CloseDate] AND [Ar].[Id] = @P3 AND [Act].[TypeId] = '{E3831DEC-CFC0-DF11-B00F-001D60E938C6}') [AccountMeetingsTotal], ISNULL(( SELECT DATEDIFF(DAY, MAX([Act].[StartDate]), [OIS].[CloseDate]) [cnt] FROM [dbo].[Activity] [Act] WITH(NOLOCK) WHERE [Act].[AccountId] = [O].[AccountId] AND [Act].[TypeId] = '{E3831DEC-CFC0-DF11-B00F-001D60E938C6}'), 0) [AccountDaysToLastMeeting], ( SELECT COUNT(*) [cnt] FROM [dbo].[Activity] [Act] WITH(NOLOCK) INNER JOIN [dbo].[ActivityResult] [Ar] WITH(NOLOCK) ON ([Act].[ResultId] = [Ar].[Id]) WHERE [Act].[AccountId] = [O].[AccountId] AND [Act].[CreatedOn] < [OIS].[CloseDate] AND [Ar].[Id] = @P4 AND [Act].[TypeId] = '{E1831DEC-CFC0-DF11-B00F-001D60E938C6}') [AccountCallsTotal], ISNULL(( SELECT DATEDIFF(DAY, MAX([Act].[StartDate]), [OIS].[CloseDate]) [cnt] FROM [dbo].[Activity] [Act] WITH(NOLOCK) WHERE [Act].[AccountId] = [O].[AccountId] AND [Act].[TypeId] = '{E1831DEC-CFC0-DF11-B00F-001D60E938C6}'), 0) [AccountDaysToLastCall], ( SELECT COUNT(*) [cnt] FROM [dbo].[Activity] [Act] WITH(NOLOCK) INNER JOIN [dbo].[ActivityResult] [Ar] WITH(NOLOCK) ON ([Act].[ResultId] = [Ar].[Id]) WHERE [Act].[AccountId] = [O].[AccountId] AND [Act].[CreatedOn] < [OIS].[CloseDate] AND [Ar].[Id] = @P5 AND [Act].[TypeId] = '{E2831DEC-CFC0-DF11-B00F-001D60E938C6}') [AccountEmailsTotal], ISNULL(( SELECT DATEDIFF(DAY, MAX([Act].[StartDate]), [OIS].[CloseDate]) [cnt] FROM [dbo].[Activity] [Act] WITH(NOLOCK) WHERE [Act].[AccountId] = [O].[AccountId] AND [Act].[TypeId] = '{E2831DEC-CFC0-DF11-B00F-001D60E938C6}'), 0) [AccountDaysToLastEmail] FROM [dbo].[Opportunity] [O] WITH(NOLOCK) INNER JOIN ( SELECT MAX([OS].[StartDate]) [StartDateLastStage], MAX([OS].[DueDate]) [CloseDate], [OS].[OpportunityId] [OpportunityId] FROM [dbo].[OpportunityInStage] [OS] WITH(NOLOCK) GROUP BY [OS].[OpportunityId]) [OIS] ON ([OIS].[OpportunityId] = [O].[Id]) LEFT OUTER JOIN [dbo].[Account] [A] WITH(NOLOCK) ON ([O].[AccountId] = [A].[Id]) WHERE [O].[StageId] IN ('{60D5310C-5BE6-DF11-971B-001D60E938C6}', '{A9AAFDFE-2242-4F42-8CD5-2AE3B9556D79}', '{736F54FD-E240-46F8-8C7C-9066C30AFF59}') ORDER BY [O].[CreatedOn] DESC

Is it a known issue ? did i miss something ?

 

Best regards

Patrice

Like 0

Like

4 comments
Best reply

Hello Patrice,

 

it seems like you added some data to the model which cannot be correctly proccessed. There is a chance it was a column with the type "Date" which cannot be operated automatically.

 

To fix this, you need to delete the "StartDateLastStage" column from the request for the machine learning model.

 

Regards,

Gleb.

Hello Patrice,

 

it seems like you added some data to the model which cannot be correctly proccessed. There is a chance it was a column with the type "Date" which cannot be operated automatically.

 

To fix this, you need to delete the "StartDateLastStage" column from the request for the machine learning model.

 

Regards,

Gleb.

Hi Gleb,

thanks for your answer.

in "Advanced tools for adding columns", i deleted the StartDateLastStage part in the sql query : 

the model works now.

Though I'm not sure what happened, as the model is a OOB one which was not modified from our side.

 

Best regards

Patrice

Patrice-ABPost,

 

thnks for letting me know.

I will pass this information to the dev team so they can take a look.

 

Regards,

Gleb.

 

mmh, after all it does not seems to work so well :

the model testing is ok, but when using [Predict data] process element, an error occur, i must have messed up the model.

Maybe i should start from a fresh new model, or maybe use the 7.14.0 version of the model, which is available in the model collection.

 

System.AggregateException: One or more errors occurred. ---&gt; Terrasoft.Web.Http.Abstractions.HttpException: [InternalServerError] {"responseStatus":{"errorCode":"InternalServerError","message":"Engine: [UsageException] ['CreatedOn']: Column(s) ['CreatedOn']] contain missing values"}} ---&gt; System.Exception: {"responseStatus":{"errorCode":"InternalServerError","message":"Engine: [UsageException] ['CreatedOn']: Column(s) ['CreatedOn']] contain missing values"}}
   --- End of inner exception stack trace ---
   at Terrasoft.Configuration.ML.MLServiceProxy.TransformResponseErrorByStatus(IRestResponse response)
   at Terrasoft.Configuration.ML.MLServiceProxy.Post[T](String methodName, Object requestBody, Int32 timeoutSec)
   at Terrasoft.Configuration.ML.MLServiceProxy.Predict[TOut](Guid modelInstanceUId, String methodName, PredictionInput predictionParams, Int32 timeoutSec)
   at Terrasoft.Configuration.ML.MLServiceProxy.Predict[TOut](Guid modelInstanceUId, IList`1 dataList, String methodName, DatasetInput defaultPredictionParams, Int32 timeoutSec)
   at Terrasoft.Configuration.ML.MLServiceProxy.Score(MLModelConfig model, IList`1 dataList, Boolean predictContributions)
   at Terrasoft.Configuration.ML.MLBaseEntityPredictor`1.Predict(MLModelConfig modelConfig, IList`1 dataList, MLDataPredictionUserTask predictionUserTask)
   at Terrasoft.Configuration.ML.MLBatchPredictor`1.Predict(MLModelConfig model, IList`1 dataList, MLDataPredictionUserTask predictionUserTask)
   at Terrasoft.Configuration.ML.MLBatchPredictor`1.&lt;&gt;c__DisplayClass17_0.&lt;Predict&gt;b__0(IList`1 dataForPrediction)
   --- End of inner exception stack trace ---
   at Terrasoft.Configuration.ML.MLBatchPredictor`1.Predict(MLModelConfig modelConfig, Action`1 chunkPredictedHandler, MLDataPredictionUserTask predictionUserTask)
   at Terrasoft.Configuration.ML.MLBatchPredictionJob.PredictAndSaveResult(MLModelConfig model, UserConnection userConnection, MLDataPredictionUserTask predictionUserTask)
   at Terrasoft.Configuration.ML.MLBatchPredictionJob.ProcessModel(UserConnection userConnection, Guid modelId, String filterData, MLDataPredictionUserTask predictionUserTask)
   at Terrasoft.Core.Process.Configuration.MLDataPredictionUserTask.InternalExecute(ProcessExecutingContext context)
   at Terrasoft.Core.Process.ProcessActivity.RunInCultureScope[T](Func`1 action)
   at Terrasoft.Core.Process.ProcessActivity.ExecuteElement(ProcessExecutingContext context)
   at Terrasoft.Core.Process.ProcessActivity.ExecuteItem(ProcessExecutingContext context)
   at Terrasoft.Core.Process.ProcessFlowElement.Execute(ProcessExecutingContext context)
---&gt; (Inner Exception #0) Terrasoft.Web.Http.Abstractions.HttpException (0x80004005): [InternalServerError] {"responseStatus":{"errorCode":"InternalServerError","message":"Engine: [UsageException] ['CreatedOn']: Column(s) ['CreatedOn']] contain missing values"}} ---&gt; System.Exception: {"responseStatus":{"errorCode":"InternalServerError","message":"Engine: [UsageException] ['CreatedOn']: Column(s) ['CreatedOn']] contain missing values"}}
   at Terrasoft.Configuration.ML.MLServiceProxy.TransformResponseErrorByStatus(IRestResponse response)
   at Terrasoft.Configuration.ML.MLServiceProxy.Post[T](String methodName, Object requestBody, Int32 timeoutSec)
   at Terrasoft.Configuration.ML.MLServiceProxy.Predict[TOut](Guid modelInstanceUId, String methodName, PredictionInput predictionParams, Int32 timeoutSec)
   at Terrasoft.Configuration.ML.MLServiceProxy.Predict[TOut](Guid modelInstanceUId, IList`1 dataList, String methodName, DatasetInput defaultPredictionParams, Int32 timeoutSec)
   at Terrasoft.Configuration.ML.MLServiceProxy.Score(MLModelConfig model, IList`1 dataList, Boolean predictContributions)
   at Terrasoft.Configuration.ML.MLBaseEntityPredictor`1.Predict(MLModelConfig modelConfig, IList`1 dataList, MLDataPredictionUserTask predictionUserTask)
   at Terrasoft.Configuration.ML.MLBatchPredictor`1.Predict(MLModelConfig model, IList`1 dataList, MLDataPredictionUserTask predictionUserTask)
   at Terrasoft.Configuration.ML.MLBatchPredictor`1.&lt;&gt;c__DisplayClass17_0.&lt;Predict&gt;b__0(IList`1 dataForPrediction)&lt;---

 

Show all comments