Endpoint or GET workaround for 30k+ list or program status? (using Klipfolio)

[Jared_Eliason1]

We are using Klipfolio for Marketo reporting and so far I have used /rest/v1/list/0000/leads.json
and /rest/v1/leads/programs/0000.json to create datasources.

But one limitation is "you will only be able to pull in static lists that contain less than 30,000 leads". Therefore, to take email as example, I can neither pull a static list or pull by program status for Sent and Delivered for large email sends. 

Is there an endpoint or workaround I can use to pull the integer of the # of members for both a static list and a program?

So let's say we have a static list with 10,000 people. I don't want to pull in 10,000 records with the data source but rather simply GET the *size* (member count) of said list; so that the data source will return the integer 10,000, not 10,000 results. 

p.s. Anyone out there had success doing MKTO reporting through Klipfolio? Any insights, challenges overcome, best practices you have?

[SanfordWhiteman]

To persist the count, we've used webhooks triggering on Add to List, Remove from List. Catching Person was Deleted is a gap in the logic.

Honestly I don't trust any reporting tools that try to connect directly to the Marketo API.  They just aren't well-thought-out IME. The data and relationships are delicate enough that they should be ETLd (or ELTLd) separately to a database, then your reporting tool uses that db.

[Jared_Eliason1]

 Honestly I don't trust any reporting tools that try to connect directly to the Marketo API.  They just aren't well-thought-out IME.

Would tend to agree, though I've been able to create some usable and valuable custom dashboards. This list size limitation is the only real obstacle right now and I'd prefer not do manual data input on any of the reports.  

To persist the count, we've used webhooks triggering on Add to List, Remove from List. Catching Person was Deleted is a gap in the logic.

Makes sense. But not clear on the how:

1. Is there a webhook template that could be used for this? 
2. What endpoint would I use to create a data source?  

Or is there a way to use the webhooks to push Sent and Delivered list sizes to a Google sheet? (since I can add Google sheet as a data source in Klipfolio). 

[SanfordWhiteman]

Or is there a way to use the webhooks to push Sent and Delivered list sizes to a Google sheet? (since I can add Google sheet as a data source in Klipfolio). 

You can add anything directly to a sheet via a webhook. (See my blog post on "logging Marketo form fills to a Google Sheet.")

However, removing rows isn't possible this way. So you'd need to count the Adds and subtract the count of Removes, making sure to group on lead ID. (Since the same person could be "added" more than once, but is only counted as 1 member of the list in the end.)

Is there a webhook template that could be used for this? 

What endpoint would I use to create a data source? 

Can't really answer those due to Community rules.

But it's also possible to do this using Marketo alone, using Form Fills (via webhook) to increase/decrease a score on what I call a Resource Lead. Did a prez on this for our MUG I think early last year, if you want to hunt that down.

[Jared_Eliason1]

Thanks, will hunt and dig some more.

[Casey_Grimes]

From my experience with Klipfolio, the way they set up their endpoints for calling data from Marketo is a bit problematic and tends to timeout once you've got something that needs more than 4 or 5 calls' worth (~1000 data points) that needs to be visualized. The solution to this has been to use either BigQuery or Snowflake to extract the data you want and store it there, then just actually use those datasets the same way you would do for Google Sheets. The data still shows up in the Excel-esque table method that Klipfolio uses, but it's much easier to query and scale. Moving the data into Sheets proper is possible, as Sandy alludes to, but it's probably not the best tool to get the job done. 

The initial set up of transfer of data to a database is a bit of lifting, but once that's done it's pretty straightforward to integrate into your Klips.

[Jared_Eliason1]

Thanks Courtney. That was my growing hunch (that to really scale we'll need to use a 3rd party DB).

[Pierce_Ujjainw3]

Do you guys use SFDC? We have found there are a lot less reporting limitations if you're able to pull the data through SFDC.