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

您的线索到收款数据模板

Microsoft Dynamics 365 Sales

← 返回改进指南

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

您的线索到收款数据模板

此模板为收集线索到收款流程分析所需的关键数据提供了结构化方法，列出重要属性和关键活动，并说明如何提取这些信息。按照这些建议，您可以构建完整的事件日志，深入分析流程。

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

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

下载模板

线索到收款属性

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

5 必填 8 建议填写 5 选填

	名称	说明
	开始时间 ActivityStartTime	表示特定活动或事件开始时间的时间戳。
说明此属性记录活动发生的日期和时间，是流程挖掘中用于按时间顺序排列事件和计算时长的主要时间要素。开始时间是各类时间分析的基础，包括计算活动间的周期时间、识别延误和衡量整体流程表现。它支持生成动态流程动画，并帮助了解工作的时间分布。重要性此时间戳对于排列事件顺序和计算周期时间、等待时间等各项绩效指标至关重要。获取途径通常使用相关实体（销售机会、报价、SalesOrder、发票）中与活动对应的“createdon”或“modifiedon”时间戳。示例 2023-04-15T10:00:00Z2023-04-25T14:30:00Z2023-05-10T11:20:00Z
	活动 ActivityName	销售流程中特定时点发生的业务活动或事件名称。
说明此属性描述从销售线索到收款流程中的具体步骤或里程碑，例如“创建报价”或“收到付款”。每项活动都代表销售机会生命周期中的一个独立事件。分析这些活动的顺序和频率是流程挖掘的核心。它能揭示实际流程路径，帮助识别常见路径和替代路径（变体），并定位返工或瓶颈。这些分析对于了解流程遵循度和效率至关重要。重要性定义流程图中的步骤，是通过流程挖掘发现低效环节和偏差的分析基础。获取途径此属性通常根据Dynamics 365中与销售机会、报价、订单和发票实体相关的各类事件生成。通常需要设置逻辑，将状态变更或记录创建事件映射为标准化活动名称。示例创建销售机会向客户发送报价签署合同收到付款
	销售机会 SalesOpportunity	销售机会的唯一标识符，也是从销售线索到收款流程的主要案例ID。
说明销售机会ID可唯一标识每笔进入销售漏斗的潜在交易。它是串联相关活动的主线，涵盖从创建销售机会到最终收款的整个过程。在流程挖掘中，此属性是还原每个销售案例端到端历程的基础。它有助于分析完整流程、计算总周期时间并比较销售机会的不同路径，进而发现从销售线索筛选到收款过程中的低效环节和瓶颈。重要性这是用于关联所有流程事件的关键案例ID，可追踪每笔销售交易的完整生命周期。获取途径这是销售机会实体的主键，通常为“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	客户所在的地理区域或销售区域。
说明此属性指定与销售商机关联的地理区域，例如“北美”“欧洲、中东和非洲”或“亚太地区”。通常根据客户地址信息得出。地区是对比分析的重要维度。在“发票生成与交付时间”仪表板等场景中，可借助此属性查看不同地区的流程表现是否存在差异。分析结果有助于发现当地法规、团队表现或市场状况造成的差异，为有针对性的区域流程改进提供依据。重要性支持按地理区域划分流程，突出显示各地区在表现、合规和客户行为方面的差异。获取途径这些数据通常存储在关联的Account或Contact实体中，例如“address1_country”字段或自定义“Region”字段。示例北美欧洲、中东和非洲亚太地区拉丁美洲
	客户 CustomerId	与销售商机关联的客户账户。
说明此属性用于识别正在跟进商机的潜在客户或现有客户，并将销售流程关联到CRM系统中的特定账户。按客户或客户细分分析流程，有助于深入了解不同类型的客户如何参与销售流程。分析可以揭示某些客户的周期时间是否更长、是否需要更多返工，或赢单率是否更高。这些信息有助于调整销售方式并管理客户关系。重要性支持分析特定客户或客户细分的流程表现，帮助调整销售策略。获取途径这是商机实体上的“customerid”查找字段，关联到Account或Contact记录。示例 Contoso有限公司Adventure WorksFabrikam公司
	结束时间 ActivityEndTime	表示特定活动或事件完成时间的时间戳。对于原子事件，通常与开始时间相同。
说明结束时间表示活动完成的时刻。对于“生成发票”等瞬时事件，结束时间通常与开始时间相同。对于可衡量时长的活动，例如人工审核，此时间戳记录工作完成的时刻。此属性用于计算各项活动的处理时间。比较一项活动的结束时间与下一项活动的开始时间，即可分析流程中的实际工作时间（处理时间）和空闲时间（等待时间）。重要性可计算活动处理时间，区分实际工作时间和等待时间，从而更细致地了解流程表现。获取途径对于瞬时事件，结束时间与开始时间相同。对于有持续时间的活动，可能需要根据后续时间戳或状态变更推算。示例 2023-04-15T10:00:00Z2023-04-25T15:00:00Z2023-05-10T11:25:00Z
	销售机会状态 OpportunityStatus	销售机会的最终结果，例如赢单或丢单。
说明此属性记录销售机会的最终状态。主要结果通常为“Won”（赢单）或“Lost”（丢单）。按结果分析对于了解哪些流程模式带来成功或失败至关重要。按状态筛选案例后，可以比较赢单与丢单交易的周期时间、活动和用户参与情况。这对于计算销售机会赢单率KPI，以及识别与积极结果相关的行为十分重要。重要性区分成功与未成功的结果，支持对比分析，找出哪些流程行为有助于赢单。获取途径对应销售机会实体中的“statecode”（状态）和“statuscode”（状态原因）字段。示例赢单丢单进行中
	销售机会负责人 OwnerId	负责销售机会的销售代表或用户。
说明销售机会负责人是Microsoft Dynamics 365 Sales中被指派管理并推进销售机会的用户，通常负责与该案例相关的大部分活动。按负责人分析流程，有助于比较不同销售代表、团队或部门的表现。分析结果可以揭示优秀员工的实践经验，为其他人员找到辅导机会，并帮助了解工作量分配如何影响流程效率和结果。重要性支持按用户或团队分析表现，帮助识别优秀员工，并诊断特定个人或群体相关的问题。获取途径这是销售机会实体中的“ownerid”字段，关联到用户或团队记录。示例 John DoeJane SmithEMEA销售团队
	销售机会金额 OpportunityValue	销售机会的预估或实际金额。
说明此属性表示销售机会对应的潜在或最终收入。初始值可能是预估金额，交易赢单后可更新为实际合同金额。并非所有销售机会都相同，因此这一分析维度至关重要。按交易金额细分流程后，企业可以了解大额交易是否遵循不同流程或需要更长时间才能完成。此属性还有助于优先改进高价值业务环节。重要性提供财务背景信息，支持按交易金额分析，并优先改进高价值销售机会相关流程。获取途径通常位于销售机会实体的“estimatedvalue”（预估收入）或“actualvalue”（实际收入）等字段中。示例 50000.00125000.0025000.00
	销售线索来源 LeadSource	最初销售线索的来源。
说明此属性表示促成销售机会的销售线索来源，例如“网站”“合作伙伴”“展会”或“广告”。它用于跟踪不同营销渠道和销售线索生成策略的效果。在流程挖掘中，按销售线索来源分析对于“销售线索转化分析”仪表板至关重要。它有助于计算各渠道的销售线索转化为销售机会的比率，并识别哪些来源带来的线索价值更高或筛选速度更快。这些洞察对于优化营销支出和销售重点十分重要。重要性有助于评估不同营销渠道获取优质销售线索的效果，直接支持销售线索转化分析。获取途径通常对应销售线索实体中的“leadsourcecode”字段，销售线索通过资格审核后，该字段会转移到销售机会。示例网站合作伙伴展会推荐
	销售阶段 SalesStage	销售机会在既定销售流程管道中所处的阶段。
说明销售阶段表示商机所处的具体阶段，例如“资格审查”“开发”“提案”或“成交”。这些阶段概括了销售渠道及其进展。流程挖掘根据时间戳推导详细活动，而销售阶段则提供有价值的业务背景。在“整体销售周期时间分析”仪表板中，销售阶段用于划分流程，了解各主要阶段耗时。这有助于识别耗时最长的阶段，以及商机通常在哪些阶段停滞。重要性提供商机进展的业务背景，便于分析销售渠道各阶段的耗时。获取途径此信息取自与商机实体关联的业务流程中的“stepname”字段。示例 1-资格审查2-开发3-提案4-成交
	产品类别 ProductCategory	商机中销售的产品或服务类别。
说明此属性用于对销售商机关联的主要产品或服务进行分类，例如“硬件”“软件”或“专业服务”。按产品类别分析流程，是“订单履行表现”仪表板的重要用途。此分析有助于判断某些产品的销售周期是否更长、履行流程是否不同，或赢单率是否更低。根据这些发现，可针对不同产品线制定专门的销售流程，提升效率。重要性支持根据销售内容分析表现，揭示特定产品相关的流程差异和瓶颈。获取途径此信息通常可从与商机关联的Opportunity Product（opportunityproduct）实体中获取。示例云服务本地部署软件咨询服务
	付款条件 PaymentTerms	发票约定的付款条件，例如开票后30天或60天付款。
说明此属性规定客户支付已交付商品或服务费用的条件，并明确开具发票后的付款期限。在“收款周期分析”仪表板中，此属性不可或缺。您可以按约定条件划分收款时间，识别经常逾期付款的客户，并判断某些付款条件是否会导致收款周期延长。这有助于优化应收账款流程并管理现金流。重要性为分析付款表现提供基准，帮助识别逾期付款并评估收款策略的效果。获取途径此信息通常存储在Invoice实体中，字段名称常为“paymenttermscode”。示例开票后30天付款开票后60天付款收到发票后付款
	合同类型 ContractType	与销售相关的法律合同类型，例如“新业务”或“续约”。
说明此属性用于对销售商机关联的合同进行分类。不同类型的合同，例如新客户协议、续约或增购，可能采用不同的审批和签署流程。 “合同签署周期时间”仪表板使用此属性划分分析范围。比较不同合同类型的签署时间，可以判断续约是否比新合同处理得更快，或复杂的定制合同是否造成明显延误。这有助于确定合同管理流程的改进重点。重要性通过识别哪些合同类型会造成流程延误，帮助分析并优化合同签署阶段。获取途径这可能是Opportunity或Contract实体上的自定义字段，具体取决于业务需求。示例新业务续约业务拓展主服务协议
	折扣百分比 DiscountPercentage	报价或订单适用的折扣百分比。
说明此属性记录销售报价或订单相对于标价的总折扣百分比，是监控销售利润率和定价规范执行情况的重要指标。 “报价管理与折扣合规”仪表板使用此属性分析折扣情况。通过查看折扣分布，管理者可以识别过高或未经授权的折扣，评估折扣对赢单率的影响，并确保符合定价政策。重要性此属性对于分析定价策略和折扣合规至关重要，有助于了解折扣对盈利能力和赢单率的影响。获取途径位于Quote（“discountpercentage”）和SalesOrder（“discountpercentage”）实体中。示例 0.050.100.15
	是否返工 IsRework	用于标记某项活动是否在同一案例中重复或返工。
说明此布尔属性是一个计算字段，用于识别单个销售商机中重复发生的活动或活动顺序。例如，创建报价后遭到拒绝，随后又创建一份新报价，则第二次“创建报价”活动会标记为返工。识别返工对于“流程变体与返工分析”仪表板至关重要。通过突出显示循环和重复步骤，此属性有助于量化流程低效程度。了解返工发生的位置和原因，是简化流程、减少无效工作并缩短销售周期的第一步。重要性直接标记流程低效和循环，帮助量化返工成本并发现简化流程的机会。获取途径这是一个计算属性，由流程挖掘工具分析每个案例的活动顺序得出。示例 truefalse

必填建议填写选填

线索到收款活动

这些关键流程步骤和里程碑应记录在事件日志中，以清晰呈现线索到收款的全过程。

7 建议填写 7 选填

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

建议填写选填

数据提取指南

如何从Microsoft Dynamics 365 Sales获取数据

步骤

前提条件：确保您拥有具备参与者访问权限的Azure订阅、具有系统管理员权限的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工作区。
选择要复制的表：选择线索到收款流程所需的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配置：设置链接时，可选择“仅追加”模式。该模式会保留变更历史记录，有助于流程挖掘，但可能增加存储用量。对于此查询，使用标准的“就地更新”模式即可。
表选择：谨慎选择要复制的表。仅纳入与线索到收款流程相关的表，以缩短同步时间并降低存储成本。主要表包括：opportunity、quote、salesorder、invoice、contract，以及account和systemuser等相关主数据表。
日期范围筛选：默认情况下，提供的查询不包含日期筛选条件。建议添加WHERE子句，通常按opportunity.createdon日期筛选，将分析范围限定在最近6至12个月等相关时段，从而显著提升查询性能。
前提条件：您必须拥有相应的Azure权限（资源组的Contributor权限）和Power Platform权限（System Administrator权限），才能创建和管理Dataverse的Azure Synapse Link。

查询示例 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管理中心的“环境设置”>“功能”>“TDS终结点”中进行设置。
查找环境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。

查询示例 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 > 应用注册 > 新注册。将应用程序设为Web应用，并添加客户端密钥。记下应用程序（客户端）ID、目录（环境）ID和客户端密钥值。
授予API权限：在新建的应用注册中，进入API权限 > 添加权限。选择“Dynamics CRM”或“Dataverse”，再选择“委托权限”，选中“user_impersonation”，然后点击“添加权限”。最后，为您的目录授予管理员同意。
创建Dynamics 365应用程序用户：在Power Platform管理中心选择您的环境。依次进入设置 > 用户+权限 > 应用程序用户。创建新的应用程序用户，并将其关联到第1步注册的Azure AD应用程序。为该用户分配安全角色，例如“系统管理员”，确保其拥有读取所有所需数据的权限。
设置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的占位值替换为您的环境信息和所需的提取时间范围。
自定义阶段名称（如有需要）：脚本会根据“识别”和“提出方案”等业务流程阶段名称推断部分活动。检查“已识别需求”和“已提出解决方案”等活动对应的OData筛选查询，并根据您组织的自定义销售流程调整阶段名称。
运行提取脚本：打开终端或命令提示符，进入保存脚本的目录，然后运行命令：python extract_d365.py。脚本将进行身份验证，查询各项活动的API数据并处理结果。
检查输出文件：脚本成功运行后，会在同一目录中生成名为d365_lead_to_cash_event_log.csv的CSV文件，其中包含提取的事件日志。
准备上传：打开CSV文件，确认表头和数据格式正确。文件应包含SalesOpportunity、ActivityName和ActivityStartTime等列。完成后即可将文件上传到ProcessMind进行分析。

配置

身份验证凭据：脚本需要Azure Active Directory应用注册中的应用程序（客户端）ID、客户端密码和环境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。

查询示例 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填为14个准确活动名称之一，ActivityStartTime填为事件时间戳，ActivityEndTime在可用时填为完成时间戳，SourceSystem填为Microsoft Dynamics 365 Sales，LastDataUpdate填为工作流刷新时间戳。有数据时，也请填入建议的属性。
将标准化后的记录汇总为数组，创建CSV表格，并将文件保存到SharePoint或其他经批准的云存储位置。采用稳定的文件命名规则，例如[Process name]_[Run timestamp].csv。确保CSV使用UTF-8编码并包含标题行。
按照下文所述检查输出。确认14种活动名称均可作为记录出现，SalesOpportunity已填入，时间戳有效且顺序符合预期，并通过稳定的事件键控制重复记录。
使用已配置的导入流程，将验证通过的CSV上传至ProcessMind。将SalesOpportunity映射为案例标识符，ActivityName映射为活动，ActivityStartTime映射为开始时间，ActivityEndTime在使用时映射为结束时间，并将其余列映射到对应属性。保留准确的活动名称，不要依赖ProcessMind推导其他事件。

配置

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

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

准备好开始了吗？

立即使用此数据模板，开始优化线索到收款流程。发现低效环节，改善业务成果。

返回改进指南

下载模板

提升赢单率：立即优化线索到收款流程

优化销售渠道，赢单率提升20%。

开始免费试用

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

您的线索到收款数据模板

您的线索到收款数据模板

线索到收款属性

线索到收款活动

数据提取指南

使用Azure Synapse Link导出Dynamics 365数据

步骤

配置

查询示例 sql

通过SQL TDS终结点直接查询Dataverse

步骤

配置

查询示例 sql

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

步骤

配置

查询示例 python

通过Power Automate工作流定时导出数据

步骤

配置

查询示例 sql

准备好开始了吗？

提升赢单率：立即优化线索到收款流程