The CData JDBC Driver for Microsoft Ads models entities in the Microsoft Ads API as database views and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

The following sections provide information on executing SQL to the available API objects. The driver offloads as much of the SELECT statement processing as possible to Microsoft Ads and then processes the rest of the query in memory; API limitations and requirements are documented in this section.

See SupportEnhancedSQL for more information on how the driver circumvents API limitations with in-memory client-side processing.

Views

The driver Microsoft Ads objects as Views, including reports.

You can query Reports based on an aggregation and time period. The default time period is this year; to change the time period, see the following Time Filter section.

When retrieving only some report columns, all columns marked as 'Select Requirement' and at least one of the columns marked as 'Metric' should be selected to issue a valid report request. On the other hand, if you're retrieving all columns then the driver will default to including the minimally required columns in the request.

Querying Report Aggregations

By default the report data will be aggregated by the entire specified report time, and the report will not include a time period column. Data, by default, will be retrieved only for the last month. If you want to change this behaviour see the 'Specifying a Time Filter' section below.

Additionally you can aggregate the report data by selecting one of the following aggregation columns:

• Daily: Each row of the report identifies the month, day, and year when the transaction occurred. The report data will be aggregated by each day. The report will include a column named Daily that contains the day formatted as yyyy-mm-dd.
  

  SELECT Daily, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ]

• DayOfWeek: Each row of the report identifies the day of the week when the transaction occurred. The report data will be aggregated by each of the seven days in a week. The report will include a column named DayOfWeek, and the possible values are 1 - 7 where 1 represents Sunday and 7 represents Saturday.

  If the report time spans multiple weeks, then the performance data across all weeks for a given day of the week will be aggregated in one row. For example if Campaign A has 5 impressions every Monday (day 2) throughout each of the 3 weeks included in the report time range, then the report will include one row with DayOfWeek set to 2 and impressions in that row totaling 15.
  

  SELECT DayOfWeek, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ]

• Hourly: Each row of the report identifies the hour when the transaction occurred. The report data will be aggregated by each hour of the day. The report will include a column named Hourly, and the possible values are 0 - 23.
  

  SELECT Hourly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ]

• HourOfDay: Each row of the report identifies the hour of the day when the transaction occurred. The report data will be aggregated by each of the 24 hours across all days. The report will include a column named HourOfDay, and the possible values are 0 - 23.

  If the report time spans multiple days, then the performance data across all days for a given hour will be aggregated in one row. For example if Campaign A has 5 impressions during hour 7 on each of the 3 days included in the report time range, then the report will include one row with impressions for HourOfDay totaling 15.
  

  SELECT HourOfDay, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ]

• Monthly:

  Each row of the report identifies the month when the transaction occurred. The report data will be aggregated by each month. The report will include a column named Monthly that contains the first day of the month formatted as yyyy-mm-dd.
  

  SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ]

• Weekly:

  Each row of the report identifies the week when the transaction occurred. The report data will be aggregated by each week. The report will include a column named Weekly that contains the date of the Sunday for each week formatted as yyyy-mm-dd.
  

  SELECT Weekly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ]

• Yearly:

  Each row of the report identifies the year when the transaction occurred. The report data will be aggregated by each year. The report will include a column named Yearly that contains the year formatted as yyyy.
  

  SELECT Yearly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ]

Specifying a Time Filter

You can specify a custom date range or specify a predefined date range.

• To search on a predefined date range, set the DatePreset pseudo column in the WHERE clause to one of the following values: Today, Yesterday, LastSevenDays, ThisWeek, LastWeek, LastFourWeeks, ThisMonth, LastMonth, LastThreeMonths, LastSixMonths, ThisYear, or LastYear. The default DatePreset value is "ThisYear".
  

  SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ] WHERE DatePreset = 'LastYear'

• To specify a custom date range, specify the StartDate and EndDate pseudo columns in the WHERE clause.
  

  SELECT Monthly, AccountName, AccountStatus, Impressions, Clicks, ConversionRate  FROM [ AccountPerformanceReport ] WHERE StartDate = '2014-01-01' AND EndDate = '2017-09-19'

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.