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
