具有受影响机会的 salesforce soql 营销事件

Question

我正在尝试创建一个 SOQL 查询，该查询返回具有受影响机会的营销事件列表，类似于同名的标准营销事件报告。以下似乎没有返回与报告相同的值:

SELECT Id,Name FROM Opportunity
where CampaignId in (SELECT CampaignId from CampaignMember where CampaignId = '??')

任何帮助将不胜感激。

Answer 1

如果您追求简单的东西 - 这可能就足够了:

SELECT Campaign.Name, Id, Name, Amount
FROM Opportunity
WHERE CampaignId != null AND CloseDate = THIS_MONTH

您可以轻松地将其转换为聚合查询，从而获得每个广告系列的总计:

SELECT Campaign.Name, Campaign.NumberOfOpportunities, SUM(Amount), COUNT(Id)
FROM Opportunity
WHERE CampaignId != null AND CloseDate = THIS_MONTH
GROUP BY Campaign.Name, Campaign.NumberOfOpportunities

(如果您在 Campaign.NumberOfOpportunities 和 Opps 计数之间发现差异，没关系。这是因为第一个字段是“所有时间计数器”，而后一个字段受到 WHERE CloseDate = THIS_MONTH)

<小时/>

如果您需要报告返回的整套数据(尤其是“已回复”)，那么这将很棘手，因为 ERD diagram for these objects有点恶心。

Campaign Influence allows marketers to measure and report on multiple campaigns that have influenced a single opportunity.

因此，我们可以以某种方式从联系人开始，通过 OpportunityContactRole 转到机会。同时从“联系人”转到“CampaignMembers”再到“Campaign”。

SELECT Name, Email, Account.Name, 
    (SELECT HasResponded, Campaign.Name FROM CampaignMembers),
    (SELECT Opportunity.Name, Opportunity.Amount FROM OpportunityContactRoles)
FROM Contact 
LIMIT 10

但是，您必须处理查询结果才能获取事件名称(例如，我认为不允许使用 Data Loader 导出子查询)。

<小时/>

最后但并非最不重要的一点 - 如果您仅对一个营销事件或仅一个机会感兴趣，则可以对其进行简化(而且您已经非常接近了!)

SELECT Contact.Name, Opportunity.Account.Name, Opportunity.Name, Opportunity.Amount
FROM OpportunityContactRole
WHERE ContactId IN (SELECT ContactId FROM CampaignMember WHERE Campaign.Name = 'Sample Campaign')