We're building out a data warehouse of lead activity that will be used for our dashboards/reports in Power BI.
QUESTION 1 (PROGRAMS): During our call today, we learned that the developers are having to make daily calls to pull all PROGRAMS into our DB. I asked them why then aren't just grabbing new programs and appending them to a master Program table. Then state that it's not possible. While the GET programs call does return a "CreatedAt" date:
There's no way to make subsequent calls to only return those programs created since the last call (since there's no filter type to do so):
That makes no sense. Are our developers looking at this the wrong way - and instead should be using another approach to get just new programs (rather than the entire list each day)?
QUESTION 2 (LEAD ACTIVITY): Ideally, we need to capture specific activity based on PROGRAM STATUS (and also attribute it to the appropriate PROGRAM). For example, one of our metrics is to measure the number of RESPONSES/ENGAGEMENT (based on PROGRAM STATUS, eg., "fills out form", "engaged - downloaded", etc.) for each reporting period (daily, monthly, quarterly). So we need to filter just that activity that contains any of the defined PROGRAM STATUSES. What is the appropriate call(s) in order to grab something that ultimately looks like this (especially when ChannelID and/or ProgramID aren't included attributes)?:
ActivityID, LeadID, ProgramID, ProgramStatusID
The "GET Lead Activities" doesn't seem to contain all of these attributes - specifically the key attribute for us, PROGRAM STATUS.
Hopefully Sanford Whiteman (in addition to other API experts in the community) will provide some insight here.
Hey Dan,
IIRC, the Get Lead Activities stream contains the event Change Status in Progression, which is the old-school term for a change in Program Status. This includes the Program ID, so while it doesn't let you backfill programs that don't have any members, it will expose the ID of a program as soon as someone is added to it, at which the Asset API can be used to get the Program metadata.
Thanks for chiming in, Sandy. The GET LEAD ACTIVITIES stream only returns the following:
Unless I'm looking at this wrong, I don't see anywhere information around Program/Progression Status.
Also, with regards to my first question on building an active PROGRAMS table in our data warehouse - and considering we have over 2,500 programs today - is it true that it's required to download this massive number each day (wasting precious API calls) vs. simply updating the DB with just those programs that have changed (or that have been added/deleted)?
Hi Dan, 2,500 Programs are downloaded in 13 API calls (batch size 200) so - while I agree it would be convenient to filter on 'last updated' - it's not a huge hog of API calls. Not sure if you're planning to download folders, emails, landing pages, forms or files, but the API pretty works the same for all assets: you have to download everything every time.
Jep Castelein Do you have an example for that? How can I download 2500 programs and emails in 13 API calls (batch size 200)?
Because now I am using this call: https://891-VEY-973.mktorest.com/rest/asset/v1/programs.json?access_token=<access token>&maxReturn=200
and each time I am getting the same 200 records and I don't know how I can download the next 200 programs and then the next 200 programs and so on...
Thank you!
Shiran
Add ‘offset=200’ to your call to get the 2nd batch
Hi Shiran (and Idan Dayag),
There are a few different ways to get around this based on your request parameters; the easiest (and the sanest for me in terms of structuring where to send data) has been to actually modify my approach and use byTag to replicate the structure inside the Marketo instance. However, what you're probably looking for is to use the offset parameter. So, if you have 2500 program, your first call would be
https://891-VEY-973.mktorest.com/rest/asset/v1/programs.json?access_token=access token&maxReturn=200
But your subsequent calls would be
https://891-VEY-973.mktorest.com/rest/asset/v1/programs.json?access_token=access token&maxReturn=200&offset=200
https://891-VEY-973.mktorest.com/rest/asset/v1/programs.json?access_token=access token&maxReturn=200&offset=400
https://891-VEY-973.mktorest.com/rest/asset/v1/programs.json?access_token=access token&maxReturn=200&offset=600
etc. through all the calls you need.
Thanks Jep - that's good to know (and the team is sticking with this approach).
Hope you're doing well - long time, no talk!
Hi Dan,
We are looking at pulling all emails and programs and store this data in our DB.
We still have not figured out how to pull more than 200 rondom records.
We want to know how this can be done by several API calls.
do you have an example for that call?
Idan
Unless I'm looking at this wrong, I don't see anywhere information around Program/Progression Status.
One of the activity types (activityTypeId 104 in my primary instance, though this is not guaranteed to be the same in all instances) is Change Status in Progression, with primary attribute Program ID and auxiliary attributes:
[
{
"name": "Acquired By",
"dataType": "boolean"
},
{
"name": "New Status ID",
"dataType": "integer"
},
{
"name": "Old Status ID",
"dataType": "integer"
},
{
"name": "Reason",
"dataType": "string"
},
{
"name": "Success",
"dataType": "boolean"
},
{
"name": "New Status",
"dataType": "string"
},
{
"name": "Old Status",
"dataType": "string"
}
]
These activities are a substitute for notification that a new Program ID exists. So you don't have to download all the Program IDs every day to find new Programs, as long as you limit yourself to Programs that have had at least one member.
Detecting changes to the Program-level config, though, or if a Program has been deleted, I think requires download-and-compare via the Asset API like you're doing now. You could decide to redownload the program when triggered by a Change Status activity (that is, the first time you see a Change Status for a Program on a given day, update the corresponding Program metadata, regardless of whether you have existing metadata). Then maybe check for deletes once a week.
So the elements map as below for activity 104
primaryAttributeValueId: = programID
primaryAttributeValue = programName
Right.
ETA: ... though this isn't really apparent from the endpoint documentation. In any case you should only use the ID as the name may change.
It seems that we're still not able to pull in the appropriate data properly. Here's a an example of what we're aiming for (this is from RCE):
Can this or can this not be accomplished use a combination of API calls?
Hi Dan Stevens,
This report seems to be created by Program Membership Analysis from RCE. To pull this data, you will need to retrieve program members (includes program status), and combine them with programs (includes program attributes) and leads (includes lead information) to build this report.
DLM allows you to extract Program Member data to your database, and will only grab the updated data, so you utilize your API calls effectively.
Here is an article about Program Member data and relevant analysis for your reference.
Program Membership data and analysis
You can also generate this report with lead activities (Change Status in Progression), but it may take more effort to filter the data in current status and delete non-use data, because it contains all history of status change.
Best Regards,
Vivian
DLM allows you to extract Program Member data to your database, and will only grab the updated data, so you utilize your API calls effectively.
If you're doing accurate incremental updates of Program Members, then you must be using the Activity Log, as discussed above. There's no other way to do this losslessly.
Just to further clarify, when pulling lead activities, the desire is to include the "programName", "programID", and "Program Status". I think it's that last attribute that's still unclear. Where is "Program Status" found within the Lead Activity? I'd have to think this would be key data as part of the data returned with Lead Activity, but it's not apparent by looking at the documentation.
Hi Dan,
In the activity, you will need to look at the "program status was changed" activity. In this activity type, you will get the Old Status and the New Status.
Another API endpoint (/rest/v1/leads/programs/{programId}.json) can provide the list of leads in a program with their membership status in the program. But this is not an activity related endpoint.
-Greg
Thanks Greg. However, I don't see a "program status was changed" here:
But Dan, you don't see any of the activity type ids in the generic response model page. That's because they're described in the types.json ednpoint.. Then they're returned in the activities.json endpoint when you actually execute a query.
OK, so here's the types.json:
I'm still not seeing a "program status was changed". Or would that be the "description"? And if so, where would we lookup the "old status" and "new status" that Greg refers to?
Hi Dan,
The Types endpoint will give you the type details (or meta data). The Attributes array n the type will describe the details you ca get for each type. Typically, for the "program status was changed" some of the attributes will be "old status", "new status", "program ID", etc...
Then the activities endpoint, as you can see, also has an "Attributes" array that will contains the actual values. The tricky part is that the list of attributes is different between each activity types. And your client fetching the data needs to call the "type" end point first to get a description of these attributes array so that it can parse them.
-Greg