Click Stream Data Report

Last Post 15 Jan 2013 08:26 AM by bryanmurtha. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
bryanmurtha
New Member
New Member

--
14 Jan 2013 12:54 PM
I have a table of click stream data. This is coming out of our ad server.
"For all attributed conversions for advertiser 1234 (Advertiserx) in the past 60 days, by campaign, what was the average frequency in the previous 7 days?" Only impressions within the same campaign should count for the results. So for a conversion attributed to campaign A, only count impressions from campaign A in the frequency number. Frequency being defined as the number of impressions before a conversion event.

Data is just in one table

USER_ID_64 CAMPAIGN_ID ADVERTISER_ID EVENT_TYPE
1440807581240353501 377347 1218 imp
5392264912810465764 648993 1218 imp
8673773975809304901 465210 1218 imp
1599800228947371832 465210 1218 imp
2402746776034940631 465210 1218 conv


I'm kind of stuck on this one. Any help would be greatly, enormously appreciated!
bryanmurtha
New Member
New Member

--
14 Jan 2013 01:40 PM
CREATE TABLE [dbo].[L1FT_1](
[DATETIME] [datetime] NULL,
[USER_ID_64] [numeric](22, 0) NULL,
[AUCTION_ID_64] [numeric](22, 0) NULL,
[CAMPAIGN_ID] [numeric](22, 0) NULL,
[ADVERTISER_ID] [numeric](22, 0) NULL,
[EVENT_TYPE] [varchar](50) NULL,
)

DATETIME USER_ID_64 AUCTION_ID_64 CAMPAIGN_ID ADVERTISER_ID EVENT_TYPE
2013-01-01 00:01:07.000 1440807581240353501 1551288167349031009 377347 1218 conv
2013-01-01 00:01:12.000 5392264912810465764 5034633080599473145 648993 1218 conv
2013-01-01 00:01:15.000 8673773975809304901 819989334178758158 465210 1218 conv
2013-01-01 00:01:55.000 1599800228947371832 8481413779900741508 465210 1218 conv
2013-01-01 00:02:45.000 2402746776034940631 2537760596143653064 465210 1218 conv
bryanmurtha
New Member
New Member

--
15 Jan 2013 07:32 AM
I'm getting closer but still could use some help. Maybe I'll get it done before hand, we will see.



WITH Campaign_Frequency (USER_ID_64, CAMPAIGN_ID, RECORD_DATE, FREQUENCY)
AS
-- Store the number of impressions per day for the past 60 days
-- By User, Campaign and Day
(
SELECT L1.USER_ID_64
, L1.CAMPAIGN_ID
, CAST(L1.[DATETIME] AS DATE) AS RECORD_DATE
, COUNT(*) AS FREQUENCY
FROM level1fields L1
WHERE L1.event_type='imp'
AND L1.ADVERTISER_ID = 3582
AND L1.[DATETIME] > GETDATE()-60
GROUP BY L1.USER_ID_64
,L1.CAMPAIGN_ID
,CAST(L1.[DATETIME] AS DATE)
)



SELECT CF.CAMPAIGN_ID
, CF.RECORD_DATE
, AVG(CF.FREQUENCY) AS AVERAGE_FREQUENCY
FROM Campaign_Frequency CF
INNER JOIN level1fields L1
ON L1.USER_ID_64 = CF.USER_ID_64
AND L1.CAMPAIGN_ID = CF.CAMPAIGN_ID
WHERE L1.PIXEL_ID <> 0 -- Conversion Event
AND L1.ADVERTISER_ID = 3582
bryanmurtha
New Member
New Member

--
15 Jan 2013 08:26 AM
I thought I had it but it's giving me this error.
Msg 422, Level 16, State 4, Line 22
Common table expression defined but not used.


WITH Campaign_Frequency (USER_ID_64, CAMPAIGN_ID, RECORD_DATE, FREQUENCY)
AS
-- Store the number of impressions per day for the past 67 days
-- Since we need the Previous 7 days of data for the previous 60
-- By User, Campaign and Day
(
SELECT L1.USER_ID_64
, L1.CAMPAIGN_ID
, CAST(L1.[DATETIME] AS DATE) AS RECORD_DATE
, COUNT(*) AS FREQUENCY
FROM level1fields L1
WHERE L1.event_type='imp' -- Impressions
AND L1.ADVERTISER_ID = 3582 -- For Seamless
AND L1.[DATETIME] > GETDATE()- 67 -- In the Past 7 Days
GROUP BY L1.USER_ID_64
,L1.CAMPAIGN_ID
,CAST(L1.[DATETIME] AS DATE)
)

--select * from Campaign_Frequency CF
SELECT 'day',DATEADD(day,-7,GETDATE())
SELECT L1.CAMPAIGN_ID
, CAST(L1.[DATETIME] AS DATE)
, (SELECT SUM(FREQUENCY) FROM Campaign_Frequency CF WHERE CF.USER_ID_64 = L1.USER_ID_64 AND CF.CAMPAIGN_ID = L1.CAMPAIGN_ID AND CF.RECORD_DATE > DATEADD(day,-7,L1.DATETIME)) AS AVERAGE_FREQUENCY
FROM level1fields L1
WHERE L1.PIXEL_ID <> 0 -- Conversion Event
AND L1.ADVERTISER_ID = 3582 -- Seamless
AND L1.[DATETIME] > GETDATE()-60 --Last 60 Days
GROUP BY L1.USER_ID_64
, L1.CAMPAIGN_ID
, CAST(L1.[DATETIME] AS DATE)


Acceptable Use Policy
---