Sending Out Personalized Emails...

[clamum]

I'm working on an application that sends out a "Question of the Day" sort of thing to a group of users. A web application exists so users can submit questions and days to send them out, and a Windows Service runs every 15 minutes to see if there's any to be sent out; if so it gets the list of users to send to and runs a loop to send them out to each user as they must be personalized for each user to include that user's username.

That's the whole thing in a nutshell, but the problem lies in the fact that the recipient pool could grow to include a thousand or more recipients, and I don't think the server will be able to handle that (using .NET and the SmtpClient to send them out in a loop on each user's record).

So, it was suggested to send out the emails according to each user's local time, at 8 am. If a user is EST time zone (GMT - 5), and the server is also EST, when the timer goes off on the Windows Service at say 8:15 am, then that user's email should be sent out. However, even though the same question was scheduled to be sent to a user in GMT - 0, it should not be sent out until 5 hours from now.

Currently we do not store a user's time zone, and I think that would be the first step here. But beyond that, I'm having some trouble wrapping my head around how the Service should wait until GMT - 0 to send out that second email. I should also mention that a log table is kept and after each successful email is sent, a record is written there so the Service can pick back up after an error or crash.

If anyone has any ideas at all that could point me in the right direction it would be greatly appreciated.

 

[txrandom]

It may be easier to assign each user a certain slot. Depending on how many slots you have, it could be a bad idea. You probably don't want a group of customers getting their Question of the Day at 11:55 pm.

 

[clamum]

That sounds like the right idea, but I think staggering the emails by a user's timezone would be, in effect, assigning a user a certain slot (timezone being the slot). The users can come from all over the world so there can be quite a few "slots".

Pseudo-code, the current logic is something like this:

1) Get questions for today (using DateTime.Now on server)
2) Get list of users for each question who haven't been sent to yet (not in log table)
3) Do some other stuff, then loop through each user record and send email

I am thinking a logic check inserted between 2) and 3) something along this line might work:

2.5) Get list of users, from unsent, where it is >= 8 am in their local time. <--- This requires the timezoneoffset to be stored for each user

 

[imported_Dhaval00]

Why not just add a limited number of users to the "BCC" field on the Messages object and then send the email? If the action is successful, assuming you were caching the users in a generic Queue, remove the users for the Queue, and get the next batch of users. SMTP on IIS can handle the load, but I am assuming you'll be using the BeginSend and EndSend methods to send the emails asynchronously? To drive the logic robustly, you can check for exceptions in EndSend method - if an exception was raised, you know something's wrong with the receiver's email (or the server itself) - at this juncture you could have yet another event raised to try and resend the email, log the failure, or simply requeue the user on the queue. Can be done multiple ways with a plethora of options...

You should have some estimate of how many emails you're dealing with?

 

[clamum]

Originally, I was planning on using the BCC field but the thing is, each email's content must be tailored to each user. Actually what is being done is their username is being inserted into the HTML markup of the email to a postback URL, so when they answer the question and are referred back to the website, their response to the question is stored.

The logic now isn't quite as robust as what you're talking about Dhaval00, but it is in early stages of development and that sounds like something to do with it. It is not using the asynchronous send now, no, but I have looked into that a bit and will be implementing it soon.

As for the estimate of emails, it could be well over a thousand, perhaps 5-10 thousand at most as far as I know at this point. Right now though it is in the early stages and there's probably less than a couple hundred, if that, so we haven't had any issues. But once this thing is deployed and more users have access to it, the user base will grow substantially.

I should also mention that this is using .NET 3.5, so I think the new TimeZoneInfo and DateTimeOffset classes may be helpful in getting this local time logic down, which I think for me is the key.

Thanks for all your help so far guys!

 

[clamum]

Still haven't gotten to the staggered time thing but I did add asynchronous sending which seems to be working.

One problem with that though, is I have the SendCompleted event method handling the completion of each individual email which if successful writes a record to a log table for the email, but I'm not sure how to handle it when ALL emails are sent because another database write has to be done then. I guess what I can do since the db write doesn't necessary have to be right away, is when the service timer goes off next I'll do a query and write that record before doing the normal processing.

 

[KB]

First start storing the users GMT offset. You can store it in a hidden field using javascript on the web form or ask the user to enter it.

Then have the service check every hour who's local time is 8 AM based on the offset.

// javascript to store offset in hidden field.. this will be a positive 300 at Eastern time document.getElementByID("offset").value = (new Date).getTimezoneOffset();


//.Net code to calculate the offset for the person if it is 8 AM there, this doesn't work for daylight savings differences htough

//this is the number of minutes between the local machines time and 8 AM

Int32 minutesFrom8 = DateTime.Now.SubTract(New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 8, 0, 0)).TotalMinutes;

//this is the number of minutes between the local machines time and UTC
Int32 minutesFromUTC = Math.Abs(TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).TotalMinutes);

//calculate what the persons offset should be if it is 8 AM there
Int32 offset = minutesFrom8 + minutesFromUTC;

Now use a SQL query to find who is at 8 AM in the database

SELECT * FROM MailRecipients WHERE UTCOffset = offset

 

[clamum]

Holy shit KB, you rock! That looks like it is just what I am looking for. I use the "new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 8, 0, 0)" right now on the service, along with a couple of other lines, to check that it is 8 am Eastern as that is the time when the service starts processing for the day. So I think I will be able to figure it out.

I'll post back when I get a chance to work on it more but thanks much in advance man!

 

[clamum]

Just an update, I've finally gotten to work on this and using basically your idea it seems to be working tell KB, thank you.

One thing that I have noticed though is that it currently does not take into account tomorrow. If a set of emails is scheduled for tomorrow at 8 AM and if it is 10 PM server time (offset -4 UTC), then it needs to be sending out these new emails for offset +6 UTC.

I had to adjust your example a bit in order to get it to compile: instead of "Int32 offset = minutesFrom8 + minutesFromUTC;" I have "double utcOffset = ((minutesFromLocal8Am + minutesOffsetFromUtc) * -1) / 60;". I had to add the "* -1" so the offset would come out correctly (as each hour passes on the server, the offset gets more negative) and divided by 60 as I am storing decimals for offsets.

However, it seems I need to check not only backwards in time as is currently happening, but forwards too. Still have to do some tweaking but it's coming along. Doesn't help I'm the only one working on this and have been worked like a dang slave the past week or more.