API questions: Programs and Lead Activity

[Dan_Stevens_]

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.

[Dan_Stevens_]

Thanks Greg.  Both you and Sandy have been extremely helpful thus far.  As I'm sure you've concluded, this is the first time our development team has used the API to pull in the required data needed to build out our enhanced BI environment.  So I doubt we're making the most efficient API call strategy - especially given the large amounts of data, with a 10,000 limit each day.  Just out of curiosity, are you familiar with the Data Loader for Marketo: Extract Marketo data to database | Data Loader for Marketo ?  Unfortunately I learned about this after our team was well into the dev effort.  I wonder if DLM has what we're looking for already pre-built - and something we can plug-n-play into our SQL Server environment.

VIvian Wu​

[SanfordWhiteman]

I tried an early version that required SOAP (i.e. root) access... I'm sure you know how I feel about that. Heard that requirement has been removed, but I haven't retested.

In theory, a packaged solution benefits from cases introduced by their userbase, ending up with a better-rounded product than you could build yourself. In practice, in this particular area... not so much.

So I definitely wouldn't shy away from building this in-house, but I think your developers need to get on the Community themselves. With respect, the questions they're relaying don't make a lot of sense if they've been spending real time on this.

[SanfordWhiteman]

You have to query the types.json endpoint to see the types fleshed out.  The generic model here doesn't show you a response, it shows you the schema (or scaffolding, or object structure) used by any response.

[Dan_Stevens_]

Thanks again, Sandy, but I'm afraid I'm still not following.  I'm going to loop in our developer, Grant Stephens​, to see if he can make better sense of this.

Grégoire Michel, have you any experience with what we're trying to do here?

[Grégoire_Miche2]

Hi Dan,

Sorry for answering late, I was off with the flu.

May be would it be worth starting with the activity type endpoint http://developers.marketo.com/rest-api/endpoint-reference/lead-database-endpoint-reference/#!/Activi...  that will provide you with the complete list of codes and labels for activities in your instance.

-Greg