SHARE
TWEET

Untitled

a guest Oct 10th, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  #  Компания Keyword / Фид Marka
  2. SELECT
  3.        "DEA-Marka-rus" AS campaignName,
  4.        CONCAT(m.marka_name, "#", kw.`language`) AS `adGroupName`,
  5.        CONCAT("+", REPLACE(REPLACE(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(kw.keyword,
  6.                                                                         "[VCH]", vch.VCH_synonym),
  7.                                                                 "[marka_en]", mas.marka_synonym),
  8.                                                         "[marka_rus]", mas.marka_synonym),
  9.                                                 "[attractor]", IFNULL(att.attractor,""))),
  10.                                    " ", " +"), ",", ".")) AS keyword,
  11.        "Broad" AS keyword_match_type,
  12.        MD5(
  13.            CONCAT_WS(
  14.                '+'
  15.                , CONCAT("+", REPLACE(REPLACE(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(kw.keyword,
  16.                                                                                   "[VCH]", vch.VCH_synonym),
  17.                                                                           "[marka_en]", mas.marka_synonym),
  18.                                                                   "[marka_rus]", mas.marka_synonym),
  19.                                                           "[attractor]", IFNULL(att.attractor,""))),
  20.                                              " ", " +"), ",", "."))
  21.                , "Broad"
  22.                , CONCAT(m.marka_name, "#", kw.`language`)
  23.                , "DEA-Marka-rus"
  24.  
  25.        )) AS DOK_keyword_ID
  26. FROM
  27.        yii_doc.adwords_plitka AS p
  28.               INNER JOIN ppc.model AS m ON p.model = m.seo_model_id
  29.               LEFT JOIN ppc.model_synonyms AS mos ON p.model = mos.seo_model_id
  30.               LEFT JOIN ppc.marka_synonyms AS mas ON p.marka = mas.seo_marki_id AND mos.`language` = mas.`language` -- язык марки совпадает с языком модели
  31.               LEFT JOIN ppc.keyword AS kw ON mos.`language` = kw.`language` AND kw.campaign_type = "Marka"
  32.               LEFT JOIN ppc.attractor AS att ON kw.attractor = att.is_for_Z
  33.               CROSS JOIN ppc.VCH_synonyms AS vch
  34. GROUP BY 1,2,3
  35. HAVING keyword IS NOT NULL;
  36.  
  37.  
  38.  
  39.  #  Компания Ads / Фид Marka
  40. SELECT
  41.        "DEA-Marka-rus" AS campaignName,
  42.        CONCAT(m.marka_name, "#", ad.`language`) AS adGroupName,
  43.        REPLACE(ad.headline_1, "[m.marka_name]", IF(ad.`language`="en", m.marka_en_ads, m.marka_rus_ads)) AS headline1,
  44.        ad.headline_2 headline2,
  45.        REPLACE(ad.description, "[m.marka_name]", IF(ad.`language`="en", m.marka_name, m.marka_rus_ads)) AS description1,
  46.        ad.path_1 AS path1,
  47.        REPLACE(IF(ad.`language`="en",m.marka_en_ads,m.marka_rus_ads), "/", " ") AS path2,
  48.        SUBSTRING_INDEX(p.url,"/",5) AS finalUrl,
  49.        "active" AS ads_status,
  50.        MD5(
  51.          CONCAT_WS(
  52.            '+'
  53.            , REPLACE(ad.headline_1, "[m.marka_name]", IF(ad.`language`="en", m.marka_en_ads, m.marka_rus_ads))
  54.            , REPLACE(ad.description, "[m.marka_name]", IF(ad.`language`="en", m.marka_name, m.marka_rus_ads))
  55.            , SUBSTRING_INDEX(p.url,"/",5)
  56.            , ad.path_1
  57.            , REPLACE(IF(ad.`language`="en",m.marka_en_ads,m.marka_rus_ads), "/", " ")
  58.            , CONCAT(m.marka_name, "#", ad.`language`)
  59.            , "DEA-Marka-rus"
  60.           )
  61.        ) AS DOK_Ads_ID
  62. FROM yii_doc.adwords_plitka AS p
  63.        INNER JOIN ppc.model m ON p.model = m.seo_model_id
  64.        INNER JOIN ppc.zapchast z ON p.zapchast = z.seo_izdelie_id AND z.is_catalog = 1 AND z.is_redirected = 0
  65.        CROSS JOIN (SELECT * FROM ppc.advertisement WHERE campaign_type = "Marka") AS ad
  66. GROUP BY campaignName, adGroupName, ad.id;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top