Revert "adding search_with_ads_organic to mobile_clients_daily table"

[scholtzan]

Reverts mozilla/bigquery-etl#5683

┆Issue is synchronized with this Jira Task

[dataops-ci-bot]

Integration report for "Revert "adding search_with_ads_organic to mobile_clients_daily table (#5683)""

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-31 16:50:15.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-31 16:50:20.000000000 +0000 @@ -722,12 +722,6 @@ SUBSTR(search.key, STRPOS(search.key, '.') + 1), search.search_type ) - WHEN search.search_type = 'search-with-ads' - THEN IF( - REGEXP_CONTAINS(search.key, '\\.'), - SUBSTR(search.key, STRPOS(search.key, '.') + 1), - search.search_type - ) ELSE search.search_type END AS source, search.value AS search_count, @@ -780,8 +774,6 @@ CASE WHEN search_type = 'ad-click' THEN IF(STARTS_WITH(source, 'in-content.organic'), 'ad-click-organic', search_type) - WHEN search_type = 'search-with-ads' - THEN IF(STARTS_WITH(source, 'in-content.organic'), 'search-with-ads-organic', search_type) WHEN STARTS_WITH(source, 'in-content.sap.') THEN 'tagged-sap' WHEN REGEXP_CONTAINS(source, '^in-content.*-follow-on') @@ -864,15 +856,6 @@ ) ) AS search_with_ads, SUM( - IF( - search_type != 'search-with-ads-organic' - OR engine IS NULL - OR search_count > 10000, - 0, - search_count - ) - ) AS search_with_ads_organic, - SUM( IF(search_type != 'unknown' OR engine IS NULL OR search_count > 10000, 0, search_count) ) AS unknown, udf.mode_last(ARRAY_AGG(country)) AS country, ```

Link to full diff

[dataops-ci-bot]

Integration report for "Merge branch 'main' into revert-5683-RS_788_clients_daily_table"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-31 16:53:25.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/search_derived/mobile_search_clients_daily_v1/query.sql 2024-05-31 16:53:36.000000000 +0000 @@ -722,12 +722,6 @@ SUBSTR(search.key, STRPOS(search.key, '.') + 1), search.search_type ) - WHEN search.search_type = 'search-with-ads' - THEN IF( - REGEXP_CONTAINS(search.key, '\\.'), - SUBSTR(search.key, STRPOS(search.key, '.') + 1), - search.search_type - ) ELSE search.search_type END AS source, search.value AS search_count, @@ -780,8 +774,6 @@ CASE WHEN search_type = 'ad-click' THEN IF(STARTS_WITH(source, 'in-content.organic'), 'ad-click-organic', search_type) - WHEN search_type = 'search-with-ads' - THEN IF(STARTS_WITH(source, 'in-content.organic'), 'search-with-ads-organic', search_type) WHEN STARTS_WITH(source, 'in-content.sap.') THEN 'tagged-sap' WHEN REGEXP_CONTAINS(source, '^in-content.*-follow-on') @@ -864,15 +856,6 @@ ) ) AS search_with_ads, SUM( - IF( - search_type != 'search-with-ads-organic' - OR engine IS NULL - OR search_count > 10000, - 0, - search_count - ) - ) AS search_with_ads_organic, - SUM( IF(search_type != 'unknown' OR engine IS NULL OR search_count > 10000, 0, search_count) ) AS unknown, udf.mode_last(ARRAY_AGG(country)) AS country, ```

Link to full diff