Weekly High Value Opportunities Digest in Dynamics 365

In this article, the sixth in the series of articles devoted to Everyday Power Automate Solutions for Dynamics 365, we will build a simple flow that sends a weekly email containing the top five open opportunities (high-value sales opportunities) with the highest estimated revenue created during the previous seven days.

Andrei is a highly skilled Project Manager, Microsoft-certified Consultant, and hands-on experienced Developer. He runs numerous projects, participates in industry events, and has extensive experience delivering Dynamics 365 Sales and Dynamics 365 Customer Service implementations, using Power Automate, Power Apps, Power Portals, Power BI Copilot Studio, and Microsoft AI, combining strong strategic leadership with deep technical expertise.

Opportunities are a main source of information for sales managers. They help track potential revenue, monitor the sales pipeline, and identify the biggest business opportunities. However, in busy sales environments, managers often don't have time to review every new opportunity created during the week, especially when dozens of records are added every day.

An effective way to solve this is by providing a summary that highlights only the opportunities that deserve immediate attention. Instead of manually filtering views or building reports, Power Automate can prepare this information automatically. This approach can also simplify the creation of sales opportunities reports in Dynamics 365, giving managers a concise overview of the most important opportunities and helping them focus on deals that may require immediate action.

In the previous article from this series, Dynamics 365 Opportunity Management: Detecting and Notifying Missing Fields, we explored how to build a simple Power Automate flow that automatically reminds opportunity owners when an open opportunity has passed its expected close date. In this article, let's look at another request raised by a sales manager.

During one of our meetings, a sales manager explained that reviewing every new opportunity at the start of the week had become time-consuming. While most opportunities were relatively small deals, a few could significantly impact the sales pipeline. The manager wanted a quick way to identify these high-value opportunities without manually sorting and filtering records every Monday morning.

The requested solution was straightforward. Every Monday, the system should review all open opportunities created during the previous seven days, sort them by "Estimated Revenue" in descending order, and send an email containing the top five records. The email should give the manager a quick overview of the largest potential deals, so they can focus their attention on where it could have the greatest business impact.

Once you have accessed Power Automate and selected the appropriate environment, you can create the flow:

1. In the environment where you want to create the automation, choose the "Scheduled cloud flow" (1) to create a new flow.

   

2. Enter a meaningful name for the flow (2), configure the start date, time, and recurrence interval (3), and then click on the "Create" (4).

   

3. Before adding any actions, verify that the "Recurrence" trigger reflects the schedule configured in the previous step. Once that is confirmed, proceed with the following steps:

   1) Add a new "List rows" action. Since the goal is to review opportunities, select the "Opportunities" table in the "Table name" field (5).

   2) As we do not need to retrieve every column from the "Opportunity" table, limit the results to only the fields required for the review. In the "Select columns" field, enter the "Topic", "Estimated Revenue", "Created On", and "Status" (6).

   ! Note. If you would like to include a direct link to each opportunity in the email, you can use the same approach described in one of the previous articles in this series.

   3) Next, configure the "Filter rows" field. We only want to retrieve the active opportunities that have an "Estimated Revenue" value and were created during the last seven days (7).

   4) Now, since we are interested in the largest potential deals, we also need to sort out the results. In the "Sort By" field, sort the records by the "Estimated Revenue" in descending order (8).

   5) Finally, set a limit on the number of returned records. In the "Row count" field, enter "5", so the flow only retrieves the top five opportunities (9).

   

4. Now, let us create a string variable that will store all the information we want to include in the email (10).

   

5. The next step is to combine all the returned records into a single piece of text so that we can send one email instead of five separate notifications.

   There are several ways to achieve this, but since this series is intended for beginners and we are only processing five records, we will use the "Apply to each" action (11).

   

6. Inside the "Apply to each" action, add an "Append to string variable" step (12). In this example, we are using the following expression:

   CodeCopy

   concat(items('Apply_to_each')?['name'],' ',items('Apply_to_each')?['estimatedvalue'],'<br>')

   This expression combines multiple values into a single string that will later be used in the email body.

   ! Note. If you concatenate multiple fields, do not forget to add spaces or other separators where needed. Since the email body supports HTML formatting, we also recommend adding a "<br>" tag at the end of each line to make the final email easier to read.

   

7. Now, configure the email notification:

   1) Add a new "Send an email" action

   2) Enter the manager's email address in the "To" field

   3) Create a subject, switch the email body to HTML format, and use the string variable as the main content of the message

   

   From this point on, every Monday at 9:00 AM the manager will receive a summary email containing the five highest-value open opportunities created during the previous seven days.

UDS Blog articles:

• Dynamics 365 Opportunity Management: Detecting and Notifying Missing Fields
• Reminding about an Overdue Opportunity to Close Dates in Dynamics 365 CRM
• Detecting Duplicated Leads by Email in Dynamics 365 CRM
• Identifying and Automating Inactive Leads in Dynamics 365 CRM
• Managing Forgotten Leads in Dynamics 365 CRM
• Power Automate Solution: Instant Teams Notifications for New Contact Registrations