Author Topic: Scheduled Email - What's Coming Tomorrow (or by date)... (Read 5739 times)

BGSWebDesign · « **on:** February 01, 2006, 02:42:43 pm »

Hi,

I just thought of this which would be very useful!

I use many CRON Followups, the difficulty is in knowing how far along my lists are (I have 40-50 lists) in the CRON and how many will be coming due tomorrow, or the next day, or the day after that.... (one day at a time would be fine).

Is there a quick SQL command you could give us to run on the entire database that may tell us WHICH followups (how many from each list) will be coming due to be sent out tomorrow - remember that CRON followups may be on Delay=0 but still not go out - so if the command needs to include a statement that can detect this it would be great?

As an alternative I guess I could run DailyMail and turn OFF (check all of the checkboxes), but I'm not sure that reports how many messages would be sent?

Any help on this would be appreciated...

DW · « **Reply #1 on:** February 01, 2006, 07:46:45 pm »

Brett,

It should definitely be possible to project the date into the future with PHP and then test the cron and followups based on the projected day.

I like this idea. Where do you think this information would best be presented? I'm thinking on a summary/report page.

Regards, DW

BGSWebDesign · « **Reply #2 on:** February 02, 2006, 05:14:21 am »

Hi DW,

Quote

I like this idea. Where do you think this information would best be presented? I'm thinking on a summary/report page.

There are a few places I see it could show up, 1.) Send an Email page, 2.) Link Tracking Page, and/or on a Summary/Reports page. Any or all of these would be great.

How long before we may see this? I'm having a heck of time trying to do this on paper - keep track of which CRONs are due to hit this week, or tomorrow. I originally was using monthly scheduling but have now moved to bi-weekly, and each of the bi-weekly emails also has a followup 2 or 3 days later, so it's a 'virtual nightmare' trying to keep track of what's going to be going out and when - making it very hard for me to plan my other scheduled mailings - such as newsletters and other promotions!

Please let me know when this might be available?

DW · « **Reply #3 on:** February 02, 2006, 06:36:02 am »

Brett,

I will try to help you out with a custom script for this tonight. Doing it custom will make it quick to code because I won't need to work it into the interface. Of course, I'll be taking notes for ListMail while I'm at it. :wink:

Regards, DW

BGSWebDesign · « **Reply #4 on:** February 02, 2006, 12:23:35 pm »

Hi DW,

Great! I can't wait... to help you out I just signed on to DreamHost (as an affiliate) listing you as the referral - that way anyone under me should turn you some profit too

Thanks again, and let me know what you come up with...

DW · « **Reply #5 on:** February 02, 2006, 08:22:30 pm »

Thanks for the referral at DreamHost!

Now that I'm about to start on this I realize I may have missed some details. You say you want to know "which crons will hit this week", etc. Of course, this can be different for each and every user who is subscribed.

What type of information, exactly, would you like to see?

Regards, DW

BGSWebDesign · « **Reply #6 on:** February 03, 2006, 09:23:58 am »

Hi DW,

Quote from: "DW"

Now that I'm about to start on this I realize I may have missed some details. You say you want to know "which crons will hit this week", etc. Of course, this can be different for each and every user who is subscribed.

What type of information, exactly, would you like to see?

Oops, I see what you mean.... ok, one reason I didn't think of this is because most of my lists are closed and don't have new subscribers, so those CRONS all hit at the same time for those lists - there are NO different subscribers hitting CRONS at different times - also, since many of my CRONS only hit on certain days such as Mon only, or Thur only, or Mon-Tue only, it's a little easier to predict.

Would it be real difficult to get a tally, since you can use Delay to figure out how many days out each are from each list, right, and keep a running total for all lists, or split by list? Knowing the exact list is somewhat important but not as important as knowing that there will be 50,000 emails going out tomorrow.

I'm not interested in which crons by subscriber, BUT would like to see Which Lists will have mailings tomorrow, the day after, or for the entire week, next week... I see how this could be confusing, for you and I!

If I could know which lists have CRONS setup that MAY run on certain days that would be useful for me - but knowing the exact subscriber count (for each mailing), well that could be interesting too!

For example, if I could click a button and see that:

Code: [Select]


Schedule for week
---------------------
list             date(s)                   # subscribers
----           --------                    ----------------
List1          Monday                          8,542
List2          Tuesday                         5,445
               Wednesday                       4,322
               Thursday                        6,667
List3          Friday                          9,995
List4          Friday                          8,776

That's one way to view it, but now that I think about it, having the DAY of the week first might be better - like this -

Code: [Select]


Schedule for week
---------------------
Date            List                      # subscribers
----            --------                  ----------------
Monday         List1                          8,542

Tuesday        List2                          5,445

Wednesday      List2                          4,322

Thursday       List2                          6,667

Friday         List3                          9,995
Friday         List4                          8,776

And, as I said, if you can't get the list#, and just want to show me the total emails going out for the day, that could work too - but not as good, for example:

Code: [Select]


Schedule for week
---------------------
Date        # subscribers
----        ----------------
Monday        8,542

Tuesday       5,445

Wednesday     4,322

Thursday      6,667

Friday        18,771

Now, that we're on this issue, I noticed that I can NOT do a User SELECTION by selecting Delay#? Why not, as this is going to be more useful for me than Seq #, as you can see, I just did a huge mailing this morning, if I can do a selection using Delay #, I would have selected only those users which have NOT just had a mailing sent out, so I would KNOW what the Delay # would be for those users.... does that make sense?

Anyway, that's way more than enough for you to think about :? I'm sure you'll come up with something good with all of this information! Have a great weekend...

BGSWebDesign · « **Reply #7 on:** February 06, 2006, 06:27:03 am »

bump...

any ideas/thoughts on this DW?

DW · « **Reply #8 on:** February 07, 2006, 01:07:24 am »

Brett,

Due to the variety of timing on some lists and my hopes of keeping this simple we should probably only watch certain lists - the ones where everyone is at the same position in queue. This can be easily set up in an array within the script..

I've made a note to add Delay to LMP's User Selection search feature.

Here goes on the script - I must admit, it wasn't quite as easy as I had imagined.

It took me, I think, just over an hour. Copy and paste the code into a new .PHP file, upload it to your LMP folder and browse to it.

Code: [Select]

<?phpinclude("./config.php");include("./admin.php");include('./cron.php');/*// set/use password $pw = $_GET['pw'];$password = '12345';if($pw<>$password){ sleep(5); exit('incorrect password'); }*/// debug output// $debug = 1;// days ahead to show$days = '10';$lists = array('1','2');$today = date("Y-m-d");list($y,$m,$d)=explode('-',$today);echo "First projected day is NEXT dailymail...<br><br>";while(list($k,$list)=each($lists)){	// get 1 user from this list to determine seq and delay	list($cs,$cd)=mysql_fetch_row(mysql_query("select cseq,cdel from $utable where list = '$list' limit 1"));	$ocs = $cs; $ocd = $cd;	echo "List $list (cseq $cs, cdel $cd)<br><table border=1 cellspacing=0 cellpadding=0><tr><td>Date</td><td>Emails</td></tr>";	$frows = mysql_query("select seq,del,cron from $ftable where list = '$list' and seq >= '$cs' order by seq;");	if(@mysql_num_rows($frows)==0) exit('list '.$list.' has no followups - set proper lists in script config');	$fuparr = array();	while(list($se,$de,$c)=mysql_fetch_row($frows)){		$fuparr[$se] = array($de,$c);	}	for($i=1;$i<=$days;$i++){		$thetime = mktime(0,1,0,$m,$d+$i,$y);		$thedate = date("D M jS", $thetime);		$crdate = date("Ymd", $thetime);		$optdate = date("i,H,d,m,w,Y",$thetime);		if($debug) echo "<br>[Day $i] DATE=$thedate CSEQ=$cs CDEL=$cd<br>";		if($debug) echo "optdate=$optdate<br>";		// calc new seq and delay based on days ahead		if($cd>0){			if($debug) echo "cdel>0, decreasing delay by 1..<br>";			$cd--;		}		elseif($cd==0){			// check cron / send followup			list($x,$cr)=$fuparr[key($fuparr)];			if($cr){				if($debug) echo "checking cron... cron=$cr<br>";				$cron = new cron;				$cron->calcLastRan("* * ".$cr,$optdate);				$due = date("Ymd",$cron->getLastRanUnix()-1);				if($debug) echo "crdate=$crdate due=$due<br>";				if($crdate == $due){					if($debug) echo "cron is due - advance<br>";					$adv = 1;				}			} else {				if($debug) echo "no cron.. send followup if exists..<br>";				$adv = 1;			}			if($adv){				$cnt=@mysql_num_rows(mysql_query("select id from $utable where list = '$list' and cseq = '$ocs' and cdel = '$ocd';"));				if($debug) echo "i=$i list=$list cnt=$cnt<br>";				if(key($fuparr)){					$stats[$i][$list] = $cnt;					if($debug) echo "send fup ".key($fuparr)."<br>";					next($fuparr);					$cs = key($fuparr);					list($cd,$cr)=$fuparr[key($fuparr)];					if($debug) echo "next fup = seq $cs del $cd cron $cr<br>";				}				$adv = false;			}		}		if(!$stats[$i][$list]) $stats[$i][$list] = '0';		echo "<tr><td>$thedate</td><td>".$stats[$i][$list]."<br></td></tr>";	}	echo "</table><br>";}?>


You may want to enable the password section near the top of the script or even check the debug output to make sure it's working properly by uncommenting the appropriate line.

Also, you must modify ListMail's "cron.php" file as follows:

Line 116 Old:Code: [Select]
function calcLastRan($string)
Line 116 New:Code: [Select]
function calcLastRan($string,$optdate='')

Line 149 Old:Code: [Select]
$t = strftime("%M,%H,%d,%m,%w,%Y", time());
Line 149 New:Code: [Select]
if($optdate) $t = $optdate; else $t = strftime("%M,%H,%d,%m,%w,%Y", time());

Let me know what you think and especially if you notice any errors (I only tested it with one list containing 3 followups and 1 cron on seq #2).  I can make the tables side by side if you would prefer.

While I am trying to get away from doing these types of custom jobs, even for money, I very much appreciate your contributions to the development of the program and, well, you made a very convincing plea for help!    I hope this custom script eases some of the stress of managing your 'advanced' email campaigns.

Regards

BGSWebDesign · « **Reply #9 on:** February 08, 2006, 04:12:45 pm »

Hi DW,

Great, thank you, I'm about to test this, probably tomorrow. It looks great and I can't wait to see what it shows... I've already lost track of previous scheduling and had to spend a few hours today tracking down what was SUPPOSED to go out, what didn't and setting things up so that they did!

One thing I wonder, is there any way that the dailymail report could be made more useful? For example I only get a list of how many followups went out from each list, nothing about which message (subject) was sent, and this would help greatly too...

I guess what I'm hoping for someday, is a cool 'calendar' view of what went out, what's coming up to go out, etc... that's about the only thing that would help me with planning upcoming dates to send out newsletters and other promotions - since I've got about 20-30 lists firing off messages every other night or so, I can't think of another way to do this?

Regarding the new PHP file, I see this comment:
// get 1 user from this list to determine seq and delay

Why couldn't you loop through for ALL Sequence numbers (up to a max of 99 or something) and get a count on what is coming up for each, is that a lot of coding? I could see that would be more useful - for a few of my lists which do NOT have all the subscribers on the same SEQUENCE/DELAY, and that would be the only way to figure out what is coming up for those users....

Well, that would be one huge report probably.... maybe it's something I can work up on my own, I can see this would be very helpful...

Thanks for the fast reply and code