Set SWXL Parameters (Steps 7-11)

OVERVIEW:

• Set SWXL link parameters and group sizes.
• QA SWXL parameters.

PROCESS:

7. Get count of URLs that can be in SWXL.

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/URLPriority/util/get-count-of-urls-in-swxl.sql

select count(su.site_url_id)
from dbo.Site_URL su with(nolock)
  join up.vw_URL_Priority vup with(nolock) on su.site_url_id = vup.url_id
where exists (select 1 from dbo.KW_URL_Main_Assignment kuma with(nolock)
      join dbo.KW_Collection kc with(nolock) on kuma.kw_id = kc.kw_id
      join dbo.Collection c with(nolock) on kc.collection_id = c.id and c.name = 'ILG Inclusion list'
    where kuma.site_url_id = su.site_url_id);

8. QA up.URL_Priority_Factor and up.URL_Priority_Value; mainly check if there are some URLs with data for each of the factors.

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/URLPriority/util/qa-url-priority-factors.sql

select upf.name, upf.weight, upf.need_normalized, isnull(t.cnt, 0) as cnt
  from up.URL_Priority_Factor upf
    left outer join (select upv.url_priority_factor_id, count(*) as cnt
      from up.URL_Priority_Value upv with(nolock)
        join dbo.Site_URL su with(nolock) on upv.url_id = su.site_url_id
      where upv.data_value > 0
      group by upv.url_priority_factor_id) t on upf.id = t.url_priority_factor_id
  where upf.weight > 0
  order by upf.weight desc;

9. Create/update an entry in up.URL_SWXL_Parameter.

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/URLPriority/util/insert-URL_SWXL_Parameter.sql

select * from up.url_swxl_parameter order by id desc;

insert into up.URL_SWXL_Parameter (group_count,urls_per_group,max_links_per_group,min_url_links,min_kw_links,links_per_page,p2p_links_per_page,created_timestamp,process_flag,kw_ls_ratio)
values (
  -- group_count (usually between 4 and 8)
    4,
  -- urls_per_group (need to be pre-calculated)
    '3000,25000,250000,{remaining_swxl_urls}',
  -- max_links_per_group (need to be precise for top groups)
    '150000,8000,1000,1000',
  -- min_url_links
    4,
  -- min_kw_links (usually 1)
    1,
  -- links_per_page (almost always 32)
    32,
  -- p2p_links_per_page (almost always 32)
    12,
  -- created_timestamp
    getdate(),
  -- process_flag (0 for new entries)
    0,
  -- kw_ls_ratio; usually 0.5
    0.5);

select * from up.url_swxl_parameter order by id desc;

10. Assign URL/KW links. Takes about 10-15 minutes typically.

exec up.usp_assign_url_links @url_swxl_parameter_id = @url_swxl_parameter_id;
 exec up.usp_assign_kw_links @url_swxl_parameter_id = @url_swxl_parameter_id;

11. QA

https://dev.myersmediagroup.com/mmgsvn/KMS/trunk/URLPriority/util/qa-url-priority.sql

Template: “{pos} QA URL Priority yyyymmdd.xlsx” in https://drive.google.com/open?id=0BxGkM7mSsh-4bUxUMHBWYnBieDQ

A NULL group_id in “Group Summary” means the urls_per_group values in #9 are incorrect.