SELECT keyword AS keyword
     , site AS site
     , campaign_name AS campaign_name
     , AVG(average_position) AS average_position
     , SUM(clicks) AS clicks
     , SUM(conversions) AS conversions
     , SUM(revenue) AS revenue
FROM (

-- Adwords Keyword Data

SELECT kp.keyword AS keyword,
       w.website_short_name AS site,
       kp.date AS date,
       kp.campaign_name AS campaign_name,
       AVG(NULLIF(kp.average_position,0)) AS average_position,
       SUM(COALESCE(kp.clicks,0)) AS clicks,
       SUM(COALESCE(kp.cost,0))/1000000 AS cost,
       SUM(COALESCE(kp.conversions,0)) AS conversions,
       (kp.conversions::FLOAT * kp.value_per_conversion::FLOAT) AS revenue
FROM ads.keyword_performance kp  -- For Adwords
INNER JOIN reference.websiteidentifiers w
        ON w.website_id = kp.website_id
WHERE kp.date BETWEEN '{{ start_date }}' AND '{{ end_date }}'
  AND kp.conversions > 0
GROUP BY kp.keyword
       , w.website_short_name
       , kp.date
       , kp.campaign_name
       , kp.conversions
       , kp.value_per_conversion

UNION ALL

-- Bingads Keyword data

SELECT bkp.keyword AS keyword,
       w.website_short_name AS site,
       bkp.date AS date,
       bkp.campaign_name AS campaign_name,
       AVG(NULLIF(bkp.average_position,0)) AS average_position,
       SUM(COALESCE(bkp.clicks,0)) AS clicks,
       SUM(COALESCE(bkp.spend,0)) AS cost,
       SUM(COALESCE(bkp.conversions,0)) AS conversions,
       SUM(COALESCE(bkp.revenue,0)) AS revenue
FROM ads.bingads_keyword_performance bkp
INNER JOIN reference.website_identifiers w
        ON w.website_id = bkp.website_id
WHERE bkp.date BETWEEN '{{ start_date }}' AND '{{ end_date }}'
  AND bkp.revenue > 0
GROUP BY bkp.keyword
       , w.website_short_name
       , bkp.campaign_name
       , bkp.date
) x
GROUP BY keyword
       , site
       , campaign_name
ORDER BY revenue DESC
LIMIT '{{ limiter }}'

{% form %}

start_date:
  type: date
  default: 2017-01-01

end_date:
  type: date
  default: {{'now' | date: "%s" | minus : 86400 | date: "%Y-%m-%d"}}

limiter:
  type: text
  default: 50

{% endform %}