Getting latest program status change and latest form submitted for contacts

[balintsipos]

Note: this is a question on getting these datapoints from historical data and not how we could capture it moving forwards

Hello all, 

Why do I want to have this?

I am trying to backfill data for a new field - Last Lead Source (with a new process behind it). 
As many of you probably have Lead Source/Last Lead Source set up, the field values are calculated based on UTMs primarily but with fallbacks when UTMs are not available. 
We do use Program channel > Status change quite a bit and certain forms submitted also in some source calculations. 

To backfill Last Lead Source I would need to get the latest program channel > Status change for each contact that lacks Last Lead source data. 

Similarly, though less important, getting the latest form that was submitted by contact.

Ideally I'd want to get to this level 

Contact name/ID	Latest program channel > Status change	Latest form submitted
Person A	Hosted Webinar > Attended	Global Lead Form-1001
Person B	Content Syndication > Member	Global Contact Form-1002
Person C	Web Content > Downloaded	Global Lead Form-1001

How would you go about getting this data?

This is a one-off exercise so happy to do some calculations in excel or PowerBI even if it achieves the goal. 

[Darshil_Shah1 · Accepted solution]

You can use the Bulk Activities Extract API to extract the program status change and Fill out Form activities. You can get the last form filled out and the last program status change for each person from the response. Use the Describe Activities API endpoint to get hold of the required activity ids.