Home Page

Track who reads your news letters!

After sending out 9,000 news letter one day I wondered, geeze, how many people actually read it? Well, after pondering the thought for 30 seconds I started writing this script that could be used to track a newsletter on just about any system where you have access to the database. Essentially what this does is track how many people request the logo in your news letter (HTML news letters only) and along with that I also attach a record number so I can tell who requested it... neat right, now we know how many, who and when!

Required:

1) ASP JPeg by Persits http://www.aspjpeg.com/ (you use some other form of image processor and change code)
2) Access to update and change your news letter email address database. (could be any type of DB really)
3) HTML News Letters only.

NewsTracker database... build it any way you too but for my example below I have used MS SQL and built a table as follows.

Call the database news Tracker (dah!)

Copy paste this into query analyzer to build the table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[newstracker]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[newstracker] GO CREATE TABLE [dbo].[newstracker] ( [id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [tracker_date] [datetime] NOT NULL , [tracker_count] [int] NOT NULL ) ON [PRIMARY] GO

Should look like this:
id = int (auto number)
tracker_date = datetime (default date using getdate())
tracker_count = int (default 0)

The basic way:

Let's begin, the only thing that needs to be present in your news letter is an image tag like this:

The More Advanced Option:

The above will only count how many times the news letter is read... lets go a little further!

By knowing that the image above is being requested with this unique URL we can update the user's record that he has read the news letter and when he read it!

USER_ID_HERE = ID # of the user you are sending the news letter to, for example, when my news letter script is sending out news letters it simply does a record loop sending out news letters one at time until there are no more records left to process so during the loop I simply have the script write in the record ID of the user it currently sending to.

Tracking by user will require an additional field in your news letter database... I called the field last_read and it is datetime data type.

Save this file as newsletter.asp in the root of your web.

ConnString = "Provider=SQLOLEDB;" & _
"Password=your_password;" & _
"User ID=your_username;" & _
"Initial Catalog=your_database_name;" & _
"Data Source=your_db_server_ip;"

Set conn = server.CreateObject("adodb.Connection")
conn.open(connstring)

userid = trim(request("userid"))

strSql = "SELECT tracker_count FROM  newstracker WHERE (CONVERT(datetime, CONVERT(char(10), tracker_date, 101)) = CONVERT(datetime, CONVERT(char(10), GETDATE(), 101)))"
Set rsNewsTracker = Conn.Execute(strSql)

if rsNewsTracker.BOF and rsNewsTracker.EOF then
	strSql = "INSERT INTO newstracker (tracker_count) VALUES (1)"
else
	TrackerCount = trim(rsNewsTracker("tracker_count") + 1)
	strSql = "UPDATE newstracker SET tracker_count = "&TrackerCount&" WHERE (CONVERT(datetime, CONVERT(char(10), tracker_date, 101)) = CONVERT(datetime, CONVERT(char(10), GETDATE(), 101)))"       	
	Conn.Execute(strSql)
end if

if isnumeric(userid) = true then
	strSql = "UPDATE forum_maillist SET last_read = getdate() where id =" & userid      	
	Conn.Execute(strSql)
end if

Response.Expires = 0
Set Jpeg = Server.CreateObject("Persits.Jpeg")
ImagePath = "images/newsletter_logo.jpg"  
ImagePath = server.mapPath(ImagePath)
Jpeg.Open(ImagePath)
Jpeg.SendBinary
%>

The monitering page:

<% ConnString = "Provider=SQLOLEDB;" & _ "Password=your_password;" & _ "User ID=your_username;" & _ "Initial Catalog=your_database_name;" & _ "Data Source=your_db_server_ip;" Set conn = server.CreateObject("adodb.Connection") conn.open(connstring) SqlStr = "SELECT COUNT(*) AS [Count] FROM FORUM_MAILLIST WHERE (ACTIVE = 1)" Set rs = conn.Execute(SqlStr) TotalCount = rs("count") SqlStr = "SELECT COUNT(*) AS [Count], CONVERT(datetime, CONVERT(char(10), LAST_SENT, 101)) AS Date FROM FORUM_MAILLIST GROUP BY CONVERT(datetime, CONVERT(char(10), LAST_SENT, 101)) HAVING (NOT (CONVERT(datetime, CONVERT(char(10), LAST_SENT, 101)) IS NULL)) OR (CONVERT(datetime, CONVERT(char(10), LAST_SENT, 101)) = CONVERT(DATETIME, '1900-01-01 00:00:00', 102)) ORDER BY CONVERT(datetime, CONVERT(char(10), LAST_SENT, 101)) DESC" Set rs = conn.Execute(SqlStr) Do While Not rs.eof response.write("Last news letter sent on: " & rs("date") & " was sent to "&rs("count")&" out off "&TotalCount&" users. ") rs.movenext loop response.write("<hr>") response.write(" Track by news letter user counts (anyone that has a last_read date thus this is only counting each user one time that has opened the email) : <hr>") SqlStr = "SELECT COUNT(*) AS [Count], CONVERT(datetime, CONVERT(char(10), LAST_READ, 101)) AS Date FROM FORUM_MAILLIST WHERE (LAST_READ < GETDATE()) GROUP BY CONVERT(datetime, CONVERT(char(10), LAST_READ, 101)) ORDER BY CONVERT(datetime, CONVERT(char(10), LAST_READ, 101)) DESC" Set rs = conn.Execute(SqlStr) Do While Not rs.eof total = total + rs("count") response.write(rs("count") & " users read a news letter on " & rs("date") & " ") rs.movenext loop response.write(" Total: " & total) response.write(" Track by news letter sent/recieved date (capture forwarding and multipule open counts, counts each hit): <hr>") SqlStr = "SELECT *, CONVERT(datetime, CONVERT(char(10), tracker_date, 101)) AS Date FROM newstracker" Set rs = conn.Execute(SqlStr) Do While Not rs.eof response.write("A news letter has been read " & rs("tracker_count") & " times. ") rs.movenext loop conn.Close Set conn = Nothing Set rs = Nothing %>

That's really about it... pretty simple and it works great!