Google Display & Video 360 Usage Scenarios

Queries and Files with Data on Queries Internals

Google Display & Video 360 reports are represented as list of queries created by customer in web interface or using API (e.g. by the Google Display & Video 360 connector). Each query has specific configuration, most important parameters are: type of the report and lists of metrics and dimensions. Query with specific configuration cannot be changed later. If another set of parameters is needed new query can be created. Parameters related to dates range can be set for every run of a query. Result of running a query is a new file generated by Google Display & Video 360 server, containing reporting data. You can see internals of these structures by the following procedures:

Queries () - list configurations of all the queries (or specific query defined by parameter queryId);
SQL
```
SELECT * FROM (
		CALL google_display_video.Queries ()
) AS x;;
```
QueryReportFiles () - list all the files with reporting data of a specific query defined by parameter queryId
SQL
```
SELECT * 
FROM (
	CALL google_display_video.QueryReportFiles (
		queryId => 1
	)
) AS x;;
```
QueryCreate () - creates new query with specific configuration, making API generate files with data in CSV format (CSV files can be parsed by the connector)
SQL
```
SELECT * 
FROM (
	CALL google_display_video.QueryCreate (
		asynchronous => true,
		reportType => 'TYPE_GENERAL',
		dataRange => 'ALL_TIME',
		groupBys => 'FILTER_DATE,FILTER_COUNTRY_ID',
		metrics => 'METRIC_CLICKS,METRIC_COUNTERS,METRIC_CTR,METRIC_IMPRESSIONS'
	)
) AS x;;
```
Full lists of possible values for parameter reportType can be referenced from the procedure ReportTypes (),
values for dataRange parameter are in the procedure DataRange (),
values for groupBys (dimensions of the report) parameter are in Filters () procedure,
values for metrics (metrics of the report) parameter are in Metrics () procedure.

QueryDelete () - deletes the query defined by parameter queryId

SQL

SELECT * 
FROM (
	CALL google_display_video.QueryDelete (
		queryId => 1
	)
) AS x;;

QueryRun () - runs the query defined by parameter queryId with new dates rage

SQL

SELECT * 
FROM (
	CALL google_display_video.QueryRun (
		queryId => 1,
		asynchronous => true,
		dataRange => 'ALL_TIME'
	)
) AS x;;

Getting Data for Existing Queries

All the queries that exist in the account and have CSV format for result files will appear in connector dynamically as a list of similar procedures named after queries in format:

Q_<not longer than first 96 characters of query title>_<numeric index for coinciding titles of the queries>

The procedures have different list of columns that depend on list of dimensions and metrics relating to the query, but the same set of the parameters:

dataRange - text based definition of the dates range, e.g. ALL_TIME, CURRENT_DAY, CUSTOM_DATES, LAST_14_DAYS, etc. All possible values can be referenced from the procedure DataRange;
SQL
```
CALL google_display_video_src.Q_TYPE_GENERAL_2020_12_24_12_33_28 (
    dataRange => 'PREVIOUS_MONTH'
);;
```

dataStart / dataEnd - range of the custom dates for the report when parameter dataRange is set to CUSTOM_DATES;

SQL

CALL google_display_video_src.Q_TYPE_GENERAL_2020_12_24_12_33_28 (
    dataRange => 'CUSTOM_DATES',
    dataStart => TIMESTAMPADD (SQL_TSI_DAY, -10, CURDATE ()),
    dataEnd => CURDATE ()
);;

reportUrl - can show data from the report with certain URL, the URL can be get from corresponding list of reports received from QueryReportFiles () procedure;

SQL

CALL google_display_video_src.Q_TYPE_GENERAL_2020_12_24_12_33_28 (
    reportUrl => 'https://storage.googleapis.com/dfa_-0000000000000000000000000000000000000000/TYPE_GENERAL_2020-12-24_000000_20210311_000000_00000000_0000000000.csv?GoogleAccessId=00000000000-00000000000000000000000000sm@developer.gserviceaccount.com&Expires=00000000000&Signature=SIGNATURE'
);;

target_table - parameter for saving data into table on a local storage.
SQL
```
CALL google_display_video_src.Q_TYPE_GENERAL_2020_12_24_12_33_28 (
    target_table=> 'dwh.google_display_video_Report_01',
	daysToRedownload => 10
);;
```
If table doesn't exist - it will be created. There could be 2 ways of loading data:

1. Differential append data if the query has groupBy (another explanation - dimensions) parameter contains FILTER_DATE by getting latest date from the stored report data and running new report only for dates range starting from that date (corrected by value of the parameter daysToRedownload) till now;
2. Fully rewriting data when query's parameter groupBy doesn't contains FILTER_DATE.

If new queries were created by API calls or over web interface (format of the report files should be CSV) and they are not shown in the list of connector's procedure, they will appear by refreshing connector's metadata by running menu item "Refresh metadata" (or if the connector metadata are refreshed another way, e.g. connector re-installation, server restart, etc)