[Bug] Incorrect surrogate key combination used in search report about dbt_ad_reporting HOT 4 CLOSED

Hi @clay-walker thanks for opening this issue. Someone from my team will follow up here in the next few days to discuss the issue further and determine a possible solution.

from dbt_ad_reporting.

hey @clay-walker i definitely agree that match_type should be included in the surrogate key!

as for your second point, does microsoft hard-delete keyword_id from the daily search report? i am wondering if changing this line to reference report.keyword_id (instead of keyword_history) would result in no nulls. i must admit i'm a little apprehensive to apply an inner join 😨

also just curious - do you know if microsoft hard deletes IDs from other history tables (ie campaign or ad_group?)

from dbt_ad_reporting.

looks like someone else is seeing this issue with the microsoft ad_group report as well - fivetran/dbt_microsoft_ads#18. and we've just confirmed that keyword_id or other ids are not hard-deleted from the report tables.

so my next question would be, what's your opinion on referencing the report.keyword_id instead, so that if a keyword has been hard-deleted, you'd still have its ID but not any other info about it from the keyword_history table? would that be valuable or confusing/unnecessary to have, as opposed to filtering them out with an inner join? i presume that some people would at least want to keep them 🤔

i am thinking that the path forward will be

1. adding match_type to the surrogate key
2. auditing the whole microsoft package for any instances in which we selecting an id (such as keyword_id) in the final CTE of a model, but from the right side of the join. we should change this to reference the report on the left side of the join

from dbt_ad_reporting.

Hi @clay-walker the latest version of the package (v1.0.4) should address this issue you identified within this bug report. Feel free to upgrade to the latest version and the initial issue should be addressed.

For the time being I will close this issue as the fix has been rolled out. Please feel free to reopen or create a new issue if you experience any other questions when using the package.

from dbt_ad_reporting.