On the List Views you have always a search field for the most commonly used search tasks, to filter your list. Below the Search Input Field, you have an «Advanced» Option, which fades out the Search Query Builder.
PVYmailr allows the writing of partial Postgres SQL expressions to query, filter, and segment subscribers.
These are the fields in the subscriber database that can be queried:
| Field | Description |
|---|---|
| subscribers.uuid | The randomly generated unique ID of the subscriber |
| subscribers.email | E-mail ID of the subscriber |
| subscribers.name | Name of the subscriber |
| subscribers.status | Status of the subscriber (enabled, disabled, blocklisted) |
| subscribers.attribs | Map of arbitrary attributes represented as JSON. Accessed via the -> and ->> Postgres operator |
| subscribers.created_at | Timestamp when the subscriber was first added |
| subscribers.updated_at | Timestamp when the subscriber was modified |
Here’s a sample JSON map of attributes assigned to an imaginary subscriber.
"city": "Lachen",
"active_engaged": true,
"spoken_languages": ["English", "French", "German"],
"contributor_level": 3,
"stack": {
"frameworks": ["nextjs", "django"],
"languages": ["go", "python"],
"preferred_language": "go"
}
}
Attributes can be queried with the parameter: subscribers.attribs as described above. However, we have some plans to simplify that on a later release for non-technical users into the interface.
Attributes are powerful and can also reflect informations from your CRM/ERP or eCommerce App, and helps you to segment users more in detailed lists, to interact based on preferences, skills or bought products/services.
Like every Query Builder, it requires some technical understanding for SQL based queries. To understand it better, you will find the SQL Schemata below.
List all Subscribers which are not confirmed for Double-Opt-in:
subscribers.id IN (SELECT subscriber_id FROM subscriber_lists WHERE status='unconfirmed')
It’s note worthy to avoid mass mailings to unconfirmed subscriptions in the age of M.L and A.I driven Content Filters on ISP Level. PVYmailr User shall always tick the settings:
and maintain their List on a regular base. While unconfirmed you can send once a double-opt-in confirmation request, all others you can either keep or remove from the list.
subscribers.status='blocklisted'
subscribers.name LIKE '%user%' where if you look John or Johannes you can go with 'Joh'