Google Ads (Former AdWords) Connector

You are looking at an older version of the documentation. The latest version is found here.

The Google Ads Connector reads data from the Google Ads Web Service API and makes it available in the Data Virtuality Server in the relational form. This way, users can query Google Ads Data via SQL and write complex queries directed to multiple data sources. For example, e.g. a single SQL query can join tables from MySQL and combine them with data from Google Ads.

Connector Configuration

Metadata

Before issuing the SQL queries to the Google Ads Web Service, we need to configure a connection using the SYSADMIN.createConnection() procedure:

CALL SYSADMIN.createConnection( 'x1', 'Ads', null, 'DeveloperToken=dev-token,ClientId=example-client.apps.googleusercontent.com,ClientSecret=cl-secret,RefreshToken=ref-token,UserAgent=DataVirtuality');
CALL SYSADMIN.createDatasource( 'x1', 'Ads', null, 'convertEmptyStringResultToNull=true');

The convertEmptyStringResultToNull translator parameter is optional, and its default value is TRUE. This parameter converts "--" string values in the result set returned by a Google Ads report system procedure for number field types to null values accordingly.

Google Ads SDK version 201406 requires you to set userAgent to a non-empty string by which you can identify your API Request, and Google Team can identify where the request comes from if any problem arises. Put any valid name to userAgent. The parameter is not mandatory and has the 'DataVirtuality' value by default.

If you get the error "Execution failed: Ads credentials seem to be invalid. No metadata will be loaded", you have to allow the Data Virtuality Server machine to connect the Google Ads API at Google. 

First, check your Ads account via Browser. If there is a Message like "Someone tried to access your account via API, please confirm that's you", confirm it after checking that it is the IP Address of the server running the Data Virtuality Server. Secondly, sometimes it is needed to log in from the same machine where the Data Virtuality Server runs via Browser (especially when the server is not located in the IP Address range you usually are logged in ) and solve the captcha.

Usage

The Data Virtuality Server supports querying the Ads Reporting API from Google. The Ads Service provides a number of reports for each clientCustomerId. clientCustomerIds are presented in the customer_id table. The Data Virtuality Server presents reports themselves as stored procedures, whereby there is a single stored procedure per report type prefixed by "get":

To query the customer_id table, you can use a regular SQL SELECT query like the following:

SELECT * from Ads1.customer_id

You can query data from a particular report by issuing the SQL stored procedure call like this:

SELECT
cust_id as CustomerId,
displayURL As AnzeigeUrl,
headline as Anzeige,
ID As AnzeigenId,
adgroupname as Anzeigengruppe,
adgroupstatus as Anzeigenstatus,
adtype as Anzeigentyp,
conversionRate As Conversion,
AveragePosition As Position,
Impressions as Impressionen,
CampaignName as Kampagne,
d.Criteria As KeywordPlacement,
Clicks as Klicks,
AccountDescriptiveName as Konto,
Cost as Kosten,
ExternalCustomerId as Kundennummer,
"Date" As Datum,
Description1 As Textzeile1,
Description2 As Textzeile2,
AdNetworkType2 As Netzwerk, 
CreativeDestinationUrl As ZielUrl 


FROM 
(SELECT * FROM Ads1.customer_id cust,
table(
      CALL Ads1.getAD_PERFORMANCE_REPORT(
              cust.customerid, 
              timestampadd(SQL_TSI_DAY,-7,curdate()),
              curdate(),              'displayURL, headline,id, adgroupname, adgroupstatus, adtype, conversionRate, AveragePosition, Impressions, CampaignName, Clicks, AccountDescriptiveName, Cost, ExternalCustomerId, Date, Description1, Description2, AdNetworkType2, CreativeDestinationUrl'
      )
) a 
) b


left outer join 


(
SELECT distinct cust.customerid AS cust_id,c.id AS cid ,c.Criteria 
FROM Ads1.customer_id cust,table(
  CALL Ads1.getCRITERIA_PERFORMANCE_REPORT(cust.customerid, 
       timestampadd(SQL_TSI_DAY,-7,curdate()),
       curdate(), 
       'id,Criteria'
  )) c 
) d
on d.cust_id=b.customerid

General syntax for a particular stored procedure is as follows:

CALL <Ads_schema_name>.<stored_procedure_name>(<String clientCustomerId>,<Date fromDate>,<Date toDate>,<String fieldsToOutput>)

The first parameter denotes the client customer id, and the second and the third ones, the interval to get data. The last parameter is a comma-separated string with the particular Report fields that the stored procedure should return. This is required since Ads does not allow querying a specific field combination in a single report.

In the result, the stored procedure for a particular report would return all fields of the report, but the fields not specifically requested in the 4th parameter will contain null values.

To skip the NULL fields in the results of a stored procedure, you can wrap the CALL statement into a SELECT statement providing only the non-null fields in the SELECT's projection as follows:

SELECT c.id AS cid ,c.Criteria 
FROM table(
  CALL Ads1.getCRITERIA_PERFORMANCE_REPORT(
       '11111111', 
       timestampadd(SQL_TSI_DAY,-7,curdate()),
       curdate(), 
       'id,Criteria'
  )
) c 

Using Predicates

Predicates are equivalent to filters in the Googe Ads UI. A predicate is comprised of a report field, operators, and values. Predicates are treated as inclusive (AND) conditions.

Example: Performance report. By default, this report will only include rows where IsNegative = FALSE. To include negative keywords, add a predicate for IsNegative IN [TRUE, FALSE] or IsNegative = TRUE. 

SELECT * FROM (CALL "adwords1.getAD_PERFORMANCE_REPORT"(
"customerId" => '2135959314',
"startDate" => curdate(),
"endDate" => curdate(),
"fields" => 'Id',
"predicates" => '[IsNegative,EQUALS,{true}]'
 )) AS a ;;

Several predicates can be used:

SELECT * FROM (CALL "adwords1.getAD_PERFORMANCE_REPORT"(
"customerId" => '2135959314',
"startDate" => curdate(),
"endDate" => curdate(),
"fields" => 'Id',
"predicates" => '[IsNegative,NOT_EQUALS,{true}],[Id,IN,{1,2}]'
 )) AS a ;;

See Google Ads Predicates for the list of predicates.

See Google Ads documentation for more details.

Google Ads Connector with Write Support

The Google Ads Connector with write support is used to read and write data via the Google Ads Web Service API through special stored procedures created dynamically in the Data Virtuality Server. This way, users can change campaigns, budgets, labels, ads and so on in a Google Ads data source using the Google Ads connector with write support.

Connector configuration

Metadata

To use the Google Ads connector with write support, we need to configure a connection using the SYSADMIN.createConnection()/SYSADMIN.createDataSource() procedures:

CALL SYSADMIN.createConnection( 'x1', 'Ads', null, 'DeveloperToken=dev-token,ClientId=example-client.apps.googleusercontent.com,ClientSecret=cl-secret,RefreshToken=ref-token,UserAgent=DataVirtuality');
CALL SYSADMIN.createDatasource( 'x1', 'Adsmgmt', null, null);

Google Ads Connector with write support can work only through the Adsmgmt translator. Please provide the name in the SYSADMIN.createDataSource() procedure to get write support in the Google Ads connector.

Usage

Once the Google Ads connector with write support is configured, the Data Virtuality Server will get all needed stored procedures for using load and write operations via the Google Ads Web Service API:

All stored procedures have a corresponding prefix indicating the operation for which a stored procedure is designed. For example, to get data, users should use get* system procedures; to insert a new Ads object, users should use insert* system procedures.

Please note that before inserting a new Ads object, sometimes you should create the object calling new*  system procedure.

All system procedure arguments are sorted in alphanumeric order to avoid mixing them randomly when recreating the Google Ads connector with write support. A system procedure's return value is an array represented as an object in Data Virtuality Server. Each item in an array is called an element, and each element is accessed by its numerical index. In the Data Virtuality Server, array numbering begins with 1.

All not Ads_OBJECT stored procedures have mandatory clientCustomerId parameter as the first argument.