Category: SWXL Templates

Generate EDW and Region-Name Associations

DEPENDENCIES:

  • Import/Update: EDW Keyword & URL Data

OVERVIEW:

  • Generate EDW KW-URL Associations (Primary language only)
  • Generate Region-Name Associations – All languages
  • Generate Special-Regions Associations – All languages

PROCESS:

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWUrlAssociation/trunk/EDWAssociation/script/edw-assoc.pl

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWUrlAssociation/trunk/OtherAssociations/Region_Names.sql

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/KWUrlAssociation/trunk/OtherAssociations/Special_Regions.sql

Review Region Priority

Primary Language Only. Ask the client to review the Regions in the GSI and their associated Priorities, for the top 100 to 1,000 regions (some will do more, 100 should be the minimum).

The MMG QA role should also check the top Regions, comparing what is listed in the GSI to the Specialist Workbook. Also look at the regions in the top 500 that do not have very high search volume or Gross Bookings numbers – make notes on those that fit that description and are not Specialist-requested top regions.

This task should be run only on the Primary language, as Secondary languages’ Region Priorities should be a clone of the Primary. If any updates are made, they should be cloned to the secondaries

Increment Summary Version

OVERVIEW:

  • Generate Summary Tables – “Maintenance Version”

PROCESS:

  1. Insert a new ‘fm.Summary_Version’ entry with ‘is_live’ = False 
    insert into fm.summary_version (version, Last_updated, is_live, Note) values ('',getdate(), 0, '');
  2. Update config ‘fm.summary.DbVersion’. 
    update config set value = '', note = '', updated = getdate() where component = 'fm.summary';
  3. Generate Summary Tables 
    exec fm.usp_update_Summary_Historical_All;
  4. Copy to both prod01 and gsi.myersmediagroup.com: 
    use global_swxl; exec [dbo].[usp_global_summary_update_all] @site_id = ?;

Update Geo Data

OVERVIEW:

Update Atlas/Gaia version in GKMS database – All Languages

Update SWXL database references to the latest datasource versions of Atlas/Gaia.
This data is used in URL and KWGeneration processes and includes Regions, Airports, POIs and Activities.

PROCESS:

  1. The ‘AtlasVersion’ value in Config table needs to be updated to specify which version in the datasources database to reference. 
    set-config.pl gkms_us AtlasVersion 20170724
  2. Run: 
    build-db.pl gkms_us update.inc --nocreate
    https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/db/synonym/atlas/update.inc
  3. Make sure views and synonyms point to the right version.
  4. Update your .mmg_conf.pl file.

NOTES:

After this point, KW Generation preprocess should be considered out of date.

 

Generate SWXL Workbooks

DEPENDENCIES:

  • Import/Update Hotel GB
  • New Language SWXL: Need to ask for KW Variants from Specialist
  • URL Templates
  • KW Root Terms
  • KW Variants
  • Generate KW Variant eSV Factors for all URL Templates

OVERVIEW:

To collect an initial review from the SEO Specialist we provide them with the following items:

  • KW Variants per LOB
  • URL Templates to include (and inform of new custom URLs)
  • KW root terms (New SWXL Only)

Workbook templates can be found on Google Drive:
https://drive.google.com/open?id=0BxGkM7mSsh-4bUxUMHBWYnBieDQ

  • {pos} SWXL Workbook Main yyyymmdd.xlsx
  • {pos} SWXL Workbook Previous Boosted Collections yyyymmdd.xlsx
  • {pos} SWXL Workbook URL Reassign yyyymmss.xlsx
  • (there may be others needed on a case-by-case basis)

DETAILS:

Most of the scripts to dump the data for the workbook are in: https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/SWXL-workbook/

Generate SWXL Main Workbook

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/SWXL-workbook

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/SWXL-workbook/workbook-lob-kw-variants.pl

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/SWXL-workbook/workbook-top-100-regions.sql

Generate SWXL Previous Boosted Collections Workbook

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/SWXL-workbook/Previous_User_Associations_and_Boosted_Collections%.sql.

Generate SWXL URL Level Reassignments Workbook

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/SWXL-workbook/workbook-url-level-reassign.sql

  • Generate Workbooks – Primary language
  • Generate Workbooks – 2nd language
  • Generate Workbooks – 3rdlanguage

NEW SWXL:

Post LOB Link Category Names (localized). Create a tab within the workbook that indicates how SWXL will display LOB category names. These will typically include:

  • Activities, a.k.a Things to Do
  • Car Rentals a.k.a. Car Hire
  • Cruises
  • Flights
  • Hotels
  • Vacation Packages a.k.a Holiday Packages City Breaks
  • General

NOTES:

Update template_elements based on changes noted during sample_url verification

For custom templates that we need to generate keywords, populate kwg.datafeed, kwg,datafeed_type, kwg,template_datafeed, kwg,kw_variants

Review Remaining Associators

OVERVIEW:

If there are any other active associators, they should be reviewed for whether their associations need updating.

Primary Language Only. Non-primary languages likely will not have any additional associators, as the only associations come from KWG, User Associations, and Bulk Rank Check.

PROCESS:

select kua.kw_url_associator_id as id, kua.[name], kua.description as [desc], url_cnt, kw_cnt
 ,suk.min_t, suk.max_t
 from kw_url_associator kua
 left outer join (
 select kw_url_associator_id, count(distinct site_url_id) as url_cnt, count(*) as kw_cnt,
 min(association_timestamp) as min_t,
 max(association_timestamp) as max_t
 from site_url_kw with(nolock)
 group by kw_url_associator_id
 ) suk on suk.kw_url_associator_id = kua.kw_url_associator_id
 where is_Active = 1
 order by kua.priority

Review Keyword Priority & SWXL Groups

OVERVIEW:

Client and MMG QA to review Group 1 Membership:

  • Are the right URL templates generally represented in the correct proportion in G1?
  • Are there terms with SV=0, low clicks, no performance data in G1 that should not be?
  • Ask client to validate that all their manually selected terms are included in G1, with the correct URL associations. QA team should validate this as well.
  • Update priorities/groups based on Specialist/analyst review

NOTES:

https://docs.google.com/a/myersmediagroup.com/document/d/1gtUgKgA4YYxhm6JVTp4bC4lYKhwXHgNrdTO1EJFb3Rg/edit#heading=h.540a4fcab830

Create Final SEO Toolbar Summary Tables

OVERVIEW:

  • Generate summary tables on GKMS database – All Languages
  • Copy summary tables to SEO Toolbar database – All Languages

PROCESS:

  1. Run from the GKMS_{pos} server 
    • ^KMS/trunk/Reporting/trunk/SEO_Toolbar_V2/GKMS/script/update-all-tables.pl 
    • ./update-all-tables.pl -c gkms_{pos} -l seo_toolbar -f
  2. Run from the seo_toolbar server 
    • ^KMS/trunk/Reporting/trunk/SEO_Toolbar_V2/seo_toolbar/script/update-all-tables-from-gkms.pl 
    • ./update-all-tables-from-gkms.pl -c seo_toolbar -p {POS}

Fetch-TG Crawl & Import

DEPENDENCIES:

  • Import/Update: Atlas Region Data
  • Import/Update Data: Hotel Property Data
  • Current URL template_elements for all travel guide (TG) URLs

OVERVIEW:
Crawl the site and extract the correct TG names and URLs for the following:

  • TG Hotels (hotel properties)
  • TG POI
  • TG Airports
  • TG Region Names for each LOB: Hotels, Flights, Car Rentals and Vacations

We run all regions through key URL templates for each LOB. We run each LOB just in case there are differences in the region names per LOB (there have been in the past).

Note that each crawl will very likely result in 400’s or 500’s status codes, which is probably a failure due to high load. These URLs should be retried (set is_processed = 0 and re-run 2-fetch.pl).

Reference: https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/fetch-tg

PROCESS:

  1. Crawl site and extract correct TG names and URLs 
    https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/fetch-tg/all-create-and-populate.sh (which is destructive)
  2. Fetch and parse: 
    https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/fetch-tg/all-fetch-and-parse.sh
  3. Monitor status with 
    https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/fetch-tg/all-status.pl
  4. Requeue failures with “-r” and retry at step 2.
     
  5. Update sitemap with correct TG names for each LOB. Run: 
    https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/SiteMapManager/trunk/site_map_validator/queries/import-validation-from-fetch-tg.sql

Client SWXL Workbook Integration

If the specialist specifies that URL templates should be added/enabled, check the following:

  • Ensure that root-variants are set correctly and reference the intended datafeeds.
  • Confirm that we have received datafeeds for all new custom templates.
  • Confirm that all custom datafeeds received contain the required information.