 |
Newsletter
Report
<%
set subRecord = Server.CreateObject("ADODB.Recordset")
subRecord.ActiveConnection = mlConn
subRecord.Source = "SELECT COUNT(*) AS subCount FROM ML_Subscribers WHERE Format = 'Text' AND ListID = " & lstID
subRecord.CursorType = 1
subRecord.CursorLocation = 3
subRecord.LockType = 3
subRecord.Open()
subCountText = subRecord("subCount")
if isnull(subCountText) then subCountText = 0
set subRecord = nothing
set subRecord = Server.CreateObject("ADODB.Recordset")
subRecord.ActiveConnection = mlConn
subRecord.Source = "SELECT COUNT(*) AS subCount FROM ML_Subscribers WHERE Format = 'HTML' AND ListID = " & lstID
subRecord.CursorType = 1
subRecord.CursorLocation = 3
subRecord.LockType = 3
subRecord.Open()
subCountHTML= subRecord("subCount")
if isnull(subCountHTML) then subCountHTML = 0
set subRecord = nothing
subCountTotal = subCountHTML + subCountText
if subCountTotal > 0 then
subCountPercentText = "(" & formatpercent(subCountText / subCountTotal, 1) & "% total)"
subCountPercentHTML = "(" & formatpercent(subCountHTML / subCountTotal, 1) & "% total)"
end if
%>
|
Newsletter
Basic Stats
|
| More
Info: |
Newsletter
Details | ">List
Details | ">List
Report |
| Subject: |
<%=msgRecord("Subject")%> |
| Body: |
Text
Version | HTML
Version |
| Sent
To: |
<%=msgRecord("BroadcastSubscribers")%> subscribers |
| Sent
On: |
<% if isnull(msgRecord("msgBroadcastedDate")) then %>
Pending
<% else %>
<%=getDateFromNum(msgRecord("msgBroadcastedDate"))%>
<% end if %>
|
| Sent: |
<% if isnull(msgRecord("msgBroadcastedDate")) then %>
Pending
<% else %>
<%=datediff("d", getDateFromNum(msgRecord("msgBroadcasteddate")), date)%> days ago
<% end if %>
|
| Subscribers: |
<%=subCountText%> Text Subscribers <%=subCountPercentText%>
<%=subCountHTML%> HTML Subscribers <%=subCountPercentHTML%> |
| Notes: |
* You
have a higher
<% if subCountText > subCountHTML then %>
Text
<% else %>
HTML
<% end if %>
subscriber count |
<%
set redRecord = Server.CreateObject("ADODB.Recordset")
redRecord.ActiveConnection = mlConn
redRecord.Source = "SELECT SUM(ML_Reads.readNumTimes) AS redCount FROM ML_Reads INNER JOIN ML_Subscribers ON ML_Reads.readSubscriberID = ML_Subscribers.SubscriberID WHERE ML_Subscribers.Format = 'Text' AND ML_Reads.readMessageID = " & msgID
redRecord.CursorType = 1
redRecord.CursorLocation = 3
redRecord.LockType = 3
redRecord.Open()
redCountText = redRecord("redCount")
if isnull(redCountText) then redCountText = 0
set redRecord = nothing
set redRecord = Server.CreateObject("ADODB.Recordset")
redRecord.ActiveConnection = mlConn
redRecord.Source = "SELECT SUM(ML_Reads.readNumTimes) AS redCount FROM ML_Reads INNER JOIN ML_Subscribers ON ML_Reads.readSubscriberID = ML_Subscribers.SubscriberID WHERE ML_Subscribers.Format = 'HTML' AND ML_Reads.readMessageID = " & msgID
redRecord.CursorType = 1
redRecord.CursorLocation = 3
redRecord.LockType = 3
redRecord.Open()
redCountHTML= redRecord("redCount")
if isnull(redCountHTML) then redCountHTML = 0
set redRecord = nothing
redCountTotal = redCountHTML + redCountText
if redCountTotal > 0 then
redCountPercentText = "(" & formatpercent(redCountText / redCountTotal, 1) & ")"
redCountPercentHTML = "(" & formatpercent(redCountHTML / redCountTotal, 1) & ")"
end if
set redRecord = Server.CreateObject("ADODB.Recordset")
redRecord.ActiveConnection = mlConn
redRecord.Source = "SELECT COUNT(*) AS redCount FROM ML_Reads INNER JOIN ML_Subscribers ON ML_Reads.readSubscriberID = ML_Subscribers.SubscriberID WHERE ML_Subscribers.Format = 'Text' AND ML_Reads.readMessageID = " & msgID
redRecord.CursorType = 1
redRecord.CursorLocation = 3
redRecord.LockType = 3
redRecord.Open()
redUCountText = redRecord("redCount")
if isnull(redUCountText) then redUCountText = 0
set redRecord = nothing
set redRecord = Server.CreateObject("ADODB.Recordset")
redRecord.ActiveConnection = mlConn
redRecord.Source = "SELECT COUNT(*) AS redCount FROM ML_Reads INNER JOIN ML_Subscribers ON ML_Reads.readSubscriberID = ML_Subscribers.SubscriberID WHERE ML_Subscribers.Format = 'HTML' AND ML_Reads.readMessageID = " & msgID
redRecord.CursorType = 1
redRecord.CursorLocation = 3
redRecord.LockType = 3
redRecord.Open()
redUCountHTML= redRecord("redCount")
if isnull(redUCountHTML) then redUCountHTML = 0
set redRecord = nothing
redUCountTotal = redUCountHTML + redUCountText
if redUCountTotal > 0 then
redUCountPercentText = "(" & formatpercent(redUCountText / redUCountTotal, 1) & ")"
redUCountPercentHTML = "(" & formatpercent(redUCountHTML / redUCountTotal, 1) & ")"
end if
set redRecord = Server.CreateObject("ADODB.Recordset")
redRecord.ActiveConnection = mlConn
redRecord.Source = "SELECT ML_Broadcast.Subject AS Subject, ML_Broadcast.MessageID AS MessageID, ML_Reads.readLastDate AS readLastDate, ML_Subscribers.Email AS Email, ML_Subscribers.SubscriberID AS SubscriberID FROM (ML_Reads INNER JOIN ML_Broadcast ON ML_Reads.readMessageID = ML_Broadcast.MessageID) INNER JOIN ML_Subscribers ON ML_Reads.readSubscriberID = ML_Subscribers.SubscriberID WHERE ML_Reads.readMessageID = " & msgID & " ORDER BY ML_Reads.readLastDate DESC"
redRecord.CursorType = 1
redRecord.CursorLocation = 3
redRecord.LockType = 3
redRecord.Open()
%>
|
Newsletter
Read Tracking Stats
|
<%
if redRecord.eof then
%>
|
Last
Read:
|
None |
<% else %>
|
Last
Read:
|
"><%=redRecord("Subject")%>
on <%=getDateFromNum(redRecord("readLastDate"))%> by "><%=redRecord("Email")%> |
|
|
Total
Reads:
|
<%=redCountText%> Total reads from Text Subscribers <%=redCountPercentText%>
<%=redCountHTML%> Total reads from HTML Subscribers <%=redCountPercentHTML%>
--------------------------------------------
<%=redCountTotal%> Total reads from Subscribers (100%) |
|
|
Unique
Reads:
|
<%=redUCountText%> Unique reads from Text Subscribers <%=redUCountPercentText%>
<%=redUCountHTML%> Unique reads from HTML Subscribers <%=redUCountPercentHTML%>
--------------------------------------------
<%=redUCountTotal%> Unique reads from Subscribers (100%) |
|
|
Notes:
|
*
This Message received
more total reads from
<% if redCountText > redCountHTML then %>
Text
<% else %>
HTML
<% end if %>
Subscribers
* This Message received
more unique reads from
<% if redUCountText > redUCountHTML then %>
Text
<% else %>
HTML
<% end if %>
Subscribers |
<%
end if
set redRecord = nothing
%>
<%
set urlRecord = Server.CreateObject("ADODB.Recordset")
urlRecord.ActiveConnection = mlConn
urlRecord.Source = "SELECT COUNT(*) AS urlCount FROM ML_URLs WHERE MessageID = " & msgID
urlRecord.CursorType = 1
urlRecord.CursorLocation = 3
urlRecord.LockType = 3
urlRecord.Open()
urlCount = urlRecord("urlCount")
if isnull(urlCount) then urlCount = 0
set urlRecord = nothing
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT SUM(ML_Clicks.clkNumTimes) AS clkCount FROM (ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID) INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID WHERE ML_Subscribers.Format = 'Text' AND ML_URLs.MessageID = " & msgID
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
clkCountText = clkRecord("clkCount")
if isnull(clkCountText) then clkCountText = 0
set clkRecord = nothing
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT SUM(ML_Clicks.clkNumTimes) AS clkCount FROM (ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID) INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID WHERE ML_Subscribers.Format = 'HTML' AND ML_URLs.MessageID = " & msgID
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
clkCountHTML= clkRecord("clkCount")
if isnull(clkCountHTML) then clkCountHTML = 0
set clkRecord = nothing
clkCountTotal = clkCountHTML + clkCountText
if clkCountTotal > 0 then
clkCountPercentText = "(" & formatpercent(clkCountText / clkCountTotal, 1) & ")"
clkCountPercentHTML = "(" & formatpercent(clkCountHTML / clkCountTotal, 1) & ")"
end if
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT COUNT(*) AS clkCount FROM (ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID) INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID WHERE ML_Subscribers.Format = 'Text' AND ML_URLs.MessageID = " & msgID
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
clkUCountText = clkRecord("clkCount")
if isnull(clkUCountText) then clkUCountText = 0
set clkRecord = nothing
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT COUNT(*) AS clkCount FROM (ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID) INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID WHERE ML_Subscribers.Format = 'HTML' AND ML_URLs.MessageID = " & msgID
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
clkUCountHTML= clkRecord("clkCount")
if isnull(clkUCountHTML) then clkUCountHTML = 0
set clkRecord = nothing
clkUCountTotal = clkUCountHTML + clkUCountText
if clkUCountTotal > 0 then
clkUCountPercentText = "(" & formatpercent(clkUCountText / clkUCountTotal, 1) & ")"
clkUCountPercentHTML = "(" & formatpercent(clkUCountHTML / clkUCountTotal, 1) & ")"
end if
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT ML_URLs.urlRedirect AS urlRedirect, ML_Clicks.clkLastDate AS clkLastDate, ML_Subscribers.Email, ML_Subscribers.SubscriberID AS SubscriberID FROM (ML_Clicks INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID) INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID WHERE ML_URLs.MessageID = " & msgID & " ORDER BY ML_Clicks.clkLastDate DESC"
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
%>
|
URL
Tracking Stats
|
<% if clkRecord.eof then %>
|
Total
URLs Tracked:
|
<%=urlCount%> |
|
Last
Click:
|
None |
<% else %>
|
Total
URLs Tracked:
|
<%=urlCount%> |
|
Last
Click:
|
<% if clkRecord.eof then %>
None
<% else %>
" target="_blank"><%=clkRecord("urlRedirect")%> on <%=getDateFromNum(clkRecord("clkLastDate"))%> by "><%=clkRecord("Email")%>
<% end if %>
|
|
|
Total
Clicks:
|
<%=clkCountText%> Total clicks from Text Subscribers <%=clkCountPercentText%>
<%=clkCountHTML%> Total clicks from HTML Subscribers <%=clkCountPercentHTML%>
--------------------------------------------
<%=clkCountTotal%> Total clicks from Subscribers (100%) |
|
|
Unique
Clicks:
|
<%=clkUCountText%> Unique clicks from Text Subscribers <%=clkUCountPercentText%>
<%=clkUCountHTML%> Unique clicks from HTML Subscribers <%=clkUCountPercentHTML%>
--------------------------------------------
<%=clkUCountTotal%> Unique clicks from Subscribers (100%) |
|
|
Notes:
|
*
This Message received
more total clicks from
<% if clkCountText > clkCountHTML then %>
Text
<% else %>
HTML
<% end if %>
Subscribers
* This Message received
more unique clicks from
<% if clkUCountText > clkUCountHTML then %>
Text
<% else %>
HTML
<% end if %>
Subscribers |
<%
end if
set clkRecord = nothing
%>
<%
set urlRecord = Server.CreateObject("ADODB.Recordset")
urlRecord.ActiveConnection = mlConn
urlRecord.Source = "SELECT * FROM ML_URLs WHERE urlClicks > 0 AND MessageID = " & msgID
urlRecord.CursorType = 1
urlRecord.CursorLocation = 3
urlRecord.LockType = 3
urlRecord.Open()
if not urlRecord.eof then
minURLClicks = urlRecord("urlClicks")
minURLRedirect = urlRecord("urlRedirect")
maxURLClicks = urlRecord("urlClicks")
maxURLRedirect = urlRecord("urlRedirect")
%>
|
Detailed
URL Tracking Stats
|
<%
do until urlRecord.eof
urlID = urlRecord("urlID")
if urlRecord("urlClicks") < minURLClicks then
minURLClicks = urlRecord("urlClicks")
minURLRedirect = urlRecord("urlRedirect")
elseif urlRecord("urlClicks") > maxURLClicks then
maxURLClicks = urlRecord("urlClicks")
maxURLRedirect = urlRecord("urlRedirect")
end if
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT SUM(ML_Clicks.clkNumTimes) AS clkCount FROM (ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID) INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID WHERE ML_Subscribers.Format = 'Text' AND ML_URLs.urlID = " & urlID
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
clkCountText = clkRecord("clkCount")
if isnull(clkCountText) then clkCountText = 0
set clkRecord = nothing
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT SUM(ML_Clicks.clkNumTimes) AS clkCount FROM (ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID) INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID WHERE ML_Subscribers.Format = 'HTML' AND ML_URLs.urlID = " & urlID
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
clkCountHTML = clkRecord("clkCount")
if isnull(clkCountHTML) then clkCountHTML = 0
set clkRecord = nothing
clkCountTotal = clkCountHTML + clkCountText
if clkCountTotal > 0 then
clkCountPercentText = "(" & formatpercent(clkCountText / clkCountTotal, 1) & ")"
clkCountPercentHTML = "(" & formatpercent(clkCountHTML / clkCountTotal, 1) & ")"
end if
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT COUNT(*) AS clkCount FROM (ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID) INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID WHERE ML_Subscribers.Format = 'Text' AND ML_URLs.urlID = " & urlID
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
clkUCountText = clkRecord("clkCount")
if isnull(clkUCountText) then clkUCountText = 0
set clkRecord = nothing
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT COUNT(*) AS clkCount FROM (ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID) INNER JOIN ML_URLs ON ML_Clicks.clkURLID = ML_URLs.urlID WHERE ML_Subscribers.Format = 'HTML' AND ML_URLs.urlID = " & urlID
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
clkUCountHTML = clkRecord("clkCount")
if isnull(clkUCountHTML) then clkUCountHTML = 0
set clkRecord = nothing
clkUCountTotal = clkUCountHTML + clkUCountText
if clkUCountTotal > 0 then
clkUCountPercentText = "(" & formatpercent(clkUCountText / clkUCountTotal, 1) & ")"
clkUCountPercentHTML = "(" & formatpercent(clkUCountHTML / clkUCountTotal, 1) & ")"
end if
set clkRecord = Server.CreateObject("ADODB.Recordset")
clkRecord.ActiveConnection = mlConn
clkRecord.Source = "SELECT ML_Clicks.clkLastDate AS clkLastDate, ML_Subscribers.Email AS Email, ML_Subscribers.SubscriberID AS SubscriberID FROM ML_Clicks INNER JOIN ML_Subscribers ON ML_Clicks.clkSubscriberID = ML_Subscribers.SubscriberID WHERE ML_Clicks.clkURLID = " & urlID & " ORDER BY ML_Clicks.clkLastDate DESC"
clkRecord.CursorType = 1
clkRecord.CursorLocation = 3
clkRecord.LockType = 3
clkRecord.Open()
%>
|
URL:
|
"><%=urlRecord("urlRedirect")%> |
|
Position:
|
URL
#<%=urlRecord("urlIndex")%> in the
<% if urlRecord("urlInside") = 0 then %>
Text
<% else %>
HTML
<% end if %>
version |
|
Last
Click:
|
<% if clkRecord.eof then %>
None
<% else %>
<%=getDateFromNum(clkRecord("clkLastDate"))%> by "><%=clkRecord("Email")%>
<% end if %>
|
| |
|
|
Total
Clicks:
|
<%=clkCountText%> Total clicks from Text Subscribers <%=clkCountPercentText%>
<%=clkCountHTML%> Total clicks from HTML Subscribers <%=clkCountPercentHTML%>
--------------------------------------------
<%=clkCountTotal%> Total clicks from Subscribers (100%) |
| |
|
|
Unique
Clicks:
|
<%=clkUCountText%> Unique clicks from Text Subscribers <%=clkUCountPercentText%>
<%=clkUCountHTML%> Unique clicks from HTML Subscribers <%=clkUCountPercentHTML%>
--------------------------------------------
<%=clkUCountTotal%> Unique clicks from Subscribers (100%) |
<%
urlRecord.movenext
loop
%>
|
|
Notes:
|
* The
URL (<%=maxURLRedirect%>) received the most
clicks
* The URL (<%=minURLRedirect%>) received the least
clicks |
<%
end if
set clkRecord = nothing
set urlRecord = nothing
%>
|
|