Creating a Query for Closed Opportunities in Microsoft Dynamics CRM

Creating a Query for Closed Opportunities in Microsoft Dynamics CRM

Visibility into lost opportunities is just as important as won and open opportunities in Microsoft Dynamics CRM. Each plays a vital role in predicting your future sales pipeline. Analyzing your lost opportunities allows you to change your sales strategy based on why you are losing opportunities. For example, if you see a trend in lost opportunities from a referral partner, you may want to review your onboarding process of a referred lead.

This article will teach you how to view your closed-as-lost opportunities in CRM. Because Microsoft CRM classifies the action of clicking Close as Lost as an activity, this opportunity query will be performed based on activities.

To start, first open the advanced find dialog and under Look for choose Activities. Then you will want to select Activity Type equals and then choose the Opportunity Close activity type.

To be sure that you are only returning the most recent or final Opportunity Close activity for each opportunity, add to the query that the Activity Status equals Active.

We will further narrow our query by showing only Opportunity Close activities where the Regarding (Opportunity) was closed as lost. To do this, we will need to filter on Regarding (Opportunity) under the related section of a select statement. We will then filter on Status equals Lost. We can also add other Opportunity related filters such as Actual Close date and Opportunity Type.

Select Regarding (Opportunity) from the related section of the select dropdown. Then click the select dropdown under the newly added Regarding (Opportunity) section. This will allow us to select Opportunity fields to add to the filter. Lastly, create the statement for Status Equals Lost.

Our query now contains all the Opportunity Close activities that are related to a lost opportunity. We can also add additional filtering to give us better insight. In the example below, we see my lost opportunity close activities in the last 90 days in Milwaukee.


Leave a comment!

You must be logged in to post a comment.