Fivetran-adwords-into-suitable-test-analysis-format.sql

with control_campaigns as (
select
    'Control' as measurement_group
  , campaign_name as base_campaign
  , date
  , cost
  , conversions
  , conversion_value
from
  fivetran.adwords.google_campaign_performance
where
  date > {test_start_date}
  and date < {test_end_date} /*or current_date if not finished yet*/
  and campaign_name not ilike '%draft_identifier%' /*The common suffix you added to all your draft campaigns*/
),
test_campaigns as (
select
    'Test' as measurement_group  
  , replace(campaign_name,draft_identifier,'') as base_campaign
  , date  
  , cost
  , conversions
  , conversion_value
from
  fivetran.adwords.google_campaign_performance
where
  date > {test_start_date}
  and date < {test_end_date} /*or current_date if not finished yet*/
  and campaign_name ilike '%draft_identifier%' /*The common suffix you added to all your draft campaigns*/
)

select
    coalesce(C.base_campaign, T.base_campaign) as base_campaign
  , coalesce(C.date, T.date) as date
  , coalesce(C.cost, 0) as ctl_cost
  , coalesce(T.cost, 0) as test_cost
  , coalesce(C.conversions, 0) as ctl_conversions
  , coalesce(T.conversions, 0) as test_conversions
from
  control_campaigns C
  full join test_campaigns T on T.base_campaign = C.base_campaign and T.date = C.date
  
/*This is assuming all your campaigns are part of the test and that you are only running one test*/
/*If that is not the case then you need add another where clause to exclude campaigns not included in the test*/