Get total count for all unsubscribed = true in the DB

[dtavangar]

Hello! As the subject line reads, I have been trying to get a total count for all unsubscribed in the market DB. The unsubscribed is a boolean, and also due to the business requirements I am not able to manually create a smart list and then query that via APwhich makes it more difficult to get because:

1. I cant query the system smart list via API 

2. I can't query boolean fields via API

 I can get part of the data by using the activity API and filter by ID and then use the ID of the activity, but this has 90-day data retention, so I have not been able to get the total count, only 90 days' worth of data.

Another logic I tried was to do a bulk extract with time periods. So, If I set the start date at 2020, then the code will create jobs for every 31-day span. However, this will take a very long time to complete and also cause performance issues.

Any recommendations on how to get this data?  I appreciate the help.

[SanfordWhiteman]

Another logic I tried was to do a bulk extract with time periods. So, If I set the start date at 2020, then the code will create jobs for every 31-day span. However, this will take a very long time to complete and also cause performance issues.

 What performance issues? You run a baseline via Bulk Extract and then just get people updated in the past month.

[dtavangar]

I need the total count for all leads in Marketo DB that have unsubscribed field set to true.  that's all I am looking for. The SQL query would be :

SELECT count(*) FROM table WHERE some conditions. 

if I needed only to go back 31 days it would be walk in the park, but I need the total count with no time constraints. 

[SanfordWhiteman]

It’s a one-time Bulk Extract to catch up. That will take awhile but you run it one time, ever.

Then you can get all people updated each day using the Get Lead Changes endpoint. Yes, of course you need to persist the previous result (a single integer!) in order to update it with the current day’s result. A quite basic ask, we have many processes running like this.

You’ve also said you aren’t allowed to create a filtered Smart List for some reason — that would of course be even easier as you can just get the count of records via the API.

[dtavangar]

Thanks, Sandford; there isn't any other endpoint or approach to getting this count.

Would you know why Marketo has made it so hard to get data that is already available in the DB? It seems they have specifically put in rail guards to prevent people from getting this data. I guess I am spoiled from being an open-source developer where you have access to it all 🙂 

I appreciate your help and response, but if there truly isn't any other way I need to work on building an internal DB or a warehouse to store some of this data and then query against that, it just seems like overkill to find out how many subscribers you have systematically. 

[SanfordWhiteman]

Having a data warehouse is good for a variety of applications – we heartily recommend it – but here you don't need one. It's just that you're hamstrung by not being willing (why?) to create a Smart List.

(Also it's less about OSS vs. closed source than about hosted service with API-mediated access vs. on-prem with DB credentials available. It's quite possible to have hosted open source apps which don't give you DB/shell access.)

[dtavangar]

The data I am trying to grab is already in a system Smarlist, so manually creating smart lists wouldn't be efficient (creating a smart list for a smart list). We need to fetch the data programmatically, so if it were possible to create a smart list via the API and then fetch the data, that would be an idea I'd consider, but we aren't able to do that either. Our internal processes are all fully automated, and I need this data in a weekly report for our teams. I wish there were an automated way of using the Marketo engine without the UI and all through the code. Why are the APIs so limited? We can argue the Smart list's complexity, and I agree with almost anything you say, but the data is already inside of the DB, so why not allow us to fetch the data? I get a feeling it'll come down to performance.

[SanfordWhiteman · Accepted solution]

Well, every API in the world is limited. Limitations are really what define an API: the features of the underlying infrastructure it chooses to expose. Otherwise we’d be back to hitting companies’ SQL servers directly with only IP restrictions... there’s a good reason that era is in the past!

It still isn’t clear why the task of creating a Smart List in the Marketo UI matters. You aren’t writing a commercial app (where this would have to be done by each customer) so it’s one time, ever. A no-brainer for any MOps team.