> 持续流程改进 > 线索到收款 > Microsoft Dynamics 365 Sales > 数据模板

您的Lead to Cash数据模板

Microsoft Dynamics 365 Sales

← 返回改进指南

线索到收款属性 (18) 线索到收款活动 (14) 提取指南 (4)

您的Lead to Cash数据模板

此模板为收集Lead to Cash流程分析所需的关键数据提供了结构化方法。模板列出了重要属性和关键活动，并说明如何提取这些信息。遵循这些建议，您可以构建完整的事件日志，获得深入的流程洞察。

推荐收集的属性
用于流程发现的关键活动
数据提取实用指南

刚接触事件日志？了解如何创建流程挖掘事件日志.

下载模板

线索到收款属性

这些数据字段对于创建完整的事件日志至关重要，可支持对您的线索到收款流程进行详细分析。

5 必需 8 建议 5 可选

	名称	说明
	开始时间 ActivityStartTime	表示特定活动或事件开始时间的时间戳。
说明此属性记录活动发生的日期和时间，是流程挖掘中用于按时间顺序排列事件和计算时长的主要时间要素。开始时间是所有基于时间的分析的基础，包括计算活动间周期时间、识别延迟和衡量整体流程绩效。它支持创建动态流程动画，并帮助您了解工作的时间分布。为什么重要此时间戳对于排列事件顺序以及计算周期时间、等待时间等所有绩效指标至关重要。获取位置通常使用相关实体（Opportunity、Quote、SalesOrder、Invoice）中与该活动对应的“createdon”或“modifiedon”时间戳。示例 2023-04-15T10:00:00Z2023-04-25T14:30:00Z2023-05-10T11:20:00Z
	活动 ActivityName	销售流程中特定时间点发生的业务活动或事件名称。
说明此属性描述Lead to Cash流程中的具体步骤或里程碑，例如“Quote Created”或“Payment Received”。每项活动都代表销售机会生命周期中的一个独立事件。分析这些活动的顺序和频率是流程挖掘的核心。它可以揭示实际流程、帮助识别常见路径和替代路径（变体），并定位返工或瓶颈发生的位置。这对于了解流程遵循度和效率至关重要。为什么重要定义流程地图中的步骤，是发现低效环节和偏差、开展所有流程挖掘分析的基础。获取位置此属性通常源自Dynamics 365中与Opportunity、Quote、Order和Invoice实体相关的各种事件。通常需要通过逻辑将状态变更或记录创建事件映射为标准化活动名称。示例机会已创建报价已发送给客户合同已签署已收到付款
	销售机会 SalesOpportunity	销售机会的唯一标识符，作为Lead to Cash流程的主要案例ID。
说明 Sales Opportunity ID可唯一标识销售漏斗中不断推进的每笔潜在交易。它是连接所有相关活动的主线，从机会初始创建一直延伸到最终收款。在流程挖掘中，此属性是还原每个销售案例端到端历程的基础。它支持分析完整流程、计算总周期时间、比较机会采用的不同路径，并最终识别从线索资格审查到收款过程中的低效环节和瓶颈。为什么重要这是连接所有流程事件的必要Case ID，使您能够追踪每笔销售交易的完整生命周期。获取位置这是Opportunity实体的主键，通常为“opportunityid”字段。示例 OPP-01532-A3B1C7OPP-01533-F8D2E5OPP-01534-G9H3I4
	最后数据更新时间 LastDataUpdate	源系统最近一次刷新数据的时间戳。
说明此属性表示数据最近一次从Microsoft Dynamics 365 Sales提取并加载到流程挖掘工具的日期和时间，为所分析数据的新鲜度提供参考。了解数据的时效性对于确保分析和结论基于最新信息且具有现实意义至关重要。它有助于用户信任分析洞察，并根据流程当前状态做出明智决策。为什么重要表示数据的新鲜度，确保分析结果及时、相关，为决策提供依据。获取位置此时间戳由数据提取和加载流程自动生成。示例 2024-07-27T02:00:00Z2024-07-28T02:00:00Z
	源系统 SourceSystem	提取数据的系统。
说明此属性用于标识事件数据来源的应用程序。对于此流程，来源始终为Microsoft Dynamics 365 Sales。在包含多个集成系统的环境中，该字段对于数据溯源和故障排查至关重要。它有助于了解数据背景，确保分析基于正确来源的信息。为什么重要提供有关数据来源的重要背景信息，是数据治理、验证和集成管理的基础。获取位置这是一个静态值，应在数据提取过程中填充，用于标记记录来源。示例 Microsoft Dynamics 365 Sales
	区域 Region	客户所在的地理区域或销售辖区。
说明此属性表示与销售机会相关的地理区域，例如“North America”“EMEA”或“APAC”，通常根据客户地址信息推导。区域是对比分析的重要维度。它可用于“Invoice Generation And Delivery Time”等仪表板，了解不同区域的流程绩效是否存在差异。此类分析可以发现由当地法规、团队绩效或市场环境造成的差异，为制定区域化流程改进措施提供依据。为什么重要支持按地理区域细分流程，突出不同区域在绩效、合规和客户行为方面的差异。获取位置这些数据通常存储在相关的Account或Contact实体中，例如“address1_country”或自定义的“Region”字段。示例北美EMEAAPACLATAM
	客户 CustomerId	与销售机会关联的客户账户。
说明此属性用于标识销售机会面向的潜在客户或现有客户，并将销售流程关联到CRM系统中的具体账户。按客户或客户细分分析流程，有助于深入了解不同类型客户与销售流程的互动方式。您可以发现某些客户是否需要更长的周期、更多返工，或具有更高的赢单率。这些信息有助于制定针对性的销售方式并管理客户关系。为什么重要支持分析特定客户或客户细分的流程绩效，帮助制定更有针对性的销售策略。获取位置这是Opportunity实体中的“customerid”查找字段，关联到Account或Contact记录。示例 Contoso LtdAdventure WorksFabrikam, Inc.
	机会价值 OpportunityValue	销售机会的预计或实际货币价值。
说明此属性表示销售机会对应的潜在收入或最终收入。它可能最初是预计值，并在交易赢单后更新为实际合同金额。这是分析中的关键维度，因为不同机会的价值并不相同。按交易规模细分流程后，企业可以了解大额交易是否遵循不同流程，或是否需要更长时间才能成交。此外，还可以优先改进高价值业务环节。为什么重要提供财务背景，支持按交易规模分析，并优先改进高价值机会相关的流程。获取位置通常位于Opportunity实体的“estimatedvalue”（预计收入）或“actualvalue”（实际收入）等字段中。示例 50000.00125000.0025000.00
	机会状态 OpportunityStatus	销售机会的最终结果，例如Won或Lost。
说明此属性记录销售机会的最终状态。主要结果通常为“Won”，表示成功成交；或“Lost”，表示交易未能完成。按结果分析是了解哪些流程模式会带来成功或失败的基础。按状态筛选案例后，可以比较赢单与丢单交易的周期时间、活动和用户参与情况。这对于计算Opportunity Win Rate KPI以及识别与积极结果相关的行为至关重要。为什么重要区分成功和未成功的结果，支持对比分析，帮助识别哪些流程行为能够促成赢单。获取位置对应Opportunity实体中的“statecode”（Status）和“statuscode”（Status Reason）字段。示例赢单丢单进行中
	机会负责人 OwnerId	负责该机会的销售代表或用户。
说明 Opportunity Owner是Microsoft Dynamics 365 Sales中负责管理和推进销售机会的用户，通常负责该案例的大部分相关活动。按负责人分析流程，有助于比较不同销售代表、团队或部门的绩效。您可以借此发现优秀员工的最佳实践，为其他人员识别辅导机会，并了解工作量分配如何影响流程效率和结果。为什么重要支持按用户或团队分析绩效，帮助识别高绩效人员，并诊断特定个人或群体相关的问题。获取位置这是Opportunity实体中的“ownerid”字段，关联到User或Team记录。示例 John DoeJane SmithEMEA销售团队
	线索来源 LeadSource	初始线索的生成来源。
说明此属性表示促成销售机会的线索来源，例如“Web”“Partner”“Trade Show”或“Advertisement”。它用于跟踪不同营销渠道和线索生成策略的效果。在流程挖掘中，按线索来源分析对于“Lead Conversion Performance”仪表板至关重要。它有助于计算各渠道的线索到机会转化率，识别能够产生高价值线索或实现快速资格审查的来源。这些洞察对于优化营销投入和销售重点非常重要。为什么重要帮助评估不同营销渠道生成优质线索的效果，直接支持线索转化分析。获取位置通常为Lead实体中的“leadsourcecode”字段，在线索完成资格审查后传递至Opportunity。示例网站合作伙伴贸易展会推荐
	结束时间 ActivityEndTime	表示特定活动或事件完成时间的时间戳。对于原子事件，通常与开始时间相同。
说明结束时间表示活动完成的时刻。对于“Invoice Generated”等瞬时事件，结束时间通常与开始时间相同。对于人工审核等具有可衡量时长的活动，此时间戳记录工作完成的时间。此属性用于计算单项活动的处理时间。通过比较一项活动的结束时间与下一项活动的开始时间，可以同时分析流程中的实际工作时间（处理时间）和空闲时间（等待时间）。为什么重要支持计算活动处理时间，区分实际工作时间和等待时间，从更细粒度的角度呈现流程绩效。获取位置对于瞬时事件，该时间与开始时间相同。对于有持续时间的活动，可能需要根据后续时间戳或状态变更推导。示例 2023-04-15T10:00:00Z2023-04-25T15:00:00Z2023-05-10T11:25:00Z
	销售阶段 SalesStage	销售机会在既定销售流程管道中的阶段。
说明 Sales Stage表示机会所处的具体阶段，例如“Qualify”“Develop”“Propose”或“Close”。这些阶段提供销售管道和进展的高层概览。流程挖掘根据时间戳推导详细活动，而Sales Stage则提供有价值的业务背景。它用于“Overall Sales Cycle Time Analysis”仪表板，对流程进行分段，并了解每个主要阶段耗费的时间。这有助于识别耗时最长的阶段，以及机会容易受阻的位置。为什么重要提供有关机会进展的业务背景，支持分析销售管道各阶段的耗时。获取位置此信息源自与Opportunity实体关联的Business Process Flow中的“stepname”字段。示例 1-资格审查2-培育3-提案4-成交
	产品类别 ProductCategory	商机中销售的产品或服务类别。
说明此属性用于对与销售商机关联的主要产品或服务进行分类，例如“Hardware”“Software”或“Professional Services”。按产品类别分析流程对于“Order Fulfillment Performance”仪表板至关重要。通过该分析，您可以判断某些产品类型是否具有更长的销售周期、不同的履约流程或更低的赢单率。这些洞察有助于针对不同产品线设计专门的销售流程，从而提升效率。为什么重要支持根据销售内容分析绩效，帮助发现特定产品对应的流程差异和瓶颈。获取位置这些信息通常来自与Opportunity关联的Opportunity Product（opportunityproduct）实体。示例云服务本地部署软件咨询服务
	付款条款 PaymentTerms	发票约定的付款条款，例如Net 30或Net 60。
说明此属性规定客户为已交付的商品或服务付款时需遵循的条件，并明确发票开具后的付款期限。对于“Payment Collection Cycle Analysis”仪表板，此属性不可或缺。您可以按约定条款细分收款时间，识别长期逾期付款的客户，并判断某些付款条款是否对应更长的收款周期。这有助于优化应收账款流程和现金流管理。为什么重要为分析付款绩效提供基准，帮助识别逾期付款并评估收款策略的有效性。获取位置这些信息通常存储在Invoice实体中，字段名通常为“paymenttermscode”。示例 30天账期60天账期收到即付
	合同类型 ContractType	与销售相关的法律合同类型，例如“New Business”或“Renewal”。
说明此属性用于对销售商机关联的合同进行分类。不同合同类型，例如新客户协议、续约合同或增购合同，可能遵循不同的审批和签署流程。 “Contract Signing Cycle Time”仪表板使用此属性进行分析细分。比较不同合同类型的签署时间后，企业可以判断续约合同是否比新合同处理得更快，或复杂的定制合同是否造成明显延迟。这有助于针对性地改进合同管理流程。为什么重要通过识别某些合同类型是否会造成流程延迟，帮助分析并优化合同签署阶段。获取位置这通常是Opportunity或Contract实体中的自定义字段，因为其内容取决于具体业务需求。示例新业务续约业务扩展主服务协议
	折扣百分比 DiscountPercentage	应用于报价或订单的折扣百分比。
说明此属性记录销售报价或订单相对于标价的总折扣比例，是监控销售利润率和定价纪律的重要指标。 “Quote Management And Discount Compliance”仪表板使用此属性分析折扣行为。通过展示折扣分布，管理人员可以识别过高或未经授权的折扣，评估折扣对赢单率的影响，并确保符合定价政策。为什么重要对于分析定价策略和折扣合规至关重要，有助于了解折扣对盈利能力和赢单率的影响。获取位置位于Quote（“discountpercentage”）和SalesOrder（“discountpercentage”）实体中。示例 0.050.100.15
	是否返工 IsRework	用于标记某项活动是否为同一案例中的重复或返工。
说明此布尔属性是一个计算字段，用于识别单个销售商机中重复执行的活动或活动序列。例如，报价创建后被拒绝，随后又创建新报价，则第二次“Quote Created”活动会被标记为返工。识别返工对于“Process Variant And Rework Analysis”仪表板至关重要。它通过突出显示循环和重复步骤，帮助量化流程低效程度。了解返工发生的位置和原因，是简化流程、减少无效工作并加快销售周期的第一步。为什么重要直接标记流程低效和循环，帮助量化返工成本并发现简化流程的机会。获取位置这是一个计算属性，由流程挖掘工具分析每个案例的活动序列后得出。示例 truefalse

必需建议可选

线索到收款活动

以下是需要记录在事件日志中的关键流程步骤和里程碑，可清晰呈现您的线索到收款旅程。

7 建议 7 可选

	活动	说明
	客户已接受报价	客户已正式同意报价中的条款和价格。通常由销售人员将Quote记录的状态更新为“Won”来记录。
为什么重要这是表明客户正式承诺的关键里程碑，可用于衡量报价到订单的转化率和平均报价审批时间。获取位置根据Quote记录中“statecode”更新为“Won”（值为2）时的时间戳来推断。采集使用Quote的“statecode”变更为“Won”时的“modifiedon”时间戳。事件类型 inferred
	已收到付款	客户已支付发票的全部款项。当Invoice记录的状态标记为“Paid”时，会捕获此事件。
为什么重要这是代表收入实现的关键财务里程碑。分析从发票发送到收到付款的时间，有助于优化收款周期。获取位置根据Invoice记录中“statecode”变更为“Paid”（值为2）时的时间戳来推断。采集使用Invoice的“statecode”更新为“Paid”时的“modifiedon”时间戳。事件类型 inferred
	报价已创建	已为客户生成产品或服务的正式报价。当系统创建新的Quote记录并将其关联到Sales Opportunity时，会捕获此事件。
为什么重要这标志着流程从概念性方案转为正式报价。从此时到报价接受之间的时间，是衡量报价管理效率的关键指标。获取位置这是一个明确事件，记录为Quote实体中新记录的创建日期。该记录通过查找关系关联到上级Opportunity。采集使用Quote实体中的“createdon”时间戳，并通过Opportunity ID进行关联。事件类型 explicit
	机会已丢单	销售机会未能成功，最终在未成交的情况下关闭。当机会状态变更为“Lost”时，会捕获此事件。
为什么重要这是销售流程主要的失败结束活动。分析这些案例有助于识别交易失败原因、销售漏斗中的客户流失节点以及竞争对手的影响。获取位置根据Opportunity记录中“statecode”变更为“Lost”（值为2）时的时间戳来推断。采集使用“actualclosedate”字段，或Opportunity的“statuscode”设置为“Lost”时的“modifiedon”时间戳。事件类型 inferred
	机会已创建	标志着某个具体潜在交易销售流程的开始。当系统创建新的Sales Opportunity记录时，通常会捕获此事件，该记录往往源于线索资格审查完成。
为什么重要这是Lead to Cash流程分析的主要起始活动，可用于衡量总销售周期时间并分析线索转化效果。获取位置这是从Opportunity表中Opportunity记录的创建时间戳直接捕获的明确事件。采集使用Opportunity实体中的“createdon”时间戳。事件类型 explicit
	机会已赢单	销售机会已成功关闭并促成交易。当机会状态变更为“Won”时，会捕获此事件。
为什么重要这是销售流程主要的成功结束活动，可用于计算赢单率、成功交易的销售周期以及整体销售绩效。获取位置根据Opportunity记录中“statecode”变更为“Won”（值为1）时的时间戳来推断。相关报价或订单处理完成后，通常会自动触发此变更。采集使用“actualclosedate”字段，或Opportunity的“statuscode”设置为“Won”时的“modifiedon”时间戳。事件类型 inferred
	销售订单已创建	系统中已正式创建销售订单，用于管理已售产品或服务的履行。这是一个明确事件，通常通过将“Won”状态的Quote转换为Order生成。
为什么重要这标志着工作正式从销售交接给运营或履行团队，也是衡量订单履行周期和售后效率的起点。获取位置这是从SalesOrder记录的创建时间戳直接捕获的明确事件，该记录关联到上级Opportunity。采集使用SalesOrder（Order）实体中的“createdon”时间戳。事件类型 explicit
	发票已发送给客户	发票已交付客户付款。通常根据Invoice记录的状态变更来推断。
为什么重要这标志着付款条款计时开始，也是衡量应收账款周转天数（DSO）的第一步。此处的延迟会直接影响现金流。获取位置根据Invoice实体的状态变更来推断。虽然标准系统没有“Sent”状态，但工作流通常会在此操作中更新自定义字段或状态。采集跟踪Invoice记录中的自定义“Sent Date”字段，或状态原因变更。事件类型 inferred
	发票已生成	已根据履行完成的销售订单创建客户发票。这是一个明确事件，在系统创建新的Invoice记录时记录。
为什么重要此活动标志着开票和收款周期开始。从订单履行到发票生成的时间，是衡量开票流程效率的关键指标。获取位置这是从Invoice记录的创建时间戳直接捕获的明确事件，该记录关联到SalesOrder。采集使用Invoice实体中的“createdon”时间戳。事件类型 explicit
	合同已签署	双方已签署具有法律约束力的合同，协议正式确定。此事件通常通过手动更新Opportunity或相关Contract实体来记录，也可能由电子签名集成触发。
为什么重要此活动对于跟踪合同最终确定周期至关重要。该阶段的延迟可能显著影响收入预测和项目启动。获取位置根据Opportunity中的“Contract Signed Date”等日期字段被填充，或状态发生变更来推断。系统可能需要定制才能提供此字段。采集使用特定状态变更的时间戳，或合同相关字段被填充的日期。事件类型 inferred
	报价已发送给客户	已将生成的报价正式交付客户审核。通常根据Quote记录的状态变更为“Active”来推断。
为什么重要此活动标志着客户审核和谈判计时开始，是分析报价审批周期、识别客户响应延迟的重要数据点。获取位置根据Quote记录中“statecode”变更为“Active”时的时间戳来推断。采集监控审计历史，或使用工作流在Quote的“statecode”变为1（Active）时更新的时间戳字段。事件类型 inferred
	解决方案已提出	此活动表示已向潜在客户正式展示解决方案或方案。通常根据机会在生命周期中进入“Propose”阶段来推断。
为什么重要分析此活动前后的耗时，有助于评估解决方案设计和方案生成步骤的效率。反复出现通常表示销售周期中存在返工。获取位置根据Opportunity记录的状态变更，或相关Business Process Flow推进到“Propose”阶段来推断。采集使用Opportunity实体中状态或阶段变更的时间戳。事件类型 inferred
	订单已履行	销售订单中的所有产品均已发货，或所有服务均已交付客户。当SalesOrder记录的状态更新为“Fulfilled”或类似状态时，会捕获此事件。
为什么重要此活动标志着交付环节结束，是分析订单履行绩效的关键里程碑，也是开票的触发点。获取位置根据SalesOrder记录的“statecode”变更为“Fulfilled”（值为3）时的时间戳来推断。采集使用SalesOrder的“statecode”更新为“Fulfilled”时的“modifiedon”时间戳。事件类型 inferred
	需求已识别	表示销售团队已收集并理解客户需求的阶段。通常由销售人员更新Business Process Flow中的阶段，或更改机会状态来记录。
为什么重要跟踪此阶段有助于识别从首次接触到形成可行方案所需的时间。此处的延迟可能表明需求调研沟通或资源可用性存在问题。获取位置根据Opportunity的Business Process Flow阶段变更为“Develop”或“Propose”阶段，或自定义状态字段更新来推断。使用阶段变更的时间戳。采集跟踪Opportunity实体或相关Business Process Flow实体中“stageid”的变更。事件类型 inferred

建议可选

提取指南

如何从Microsoft Dynamics 365 Sales获取数据

步骤

前提条件：确保您拥有具备Contributor访问权限的Azure订阅、具有System Administrator权限的Power Apps环境，以及Dynamics 365 Sales实例的访问权限。
创建Azure资源：在Azure门户中创建Azure Synapse Analytics工作区和Azure Data Lake Storage Gen2账户。确保Synapse工作区可以访问该存储账户。
链接Power Apps环境：进入Power Platform管理中心（make.powerapps.com）。在左侧导航中选择“Data”，然后选择“Azure Synapse Link”。点击“+ New link to data lake”。
配置Synapse Link：按照向导连接您的Azure订阅、资源组和第2步创建的存储账户。选择要连接的Synapse工作区。
选择要复制的表：选择Lead to Cash流程所需的Dataverse表。至少选择：Opportunity、Quote、SalesOrder、Invoice、Account、SystemUser和Contract。如有需要，添加其他相关自定义表，然后启动同步。
等待初始同步：等待系统完成初始数据复制。所需时间取决于数据量。您可以在Azure Synapse Link仪表板中监控状态。
在Synapse Studio中访问数据：打开Azure Synapse Studio，进入“Data”中心。复制的Dataverse表将以外部表形式显示在Lake Database中。
创建无服务器SQL脚本：在Synapse Studio的“Develop”中心创建新的“SQL script”。您将在此运行提供的查询，将复制的数据转换为事件日志。
执行事件日志查询：将“query”部分提供的完整T-SQL查询复制到新脚本窗口中。根据您的数据库名称和自定义逻辑调整占位符。
运行查询：针对无服务器SQL池执行查询。查询将处理原始表，并生成扁平化的事件日志结构，每行对应一项业务活动。
保存结果：查询成功执行后，使用Synapse Studio中的“Export”功能将结果保存为CSV文件。
准备上传：确保导出的CSV文件表头与ProcessMind要求的属性名称一致，例如SalesOpportunity、ActivityName和ActivityStartTime。将最终CSV文件上传到ProcessMind。

配置

Azure Synapse Link配置：设置链接时，您可以选择“Append only”模式。该模式能够保留变更历史记录，对流程挖掘很有帮助，但可能增加存储消耗。对于本查询，标准的“In-place update”模式即可满足需求。
表选择：谨慎选择要复制的表。仅包含与Lead to Cash流程相关的表，以减少同步时间和存储成本。关键表包括：opportunity、quote、salesorder、invoice、contract，以及account和systemuser等相关主数据表。
日期范围筛选：提供的查询默认不包含日期筛选。强烈建议添加WHERE子句，通常基于opportunity.createdon日期，将分析范围限制在相关期间，例如最近6至12个月。这可以显著提升查询性能。
前提条件：您必须同时拥有Azure中的相应权限（资源组上的Contributor权限）和Power Platform中的相应权限（System Administrator），才能创建和管理Dataverse的Azure Synapse Link。

a 示例查询 sql

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'

步骤

启用TDS终结点：确保Dynamics 365 Dataverse环境已启用表格数据流（TDS）终结点。该设置位于Power Platform管理中心的“Environment settings > Features > TDS endpoint”下。
确定环境URL：找到您所在组织的环境URL，通常格式为[org-name].crm.dynamics.com。
使用SQL客户端连接：打开SQL Server Management Studio（SSMS）或Azure Data Studio等SQL客户端，创建到服务器的新连接。
配置连接：在服务器名称字段中输入环境URL并追加端口5558，例如[org-name].crm.dynamics.com,5558。选择“Azure Active Directory - Password”或“Azure Active Directory - Integrated”进行身份验证，并输入您的Dynamics 365凭据。
准备查询：打开连接到Dataverse环境数据库的新查询窗口。
自定义查询：将“query”部分提供的SQL脚本复制到新查询窗口中。找到脚本顶部的DECLARE语句，设置@StartDate和@EndDate变量，以定义所需的数据提取日期范围。
执行提取：运行SQL脚本。查询将从opportunity、quote、salesorder和invoice等多个表中收集数据，构建完整的事件日志。
检查结果：查询完成后，结果会以表格形式显示在SQL客户端中。请简要检查数据，确认内容正确，并包含预期的活动和属性。
导出为CSV：将结果集导出为CSV文件。在SSMS中，您可以右键点击结果网格，选择“Save Results As...”。
完成上传准备：确保保存的CSV文件采用UTF-8编码，且列标题符合您的流程挖掘工具要求。现在即可将文件上传到ProcessMind。

配置

TDS终结点：必须为Dataverse环境启用表格数据流（TDS）协议设置，以提供直接的只读SQL连接。
用户权限：连接用户必须在Dynamics 365中拥有安全角色，并对查询引用的所有表具有读取权限，包括opportunity、quote、salesorder、invoice、account和systemuser。
日期范围筛选：必须使用明确的日期范围筛选数据，以控制查询范围并提升性能。提供的脚本使用@StartDate和@EndDate变量完成筛选。建议初次分析使用3至6个月的数据范围。
性能注意事项：在大型生产环境中执行此查询可能消耗系统资源，并影响其他用户的使用体验。建议在业务低峰期执行大型数据提取。
SQL客户端要求：您需要使用支持通过Azure Active Directory身份验证连接Azure SQL Database的SQL客户端。常用客户端包括SQL Server Management Studio（SSMS）v18或更高版本，以及Azure Data Studio。

a 示例查询 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

步骤

在Azure Active Directory中注册应用程序：连接Dynamics 365 Web API前，必须在Azure AD租户中注册应用程序，以获取身份验证所需的凭据。进入Azure Active Directory > App registrations > New registration。将应用程序设置为Web应用，并添加客户端密钥。记录Application（client）ID、Directory（tenant）ID和Client Secret值。
授予API权限：在新建的App registration中，进入API permissions > Add a permission。选择“Dynamics CRM”或“Dataverse”，选择“Delegated permissions”，再选择“user_impersonation”，然后点击“Add permissions”。最后，为您的目录授予管理员同意。
创建Dynamics 365应用程序用户：在Power Platform管理中心选择您的环境。进入Settings > Users + permissions > Application users。创建新的应用程序用户，并将其关联到第1步注册的Azure AD应用程序。为该用户分配“System Administrator”等安全角色，确保其拥有读取所有必需数据的足够权限。
设置Python环境：确保您的系统已安装Python。运行以下命令安装身份验证、HTTP请求和数据处理所需的库：pip install requests msal pandas。
配置Python脚本：将提供的Python脚本复制到文件中，例如extract_d365.py。打开文件，找到顶部的配置部分，将D365_URL、CLIENT_ID、CLIENT_SECRET、TENANT_ID、START_DATE和END_DATE的占位值替换为您的环境信息和所需提取时间范围。
自定义阶段名称（如有必要）：脚本会根据“Identify”和“Propose”等Business Process Flow阶段名称推断部分活动。检查“Requirements Identified”和“Solution Proposed”等活动对应的OData筛选查询，并根据您所在组织的自定义销售流程调整阶段名称。
运行提取脚本：打开终端或命令提示符，进入保存脚本的目录，然后运行命令：python extract_d365.py。脚本将进行身份验证，针对每项活动查询API并处理数据。
检查输出文件：成功运行后，脚本会在同一目录中生成名为d365_lead_to_cash_event_log.csv的CSV文件，其中包含提取的事件日志。
准备上传：打开CSV文件，确认表头和数据格式正确。文件应包含SalesOpportunity、ActivityName和ActivityStartTime等列，现在即可上传到ProcessMind进行分析。

配置

身份验证凭据：脚本需要Azure Active Directory应用注册提供的Application（Client）ID、Client Secret和Tenant ID，以便通过Dataverse API进行身份验证。
Dynamics 365 URL：您必须提供Dynamics 365 Sales环境的基础URL，例如https://yourorg.api.crm.dynamics.com。
日期范围筛选：START_DATE和END_DATE变量控制提取时间范围。建议先使用3至6个月的时间段，以控制数据量并验证结果，再进行更大范围的提取。
API终结点版本：脚本使用Dataverse Web API的v9.2版本，具体版本会在请求URL中指定。
分页：脚本每页最多请求5000条记录（odata.maxpagesize=5000）以优化性能，并自动跟随响应中的@odata.nextLink，获取指定查询的后续所有页面。
所需权限：运行脚本的应用程序用户必须拥有具备以下实体读取权限的安全角色：Opportunity、Quote、SalesOrder、Invoice、Account和SystemUser。

a 示例查询 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()

步骤

在Power Automate中创建包含Recurrence触发器的定时云端流。配置间隔、时区和运行频率。根据源数据量和报告要求设置合适的刷新间隔。
添加Dataverse操作，获取所需的Sales Opportunity、Quote、Sales Order、Invoice及相关记录。使用Microsoft Dataverse连接器，并将每个操作配置为连接到包含Microsoft Dynamics 365 Sales的环境。将查询中的表和列占位符替换为您环境中配置的逻辑名称。
使用[Start date parameter]和[End date parameter]配置提取窗口。将日期筛选应用于每项活动使用的事件时间戳。对于增量提取，还应根据上次成功运行记录水位标记，并加入较短的重叠时间段，以捕获延迟更新。
获取案例和事件日志所需的商机属性，包括商机标识符、所有者、状态、价值、潜在客户来源、销售阶段、客户、区域、创建时间戳及相关修改时间戳。通过已配置的关系或查找字段获取相关报价、订单和发票记录。
将流配置为每项符合条件的活动创建一行事件。流必须明确创建Opportunity Created、Requirements Identified、Solution Proposed、Quote Created、Quote Sent to Customer、Quote Accepted by Customer、Contract Signed、Sales Order Created、Order Fulfilled、Invoice Generated、Invoice Sent to Customer、Payment Received、Opportunity Won和Opportunity Lost这十四项活动。ProcessMind会按原样读取这些行，不会推断缺失的事件。
对于状态或阶段活动，请使用记录状态转换的时间戳。如果源系统未保留转换时间戳，请使用配置的审计历史或变更跟踪源。除非当前提取时间戳确实代表业务事件发生时间，否则不要用它替代事件时间。
将所有事件行规范化为相同的架构。将SalesOpportunity填充为案例标识符，将ActivityName填充为十四个精确活动名称之一，将ActivityStartTime填充为事件时间戳，在可用时将ActivityEndTime填充为完成时间戳，将SourceSystem填充为Microsoft Dynamics 365 Sales，并将LastDataUpdate填充为流刷新时间戳。在数据可用时填充推荐属性。
将规范化后的行组装为数组，创建CSV表，并将文件保存到SharePoint或其他获批准的云存储位置。使用稳定的文件命名规则，例如[Process name]_[Run timestamp].csv。确保CSV采用UTF-8编码并包含表头。
使用下述检查项验证输出。确认十四项活动名称均可作为行出现，SalesOpportunity已填充，时间戳有效且在预期情况下按顺序排列，并通过稳定的事件键控制重复行。
使用已配置的导入流程将验证后的CSV上传到ProcessMind。将SalesOpportunity映射为案例标识符，将ActivityName映射为活动，将ActivityStartTime映射为开始时间，在使用时将ActivityEndTime映射为结束时间，并将其余列映射到对应属性。保留精确的活动名称，不要依赖ProcessMind推导其他事件。

配置

**流程触发：**使用Power Automate云端计划流，并设置明确的时区和重复间隔。计划频率应与源系统更新延迟及所需报表时效保持一致。
**Dataverse连接：**使用Microsoft Dataverse连接器，并配置具备读取机会、报价、订单、发票、审计及相关客户数据权限的账户或服务主体。
**源对象：**将[Your table name]和[Your column name]占位符替换为目标环境中的逻辑表名和列名。激活工作流前，请在Dataverse元数据中确认关系和查找字段。
**日期范围：**首次加载时提取约三至六个月的历史数据；如流程分析需要，也可提取更长时间段的数据。定期加载时，使用持久化水印并设置较小的重叠时间窗口，以捕获延迟更新。
**活动筛选：**根据已配置的创建、修改、状态转换、阶段转换、履约、发票、付款和合同事件时间戳筛选记录。仅当公司、业务部门、区域、单据类型或其他组织字段存在且确有需要时，才应用相应筛选条件。
**活动映射：**为全部十四项活动配置明确映射。阶段和状态值必须使用环境中配置的选项值进行映射，不应直接假定标签名称。
**审计历史：**当需要状态或阶段转换时间戳，而基础记录未保留历史转换信息时，请使用Dataverse审计或其他获批准的变更历史来源。审计数据是否可用取决于环境配置、保留期限和权限。
**合同事件：**仅当存在合同表或电子签名集成时，才配置合同源。否则，请使用获批准的机会字段或相关记录字段，并确保该字段明确记录合同签署。
**性能：**使用带筛选条件的Dataverse查询、分页、指定列和增量提取。避免提取所有列或反复加载完整历史数据。必要时，将大型实体加载拆分为有界日期窗口。
**存储：**将CSV保存到获批准的SharePoint库或云存储位置，并设置访问控制、保留策略和可预测的命名规范。
**前置条件：**环境必须具备Power Automate、Microsoft Dataverse连接器、Dynamics 365 Sales数据访问权限、充足的API和流程容量，以及所需的审计、变更跟踪、SharePoint或存储权限。

a 示例查询 sql

WITH OpportunityBase AS (
    SELECT
        o.[SalesOpportunityId] AS SalesOpportunity,
        o.[OwnerId] AS OwnerId,
        o.[OpportunityStatus] AS OpportunityStatus,
        o.[OpportunityValue] AS OpportunityValue,
        o.[LeadSource] AS LeadSource,
        o.[SalesStage] AS SalesStage,
        o.[CustomerId] AS CustomerId,
        o.[Region] AS Region,
        o.[CreatedOn] AS OpportunityCreatedOn,
        o.[ModifiedOn] AS OpportunityModifiedOn,
        o.[LastDataUpdate] AS LastDataUpdate
    FROM [Your opportunity table] AS o
    WHERE o.[CreatedOn] >= '[Start date parameter]'
      AND o.[CreatedOn] < '[End date parameter]'
),
EventLog AS (
    SELECT SalesOpportunity, 'Opportunity Created' AS ActivityName, OpportunityCreatedOn AS ActivityStartTime, OpportunityCreatedOn AS ActivityEndTime, OwnerId, OpportunityStatus, OpportunityValue, LeadSource, SalesStage, CustomerId, Region, 'Microsoft Dynamics 365 Sales' AS SourceSystem, LastDataUpdate
    FROM OpportunityBase
    WHERE OpportunityCreatedOn IS NOT NULL
    UNION ALL
    SELECT SalesOpportunity, 'Requirements Identified', [RequirementsIdentifiedOn], [RequirementsIdentifiedOn], OwnerId, OpportunityStatus, OpportunityValue, LeadSource, SalesStage, CustomerId, Region, 'Microsoft Dynamics 365 Sales', LastDataUpdate
    FROM OpportunityBase
    WHERE [RequirementsIdentifiedOn] IS NOT NULL
    UNION ALL
    SELECT SalesOpportunity, 'Solution Proposed', [SolutionProposedOn], [SolutionProposedOn], OwnerId, OpportunityStatus, OpportunityValue, LeadSource, SalesStage, CustomerId, Region, 'Microsoft Dynamics 365 Sales', LastDataUpdate
    FROM OpportunityBase
    WHERE [SolutionProposedOn] IS NOT NULL
    UNION ALL
    SELECT q.[SalesOpportunityId], 'Quote Created', q.[CreatedOn], q.[CreatedOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', q.[LastDataUpdate]
    FROM [Your quote table] AS q
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = q.[SalesOpportunityId]
    WHERE q.[CreatedOn] IS NOT NULL
    UNION ALL
    SELECT q.[SalesOpportunityId], 'Quote Sent to Customer', q.[ActiveOn], q.[ActiveOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', q.[LastDataUpdate]
    FROM [Your quote table] AS q
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = q.[SalesOpportunityId]
    WHERE q.[ActiveOn] IS NOT NULL
    UNION ALL
    SELECT q.[SalesOpportunityId], 'Quote Accepted by Customer', q.[WonOn], q.[WonOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', q.[LastDataUpdate]
    FROM [Your quote table] AS q
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = q.[SalesOpportunityId]
    WHERE q.[WonOn] IS NOT NULL
    UNION ALL
    SELECT c.[SalesOpportunityId], 'Contract Signed', c.[ContractSignedOn], c.[ContractSignedOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', c.[LastDataUpdate]
    FROM [Your contract table] AS c
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = c.[SalesOpportunityId]
    WHERE c.[ContractSignedOn] IS NOT NULL
    UNION ALL
    SELECT so.[SalesOpportunityId], 'Sales Order Created', so.[CreatedOn], so.[CreatedOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', so.[LastDataUpdate]
    FROM [Your sales order table] AS so
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = so.[SalesOpportunityId]
    WHERE so.[CreatedOn] IS NOT NULL
    UNION ALL
    SELECT so.[SalesOpportunityId], 'Order Fulfilled', so.[FulfilledOn], so.[FulfilledOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', so.[LastDataUpdate]
    FROM [Your sales order table] AS so
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = so.[SalesOpportunityId]
    WHERE so.[FulfilledOn] IS NOT NULL
    UNION ALL
    SELECT i.[SalesOpportunityId], 'Invoice Generated', i.[CreatedOn], i.[CreatedOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', i.[LastDataUpdate]
    FROM [Your invoice table] AS i
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = i.[SalesOpportunityId]
    WHERE i.[CreatedOn] IS NOT NULL
    UNION ALL
    SELECT i.[SalesOpportunityId], 'Invoice Sent to Customer', i.[SentOn], i.[SentOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', i.[LastDataUpdate]
    FROM [Your invoice table] AS i
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = i.[SalesOpportunityId]
    WHERE i.[SentOn] IS NOT NULL
    UNION ALL
    SELECT i.[SalesOpportunityId], 'Payment Received', i.[PaidOn], i.[PaidOn], o.OwnerId, o.OpportunityStatus, o.OpportunityValue, o.LeadSource, o.SalesStage, o.CustomerId, o.Region, 'Microsoft Dynamics 365 Sales', i.[LastDataUpdate]
    FROM [Your invoice table] AS i
    INNER JOIN OpportunityBase AS o ON o.SalesOpportunity = i.[SalesOpportunityId]
    WHERE i.[PaidOn] IS NOT NULL
    UNION ALL
    SELECT SalesOpportunity, 'Opportunity Won', [OpportunityWonOn], [OpportunityWonOn], OwnerId, OpportunityStatus, OpportunityValue, LeadSource, SalesStage, CustomerId, Region, 'Microsoft Dynamics 365 Sales', LastDataUpdate
    FROM OpportunityBase
    WHERE [OpportunityWonOn] IS NOT NULL
    UNION ALL
    SELECT SalesOpportunity, 'Opportunity Lost', [OpportunityLostOn], [OpportunityLostOn], OwnerId, OpportunityStatus, OpportunityValue, LeadSource, SalesStage, CustomerId, Region, 'Microsoft Dynamics 365 Sales', LastDataUpdate
    FROM OpportunityBase
    WHERE [OpportunityLostOn] IS NOT NULL
)
SELECT
    SalesOpportunity,
    ActivityName,
    ActivityStartTime,
    ActivityEndTime,
    SourceSystem,
    LastDataUpdate,
    OwnerId,
    OpportunityStatus,
    OpportunityValue,
    LeadSource,
    SalesStage,
    CustomerId,
    Region
FROM EventLog
WHERE ActivityStartTime >= '[Start date parameter]'
  AND ActivityStartTime < '[End date parameter]'
ORDER BY SalesOpportunity, ActivityStartTime, ActivityName;

准备开始了吗？

立即使用此数据模板优化您的Lead to Cash流程。发现低效环节，推动更好的业务成果。

返回改进指南下载模板

提升赢单率：立即优化Lead to Cash

释放优化后的销售管道潜力，将赢单率提升20%。

开始免费试用

免费试用14天，无需信用卡。

您的Lead to Cash数据模板

您的Lead to Cash数据模板

线索到收款属性

线索到收款活动

提取指南

使用Azure Synapse Link导出Dynamics 365数据

步骤

配置

a 示例查询 sql

使用SQL TDS终结点直接查询Dataverse

步骤

配置

a 示例查询 sql

使用Dynamics 365 Web API通过Python脚本提取数据

步骤

配置

a 示例查询 python

通过Power Automate Flow定时导出数据

步骤

配置

a 示例查询 sql

准备开始了吗？

提升赢单率：立即优化Lead to Cash

您的Lead to Cash数据模板

您的Lead to Cash数据模板

线索到收款属性

线索到收款活动

提取指南

使用Azure Synapse Link导出Dynamics 365数据

步骤

配置

a 示例查询 sql

使用SQL TDS终结点直接查询Dataverse

步骤

配置

a 示例查询 sql

使用Dynamics 365 Web API通过Python脚本提取数据

步骤

配置

a 示例查询 python

通过Power Automate Flow定时导出数据

步骤

配置

a 示例查询 sql

准备开始了吗？

提升赢单率：立即优化Lead to Cash

我们重视您的隐私