> Continuous Process Improvement > Lead to Cash > Microsoft Dynamics 365 Sales > Data Template

Your Lead to Cash Data Template

Microsoft Dynamics 365 Sales

Lead to Cash Attributes (19) Lead to Cash Activities (14) Extraction Guides (4)

Your Lead to Cash Data Template

This template provides a structured approach to collecting the essential data for your Lead to Cash process analysis. It outlines the crucial attributes, key activities, and provides guidance on how to extract this information. By following these recommendations, you can build a comprehensive event log for powerful process insights.

Recommended attributes for data collection
Key activities to track for process discovery
Practical guidance for data extraction

New to event logs? Learn how to create a process mining event log.

Download Template

Lead to Cash Attributes

These data fields are essential for creating a complete event log, enabling detailed analysis of your Lead to Cash process.

5 Required 8 Recommended 6 Optional

	Name	Description
	Activity ActivityName	The name of the business activity or event that occurred at a specific point in the sales process.
Description This attribute describes a specific step or milestone within the Lead to Cash process, such as 'Quote Created' or 'Payment Received'. Each activity represents a distinct event in the lifecycle of a sales opportunity. Analyzing the sequence and frequency of these activities is the core of process mining. It reveals the actual process flows, helps identify common and alternative paths (variants), and pinpoints where rework or bottlenecks occur. This analysis is crucial for understanding process adherence and efficiency. Why it matters Defines the steps in the process map, which is the foundation of all process mining analysis for discovering inefficiencies and deviations. Where to get This attribute is typically derived from various events related to the Opportunity, Quote, Order, and Invoice entities within Dynamics 365. It often requires logic to map status changes or record creation events to standardized activity names. Examples Opportunity CreatedQuote Sent to CustomerContract SignedPayment Received
	Sales Opportunity SalesOpportunity	The unique identifier for a sales opportunity, serving as the primary case ID for the Lead to Cash process.
Description The Sales Opportunity ID uniquely identifies each potential sale as it progresses through the sales funnel. It acts as the central thread that connects all related activities, from the initial creation of the opportunity to the final payment collection. In process mining, this attribute is fundamental for reconstructing the end-to-end journey of each sales case. It allows for the analysis of the entire process flow, calculation of total cycle times, and comparison of different paths that opportunities take, ultimately enabling the identification of inefficiencies and bottlenecks from lead qualification to cash receipt. Why it matters This is the essential Case ID that links all process events together, making it possible to trace the full lifecycle of each sales deal. Where to get This is the primary key of the Opportunity entity, typically the 'opportunityid' field. Examples OPP-01532-A3B1C7OPP-01533-F8D2E5OPP-01534-G9H3I4
	Start Time ActivityStartTime	The timestamp indicating when a specific activity or event began.
Description This attribute records the date and time that an activity occurred. It is the primary temporal component used in process mining to order events chronologically and calculate durations. The Start Time is essential for all time-based analyses, including calculating cycle times between activities, identifying delays, and measuring overall process performance. It enables the creation of a dynamic process animation and helps in understanding the temporal distribution of work. Why it matters This timestamp is critical for ordering events and calculating all performance metrics, such as cycle times and wait times. Where to get This is typically the 'createdon' or 'modifiedon' timestamp from the relevant entity (Opportunity, Quote, SalesOrder, Invoice) corresponding to the activity. Examples 2023-04-15T10:00:00Z2023-04-25T14:30:00Z2023-05-10T11:20:00Z
	Last Data Update LastDataUpdate	Timestamp of the last data refresh from the source system.
Description This attribute indicates the date and time when the data was last extracted from Microsoft Dynamics 365 Sales and loaded into the process mining tool. It provides a reference point for the freshness of the data being analyzed. Understanding the data's timeliness is critical for ensuring that analyses and conclusions are relevant and based on up-to-date information. It helps users trust the insights and make informed decisions based on the current state of the process. Why it matters Indicates the freshness of the data, ensuring that analyses are current and relevant for decision-making. Where to get This timestamp is generated by the data extraction and loading process itself. Examples 2024-07-27T02:00:00Z2024-07-28T02:00:00Z
	Source System SourceSystem	The system from which the data was extracted.
Description This attribute identifies the source application where the event data originated. For this process, it will consistently be Microsoft Dynamics 365 Sales. In environments with multiple integrated systems, this field is crucial for data lineage and troubleshooting. It helps in understanding the context of the data and ensures that analyses are based on information from the correct source. Why it matters Provides critical context about data origin, essential for data governance, validation, and managing integrations. Where to get This is a static value that should be populated during the data extraction process to label the source of the records. Examples Microsoft Dynamics 365 Sales
	Customer CustomerId	The customer account associated with the sales opportunity.
Description This attribute identifies the prospective or existing customer for whom the opportunity is being pursued. It links the sales process to a specific account in the CRM system. Analyzing the process by customer or customer segment allows for a deeper understanding of how different types of customers interact with the sales process. It can reveal if certain customers have longer cycle times, require more rework, or have higher win rates. This information is valuable for tailoring sales approaches and managing customer relationships. Why it matters Enables analysis of process performance for specific customers or customer segments, helping to tailor sales strategies. Where to get This is the 'customerid' lookup field on the Opportunity entity, which links to an Account or Contact record. Examples Contoso LtdAdventure WorksFabrikam, Inc.
	End Time ActivityEndTime	The timestamp indicating when a specific activity or event was completed. Often the same as the Start Time for atomic events.
Description The End Time marks the completion of an activity. For instantaneous events like 'Invoice Generated', the End Time is often the same as the Start Time. For activities with a measurable duration, such as a manual review step, this timestamp captures when the work was finished. This attribute is used to calculate the processing time of individual activities. By comparing the End Time of one activity with the Start Time of the next, it's possible to analyze both the active work time (processing time) and the idle time (wait time) within the process. Why it matters Enables the calculation of activity processing times, distinguishing active work time from waiting time and providing a more granular view of process performance. Where to get For instantaneous events, this is the same as the Start Time. For activities with a duration, it may need to be derived from a subsequent timestamp or status change. Examples 2023-04-15T10:00:00Z2023-04-25T15:00:00Z2023-05-10T11:25:00Z
	Lead Source LeadSource	The source from which the initial lead was generated.
Description This attribute indicates the origin of the lead that initiated the sales opportunity, such as 'Web', 'Partner', 'Trade Show', or 'Advertisement'. It tracks the effectiveness of different marketing channels and lead generation strategies. In process mining, analyzing by Lead Source is crucial for the 'Lead Conversion Performance' dashboard. It helps calculate the Lead to Opportunity Conversion Rate for each channel and identify which sources produce the most valuable leads or have the fastest qualification times. These insights are vital for optimizing marketing spend and sales focus. Why it matters Helps evaluate the performance of different marketing channels in generating quality leads, directly supporting lead conversion analysis. Where to get This is typically the 'leadsourcecode' field on the Lead entity, which is then transferred to the Opportunity upon qualification. Examples WebPartnerTrade ShowReferral
	Opportunity Owner OwnerId	The sales representative or user responsible for the opportunity.
Description The Opportunity Owner is the individual user in Microsoft Dynamics 365 Sales assigned to manage and advance the sales opportunity. This user is typically responsible for the majority of activities related to the case. Analyzing the process by owner helps to compare performance across different sales representatives, teams, or departments. It can reveal best practices from top performers, identify coaching opportunities for others, and understand how workload distribution impacts process efficiency and outcomes. Why it matters Allows for performance analysis by user or team, helping to identify top performers and diagnose issues related to specific individuals or groups. Where to get This is the 'ownerid' field on the Opportunity entity, which links to a User or Team record. Examples John DoeJane SmithEMEA Sales Team
	Opportunity Status OpportunityStatus	The final outcome of the sales opportunity, such as Won or Lost.
Description This attribute captures the terminal state of a sales opportunity. The primary outcomes are typically 'Won', indicating a successful sale, or 'Lost', indicating that the deal did not close. Analyzing by outcome is fundamental to understanding what process patterns lead to success versus failure. By filtering cases by status, one can compare cycle times, activities, and user involvement for won versus lost deals. This is key for calculating the Opportunity Win Rate KPI and identifying behaviors that correlate with positive outcomes. Why it matters Distinguishes between successful and unsuccessful outcomes, enabling comparative analysis to identify what process behaviors lead to wins. Where to get This corresponds to the 'statecode' (Status) and 'statuscode' (Status Reason) fields on the Opportunity entity. Examples WonLostIn Progress
	Opportunity Value OpportunityValue	The estimated or actual monetary value of the sales opportunity.
Description This attribute represents the potential or final revenue associated with a sales opportunity. It may start as an estimated value and be updated to the actual contract value when the deal is won. This is a critical dimension for analysis, as not all opportunities are equal. By segmenting the process based on deal size, companies can see if larger deals follow different processes or take longer to close. It also allows for prioritizing process improvement efforts on high-value segments of the business. Why it matters Provides financial context, allowing for analysis based on deal size and prioritization of process improvements for high-value opportunities. Where to get Typically found in fields like 'estimatedvalue' (Est. Revenue) or 'actualvalue' (Actual Revenue) on the Opportunity entity. Examples 50000.00125000.0025000.00
	Region Region	The geographical region or territory where the customer is located.
Description This attribute specifies the geographic area associated with the sales opportunity, such as 'North America', 'EMEA', or 'APAC'. It is often derived from the customer's address information. Region is a key dimension for comparative analysis. It is used in dashboards like 'Invoice Generation And Delivery Time' to see if there are regional variations in process performance. Such analysis can uncover differences caused by local regulations, team performance, or market conditions, informing geographically-targeted process improvements. Why it matters Allows for geographical segmentation of the process, highlighting regional differences in performance, compliance, and customer behavior. Where to get This data is typically stored on the related Account or Contact entity, in fields like 'address1_country' or a custom 'Region' field. Examples North AmericaEMEAAPACLATAM
	Sales Stage SalesStage	The stage of the opportunity within the defined sales process pipeline.
Description The Sales Stage represents the specific phase an opportunity is in, such as 'Qualify', 'Develop', 'Propose', or 'Close'. These stages provide a high-level overview of the sales pipeline and progress. While process mining derives detailed activities from timestamps, the Sales Stage provides valuable business context. It is used in the 'Overall Sales Cycle Time Analysis' dashboard to segment the process and understand how much time is spent in each major stage. This helps identify which stages are the longest and where opportunities tend to get stuck. Why it matters Provides business context about the opportunity's progress, allowing for analysis of time spent in each phase of the sales pipeline. Where to get This information is derived from the 'stepname' field related to the Business Process Flow associated with the Opportunity entity. Examples 1-Qualify2-Develop3-Propose4-Close
	Contract Type ContractType	The type of legal contract associated with the sale, such as 'New Business' or 'Renewal'.
Description This attribute categorizes the contract associated with a sales opportunity. Different types of contracts, like a new customer agreement versus a renewal or an upsell, may follow different approval and signature processes. The 'Contract Signing Cycle Time' dashboard uses this attribute to segment analysis. By comparing the signing times for different contract types, a business can identify if renewals are processed faster than new contracts, or if complex custom contracts create significant delays. This helps target improvements to the contract management process. Why it matters Helps to analyze and optimize the contract signing phase by identifying if certain types of contracts cause delays in the process. Where to get This would likely be a custom field on the Opportunity or Contract entity, as it is specific to business needs. Examples New BusinessRenewalExpansionMaster Service Agreement
	Discount Percentage DiscountPercentage	The discount percentage applied to the quote or order.
Description This attribute captures the total discount offered on a sales quote or order as a percentage of the list price. It is a key metric for monitoring sales margins and pricing discipline. The 'Quote Management And Discount Compliance' dashboard uses this attribute to analyze discounting behavior. By visualizing the distribution of discounts, management can identify excessive or unauthorized discounting, assess the impact of discounts on win rates, and ensure compliance with pricing policies. Why it matters Crucial for analyzing pricing strategy and discount compliance, helping to understand the impact of discounts on profitability and win rates. Where to get Located on the Quote ('discountpercentage') and SalesOrder ('discountpercentage') entities. Examples 0.050.100.15
	Is Rework IsRework	A flag indicating if an activity is a repetition or rework within the same case.
Description This boolean attribute is a calculated field that identifies when an activity or a sequence of activities is repeated within a single sales opportunity. For example, if a quote is created, rejected, and then a new quote is created, the second 'Quote Created' activity would be flagged as rework. Identifying rework is crucial for the 'Process Variant And Rework Analysis' dashboard. It helps quantify the extent of inefficiency in the process by highlighting loops and repeated steps. Understanding where and why rework occurs is the first step toward simplifying the process, reducing wasted effort, and accelerating the sales cycle. Why it matters Directly flags process inefficiencies and loops, helping to quantify the cost of rework and identify opportunities for process simplification. Where to get This is a calculated attribute. It is derived within the process mining tool by analyzing the sequence of activities for each case. Examples truefalse
	Payment Terms PaymentTerms	The agreed-upon payment terms for the invoice, such as Net 30 or Net 60.
Description This attribute specifies the conditions under which a customer is expected to pay for the goods or services rendered. It defines the timeframe for payment after the invoice is issued. For the 'Payment Collection Cycle Analysis' dashboard, this attribute is essential. It allows for segmenting payment collection times by the agreed terms, making it possible to identify which customers are chronically late payers and whether certain payment terms are associated with longer collection cycles. This helps in optimizing accounts receivable processes and managing cash flow. Why it matters Provides the baseline for analyzing payment performance, helping to identify late payments and assess the effectiveness of collection strategies. Where to get This is typically stored on the Invoice entity, often in a field named 'paymenttermscode'. Examples Net 30Net 60Due on receipt
	Processing Time ProcessingTime	The duration of an individual activity, calculated as the difference between its end and start time.
Description Processing Time, or activity duration, measures the time spent actively working on a specific task. It is calculated by subtracting the Activity Start Time from the Activity End Time. This metric helps distinguish between active work time and idle/wait time. In analysis, it is used to identify which specific activities are the most time-consuming in the entire process. Pinpointing activities with long processing times is a key step toward streamlining operations and improving resource efficiency. Why it matters Measures the active work duration for activities, helping to pinpoint which specific tasks are the most time-consuming and are targets for optimization. Where to get This is a calculated field, derived by subtracting 'ActivityStartTime' from 'ActivityEndTime'. Examples 8640036000
	Product Category ProductCategory	The category of the product or service being sold in the opportunity.
Description This attribute classifies the main product or service associated with the sales opportunity. For example, 'Hardware', 'Software', or 'Professional Services'. Analyzing the process by Product Category is essential for the 'Order Fulfillment Performance' dashboard. It helps determine if certain types of products have longer sales cycles, different fulfillment processes, or lower win rates. This insight can lead to specialized sales processes for different product lines to improve efficiency. Why it matters Enables performance analysis based on what is being sold, which can reveal process variations and bottlenecks specific to certain products. Where to get This information is typically available from the Opportunity Product (opportunityproduct) entity associated with the Opportunity. Examples Cloud ServicesOn-Premise SoftwareConsulting Services

Required Recommended Optional

Lead to Cash Activities

These are the critical process steps and milestones to capture in your event log, providing a clear picture of your Lead to Cash journey.

7 Recommended 7 Optional

	Activity	Description
	Opportunity Created	Marks the beginning of the sales process for a specific potential deal. This event is captured when a new Sales Opportunity record is created, often as a result of qualifying a Lead.
Why it matters This is the primary start activity for the Lead to Cash process analysis. It allows for measuring the total sales cycle time and analyzing the effectiveness of lead conversion. Where to get This is an explicit event captured from the creation timestamp of the Opportunity record in the Opportunity table. Capture Use the 'createdon' timestamp from the Opportunity entity. Event type explicit
	Opportunity Lost	The sales opportunity was not successful and has been closed without a sale. This is captured when the opportunity's status is changed to 'Lost'.
Why it matters This is the primary failure end activity. Analyzing these cases helps identify reasons for lost deals, points of churn in the sales funnel, and competitor effectiveness. Where to get Inferred from the timestamp when the 'statecode' on the Opportunity record is changed to 'Lost' (value 2). Capture Use the 'actualclosedate' field or 'modifiedon' timestamp when the Opportunity 'statuscode' is set to 'Lost'. Event type inferred
	Opportunity Won	The sales opportunity has been successfully closed, resulting in a sale. This event is captured when the opportunity's status is changed to 'Won'.
Why it matters This is the primary successful end activity for the sales process. It is used to calculate win rates, sales cycle times for successful deals, and overall sales performance. Where to get Inferred from the timestamp when the 'statecode' on the Opportunity record is changed to 'Won' (value 1). This is often done automatically when the associated quote or order is processed. Capture Use the 'actualclosedate' field or 'modifiedon' timestamp when the Opportunity 'statuscode' is set to 'Won'. Event type inferred
	Payment Received	Full payment for the invoice has been received from the customer. This is captured when the status of the Invoice record is marked as 'Paid'.
Why it matters This is a critical financial milestone representing revenue realization. Analyzing the time from invoice sent to payment received helps optimize the payment collection cycle. Where to get Inferred from the timestamp when the 'statecode' on the Invoice record changes to 'Paid' (value 2). Capture Use the 'modifiedon' timestamp when the Invoice 'statecode' is updated to 'Paid'. Event type inferred
	Quote Accepted by Customer	The customer has formally agreed to the terms and pricing presented in the quote. This is captured when the salesperson updates the status of the Quote record to 'Won'.
Why it matters This is a critical milestone indicating a firm commitment from the customer. It's essential for measuring quote-to-order conversion rates and the average quote approval time. Where to get Inferred from the timestamp when the 'statecode' on the Quote record is updated to 'Won' (value 2). Capture Use the 'modifiedon' timestamp when the Quote 'statecode' changes to 'Won'. Event type inferred
	Quote Created	A formal price quote for products or services has been generated for the customer. This is captured when a new Quote record is created and linked to the Sales Opportunity.
Why it matters This marks the transition from a conceptual proposal to a formal offer. The time between this and quote acceptance is critical for measuring quote management efficiency. Where to get An explicit event recorded as the creation date of a new record in the Quote entity, which has a lookup relationship to the parent Opportunity. Capture Use the 'createdon' timestamp from the Quote entity, linked to the Opportunity ID. Event type explicit
	Sales Order Created	A sales order is formally created in the system to manage the fulfillment of the products or services sold. This is an explicit event, typically generated by converting a 'Won' Quote into an Order.
Why it matters This marks the official handoff from sales to operations or fulfillment. It is the starting point for measuring order fulfillment cycle time and post-sale efficiency. Where to get This is an explicit event captured from the creation timestamp of the SalesOrder record, which is linked to the parent Opportunity. Capture Use the 'createdon' timestamp from the SalesOrder (Order) entity. Event type explicit
	Contract Signed	A legally binding contract has been signed by both parties, finalizing the agreement. This event is often a manual update to the Opportunity or a related Contract entity, possibly triggered by an e-signature integration.
Why it matters This activity is crucial for tracking the contract finalization cycle time. Delays at this stage can significantly impact revenue forecasting and project initiation. Where to get Inferred from a date field such as 'Contract Signed Date' being populated on the Opportunity, or a status change. This may require customization to exist. Capture Use the timestamp of a specific status change or the date a contract-related field is populated. Event type inferred
	Invoice Generated	A customer invoice has been created based on the fulfilled sales order. This is an explicit event recorded when a new Invoice record is created in the system.
Why it matters This activity starts the billing and payment collection cycle. The time from order fulfillment to invoice generation is a key indicator of billing process efficiency. Where to get This is an explicit event captured from the creation timestamp of the Invoice record, which is linked to the SalesOrder. Capture Use the 'createdon' timestamp from the Invoice entity. Event type explicit
	Invoice Sent to Customer	The invoice has been delivered to the customer for payment. This is typically inferred from a status change on the Invoice record.
Why it matters This starts the clock for payment terms and is the first step in measuring Days Sales Outstanding (DSO). Delays here directly impact cash flow. Where to get Inferred from a status change on the Invoice entity. While there is no standard 'Sent' status, a workflow often updates a custom field or status on this action. Capture Track a custom 'Sent Date' field or a status reason change on the Invoice record. Event type inferred
	Order Fulfilled	All products in the sales order have been shipped or all services have been delivered to the customer. This is captured when the status of the SalesOrder record is updated to 'Fulfilled' or a similar state.
Why it matters This activity concludes the delivery portion of the process. It is a key milestone for analyzing order fulfillment performance and is the trigger for invoicing. Where to get Inferred from the timestamp when the 'statecode' of the SalesOrder record changes to 'Fulfilled' (value 3). Capture Use the 'modifiedon' timestamp when the SalesOrder 'statecode' is updated to 'Fulfilled'. Event type inferred
	Quote Sent to Customer	The generated quote has been formally delivered to the customer for review. This event is typically inferred when the status of the Quote record is changed to 'Active'.
Why it matters This activity starts the clock on customer review and negotiation time. It is a key data point for analyzing quote approval cycles and identifying delays in customer response. Where to get Inferred from the timestamp when the 'statecode' on the Quote record changes to 'Active'. Capture Monitor the audit history or use a timestamp field updated by workflow when the Quote 'statecode' becomes 1 (Active). Event type inferred
	Requirements Identified	Represents the point where the sales team has gathered and understood the customer's needs. This is typically captured by a salesperson updating a stage in the Business Process Flow or changing the opportunity status.
Why it matters Tracking this stage helps identify how long it takes to move from initial contact to a viable proposal. Delays here can indicate issues with discovery calls or resource availability. Where to get Inferred from a change in the Opportunity's Business Process Flow stage to a 'Develop' or 'Propose' phase, or a custom status field update. The timestamp of the stage change is used. Capture Track changes to the 'stageid' in the Opportunity entity or related Business Process Flow entity. Event type inferred
	Solution Proposed	This activity signifies that a formal solution or proposal has been presented to the potential customer. It is typically inferred when the opportunity moves to a 'Propose' stage in its lifecycle.
Why it matters Analyzing the time spent before and after this activity helps evaluate the efficiency of the solution design and proposal generation steps. Repeated instances indicate rework in the sales cycle. Where to get Inferred from a status change on the Opportunity record or advancement to a 'Propose' stage in the associated Business Process Flow. Capture Use the timestamp of the status or stage change on the Opportunity entity. Event type inferred

Recommended Optional

Extraction Guides

How to get your data from Microsoft Dynamics 365 Sales

Steps

Prerequisites: Ensure you have an Azure subscription with contributor access, a Power Apps environment with system administrator privileges, and access to a Dynamics 365 Sales instance.
Create Azure Resources: In the Azure portal, create an Azure Synapse Analytics workspace and an Azure Data Lake Storage Gen2 account. Ensure the Synapse workspace has access to the storage account.
Link Power Apps Environment: Navigate to the Power Platform admin center (make.powerapps.com). Select 'Data' in the left navigation, then 'Azure Synapse Link'. Click '+ New link to data lake'.
Configure Synapse Link: Follow the wizard to connect to your Azure subscription, resource group, and the storage account created in step 2. Select the Synapse workspace to connect to.
Select Tables for Replication: Choose the specific Dataverse tables required for the Lead to Cash process. At a minimum, select: Opportunity, Quote, SalesOrder, Invoice, Account, SystemUser, and Contract. Add any other relevant custom tables. Initiate the synchronization.
Wait for Initial Synchronization: Allow the system time for the initial data copy to complete. The time required depends on the volume of data. You can monitor the status in the Azure Synapse Link dashboard.
Access Data in Synapse Studio: Open your Azure Synapse Studio. Navigate to the 'Data' hub. The replicated Dataverse tables will appear as external tables within a Lake Database.
Create a Serverless SQL Script: In the 'Develop' hub of Synapse Studio, create a new 'SQL script'. This is where you will run the provided query to transform the replicated data into an event log.
Execute the Event Log Query: Copy the complete T-SQL query provided in the 'query' section into the new script window. Adjust the placeholders for your database name and any custom logic.
Run the Query: Execute the query against the serverless SQL pool. The query will process the raw tables and generate a flattened event log structure with one row per business activity.
Save the Results: After the query successfully executes, use the 'Export' functionality in Synapse Studio to save the results as a CSV file.
Prepare for Upload: Ensure the exported CSV file headers match the required ProcessMind attribute names, for example, SalesOpportunity, ActivityName, and ActivityStartTime. Upload the final CSV file to ProcessMind.

Configuration

Azure Synapse Link Configuration: When setting up the link, you can choose 'Append only' mode. This mode is beneficial for process mining as it provides a historical record of changes, though it can increase storage consumption. For this query, standard 'In-place update' mode is sufficient.
Table Selection: Be selective with the tables you choose to replicate. Only include tables relevant to the Lead to Cash process to minimize synchronization time and storage costs. Key tables are: opportunity, quote, salesorder, invoice, contract, and related master data tables like account and systemuser.
Date Range Filtering: The provided query does not include a date filter by default. It is highly recommended to add a WHERE clause, typically on the opportunity.createdon date, to limit the analysis to a relevant period, such as the last 6 to 12 months. This significantly improves query performance.
Prerequisites: You must have appropriate permissions in both Azure (Contributor on the resource group) and Power Platform (System Administrator) to create and manage the Azure Synapse Link for Dataverse.

a Sample Query config

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Opportunity Created' AS ActivityName,
    opp.createdon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Requirements Identified' AS ActivityName,
    opp.modifiedon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE opp.salesstagecode = 2 -- 'Qualify' stage often indicates requirements are identified

UNION ALL

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Solution Proposed' AS ActivityName,
    opp.modifiedon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE opp.salesstagecode = 3 -- 'Propose' stage

UNION ALL

SELECT
    q.opportunityid AS SalesOpportunity,
    'Quote Created' AS ActivityName,
    q.createdon AS ActivityStartTime,
    q.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM quote AS q
JOIN opportunity AS opp ON q.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    q.opportunityid AS SalesOpportunity,
    'Quote Sent to Customer' AS ActivityName,
    q.modifiedon AS ActivityStartTime,
    q.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM quote AS q
JOIN opportunity AS opp ON q.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE q.statecode = 1 -- 'Active'

UNION ALL

SELECT
    q.opportunityid AS SalesOpportunity,
    'Quote Accepted by Customer' AS ActivityName,
    q.modifiedon AS ActivityStartTime,
    q.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM quote AS q
JOIN opportunity AS opp ON q.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE q.statecode = 2 -- 'Won'

UNION ALL

SELECT
    c.opportunityid AS SalesOpportunity,
    'Contract Signed' AS ActivityName,
    c.createdon AS ActivityStartTime,
    c.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM contract AS c
JOIN opportunity AS opp ON c.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    so.opportunityid AS SalesOpportunity,
    'Sales Order Created' AS ActivityName,
    so.createdon AS ActivityStartTime,
    so.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM salesorder AS so
JOIN opportunity AS opp ON so.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    so.opportunityid AS SalesOpportunity,
    'Order Fulfilled' AS ActivityName,
    so.modifiedon AS ActivityStartTime,
    so.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM salesorder AS so
JOIN opportunity AS opp ON so.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE so.statecode = 3 -- 'Fulfilled'

UNION ALL

SELECT
    inv.opportunityid AS SalesOpportunity,
    'Invoice Generated' AS ActivityName,
    inv.createdon AS ActivityStartTime,
    inv.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM invoice AS inv
JOIN opportunity AS opp ON inv.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    inv.opportunityid AS SalesOpportunity,
    'Invoice Sent to Customer' AS ActivityName,
    inv.modifiedon AS ActivityStartTime,
    inv.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM invoice AS inv
JOIN opportunity AS opp ON inv.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE inv.statecode = 1 -- 'Active'

UNION ALL

SELECT
    inv.opportunityid AS SalesOpportunity,
    'Payment Received' AS ActivityName,
    inv.modifiedon AS ActivityStartTime,
    inv.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM invoice AS inv
JOIN opportunity AS opp ON inv.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE inv.statecode = 2 -- 'Paid'

UNION ALL

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Opportunity Won' AS ActivityName,
    opp.modifiedon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE opp.statecode = 1 -- 'Won'

UNION ALL

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Opportunity Lost' AS ActivityName,
    opp.modifiedon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE opp.statecode = 2; -- 'Lost'

Steps

Enable TDS Endpoint: Ensure the Tabular Data Stream (TDS) endpoint is enabled for your Dynamics 365 Dataverse environment. This is managed in the Power Platform admin center under Environment settings > Features > TDS endpoint.
Identify Environment URL: Locate your organization's environment URL. It typically follows the format [org-name].crm.dynamics.com.
Connect with a SQL Client: Open a SQL client such as SQL Server Management Studio (SSMS) or Azure Data Studio. Start a new connection to the server.
Configure Connection: In the server name field, enter your environment URL followed by port 5558, for example, [org-name].crm.dynamics.com,5558. Select 'Azure Active Directory - Password' or 'Azure Active Directory - Integrated' for authentication and provide your Dynamics 365 credentials.
Prepare the Query: Open a new query window connected to your Dataverse environment database.
Customize the Query: Copy the SQL script provided in the 'query' section into the new query window. Locate the DECLARE statements at the top of the script and set the @StartDate and @EndDate variables to define the desired date range for the extraction.
Execute the Extraction: Run the SQL script. The query will collect data from various tables like opportunity, quote, salesorder, and invoice to build a comprehensive event log.
Review the Results: Once the query finishes, the results will be displayed in a tabular format in your SQL client. Briefly review the data to ensure it looks correct and contains the expected activities and attributes.
Export to CSV: Export the result set to a CSV file. In SSMS, you can right-click the result grid and select 'Save Results As...'.
Finalize for Upload: Ensure the saved CSV file is UTF-8 encoded and that the column headers match the requirements for your process mining tool. The file is now ready to be uploaded to ProcessMind.

Configuration

TDS Endpoint: The Tabular Data Stream (TDS) protocol setting must be enabled for the Dataverse environment. This provides a direct, read-only SQL connection.
User Permissions: The connecting user must have a security role in Dynamics 365 with read permissions for all tables referenced in the query, including opportunity, quote, salesorder, invoice, account, and systemuser.
Date Range Filtering: It is critical to filter the data using a specific date range to manage the query's scope and improve performance. The provided script uses @StartDate and @EndDate variables for this purpose. A range of 3 to 6 months is recommended for an initial analysis.
Performance Considerations: Executing this query against a large production environment can consume resources and may impact performance for other users. It is best practice to run large extractions during off-peak business hours.
SQL Client Requirement: A SQL client capable of connecting to Azure SQL Database using Azure Active Directory authentication is required. Common clients include SQL Server Management Studio (SSMS) v18 or later and Azure Data Studio.

a Sample Query sql

DECLARE @StartDate DATETIME = '2023-01-01T00:00:00';
DECLARE @EndDate DATETIME = '2024-01-01T00:00:00';

WITH FilteredOpportunities AS (
    SELECT 
        opportunityid
    FROM opportunity
    WHERE createdon >= @StartDate AND createdon < @EndDate
)

-- 1. Opportunity Created
SELECT
    o.opportunityid AS 'SalesOpportunity',
    'Opportunity Created' AS 'ActivityName',
    o.createdon AS 'ActivityStartTime',
    o.createdon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM opportunity o
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE o.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities)

UNION ALL

-- 2. Requirements Identified (Inferred from Sales Stage)
SELECT
    o.opportunityid AS 'SalesOpportunity',
    'Requirements Identified' AS 'ActivityName',
    o.modifiedon AS 'ActivityStartTime',
    o.modifiedon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM opportunity o
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE o.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND o.salesstagecode = 1 -- Qualify Stage

UNION ALL

-- 3. Solution Proposed (Inferred from Sales Stage)
SELECT
    o.opportunityid AS 'SalesOpportunity',
    'Solution Proposed' AS 'ActivityName',
    o.modifiedon AS 'ActivityStartTime',
    o.modifiedon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM opportunity o
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE o.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND o.salesstagecode = 2 -- Propose Stage

UNION ALL

-- 4. Quote Created
SELECT
    q.opportunityid AS 'SalesOpportunity',
    'Quote Created' AS 'ActivityName',
    q.createdon AS 'ActivityStartTime',
    q.createdon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM quote q
JOIN opportunity o ON q.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE q.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities)

UNION ALL

-- 5. Quote Sent to Customer
SELECT
    q.opportunityid AS 'SalesOpportunity',
    'Quote Sent to Customer' AS 'ActivityName',
    q.modifiedon AS 'ActivityStartTime',
    q.modifiedon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM quote q
JOIN opportunity o ON q.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE q.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND q.statecode = 1 -- Active

UNION ALL

-- 6. Quote Accepted by Customer
SELECT
    q.opportunityid AS 'SalesOpportunity',
    'Quote Accepted by Customer' AS 'ActivityName',
    q.modifiedon AS 'ActivityStartTime',
    q.modifiedon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM quote q
JOIN opportunity o ON q.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE q.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND q.statuscode = 4 -- Won

UNION ALL

-- 7. Contract Signed
SELECT 
    c.opportunityid AS 'SalesOpportunity',
    'Contract Signed' AS 'ActivityName',
    c.createdon AS 'ActivityStartTime',
    c.createdon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM contract c
JOIN opportunity o ON c.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE c.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities)

UNION ALL

-- 8. Sales Order Created
SELECT
    so.opportunityid AS 'SalesOpportunity',
    'Sales Order Created' AS 'ActivityName',
    so.createdon AS 'ActivityStartTime',
    so.createdon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM salesorder so
JOIN opportunity o ON so.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE so.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities)

UNION ALL

-- 9. Order Fulfilled
SELECT
    so.opportunityid AS 'SalesOpportunity',
    'Order Fulfilled' AS 'ActivityName',
    so.modifiedon AS 'ActivityStartTime',
    so.modifiedon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM salesorder so
JOIN opportunity o ON so.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE so.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND so.statecode = 3 -- Fulfilled

UNION ALL

-- 10. Invoice Generated
SELECT
    i.opportunityid AS 'SalesOpportunity',
    'Invoice Generated' AS 'ActivityName',
    i.createdon AS 'ActivityStartTime',
    i.createdon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM invoice i
JOIN opportunity o ON i.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE i.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities)

UNION ALL

-- 11. Invoice Sent to Customer
SELECT
    i.opportunityid AS 'SalesOpportunity',
    'Invoice Sent to Customer' AS 'ActivityName',
    i.modifiedon AS 'ActivityStartTime',
    i.modifiedon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM invoice i
JOIN opportunity o ON i.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE i.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND i.statecode = 1 -- Active

UNION ALL

-- 12. Payment Received
SELECT
    i.opportunityid AS 'SalesOpportunity',
    'Payment Received' AS 'ActivityName',
    i.modifiedon AS 'ActivityStartTime',
    i.modifiedon AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM invoice i
JOIN opportunity o ON i.opportunityid = o.opportunityid
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE i.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND i.statecode = 2 -- Paid

UNION ALL

-- 13. Opportunity Won
SELECT
    o.opportunityid AS 'SalesOpportunity',
    'Opportunity Won' AS 'ActivityName',
    o.actualclosedate AS 'ActivityStartTime',
    o.actualclosedate AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM opportunity o
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE o.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND o.statecode = 1 -- Won

UNION ALL

-- 14. Opportunity Lost
SELECT
    o.opportunityid AS 'SalesOpportunity',
    'Opportunity Lost' AS 'ActivityName',
    o.actualclosedate AS 'ActivityStartTime',
    o.actualclosedate AS 'ActivityEndTime',
    o.ownerid AS 'OwnerId',
    o.statuscode AS 'OpportunityStatus',
    o.actualvalue AS 'OpportunityValue',
    o.leadsourcecode AS 'LeadSource',
    o.salesstagecode AS 'SalesStage',
    o.customerid AS 'CustomerId',
    acc.address1_country AS 'Region',
    'Microsoft Dynamics 365 Sales' AS 'SourceSystem',
    GETUTCDATE() AS 'LastDataUpdate'
FROM opportunity o
LEFT JOIN account acc ON o.customerid = acc.accountid
WHERE o.opportunityid IN (SELECT opportunityid FROM FilteredOpportunities) AND o.statecode = 2; -- Lost

Steps

Register an Application in Azure Active Directory: Before you can connect to the Dynamics 365 Web API, you must register an application in your Azure AD tenant. This provides the necessary credentials for authentication. Navigate to Azure Active Directory > App registrations > New registration. Set the application as a Web app and add a client secret. Note the Application (client) ID, Directory (tenant) ID, and the Client Secret value.
Grant API Permissions: In the newly created App registration, go to API permissions > Add a permission. Select 'Dynamics CRM' or 'Dataverse'. Choose 'Delegated permissions', select 'user_impersonation', and click 'Add permissions'. Finally, grant admin consent for your directory.
Create a Dynamics 365 Application User: In the Power Platform admin center, select your environment. Navigate to Settings > Users + permissions > Application users. Create a new application user and link it to the Azure AD application you registered in step 1. Assign a security role, such as 'System Administrator', to this user to ensure it has sufficient permissions to read all required data.
Set Up Python Environment: Ensure you have Python installed on your system. Install the necessary libraries for authentication, HTTP requests, and data manipulation by running the following command: pip install requests msal pandas.
Configure the Python Script: Copy the provided Python script into a file, for example, extract_d365.py. Open the file and locate the configuration section at the top. Fill in the placeholder values for D365_URL, CLIENT_ID, CLIENT_SECRET, TENANT_ID, START_DATE, and END_DATE with your specific environment details and desired extraction period.
Customize Stage Names (If Necessary): The script infers some activities based on Business Process Flow stage names like 'Identify' and 'Propose'. Review the OData filter queries for activities like 'Requirements Identified' and 'Solution Proposed' and adjust the stage names to match your organization's custom sales process.
Execute the Extraction Script: Open a terminal or command prompt, navigate to the directory where you saved the script, and run it using the command: python extract_d365.py. The script will authenticate, query the API for each activity, and process the data.
Review the Output File: Upon successful execution, the script will generate a CSV file named d365_lead_to_cash_event_log.csv in the same directory. This file contains the extracted event log.
Prepare for Upload: Open the CSV file to ensure the headers and data format are correct. The file should have columns like SalesOpportunity, ActivityName, and ActivityStartTime. It is now ready to be uploaded to ProcessMind for analysis.

Configuration

Authentication Credentials: The script requires an Application (Client) ID, Client Secret, and Tenant ID from an Azure Active Directory App Registration to authenticate against the Dataverse API.
Dynamics 365 URL: You must provide the base URL for your Dynamics 365 Sales environment, for example, https://yourorg.api.crm.dynamics.com.
Date Range Filter: The START_DATE and END_DATE variables control the time window for the extraction. It is recommended to start with a 3 to 6 month period to manage the data volume and verify the results before attempting a larger extraction.
API Endpoint Version: The script targets version v9.2 of the Dataverse Web API. This is specified in the request URLs.
Pagination: The script requests a maximum of 5000 records per page (odata.maxpagesize=5000) to optimize performance and automatically follows the @odata.nextLink provided in the responses to retrieve all subsequent pages for a given query.
Required Permissions: The application user running the script must have a security role with read permissions for the following entities: Opportunity, Quote, SalesOrder, Invoice, Account, and SystemUser.

a Sample Query python

import requests
import msal
import pandas as pd
import datetime

class=class="hl-string">"hl-comment"># --- CONFIGURATION --- 
class=class="hl-string">"hl-comment"># Replace with your Dynamics 365 environment details
D365_URL = class="hl-string">"https://[yourorg].api.crm.dynamics.com"
CLIENT_ID = class="hl-string">"[your-application-client-id]"
CLIENT_SECRET = class="hl-string">"[your-client-secret]"
TENANT_ID = class="hl-string">"[your-directory-tenant-id]"

class=class="hl-string">"hl-comment"># Define the date range for the extraction
START_DATE = class="hl-string">"2023-01-01T00:00:00Z"
END_DATE = class="hl-string">"2023-12-31T23:59:59Z"

API_VERSION = class="hl-string">"v9.2"
AUTHORITY = fclass="hl-string">"https://login.microsoftonline.com/{TENANT_ID}"
SCOPE = [fclass="hl-string">"{D365_URL}/.default"]

def get_access_token():
    class="hl-string">"""Authenticates and retrieves an access token."""
    app = msal.ConfidentialClientApplication(CLIENT_ID, authority=AUTHORITY, client_credential=CLIENT_SECRET)
    result = app.acquire_token_for_client(scopes=SCOPE)
    if class="hl-string">"access_token" in result:
        return result[class="hl-string">'access_token']
    else:
        raise Exception(fclass="hl-string">"Error acquiring token: {result.get('error_description')}")

def fetch_all_data(access_token, query_url):
    class="hl-string">"""Fetches all pages of data for a given OData query."""
    all_records = []
    headers = {
        class="hl-string">'Authorization': fclass="hl-string">'Bearer {access_token}',
        class="hl-string">'OData-MaxVersion': class="hl-string">'4.0',
        class="hl-string">'OData-Version': class="hl-string">'4.0',
        class="hl-string">'Accept': class="hl-string">'application/json',
        class="hl-string">'Prefer': class="hl-string">'odata.include-annotations="*", odata.maxpagesize=5000'
    }
    while query_url:
        response = requests.get(query_url, headers=headers)
        response.raise_for_status()  class=class="hl-string">"hl-comment"># Raises an exception for bad status codes
        data = response.json()
        all_records.extend(data[class="hl-string">'value'])
        query_url = data.get(class="hl-string">'@odata.nextLink')
    return all_records

def main():
    class="hl-string">"""Main function to orchestrate the data extraction."""
    print(class="hl-string">"Acquiring access token...")
    access_token = get_access_token()
    print(class="hl-string">"Token acquired successfully.")

    event_log = []
    last_data_update = datetime.datetime.utcnow().isoformat() + class="hl-string">'Z'
    source_system = class="hl-string">'Microsoft Dynamics 365 Sales'

    base_url = fclass="hl-string">"{D365_URL}/api/data/{API_VERSION}"

    class=class="hl-string">"hl-comment"># 1. Opportunities: Created, Identified, Proposed, Won, Lost, Contract Signed
    print(class="hl-string">"Fetching Opportunity data...")
    opp_query = (
        fclass="hl-string">"{base_url}/opportunities?$select=opportunityid,name,createdon,modifiedon,actualclosedate,actualvalue,statecode,statuscode,stepname,_ownerid_value,_parentaccountid_value,leadsourcecode&"
        fclass="hl-string">"$expand=parentaccountid($select=address1_stateorprovince)&"
        fclass="hl-string">"$filter=createdon ge {START_DATE} and createdon le {END_DATE}"
    )
    opportunities = fetch_all_data(access_token, opp_query)
    for opp in opportunities:
        common_attrs = {
            class="hl-string">'SalesOpportunity': opp.get(class="hl-string">'opportunityid'),
            class="hl-string">'OwnerId': opp.get(class="hl-string">'_ownerid_value'),
            class="hl-string">'OpportunityStatus': opp.get(class="hl-string">'statecode@OData.Community.Display.V1.FormattedValue'),
            class="hl-string">'OpportunityValue': opp.get(class="hl-string">'actualvalue'),
            class="hl-string">'LeadSource': opp.get(class="hl-string">'leadsourcecode@OData.Community.Display.V1.FormattedValue'),
            class="hl-string">'SalesStage': opp.get(class="hl-string">'stepname'),
            class="hl-string">'CustomerId': opp.get(class="hl-string">'_parentaccountid_value'),
            class="hl-string">'Region': opp.get(class="hl-string">'parentaccountid', {}).get(class="hl-string">'address1_stateorprovince'),
            class="hl-string">'SourceSystem': source_system,
            class="hl-string">'LastDataUpdate': last_data_update
        }

        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Opportunity Created', class="hl-string">'ActivityStartTime': opp.get(class="hl-string">'createdon'), class="hl-string">'ActivityEndTime': opp.get(class="hl-string">'createdon')})
        
        class=class="hl-string">"hl-comment"># Note: class="hl-string">'Requirements Identified', class="hl-string">'Solution Proposed', and class="hl-string">'Contract Signed' are inferred from stage changes.
        class=class="hl-string">"hl-comment"># The class="hl-string">'stepname' may need to be adjusted based on your custom Business Process Flow.
        if opp.get(class="hl-string">'stepname') and class="hl-string">'identify' in opp.get(class="hl-string">'stepname', class="hl-string">'').lower():
             event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Requirements Identified', class="hl-string">'ActivityStartTime': opp.get(class="hl-string">'modifiedon'), class="hl-string">'ActivityEndTime': opp.get(class="hl-string">'modifiedon')})
        if opp.get(class="hl-string">'stepname') and class="hl-string">'propose' in opp.get(class="hl-string">'stepname', class="hl-string">'').lower():
             event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Solution Proposed', class="hl-string">'ActivityStartTime': opp.get(class="hl-string">'modifiedon'), class="hl-string">'ActivityEndTime': opp.get(class="hl-string">'modifiedon')})

        if opp.get(class="hl-string">'statecode') == 1: class=class="hl-string">"hl-comment"># Won
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Opportunity Won', class="hl-string">'ActivityStartTime': opp.get(class="hl-string">'actualclosedate'), class="hl-string">'ActivityEndTime': opp.get(class="hl-string">'actualclosedate')})
            if opp.get(class="hl-string">'stepname') and class="hl-string">'close' in opp.get(class="hl-string">'stepname', class="hl-string">'').lower():
                 event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Contract Signed', class="hl-string">'ActivityStartTime': opp.get(class="hl-string">'actualclosedate'), class="hl-string">'ActivityEndTime': opp.get(class="hl-string">'actualclosedate')})
        elif opp.get(class="hl-string">'statecode') == 2: class=class="hl-string">"hl-comment"># Lost
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Opportunity Lost', class="hl-string">'ActivityStartTime': opp.get(class="hl-string">'actualclosedate'), class="hl-string">'ActivityEndTime': opp.get(class="hl-string">'actualclosedate')})

    class=class="hl-string">"hl-comment"># 2. Quotes: Created, Sent, Accepted
    print(class="hl-string">"Fetching Quote data...")
    quote_query = (
        fclass="hl-string">"{base_url}/quotes?$select=quoteid,createdon,modifiedon,statecode,_opportunityid_value&"
        fclass="hl-string">"$filter=createdon ge {START_DATE} and createdon le {END_DATE} and _opportunityid_value ne null"
    )
    quotes = fetch_all_data(access_token, quote_query)
    for quote in quotes:
        common_attrs = {class="hl-string">'SalesOpportunity': quote.get(class="hl-string">'_opportunityid_value'), class="hl-string">'SourceSystem': source_system, class="hl-string">'LastDataUpdate': last_data_update}
        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Quote Created', class="hl-string">'ActivityStartTime': quote.get(class="hl-string">'createdon'), class="hl-string">'ActivityEndTime': quote.get(class="hl-string">'createdon')})
        if quote.get(class="hl-string">'statecode') == 1: class=class="hl-string">"hl-comment"># Active
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Quote Sent to Customer', class="hl-string">'ActivityStartTime': quote.get(class="hl-string">'modifiedon'), class="hl-string">'ActivityEndTime': quote.get(class="hl-string">'modifiedon')})
        elif quote.get(class="hl-string">'statecode') == 2: class=class="hl-string">"hl-comment"># Won
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Quote Accepted by Customer', class="hl-string">'ActivityStartTime': quote.get(class="hl-string">'modifiedon'), class="hl-string">'ActivityEndTime': quote.get(class="hl-string">'modifiedon')})

    class=class="hl-string">"hl-comment"># 3. Sales Orders: Created, Fulfilled
    print(class="hl-string">"Fetching Sales Order data...")
    order_query = (
        fclass="hl-string">"{base_url}/salesorders?$select=salesorderid,createdon,modifiedon,statecode,_opportunityid_value&"
        fclass="hl-string">"$filter=createdon ge {START_DATE} and createdon le {END_DATE} and _opportunityid_value ne null"
    )
    orders = fetch_all_data(access_token, order_query)
    for order in orders:
        common_attrs = {class="hl-string">'SalesOpportunity': order.get(class="hl-string">'_opportunityid_value'), class="hl-string">'SourceSystem': source_system, class="hl-string">'LastDataUpdate': last_data_update}
        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Sales Order Created', class="hl-string">'ActivityStartTime': order.get(class="hl-string">'createdon'), class="hl-string">'ActivityEndTime': order.get(class="hl-string">'createdon')})
        if order.get(class="hl-string">'statecode') == 3: class=class="hl-string">"hl-comment"># Fulfilled
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Order Fulfilled', class="hl-string">'ActivityStartTime': order.get(class="hl-string">'modifiedon'), class="hl-string">'ActivityEndTime': order.get(class="hl-string">'modifiedon')})

    class=class="hl-string">"hl-comment"># 4. Invoices: Generated, Sent, Paid
    print(class="hl-string">"Fetching Invoice data...")
    invoice_query = (
        fclass="hl-string">"{base_url}/invoices?$select=invoiceid,createdon,modifiedon,statecode,_opportunityid_value&"
        fclass="hl-string">"$filter=createdon ge {START_DATE} and createdon le {END_DATE} and _opportunityid_value ne null"
    )
    invoices = fetch_all_data(access_token, invoice_query)
    for invoice in invoices:
        common_attrs = {class="hl-string">'SalesOpportunity': invoice.get(class="hl-string">'_opportunityid_value'), class="hl-string">'SourceSystem': source_system, class="hl-string">'LastDataUpdate': last_data_update}
        event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Generated', class="hl-string">'ActivityStartTime': invoice.get(class="hl-string">'createdon'), class="hl-string">'ActivityEndTime': invoice.get(class="hl-string">'createdon')})
        if invoice.get(class="hl-string">'statecode') == 1: class=class="hl-string">"hl-comment"># Active (implies sent)
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Invoice Sent to Customer', class="hl-string">'ActivityStartTime': invoice.get(class="hl-string">'modifiedon'), class="hl-string">'ActivityEndTime': invoice.get(class="hl-string">'modifiedon')})
        elif invoice.get(class="hl-string">'statecode') == 2: class=class="hl-string">"hl-comment"># Paid
            event_log.append({**common_attrs, class="hl-string">'ActivityName': class="hl-string">'Payment Received', class="hl-string">'ActivityStartTime': invoice.get(class="hl-string">'modifiedon'), class="hl-string">'ActivityEndTime': invoice.get(class="hl-string">'modifiedon')})

    class=class="hl-string">"hl-comment"># Convert to DataFrame and save to CSV
    print(fclass="hl-string">"Processing complete. Total events extracted: {len(event_log)}")
    if event_log:
        df = pd.DataFrame(event_log)
        df_sorted = df.sort_values(by=[class="hl-string">'SalesOpportunity', class="hl-string">'ActivityStartTime'])
        df_sorted.to_csv(class="hl-string">'d365_lead_to_cash_event_log.csv', index=False)
        print(class="hl-string">"Event log saved to d365_lead_to_cash_event_log.csv")
    else:
        print(class="hl-string">"No events found for the specified criteria.")

if __name__ == class="hl-string">'__main__':
    main()

Steps

Prerequisites: Ensure you have an Azure subscription with contributor access, a Power Apps environment with system administrator privileges, and access to a Dynamics 365 Sales instance.
Create Azure Resources: In the Azure portal, create an Azure Synapse Analytics workspace and an Azure Data Lake Storage Gen2 account. Ensure the Synapse workspace has access to the storage account.
Link Power Apps Environment: Navigate to the Power Platform admin center (make.powerapps.com). Select 'Data' in the left navigation, then 'Azure Synapse Link'. Click '+ New link to data lake'.
Configure Synapse Link: Follow the wizard to connect to your Azure subscription, resource group, and the storage account created in step 2. Select the Synapse workspace to connect to.
Select Tables for Replication: Choose the specific Dataverse tables required for the Lead to Cash process. At a minimum, select: Opportunity, Quote, SalesOrder, Invoice, Account, SystemUser, and Contract. Add any other relevant custom tables. Initiate the synchronization.
Wait for Initial Synchronization: Allow the system time for the initial data copy to complete. The time required depends on the volume of data. You can monitor the status in the Azure Synapse Link dashboard.
Access Data in Synapse Studio: Open your Azure Synapse Studio. Navigate to the 'Data' hub. The replicated Dataverse tables will appear as external tables within a Lake Database.
Create a Serverless SQL Script: In the 'Develop' hub of Synapse Studio, create a new 'SQL script'. This is where you will run the provided query to transform the replicated data into an event log.
Execute the Event Log Query: Copy the complete T-SQL query provided in the 'query' section into the new script window. Adjust the placeholders for your database name and any custom logic.
Run the Query: Execute the query against the serverless SQL pool. The query will process the raw tables and generate a flattened event log structure with one row per business activity.
Save the Results: After the query successfully executes, use the 'Export' functionality in Synapse Studio to save the results as a CSV file.
Prepare for Upload: Ensure the exported CSV file headers match the required ProcessMind attribute names, for example, SalesOpportunity, ActivityName, and ActivityStartTime. Upload the final CSV file to ProcessMind.

Configuration

Azure Synapse Link Configuration: When setting up the link, you can choose 'Append only' mode. This mode is beneficial for process mining as it provides a historical record of changes, though it can increase storage consumption. For this query, standard 'In-place update' mode is sufficient.
Table Selection: Be selective with the tables you choose to replicate. Only include tables relevant to the Lead to Cash process to minimize synchronization time and storage costs. Key tables are: opportunity, quote, salesorder, invoice, contract, and related master data tables like account and systemuser.
Date Range Filtering: The provided query does not include a date filter by default. It is highly recommended to add a WHERE clause, typically on the opportunity.createdon date, to limit the analysis to a relevant period, such as the last 6 to 12 months. This significantly improves query performance.
Prerequisites: You must have appropriate permissions in both Azure (Contributor on the resource group) and Power Platform (System Administrator) to create and manage the Azure Synapse Link for Dataverse.

a Sample Query config

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Opportunity Created' AS ActivityName,
    opp.createdon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Requirements Identified' AS ActivityName,
    opp.modifiedon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE opp.salesstagecode = 2 -- 'Qualify' stage often indicates requirements are identified

UNION ALL

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Solution Proposed' AS ActivityName,
    opp.modifiedon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE opp.salesstagecode = 3 -- 'Propose' stage

UNION ALL

SELECT
    q.opportunityid AS SalesOpportunity,
    'Quote Created' AS ActivityName,
    q.createdon AS ActivityStartTime,
    q.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM quote AS q
JOIN opportunity AS opp ON q.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    q.opportunityid AS SalesOpportunity,
    'Quote Sent to Customer' AS ActivityName,
    q.modifiedon AS ActivityStartTime,
    q.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM quote AS q
JOIN opportunity AS opp ON q.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE q.statecode = 1 -- 'Active'

UNION ALL

SELECT
    q.opportunityid AS SalesOpportunity,
    'Quote Accepted by Customer' AS ActivityName,
    q.modifiedon AS ActivityStartTime,
    q.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM quote AS q
JOIN opportunity AS opp ON q.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE q.statecode = 2 -- 'Won'

UNION ALL

SELECT
    c.opportunityid AS SalesOpportunity,
    'Contract Signed' AS ActivityName,
    c.createdon AS ActivityStartTime,
    c.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM contract AS c
JOIN opportunity AS opp ON c.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    so.opportunityid AS SalesOpportunity,
    'Sales Order Created' AS ActivityName,
    so.createdon AS ActivityStartTime,
    so.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM salesorder AS so
JOIN opportunity AS opp ON so.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    so.opportunityid AS SalesOpportunity,
    'Order Fulfilled' AS ActivityName,
    so.modifiedon AS ActivityStartTime,
    so.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM salesorder AS so
JOIN opportunity AS opp ON so.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE so.statecode = 3 -- 'Fulfilled'

UNION ALL

SELECT
    inv.opportunityid AS SalesOpportunity,
    'Invoice Generated' AS ActivityName,
    inv.createdon AS ActivityStartTime,
    inv.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM invoice AS inv
JOIN opportunity AS opp ON inv.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid

UNION ALL

SELECT
    inv.opportunityid AS SalesOpportunity,
    'Invoice Sent to Customer' AS ActivityName,
    inv.modifiedon AS ActivityStartTime,
    inv.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM invoice AS inv
JOIN opportunity AS opp ON inv.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE inv.statecode = 1 -- 'Active'

UNION ALL

SELECT
    inv.opportunityid AS SalesOpportunity,
    'Payment Received' AS ActivityName,
    inv.modifiedon AS ActivityStartTime,
    inv.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM invoice AS inv
JOIN opportunity AS opp ON inv.opportunityid = opp.opportunityid
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE inv.statecode = 2 -- 'Paid'

UNION ALL

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Opportunity Won' AS ActivityName,
    opp.modifiedon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE opp.statecode = 1 -- 'Won'

UNION ALL

SELECT
    opp.opportunityid AS SalesOpportunity,
    'Opportunity Lost' AS ActivityName,
    opp.modifiedon AS ActivityStartTime,
    opp.modifiedon AS ActivityEndTime,
    opp.ownerid AS OwnerId,
    opp.statuscode AS OpportunityStatus,
    opp.totalamount AS OpportunityValue,
    opp.leadsourcecode AS LeadSource,
    opp.salesstagecode AS SalesStage,
    opp.parentaccountid AS CustomerId,
    acc.address1_country AS Region,
    'Microsoft Dynamics 365 Sales' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate
FROM opportunity AS opp
LEFT JOIN account AS acc ON opp.parentaccountid = acc.accountid
WHERE opp.statecode = 2; -- 'Lost'

Your Lead to Cash Data Template

Your Lead to Cash Data Template

Lead to Cash Attributes

Lead to Cash Activities

Extraction Guides

Dynamics 365 Data Export using Azure Synapse Link

Steps

Configuration

a Sample Query config

Direct Dataverse Query using the SQL TDS Endpoint

Steps

Configuration

a Sample Query sql

Python Script Extraction using Dynamics 365 Web API

Steps

Configuration

a Sample Query python

Scheduled Data Export via Power Automate Flow

Steps

Configuration

a Sample Query config

Ready to Get Started?

Boost Your Win Rate: Optimize Lead to Cash Now

Your Lead to Cash Data Template

Your Lead to Cash Data Template

Lead to Cash Attributes

Lead to Cash Activities

Extraction Guides

Dynamics 365 Data Export using Azure Synapse Link

Steps

Configuration

a Sample Query config

Direct Dataverse Query using the SQL TDS Endpoint

Steps

Configuration

a Sample Query sql

Python Script Extraction using Dynamics 365 Web API

Steps

Configuration

a Sample Query python

Scheduled Data Export via Power Automate Flow

Steps

Configuration

a Sample Query config

We value your privacy

Ready to Get Started?

Boost Your Win Rate: Optimize Lead to Cash Now