Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CFQUERY to send bulk letter via cfmail in strict, segmented  order

Posted on 2007-10-16
18
Medium Priority
?
678 Views
Last Modified: 2013-12-24
I'm just now accepting enrollment to my new site, http://www.electriciansnet.com and I have acquired a list of about 8000 possible electrical contractors. I have managed to insert (using cffile but I have to re-code the path manually every time- is there an easier way to make the path dynamic?) the filtered list into a table that has 2 columns, email and ID which is just an autonum column.
What I would like to do is to setup a cfschedule to run for an hour or so in the wee hours of the night but the query needs to take the following parameters into consideration.

1. It needs to check against my current members (dbo.members.Contact_Email ) so that they are not harassed by this promotion.

2. If email not exist in dbo.members, send say 267(aprox 8k/30 days) email on day 1 of 30

3.  If email not exist in dbo.members, send the next  267  email on day 2 of 30 and so on...

4. Start process over again in 30 days

5. Possibly set it up so that I can change the body of the message and start another overlapping letter/ process  to the same list.
0
Comment
Question by:Electriciansnet
  • 10
  • 8
18 Comments
 
LVL 25

Expert Comment

by:dgrafx
ID: 20191716
You also need a maildate column [datatype datetime] in your new leads table
Why dont you delete leads [after inserting to new table] that are already members?
or better yet - don't insert them
anyway ...

<cfquery datasource="#DSN#" name="gtEmail">
Declare @MailNum int
Set @MailNum = 267

SELECT TOP @MailNum EMAIL --this is the name of your email address variable from your new leads table
FROM EMAILLEADS --this is the name of your new leads table
WHERE EMAIL NOT IN (SELECT Contact_Email FROM members)
AND DATEDIFF(Day,MAILDATE,getDate()) >= 30
ORDER BY MAILDATE

UPDATE EMAILLEADS
Set MAILDATE = getDate()
WHERE EMAIL NOT IN (SELECT Contact_Email FROM members)
AND MAILID IN (SELECT TOP @MailNum MAILID FROM EMAILLEADS
Where DATEDIFF(Day,MAILDATE,getDate()) >= 30 ORDER BY MAILDATE)
</cfquery>

<cfmail from="...."
to="#Email#"
query="getMail">
#yourmailbody#
</cfmail>

I don't really know what you mean by the first part of your question - make the path dynamic
if you mean you upload a new file and want to read this file
then the file that was uploaded can be called by #cffile.serverfile#
0
 

Author Comment

by:Electriciansnet
ID: 20198393
This looks great dgrafx. Just give me a little time to try it because I'm trying to figure out how to us the mail server (Merak) to do the work but the lang is completely different. I think it uses php. Maybe I could somehow use CF (cffile or cfhttp) to do your query and save the results to a comma delimited text file in the mail dir. I have already figured out how to forward my promotion to list@electriciansnet.com which immediately starts digesting the message to the thousands of emails in the list but it requires much daily maintenance to send only one copy to a user etc. Thanks, Andy
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 20202205
not sure i understand
cf can send mail via any mailserver - you don't need to mess with any code like php.
i believe you may be misunderstanding something - if you elaborate on the area you are confused on.
all you need do is specify the mailserver to use (and username & password if required by mailserver)
i believe that if mailserver does not require a uname - password (usually worded as secure connection) then supplying one will make the connection fail, so it's important to know if it requires a secure connection or not.

<cfmail from="...."
to="#Email#"
query="getMail"
server="#mailservername#"
username="#username#"
password="#password#">
#yourmailbody#
</cfmail>
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Electriciansnet
ID: 20205826
Right- cfmail is the foundation of my site, http://www.electriciansnet.com and I love it's simplicity but my web research indicated that cfmail was not a good resource for handling large tasks such as email lists in the thousands because of the resources required. Plus I'm already dealing with a slow loading home page because of a 5 line (1 input) flash form so I need all the server I can get. But, in terms of this question, I guess you probably answered it to the tee. I guess I could schedule it to run in the middle of the night though. I know after 6 mos of teaching myself CF I sure don't want to mess with another language. To me there is no other language. Anyway, I guess I'll start applying your creation now and I'll post back and accept as answer asap. Thanks, Andy
0
 

Author Comment

by:Electriciansnet
ID: 20205873
<cfquery datasource="#enet#" name="gtEmail">
Declare @MailNum int
Set @MailNum = 267

SELECT TOP @MailNum EMAIL --this is the name of your email address variable from your new leads table
FROM dbo.mailList_1 --this is the name of your new leads table
WHERE EMAIL NOT IN (SELECT Contact_Email FROM members)
AND DATEDIFF(Day,MAILDATE,getDate()) >= 30
ORDER BY MAILDATE

UPDATE dbo.mailList_1
Set MAILDATE = getDate()
WHERE EMAIL NOT IN (SELECT Contact_Email FROM members)
AND MAILID IN (SELECT TOP @MailNum MAILID FROM dbo.mailList_1
Where DATEDIFF(Day,MAILDATE,getDate()) >= 30 ORDER BY MAILDATE)
</cfquery>
________________________________________________________________

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@MailNum'.
 
The error occurred in C:\Inetpub\wwwroot\electriciansnet.com\mailer\ance1.cfm: line 1

1 : <cfquery datasource="#enet#" name="gtEmail">
2 : Declare @MailNum int
3 : Set @MailNum = 267

SQL          Declare @MailNum int Set @MailNum = 267 SELECT TOP @MailNum EMAIL --this is the name of your email address variable from your new leads table FROM dbo.mailList_1 --this is the name of your new leads table WHERE EMAIL NOT IN (SELECT Contact_Email FROM members) AND DATEDIFF(Day,MAILDATE,getDate()) >= 30 ORDER BY MAILDATE UPDATE dbo.mailList_1 Set MAILDATE = getDate() WHERE EMAIL NOT IN (SELECT Contact_Email FROM members) AND MAILID IN (SELECT TOP @MailNum MAILID FROM dbo.mailList_1 Where DATEDIFF(Day,MAILDATE,getDate()) >= 30 ORDER BY MAILDATE)
_____________________________________________________________________________
Do I need to also create a mailNum?

I forget what you call this but the following can be viewed in a text editor and is derived from choosing "Create To Clipboard" in SQL 2005:

USE [enetdb1SQL]
GO
/****** Object:  Table [dbo].[mailList_1]    Script Date: 11/02/2007 23:48:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mailList_1](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [email] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [maildate] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [enetdb1SQL]
GO
ALTER TABLE [dbo].[mailList_1]  WITH CHECK ADD  CONSTRAINT [EmailValidator] CHECK  ((charindex(' ',ltrim(rtrim([Email])))=(0) AND left(ltrim([Email]),(1))<>'@' AND right(rtrim([Email]),(1))<>'.' AND (charindex('.',[Email],charindex('@',[Email]))-charindex('@',[Email]))>(1) AND (len(ltrim(rtrim([Email])))-len(replace(ltrim(rtrim([Email])),'@','')))=(1) AND charindex('.',reverse(ltrim(rtrim([Email]))))>=(3) AND (charindex('.@',[Email])=(0) AND charindex('..',[Email])=(0))))
0
 

Author Comment

by:Electriciansnet
ID: 20205910
I have a brand new data source setup that points to an SQL instance on the mail server so I can just create a table to whatever spwcs we need there and insert my list there but wont every line have the same mailDate?
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 20206483
Let's just hard code the value instead
When you want to change this number - remember to change it in the 2 places
<cfquery datasource="#enet#" name="gtEmail">
SELECT TOP 267 EMAIL
FROM dbo.mailList_1
WHERE EMAIL NOT IN (SELECT Contact_Email FROM members)
AND DATEDIFF(Day,MAILDATE,getDate()) >= 30
ORDER BY MAILDATE

UPDATE dbo.mailList_1
Set MAILDATE = getDate()
WHERE EMAIL NOT IN (SELECT Contact_Email FROM members)
AND MAILID IN (SELECT TOP 267 MAILID FROM dbo.mailList_1
Where DATEDIFF(Day,MAILDATE,getDate()) >= 30 ORDER BY MAILDATE)
</cfquery>

Let me address some of your other points
Your research on cfmail is incorrect!
There are a lot of people out there who are down on ColdFusion.
The reasons vary but I believe it's because they still view it as a simple limited language.
And also considering that it is fairly easy to learn (it's easy because it makes sense) makes some think it's simple.
You should research the history of CF - it started as not much of a language but just some add-in code to do some database stuff.
But NOW - as of this writing - ColdFusion is the most powerful language on the web!
You absolutely do NOT need any other language if your site has a ColdFusion server available.
Sure, SQL is another language and css etc - but you know what I mean.
And if you do want to call some Java or .Net functionality you can just do it from ColdFusion - how about that!
Once upon a time there was a limit of 60,000 or so emails per one cfmail call - I don't know if this changed.
But you certainly are not sending over this amount.
What one does is something similar to what I've posted here.
Besides - some isp's start frowning upon emails coming in in huge numbers from one mailserver at the same time so you should breakup your emailing somewhat - if you are doing large numbers anyway.

Your slow loading home page has nothing what so ever to do with sending email
on a side note - if you know your home page is slow - remove it and replace it with an html form that loads fast - speed is the name of the game on the web.

To answer one question - no you don't need to create a column named MailNum.
And also - yes - to start all rows will have the same maildate.
On day one of running this - 267 emails will be sent and these will also have their date updated so they won't be emailed until next month.
On day 2 - the next 267 will have the same thing done
And so on until they have all been emailed.
Thats assuming that you have less than 8000 leads in your database - thats the 267 * 30
You want to do some quick math and make sure all leads will be accounted for
if you have more you can send out more at a time or send out twice a day for example.

An important thing I forgot to mention is that in the beginning - you need to set all rows maildate to at least a month ago or they won't be emailed until next month.
and make the mailDate columns default value an old date so they can get emailed right away - like '01/01/2007'
this default value is what new inserts will be set at.

good luck ...

0
 

Author Comment

by:Electriciansnet
ID: 20209355
I love ColdFusion. I'm an electrical contractor and I love switches so its seems natural for me to have an interest in computers since every command line is nothing more than a switch. I realize how apparent it is that I am a novice but whatever I know about CF is from the Web over the last 7 months starting with Webmonkey tutorials.  When I decided to build this site I was lucky to stumble upon ColdFusion. I chose it because I always considered the term to be "cool" but had I found a different language, I might have given up. Theres a lot about the scripting that baffles me still but I love all the little hidden (to me until...) switches.
Anyway I paid a contractor to build contractor.cfm (you can see it by following the enroll link) and he chose to use flash forms. It took him 6 months to build but thats a different story. I liked the flash forms so I decided to apply them everywhere. Now even my ISP has commented about how slow the home page loads. If I comment out the 6 line form, boom! The page snaps to attention.
 Speaking of ISP, after purchasing the web crawler / extractor software I upraded my account to static with unlimited outgoing mail. Also, I was very clear with them that I was not selling gadgets but, trying to enroll people on my free-for-now site. They seemed ok with it. I checked because I, like everyone else, hate spam! The 1st campaign I sent out was a mess because I didn't know how to control the volume and it locked my machine up for hours. However, I did get 4 members out of it but that was 2 weeks ago and they are still my only 4 members. I try to contact them but they never respond. Sidebar: I pay $250/ mo for the same service and it is hands down the best source of advertising we have. I see about a 300% avg ROI monthly. Thats what spawned the idea.
___________________________________
"On day one of running this - 267 emails will be sent and these will also have their date updated so they won't be emailed until next month." I would like the process to start over in a month plus that gives me enough time to change content.

I may need some help with the mail date thing.
"An important thing I forgot to mention is that in the beginning - you need to set all rows maildate to at least a month ago or they won't be emailed until next month.
and make the mailDate columns default value an old date so they can get emailed right away - like '01/01/2007'
this default value is what new inserts will be set at.

good luck ..."
The query ran without errors.
What should I do: insert into mailList_1 (mailDate) values (01/01/2007)? If its that simple- won't I have to include the minute and second? Of course I need to point a scheduled task to this page to run daily, right?
0
 
LVL 25

Accepted Solution

by:
dgrafx earned 2000 total points
ID: 20210227
create a scheduled task to run daily (unless you want to run twice a day for the reasons stated above)
it will keep running <forever> starting over every month.
you need a way of testing without sending emails
just comment out the entire cfmail tag and code a query instead so you can see that it works

good luck
0
 

Author Comment

by:Electriciansnet
ID: 20211845
OK, I got it to run fine except I just need to check my smtp log to see whats happening. One weird thing though: I emptied out the table and I change my query to look like this:

<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
    <cfquery name="importcsv" datasource="#enet#">
         INSERT INTO dbo.mailList_1 (email, maildate)
         VALUES
                  (
                   '#listgetAt('#index#',1)#'
                  , 01/01/2007)
   </cfquery>
</cfloop>

I also have a constrtaint in place in SQL 2005 that filteres out more junk email but I queried the table after one massive insert and found the maildate column to read "01/01/1900". I guess it does not matter since we aren't using the year but I found it to be strange. I'm sure you know why though.

I'm about to accept this as answer and thank you! But, how will I run a new mailer on top of the 1st one and we do have it set to loop over, right?
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 20211886
<cfquery datasource="#enet#">
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
IF NOT EXISTS (Select 1 From dbo.mailList_1 WITH (NOLOCK) Where Email = '#trim(listgetAt(index,1))#')
      BEGIN
            INSERT INTO dbo.mailList_1 (email, maildate)
            Select '#trim(listgetAt(index,1))#', '01/01/2007'
      END
</cfloop>
</cfquery>

don't understand your last question ...
0
 

Author Comment

by:Electriciansnet
ID: 20213854
I guess I'm just making it too difficult.To answer my own question, all I have to do is past the same query on top of a new mail item and set another cfschedule to point to it maybe a couple of hours apart.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 20214850
???
OK - you do need 2 different and entirely separate scheduled tasks
One that reads your list and inserts it
and the other to query and email appropriate leads

DO NOT try and combine these 2.
They are not similar in any way.
The queries are different & the concept is different.
0
 

Author Comment

by:Electriciansnet
ID: 20216620
Well, I inserted the list into the table manually. Actually I never even thought about using a scheduled task- hmm...  I'll have to think about that one because you just gave me the idea that I could get my crawler to write to a list kept in the mail dir and set a scheduled task to read/insert the list from that static path. The only problem is that there is actually 3 peaces of software involved to get good addresses. I end up with about 5% of what the crawler grabbed. It likes to get addresses like this: "...@sumdumhost.com"

Anyway, all I did was point a scheduled task to your query which is nested above the cfmail tag and content.
Thanks, Andy
0
 

Author Comment

by:Electriciansnet
ID: 20217014
Please show me your logic:

<cfquery datasource="#enet#">       *** Why did you flip the loop and the query?
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
IF NOT EXISTS (Select 1 From dbo.mailList_1 WITH (NOLOCK) Where Email = '#trim(listgetAt(index,1))#')
      BEGIN                                                                   *** Select 1? with(nolock)?
            INSERT INTO dbo.mailList_1 (email, maildate)
            Select '#trim(listgetAt(index,1))#', '01/01/2007'   *** Begin, end?
      END
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 20217232
OK -
1) are you aware that web development (and software dev in general) is a lot of hard work?
2) are you aware that there are a lot more "really really bad" programmers than there are good ones?
3) and that a lot of programmers got into this thing of ours because it was thought to be practically a "get rich quick scheme".
4) a bad programmer is unwilling to learn new stuff and work as hard as the guys who built the pyramids - it's supposed to be easy money!
5) So I'm glad you ask these questions and want to know - it shows potential.

Every time CF sees "<cfquery ..." It makes a connection to the database - this is a relatively slow process - it is THE bottleneck we all avoid.
And Every time CF sees "</cfquery>" it closes this connection!
So you are now saying "Why would I want to open and close the connection an untold number of times when I should be able to open it once - grab the data or manipulate the data - then close it!"
If you said that - you'd be correct!

Select 1 means exactly that - Select 1
From Table
With (NOLOCK) means -
the db whats to give you accurate data - so with every call to the db it checks to see if anyone is currently using the data (making a call also) - if so - it waits for [first in - first out] to complete - then YOU take your turn.
NOLOCK says - don't even check - I don't care - just give me the data!

BEGIN is the beginning of the SQL IF the above criteria is met and END is the end
You can do something like
IF @X > 0
BEGIN
UPDATE Table
Set imaginarycolumn = @X
WHERE whatever
END
SELECT imaginarycolumn
From TABLE
WHERE whatever

so you see that the BEGIN & END are just the start and end points for the logic
you can still continue but the in-between code does not run unless the logic is matched.
it's like in CF where you may do
<cfif this eq that>
then do this
</cfif>
but you can still continue here and the above code doesn't run unless this eq that ...

Come check out some of our products some time ...
FusionGadgets.com
0
 

Author Comment

by:Electriciansnet
ID: 20229749
Thats awesome! I don't know if you teach but you should. You have a very unique-but-clear way of conveying the subject. So basically- you coded it like the pros do using more SQL and avoiding a bunch of extra cftags that were developed as a wrapper to do the same thing for the not-so-pro. Anyway thanks again and I thought you might like to know that I picked up 2 more members today which are the 1st in 2 weeks and for some reason my hit counter showed a 30 hit increase today alone vs. 2 or 1 or 0 most days.
Thanks again and I will check out FusionGadgets.com right now. Best Regards, Andy
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 20231065
Glad I could help Andy and good luck ...
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question