Send campaign to segment of list

nickwynja commented 3 years ago

This feature would allow using queries to send to a segment of a list. This is a pretty standard approach in email service providers. This is somewhat related to #129. The problem I see with creating dynamic "query lists" is that when someone wants to unsubscribe, they might only be unsubscribed from that list and not the "master" list.

To avoid this, I'd like to send a campaign to a segment. For example, I'll add a customer to the master list when they register and include a custom attribute with the subscribe date. Then I could send a campaign to a sub-segment who have registered in, for example, the last 30 days based on a SQL query. The campaign send would still be considered a part of the master list so that an unsubscribe would be unsubscribed from the master list.

Thanks for making listmonk! I've been looking for something like it for a long time.

knadh commented 3 years ago

Thanks @nickwynja.

The problem with creating a sub list based on a query is that the results can include subscribers from multiple lists, in which case, it would not be possible to have a single parent list.

nickwynja commented 3 years ago

I see. I think this could be managed by the segment being a sub-query just like is happening on /subscribers/lists/<id> page now. When you query on that page, it appears to only be querying against the list. Here I can search for names or attributes and results are only showing members of the current list.

Thanks @nickwynja.

The problem with creating a sub list based on a query is that the results can include subscribers from multiple lists, in which case, it would not possible to have a single parent list.

knadh commented 3 years ago

Closing this as there's a longer discussion on the same matter here https://github.com/knadh/listmonk/issues/250.

nickwynja commented 3 years ago

@knadh In your comment, you referred to this (self) issue. Did you mean to refer to a different ticket or did you mean to comment and close a different ticket?

alerque commented 3 years ago

Please re-open this issue (or link to whatever issue is actually tracking this). It is still a needed feature and the linked discussion issue is ... self referential.

knadh commented 3 years ago

@nickwynja @alerque looks like I've lost track of the issue I really meant to reference, and now I'm unable to find it either. Re-opening this.

alexbrazier commented 2 years ago

Would it be possible to do something here to filter the list based on a query/segment https://github.com/knadh/listmonk/blob/3386de40c74740b2577e5344e9f6456e9ba50502/queries.sql#L635-L637

 WHERE subscriber_lists.status != 'unsubscribed' AND
    ## Either grab a query saves in a segments table or get camps.query directly
    ## Not sure if it's possible to convert the string into a real WHERE condition though
    (SELECT query from segments WHERE id = camps.segment) AND
    id > (SELECT last_subscriber_id FROM camps) AND
    id <= (SELECT max_subscriber_id FROM camps)

Option would either be to store a new field query directly in the campaign - so you would select a list and an optional query or have a new segments section which would store the query and allow you to view the count of subscribers. Maybe a segments could include the lists so when creating a campaign you would select either a list or a segment.

This would allow us to create separate campaigns for different regions (based on a subscriber attributes) but using a global list.

knadh commented 2 years ago

Yep, this is along the lines of the earlier discussions on using sub queries. Campaigns could have a query field in the DB (and on the UI). This isn't trivial however. Right now, there's one global query that fetches subscribers in batches for all running campaigns. This will have to change to one query per campaign in addition to the query itself moving to string interpolation to include arbitrary per-campaign query expressions. That also brings in performance considerations.

josylad commented 2 years ago

I really hope segmentation will be added to Listmonk soon! it is a very important feature for any mailing software.

rpmcoding commented 1 year ago

Any eta on list segments?

malv007 commented 1 year ago

Our use case is the following: Our subscribers receive emails containing events based on their preferences by subscribing to a list. We have a job that runs everyday and populates each subscriber's "attribs" field with a list relevant events, but some days some subscribers may not have any events. Once the "attribs" field has been populated for all users , we create and start a new campaign. The problem we have is that we don't want subscribers to receive an email for a particular day if they don't have any events in their "attribs" property, and right now they are getting an email with no events.

I believe having a "query" property for each campaign will help with something like this. Right?

Do you have any other ideas as to how to implement this using listmonk?

satoshinotdead commented 1 year ago

I really hope segmentation will be added to Listmonk soon! it is a very important feature for any mailing software.

Any update here? Did you make some progress that could be engaged to contribute on code?

It's a must and I suspect that could be a major change on Listmonk scalating steps :)

knadh commented 1 year ago

Hi @satoshinotdead. Haven't started working on this yet.

satoshinotdead commented 1 year ago

There is a query as workaround to export and import while it's developed? My skills are limited but I like to try and learn.

And I chose to use open-source, all the way (moved from Mailerlite here).

knadh commented 1 year ago

Create a temporary list
Use the "Advanced" search to query and segment using SQL expressions. See docs.
In the results, click on the table checkbox to check one or more subscribers. Use the "Manage lists" bulk option to add the users to the temporary list.
Send campaign. Once done, delete the temporary list.

satoshinotdead commented 1 year ago

Thanks! I have readed before reply because it seems that specific query is not there.

Please, can you give an example about _how can we check open or clicks for sample_campaign_ from a SQL query to begineers?

Big kudos for Listmonk.

relikd commented 9 months ago

Create a temporary list

Use the "Advanced" search [...]

I've seen this suggestion a few times now. What about managing a list of SQL snippets/filter? So you would define a filter once, eg. select all subscribers which do / do-not have a custom attribute "telephone". And, in a campaign, you can optionally select a single filter which is then applied before send.

I am not sure if this will make implementing easier, but the snippets could be re-used throught the UI.

josejuanmontiel commented 4 months ago

Hi @knadh i think the difficult is that POST /api/subscribers/lists send the list of the ids of the subscribers returned by the query... as intro what do you think if we add the compoment of list seleccion and new button "dinamic list" (after test the query) imagen we could add the actual snipped of the query to the table of list (in later iteration we could edit.. in list form) and then ... to update the subscriber dinamicaly... we can launch an update with the same query to retreive the list ... but now inside NextSubscribers (in pipe.go) update the subscriber_lists table for al this subscribers with the "dinamic list"...

This could be an easy iteration to add "dinamic list" without much overhead... to later consideration:

Performance (create another scheduler to prepare the data before... we are talking about thousands of subscriber)
Unsubscribe (with this aproach i think won't be a problem because mantain the same way of use)

For future we can improve:

The workflow of the segmentation ... other tools create a "temporal tables" for the segmented subscriber, may we can "integrate" dungbeetle ;) in some way
add some kind of LowCode to define some workflow bifurcation in segmentation ... mayby using blocky https://codesandbox.io/s/blockly-vue-component-8m460?file=/src/App.vue

But, for the first step i think could be easy. What do you think... open a PR? :)

knadh commented 3 months ago

Hi @josejuanmontiel. This indeed warrants a discussion, but it wouldn't be feasible until the next release, which has massive changes (multi-user support). We can pick up this discussion after the v4.0.0 release in a couple of months.

github-actions[bot] commented 3 weeks ago

This issue has been marked 'stale' after 90 days of inactivity. If there is no further activity, it will be closed in 7 days.

alerque commented 3 weeks ago

This is still a desired feature, loose the bot.

skiunke commented 3 weeks ago

This is still a desired feature, loose the bot.

So that we're on the same page, this is about creating a campaign and specifying a SQL statement to filter the list the campaigns will be applied to further by their attributes?

josejuanmontiel commented 2 weeks ago

Hi!

So that we're on the same page, this is about creating a campaign and specifying a SQL statement to filter the list the campaigns will be applied to further by their attributes?

If you ask to me... i don't understand what you said, sorry... my approach, as describe before, will be use the same query that actually return all the user that match (the query) ... to use it exactly before the campaign will launch, in this way it will be "dynamic"... later, could be interesting allow joins with other tables that could be generate in the system by other method for example like dungbeetle project.

knadh / listmonk

Send campaign to segment of list #250