HfeBwZCh

1BEGIN;
2
3-- Ensure that tables structure is up to date
4SELECT create_or_update_tables_ddl();
5
6-- Update manual_costs table with data on affiliate costs
7SELECT update_affiliate_costs('2019-02-01', '2019-09-19');
8
9COMMIT;
10
11BEGIN;
12
13--
14-- Data refreshing
15--
16
17DROP TABLE IF EXISTS ais_new_attributions_and_costs;
18
19CREATE TABLE IF NOT EXISTS ais_new_attributions_and_costs AS
20WITH
21types AS (
22  SELECT DISTINCT type FROM attributions
23), exchange_rates AS (
24  SELECT
25    DISTINCT ON (
26      date,
27      source
28    )
29    date,
30    source,
31    currency,
32    rate,
33    auto
34  FROM exchange_rates
35  ORDER BY date, source, ctid DESC
36), channel_rename AS (
37    SELECT
38      DISTINCT ON (fromname, limit_renaming_to_column_name)
39      fromname,
40      toname,
41      limit_renaming_to_column_name,
42      is_regex
43    FROM channel_rename
44    ORDER BY fromname, limit_renaming_to_column_name, ctid DESC
45), costs_filtered AS (
46  SELECT
47    date,
48    type,
49    source,
50    medium,
51    campaign,
52    keyword,
53    channelgrouping,
54    adcontent,
55    placement,
56    creative,
57    impressions,
58    clicks,
59    reportedgoalcompletions,
60    reportedgoalallcompletions,
61    totalcost,
62    viewableimpressions,
63    exactmatchis,
64    searchis,
65    searchlostisrank,
66    qualityscore,
67    landingpageexp,
68    expectedctr,
69    bouncerate,
70    businessname,
71    description,
72    descriptionline1,
73    descriptionline2,
74    headline,
75    headline1,
76    headline2,
77    longheadline,
78    avgposition,
79    adrelevance,
80    budget,
81    contentlostisbudget,
82    searchlostisbudget,
83    contentis,
84    contentlostisrank,
85    adgroup,
86    adgroupstate,
87    status,
88    autocpc,
89    maxcpc,
90    matchtype,
91    firstpagecpc,
92    firstpositioncpc,
93    topofpagecpc,
94    displayurl,
95    destinationurl,
96    label,
97    productgroup,
98    productgroupl2,
99    adid,
100    adgroupid,
101    keywordid,
102    campaignid,
103    accountid,
104    adwordsnetwork,
105    biddingstrategytype,
106    device
107 FROM costs
108  WHERE date >= '2019-02-01' AND date <= '2019-09-19'
109), manual_costs AS (
110  SELECT
111    date,
112    type,
113    source,
114    medium,
115    campaign,
116    keyword,
117    channelgrouping,
118    adcontent,
119    placement,
120    creative,
121    impressions,
122    clicks,
123    reportedgoalcompletions,
124    reportedgoalallcompletions,
125    totalcost,
126    viewableimpressions,
127    exactmatchis,
128    searchis,
129    searchlostisrank,
130    qualityscore,
131    landingpageexp,
132    expectedctr,
133    bouncerate,
134    businessname,
135    description,
136    descriptionline1,
137    descriptionline2,
138    headline,
139    headline1,
140    headline2,
141    longheadline,
142    avgposition,
143    adrelevance,
144    budget,
145    contentlostisbudget,
146    searchlostisbudget,
147    contentis,
148    contentlostisrank,
149    adgroup,
150    adgroupstate,
151    status,
152    autocpc,
153    maxcpc,
154    matchtype,
155    firstpagecpc,
156    firstpositioncpc,
157    topofpagecpc,
158    displayurl,
159    destinationurl,
160    label,
161    productgroup,
162    productgroupl2,
163    adid,
164    adgroupid,
165    keywordid,
166    campaignid,
167    accountid,
168    adwordsnetwork,
169    biddingstrategytype,
170    device
171 FROM manual_costs
172  WHERE date >= '2019-02-01' AND date <= '2019-09-19'
173), attributions AS (
174  SELECT * FROM attributions
175  WHERE date >= '2019-02-01' AND date <= '2019-09-19'
176), renamedcostssources AS (
177  SELECT
178    date,
179    joinedtype AS type,
180    source,
181    medium,
182    campaign,
183    adgroup,
184    CASE
185      WHEN label IN (' --', '', '--') THEN NULL
186      ELSE CAST(label::json->>0 AS TEXT) END AS label,
187    matchtype,
188    COALESCE(keyword, '(Unavailable)') AS keyword,
189    status,
190    CASE WHEN source = 'bing' THEN '(Unavailable)' ELSE replace_null_values(adcontent) END AS adcontent,
191    headline2,
192    placement,
193    creative,
194    displayurl,
195    CASE
196      WHEN destinationurl IN (' --', '', '--') THEN NULL
197      ELSE CAST(destinationurl::json->>0 AS TEXT) END AS destinationurl,
198    COALESCE(productgroup, '(Unavailable)') AS productgroup,
199    COALESCE(productgroupl2, '(Unavailable)') AS productgroupl2,
200    adid,
201    adgroupid,
202    keywordid,
203    campaignid,
204    accountid,
205    adwordsnetwork,
206    biddingstrategytype,
207    device,
208    totalcost,
209    reportedgoalcompletions,
210    reportedgoalallcompletions,
211    clicks,
212    impressions,
213    viewableimpressions,
214    exactmatchis,
215    searchis,
216    searchlostisrank,
217    qualityscore,
218    landingpageexp,
219    expectedctr,
220    bouncerate,
221    avgposition,
222    adrelevance,
223    contentlostisbudget,
224    searchlostisbudget,
225    contentis,
226    contentlostisrank,
227    maxcpc,
228    firstpagecpc,
229    firstpositioncpc,
230    topofpagecpc,
231    COALESCE(channel_rename.toname, source) AS renamedsource
232  FROM (
233      -- costs with no type are to be merged with types from the attributions
234      SELECT costs_filtered.*, types.type as joinedtype
235      FROM costs_filtered, types
236      WHERE costs_filtered.type IS NULL
237      UNION ALL
238      -- costs with a given type use the provided value
239      SELECT costs_filtered.*, costs_filtered.type as joinedtype
240      FROM costs_filtered
241      WHERE costs_filtered.type IS NOT NULL
242      UNION ALL
243      -- manual uploaded costs
244      SELECT manual_costs.*, types.type as joinedtype
245      FROM manual_costs, types
246      WHERE manual_costs.type IS NULL
247      UNION ALL
248      -- manual uploaded costs with a given type use the provided value
249      SELECT manual_costs.*, manual_costs.type as joinedtype
250      FROM manual_costs
251      WHERE manual_costs.type IS NOT NULL
252    ) costs_with_type
253    LEFT OUTER JOIN channel_rename
254      ON ((not channel_rename.is_regex AND source = channel_rename.fromname) OR
255          (channel_rename.is_regex AND source ~ channel_rename.fromname))
256      AND (channel_rename.limit_renaming_to_column_name = 'source' OR channel_rename.limit_renaming_to_column_name IS NULL)
257  WHERE totalcost > 0
258    OR clicks > 0
259    OR impressions > 0
260), renamedcostsmediums AS (
261    SELECT *, COALESCE(channel_rename.toname, medium) as renamedmedium
262          FROM renamedcostssources
263          LEFT OUTER JOIN channel_rename
264          ON ((not channel_rename.is_regex AND medium = channel_rename.fromname) OR
265              (channel_rename.is_regex AND medium ~ channel_rename.fromname))
266            AND (channel_rename.limit_renaming_to_column_name = 'medium' OR channel_rename.limit_renaming_to_column_name IS NULL)
267), renamedcostscampaigns AS (
268  SELECT *, COALESCE(channel_rename.toname, campaign) AS renamedcampaign FROM channel_rename
269  RIGHT OUTER JOIN renamedcostsmediums
270  ON ((not channel_rename.is_regex AND campaign = channel_rename.fromname) OR
271      (channel_rename.is_regex AND campaign ~ channel_rename.fromname))
272  AND (channel_rename.limit_renaming_to_column_name = 'campaign' OR channel_rename.limit_renaming_to_column_name IS NULL)
273), grouped_costs AS (
274  SELECT
275    renamedcostscampaigns.date AS costdate,
276    type AS costtype,
277    renamedsource AS costsource,
278    renamedmedium AS costmedium,
279    renamedcampaign AS costcampaign,
280    keyword AS costkeyword,
281    adcontent AS costadcontent,
282    adgroup AS costadgroup,
283    placement AS costplacement,
284    creative AS costcreative,
285    MIN(label) AS costlabel,
286    COUNT(DISTINCT label) AS costcountlabel,
287    MIN(matchtype) AS costmatchtype,
288    COUNT(DISTINCT matchtype) AS costcountmatchtype,
289    MIN(status) AS coststatus,
290    COUNT(DISTINCT status) AS costcountstatus,
291    MIN(headline2) AS costheadline2,
292    COUNT(DISTINCT headline2) AS costcountheadline2,
293    MIN(displayurl) AS costdisplayurl,
294    COUNT(DISTINCT displayurl) AS costcountdisplayurl,
295    MIN(destinationurl) AS costdestinationurl,
296    COUNT(DISTINCT destinationurl) AS costcountdestinationurl,
297    COALESCE(sum(renamedcostscampaigns.totalcost*exchange_rates.rate),
298             sum(renamedcostscampaigns.totalcost)) AS totalcost,
299    SUM(reportedgoalcompletions) AS reportedgoalcompletions,
300    SUM(reportedgoalallcompletions) AS reportedgoalallcompletions,
301    SUM(clicks) AS clicks,
302    SUM(impressions) AS impressions,
303    SUM(viewableimpressions) AS viewableimpressions,
304    SUM(exactmatchis * impressions)/NULLIF(SUM(impressions), 0) AS exactmatchis,
305    SUM(searchis * impressions)/NULLIF(SUM(impressions), 0) AS searchis,
306    SUM(searchlostisrank * impressions)/NULLIF(SUM(impressions), 0) AS searchlostisrank,
307    SUM(qualityscore * impressions)/NULLIF(SUM(impressions), 0) AS qualityscore,
308    SUM(landingpageexp * impressions)/NULLIF(SUM(impressions), 0) AS landingpageexp,
309    SUM(expectedctr * impressions)/NULLIF(SUM(impressions), 0) AS expectedctr,
310    SUM(bouncerate * clicks)/NULLIF(SUM(clicks), 0) AS bouncerate,
311    SUM(avgposition * impressions)/NULLIF(SUM(impressions), 0) AS avgposition,
312    SUM(adrelevance * impressions)/NULLIF(SUM(impressions), 0) AS adrelevance,
313    SUM(contentlostisbudget * impressions)/NULLIF(SUM(impressions), 0) AS contentlostisbudget,
314    SUM(searchlostisbudget * impressions)/NULLIF(SUM(impressions), 0) AS searchlostisbudget,
315    SUM(contentis * impressions)/NULLIF(SUM(impressions), 0) AS contentis,
316    SUM(contentlostisrank * impressions)/NULLIF(SUM(impressions), 0) AS contentlostisrank,
317    SUM(maxcpc * impressions)/NULLIF(SUM(impressions), 0) AS maxcpc,
318    SUM(totalcost)/NULLIF(SUM(clicks), 0) AS avgcpc,
319    SUM(firstpagecpc * impressions)/NULLIF(SUM(impressions), 0) AS firstpagecpc,
320    SUM(firstpositioncpc * impressions)/NULLIF(SUM(impressions), 0) AS firstpositioncpc,
321    SUM(topofpagecpc * impressions)/NULLIF(SUM(impressions), 0) AS topofpagecpc,
322    productgroup AS costproductgroup,
323    productgroupl2 AS costproductgroupl2,
324    COUNT(DISTINCT productgroup) AS costcountproductgroup,
325    COUNT(DISTINCT productgroupl2) AS costcountproductgroupl2,
326    adid AS costadid,
327    1 AS costcountadid,
328    MIN(adgroupid) AS costadgroupid,
329    COUNT(DISTINCT adgroupid) AS costcountadgroupid,
330    MIN(keywordid) AS costkeywordid,
331    COUNT(DISTINCT keywordid) AS costcountkeywordid,
332    MIN(campaignid) AS costcampaignid,
333    COUNT(DISTINCT campaignid) AS costcountcampaignid,
334    MIN(accountid) AS costaccountid,
335    COUNT(DISTINCT accountid) AS costcountaccountid,
336    MIN(adwordsnetwork) AS costadwordsnetwork,
337    COUNT(DISTINCT adwordsnetwork) AS costcountadwordsnetwork,
338    MIN(biddingstrategytype) AS costbiddingstrategytype,
339    COUNT(DISTINCT biddingstrategytype) AS costcountbiddingstrategytype,
340    MIN(device) AS costdevice,
341    COUNT(DISTINCT device) AS costcountdevice
342  FROM renamedcostscampaigns
343  LEFT JOIN exchange_rates
344    ON renamedcostscampaigns.date::date = exchange_rates.date
345    AND renamedcostscampaigns.renamedsource = exchange_rates.source
346  GROUP BY
347    productgroup,
348    productgroupl2,
349    renamedcostscampaigns.date,
350    type,
351    renamedsource,
352    renamedmedium,
353    renamedcampaign,
354    keyword,
355    adcontent,
356    adgroup,
357    placement,
358    creative,
359    adid
360), costdims AS (
361  SELECT DISTINCT ON (
362      -- It's mandatory to distinct on date to sort by date to use the most recent value
363      date,
364      COALESCE(adid, '(Unavailable)'),
365      COALESCE(accountid, '(Unavailable)'),
366      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(source), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
367      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(medium), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
368      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaign), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
369      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keyword), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
370      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adcontent), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
371      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adgroup), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
372      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(placement), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
373      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(creative), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
374    )
375    COALESCE(REGEXP_REPLACE(REPLACE(LOWER(source), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)') AS costsource,
376    COALESCE(REGEXP_REPLACE(REPLACE(LOWER(medium), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)') AS costmedium,
377    COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaign), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)') AS costcampaign,
378    COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keyword), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)') AS costkeyword,
379    COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adcontent), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)') AS costadcontent,
380    COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adgroup), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)') AS costadgroup,
381    COALESCE(REGEXP_REPLACE(REPLACE(LOWER(placement), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)') AS costplacement,
382    COALESCE(REGEXP_REPLACE(REPLACE(LOWER(creative), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)') AS costcreative,
383    COALESCE(adid, '(Unavailable)') AS costadid,
384    COALESCE(accountid, '(Unavailable)') AS costaccountid,
385    COALESCE(adgroupid, '(Unavailable)') AS costadgroupid,
386    COALESCE(keywordid, '(Unavailable)') AS costkeywordid,
387    source AS origsource,
388    medium AS origmedium,
389    campaign AS origcampaign,
390    keyword AS origkeyword,
391    adcontent AS origadcontent,
392    adgroup AS origadgroup,
393    placement AS origplacement,
394    creative AS origcreative
395    FROM costs
396    WHERE date >= '2019-02-01'::date - INTERVAL '30 days'
397    ORDER BY
398      -- Sorting by date ensures that we use the latest values available
399      date DESC,
400      COALESCE(adid, '(Unavailable)'),
401      COALESCE(accountid, '(Unavailable)'),
402      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(source), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
403      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(medium), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
404      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaign), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
405      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keyword), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
406      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adcontent), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
407      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adgroup), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
408      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(placement), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
409      COALESCE(REGEXP_REPLACE(REPLACE(LOWER(creative), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)'),
410      source, medium, campaign, keyword, adcontent, adgroup, placement, creative
411), costdims_source AS (
412  SELECT DISTINCT ON (
413    costsource
414  )
415    costsource,
416    origsource
417    FROM costdims
418    ORDER BY
419      costsource, origsource
420), costdims_source_medium AS (
421  SELECT DISTINCT ON (
422    costsource,
423    costmedium
424  )
425    costsource,
426    costmedium,
427    origsource,
428    origmedium
429    FROM costdims
430    ORDER BY
431      costsource,
432      costmedium,
433      origsource,
434      origmedium
435), costdims_source_medium_campaign AS (
436  SELECT DISTINCT ON (
437    costsource,
438    costmedium,
439    costcampaign
440  )
441    costsource,
442    costmedium,
443    costcampaign,
444    origsource,
445    origmedium,
446    origcampaign
447    FROM costdims
448    ORDER BY
449      costsource,
450      costmedium,
451      costcampaign,
452      origsource,
453      origmedium,
454      origcampaign
455), costdims_source_medium_campaign_keyword AS (
456  SELECT DISTINCT ON (
457    costsource,
458    costmedium,
459    costcampaign,
460    costkeyword
461  )
462    costsource,
463    costmedium,
464    costcampaign,
465    costkeyword,
466    origsource,
467    origmedium,
468    origcampaign,
469    origkeyword
470    FROM costdims
471    ORDER BY
472      costsource,
473      costmedium,
474      costcampaign,
475      costkeyword,
476      origsource,
477      origmedium,
478      origcampaign,
479      origkeyword
480), costdims_source_medium_campaign_keyword_adcontent AS (
481  SELECT DISTINCT ON (
482    costsource,
483    costmedium,
484    costcampaign,
485    costkeyword,
486    costadcontent
487  )
488    costsource,
489    costmedium,
490    costcampaign,
491    costkeyword,
492    costadcontent,
493    origsource,
494    origmedium,
495    origcampaign,
496    origkeyword,
497    origadcontent
498    FROM costdims
499    ORDER BY
500      costsource,
501      costmedium,
502      costcampaign,
503      costkeyword,
504      costadcontent,
505      origsource,
506      origmedium,
507      origcampaign,
508      origkeyword,
509      origadcontent
510), costdims_source_medium_campaign_keyword_adcontent_adgroup AS (
511  SELECT DISTINCT ON (
512    costsource,
513    costmedium,
514    costcampaign,
515    costkeyword,
516    costadcontent,
517    costadgroup
518  )
519    costsource,
520    costmedium,
521    costcampaign,
522    costkeyword,
523    costadcontent,
524    costadgroup,
525    origsource,
526    origmedium,
527    origcampaign,
528    origkeyword,
529    origadcontent,
530    origadgroup
531    FROM costdims
532    ORDER BY
533      costsource,
534      costmedium,
535      costcampaign,
536      costkeyword,
537      costadcontent,
538      costadgroup,
539      origsource,
540      origmedium,
541      origcampaign,
542      origkeyword,
543      origadcontent
544), costdims_by_adid AS (
545  SELECT DISTINCT ON (
546    costadid
547  )
548    costadid,
549    costaccountid,
550    costadgroupid,
551    costsource,
552    costmedium,
553    costcampaign,
554    costkeyword,
555    costadcontent,
556    costadgroup,
557    origsource,
558    origmedium,
559    origcampaign,
560    origkeyword,
561    origadcontent,
562    origadgroup
563  FROM costdims
564  ORDER BY
565    costadid,
566    costaccountid,
567    costsource,
568    costmedium,
569    costcampaign,
570    costkeyword,
571    costadcontent,
572    costadgroup,
573    origsource,
574    origmedium,
575    origcampaign,
576    origkeyword,
577    origadcontent,
578    origadgroup
579), costdims_by_adid_keyword AS (
580  SELECT DISTINCT ON (
581    costadid,
582    costkeyword
583  )
584    costadid,
585    costkeyword,
586    costkeywordid
587  FROM costdims
588  ORDER BY
589    costadid,
590    costkeyword,
591    costkeywordid
592), attributions_with_fixed_strings AS (
593  SELECT
594    date,
595    type,
596    TRIM(REGEXP_REPLACE(sourcepath, '^CLICK:|:CLICK$|^IMPRESSION:|:IMPRESSION$', '')) AS sourcepath,
597    TRIM(REGEXP_REPLACE(mediumpath, '^CLICK:|:CLICK$|^IMPRESSION:|:IMPRESSION$', '')) AS mediumpath,
598    TRIM(REGEXP_REPLACE(campaignpath, '^CLICK:|:CLICK$|^IMPRESSION:|:IMPRESSION$', '')) AS campaignpath,
599    TRIM(REGEXP_REPLACE(keywordpath, '^CLICK:|:CLICK$|^IMPRESSION:|:IMPRESSION$', '')) AS keywordpath,
600    TRIM(REGEXP_REPLACE(adwordsadcontentpath, '^CLICK:|:CLICK$|^IMPRESSION:|:IMPRESSION$', '')) AS adwordsadcontentpath,
601    TRIM(REGEXP_REPLACE(adwordsadgrouppath, '^CLICK:|:CLICK$|^IMPRESSION:|:IMPRESSION$', '')) AS adwordsadgrouppath,
602    TRIM(REGEXP_REPLACE(adwordscreativeidpath, '^CLICK:|:CLICK$|^IMPRESSION:|:IMPRESSION$', '')) AS adwordscreativeidpath,
603    placementpath,
604    creativepath,
605    first_touch,
606    last_touch,
607    dc_last_touch,
608    linear_touch,
609    markov,
610    first_touch_value,
611    last_touch_value,
612    dc_last_touch_value,
613    linear_touch_value,
614    markov_value,
615    clicks,
616    impressions
617  FROM attributions
618), attributions_with_extracted_productgroupnames AS (
619    SELECT
620    date,
621    type,
622    sourcepath,
623    mediumpath,
624    campaignpath,
625    keywordpath,
626    adwordsadcontentpath,
627    adwordsadgrouppath,
628    adwordscreativeidpath,
629    COALESCE(extract_product_type('product_type_l1==', keywordpath), extract_product_type('product type l1==', keywordpath), '(Unavailable)') AS productgroup,
630    COALESCE(extract_product_type('product_type_l2==', keywordpath), extract_product_type('product type l2==', keywordpath), '(Unavailable)') AS productgroupl2,
631    placementpath,
632    creativepath,
633    first_touch,
634    last_touch,
635    dc_last_touch,
636    linear_touch,
637    markov,
638    first_touch_value,
639    last_touch_value,
640    dc_last_touch_value,
641    linear_touch_value,
642    markov_value,
643    clicks,
644    impressions
645	FROM attributions_with_fixed_strings
646), attributions_with_processed_keyword_and_adwordscontent AS (
647    SELECT
648    date,
649    type,
650    sourcepath,
651    mediumpath,
652    campaignpath,
653    CASE
654        WHEN keywordpath = '(automatic matching)' THEN 'AutomaticContent'
655        WHEN keywordpath = '(Remarketing/Content targeting)' OR productgroup  <> '(Unavailable)' THEN NULL
656        ELSE keywordpath END AS keywordpath,
657    -- set '(Unavailable)' to adwordsadcontentpath to make it match with adcontent from costs table when keywordpath is (Remarketing/Content targeting)
658    CASE WHEN sourcepath = 'bing' THEN '(Unavailable)'
659         WHEN keywordpath = '(Remarketing/Content targeting)' THEN '(Unavailable)'
660         ELSE replace_null_values(adwordsadcontentpath) END AS adwordsadcontentpath,
661    adwordsadgrouppath,
662    adwordscreativeidpath,
663    productgroup,
664    productgroupl2,
665    placementpath,
666    creativepath,
667    first_touch,
668    last_touch,
669    dc_last_touch,
670    linear_touch,
671    markov,
672    first_touch_value,
673    last_touch_value,
674    dc_last_touch_value,
675    linear_touch_value,
676    markov_value,
677    clicks,
678    impressions
679	FROM attributions_with_extracted_productgroupnames
680), renamedattrsources AS (
681  SELECT
682    date,
683    type,
684    COALESCE(
685      channel_rename.toname,
686      costdims_source_medium_campaign_keyword_adcontent_adgroup.origsource,
687      costdims.origsource,
688      costdims_source_medium_campaign_keyword_adcontent.origsource,
689      costdims_source_medium_campaign_keyword.origsource,
690      costdims_source_medium_campaign.origsource,
691      costdims_source_medium.origsource,
692      costdims_source.origsource,
693      sourcepath
694    ) AS renamedsource,
695    COALESCE(
696      costdims.origsource,
697      costdims_source_medium_campaign_keyword_adcontent_adgroup.origsource,
698      costdims_source_medium_campaign_keyword_adcontent.origsource,
699      costdims_source_medium_campaign_keyword.origsource,
700      costdims_source_medium_campaign.origsource,
701      costdims_source_medium.origsource,
702      costdims_source.origsource,
703      sourcepath
704    ) AS sourcepath,
705    COALESCE(
706      costdims.origmedium,
707      costdims_source_medium_campaign_keyword_adcontent_adgroup.origmedium,
708      costdims_source_medium_campaign_keyword_adcontent.origmedium,
709      costdims_source_medium_campaign_keyword.origmedium,
710      costdims_source_medium_campaign.origmedium,
711      costdims_source_medium.origmedium,
712      mediumpath
713    ) AS mediumpath,
714    COALESCE(
715      costdims.origcampaign,
716      costdims_source_medium_campaign_keyword_adcontent_adgroup.origcampaign,
717      costdims_source_medium_campaign_keyword_adcontent.origcampaign,
718      costdims_source_medium_campaign_keyword.origcampaign,
719      costdims_source_medium_campaign.origcampaign,
720      campaignpath
721    ) AS campaignpath,
722    COALESCE(
723      costdims.origkeyword,
724      costdims_source_medium_campaign_keyword_adcontent_adgroup.origkeyword,
725      costdims_source_medium_campaign_keyword_adcontent.origkeyword,
726      costdims_source_medium_campaign_keyword.origkeyword,
727      keywordpath,
728      '(Unavailable)'
729    ) AS keywordpath,
730    COALESCE(
731      costdims_by_adid.origadcontent,
732      costdims.origadcontent,
733      costdims_source_medium_campaign_keyword_adcontent_adgroup.origadcontent,
734      costdims_source_medium_campaign_keyword_adcontent.origadcontent,
735      adwordsadcontentpath
736    ) AS adwordsadcontentpath,
737    COALESCE(
738      costdims_by_adid.origadgroup,
739      costdims.origadgroup,
740      costdims_source_medium_campaign_keyword_adcontent_adgroup.origadgroup,
741      adwordsadgrouppath
742    ) AS adwordsadgrouppath,
743    COALESCE(
744      costdims.origplacement,
745      placementpath
746    ) AS placementpath,
747    adwordscreativeidpath,
748    costdims_by_adid.costaccountid AS accountidpath,
749    costdims_by_adid.costadgroupid AS adgroupidpath,
750    costdims_by_adid_keyword.costkeywordid AS keywordidpath,
751    COALESCE(
752      costdims.origcreative,
753      creativepath
754    ) AS creativepath,
755    productgroup,
756    productgroupl2,
757    first_touch,
758    last_touch,
759    dc_last_touch,
760    linear_touch,
761    markov,
762    first_touch_value,
763    last_touch_value,
764    dc_last_touch_value,
765    linear_touch_value,
766    markov_value,
767    clicks,
768    impressions
769  FROM attributions_with_processed_keyword_and_adwordscontent
770    LEFT OUTER JOIN channel_rename
771      ON ((not channel_rename.is_regex AND sourcepath = channel_rename.fromname) OR
772          (channel_rename.is_regex AND sourcepath ~ channel_rename.fromname))
773      AND (channel_rename.limit_renaming_to_column_name = 'source' OR channel_rename.limit_renaming_to_column_name IS NULL)
774    LEFT OUTER JOIN costdims_by_adid
775      ON costdims_by_adid.costadid = COALESCE(adwordscreativeidpath, '(Unavailable)')
776    LEFT OUTER JOIN costdims_by_adid_keyword
777      ON costdims_by_adid_keyword.costadid = COALESCE(adwordscreativeidpath, '(Unavailable)')
778        AND costdims_by_adid_keyword.costkeyword = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keywordpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
779    LEFT OUTER JOIN costdims_source
780      ON costdims_source.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(sourcepath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
781    LEFT OUTER JOIN costdims_source_medium
782      ON costdims_source_medium.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(sourcepath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
783        AND costdims_source_medium.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(mediumpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
784    LEFT OUTER JOIN costdims_source_medium_campaign
785      ON costdims_source_medium_campaign.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(sourcepath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
786        AND costdims_source_medium_campaign.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(mediumpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
787        AND costdims_source_medium_campaign.costcampaign = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaignpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
788    LEFT OUTER JOIN costdims_source_medium_campaign_keyword
789      ON costdims_source_medium_campaign_keyword.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(sourcepath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
790        AND costdims_source_medium_campaign_keyword.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(mediumpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
791        AND costdims_source_medium_campaign_keyword.costcampaign = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaignpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
792        AND costdims_source_medium_campaign_keyword.costkeyword = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keywordpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
793    LEFT OUTER JOIN costdims_source_medium_campaign_keyword_adcontent
794      ON costdims_source_medium_campaign_keyword_adcontent.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(sourcepath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
795        AND costdims_source_medium_campaign_keyword_adcontent.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(mediumpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
796        AND costdims_source_medium_campaign_keyword_adcontent.costcampaign = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaignpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
797        AND costdims_source_medium_campaign_keyword_adcontent.costkeyword = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keywordpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
798        AND costdims_source_medium_campaign_keyword_adcontent.costadcontent = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adwordsadcontentpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
799    LEFT OUTER JOIN costdims_source_medium_campaign_keyword_adcontent_adgroup
800      ON costdims_source_medium_campaign_keyword_adcontent_adgroup.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(sourcepath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
801        AND costdims_source_medium_campaign_keyword_adcontent_adgroup.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(mediumpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
802        AND costdims_source_medium_campaign_keyword_adcontent_adgroup.costcampaign = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaignpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
803        AND costdims_source_medium_campaign_keyword_adcontent_adgroup.costkeyword = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keywordpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
804        AND costdims_source_medium_campaign_keyword_adcontent_adgroup.costadcontent = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adwordsadcontentpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
805        AND costdims_source_medium_campaign_keyword_adcontent_adgroup.costadgroup = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adwordsadgrouppath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
806    LEFT OUTER JOIN costdims
807      ON costdims.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(sourcepath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
808        AND costdims.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(mediumpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
809        AND costdims.costcampaign = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaignpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
810        AND costdims.costkeyword = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keywordpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
811        AND costdims.costadcontent = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adwordsadcontentpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
812        AND costdims.costadgroup = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(adwordsadgrouppath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
813        AND costdims.costplacement = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(placementpath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
814        AND costdims.costcreative = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(creativepath), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
815  WHERE markov > 0 OR linear_touch > 0 OR dc_last_touch > 0
816), renamedattrmediums AS (
817    SELECT *, COALESCE(channel_rename.toname, mediumpath) as renamedattrrmedium
818          FROM renamedattrsources
819          LEFT OUTER JOIN channel_rename
820          ON ((not channel_rename.is_regex AND mediumpath = channel_rename.fromname) OR
821              (channel_rename.is_regex AND mediumpath ~ channel_rename.fromname))
822            AND (channel_rename.limit_renaming_to_column_name = 'medium' OR channel_rename.limit_renaming_to_column_name IS NULL)
823), renamedattrcampaigns AS (
824  SELECT *, COALESCE(channel_rename.toname, campaignpath) AS renamedcampaign
825  FROM channel_rename
826    RIGHT OUTER JOIN renamedattrmediums
827      ON ((not channel_rename.is_regex AND campaignpath = channel_rename.fromname) OR
828          (channel_rename.is_regex AND campaignpath ~ channel_rename.fromname))
829      AND (channel_rename.limit_renaming_to_column_name = 'campaign' OR channel_rename.limit_renaming_to_column_name IS NULL)
830), grouped_attributions AS (
831  SELECT
832    date AS attrdate,
833    type AS attrtype,
834    renamedsource AS attrsource,
835    renamedattrrmedium AS attrmedium,
836    renamedcampaign AS attrcampaign,
837    CASE
838      WHEN keywordpath = '(unavailable)' THEN '(Unavailable)'
839      ELSE keywordpath END AS attrkeyword,
840    adwordsadcontentpath as attradcontent,
841    adwordsadgrouppath as attradgroup,
842    adwordscreativeidpath AS attradid,
843    accountidpath AS attraccountid,
844    adgroupidpath AS attradgroupid,
845    keywordidpath AS attrkeywordid,
846    placementpath AS attrplacement,
847    creativepath AS attrcreative,
848    productgroup AS attrproductgroup,
849    productgroupl2 AS attrproductgroupl2,
850    SUM(first_touch) AS first_touch,
851    SUM(last_touch) AS last_touch,
852    SUM(dc_last_touch) AS dc_last_touch,
853    SUM(linear_touch) AS linear_touch,
854    SUM(markov) AS markov,
855    SUM(first_touch_value) AS first_touch_value,
856    SUM(last_touch_value) AS last_touch_value,
857    SUM(dc_last_touch_value) AS dc_last_touch_value,
858    SUM(linear_touch_value) AS linear_touch_value,
859    SUM(markov_value) AS markov_value,
860    SUM(clicks) as clickstoconversion,
861    SUM(impressions) AS impressionstoconversion
862  FROM renamedattrcampaigns
863  GROUP BY
864    date,
865    type,
866    renamedsource,
867    renamedattrrmedium,
868    renamedcampaign,
869    CASE
870      WHEN keywordpath = '(unavailable)' THEN '(Unavailable)'
871      ELSE keywordpath END,
872    adwordsadcontentpath,
873    adwordsadgrouppath,
874    adwordscreativeidpath,
875    accountidpath,
876    adgroupidpath,
877    keywordidpath,
878    placementpath,
879    creativepath,
880    productgroup,
881    productgroupl2
882), renamedgroupingssource AS (
883  SELECT
884    DISTINCT ON (
885      COALESCE(channel_rename.toname, costdims_source_medium_campaign_keyword.origsource, costdims_source_medium_campaign.origsource, costdims_source_medium.origsource, costdims_source.origsource, source),
886      COALESCE(costdims_source_medium_campaign_keyword.origmedium, costdims_source_medium_campaign.origmedium, costdims_source_medium.origmedium, medium),
887      COALESCE(costdims_source_medium_campaign_keyword.origcampaign, costdims_source_medium_campaign.origcampaign, campaign),
888      COALESCE(costdims_source_medium_campaign_keyword.origkeyword, keyword, '(Unavailable)')
889    )
890    channelgrouping,
891    COALESCE(channel_rename.toname, costdims_source_medium_campaign_keyword.origsource, costdims_source_medium_campaign.origsource, costdims_source_medium.origsource, costdims_source.origsource, source) AS source,
892    COALESCE(costdims_source_medium_campaign_keyword.origmedium, costdims_source_medium_campaign.origmedium, costdims_source_medium.origmedium, medium) AS medium,
893    COALESCE(costdims_source_medium_campaign_keyword.origcampaign, costdims_source_medium_campaign.origcampaign, campaign) AS campaign,
894    COALESCE(costdims_source_medium_campaign_keyword.origkeyword, keyword, '(Unavailable)') AS keyword
895  FROM channel_grouping
896  LEFT OUTER JOIN channel_rename
897    ON ((not channel_rename.is_regex AND source = channel_rename.fromname) OR
898        (channel_rename.is_regex AND source ~ channel_rename.fromname))
899    AND (channel_rename.limit_renaming_to_column_name = 'source' OR channel_rename.limit_renaming_to_column_name IS NULL)
900  LEFT OUTER JOIN costdims_source
901    ON costdims_source.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(source), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
902  LEFT OUTER JOIN costdims_source_medium
903    ON costdims_source_medium.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(source), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
904      AND costdims_source_medium.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(medium), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
905  LEFT OUTER JOIN costdims_source_medium_campaign
906    ON costdims_source_medium_campaign.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(source), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
907      AND costdims_source_medium_campaign.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(medium), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
908      AND costdims_source_medium_campaign.costcampaign = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaign), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
909  LEFT OUTER JOIN costdims_source_medium_campaign_keyword
910    ON costdims_source_medium_campaign_keyword.costsource = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(source), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
911      AND costdims_source_medium_campaign_keyword.costmedium = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(medium), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
912      AND costdims_source_medium_campaign_keyword.costcampaign = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(campaign), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
913      AND costdims_source_medium_campaign_keyword.costkeyword = COALESCE(REGEXP_REPLACE(REPLACE(LOWER(keyword), '_', ' '), '\s+', ' ', 'g'), '(Unavailable)')
914  ORDER BY
915    COALESCE(channel_rename.toname, costdims_source_medium_campaign_keyword.origsource, costdims_source_medium_campaign.origsource, costdims_source_medium.origsource, costdims_source.origsource, source),
916    COALESCE(costdims_source_medium_campaign_keyword.origmedium, costdims_source_medium_campaign.origmedium, costdims_source_medium.origmedium, medium),
917    COALESCE(costdims_source_medium_campaign_keyword.origcampaign, costdims_source_medium_campaign.origcampaign, campaign),
918    COALESCE(costdims_source_medium_campaign_keyword.origkeyword, keyword, '(Unavailable)')
919), renamedgroupingcampaigns AS (
920  SELECT
921    DISTINCT ON (
922      source,
923      medium,
924      COALESCE(channel_rename.toname, campaign),
925      keyword
926    )
927    channelgrouping,
928    source,
929    medium,
930    COALESCE(channel_rename.toname, campaign) AS campaign,
931    keyword
932  FROM renamedgroupingssource
933  LEFT OUTER JOIN channel_rename
934    ON ((not channel_rename.is_regex AND campaign = channel_rename.fromname) OR
935        (channel_rename.is_regex AND campaign ~ channel_rename.fromname))
936    AND (channel_rename.limit_renaming_to_column_name = 'campaign' OR channel_rename.limit_renaming_to_column_name IS NULL)
937  ORDER BY
938    source,
939    medium,
940    COALESCE(channel_rename.toname, campaign),
941    keyword
942), groupings AS (
943    SELECT DISTINCT
944        MIN(channelgrouping) AS channelgrouping,
945        source,
946        -- replace(lower(medium), '_', ' ') AS medium,
947        campaign,
948        COALESCE(keyword, '(Unavailable)') AS keyword
949    FROM renamedgroupingcampaigns
950    GROUP BY
951        source,
952        -- replace(lower(medium), '_', ' '),
953        campaign,
954        COALESCE(keyword, '(Unavailable)')
955)
956SELECT
957    -- dimensions to match
958    COALESCE(costdate, attrdate) AS date,
959    COALESCE(costtype, attrtype) AS type,
960    COALESCE(costsource, attrsource) AS source,
961    COALESCE(costmedium, attrmedium) AS medium,
962    COALESCE(costcampaign, attrcampaign) AS campaign,
963    COALESCE(costkeyword, attrkeyword) AS keyword,
964    CASE
965        WHEN MIN(costmatchtype) = 'Exact' THEN '[' || COALESCE(costkeyword, attrkeyword) || ']'
966        WHEN MIN(costmatchtype) = 'Phrase' THEN '"' || COALESCE(costkeyword, attrkeyword) || '"'
967        ELSE COALESCE(costkeyword, attrkeyword)
968    END AS adwordskeyword,
969    COALESCE(costproductgroup, attrproductgroup) as productgroup,
970    COALESCE(costproductgroupl2, attrproductgroupl2) as productgroupl2,
971    COALESCE(costadcontent, attradcontent) AS adcontent,
972    COALESCE(costadgroup, attradgroup) AS adgroup,
973    COALESCE(costadid, attradid) AS adid,
974    -- backwards compatible, we used to get the `adid` from the `costs` table
975    1 AS countadid,
976    COALESCE(costplacement, attrplacement) AS placement,
977    COALESCE(costcreative, attrcreative) AS creative,
978    CASE
979        WHEN COALESCE(costproductgroupl2, attrproductgroupl2) = '(Unavailable)' THEN COALESCE(costadgroup, attradgroup)
980        ELSE COALESCE(costproductgroupl2, attrproductgroupl2)
981    END AS ad_product_group,
982
983    -- dimensions from costs
984    MIN(costlabel) AS label,
985    SUM(costcountlabel) AS countlabel,
986    MIN(costmatchtype) AS matchtype,
987    SUM(costcountmatchtype) AS countmatchtype,
988    MIN(coststatus) AS status,
989    SUM(costcountstatus) AS countstatus,
990    MIN(costheadline2) AS headline2,
991    SUM(costcountheadline2) AS countheadline2,
992    MIN(costdisplayurl) AS displayurl,
993    SUM(costcountdisplayurl) AS countdisplayurl,
994    MIN(costdestinationurl) AS destinationurl,
995    SUM(costcountdestinationurl) AS countdestinationurl,
996    SUM(costcountproductgroup) AS countproductgroup,
997    SUM(costcountproductgroupl2) AS countproductgroupl2,
998    COALESCE(MIN(costadgroupid), attradgroupid) AS adgroupid,
999    SUM(costcountadgroupid) AS countadgroupid,
1000    COALESCE(MIN(costkeywordid), attrkeywordid) AS keywordid,
1001    SUM(costcountkeywordid) AS countkeywordid,
1002    MIN(costcampaignid) AS campaignid,
1003    SUM(costcountcampaignid) AS countcampaignid,
1004
1005    COALESCE(costaccountid, attraccountid) AS accountid,
1006    -- backwards compatible, we used to get the `accountid` from the `costs` table
1007    1 AS countaccountid,
1008
1009    MIN(costadwordsnetwork) AS adwordsnetwork,
1010    SUM(costcountadwordsnetwork) AS countadwordsnetwork,
1011    MIN(costbiddingstrategytype) AS biddingstrategytype,
1012    SUM(costcountbiddingstrategytype) AS countbiddingstrategytype,
1013    MIN(costdevice) AS device,
1014    SUM(costcountdevice) AS countdevice,
1015
1016    -- dimensions from other tables
1017    COALESCE(MIN(groupings.channelgrouping), 'Other') AS channelgrouping,
1018
1019    -- metrics from costs
1020    COALESCE(SUM(totalcost), 0) AS totalcost,
1021    COALESCE(SUM(reportedgoalcompletions), 0) AS reportedgoalcompletions,
1022    COALESCE(SUM(reportedgoalallcompletions), 0) AS reportedgoalallcompletions,
1023    COALESCE(SUM(clicks), 0) AS clicks,
1024    COALESCE(SUM(impressions), 0) AS impressions,
1025    SUM(viewableimpressions) AS viewableimpressions,
1026    SUM(exactmatchis * impressions)/NULLIF(SUM(impressions), 0) AS exactmatchis,
1027    SUM(searchis * impressions)/NULLIF(SUM(impressions), 0) AS searchis,
1028    SUM(searchlostisrank * impressions)/NULLIF(SUM(impressions), 0) AS searchlostisrank,
1029    SUM(qualityscore * impressions)/NULLIF(SUM(impressions), 0) AS qualityscore,
1030    SUM(landingpageexp * impressions)/NULLIF(SUM(impressions), 0) AS landingpageexp,
1031    SUM(expectedctr * impressions)/NULLIF(SUM(impressions), 0) AS expectedctr,
1032    SUM(bouncerate * clicks)/NULLIF(SUM(clicks), 0) AS bouncerate,
1033    SUM(avgposition * impressions)/NULLIF(SUM(impressions), 0) AS avgposition,
1034    SUM(adrelevance * impressions)/NULLIF(SUM(impressions), 0) AS adrelevance,
1035    SUM(contentlostisbudget * impressions)/NULLIF(SUM(impressions), 0) AS contentlostisbudget,
1036    SUM(searchlostisbudget * impressions)/NULLIF(SUM(impressions), 0) AS searchlostisbudget,
1037    SUM(contentis * impressions)/NULLIF(SUM(impressions), 0) AS contentis,
1038    SUM(contentlostisrank * impressions)/NULLIF(SUM(impressions), 0) AS contentlostisrank,
1039    SUM(maxcpc * impressions)/NULLIF(SUM(impressions), 0) AS maxcpc,
1040    SUM(totalcost)/NULLIF(SUM(clicks), 0) AS avgcpc,
1041    SUM(firstpagecpc * impressions)/NULLIF(SUM(impressions), 0) AS firstpagecpc,
1042    SUM(firstpositioncpc * impressions)/NULLIF(SUM(impressions), 0) AS firstpositioncpc,
1043    SUM(topofpagecpc * impressions)/NULLIF(SUM(impressions), 0) AS topofpagecpc,
1044    -- metrics from attributions
1045    SUM(first_touch) AS first_touch,
1046    SUM(last_touch) AS last_touch,
1047    SUM(dc_last_touch) AS dc_last_touch,
1048    SUM(linear_touch) AS linear_touch,
1049    COALESCE(SUM(markov), 0) AS markov,
1050    SUM(first_touch_value) AS first_touch_value,
1051    SUM(last_touch_value) AS last_touch_value,
1052    SUM(dc_last_touch_value) AS dc_last_touch_value,
1053    SUM(linear_touch_value) AS linear_touch_value,
1054    SUM(markov_value) AS markov_value,
1055    COALESCE(SUM(clickstoconversion), 0) as clickstoconversion,
1056    COALESCE(SUM(impressionstoconversion), 0) AS impressionstoconversion
1057  FROM grouped_costs FULL OUTER JOIN grouped_attributions
1058    ON costdate = attrdate
1059      AND costproductgroup = attrproductgroup
1060      AND costproductgroupl2 = attrproductgroupl2
1061      AND costtype = attrtype
1062      AND costsource = attrsource
1063      AND costmedium = attrmedium
1064      AND costcampaign = attrcampaign
1065      AND costkeyword = attrkeyword
1066      AND costadcontent = attradcontent
1067      AND costadgroup = attradgroup
1068      AND costplacement = attrplacement
1069      AND costcreative = attrcreative
1070      AND costaccountid = attraccountid
1071      AND costadid = attradid
1072  LEFT JOIN groupings
1073    ON LOWER(groupings.source) = LOWER(COALESCE(costsource, attrsource, '(Unavailable)'))
1074        -- AND groupings.medium = LOWER(COALESCE(costmedium, attrmedium, '(Unavailable)'))
1075        AND LOWER(groupings.campaign) = LOWER(COALESCE(costcampaign, attrcampaign, '(Unavailable)'))
1076        AND LOWER(groupings.keyword) = LOWER(COALESCE(costkeyword, attrkeyword, '(Unavailable)'))
1077  GROUP BY
1078    COALESCE(costdate, attrdate),
1079    COALESCE(costproductgroup, attrproductgroup),
1080    COALESCE(costproductgroupl2, attrproductgroupl2),
1081    COALESCE(costtype, attrtype),
1082    COALESCE(costsource, attrsource),
1083    COALESCE(costmedium, attrmedium),
1084    COALESCE(costcampaign, attrcampaign),
1085    COALESCE(costkeyword, attrkeyword),
1086    attrkeywordid,
1087    COALESCE(costadgroup, attradgroup),
1088    attradgroupid,
1089    COALESCE(costadid, attradid),
1090    COALESCE(costadcontent, attradcontent),
1091    COALESCE(costplacement, attrplacement),
1092    COALESCE(costcreative, attrcreative),
1093    COALESCE(costaccountid, attraccountid);
1094
1095
1096-- CREATE TABLE IF NOT EXISTS WITH THE BASIC COLUMNS
1097CREATE TABLE IF NOT EXISTS ais_attributions_and_costs (
1098  date TIMESTAMP WITH TIME ZONE,
1099  type TEXT,
1100  source TEXT,
1101  medium TEXT,
1102  campaign TEXT
1103) PARTITION BY RANGE (date);
1104
1105-- PARTITIONS by date
1106SELECT create_partitions_by_date('ais_attributions_and_costs', 'aac');
1107
1108-- IF TABLE ALREADY EXISTS AND ANY COLUMNS NOT IN TABLE THEN ADD COLUMNS
1109ALTER TABLE ais_attributions_and_costs
1110ADD COLUMN IF NOT EXISTS date TIMESTAMP WITH TIME ZONE,
1111ADD COLUMN IF NOT EXISTS type TEXT,
1112ADD COLUMN IF NOT EXISTS source TEXT,
1113ADD COLUMN IF NOT EXISTS medium TEXT,
1114ADD COLUMN IF NOT EXISTS campaign TEXT,
1115ADD COLUMN IF NOT EXISTS keyword TEXT,
1116ADD COLUMN IF NOT EXISTS adwordskeyword TEXT,
1117ADD COLUMN IF NOT EXISTS adcontent TEXT,
1118ADD COLUMN IF NOT EXISTS placement TEXT,
1119ADD COLUMN IF NOT EXISTS creative TEXT,
1120ADD COLUMN IF NOT EXISTS adgroup TEXT,
1121ADD COLUMN IF NOT EXISTS label TEXT,
1122ADD COLUMN IF NOT EXISTS countlabel NUMERIC,
1123ADD COLUMN IF NOT EXISTS matchtype TEXT,
1124ADD COLUMN IF NOT EXISTS countmatchtype NUMERIC,
1125ADD COLUMN IF NOT EXISTS status TEXT,
1126ADD COLUMN IF NOT EXISTS countstatus NUMERIC,
1127ADD COLUMN IF NOT EXISTS headline2 TEXT,
1128ADD COLUMN IF NOT EXISTS countheadline2 NUMERIC,
1129ADD COLUMN IF NOT EXISTS displayurl TEXT,
1130ADD COLUMN IF NOT EXISTS countdisplayurl NUMERIC,
1131ADD COLUMN IF NOT EXISTS destinationurl TEXT,
1132ADD COLUMN IF NOT EXISTS countdestinationurl NUMERIC,
1133ADD COLUMN IF NOT EXISTS channelgrouping TEXT,
1134ADD COLUMN IF NOT EXISTS totalcost REAL,
1135ADD COLUMN IF NOT EXISTS reportedgoalcompletions REAL,
1136ADD COLUMN IF NOT EXISTS reportedgoalallcompletions REAL,
1137ADD COLUMN IF NOT EXISTS clicks REAL,
1138ADD COLUMN IF NOT EXISTS impressions REAL,
1139ADD COLUMN IF NOT EXISTS viewableimpressions REAL,
1140ADD COLUMN IF NOT EXISTS exactmatchis REAL,
1141ADD COLUMN IF NOT EXISTS searchis REAL,
1142ADD COLUMN IF NOT EXISTS searchlostisrank REAL,
1143ADD COLUMN IF NOT EXISTS qualityscore DOUBLE PRECISION,
1144ADD COLUMN IF NOT EXISTS landingpageexp DOUBLE PRECISION,
1145ADD COLUMN IF NOT EXISTS expectedctr DOUBLE PRECISION,
1146ADD COLUMN IF NOT EXISTS bouncerate REAL,
1147ADD COLUMN IF NOT EXISTS avgposition REAL,
1148ADD COLUMN IF NOT EXISTS adrelevance DOUBLE PRECISION,
1149ADD COLUMN IF NOT EXISTS contentlostisbudget REAL,
1150ADD COLUMN IF NOT EXISTS searchlostisbudget REAL,
1151ADD COLUMN IF NOT EXISTS contentis REAL,
1152ADD COLUMN IF NOT EXISTS contentlostisrank REAL,
1153ADD COLUMN IF NOT EXISTS maxcpc REAL,
1154ADD COLUMN IF NOT EXISTS avgcpc REAL,
1155ADD COLUMN IF NOT EXISTS firstpagecpc REAL,
1156ADD COLUMN IF NOT EXISTS firstpositioncpc REAL,
1157ADD COLUMN IF NOT EXISTS topofpagecpc REAL,
1158ADD COLUMN IF NOT EXISTS first_touch REAL,
1159ADD COLUMN IF NOT EXISTS last_touch REAL,
1160ADD COLUMN IF NOT EXISTS dc_last_touch REAL,
1161ADD COLUMN IF NOT EXISTS linear_touch REAL,
1162ADD COLUMN IF NOT EXISTS markov REAL,
1163ADD COLUMN IF NOT EXISTS first_touch_value REAL,
1164ADD COLUMN IF NOT EXISTS last_touch_value REAL,
1165ADD COLUMN IF NOT EXISTS dc_last_touch_value REAL,
1166ADD COLUMN IF NOT EXISTS linear_touch_value REAL,
1167ADD COLUMN IF NOT EXISTS markov_value REAL,
1168ADD COLUMN IF NOT EXISTS clickstoconversion REAL,
1169ADD COLUMN IF NOT EXISTS impressionstoconversion REAL,
1170ADD COLUMN IF NOT EXISTS productgroup TEXT,
1171ADD COLUMN IF NOT EXISTS productgroupl2 TEXT,
1172ADD COLUMN IF NOT EXISTS countproductgroup REAL,
1173ADD COLUMN IF NOT EXISTS countproductgroupl2 REAL,
1174ADD COLUMN IF NOT EXISTS ad_product_group TEXT,
1175ADD COLUMN IF NOT EXISTS adid TEXT,
1176ADD COLUMN IF NOT EXISTS countadid NUMERIC,
1177ADD COLUMN IF NOT EXISTS adgroupid TEXT,
1178ADD COLUMN IF NOT EXISTS countadgroupid NUMERIC,
1179ADD COLUMN IF NOT EXISTS keywordid TEXT,
1180ADD COLUMN IF NOT EXISTS countkeywordid NUMERIC,
1181ADD COLUMN IF NOT EXISTS campaignid TEXT,
1182ADD COLUMN IF NOT EXISTS countcampaignid NUMERIC,
1183ADD COLUMN IF NOT EXISTS accountid TEXT,
1184ADD COLUMN IF NOT EXISTS countaccountid NUMERIC,
1185ADD COLUMN IF NOT EXISTS adwordsnetwork TEXT,
1186ADD COLUMN IF NOT EXISTS countadwordsnetwork NUMERIC,
1187ADD COLUMN IF NOT EXISTS biddingstrategytype TEXT,
1188ADD COLUMN IF NOT EXISTS countbiddingstrategytype NUMERIC,
1189ADD COLUMN IF NOT EXISTS device TEXT,
1190ADD COLUMN IF NOT EXISTS countdevice NUMERIC;
1191
1192-- DELETE VALUES FOR THE DAYS WE WANT TO REFRESH
1193DELETE FROM ais_attributions_and_costs
1194WHERE date >= '2019-02-01' AND DATE <= '2019-09-19';
1195
1196-- UPDATE NEEDS_REFRESH COLUMNS TO FALSE
1197UPDATE manual_costs SET needs_refresh = FALSE
1198WHERE date >= '2019-02-01' AND DATE <= '2019-09-19';
1199
1200INSERT INTO ais_attributions_and_costs (
1201  date,
1202  type,
1203  source,
1204  medium,
1205  campaign,
1206  keyword,
1207  adwordskeyword,
1208  adcontent,
1209  placement,
1210  creative,
1211  adgroup,
1212  label,
1213  countlabel,
1214  matchtype,
1215  countmatchtype,
1216  status,
1217  countstatus,
1218  headline2,
1219  countheadline2,
1220  displayurl,
1221  countdisplayurl,
1222  destinationurl,
1223  countdestinationurl,
1224  channelgrouping,
1225  productgroup,
1226  productgroupl2,
1227  countproductgroup,
1228  countproductgroupl2,
1229  ad_product_group,
1230  adid,
1231  countadid,
1232  adgroupid,
1233  countadgroupid,
1234  keywordid,
1235  countkeywordid,
1236  campaignid,
1237  countcampaignid,
1238  accountid,
1239  countaccountid,
1240  adwordsnetwork,
1241  countadwordsnetwork,
1242  biddingstrategytype,
1243  countbiddingstrategytype,
1244  device,
1245  countdevice,
1246
1247  -- metrics from costs
1248  totalcost,
1249  reportedgoalcompletions,
1250  reportedgoalallcompletions,
1251  clicks,
1252  impressions,
1253  viewableimpressions,
1254  exactmatchis,
1255  searchis,
1256  searchlostisrank,
1257  qualityscore,
1258  landingpageexp,
1259  bouncerate,
1260  avgposition,
1261  adrelevance,
1262  contentlostisbudget,
1263  searchlostisbudget,
1264  contentis,
1265  contentlostisrank,
1266  maxcpc,
1267  avgcpc,
1268  firstpagecpc,
1269  firstpositioncpc,
1270  topofpagecpc,
1271  expectedctr,
1272  -- metrics from attributions
1273  first_touch,
1274  last_touch,
1275  dc_last_touch,
1276  linear_touch,
1277  markov,
1278  first_touch_value,
1279  last_touch_value,
1280  dc_last_touch_value,
1281  linear_touch_value,
1282  markov_value,
1283  clickstoconversion,
1284  impressionstoconversion
1285) SELECT
1286  date,
1287  type,
1288  source,
1289  medium,
1290  campaign,
1291  keyword,
1292  adwordskeyword,
1293  adcontent,
1294  placement,
1295  creative,
1296  adgroup,
1297  label,
1298  countlabel,
1299  matchtype,
1300  countmatchtype,
1301  status,
1302  countstatus,
1303  headline2,
1304  countheadline2,
1305  displayurl,
1306  countdisplayurl,
1307  destinationurl,
1308  countdestinationurl,
1309  channelgrouping,
1310  productgroup,
1311  productgroupl2,
1312  countproductgroup,
1313  countproductgroupl2,
1314  ad_product_group,
1315  adid,
1316  countadid,
1317  adgroupid,
1318  countadgroupid,
1319  keywordid,
1320  countkeywordid,
1321  campaignid,
1322  countcampaignid,
1323  accountid,
1324  countaccountid,
1325  adwordsnetwork,
1326  countadwordsnetwork,
1327  biddingstrategytype,
1328  countbiddingstrategytype,
1329  device,
1330  countdevice,
1331
1332  -- metrics from costs
1333  totalcost,
1334  reportedgoalcompletions,
1335  reportedgoalallcompletions,
1336  clicks,
1337  impressions,
1338  viewableimpressions,
1339  exactmatchis,
1340  searchis,
1341  searchlostisrank,
1342  qualityscore,
1343  landingpageexp,
1344  bouncerate,
1345  avgposition,
1346  adrelevance,
1347  contentlostisbudget,
1348  searchlostisbudget,
1349  contentis,
1350  contentlostisrank,
1351  maxcpc,
1352  avgcpc,
1353  firstpagecpc,
1354  firstpositioncpc,
1355  topofpagecpc,
1356  expectedctr,
1357  -- metrics from attributions
1358  first_touch,
1359  last_touch,
1360  dc_last_touch,
1361  linear_touch,
1362  markov,
1363  first_touch_value,
1364  last_touch_value,
1365  dc_last_touch_value,
1366  linear_touch_value,
1367  markov_value,
1368  clickstoconversion,
1369  impressionstoconversion
1370FROM ais_new_attributions_and_costs;
1371
1372ALTER TABLE ais_attributions_and_costs OWNER TO admin;
1373
1374CREATE INDEX IF NOT EXISTS ais_attributions_and_costs_date_idx ON ais_attributions_and_costs (date);
1375CREATE INDEX IF NOT EXISTS ais_attributions_and_costs_type_idx ON ais_attributions_and_costs (type);
1376
1377COMMIT;
1378-- CREATE UNIQUE INDEX IF NOT EXISTS ais_attributions_and_costs_unique_idx ON ais_attributions_and_costs (date, type, source, medium, campaign, keyword, adcontent, placement, creative);