Use this data connector to import Google Ads reports into Treasure Data.

There are several Google Ads integrations available. This article describes Google Ads Remarketing.


Google has rebranded Google AdWords to Google Ads.

This article does not describe Google Ads via Audience Partner API (formerly known as AdWords via DDP). The name changed after Google rebranded Google AdWords to Google Ads.

You use this same connector to export Google Ads Remarketing data. See Google AdWords Remarketing Lists Export Integration.




Prerequisites

  • Basic knowledge of Treasure Data

  • Basic knowledge of Google Ads

Use TD Console

Create a New Connection

Navigate to Integrations Hub to Catalog, search and select Google AdWords input. The following dialog opens.


Select it to connect a new account. Log into your Google AdWords account from the new window and grant Treasure Data access to your Ads campaigns:


You are redirected back to Integrations Hub to Catalog. Repeat the step to connect to a new account to choose your new OAuth connection.

Select Continue and give your connection a name:

Then select Done.

Create a Source

After creating the connection, you are automatically taken to the Sources tab. Look for the connection you created and select New Source.


The following dialog opens. Edit the details and select Next.

In the field Ad Account, enter the Customer ID in your Google Ads console UI.

The parameters that you specify next are based on the Google Ad report type.




Report Type

You can select from the following reports: Ad Performance, AdGroup Performance, Campaign Performance, and Audience Performance. See the Appendix for more information.

Attributes / Segments / Metrics

Depending on the report type picked, only available attributes, segments, and metrics are showed when you select Add.

Some predefined attributes are automatically added, corresponding to the report type. See the Appendix for more information.

If you check Use predefined metrics, a list of predefined metrics is automatically added. See the Appendix for more information. You can select additional metrics from the Metrics dropdown.

Incremental Loading

By enabling incremental loading, you can schedule the data loading during a time period that you specify. Note that data is fetched only when the full period of time (automatically computed based on the Date Range value) is complete.

Date Range

Specify the date range of the report being generated. You must enter a start date and an end date if you select CUSTOM_DATE. The period for scheduling mentioned in Incremental Loading is the number of days between Start Date and End Date. See the Appendix for more information.

Next, you see a Preview of your data. The preview data doesn’t reflect the actual data from your Google Ads account. It’s just dummy data to show all the fields you picked, as well as the data type of those fields.

If you wish to change anything, select Back or else select Next.

Advanced Settings

Advanced Settings allow you to customize the guessed properties. Edit the following section, if needed.

Choose the Target Database and Table

Choose an existing or create a new database and table


Create a new database and give your database a name. Complete similar steps for Create new table.

Select whether to append records to an existing table or replace your existing table.

If you want to set a different partition key seed rather than use the default key, you can specify one using the popup menu.

 Scheduling

In the Schedule tab, you can specify a one-time transfer, or you can schedule an automated recurring transfer. If you select Once now, select Next. If you select Repeat… specify your schedule options, then select Next.



After your transfer has run, you can see the results of your transfer in the Databases tab.

Use TD Toolbelt from the Command Line

Install TD Toolbelt v0.11.9 or Later

You can install the newest TD Toolbelt.

$ td --version
0.15.0

Create Configuration File

Prepare configuration file (for eg: load.yml) with your Google Ads account access information, similar to the following example.

in:
    type: google_adwords
    client_id: "<app client id>" (required, string)
    client_secret: "<app client secret>" (required, string)
    refresh_token: "<your oauth2 refresh token>" (required, string)
    developer_token: "<AdWords developer token>" (required, string)
    target: audience_performance_report (required, string)
    client_customer_id: "<your AdWords account number>" (required, string)
    date_range: custom_date (required, enum)
    start_date: 2018-04-01 (required if `custom_date` date range, date)
    end_date: 2018-04-07 (required if `custome_date` date range, date)
    include_zero_impressions: true (optional, boolean, default is `true`)
    incremental: true (optional, boolean, default is `false`)
    columns:
    - AdGroupName
    - CampaignStatus
    - Date
    - Clicks
    - ...
out:
    mode: replace

This example dumps Audience Performance report from Google Ads:

  • client_id: OAuth application client id.

  • client_secret: OAuth application client secret

  • refresh_token: Refresh token, result of OAuth authentication flow

  • target: Google Ads report type to be imported.

    • See the Appendix for the list of available target.

  • client_customer_id: Google AdWords account number

  • date_range: Available date range from Ads

    • See the Appendix for the list of available date_range.

  • start_date: From which date (yyyy-MM-dd) report data to be generated. This field is required if custom_date is selected for date_range.

  • end_date: To which date (yyyy-MM-dd) product data to be imported. This field is required if date_range is custom_date.

  • include_zero_impressions: true means accepting all rows that have no impression

  • incremental: Support incremental loading

  • columns: List of attributes, segments and metrics to be collected

    • See the Appendix for the list of available attributes, segments and metrics.

For more details on available out modes, see the Appendix.

Preview Data to Import (Optional)

You can preview data to be imported using the command td connector:preview.

$ td connector:preview load.yml
+-----------------+---------------------+-----------------+----
| id:long         | displayurl:string   | headline:string | ...
+-----------------+---------------------+-----------------+----
| 42023           | "Hello"             | "Ads"           |
| 42045           | "World"             | "Ads"           |
+-----------------+---------------------+-----------------+----

Execute Load Job

You use td connector:issue to execute the job. The following are required:

  • name of the schedule

  • cron-style schedule

  • database and table where the data will be stored

  • the Data Connector configuration file

$ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column updated_date

If the database or the table do not exist in TD, td connector:issue fails

It’s also recommended to specify 

  • -time-column

 option, because Treasure Data’s storage is partitioned by time (see data partitioning). If the --time-column option is not available, the data connector selects the first long or timestamp column as the partitioning time. The type of the column specified by --time-column must be either of long or timestamp type (use Preview results to check for the available column name and type). A time column is available at the end of the output.

$ td connector:issue load.yml --database td_sample_db --table td_sample_table --time-column updated_date --auto-create-table
in:
  type: googlead
  ...
filters:
- type: add_time
  from_value:
    mode: upload_time
  to_column:
    name: time
out:
  type: td


Read more at add_time filter plugin.

If you have a field called time, you don’t need to specify the option -time-column.

Scheduled Execution

You can schedule periodic data connector execution for Google Ads import. We have configured our scheduler carefully to ensure high availability. By using this feature, you no longer need a cron daemon on your local data center.

Create the Schedule

A new schedule can be created using the td connector:create command. The name of the schedule, cron-style schedule, the database and table where their data is stored, and the data connector configuration file are required.

$ td connector:create \
    daily_google_adwords_import \
    "10 0 * * *" \
    td_sample_db \
    td_sample_table \
    load.yml

The `cron` parameter also accepts these three options: `@hourly`, `@daily` and `@monthly`.

By default, schedule is setup in UTC timezone. You can set the schedule in a timezone using -t or --timezone option. The `--timezone` option supports only extended timezone formats like 'Asia/Tokyo', 'America/Los_Angeles' etc. Timezone abbreviations like PST, CST are *not* supported and may lead to unexpected schedules.

List the Schedules

You can see the list of currently scheduled entries by td connector:list.

$ td connector:list
+-----------------------+--------------+----------+-------+--------------+-----------------+-----------------------------+
| Name                  | Cron         | Timezone | Delay | Database     | Table           | Config                      |
+-----------------------+--------------+----------+-------+--------------+-----------------+-----------------------------+
| daily_google_adwords_import | 10 0 * * *   | UTC      | 0     | td_sample_db | td_sample_table | {"type"=>"google_adwords", ... } |
+-----------------------+--------------+----------+-------+--------------+-----------------+-----------------------------+

Show the Setting and History of Schedules

td connector:show shows the execution setting of a schedule entry.

% td connector:show daily_google_adwords_import
Name     : daily_google_adwords_import
Cron     : 10 0 * * *
Timezone : UTC
Delay    : 0
Database : td_sample_db
Table    : td_sample_table

td connector:history shows the execution history of a schedule entry. To investigate the results of each individual execution, use td job <jobid>.

% td connector:history daily_google_adwords_import
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
| JobID  | Status  | Records | Database     | Table           | Priority | Started                   | Duration |
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
| 578066 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-18 00:10:05 +0000 | 160      |
| 577968 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-17 00:10:07 +0000 | 161      |
| 577914 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-16 00:10:03 +0000 | 152      |
| 577872 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-15 00:10:04 +0000 | 163      |
| 577810 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-14 00:10:04 +0000 | 164      |
| 577766 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-13 00:10:04 +0000 | 155      |
| 577710 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-12 00:10:05 +0000 | 156      |
| 577610 | success | 10000   | td_sample_db | td_sample_table | 0        | 2015-04-11 00:10:04 +0000 | 157      |
+--------+---------+---------+--------------+-----------------+----------+---------------------------+----------+
8 rows in set

Delete the Schedule

td connector:delete removes the schedule.

$ td connector:delete daily_google_adwords_import


Appendix

Modes for Out Plugin

You can specify file import mode in out section of load.yml.

Append (Default)

This is the default mode and records are appended to the target table.

in:
  ...
out:
  mode: append

Replace (In td 0.11.10 and Later)

This mode replaces data in the target table. Any manual schema changes made to the target table remain intact with this mode.

in:
  ...
out:
  mode: replace


Available Report Types

Report Type

Description

ad_performance_report

Ad Performance Report

adgroup_performance_report

AdGroup Performance Report

campaign_performance_report

Campaign Performance Report

audience_performance_report

Audience Performance Report

keywords_performance_report

Keywords Performance Report


Predefined Attributes

Report Type

Predefined Attributes

Ad Performance

– Id
– DisplayUrl
– Headline
– HeadlinePart1
– HeadlinePart2

AdGroup Performance

– AdGroupId
– AdGroupName

Campaign Performance

– CampaignId
– CampaignName

Audience Performance

– Id
– UserListName

Keywords Performance Report

– Id
– AdGroupId


Predefined Metrics

Report Type

Predefined Metrics

Ad Performance
AdGroup Performance
Campaign Performance

– Clicks
– Impressions
– Ctr
– AverageCpc
– Cost
– Conversions
– ViewThroughConversions
– CostPerConversion
– ConversionRate

Audience Performance

– Clicks
– Impressions
– Ctr
– AverageCpc
– Cost
– AverageCPM
– AbsoluteTopImpressionPercentage
– TopImpressionPercentage

Keywords Performance 

– Clicks
– Impressions
– Ctr
– AverageCpc
– Cost
– Conversions
– ViewThroughConversions
– CostPerConversion
– ConversionRate
– AbsoluteTopImpressionPercentage
– TopImpressionPercentage


Available Date Ranges

Date Range

Description

TODAY

Today only.

YESTERDAY

Yesterday only.

LAST_7_DAYS

The last 7 days not including today.

LAST_WEEK

The seven-day period starting with previous Monday.

LAST_BUSINESS_WEEK

The 5 day business week, Monday through Friday, of the previous business week.

THIS_MONTH

All days in the current month.

LAST_MONTH

All days in the previous month.

ALL_TIME

The entire available time range.

CUSTOM_DATE

A custom date range. Need start_date and end_date in yyyy-MM-dd.

LAST_14_DAYS

The last 14 days not including today.

LAST_30_DAYS

The last 30 days not including today.

THIS_WEEK_SUN_TODAY

The period between the previous Sunday and the current day.

THIS_WEEK_MON_TODAY

The period between the previous Monday and the current day.

LAST_WEEK_SUN_SAT

The seven-day period starting with the previous Sunday.


Available Fields

Report Type

Reference

Ad Performance

Ad Performance Report

AdGroup Performance

AdGroup Performance Report

Campaign Performance

Campaign Performance Report

Audience Performance

Audience Performance Report

Keywords Performance

Keywords Performance

  • No labels