I’m writing a statistical analysis program in ASP.net and MSSQL7 that
analyzes data that I’ve collected from my business’s webpage.  I’ve
arrived at problems writing a SQL call to generate certain statistics.

Whenever someone enters our site from one of the PPC search engines, I
write out a row to the Hits table. In that table are the following
columns:
HitID - the Unique ID assigned to each hit that comes into the site 
Keyword - the keyword the user searched on when he or she came to the
site
SearchEngine - the PPC engine the user came from 
Source - this is pretty much always 'PPC'...if we were to do other
things, like a newsletter, then this would be different.
TimeArrived - the date and time the user arrived at the website. I
have no idea why I didn't call it "datearrived," since I use "date"
and not "time" pretty much everywhere else...
(I don't think the rest are important, but they might be, so I'll
include them for completeness's sake)
Referring URL - the URL the user came from 
Referring Website - the string between the 'http://' and the first '/'
in the URL. I know it's redundant information, but when I designed
this part, I didn't know how to parse it out afterwards, so I just
figured I'd duplicate it.
Page Visited - the page the user first arrived at 

When a person comes to the site, I also write out a session cookie
containing the user's hitID. If the person fills out an enrollment
form (a process which we refer to as "responding"), I attach that
session ID to the form. The response form (and thus the responses
table) is long; these are the important fields:
id - a unique ID for each response 
date - the date and time of the response 
status - a varchar field containing a status code. I would have made
it a number, but I wanted it to be viewable from looking at the raw
database.
hitid - the HitID of the user, taken from the session cookie. If there
is no session cookie (for whatever reason), the HidID is written out
as 0.  While it wouldn't occur often, I can't guarantee that there
will never be more than one response record attached to a singular
hitid.

Later, some of the responses turn into "confirmations", which means
that they've actually ordered from us, not just filled out the form.
This usually happens about three or four days after the initial
response. When this happens, the status of the response is changed to
a phrase containing the word "confirm" in it (there are a few of them,
but they all contain that word).

So now that we've collected all this marketing intel., we need to
analyze it.

I've written a parser that takes reports from various PPC companies
and puts them into a table called PPC. Information in this column is
written out as one record per search engine per keyword per day. The
schema is as follows:
id - a unique ID for the record in the table 
date - the date to which the information in the record applies 
searchengine - the PPC engine to which the information applies 
keyword - the keyword to which the information applies 
clicks - the number of clicks on the applicable keyword on the
applicable search engine on the applicable day.
impressions - same as clicks, but for impressions 
cpc - the cost per click on the applicable keyword ... 
avgpos - (I don't always have a value for this field) The average
position that the keyword was shown in for the applicable keyword ...

With this data in, the last step is actually analyzing the three
tables for useful statistics on the various keywords, search engines,
and time frames. That's the step I've been trying to complete.

So what I need is a SQL call that I can run that generates a table
with the following information:
SearchEngine 
Keyword 
Cost / Click - When calculating the CPC, I can't just take an average
of all the records.  I need to calculate the total amount spent per
day (clicks * cpc), add that up for every day, and then divide that by
the number of total clicks.  Just doing an average doesn't take into
account the fact that some days we'll get more clicks than others.
Total Spent - # Clicks * CPC 
#Responses - counting the number of records in the responses table 
#Confirms - counting the number of records in the responses table with
"confirm" in their status
Total Spent / #Responses 
Total Spent / #Confirms 

Oh yeah, and I want to be able to order by any four of the fields in
any order, narrow my selection to only those keywords that either are
or contain a user-specified string, further narrow my selection to
only those records that fit other user-specified criteria for any of
the columns in the table I'm generating, and select only the top x
records (where x is a user-specified number).  I already have
user-controls that output the SQL for all of these things, but I need
to have places in which I may put that SQL in my call.

Between someone on the www.asp.net forums and myself, I’ve come up
with the following SQL call.  Right now, its output for nearly every
row is incorrect, I think in a large part due to the fact that the
method that I’m using to generate the number of clicks is yielding
incorrect values.  I’ve got to get this done ASAP, and I hope this
method will yield better results than this guy and I (both of whom
aren’t SQL experts) working to try and figure it out.

If modifying this call is easier than writing a whole new one, be my
guest; if you’d prefer to write a new one, I’m game for that, too. 
I’m just concerned with its working right now.

Anyway, here’s the call:

/*sp_dboption @dbname='NDP', @optname='Select Into', @optvalue=true;*/
/*Running the above might be necessary to get the "Select Into"s to
work*/

Drop table ResponsesPPC
Drop table ConfirmPPC
Drop table TempPPC

SELECT Responses.[ID] as [ID], Responses.Status, PPC.SearchEngine,
PPC.Keyword
Into ResponsesPPC
FROM Responses, PPC
WHERE Responses.HitID IN
           (SELECT Hits.HitID
            FROM Hits
            WHERE Hits.SearchEngine = PPC.SearchEngine
                 AND Hits.Keyword = PPC.Keyword)

SELECT ID, Status, SearchEngine, Keyword
Into ConfirmPPC
FROM ResponsesPPC
WHERE Status LIKE "%confirm%"
Order by SearchEngine, Keyword

SELECT PPC.SearchEngine, PPC.Keyword,
SUM(PPC.Clicks), /*I noticed that this
column gives me incorrect values
(I don't need it in my final report, but it's useful for debugging).
For some keywords, it gives me huge numbers
(e.g. 265 clicks on one word that got ~10 clicks /day over five days),
and for others, it doesn't give me enough.  I think this is a major
part
of what's throwing off the rest of the statistics*/
Case SUM(PPC.Clicks) WHEN 0 THEN 0 ELSE
SUM(PPC.clicks * PPC.cpc) / SUM(PPC.Clicks) END as CPC,
SUM(PPC.clicks * PPC.cpc) AS TotalCost,
count(ResponsesPPC.ID) As NumResponses,
Count(ConfirmPPC.ID) As Confirms,
(Case Count(ResponsesPPC.ID) WHEN 0 THEN 0 ELSE
SUM(PPC.clicks * PPC.cpc) / count(ResponsesPPC.ID) END) AS
CostPerResponse,
(Case Count(ConfirmPPC.ID) WHEN 0 THEN 0 ELSE 
SUM(PPC.clicks * PPC.cpc) / count(ConfirmPPC.ID) END) As
CostPerConfirm
FROM (PPC LEFT JOIN ResponsesPPC ON PPC.SearchEngine =
ResponsesPPC.SearchEngine
AND PPC.Keyword = ResponsesPPC.Keyword)
LEFT JOIN ConfirmPPC ON PPC.SearchEngine = ConfirmPPC.SearchEngine
AND PPC.Keyword = ConfirmPPC.Keyword
GROUP BY PPC.SearchEngine, PPC.Keyword
Order by PPC.keyword desc

/*Drop table ResponsesPPC
Drop table ConfirmPPC
Drop table TempPPC
*/
/*I don't drop them right now so I can look at them,
but normally, one would drop those tables.*/

Thanks a lot for your help,
-Justin Lebar