Category: SWXL Templates

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.

DEPENDENCIES:

KWG Preprocess Scripts 02a-04 are up to date. If any new URLs or templates have been added since last run, you will likely need to rerun those scripts and (partially) truncate the tables below before running the following scripts.

PROCESS:

• https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWGeneration/trunk/KWExplosion/preprocess/05a-Site_URL_Region_Reference.pl
• https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWGeneration/trunk/KWExplosion/preprocess/05b-Site_URL_Contextual.sql
• https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWGeneration/trunk/KWExplosion/preprocess/05c-Route_Region.sql

OVERVIEW:

Run KW Priority on all generated and imported keywords using rank, eSV, rSV, URL associations and other data points

PROCESS:

Run the queries located in:
https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWPriority/trunk/script/update-kw-priority.sql

OVERVIEW:

Import URLs that are ranking for keywords in SEOClarity that we do not already have in other URL templates. We run a simple matching process, and if there are any URLs that are already in the valid site map, we do not import them. All other URLs are imported into the “Static URLs” template 102.

PROCESS:

Run: https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/util/import-stat-urls-to-smg.sql

Run script to find EDW URLs that are not going to be generated in the standard URL templates.
Add them to the Static URL template, or decide whether or not they need their own URL templates (you may need to contact the SEO Specialist).

Run: https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/util/import-edw-urls-to-smg.sql

DEPENDENCIES:

• Import/Update Hotel Property Data
• Request/Collect: Top 100 Regions

OVERVIEW:

Generate default Region Priorities according to the documented process below. Then give a special boost to the Top 100 Regions the Client identified directly.

PROCESS:

1. Run 03a-Updated_Latest_Data.sql
2. Generate Region Priorities – Primary Language:
   https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWGeneration/trunk/KWExplosion/preprocess/03b-update_region_priority.pl with “-d”
3. QA Region Priorities Generated
4. Clone Region Priorities to 2nd and 3rd language:
   https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWGeneration/trunk/KWExplosion/preprocess/03c-update_region_priority-non-primary.sql

NOTES:
https://docs.google.com/a/myersmediagroup.com/document/d/192C33h2Y-Glo4lcJfTX5mj4_yDil_HEbT4wrGH3zsdw/edit

OVERVIEW:

Primary Language Only. One common method used to adjust priorities of keywords is to group them in collections with boost values which influence the priorities of those keywords. Note that only entries with a boost value need to be updated and while a SWXL may have any number of boosted collections, the standard ones are:

• User Association keywords. We boost priority for keywords in this collection, as most of the keywords are important. This is especially important for new keywords we didn’t have in GKMS before because they will have no other data to give them priority.
• ‘SV = 0’ keywords. If keywords have no real search volume, i.e. no one searches for it, we should lower its priority.
• ‘Expedia POS Top Targets’. If the client specifies Top Target keywords, these need to be in a boosted collection to ensure they get placed in G1.

PROCESS:

• Review the dbo.collection_info.query column which describes how each collection is updated.
• Update the dbo.collection_info.last_updated column

Run the scripts located in:
https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/DB/tools/db_builder/GKMS/common/USP/update_collection/

NOTES:

See http://gkms-{pos}.mmg.local:8080/collections.aspx for an admin overview of the database tables.