Get total count for all unsubscribed = true in the DB | Community
Skip to main content
D
Adobe Employee
DamonT3Adobe Employee
Adobe Employee
June 27, 2024
Solved

Get total count for all unsubscribed = true in the DB

  • June 27, 2024
  • 1 reply
  • 2612 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by SanfordWhiteman

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.


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.

 

 

1 reply

SanfordWhiteman
SanfordWhiteman
Level 10
June 27, 2024
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.

D
Adobe Employee
DamonT3Adobe EmployeeAuthor
Adobe Employee
June 27, 2024

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
SanfordWhiteman
Level 10
June 27, 2024

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.

Terms & ConditionsAccessibility statement

Loading footer...