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.)