Strategy input requested regarding new-subscriber imports

ℹ️ Support
1

This isn’t a problem with phpList per se, but a problem with my strategy using it. I didn’t see a more relevant category than Support. This is sort of long and involved/detailed so I will thank in advance any who take the time to read and respond!

I’m a writer and I have two public lists that I maintain for my main pen name (i.e., my real name). One is a general newsletter that I send whenever I want (but usually only once or twice a month, just not on any kind of schedule). The other is a new-releases-only list. Both of these lists are open to anyone to subscribe.

In addition to the public lists, I use one-time private lists to follow up with people who have downloaded a free book. The way this works is that I offer a book for free using BookFunnel and people who download a copy can opt-in at that time to my general newsletter list. This is usually done during date-limited promotions (“bundles”) with other books from other writers. At the end of the promotion, I download a CSV file from BookFunnel that lists everyone who requested a book copy and opted-in to my list. I import that CSV into phpList and send a one-time message to thank people for downloading the book, ask for a review, and prompt them regarding their subscription on my newsletter.

When I first did this, I only imported the CSV into a private list and, in that one-time mail to that list, I provided a link to go and subscribe to my general newsletter or new-releases-only list. Not many did, but those who did were clearly very engaged and good people to have on my list. But this is asking people who have already opted in and confirmed their opt-in to opt-in again… not a very good experience for them and a way for me to throw away a lot of subscribers.

The most recent time I did this, I made a mess of things that I definitely don’t want to repeat. I imported the CSV into a private list for a one-time followup, like before, but I also imported those addresses to my general newsletter list with the plan to prompt people to unsubscribe if they didn’t actually want to be on the list. (Originally I thought to use the Segmentation plugin as part of this, but ended up not doing so.) As usual with these imports, some people were already in my database, either on one of my two public lists or already blacklisted. (Some people apparently grab any free book they can from BookFunnel bundles even if it means “opting in” to lists they’ve already opted out of.) I only wanted to send the “thanks, and you can unsub” follow-up to people who weren’t already on one of my public lists, but that’s where things got really messy, and I ended up using PhpMyAdmin and other things to try to eliminate prior subscribers from the new private one-time list. Bleah…

Taking the first opt-in as authoritative instead of asking people to opt in again resulted in a much larger increase in my subscribers, of course. I want to continue with that overall approach, but with better data handling, and (of course) whatever I do data-wise I want to minimize or eliminate annoyance to my subscribers.

My tentative plan for the next time is still messy but probably more reliable and less work (I think):

  1. Import the CSV to a private one-time list only. This list would be temporary and not used for a campaign.
  2. Use SQL in PhpMyAdmin to identify people who are on that new list as well as either of my public lists.
  3. Eliminate those existing subscribers from the CSV file. For those who remain, add a column to populate the preferred-genre attribute, based on the book they downloaded.
  4. Delete the temporary one-time list from phpList.
  5. Import the edited CSV to a private one-time list and my general newsletter list.
  6. Send a “thanks, and you can unsub if you want” follow-up to the newly-imported private list.

All of that screams “there must be a better way.” I could presumably change step 1 to import the CSV to a temporary table via PhpMyAdmin which would avoid other effects in phpList, so maybe I’ll do that.

But it’s still not a very clean process, so I figured I would throw this out to the phpList community for input. Maybe I was right in my original inclination to use the Segmentation plugin? Part of the problem is that preferred-genre attribute. If an existing subscriber has that set (whether I set it or they did), I don’t want to overwrite the old data, but a new subscriber should have it set based on the genre of the book they downloaded.

If you managed to read this far, thanks! (And if you want a free book, just ask, ha ha…)

2

@Crenel84 It’s a bit difficult to figure out exactly what you are trying to achieve, but if you have a set of email addresses then phplist will tell you which are already in the database

Menu subscribers > manage subscribers > verify subscribers

1 Like
3

Oh, right on – that is very helpful! Had not seen that feature. Thanks!

The main goal is to send a “thanks” message (including a prompt to unsubscribe, since many people just want the free book) to only new people who downloaded a free book, without affecting anyone already on one of my two public lists and without affecting any very-new people who didn’t download a book.

Example: Alice is on my newsletter list, Bob is on my new-releases list. I import a CSV that has Alice, Bob, Cindy, and Dean. While I’m writing up the “thanks” message, Elaine signs on to my newsletter through the regular Subscribe page. The “thanks” message should only go to Cindy and Dean, since Alice and Bob are already subscribers and Elaine didn’t download a free book.

Using the Verify subscribers tool, I think I can get what I want this way:

  1. Copy & paste addresses from the CSV into the Verify subscribers tool.
  2. Using the Existing subscriber results, remove existing subscribers from the CSV.
  3. Add the preferred-genre column to the CSV.
  4. Import the CSV to a one-time list and my regular newsletter list.
  5. Send the “thanks” message to the one-time list.

It’ll be up to me to figure out a scripted way of doing step 2 since doing it manually would be error-prone and time-consuming, but that shouldn’t be too hard.

4

@Crenel84 There is also the Invite plugin, which looks like it will help. See https://github.com/michield/phplist-plugin-invite/

There are a couple of problem with that plugin, one of which looks like it would affect your use, to which I have submitted a patch.
So if you want to try the plugin then I recommend using my patched version until the patches are applied to the master plugin version.

Use this URL to install the patched version https://github.com/bramley/phplist-plugin-invite/archive/master.zip

5

Thank you, I will take a look at that too!

6

In case anyone is interested, here’s a follow-up detailing a solution that seems to scale fairly well (although I’m still unsure about creating so many one-time lists in phpList).

  1. Download source CSV file of subscriber data (in my case, from BookFunnel).
  2. Use the “Advanced Search” capability (provided by the Subscribers plugin by @duncanc) to “export to Excel” (actually CSV) the complete subscriber lists for my two main lists (regular newsletter and new-releases-only newsletter).
  3. Open the source CSV file in LibreOffice Calc and add a blank column adjacent to the column containing email addresses.
  4. Using LibreOffice Calc, open the CSV files exported from phpList and copy the email address data into the blank column created in step 3.
  5. In the Calc spreadsheet opened in step 3, select the two columns of email addresses (source from BookFunnel and pasted-in from the other files).
  6. Use conditional formatting to highlight duplicate values. (Format -> Conditional Formatting… -> Condition… -> Cell value is -> duplicate -> Apply style -> (something eye-catching))
  7. In the column of email addresses from the BookFunnel source, delete any duplicate values now marked with conditional formatting.
  8. Delete the column containing pasted-in addresses.
  9. Delete other extraneous columns (device type, download date, etc.)
  10. Add a “Primary Genre” column and populate all rows in that column with the genre of the book that was downloaded.
  11. Save the CSV file. (In my case I “Save as” so that I can retain a pristine copy of the source data.)
  12. Import the CSV file in phpList with the “Omit Invalid” checkbox checked, and choosing a new one-time list as well as my general newsletter list for the import.
  13. Send a “thanks for downloading, click this link if you really didn’t want to subscribe” message to the new one-time list.

I’ve also added a new list category of “Archived” to hold older one-time lists.

Step 7 doesn’t scale as well as the rest, so I’ll eventually create a script that will process the CSV files rather than me doing it manually in a spreadsheet. This routine seems to work for now, though, while my total # of subscribers is still well below 1000.