I have configured a Google Ads DataTransfer stream from Google Ads to my GoogleBigQuery project. It runs, data flows, everything is fine. But when I decided to build a query that return an amount of money spend in the context of distinct combination of utm_marks (source, medium, campaign) I've faced a trouble with 'doublicated' data.

So, the query firstly goes to Adstat Table and takes the stats of every creativeId (I suppose creativeId means Ad) in every campaignId. Then it takes an every utm_marks from AdTrackingUrlTemplate of every creativeId from every campaign. Finally it merges two tables in one and in the output I have a full info about stats for every utm_mark.

Query looks like this:

 with   Adstat as (             select *              from `myproject.GoogleAds.AdStats_7394379271`         ),  Ad as (         select              CampaignId,              CreativeId,              REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?&]utm_source=([^&]+)") as source,             REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?&]utm_medium=([^&]+)") as medium,             REGEXP_EXTRACT(CreativeTrackingUrlTemplate, r"[?&]utm_campaign=([^&]+)") as campaign         from              `myproject.GoogleAds.p_Ad_7394379271`         where              CreativeTrackingUrlTemplate is not null          and              CreativeTrackingUrlTemplate!="{lpurl}"         group by              CampaignId, CreativeId, source, medium, campaign        ) select     date, CampaignId, CreativeId, impressions,      Clicks, Cost, Cost * 1.2/1000000 as adCost, source, medium, campaign from      Adstat  left join       Ad using (CampaignId, CreativeId) where      date = '2021-11-26' and      CampaignId = 1688777252 and      CreativeId = 328994634699 

output:

And there is a trouble. If a creativeId during its lifetime has a several utm_marks in AdTrakingTemplate, all of them will go to result and all of them will receive a stats from AdStats Table (you can see at in output: same date, same CreativeAd, same stats, but different utms). So we have a double (triple,quadriple) impressions, clicks, amount spent etc. It's a pretty common case, because it's easier from manager to change a tracking template, than create a new Ad or Campaign in Google Ads.

And, unfortunatly, I don`t know, how to figure it out, cause there no way to determ which exactly utm_marks were in createiveIdTrakingTemplate when some stat actcions (impressions, click, etc) were performed.

Does anyone know, how to deal with it? Thanks for help!

I'm trying to pull data from my Google Ads account using their API. I'm using Python

In the code example there is a query where I can specify the date range from which I want to collect results from my campaign.

I would like to dynamically change de date everyday and run the script to get data from the previous day.

My idea was to create a variable with the previous day date in the format Google API is requiering it, named "yesterday".

But I don't know how to use this variable into the query

Here's a part of the code :

import argparse import sys from google.ads.googleads.client import GoogleAdsClient from google.ads.googleads.errors import GoogleAdsException from datetime import * yesterday_date = date.today() - timedelta(days=1) yesterday = yesterday_date.strftime("%Y-%m-%d") def main(client, customer_id): ga_service = client.get_service("GoogleAdsService") query = """     SELECT       campaign.id,       campaign.name,       metrics.cost_micros,       campaign.status     FROM campaign     WHERE segments.date = '2021-08-19'         """ # Issues a search request using streaming. response = ga_service.search_stream(customer_id=customer_id, query=query) for batch in response:     for row in batch.results:         print(             f"Campaign with ID {row.campaign.id}, status is {row.campaign.status}, cost is {row.metrics.cost_micros} and name "             f'"{row.campaign.name}" was found.'         ) 

I would like to do something like this :

WHERE segments.date = yesterday 

I've tried to use sqlite3 library, to use cursor.execute() but I don't know how to use them properly.

I'm a beginner so the answer might be very easy.

I am trying to extract the Unnest data contained in JSON Arrays that Google Ads sends via BigQuery Data Transfers. Unfortunately, I am getting stuck in the middle.

Original Data in a BigQuery Table:

Desired Outcome:

This is the query that got me the closest but is still showing JSON.

SELECT   CreativeId,   JSON_QUERY_ARRAY(ResponsiveSearchAdDescriptions) AS Ads FROM   `priXXXXXX.sandbox.Ad_XXXXXXX` WHERE   ResponsiveSearchAdDescriptions IS NOT NULL LIMIT   100 

The Query should be able to include this condition ResponsiveSearchAdDescriptions IS NOT NULL

Some ideas?