Integrations runs a PPC (pay-per-click) report each week (Thursday) to assist in the evaluation of our online advertising.
SELECT cal_week,
cal_date,
cal_day_of_week,
type,
sourcegroup,
source,
Count (source) AS count,
0 AS conversion
FROM telcom.dbo.qwestcalls b
INNER JOIN _billm..typesourcenumber c
ON b.dialednum = c.number
INNER JOIN misc_10..lu_date d
ON b.callday = d.cal_date
AND callday > '2/19/2014'
AND calldur < 60
GROUP BY cal_week,
cal_date,
cal_day_of_week,
type,
sourcegroup,
source
UNION
SELECT cal_week,
cal_date,
cal_day_of_week,
type,
sourcegroup,
source,
Count (source) AS count,
1 AS conversion
FROM telcom.dbo.qwestcalls b
INNER JOIN _billm..typesourcenumber c
ON b.dialednum = c.number
INNER JOIN misc_10..lu_date d
ON b.callday = d.cal_date
AND callday > '2/19/2014'
AND calldur >= 60
GROUP BY cal_week,
cal_date,
cal_day_of_week,
type,
sourcegroup,
source