How to extract records based on a date criteria

[Jo_Pitts1]

Afternoon all.

I have a scenario where on a daily basis, I need to extract all leads that have had an expiry date reached.

It would be reasonable to assume that this will result in around 1,000 - 2,000 records each day needing to be extracted.

Is Bulk extract the way to go?  If so, how will I filter on the field in question?

Cheers

Jo

[SanfordWhiteman · Accepted solution]

Depending on your subscription, you may not be able to use only Bulk Extract for this particular scenario.

If your BE supports Smart Lists then run an extract on a corresponding SL.

If your BE only supports Static Lists then you'd have to run daily batch(es) to add people to a list. Then clear all the existing people in that list (or you could run that clearing run the night before, getting the order right is the point). Then extract that List. 

Or, given the small number of leads, you could use the Lead by Filter Type export (as in fact Bulk Extract is quite complex to reliably automate, if you're not familiar with building that kind of logic).  Copy your Date field to a String field whenever it changes, then that "stamped" date-like String can now be used as a filter.

Alternately you could copy 

[Jo_Pitts1]

Sanford,

I'm pretty relaxed about the logic of BE.. namely creating a job, polling for status, downloading file when ready.

That being said, if the Lead by Filter Type export is easier then I'm all for it :). 

Do you mean this?

GET /rest/v1/{type}.json?filterType={field to query}&filterValues={comma-separated list of possible values}

Why does the field need to be copied to a string?  Does the lead query not takes dates in the comma separated list?

[SanfordWhiteman]

---

I’m pretty relaxed about the logic of BE.. namely creating a job, polling for status, downloading file when ready.

Manually, sure. But there are numerous exceptions and retry handlers that you have to code for when automating it. Enqueuing can fail, and status checks need to be on a timer. The download can be interrupted and need to be Range-resumed. Etc.

Does the lead query not takes dates in the comma separated list?

---

Right, Date fields are not indexed as lookup fields.

[Jo_Pitts1]

---

Does the lead query not takes dates in the comma separated list?

---

Right, Date fields are not indexed as lookup fields.

---

That seems a little bizarre... still, we live and learn 🙂

[SanfordWhiteman]

It is kinda weird, but I think it's based on the probable (though not measured) rate of change plus  the max cardinality of a Date... ?

With some common Marketo patterns, you might be updating a Date for huge swaths of your db -- maybe even the whole db -- every day. So having it be indexed would impact performance on that side. Conversely, the number of possible values is only 366 per year, so there's a ceiling on the cardinality. I don't know for sure though, it could just be arbitrary.