salesforce soql campaigns with influenced opportunities

Question

I'm trying to create a SOQL query that returns a list of Campaigns with Influenced Opportunities, similar to the standard campaign report with the same name. The following doesn't seem to be returning the same values as the report:

SELECT Id,Name FROM Opportunity

where CampaignId in (SELECT CampaignId from CampaignMember where CampaignId = '??')

Any help would be greatly appreciated.

Answer 1

If you're looking for something simple, this should be enough:

SELECT Campaign.Name, Id, Name, Amount

FROM Opportunity

WHERE CampaignId != null AND CloseDate = THIS_MONTH

You can also convert it to an aggregated query that will give you totals per campaign as well:

SELECT Campaign.Name, Campaign.NumberOfOpportunities, SUM(Amount), COUNT(Id)

FROM Opportunity

WHERE CampaignId != null AND CloseDate = THIS_MONTH

GROUP BY Campaign.Name, Campaign.NumberOfOpportunities

 Looking for a comprehensive Salesforce course? Enroll now!