Feature Request/Question -- When to use URL vs. Criteria Ad adapter models

[kristin-bagnall]

Are you a Fivetran customer? Yes -- Question came in from Nicolas Konz (AMN Data Solutions GmbH)

Is your feature request related to a problem? Please describe. Historically using URL ad adapter, and noticed missing Dynamic Search Ads. These ads were found in the Criteria Ad adapter model

Additional context Criteria ad adapter table has ~138K records URL ad adapter table has ~60K records

• Joining multiple google ads accounts

Please indicate the level of urgency and business impact of this request Please prioritize when we have more details from Nicolas

[kristin-bagnall]

@konznic93 can you please let us know if all of the URL ad adapter records are in the criteria ad adapter table?

If so, one solution here would be to combine the ad adapter models and incorporate URL-specific info into the criteria table (will need @DylanBaker's expertise to understand if that makes sense though)

[konznic93]

@fivetran-kristin I checked and the whole part regarding URL´s & UTM´s is missing. The criteria report is based on the keywords instead of the URL data which seems to come from Google Analytics.

I am not sure it is possible to match both and keep that but they all contain campaign & ad group ids.

[DylanBaker]

@konznic93 picking this up.

My expectation was that while the number of records in each table would be different because they have different grains, all ads would be in there and the metrics would be complete.

Would you be able to confirm that for a given day, the metrics are actually different between the two reports?

select date, sum(cost), sum(impressions), sum(clicks)
from {{ url or criteria raw table }}
group by 1

If those are different, we'll need to figure out if that is an issue with the data being passed through, or a misunderstanding on our side of what the underlying report provides. As I said above, my understanding was that both should be a complete set of data, just presented differently.

Is it all Dynamic Search Ads that are missing or just some?

As you said, there isn't really a great way to combine these tables. I think the only 'combined' option is to find the missing ads and union them into the URL table from the criteria table. You'd still be missing some URLs, but it would be as close as we can get.

[konznic93]

@DylanBaker yes I can confirm that for a given day the metrics are different. Since we just tested Dynamic search ads we only had one active so we can not test that. But as I understood the Google Ads Docs Dynmaic Search Ads are only included in the criteria adapter.

But attached the Google Docs which describe where the data of Dynamic Search ads can be found. https://developers.google.com/adwords/api/docs/guides/dynamic-search-ads#reporting

[DylanBaker]

@konznic93 Understood, thanks for looking into that.

It sounds like there isn't therefore a single report that would give you URL based reporting for those it applies to, and non-URL based reporting for those that it doesn't. Does that align with your current understanding?

If that's the case, I think your best bet is to build a new model that is built on the URL ad adapter model, and then URL in any records that apply to campaigns/ad groups/ads that are not in that model from the Criteria Ad Adapter. Does that sound like it would satisfy your use case? This is definitely something we will consider putting directly into the package.

[DylanBaker]

@konznic93 I've created a new branch that attempt to solve this problem with the solution described above.

You can see it here: https://github.com/fivetran/dbt_google_ads/pull/8

It creates a new model called google_ads__combined_ad_adapter. It uses the URL report as its base but ads in any records from the criteria report where the date_day, customer_id, campaign_id, ad_group_id is missing.

Would you mind testing it out to see if it solves the problem you were having?

[leinemann]

Hi @DylanBaker & @konznic93, I do have the same issue and I think the combined model you created might solve it. What are the reasons why you didn't take this any further?
many thanks, Christoph.

[leinemann]

Hi again, I've done some more testing my end and the combined model fixes the issues that I've seen, so I'd be interested in this becoming part of the official package subject to any concerns you might be seeing with this?

[leinemann]

Hey @fivetran-joemarkiewicz , could you please share some context on why this got the wontfix label.

[fivetran-joemarkiewicz]

Hi @leinemann this got the wontfix label because we were not able to fully confirm that this approach would work an if it was of interest to other users. However, yourself and another individual have brought this ticket to my attention this week and I think it is worth readdressing on our end.

There is also some added complexity as it seems the Google_Ads API is changing the very structure of the criteria report (this will be key once Google stops supporting Adwords in the near future). I will look into this further and will let you know what our next steps will be.

[fivetran-joemarkiewicz]

Hi All,

I just wanted to provide an update that I am still looking into this and testing to see how this would best fit into the current (and future) structure of this package and the ad_reporting package. I will be OOO until next Tuesday, but will provide an update by the end of this current sprint (next Thursday).

Thanks for your patience!

[fivetran-joemarkiewicz]

@leinemann & @konznic93

After diving deeper into this request @DylanBaker and I have come to the realization that the Dynamic search ads may be automatically captured within the newer Google Ads API final_url_performance equivalent report. As Google is planning to sunset Adwords in April of 2022, we have a PR open to reflect these API and connector changes and it may make the most sense to combine these two efforts.

Therefore we are confirming if these DSA campaigns are indeed passed through the final_url_performance equivalent report in the Google Ads API. If that is the case, we will combine these two efforts. I will post back here with more details once we are able to confirm this.

[fivetran-joemarkiewicz]

@leinemann & @konznic93

We are ready to cut a breaking release of this package which allows the use of the Google Ads API via Fivetran. The latest code is now merged with master and I was wondering if either of you would be open to creating a Google Ads Google Ads API connector within Fivetran and testing the master branch with the google_ads api_source variable to see if the DSA ads show in the final url table.

Let me know if you would be open to testing this for us and if you need help setting up the Google Ads API version of the connector. Thanks!

[fivetran-joemarkiewicz]

Hi All,

Just wanted to let you know that we have cut this most recent release v0.4.0 of the [dbt_google_ads]() package for compatibility with the new Google Ads API. Since the Google Ads API does not leverage the final_url or criteria reports directly, and instead builds the final_url report from the ground up I believe the DSA ads will be included in the final models.

Feel free to try out the new Google Ads API version of the connector and upgrade your package to use the Google Ads API build and let me know if you are able to see the DSA ads.

[leinemann]

Thank you for all this and sorry about my late reply @fivetran-joemarkiewicz . Yes, we'll be upgrading to this in the next few days and let you know how it goes.

[bnealdefero]

Hi @fivetran-joemarkiewicz, appreciate the work being done here.. Been following this a bit as we too are missing dynamic search ads. Is there documentation around setting up the Google Ads API version of the connector?

[fivetran-joemarkiewicz]

Hey @bnealdefero Glad you have been following this thread! The Google Ads API functionality it seems is still feature flagged. You can get this lifted by reaching out to your Fivetran csm and asking to use the Google Ads API compatible version of the connector. From there it should be as easy as just switching the API to Google Ads when you are setting up the connector.

[fivetran-joemarkiewicz]

Hi @leinemann and @bnealdefero wanted to reach back out and see if the upgrade to the Google Ads API solved the DSA exclusion issue.

[bnealdefero]

Hi @fivetran-joemarkiewicz . Apologies for delay.

I did have a chance to switch to the Google Ads API version of the connector but still seem to be missing dsa's.

Full disclosure, hoping to hit this in more detail first of next week but up to this point I haven't had time to do anything other than pull in the latest version of this package as well as the ad_reporting package and then dbt run along with a few queries to see if we picked up the dsa's.. Looks like they are still missing, but that doesn't help this group here at all so as soon as I can provide more detail I will.

Thank you...

[fivetran-joemarkiewicz]

@bnealdefero thanks so much for testing this out and my apologies that it didn't resolve the issue.

I think it would be best to chat live during out office hours session when you have free time to look into this in more detail together. Let me know if none of the available slots for office hours work for you.

Thanks,

[fivetran-joemarkiewicz]

Hi @bnealdefero just wanted to bump the above request for office hours as I think it would be great to debug this live with you.

Thanks!

[bnealdefero]

Hi @fivetran-joemarkiewicz apologies for delay. Just scheduled something in your office hours. Looking forward to it!

[fivetran-joemarkiewicz]

Hi All,

I was able to meet with @bnealdefero to chat about where the DSA ads are being filtered out and narrowed it down to the left joins in the adapter model https://github.com/fivetran/dbt_google_ads/blob/main/models/url_google_ads/google_ads__url_ad_adapter.sql#L63-L73

We found that DSA's are not located within the stats base table, but are in the others. I will dig more into this and see if I am able to arrive at a working branch for you to test and see if the DSA can be populated in the final ad roll up table.

[fivetran-joemarkiewicz]

@bnealdefero would you be able to share the other table you found the Dynamic Search Ads within?

I was able to see they exist within the ad_history table. I know you shared a few other sources where these ads existed.

[bnealdefero]

HI @fivetran-joemarkiewicz you bet.

I was able to see them in the campaign_stats, campaign_history and ad_history tables.

Thanks..

[fivetran-joemarkiewicz]

Thanks @bnealdefero!

How are these I Dynamic Search Ads integrated into the campaign_stats table? Are DSA's integrated into the same campaigns as other ads? If so, I wonder if you would be able to choose a campaign that includes DSA's and other Ads for a sample day and run the below query:

SELECT 
    date,
    id as campaign_id, 
    sum(impressions) as total_impressions,
    sum(clicks) as total_clicks
FROM `google_ads_schema.campaign_stats` 
where id = <INSERT CAMPAGIN ID>
    and date = '<INSERT RANDOM DAY>'
group by 1,2

My suspicion is that the delta between this query and the ad_reporting query for this same campaign would include the DSA's. Would you be able to try this to confirm? Additionally, I noticed there seems to be no spend data related to DSA's. How do you expect spend to be recorded for these ads?

Let me know if you wanted to jump on another Calendly call to do this live 😄

[bnealdefero]

Hi @fivetran-joemarkiewicz

Had some discussions with our media folks here, DSA's can be in their own campaign or ad group. They can’t be included with other ads so we shouldn't run in to he case of DSA's being intermixed with regular ads.

We are pretty geared to only reporting at the campaign level here and if ad specific details are needed we would just jump into the specific platform. Not sure everyone operates and/or wants to operate that way though.

Maybe there could be a switch added upstream to alternate between ad_stats and campaign_stats? Total guess there but out of curiosity I'm going to look into that.

To the note about spend, spend is included in the campaign_stats table as cost_micros

Here's that sample query you gave with spend included, but based on DSA's not being included in campaigns with other ads, no real delta to run.

SELECT 
    date,
    id as campaign_id, 
    sum(impressions) as total_impressions,
    sum(clicks) as total_clicks,
    sum(cost_micros / 1000000.0) as total_spend
FROM fivetran.google_ads.campaign_stats 
where id = 11373982011
    and date = '2021-09-01'
group by 1,2

Thanks for the work here and if I can help more just holler and I will do my best.

[fivetran-joemarkiewicz]

Thanks @bnealdefero! This is extremely helpful information. Let me dig in some more and validate and I will get back to you on my thoughts for next steps.

[fivetran-joemarkiewicz]

Hi @bnealdefero sorry for the delay as we have been looking into this pretty diligently to come to an understanding. As we looked into this more, we were trying to find ways to replicate this DSA behavior in our own data, but we were unable to do so.

To better understand the behavior of these DSA ads and how we should appropriately account for them in this package, we would like to see how exactly how they are being omitted from the queries. Would you be able to run the below query in your warehouse and let us know if you return any results? We currently return none, but are interested to see the outcome for you.

FYI You will most likely want to change the schema in the queries to fit yours.

with ad_stats as (

  select date, campaign_id, sum(cost_micros) as cost, sum(clicks) as clicks
  from google_ads_api_2.ad_stats
  group by 1,2

), ad_group_stats as (

  select date, campaign_id, sum(cost_micros) as cost, sum(clicks) as clicks
  from google_ads_api_2.ad_group_stats
  group by 1,2

), campaign_stats as (

  select date, id as campaign_id, sum(cost_micros) as cost, sum(clicks) as clicks
  from google_ads_api_2.campaign_stats
  group by 1,2

), joined as (

  select 
    coalesce(ad_stats.date, ad_group_stats.date, campaign_stats.date) as date,
    coalesce(ad_stats.campaign_id, ad_group_stats.campaign_id, campaign_stats.campaign_id) as campaign_id,
    round(ad_stats.cost,2) as ad_cost,
    round(ad_group_stats.cost,2) as ad_group_cost,
    round(campaign_stats.cost,2) as campaign_cost,
    round(ad_stats.clicks,2) as ad_clicks,
    round(ad_group_stats.clicks,2) as ad_group_clicks,
    round(campaign_stats.clicks,2) as campaign_clicks
  from campaign_stats
  full outer join ad_group_stats
    on campaign_stats.date = ad_group_stats.date
    and campaign_stats.campaign_id = ad_group_stats.campaign_id
  full outer join ad_stats
    on ad_stats.date = campaign_stats.date
    and ad_stats.campaign_id = campaign_stats.campaign_id

)

select *
from joined
where ad_cost != ad_group_cost
or ad_cost != campaign_cost
or ad_group_cost != campaign_cost
or ad_clicks != ad_group_clicks
or ad_clicks != campaign_clicks
or ad_group_clicks != campaign_clicks

If this does produce results for you, I would like to schedule another Calendly meeting to do some more deep dive investigation as to how we can account for these DSA ads.

[bnealdefero]

Hi @fivetran-joemarkiewicz no worries at all on the delay..

I went ahead and ran the query and I do return results.. I'll get something setup via your calendly..

Thanks for the continued push..

[fivetran-joemarkiewicz]

Hey @bnealdefero as always, it was great chatting with you today!

Following our call, it seems these DSA ads could potentially be integrated into the google_ads__url_ad_adapter and eventually the ad_reporting package as well. You had also mentioned you would be open to investigating this more with your data, which I really appreciate! The way I think this will need to be done is by unioning the DSA records with the other ads in the google_ads__url_ad_adapter model.

During our investigation today, we found that DSA stats exist within the source campaign_stats table; however, they do not exist within the ad_stats table (which the google_ads__url_ad_adapter is built from). With the assumption (which needs to be verified with Google) that DSA's only exist within their own campaigns, I think it would be possible to perform a query within google_ads__url_ad_adapter for these DSA campaigns and join in the relevant ad information to then be unioned with the other ads. I did take some time to look into this a bit and actually found when I joined the ad_history and final_url fields into the base campaign_stats table, the data fanned out quite a bit. I am curious if you end up seeing the same. I wonder if the fix required would be an artificial creation of ad_group and ad fields. This way the data wouldn't fan out, and you would obtain the desired campaign_stat metrics for the DSAs. I am interested to see what you find!

I did notice that we currently do not bring in the campaign_stats source table in our dbt_google_ads_source package. To help with this, I just created a branch on this repo that points to a branch of the source package with the campaign_stats model added as stg_google_ads__campaign_stats. You can use the below packages.yml config for testing this out:

packages:
  - git: https://github.com/fivetran/dbt_google_ads.git
    revision: feature/google-dsa-ads
    warn-unpinned: false

Let me know if you want to jump on another call next week as you are able to dig into this more. I look forward to hearing more and hope you are able to bring these DSA ads in 😄

[fivetran-joemarkiewicz]

The v1.0.0 version of the ad reporting package (and accompanied google_ads release) should have addressed this issue with the breakout of the different reports.

Closing this issue as it should be resolved. Please feel free to reopen if there is more discussion to be had.