Incorporate EDW KW-Based Monthly

OVERVIEW:

For a New SWXL – extract the last 15 months of EDW data for this POS. Filter for all kws with clicks >=3, all Search Engines. Include KW, landing page URL, monthly sum of clicks, orders and GB.

For an Existing SWXL – Just make sure the most recent EDW data has been imported. This is mostly automated now, at least for existing POS.

http://builds.mmg.local:8080/view/-Main/job/data_edw-monthly-prod{01,02,03}/

QA:

declare @start_date datetime;
set @start_date = (select cast(dateadd(month, -18, dateadd(day, 1 - day(getdate()), getdate())) as date));

select [date] [Report Period], count(*) [All], sum(clicks) [Clicks], sum(orders) [Orders], sum(GrossBookings) [Gross Bookings]
from dbo.EDW_Daily with(nolock)
where [date] >= @start_date
group by [date] order by [date];

select [report_period] [Report Period], count(*) [All], sum(total_clicks) [Clicks], sum(total_orders) [Orders], sum(total_gross_bookings) [Gross Bookings]
from dbo.KW_Performance with(nolock)
where [report_period] >= @start_date
group by [report_period] order by [report_period];

select [report_period] [Report Period], count(*) [All], sum(clicks) [Clicks], sum(orders) [Orders], sum(gross_bookings) [Gross Bookings]
from dbo.URL_Performance with(nolock)
where [report_period] >= @start_date
group by [report_period] order by [report_period];

NOTES:

Currently, this data is collected from Bharath Maturu (a-bmaturu@expedia.com). He runs a manual process and publishes the latest EDW data in the SFTP drop. The SFTP drop for both scheduled and manual drops is sftp.expedia.com.