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 "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., 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='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,
-Starwiz
