Current state... Each morning, we log into SendGrid, do an advanced search, and export the results. An example of the search (typed as it would appear on the screen)...
Future state... we now have access to the Email Activity Feed API. The API documentation has a clear example of getting results by status and date, and I've been successful getting results via Postman.
However, the documentation is less clear about how to use the CONTAINS keyword within the compound query. I can't figure out if I'm encoding the request incorrectly and/or combining the three criteria together incorrectly.
Has anyone had success combining the CONTAINS (or one of the other keyword/functions) with other query criteria? If so, would you mind sharing an example?
Some specific examples I tried are below, but I should also mention I tried using their live chat support. Regardless of browser, it gets stuck on "Connecting you with a live agent...". On StackOverflow, I found one question that was resolved using a unique function, but it didn't quite have a full example combining other query criteria.
TEST: Successful request with only two criteria (date and status).
RESULT: 200-OK. Returns results that match the advanced search.
https://api.sendgrid.com/v3/messages?limit=10&query=status%3D%22not_delivered%22%20AND%20last_event_time%20BETWEEN%20TIMESTAMP%20%222025-01-02T00%3A00%3A00Z%22%20AND%20TIMESTAMP%20%222025-01-02T23%3A59%3A59Z%22
The failed examples below are my attempt to filter on the third criteria (subject).
TEST: Add at the end of the "query" so it reads as--> AND CONTAINS(subject, "Invoice")
RESULTS: 400-BadRequest. "errors": [{"message": "CONTAINS expects an array or map type as the first argument", "field": "query"}]
https://api.sendgrid.com/v3/messages?limit=10&query=status%3D%22not_delivered%22%20AND%20last_event_time%20BETWEEN%20TIMESTAMP%20%222025-01-02T00%3A00%3A00Z%22%20AND%20TIMESTAMP%20%222025-01-02T23%3A59%3A59Z%22%20AND%20CONTAINS%28subject%2C%22Invoice%22%29
TEST: Trying the CONTAINS by itself, as the only query param --> CONTAINS(subject, "Invoice")
RESULTS: 400-BadRequest. "errors": [{"message": "CONTAINS expects an array or map type as the first argument", "field": "query"}]
https://api.sendgrid.com/v3/messages?limit=10&query=CONTAINS%28subject%2C%22Invoice%22%29
TEST: Out of curiosity, I removed the "query" param name, so that the URL ended with the encoded function.
RESULT: To my surprise, I got a 200-OK with results. However, the results did not actually filter on subject as expected (i.e. I got results without the word "Invoice" in the subject).
https://api.sendgrid.com/v3/messages?limit=10&CONTAINS%28subject%2C%22Invoice%22%29
The documentation shows extra parenthesis wrapping the CONTAINS function, as in--> (CONTAINS(subject, "Invoice")) Encoded, it would look like--> %28CONTAINS%28subject%2C%22Invoice%22%29%29
So I repeated the tests above with the extra parenthesis, but they made no difference to the results.
As per the API documentation https://www.twilio.com/docs/sendgrid/for-developers/sending-email/segmentation-query-language#contains, CONTAINS function is mainly for querying on arrays(not a string function). One way to query for a subject line would be to use the LIKE operator(encoded).