My filtered lists project
Hey all,
I posted in another thread on my early concept on creating filtered lists based on your custom fields. I got a bit ahead of myself and ended up completing most of it over the weekend. I'm posting here to see how much interest there would be for this feature and hopefully convince the Sendy folk to implement something like this 
The basic concept is to use an existing list to generate a new list based on filters you specify on each of the custom fields.
Under lists I have broken down the lists into two categories so you can see which are "main" lists and which are filtered lists. I also added pagination so you don't load tons of list details every time you load this page. You can click the create filtered list button, it will then ask you which parent list you want to use.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.21.04.pngIf you click through to a list that has custom fields there is a new option to add a filtered list from this screen.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.21.32.pngWhen you click this option you are shown a list with all you custom fields, you can then chose which one you wish to filter on and which values you want to filter off (I added a new data type called Numeric also to handle number ranges). You can check the option to "Include all custom fields in new list" so you will have your custom fields in that list too, if you don't check this it will only export name and email to the new filtered list.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.21.51.pngThe filter on the text field is very rudimentary at the moment, it just matches any text you put in there. E.g. If you want to include all countries that have the letters "can" in them you just enter "can" in the text field. I want to improve this but due to the way custom fields are stored it may just slow things down too much.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.22.00.pngWhen you have selected your criteria and click "Create" your list will start filtering the data you selected for. Due to the fact that custom fields are all stored in one column in the table on the database it is not very efficient as the filter function has to break this up first then check for matches. Using this feature on huge lists probably will cause Sendy to time out, I would suggest you increase your timeout limit in your php.ini to get best results. I've been testing with a 200k row list which has 12 custom fields, it takes between 30 to 60 seconds depending on how many fields I filter for, it will also depend largely on how fast the host machine is. I have put a simple spinning wheel overlay here so the user at least knows its busy working.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.22.25.pngOnce your list is filtered it will tell you how many records your new lists will contain and asks you to confirm. During the previous filter process a csv file was already created which will be used to create the new list, it relies on the import cron to work.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.22.37.pngWhen you confirm the list it will take you to the filtered lists section and show you the progress of the import, again this relies on the import csv cron to be active however you won't be able to use the filtered lists option if you don't have the cron enabled.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.22.49.pngWhen your data is imported into the new list it will work like any other list and you can then send a campaign using this list. In filtered lists you will also have a new option in the subscriber screen called "Filter info" which will show information on the list like which list it was filtered from and the criteria which were used.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.23.13.pngModal that appears when clicking "Filter info": https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-25 12.23.26.png
- You can create a filtered list off an existing filtered list, providing you included the custom fields by checking the "Include all custom fields in new list" when creating it.
I am sure there is a much more efficient way to handle this but I only had a weekend to come up with a solution but maybe this solution would be ideal for many people. I would love to see something like this in the official release, my code is almost done (just some clean up needed) and I will make this available to whoever wants to use it, as long as that is allowed of course.
Cheers!
Comments
yes, yes, yes, yes, yes...
Is this only idea that you would like to have in sendy or you have custom code / sendy that can do this kond of filtering?
thx,
g.
Hey g,
This is fully working code, I'm still actively working on it but fully functional in its current state.
The only problem with sharing it is that it requires changes to quite a few existing Sendy components, includes some new javascript libraries as well as making a few additions to the database (it is written to upgrade the database seamlessly though).
This means it probably would be ideal to share it as an entire package, instead of just sharing a single pages source code. I am not sure what the rules are regarding the Sendy source code and how it may be distributed and I'm pretty sure I can't just share it all willy-nilly.
Maybe Ben can clarify what the rules are regarding this.
Another issue is that this is not a solution for everybody and is not recommended for anybody with a huge subscriber database just due to the way the custom fields are stored in the database. This is my next goal, I am trying to figure out how to optimise this so filtering becomes almost instantaneous - this will most likely require quite a big change to the database.
Hmmm... you are right.
Ben is quite for some time (I'm wondering if he is working on version 2.0 of sendy) and he should give final thought about it
I'm in. This is exactly what I am looking for in Sendy. The ability to do list segmentation to avoid multiple lists is a must have in my books.
Would be great to see this as native in Sendy or as a package.
Will await to hear back on next steps and timing.
J.
Hi @DrJohnZoidberg. Thanks for sharing this. You can share your build with fellow users via PM if they want it.
Please note that should filtered list come to Sendy, it'll only be developed on my side to prevent issues arising in future that I have no clue about. Thanks once again.
I've just run some initial tests on a new database design for the custom fields to improve performance when searching. So far it looks very promising
For a filter that would have probably taken 20 seconds via the old method, this is a direct MySQL query I ran now:
Query took 0.0886 secThanks @Ben.
If anybody in interested in the source code, please pm me. Although note that I am working on improving the filtering speeds as I have noted before, so if you have 300k subscribers+ I would recommend you wait until I have hopefully completed the new code.
Updated the filter screen so I have more control over soft bounce and complaints limits when generating new filtered lists. I had previously just hard coded values, they are now selectable.
https://dl.dropboxusercontent.com/u/14569277/Screenshot 2013-11-28 23.29.22.png
Filtered lists will never include hard bounce or unsubscribed emails.