Getting SF report information using SOQL

Question

I have a SF report created in the Salesforce of type 'Contacts With Opportunities'. I need to set the ETL automation for this report using python.: Please have a look at following screenshot:

I have tried to download this report using 'Salesforce Reports and Dashboards REST API' but SF Reports API has limit where we can only pull 2000 records and my report has more than 2000 records. So I believe Reports API would not be the solution for me.

So, as an alternative I was thinking to create an SOQL query to pull this information. Is it possible to get this information using SOQL? If Yes, then you can see that in the report filter conditions there is a cross filter condition to filter the 'Contacts' based on the 'Campaign History'. In SF we need some relationship fields to filter parent records using child records. But I am not sure which relationship field is there between 'Contacts' and 'Campaign History'.

Can anyone help me here?

Answer 1

You can still use the Reporting API, you just have to be a bit cunning. Posting to the query endpoint

/services/data/v37.0/analytics/reports/query

allows you to run a modified version of a report without saving your changes to the report i.e. you can take an existing report, add extra filters, and run it. See https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_report_query_example.htm

So, you can modify the existing report to order it by some unique field - that unique field can even be a record id, as long as it is different for every row in your reporting.

Run the report, which gives you back 2000 records. Then look at the value of your unique field for the last row. Re-run the report with an extra filter that your unique field must be greater than the last value from the previous 2000. Then repeat until you have everything.

It's like the technique used here to do the same with from inside Apex: How can I get a report to return more than 2,000 rows using the "ReportResults" class in the "Reports" namespace?

Answer 2

I think it is possible using SOQL. As reports are only visual representation of data.

Now what you are seeing as Campaign History in report is not actually CampaignHistory object but is Campaign object itself.

Campaign object is related with the Contact/Lead through a junction object CampaignMember.

The Campaign Member junction objects in Custom Report Types When you see the Campaign Member table, you see it in two ways, depending on the direction you look: From Contacts to Campaigns, we see the Campaign History object.

From Campaigns to Contacts, we see two alternatives:

Campaign members - which is both leads and contacts, but since there is no combined object or page layout, few lookups and no related lists

Contacts - for contacts only, and all its lookups to other objects and related lists of other records

Now you need to perform SOQL on CampaignMember object.