YouTube Analytics Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started¶

YouTube Analytics Version Support

The connector uses version 2 of the YouTube Analytics API.

Establish a Connection¶

Connect to YouTube Analytics¶

YouTube Analytics supports OAuth authentication only. To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth.

The following subsections describe how to authenticate to YouTube Analytics from three common authentication flows:

• Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping. Authenticated via either embedded OAuth or custom OAuth.
• Web: access to data via a shared website. Authenticated via custom OAuth only.
• Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard. Authenticated via embedded OAuth or custom OAuth.

For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that have embedded OAuth credentials, see Creating a Custom OAuth Application.

For a complete list of connection string properties available in YouTube Analytics, see Connection.

Desktop Applications¶

provides an embedded OAuth application that simplifies authentication at the desktop; that is, in situations where the user is using a local server not connected to the internet.

You can also authenticate from the desktop via a custom OAuth application, which you configure and register at the YouTube Analytics console. For further information, see Creating a Custom OAuth Application.

Before you connect, set the following variables:

• InitiateOAuth = GETANDREFRESH. Used to automatically get and refresh the OAuthAccessToken.
• Custom OAuth applications only:
  • OAuthClientId = the client ID assigned when you registered your custom OAuth application.
  • OAuthClientSecret = the client secret assigned when you registered your custom OAuth application.
  • CallbackURL = the redirect URI defined when you registered your custom OAuth application.

When you connect, the connector opens YouTube Analytics's OAuth endpoint in your default browser. Log in and grant permissions to the application.

After you grant permissions to the application, the connector completes the OAuth process:

1. The connector obtains an access token from YouTube Analytics and uses it to request data.
2. The OAuth values are saved in the path specified in OAuthSettingsLocation. These values persist across connections.

When the access token expires, the connector refreshes it automatically.

Automatic refresh of the OAuth access token:

To have the connector automatically refresh the OAuth access token, do the following:

1. The first time you connect to data, set the following connection parameters:
   • InitiateOAuth = REFRESH.
   • OAuthClientId = the client ID in your application settings.
   • OAuthClientSecret = the client secret in your application settings.
   • OAuthAccessToken = the access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation = the path where you want the connector to save the OAuth values, which persist across connections.
2. On subsequent data connections, set the following:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAUth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set the following connection properties:
   • OAuthClientId = the Client ID in your application settings.
   • OAuthClientSecret = the Client Secret in your application settings.
3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Create a Custom OAuth Application¶

Setting up OAuth authentication requires some advance configuration. Once the user account, service account, or GCP Instance account is configured correctly, it is ready to connect.

User Accounts¶

This section describes how to register a custom OAuth application and obtain the OAuthClientId and OAuthClientSecret for User Accounts.

Desktop¶

To register an app and obtain the OAuth client credentials:

1. Log into the Google API Console.
2. Open a project.
3. From the main menu, select the API Manager.
4. In the user consent flow, click Credentials > Create Credentials > OAuth Client Id.
5. Click Other. The Google API Console creates the custom application and displays the OAuthClientId and OAuthClientSecret.
6. Record the OAuthClientId and OAuthClientSecret for later use.
7. Click Library > YouTube Analytics API > Enable API.

Service Accounts¶

To create an OAuth application, generate a private key, and then authorize the service account:

1. Log into the Google API Console.

2. Open a project.

3. From the main menu, select the API Manager.

4. In the user consent flow, click Credentials > Create Credentials > Service Account Key.

5. In the Service Account menu, select either New Service Account (if applicable) or an existing service account.

6. If this is a new service account, select one or more roles. You can assign primitive roles at the project level in the

   IAM and Admin section; other roles enable you to further customize access to Google APIs.

7. In the Key Type section, select P12.

8. Click Create. The Google API Console creates the custom application and displays the key pair and the password for the private key.

9. Download the key pair and set the private key's password in OAuthJWTCertPassword.

10. In the Service Account menu, click Manage Service Accounts.

11. Set OAuthJWTIssuer to the email address displayed in the service account Id field.

12. Click Library > YouTube Analytics API > Enable API.

GCP Instance Accounts¶

When running on a GCP virtual machine, the connector can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.

Retrieve YouTube Analytics Data¶

YouTube Analytics data is organized into various metrics (Viewers, ViewerPercentage, AgeGroup, etc.), which can be queried over various dimensions (Day, DeviceType, etc.). There are many valid combinations of metrics and dimensions. The connector surfaces some of the most commonly used combinations as relational views for ease of use.

Additionally, the connector allows you to query all valid combinations, even those not included in the predefined views. You can use the CreateCustomSchema stored procedure to create a view on a query. Refer to Data Model for how to combine metrics and dimensions in queries and schemas. Below is a guide to getting started with the default views.

Select Dimensions and Measures¶

The dimension and metrics are clearly defined for each table and can be seen in the Data Model: Simply select the metrics and the dimensions you are interested in. For example, a channel's top videos by most minutes watched in a specific country can be selected like this:

SELECT Video, Views, EstimatedMinutesWatched, AverageViewDuration, AverageViewPercentage FROM TopVideos WHERE Country='AL'

Execute SELECT * Queries¶

Unlike most database tables, it is not very helpful to select all metrics and dimensions in a given table. The connector thus interprets the SELECT * query to mean a default set of metrics and dimensions are requested. This includes queries that explicitly select all columns. Refer to the individual view's documentation in Data Model to see which fields are the default for each schema.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the YouTube Analytics connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to YouTube Analytics and then processes the rest of the query in memory (client-side).

User Defined Views¶

The YouTube Analytics connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File¶

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Groups WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views¶

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views¶

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

Data Model¶

The YouTube Analytics connector models YouTube Analytics entities in relational Tables, Views, and Stored Procedures.

YouTube Analytics allows for the querying of Dimensions and Metrics in a large number of arrangements. We provide some sample views based on common YouTube Analytics reports. You can also create your own custom views based on any combination of Dimensions and Metrics you need.

For information about how to view schemas, along with API limitations and requirements, see Defining Custom Views, below.

Tables

You can access Groups and Group Items as Tables.

Stored Procedures

Stored Procedures are function-like interfaces to the data source that can be used to access additional capabilities of the YouTube Analytics API.

Views

Reports are shown as views; that is, as tables that are "snapshots" of the data, and cannot be modified. Unlike traditional database views, it is not very helpful to create a view of an entire table by selecting all metrics and dimensions in that table, because the provider interprets the SELECT * query as if you are requesting a default set of metrics and dimensions. This is also true for queries that explicitly select all columns.

Note that the YouTube Analytics API limits the number and combinations of columns that can be projected over the data or used to restrict the results returned. These limitations and the default fields for each schema are listed in Views. Refer to these sections when defining your own view, as well.

Defining Custom Views¶

Use the CreateCustomSchema stored procedure to create a view on a query. This stored procedure provides an easy way to generate new view definitions with a custom combination of Dimensions and Metrics. Calling it creates a new schema file that you can query like any other view.

The stored procedure takes a view name, a comma-separated list of metrics, a comma-separated list of dimensions, and an output folder as inputs. To access the script files created, you must set Location to the folder containing the new script files. In this sense, Location is a substitute for the output folder.

For example, to use a new schema along with the default schemas, set Location to the db subfolder in the installation folder and call:

EXEC CreateCustomSchema Dimensions='Day,DeviceType', Metrics='Views,EstimatedMinutesWatched', TableName='DailyDeviceReports'

Tables¶

The connector models the data in YouTube Analytics as a list of tables in a relational database that can be queried using standard SQL statements.

YouTube Analytics Connector Tables¶

GroupItems¶

Create, delete, and query Items that compose a Group.

Table-Specific Information¶

Select¶

The following query returns the items that compose the YouTube Analytics groups:

SELECT * FROM GroupItems

The following query returns the items that compose a specific YouTube Analytics group:

SELECT * FROM GroupItems WHERE GroupId = '239_65GrkKS6kRL'

Additionally, OnBehalfOfContentOwner can optionally be used in the WHERE clause.

Insert¶

GroupId and Resource_Id are required to insert an Item into a Group.

INSERT INTO GroupItems (GroupId, Resource_Id) VALUES ('fjgeeljgio54', 'dkhgjio5sfff')

Columns¶

Groups¶

Create, update, delete, and query YouTube Analytics Groups.

Table-Specific Information¶

A Group is a custom collection of up to 200 channels, videos, playlists, or assets. All of the Items in a Group must represent the same type of entity. For example, you cannot create a Group that contains 100 videos and 100 playlists. An Analytics Group can only contain entities that you have uploaded or claimed or that are linked to a channel that you administer. As a result, content owners can create groups of videos, playlists, channels, or assets. Channel owners can create groups of videos and playlists.

Select¶

The following columns can be used in the WHERE clause: OnBehalfOfContentOwner and Mine.

Insert¶

ContentDetails_ItemType is required to insert a Group.

INSERT INTO Groups (Snippet_Title, ContentDetails_ItemType) VALUES ('test2', 'youtube#channel')

Columns¶

Views¶

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

YouTube Analytics Connector Views¶

Activities¶

Query YouTube Activities. An Activity resource contains information about an action that a particular Channel, or User, has taken on YouTube.

Table Specific Information¶

Select¶

YouTube Analytics only allows Channel ID to be used as filter criteria. Additionally, the extra optional columns supported are PublishedAt and Region.

All columns support the '=' operator. PublishedAt also supports '>', '>=', '<', and '<='.

By default, if no filters are specified, the driver will return activities generated by authenticated user.

SELECT * FROM Activities WHERE ChannelId = "abc123" AND PublishedAt < '2016-01-01T10:00:00Z'

Columns¶

Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The ID that YouTube uses to uniquely identify the activity.
PublishedAt	Datetime				The date and time that the activity occurred. The value is in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
ChannelId	String				The ID that YouTube uses to uniquely identify the channel associated with the activity.
Title	String				The title of the resource primarily associated with the activity.
Description	String				The description of the resource primarily associated with the activity.
ChannelTitle	String				Channel title for the channel responsible for this activity.
Type	String				The type of activity that the resource describes. Valid values: channelItem, comment (not currently returned), favorite, like, playlistItem, recommendation, social, subscription, and upload.
GroupId	String				The group ID associated with the activity.
ThumbnailsDefaultUrl	String				The URL of the default image thumbnail.
UploadVideoId	String				The ID that YouTube uses to uniquely identify the uploaded video.
LikeResourceIdKind	String				The type of the resource that received a positive (like) rating.
LikeResourceIdVideoId	String				The ID that YouTube uses to uniquely identify the video, if the rated resource is a video. This column is only present if the ResourceIdKind is youtube#video.
FavoriteResourceIdKind	String				The type of the YouTube entity marked as a favorite.
FavoriteResourceIdVideoId	String				The ID that YouTube uses to uniquely identify the favorite video.
CommentResourceIdKind	String				The comment on the API resource.
CommentResourceIdVideoId	String				The ID that YouTube uses to uniquely identify the video associated with a comment.
CommentResourceIdChannelId	String				The ID that YouTube uses to uniquely identify the channel associated with a comment.
SubscriptionResourceIdKind	String				The type of the subscription. A subscription is a channel that a user subscribed to.
SubscriptionResourceIdChannelId	String				The ID that YouTube uses to uniquely identify the channel that the user subscribed to.
PlaylistItemResourceIdKind	String				The type of the playlist item.
PlaylistItemResourceIdVideoId	String				The ID that YouTube uses to uniquely identify the video that was added to the playlist.
PlaylistItemPlaylistId	String				The value that YouTube uses to uniquely identify the playlist.
PlaylistItemPlaylistItemId	String				The value that YouTube uses to uniquely identify the item in the playlist.
RecommendationResourceIdKind	String				The type of the recommendation.
RecommendationResourceIdVideoId	String				The ID that YouTube uses to uniquely identify the video, if the recommended resource is a video.
RecommendationResourceIdChannelId	String				The ID that YouTube uses to uniquely identify the channel, if the recommended resource is a channel.
RecommendationReason	String				The reason that the YouTube entity was recommended to the user. Valid values for this column : unspecified, videoFavorited, videoLiked, and videoWatched.
RecommendationSeedResourceIdKind	String				The type of the YouTube entity that caused the recommendation.
RecommendationSeedResourceIdVideoId	String				The ID that YouTube uses to uniquely identify the video, if the recommendation was caused by a particular video.
RecommendationSeedResourceIdChannelId	String				The ID that YouTube uses to uniquely identify the channel, if the recommendation was caused by a particular channel.
RecommendationSeedResourceIdPlaylistId	String				The ID that YouTube uses to uniquely identify the playlist, if the recommendation was caused by a particular playlist.
SocialType	String				The name of the social network post. Valid values for this column: facebook, googlePlus, twitter, and unspecified.
SocialResourceIdKind	String				The type of the social network post.
SocialResourceIdVideoId	String				The ID that YouTube uses to uniquely identify the video featured in a social network post, if the post refers to a video.
SocialResourceIdChannelId	String				The ID that YouTube uses to uniquely identify the channel featured in a social network post, if the post refers to a channel.
SocialResourceIdPlaylistId	String				The ID that YouTube uses to uniquely identify the playlist featured in a social network post.
SocialAuthor	String				The author of the social network post.
SocialReferenceUrl	String				The URL of the social network post.
SocialImageUrl	String				An image of the author of the post.
ChannelItemResourceId	String				The ID of the YouTube entity that was added to the channel.

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

AdPerformanceReports¶

Query Ad Performance Reports. Ad Performance Reports provide impression-based metrics for ads that ran during video playbacks. These metrics account for each ad impression, and each video playback can yield multiple impressions.

Table Specific Information¶

Select¶

Get GrossRevenue for a video in a specific country grouped by ad type and day:

SELECT Country, AdType, Day, GrossRevenue, StartDate, EndDate FROM AdPerformanceReports WHERE video = 'vid123' AND continent = '019'

Select Rules¶

Ad performance reports for content owners:

| Dimensions | Required | adType | | -------------- | -------------------------------- | | Optional | day | | Metrics | Use 1 or more | grossRevenue, adImpressions, cpm | | Filter | Use exactly one | video, group, or a supported combination of uploaderType and claimedStatus | | Use 0 or one | country, continent, subContinent |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Captions¶

Query YouTube Captions. A Caption resource represents a YouTube caption track.

Table Specific Information¶

Select¶

YouTube Analytics allows only a subset of columns to be used as filter criteria. The VideoId column must be specified. Extra optional columns supported are ID and OnBehalfOfContentOwner.

All columns support the '=' operator.

SELECT * FROM Captions WHERE VideoId = '123456789'

Columns¶

Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The ID that YouTube uses to uniquely identify the caption track.
VideoId	String				The ID that YouTube uses to uniquely identify the video associated with the caption track.
LastUpdated	Datetime				The date and time when the caption track was last updated. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
TrackKind	String				The type of the caption track. Valid values for this column: ASR (a caption track generated using automatic speech recognition); Forced (a caption track that plays when no other track is selected in the player. For example, a video that shows aliens speaking in an alien language might have a forced caption track to only show subtitles for the alien language); standard (a regular caption track. This is the default value).
Language	String				The language of the caption track. The column value is a BCP-47 language tag.
Name	String				The name of the caption track. The name is intended to be visible to the user as an option during playback.
AudioTrackType	String				The type of audio track associated with the caption track. Valid values for this column: commentary (the caption track corresponds to an alternate audio track that includes commentary, such as director commentary); descriptive (the caption track corresponds to an alternate audio track that includes additional descriptive audio); primary (the caption track corresponds to the primary audio track for the video, which is the audio track normally associated with the video); unknown (this is the default value).
IsCC	Boolean				Indicates whether the track contains closed captions for the deaf and hard of hearing. The default value is false.
IsLarge	Boolean				Indicates whether the caption track uses large text for the vision-impaired. The default value is false.
IsEasyReader	Boolean				Indicates whether the caption track is formatted for easy reader, meaning it is at a third-grade level for language learners. The default value is false.
IsDraft	Boolean				Indicates whether the caption track is a draft. If the value is true, then the track is not publicly visible. The default value is false.
IsAutoSynced	Boolean				Indicates whether YouTube synchronized the caption track to the audio track in the video. The value will be true if a sync was explicitly requested when the caption track was uploaded. If the value is false, YouTube uses the time codes in the uploaded caption track to determine when to display captions.
Status	String				The status of the caption track. Valid values for this column: failed, serving, syncing.
FailureReason	String				The reason that YouTube failed to process the caption track. Valid values: processingFailed (YouTube failed to process the uploaded caption track); unknownFormat (the format of the caption track was not recognized); unsupportedFormat (the format of the caption track is not supported).

Channels¶

Query YouTube Channels.

Table Specific Information¶

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be passed to YouTube Analytics if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

By default, if no filters are specified, the driver will return all channels owned by the authenticated user.

YouTube Analytics allows only a subset of columns to be used as filter criteria. The channels can be filtered by the Id. Extra optional columns supported are Language and ChannelId.

A search against all channels in YouTube Analytics may be triggered by specifying one or more of SearchTerms, ChannelId, Region, SafeSearch, Rating, or Relevance to search all channels.

All columns support the '=' operator. 'PublishedAt', also supports '>', '>=', '<', and '<='.

Order by is supported for the Rating, Relevance, and Title columns.

SELECT * FROM Channels WHERE Id = 'abc123'
SELECT * FROM Channels WHERE ChannelId = 'abc123' AND SearchTerms = 'music|movies-horror' ORDER BY Rating LIMIT 10

Note: Ordering is not supported when specifying one of the colums used for searching channels such as SearchTerms.

Columns¶

Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The ID that YouTube uses to uniquely identify the channel.
Title	String				The title of the channel.
Description	String				The description of the channel. The value of the column has a maximum length of 1000 characters.
CustomUrl	String				The channel's custom URL.
PublishedAt	Datetime				The date and time that the channel was created. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
ThumbnailsDefaultUrl	String				The URL of the default image thumbnail.
DefaultLanguage	String				This column is confidential to trusted testers.
LiveBroadcastContent	String				An indication of whether a video or channel resource has live broadcast content.
LocalizedTitle	String				This column is confidential to trusted testers. The localized video title.
LocalizedDescription	String				This column is confidential to trusted testers. The localized video description.
Country	String				The country with which the channel is associated.
ViewCount	Long				The number of times the channel has been viewed.
SubscriberCount	Long				The number of subscribers that the channel has. This value is rounded down to three significant figures.
HiddenSubscriberCount	Boolean				Indicates whether the subscriber count of the channel is publicly visible.
VideoCount	Long				The number of videos uploaded to the channel.
RelatedPlaylistsLikes	String				The ID of the playlist that contains the liked videos of the channel.
RelatedPlaylistsUploads	String				The ID of the playlist that contains the uploaded videos of the channel.
PrivacyStatus	String				Privacy status of the channel. Valid values for this column: private, public, unlisted.
IsLinked	Boolean				Indicates whether the channel data identifies a user who is already linked to either a YouTube username or a Google+ account. A user who has one of these links already has a public YouTube identity, which is a prerequisite for several actions, such as uploading videos.
LongUploadsStatus	String				Indicates whether the channel is eligible to upload videos that are more than 15 minutes long. This column is only returned if the channel owner authorized the API request. See the YouTube Help Center for more information about this feature.
MadeForKids	Boolean				This value indicates whether the channel is designated as child-directed, and it contains the current 'made for kids' status of the channel.
Keywords	String				Keywords associated with your channel. The value is a space-separated list of strings.
TrackingAnalyticsAccountId	String				The ID for a Google Analytics account that you want to use to track and measure traffic to your channel.
ModerateComments	Boolean				This setting determines whether user-submitted comments left on the channel page need to be approved by the channel owner to be publicly visible. The default value is false.
SearchTerms	String				The SearchTerms parameter specifies the query term to search for.

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ChannelSections¶

Query YouTube ChannelSections. A ChannelSection contains information about a set of Videos that a Channel has chosen to feature.

Table Specific Information¶

Select¶

YouTube Analytics allows only a subset of columns to be used as filter criteria. You can specify exactly one of the following columns: ChannelId or Id. Extra optional columns supported are Language and OnBehalfOfContentOwner.

All columns support the '=' operator.

By default, if no channel ID is specified, the driver will return channel sections for the channel owned by authenticated user.

SELECT * FROM ChannelSections WHERE ChannelId = '123456789'

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Comments¶

Query YouTube Comments.

Table Specific Information¶

Select¶

YouTube Analytics allows only a subset of columns to be used as filter criteria. You must specify exactly one of the following columns: ID or ParentId. Extra optional columns supported are TextFormat.

All columns support the '=' operator.

SELECT * FROM Comments WHERE ID = '123456789'

Columns¶

Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The ID that YouTube uses to uniquely identify the comment.
ChannelId	String				The ID of the YouTube channel associated with the comment. If the comment is a video comment, then this column identifies the channel of the video, and the VideoId column identifies the video. If the comment is a channel comment, then this column identifies the channel that the comment is about.
VideoId	String				The ID of the video that the comment refers to.
TextDisplay	String				The text of the comment. The text can be retrieved in either plaintext or HTML. The TextFormat pseudo column specifies the desired text format.
TextOriginal	String				The original, raw text of the comment as it was initially posted or last updated. The original text is only returned if it is accessible to the authenticated user, which is only guaranteed if the user is the author of the comment.
ParentId	String				The unique ID of the parent comment. This column value is only set if the comment was submitted as a reply to another comment.
AuthorDisplayName	String				The display name of the user who posted the comment.
AuthorProfileImageUrl	String				The URL for the avatar of the user who posted the comment.
AuthorChannelUrl	String				The URL of the YouTube channel of the comment author, if available.
AuthorChannelIdValue	String				The ID of the YouTube channel of the comment author, if available.
AuthorGoogleplusProfileUrl	String				The URL of the Google+ profile of the comment author, if available.
CanRate	Boolean				This setting indicates whether the current viewer can rate the comment.
ViewerRating	String				The rating the viewer has given to this comment. Valid values for this column: like, none. Note that this column does not currently identify dislike ratings. The column value is like if the viewer has rated the comment positively. The value is none in all other cases, including the user having given the comment a negative rating or not having rated the comment.
LikeCount	Integer				The total number of likes (positive ratings) the comment has received.
ModerationStatus	String				The moderation status of the comment. This value is only returned if the API request was authorized by the owner of the channel or the video on which the requested comments were made. In addition, note that this value is not set if the query filtered on the ID column. Valid values: heldForReview, likelySpam, published, rejected.
PublishedAt	Datetime				The date and time when the comment was orignally published. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
UpdatedAt	Datetime				The date and time when the comment was last updated. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CommentThreads¶

Query YouTube CommentThreads. A CommentThread record contains information about a YouTube comment thread, a top-level comment and replies, if any exist, to that comment.

Table Specific Information¶

Select¶

YouTube Analytics allows only a subset of columns to be used as filter criteria. You must specify exactly one of the following columns: AllThreadsRelatedToChannelId, ChannelId, Id, or VideoId.

Extra optional columns supported are ModerationStatus, SearchTerms, and TextFormat. All columns support the '=' operator.

Order is supported for CommentThreads. Relevance are the supported order columns.

SELECT * FROM CommentThreads WHERE VideoId = 'abcdef123456' ORDER BY Relevance DESC LIMIT 10

Note: Ordering is not supported for use in conjunction with the ID parameter.

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Demographics¶

Query YouTubeAnalytics Demographics grouped by age groups and gender.

Table Specific Information¶

Select¶

Aggregate viewing statistics for videos based on viewers' age group and gender:

SELECT * FROM Demographics

Trim down Demographics for just one Video:

SELECT * FROM Demographics WHERE Video = 'video_Id'

Aggregate viewing statistics for playlists based on viewers' age group and gender:

SELECT * FROM Demographics WHERE IsCurated = true

Select Rules¶

You can use the following tables to build other queries to Top Videos.

Demographics for videos:

| Dimensions | Use 1 or more | ageGroup, gender, video | | --------------- | -------------------------------------------------------------------- | | Use 0 or more | creatorContentType, liveOrOnDemand, subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | viewerPercentage | | Filter | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | video, group | | Use 0 or more | liveOrOnDemand, subscribedStatus, youtubeProduct |

Demographics for playlists:

| Dimensions | Use 1 or more | ageGroup, gender | | --------------- | ------------------------------------------ | | Use 0 or more | subscribedStatus, youtubeProduct | | Metrics | Required | viewerPercentage | | Filter | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | video, group | | Use 0 or more | subscribedStatus, youtubeProduct |

Demographics for content owners:

| Dimensions | Use 1 or more | ageGroup, gender | | --------------- | -------------------------------------------------------------------- | | Use 0 or more | creatorContentType, liveOrOnDemand, subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | viewerPercentage | | Filter | Use exactly one | video, channel, group | | Use 0 or one | country, province, continent, subContinent | | Use 0 or more | liveOrOnDemand, subscribedStatus, youtubeProduct |

Demographics for content owner playlists:

| Dimensions | Use 1 or more | ageGroup, gender | | --------------- | ------------------------------------------ | | Use 0 or more | subscribedStatus, youtubeProduct | | Metrics | Required | viewerPercentage | | Filter | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | playlist, channel, group | | Use 0 or more | subscribedStatus, youtubeProduct |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Devices¶

Query YouTubeAnalytics Devices or Operating System statistics. This table allows you to aggregate viewing statistics based on the manner in which viewers reached your video content. For example, you can identify the number of views that occurred on mobile devices or game consoles.

Table Specific Information¶

Select¶

By default, grouping is done by Device Type:

SELECT * FROM Devices

However, you can group views by other dimensions such as Operating System:

SELECT OperatingSystem, Views, EstimatedMinutesWatched FROM Devices

Notice that you cannot mix both Device Type and Operating System in the same query due to limitations in the YouTube Analytics API.

Select Rules¶

You can use the following tables to build other queries to Devices:

Devices for videos:

| Dimensions | Use exactly one | deviceType, operatingSystem | | --------------- | ------------------------------------------------------------------------- | | Use 0 or more | creatorContentType, day, liveOrOnDemand, subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | views, estimatedMinutesWatched | | Filter | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | video, group | | Use 0 or more | operatingSystem, liveOrOnDemand, subscribedStatus, youtubeProduct |

Device data for playlists:

| Dimensions | Use exactly one | deviceType, operatingSystem | | --------------- | ------------------------------------------------- | | Use 0 or more | day, subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | views, estimatedMinutesWatched, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filter | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | playlist, channel, group | | Use 0 or more | operatingSystem, subscribedStatus, youtubeProduct |

Device data for content owners:

| Dimensions | Required | deviceType | | --------------- | ------------------------------------------------------------------------- | | Use 0 or more | creatorContentType, day, liveOrOnDemand, subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | views, estimatedMinutesWatched | | Filter | Use exactly one | video, channel, group | | Use 0 or one | country, province, continent, subContinent | | Use 0 or more | operatingSystem, liveOrOnDemand, subscribedStatus, youtubeProduct |

Device data for content owner playlists:

| Dimensions | Required | deviceType | | --------------- | ------------------------------------------------- | | Use 0 or more | day, subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | views, estimatedMinutesWatched, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filters | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | playlist, channel, group | | Use 0 or more | operatingSystem, subscribedStatus, youtubeProduct |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

EngagementReports¶

Query Engagement Reports info. For example, how many times a user shared a video.

Table Specific Information¶

Select¶

By default, results are grouped by Day. The following query returns Comments, Likes, Dislikes, etc., grouped by Day:

SELECT * FROM EngagementReports

Select Rules¶

The following query returns how frequently the channel's videos were shared on different social platforms:

SELECT SharingService, Shares FROM EngagementReports

You can use the following tables to build other queries to Engagement Reports:

Engagement Reports for videos:

| Dimensions | Required | sharingService | | -------------- | ------------------------------------ | | Optional | creatorContentType, subscribedStatus | | Metrics | Use 1 or more | shares | | Filter | Use 0 or one | country, continent, subContinent | | Use 0 or one | video, group | | Optional | subscribedStatus |

Engagement Reports for content owners:

| Dimensions | Required | sharingService | | -------------- | ------------------------------------ | | Optional | creatorContentType, subscribedStatus | | Metrics | Use 1 or more | shares | | Filter | Use exactly one | video, channel, group | | Use 0 or one | country, continent, subContinent | | Optional | subscribedStatus |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Languages¶

Query YouTube i18nLanguages. An i18nLanguage resource identifies an application language that the YouTube website supports. The application language can also be referred to as a UI language.

Table Specific Information¶

Select¶

There are no required filters for this table. An optional column supported is Language.

All columns support the '=' operator.

Columns¶

PlayListItems¶

Query YouTube PlayListItems. A PlayListItem resource identifies another YouTube entity, such as a Video, that is included in a PlayList. In addition, the PlayListItem record contains details about how that entity is used in that PlayList.

Table Specific Information¶

Select¶

You must specify exactly one of the following columns in the WHERE clause: ID or PlayListId. Extra optional columns supported are OnBehalfOfContentOwner and VideoId.

All columns support the '=' operator.

SELECT * FROM PlaylistItems WHERE PlaylistId = 'abcdef123456'

Columns¶

Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The ID that YouTube uses to uniquely identify the playlist item.
PublishedAt	Datetime				The date and time that the item was added to the playlist. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
ChannelId	String				The ID that YouTube uses to uniquely identify the user that added the item to the playlist.
Title	String				The title of the item.
Description	String				The description of the item.
ThumbnailsDefaultUrl	String				The URL of the item.
ChannelTitle	String				The channel title of the channel that the playlist item belongs to.
VideoOwnerChannelTitle	String				The channel title of the channel that uploaded this video.
VideoOwnerChannelId	String				The channel ID of the channel that uploaded this video.
PlaylistId	String				The ID that YouTube uses to uniquely identify the playlist that the playlist item is in.
Position	String				The order in which the item appears in the playlist. The value uses a zero-based index, so the first item has a position of 0, the second item has a position of 1, and so forth.
ResourceIdKind	String				The kind, or type, of the referred resource.
ResourceIdVideoId	String				If the ResourceIdKind value of the column is youtube#video, then this column will be present and its value will contain the ID that YouTube uses to uniquely identify the video in the playlist.
VideoId	String				The ID that YouTube uses to uniquely identify a video. To retrieve the video resource, set the ID column to this value.
Note	String				A user-generated note for this item.
VideoOwnerPublishedAt	Datetime				The date and time that the video was published to YouTube. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
PrivacyStatus	String				The privacy status of the playlist item.

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

PlayLists¶

Query YouTube PlayLists. A PlayList is a collection of videos that can be viewed sequentially and shared with other users.

Table Specific Information¶

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be passed to YouTube Analytics if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

By default, if no filters are specified, the YouTube Analytics connector will return all playlists for the channel owned by authenticated user.

YouTube Analytics allows only a subset of columns to be used as filter criteria. You can specify exactly one of the following columns: ChannelId or Id. Extra optional columns supported are PublishedAt, Language, OnBehalfOfContentOwner, and OnBehalfOfContentOwnerChannel.

A search against all channels in YouTube Analytics may be triggered by specifying one or more of SearchTerms, ForContentOwner, Region, SafeSearch, Rating and Rating to search all playlists.

All columns support the '=' operator. PublishedAt also supports these additional operators: '>', '>=', '<', and '<='.

Order by is supported for the Rating, Relevance, and Title columns.

SELECT * FROM PlayLists WHERE Id = 'abcdef123456'
SELECT title FROM PlayLists WHERE PublishedAt >= '2016-01-01' ORDER BY Title LIMIT 10

Note: Ordering is not supported when specifying one of the colums used for searching playlists such as SearchTerms.

Columns¶

Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The ID that YouTube uses to uniquely identify the playlist.
PublishedAt	Datetime				The date and time that the playlist was created. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
ChannelId	String				The ID that YouTube uses to uniquely identify the channel that published the playlist.
Title	String				The title of the playlist.
Description	String				The description of the playlist.
ThumbnailsDefaultUrl	String				The URL of the image.
ChannelTitle	String				Channel title of the channel that the video belongs to.
DefaultLanguage	String				This column is confidential to trusted testers. The language of the text in the Title and Description columns.
LocalizedTitle	String				This column is confidential to trusted testers. The localized playlist title.
LocalizedDescription	String				This column is confidential to trusted testers. The localized playlist description.
PrivacyStatus	String				The privacy status of the playlist. Valid values for this column: private, public, unlisted.
VideoCount	Integer				The number of videos in the playlist.
EmbedHtml	String				An iframe tag that embeds a player that will play the playlist.
SearchTerms	String				The SearchTerms parameter specifies the query term to search for.

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Regions¶

Query YouTube i18nRegions. An i18nRegion resource identifies a geographic area that a YouTube user can select as the preferred content region. The content region can also be referred to as a content locale.

Table Specific Information¶

Select¶

There are no required filters for this table. An optional column supported is Language.

All columns support the '=' operator.

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Subscriptions¶

Query YouTube User Subscriptions. A Subscription notifies a User when new Videos are added to a Channel, or when another user takes one of several actions on YouTube, such as uploading a Video, rating a Video, or commenting on a Video.

Table Specific Information¶

Select¶

You can specify exactly one of the following columns: ChannelId, Id, MyRecentSubscribers, or MySubscribers.

Extra optional columns supported are ForChannelId, OnBehalfOfContentOwner, and OnBehalfOfContentOwnerChannel. All columns support the '=' operator.

Order is supported for Subscriptions. 'Alphabetical', 'Relevance', and 'Unread' are the supported order columns.

By default, if no filters are specified, the driver will return a list of channels subscribed by authenticated user.

SELECT Title FROM Subscriptions WHERE ChannelId = 'abc123' ORDER BY Unread DESC

The default value is SUBSCRIPTION_ORDER_RELEVANCE.

Columns¶

Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The ID that YouTube uses to uniquely identify the subscription.
PublishedAt	Datetime				The date and time that the subscription was created. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
ChannelTitle	String				The title of the channel that the subscription belongs to.
Title	String				The title of the subscription.
Description	String				The details of the subscription.
ResourceIdKind	String				The type of the API resource.
ResourceIdChannelId	String				The value that YouTube uses to uniquely identify the channel that the user subscribed to.
ChannelId	String				The ID that YouTube uses to uniquely identify the channel of the subscriber.
ThumbnailsDefaultUrl	String				The URL of the default image.
TotalItemCount	Integer				The approximate number of items that the subscription points to.
NewItemCount	Integer				The number of new items in the subscription since its content was last read.
ActivityType	String				The type of activity this subscription is for. Valid values: all, uploads.
SubscriberTitle	String				The title of the channel of the subscriber.
SubscriberDescription	String				The description of the channel of the subscriber.
SubscriberChannelId	String				The ID that YouTube assigns to uniquely identify the channel of the subscriber.
SubscriberThumbnailsDefaultUrl	String				The URL of the default image.

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

TimeBasedReports¶

Query time-based info on views, subscribers, etc. For example, how many new subscribers were gained at a specific time.

Table-Specific Information¶

Select Rules¶

You can use the following tables to build queries to TimeBased Reports: TimeBased Reports for videos:

| Dimensions | Use 0 or more | day, month, video | | -------------- | ----------------- | | Metrics | Use 1 or more | views, comments, likes, dislikes, videosAddedToPlaylists, videosRemovedFromPlaylists, shares, estimatedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, subscribersGained, subscribersLost, uniques, earnings, adEarnings, grossRevenue, redPartnerRevenue, monetizedPlaybacks, playbackBasedCpm, impressions, impressionBasedCpm | | Filter | Use 0 or one | country, continent, subContinent | | Use 0 or one | video, group |

Timebased Reports for playlist rules:

| Dimensions | Use exactly one | day, month | | --------------- | ------------------------------------------ | | Optional | creatorContentType | | Use 0 or more | subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | views, estimatedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filter | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | playlist, channel, group | | Use 0 or more | subscribedStatus, youtubeProduct |

Timebased Reports for content owner rules:

| Dimensions | Use exactly one | day, month | | -------------- | -------------------------------- | | Optional | creatorContentType | | Metrics | Use 1 or more | views, comments, likes, dislikes, videosAddedToPlaylists, videosRemovedFromPlaylists, shares, estimatedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, subscribersGained, subscribersLost, uniques, earnings, adEarnings, grossRevenue, redPartnerRevenue, monetizedPlaybacks, playbackBasedCpm, impressions, impressionBasedCpm | | Filter | Use exactly one | video, channel, group | | Use 0 or one | country, continent, subContinent |

Timebased Reports for content owner playlist rules:

| Dimensions | Use exactly one | day, month | | --------------- | ------------------------------------------ | | Use 0 or more | subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | views, estimatedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylis | | Filter | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | playlist, channel, group | | Use 0 or more | subscribedStatus, youtubeProduct |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

TopVideos¶

Query the TopVideos for a channel or the TopPlaylists if you are a content owner. You can also use this view to query playback detail and geographic filters.

Table Specific Information¶

Select¶

The following query returns a channel's top videos:

SELECT * FROM TopVideos

The following query returns a channel's top videos by most minutes watched in a specific country:

SELECT Video, Views, EstimatedMinutesWatched, AverageViewDuration, AverageViewPercentage FROM TopVideos WHERE Country = 'US'

The following query returns a channel's top playlists by most minutes watched:

SELECT Playlist, Views, EstimatedMinutesWatched, PlaylistStarts, AverageViewDuration FROM TopVideos WHERE IsCurated = true

Select Rules¶

You can use the following tables to build other queries to Top Videos:

Top Videos for videos:

| Dimensions | Required | video | | -------------- | ------------------------------------- | | Optional | creatorContentType | | Metrics | Use 1 or more | views, comments, likes, dislikes, videosAddedToPlaylists, videosRemovedFromPlaylists, shares, estimatedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, subscribersGained, subscribersLost | | Filter | Use 0 or one | country, continent, subContinent | | Sort | Use 0 or more (only descending order) | views, estimatedMinutesWatched, subscribersGained, subscribersLost |

Top Videos for playlists:

| Dimensions | Required | playlist | | --------------- | ------------------------------------------ | | Metrics | Use 1 or more | views, estimatedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filter | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or more | playlist, subscribedStatus, youtubeProduct | | Sort | Use 0 or more (only descending order) | views, estimatedMinutesWatched, playlistStarts |

Top Videos for content owners:

| Dimensions | Required | video | | -------------- | ------------------------------------- | | Optional | creatorContentType | | Metrics | Use 1 or more | views, comments, likes, dislikes, videosAddedToPlaylists, videosRemovedFromPlaylists, shares, estimatedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, subscribersGained, subscribersLost, earnings, adEarnings, grossRevenue, redPartnerRevenue, monetizedPlaybacks, playbackBasedCpm, impressions, impressionBasedCpm | | Filter | Use exactly one | channel | | Use 0 or one | country, continent, subContinent | | Sort | Use 0 or more (only descending order) | views, earnings, redPartnerRevenue, estimatedMinutesWatched, subscribersGained, subscribersLost |

Top Videos for content owner playlists:

| Dimensions | Required | playlist | | --------------- | ------------------------------------------ | | Metrics | Use 1 or more | views, estimatedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filter | Required | isCurated=true | | Use 0 or one | playlist, channel | | Use 0 or one | country, province, continent, subContinent | | Use 0 or more | subscribedStatus, youtubeProduct | | Sort | Use 0 or more (only descending order) | views, estimatedMinutesWatched, playlistStarts |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

TrafficSources¶

Query TrafficSources. Statistics are based on the manner in which viewers reached your video and playlist content.

Table Specific Information¶

Select¶

The following query aggregates viewing statistics based on the manner in which viewers reached your video content; for example, to identify the number of views that stemmed from a Google search or from a link to a related video.

SELECT * FROM TrafficSources

The following query displays the Views and EstimatedMinutesWatched metrics grouped by the Day and InsightTrafficSourceType dimensions:

SELECT Day, InsightTrafficSourceType, Views, EstimatedMinutesWatched FROM TrafficSources

The following query filters the statistics to be compiled for a specific Video and Country:

SELECT Day, InsightTrafficSourceType, Views, EstimatedMinutesWatched FROM TrafficSources WHERE Video = 'video_id' AND Country = 'US'

The following query aggregates viewing statistics based on the manner in which viewers reached your playlist content; for example, to identify the number of views that stemmed from a Google search.

SELECT InsightTrafficSourceType, Views, EstimatedMinutesWatched, PlaylistStarts, ViewsPerPlaylistStart, AverageTimeInPlaylist FROM TrafficSources WHERE IsCurated = true

The following query returns search terms that generated the most views from YouTube search results for videos located in playlists. Notice that ordering and limiting are required for such a query due to restrictions in the YouTube Analytics API.

SELECT InsightTrafficSourceDetail, Views, EstimatedMinutesWatched, PlaylistStarts, ViewsPerPlaylistStart, AverageTimeInPlaylist FROM TrafficSources WHERE IsCurated = true AND InsightTrafficSourceType = 'YT_SEARCH' ORDER BY Views DESC LIMIT 25

Select Rules¶

You can use the following tables to build other queries to Traffic Sources:

Traffic Sources for videos:

| Dimensions | Use exactly one | insightTrafficSourceType, insightTrafficSourceDetail | | --------------- | --------------------------------------------------------- | | Use 0 or more | creatorContentType, day, liveOrOnDemand, subscribedStatus | | Metrics | Use 1 or more | views, estimatedMinutesWatched | | Filter | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | video, group | | Use 0 or more | liveOrOnDemand, subscribedStatus | | Sort | Use 0 or more(only descending order) | views, estimatedMinutesWatched |

Traffic Sources for playlists:

| Dimensions | Use exactly one | insightTrafficSourceType, insightTrafficSourceDetail | | --------------- | ------------------------------------------ | | Use 0 or more | day, subscribedStatus | | Metrics | Use 1 or more | views, estimatedMinutesWatched, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filter | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | playlist, channel, group | | Optional | subscribedStatus | | Sort | Use 0 or more(only descending order) | views, estimatedMinutesWatched, playlistStarts |

Traffic Sources for content owners:

| Dimensions | Required | insightTrafficSourceType | | --------------- | --------------------------------------------------------- | | Use 0 or more | creatorContentType, day, liveOrOnDemand, subscribedStatus | | Metrics | Use 1 or more | views, estimatedMinutesWatched | | Filter | Use exactly one | video, channel, group | | Use 0 or one | country, province, continent, subContinent | | Use 0 or more | liveOrOnDemand, subscribedStatus |

Traffic Sources for content owner playlists:

| Dimensions | Required | insightTrafficSourceType | | --------------- | ------------------------------------------ | | Use 0 or more | day, subscribedStatus | | Metrics | Use 1 or more | views, estimatedMinutesWatched, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filter | Required | isCurated=true | | Use 0 or one | country, province, continent, subContinent | | Use 0 or one | playlist, channel, group | | Optional | subscribedStatus |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

UserActivityByCity¶

This report provides user activity statistics, sorted by city.

Table Specific Information¶

Select Rules¶

Querying user activity by city requires you to set maxResults to an integer value of 250 or less. You must also specify a value for the sort request parameter.

Video Reports:

Content Owner Reports:

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

UserActivityByCountry¶

This report provides user activity statistics.

Table Specific Information¶

Select Rules¶

User activity by country can only be filtered to return data for a specific video.

Video Reports:

| Dimensions | Required | country | | -------------- | ------------------ | | Optional | creatorContentType | | Metrics | Use 1 or more | views, redViews, comments, likes, dislikes, videosAddedToPlaylists, videosRemovedFromPlaylists, shares, estimatedMinutesWatched, estimatedRedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, cardClickRate, cardTeaserClickRate, cardImpressions, cardTeaserImpressions, cardClicks, cardTeaserClicks, subscribersGained, subscribersLost, estimatedRevenue*, estimatedAdRevenue*, grossRevenue*, estimatedRedPartnerRevenue*, monetizedPlaybacks*, playbackBasedCpm*, adImpressions*, cpm* | | Filters | Use 0 or 1 | continent, subContinent | | Use 0 or 1 | video, group |

Playlist Reports:

| Dimensions | Required | country | | --------------- | -------------------------------- | | Use 0 or more | subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | views, redViews, estimatedMinutesWatched, estimatedRedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filters | Required | isCurated==1 | | Use 0 or 1 | continent, subContinent | | Use 0 or 1 | playlist, group | | Use 0 or more | subscribedStatus, youtubeProduct |

Content Owner Reports:

| Dimensions | Required | country | | -------------- | ----------------------- | | Optional | creatorContentType | | Metrics | Use 1 or more | views, redViews, comments, likes, dislikes, videosAddedToPlaylists, videosRemovedFromPlaylists, shares, estimatedMinutesWatched, estimatedRedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, cardClickRate, cardTeaserClickRate, cardImpressions, cardTeaserImpressions, cardClicks, cardTeaserClicks, subscribersGained, subscribersLost, estimatedRevenue*, estimatedAdRevenue*, grossRevenue*, estimatedRedPartnerRevenue*, monetizedPlaybacks*, playbackBasedCpm*, adImpressions*, cpm* | | Filters | Use exactly 1 | video, channel, group, or a supported combination of uploaderType and claimedStatus | | Use 0 or 1 | continent, subContinent |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

UserActivityByProvince¶

This report provides user activity statistics, sorted by province (includes Washington DC).

Table Specific Information¶

Select Rules¶

User activity by province applies to U.S. states and the District of Columbia. Province requires you to set the filters parameter value to country==US.

Video Reports:

| Dimensions | Required | province | | -------------- | ------------------ | | Optional | creatorContentType | | Metrics | Use 1 or more | views, redViews, estimatedMinutesWatched, estimatedRedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, cardClickRate, cardTeaserClickRate, cardImpressions, cardTeaserImpressions, cardClicks, cardTeaserClicks | | Filters | Required | country==US | | Use 0 or 1 | video, group |

Playlist Reports: This report provides playlist activity statistics for US states and Washington DC. For this report you must include country==US in the filters parameter value.

| Dimensions | Required | province | | --------------- | -------------------------------- | | Use 0 or more | subscribedStatus, youtubeProduct | | Metrics | Use 1 or more | views, redViews, estimatedMinutesWatched, estimatedRedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filters | Required | isCurated==1;country==US | | Use 0 or 1 | continent, subContinent | | Use 0 or 1 | playlist, group | | Use 0 or more | subscribedStatus, youtubeProduct |

Content Owner Reports:

| Dimensions | Required | province | | -------------- | ------------------ | | Optional | creatorContentType | | Metrics | Use 1 or more | views, redViews, estimatedMinutesWatched, estimatedRedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, cardClickRate, cardTeaserClickRate, cardImpressions, cardTeaserImpressions, cardClicks, cardTeaserClicks | | Filters | Use exactly 1 | video, channel, group, or a supported combination of uploaderType and claimedStatus | | Required | country==US |

Content Owner Playlist Reports:

| Dimensions | Required | province | | --------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | Use 0 or more | subscribedStatus, estimatedMinutesWatched, estimatedRedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist | | Filters | Required | isCurated==1;country==US | | Use 0 or 1 | playlist, channel, group | | Use 0 or more | subscribedStatus, youtubeProduct |

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

VideoAbuseReportReasons¶

Query YouTube VideoAbuseReportReasons. A VideoAbuseReportReason resource contains information about a reason that a Video would be flagged for containing abusive content.

Table Specific Information¶

Select¶

There are no required filters for this table. An optional column is Language.

The '=' operator is supported for this column.

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

VideoCategories¶

Query YouTube VideoCategories. A VideoCategory resource identifies a category that has been or could be associated with uploaded Videos.

Table Specific Information¶

Select¶

You can specify exactly one of the following columns: ID or Region. If you do not specify any filtering criteria, by default you will get results for the US Region. An extra optional parameter is Language.

SELECT * FROM VideoCategories WHERE Region = 'JP'

The IN operator can be used to filter on multiple Ids:

SELECT Id, Kind, Etag, ChannelId, Title, Assignable
FROM   VideoCategories
WHERE  (ID IN ('1', '2', '10'))

Columns¶

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Videos¶

Query YouTube Videos.

Table Specific Information¶

Select¶

By default, SupportEnhancedSQL is set to true, and the following will be passed to YouTube Analytics if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

By default, if no filters are specified, the driver will return all videos owned by the authenticated user.

Only one of the following columns may be filtered by at a time: Id, MyRating, ChannelId, or Chart. Extra columns supported are ForMine, Language, ForContentOwner, OnBehalfOfContentOwner.

A search against all videos on YouTube may be executed by specifying SearchTerms, RelatedToVideoId, VideoDuration, VideoCategoryId, or Region.

All columns support the '=' operator. PublishedAt also supports these additional operators: '>', '>=', '<', and '<='. ID and ChannelId supports IN.

Order by is supported for the Date, Rating, Relevance and Title columns.

SELECT * FROM Videos WHERE Id = '12345679'

SELECT * FROM Videos WHERE PublishedAt >= '2016-01-01T20:30:00Z' LIMIT 10
SELECT Description FROM Videos WHERE ForMine = True ORDER BY Date

Note: Ordering is not supported when specifying one of the colums used for searching channels such as SearchTerms.

The IN operator can be used to specify a list of channels. This call returns a list of videos for each specified channel.

SELECT * FROM Videos WHERE ChannelId IN (a, b, c)
SELECT * FROM Videos WHERE ChannelId IN (SELECT ID FROM Channels)

IN operator can also be used for getting data for multiple videos.

SELECT * FROM Videos WHERE ID IN (a, b, c)

Columns¶

Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The ID that YouTube uses to uniquely identify the video.
ChannelId	String				The ID that YouTube uses to uniquely identify the channel that the video was uploaded to.
CategoryId	String				The YouTube video category associated with the video.
Title	String				The title of the video.
ChannelTitle	String				Channel title for the channel that the video belongs to.
PublishedAt	Datetime				The date and time that the video was published. Note that this time might be different than the time that the video was uploaded. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sZ) format.
Description	String				The description of the video.
Duration	String				The length of the video. The property value is an ISO 8601 duration.
ViewCount	Long				The number of times the video has been viewed.
LikeCount	Long				The number of users who have indicated that they liked the video.
DislikeCount	Long				The number of users who have indicated that they disliked the video. In conjunction with changes to make video dislike counts private across the entire YouTube platform, this field is returned only if the API request was authenticated by the video owner.
CommentCount	Long				The number of comments for the video.
EmbedHtml	String				An iframe tag that embeds a player that will play the video.
PublicStatsViewable	Boolean				This value indicates whether the extended video statistics on the watch page are publicly viewable. By default, those statistics are viewable, and statistics like the viewcount and ratings will still be publicly visible even if this value is set to false.
TagsAggregate	String				A list of keyword tags associated with the video.
LiveBroadcastContent	String				Indicates if the video is an upcoming/active live broadcast. A value of none indicates the video is not an upcoming/active live broadcast. Valid values for this column: live, none, upcoming.
Dimension	String				Indicates whether the video is available in 3D or in 2D.
Definition	String				Indicates whether the video is available in high definition HD or only in standard definition. Valid values for this column: hd, sd.
Caption	String				Indicates whether captions are available for the video. Valid values for this column: false, true.
LicensedContent	Boolean				Indicates whether the video represents licensed content, which means that the content was uploaded to a channel linked to a YouTube content partner and then claimed by that partner.
AllowedAggregate	String				A list of region codes that identify countries where the video is viewable.
BlockedAggregate	String				A list of region codes that identify countries where the video is blocked.
Projection	String				Specifies the projection format of the video. Valid values: 360, rectangular.
PrivacyStatus	String				The privacy status of the playlist. Valid values: private, public, unlisted.
MadeForKids	Boolean				This value indicates whether the video is Made For Kids.
License	String				The license. Valid values: creativeCommon, youtube.
Embeddable	Boolean				This value indicates whether the video can be embedded on another website.
ThumbnailsDefaultUrl	String				The URL of the default image.
DefaultLanguage	String				This column is confidential to trusted testers. The language of the text in the Title and Description columns.
DefaultAudioLanguage	String				The default_audio_language column specifies the language spoken in the default audio track of the video.
UploadStatus	String				The status of the uploaded video. Valid values for this column: deleted, failed, processed, rejected, uploaded.
FailureReason	String				This value explains why a video failed to upload. This column only has a value if the UploadStatus column indicates that the upload failed. Valid values for this column: codec, conversion, emptyFile, invalidFile, tooSmall, uploadAborted.
RejectionReason	String				This value explains why YouTube rejected an uploaded video. This column only has a value if the UploadStatus column indicates that the upload was rejected. Valid values for this column: claim, copyright, duplicate, inappropriate, length, termsOfUse, trademark, uploaderAccountClosed, uploaderAccountSuspended.
RecordingDate	Datetime				The date and time when the video was recorded. The value is specified in ISO 8601 (YYYY-MM-DDThhss.sssZ) format.
SearchTerms	String				The query term to search for.

Pseudo-Columns¶

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Stored Procedures¶

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with YouTube Analytics.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from YouTube Analytics, along with an indication of whether the procedure succeeded or failed.

YouTube Analytics Connector Stored Procedures¶

CreateCustomSchema¶

Creates a custom schema file based on the specified Dimensions and Metrics.

CreateCustomSchema¶

Creates a custom schema file based on the specified Dimensions and Metrics.

A custom schema may be used for a more tailored approach to your data. Custom options may include comma-separated lists for specific data.

Input¶

Result Set Columns¶

GetOAuthAccessToken¶

Obtains the OAuth access token to be used for authentication with various Google services.

Input¶

Result Set Columns¶

GetOAuthAuthorizationURL¶

Obtains the OAuth authorization URL used for authentication with various Google services.

Input¶

Result Set Columns¶

RefreshOAuthAccessToken¶

Obtains the OAuth access token to be used for authentication with various Google services.

Input¶

Result Set Columns¶

System Tables¶

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables¶

The following tables return database metadata for YouTube Analytics:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables¶

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables¶

The following table returns query statistics for data modification queries:

• sys_identity: Returns information about batch operations or single updates.

sys_catalogs¶

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns¶

sys_schemas¶

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns¶

sys_tables¶

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns¶

sys_tablecolumns¶

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Groups table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Groups'

Columns¶

sys_procedures¶

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns¶

sys_procedureparameters¶

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the RefreshOAuthAccessToken stored procedure: