Newsletter Mailing System

About: New API Procedure to mail a Newsletter - fastblogit

Comments

Seth says
👍

Seth says

…

of course

the system

evolves

Seth says
The email field in the Tome database will be converted to this grammar.

<empty> | <email list> | <status> <email list>

where <email list> is a space separated list of legitimate email addresses
<empty> is a field containing no characters
<status> := “ok” | “bounce” | “unsubscribe” | …

Only records with no status (fields starting directly with an email) or “ok” will get bulk mailed to.

Limitation: all emails at one physical address record must be in the same status.

Challenge: deduping in this situation.

or much simpler and solve deduping problem, but give up multiple emails for same address

<empty> | <email> [ <status> ]

<empty> is a field containing no characters
<status> := “ok” | “bounce” | “unsubscribe” | …

Note: after conversion program, new addresses introduced via direct input, or online store, will have their status empty.

Seth says
Convert this regex to Foxpro and use it in emailConvert.prg & maillist.prg …

function validateEMAIL($EMAIL) {
    $v = "/[a-zA-Z0-9_-.+]+@[a-zA-Z0-9-]+.[a-zA-Z]+/";

    return (bool)preg_match($v, $EMAIL);
}

stack overfl

and maybe even do an error trap for Denies’s fingers in #tome …

#shucks FoxPro has no regex expressions without integrating it with a lot of other software. not going to happen. i will need to rely on the emailVerify to do all the email validation.

Seth says
restoring the test environment from the live environment …

#backup

run dos procedure above
rebuild email index as per code, click decending
locate the 2 badly coded “unsubscribed” and manually fix them
count should be around 55721 + the ones we added
run test again and hope for success

apparent success

Seth says
got this part working ..

Seth says

← what are those 18 unknowns ?

Seth says

looks good
but if i run it again i wipe out the unsubscribes
that should not happen

Seth says

hopefully the final conversion run
is stable

Seth says
moving on … next phase is writing the files and seeing how they are used.

Seth says
re programming old #unsubscribes to get into #SparkPost #SuppressionLists

i wonder if this is a reasonable URL for my “inmotion emails table” ← yes it is

obviously you need to be logged in to their sql server to get it.

Seth says
http://www.speaktomecatalog.com/unsubscribe_form.php?email=russell.seth@gmail.com

one can export the entire table with a the Export wizzard at top of the php myadmin … but i just want to export the email address and names of those which have status = unsubscribe.

i wonder how …
try google mysql export may or may not help… nope this just seems to be for the entire db … no place to put the select.

try google mysql write csv … nope

… dudes just want to hire me

try google mysql csv…. wow maybe “6.4 The CSV Storage Engine” looks like will work

…
but now i will need to find this allegid “Database Directory” over on inmotion …
or find a way to send it to a download.

CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)ENGINE = CSV;

then use the intert into select from the email table, i suppose ...

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    [PARTITION (partition_name,...)]

    [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

sounds like something that i need to put into a program.

shucks … how could i cause a download of a file from the allegid “Database Directory” ?

most of the elements are in this program … http://www.speaktomecatalog.com/unsubscribe_form.php?email=russell.seth@gmail.com … ~~guck did i just unsubscribe myself?~~

Seth says
http://speaktomecatalog.com/db/unsubscribes.php creates a list of old #unsubscribes to put in #SparkPost’s #SuppressionLists

Seth says
things to do ….

read old unsubscribes into #sparkPost suppression list see Newsletter Mailing System (comment 76681)
perhaps write subroutine in newsletter.prg to read old unsubscribes
generate test list based upon estatus= “test”
- code records ← #done
- write program in newsletter.prg ← #done
- test it into SparkPost ← #done
understand and program the unsubscribe #done
- format for the hyperlink in the actual newsletter
- <a data-msys-unsubscribe="1"href="http://speaktomecatalog.com/unsubscribe.php"title="USEFUL_NAME">Click here to unsubscribe from our newsletter</a>
- yielding→ Click here to unsubscribe from our newsletter
- …
Generate an actual mailing list #Done
Compose an actual newsletter ← in process
Send one in test
Find out how to use (probably postman) to send it out
Send the to the test list
Send out the newsletter
Retireve the bounces
Program their change in status in our database

Newsletter Mailing System

About: New API Procedure to mail a Newsletter - fastblogit

Tags

Comments

See Also