Hey Todd - I pull in program performance data through the "Leads by Program" API call. Have you looked at that one? In addition to all the standard lead fields, it lets you pull in fields specific to the lead's program membership - membership date, progression status, acquired by, reached success, etc.
I pull in that dataset with 1) lead ID, 2) program ID, and then those several membership-specific fields. Then I merge it with the full Lead record dataset and full Program dataset based on the lead ID and program ID foreign keys. From there, to do what you are talking about and keep the flexibility of changing date range, you would likely need to do some beast mode calculations... something like these columns: (I always start off with just a table card to get the data pulled together):
1. Program Name
2. COUNT(Lead ID) <= number of leads in the program
3. SUM(CASE WHEN membership_reachedSuccess=true THEN 1 else 0 END) <= number of leads that reached success in the program
4. SUM(CASE WHEN membership_acquiredBy=true THEN 1 else 0 END) <= number of leads acquired by the program
I haven't tested that, and i'm admittedly a bit rusty on my aggregations, but that might work for what you are looking for. Let me know!
Hi Todd Plunk,
We pull program member data to the database and do program performance analysis with Tableau.
Though program member data can be retrieved by the “Get Leads by Program ID” API, it can only pulls all members of designated program. So we use Data Loader for Marketo to automatically sync program member data to the database every day, which avoids repeatedly calling APIs for extracting the same records. Instead, it can update only those newly added or updated program member data including membership date, progression status, acquired by, reached success to the database.
With program member data, we combine it with lead and program data based on the lead ID and program ID to do program performance analysis. Besides, we join it with opportunitypersonrole and opportunity data based on opportunity ID and lead ID, and implement some calculations to conduct performance-opportunity analysis and marketing influenced analysis.
You can see the article via below link. Hope it can help you!