Generating various statistics from data in MSSQL7

[Justin Lebar]

Sorry about the huge post, but I think this is the amount of
information necessary for someone to help me with a good answer.

I'm writing a statistical analysis program in ASP.net and MSSQL7 that
analyzes data that I've collected from my business's webpage and the
hits it's collecting from the various pay-per-click (PPC) engines.
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 "datearrive d," 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., I need to
analyze it.

I've written a parser that takes reports from various pay-per-click
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.

After many trials and tribulations, 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.

If you'd like to help me and you think that modifying the following
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, and any help you can give me is greatly
appreciated.

Anyway, here's the call:
/*sp_dboption @dbname='NDP', @optname='Selec t 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.Statu s, PPC.SearchEngin e,
PPC.Keyword
Into ResponsesPPC
FROM Responses, PPC
WHERE Responses.HitID IN
(SELECT Hits.HitID
FROM Hits
WHERE Hits.SearchEngi ne = PPC.SearchEngin e
AND Hits.Keyword = PPC.Keyword)

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

SELECT PPC.SearchEngin e, 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(Responses PPC.ID) As NumResponses,
Count(ConfirmPP C.ID) As Confirms,
(Case Count(Responses PPC.ID) WHEN 0 THEN 0 ELSE
SUM(PPC.clicks * PPC.cpc) / count(Responses PPC.ID) END) AS
CostPerResponse ,
(Case Count(ConfirmPP C.ID) WHEN 0 THEN 0 ELSE
SUM(PPC.clicks * PPC.cpc) / count(ConfirmPP C.ID) END) As
CostPerConfirm
FROM (PPC LEFT JOIN ResponsesPPC ON PPC.SearchEngin e =
ResponsesPPC.Se archEngine
AND PPC.Keyword = ResponsesPPC.Ke yword)
LEFT JOIN ConfirmPPC ON PPC.SearchEngin e = ConfirmPPC.Sear chEngine
AND PPC.Keyword = ConfirmPPC.Keyw ord
GROUP BY PPC.SearchEngin e, 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,
-Starwiz

[Erland Sommarskog]

Justin Lebar (st*****@innova te-inc.com) writes:

Sorry about the huge post, but I think this is the amount of
information necessary for someone to help me with a good answer.
Yes, there was a whole lot of information, but I was not able to get
a complete understanding of what's going on. Probably because I'm too
impatient to go through it over and over again to get the pieces together.

There is a however a standard advice for this kind of problems. Namely,
in your posting include CREATE TABLE statements for the involved tables
(it may be a good idea to trim irrelevant columns), INSERT statements
with sample data and the desired output from that data. I realize that
in your case you may need to provide some 100-200 rows to get some
representative data. An alternative is put the data in comma-separated
files that can be bulk-loaded. Put any such files in an attachments, to
avoid line-wrap problems.
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.

Not that I think it matters here, but I would store a NULL in HitID when
there is no hit id.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Justin Lebar (we*******@rans omlatin.com) writes:

Because I'm posting to this online and not through an e-mail list, I
don't see where I can attach any files. What I've done is put CSVs of
samples for each table inside this post; you can still open it with
Excel, albeit it might be a little more difficult because of the
inserted linebreaks. I couldn't think of any other method...oh well.

Most newsreaders provide facilities to make attachments. I am not
familiar with the DevDex interface to tell whether this is possible
there.

Anyway, the only file that wrapped was the Hits table, and I was able
to repair that part, and I even managed to load it. However, I gave
up with the other two, as the date format was funky. Hits was also a
little suspect, as there was one column missing.

And most of all: there was no expected results to work from!

It would be better if you could create the BCP files in this way:

BCP db..tbl out tbl.bcp -T -c -t,

When you review the BCP files, make sure that dates are in the format
YYYY-MM-DD hh:mm:ss (milliseconds may be present, but BCP does not
seem to like missing seconds.) I believe that BCP will always generate
this format, and not honour regional settings.

If you cannot find a way to make attachments, then just include the
files as last time.

Be careful that the data agrees with the CREATE TABLE statements you posted.

And don't forget to include the expected results!

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Starwiz]

Actually, in the past few hours, I re-wrote the entire call, and I got
it to work! Sorry I put you through all that...thanks for working with
me; I appreciate it.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Starwiz (no****@nospam. com) writes:

Actually, in the past few hours, I re-wrote the entire call, and I got
it to work! Sorry I put you through all that...thanks for working with
me; I appreciate it.

Glad to hear you got it working on your own!
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp