Import YouTube Analytics Data to Google Sheets

YouTube provides several different APIs for extracting data from the platform:

  • The YouTube Data API is designed for adding YouTube functionality into your own website. As part of this functionality, it allows you to retrieve lists of content based on search parameters. It also provides some basic statistics like views, likes, comment counts, and subscriber counts, for all public videos.
  • The YouTube Reporting API is focused on reporting, and provides bulk reports of YouTube analytics data for your own channel, including detailed metrics and ad performance reports. These bulk reports are generated daily and are designed to be downloaded and stored in a data warehouse, where you can then query the data yourself.
  • The YouTube Analytics API is very similar to the Reporting API, but instead of daily bulk reports, it lets you target your queries through filtering and sorting parameters. For example, you can specify a date range in your request, which is not possible in the Reporting API. This table provides a summary of the differences between YouTube’s Reporting and Analytics APIs: https://developers.google.com/youtube/reporting#key-differences

This guide will focus on the YouTube Analytics API. We will pull YouTube video data directly into Google Sheets, using the API Connector add-on for Sheets. The YouTube Analytics API only supports authentication via OAuth2, so we’ll be connecting using API Connector’s built-in OAuth2 integration for YouTube Analytics.

YouTube’s Data API is discussed in this article.

CONTENTS

PART 1: CONNECT TO THE YOUTUBE ANALYTICS API

If you haven’t connected API Connector to YouTube Analytics before, you’ll first need to initiate the connection as follows:

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. Under Authentication, click Manage connections.
    youtube-analytics-api-img1
  3. In the list of available connections, find YouTube Analytics and click Connect.
    youtube-analytics-api-img2
  4. You will be directed to Google and asked to sign in and authorize the connection. Click through.
    youtube-analytics-api-img3
  5. You’ll now be returned to your Google Sheet, and can verify that your YouTube Analytics connection is active in the Connections screen.
    youtube-analytics-api-img4

PART 2: CREATE A YOUTUBE ANALYTICS API REQUEST URL

For our first request, we’ll get some basic information about your YouTube account. Again, the YouTube Analytics API only lets you query your own data. If you’re looking for public YouTube data, you’ll need the YouTube Data API.

  • API root: https://youtubeanalytics.googleapis.com
  • Endpoint: /v2/reports
  • Parameters: ?dimensions=video&sort=-views&metrics=estimatedMinutesWatched,views,likes,subscribersGained&ids=channel==MINE&startDate=2020-05-01&endDate=2020-06-01&maxResults=10

Putting it together, we get the full API Request URL.

https://youtubeanalytics.googleapis.com/v2/reports?dimensions=video&sort=-views&metrics=estimatedMinutesWatched,views,likes,subscribersGained&ids=channel==MINE&startDate=2020-05-01&endDate=2020-06-01&maxResults=10

PART 3: PULL YOUTUBE ANALYTICS API DATA INTO SHEETS

We can now enter our values into API Connector and start importing YouTube data into Google Sheets.

  1. In the Create Request interface, enter the Request URL we just created.
    youtube-analytics-api-img6
  2. We don’t need any headers for this API, so just leave that section blank.
  3. Under Authentication, choose YouTube Analytics from the Connections dropdown.
    youtube-analytics-api-img5
  4. Create a new tab. You can call it whatever you like. Click ‘Set current’ to use that tab as your data destination.
  5. Under Output Options, choose Compact style. The YouTube Analytics API outputs data in an unconventional format, and this style provides a clearer view of the data.
  6. Name your request and click Run. A moment later you’ll see some information about your account populate your sheet.
    youtube-analytics-api-img7
  7. Tip: for an alternative view of the data, try “default (single row)” report style and enter the following JMESPath: {Keys:columnHeaders[].name,rows:rows}. This will split your data into separate columns.
    youtube-analytics-api-img8

PART 4: MORE EXAMPLE API URLS

You can access the full YouTube Analytics API documentation here, but if you just want to get started, you can try the following URLs (one at a time).

  • Aggregated metrics for a channel
    https://youtubeanalytics.googleapis.com/v2/reports?metrics=views,comments,likes,dislikes,estimatedMinutesWatched,averageViewDuration&ids=channel==MINE&startDate=2019-05-01&endDate=2020-09-01&maxResults=10
  • Most watched videos for your channel
    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=video&metrics=estimatedMinutesWatched,views,likes,subscribersGained&maxResults=10&sort=-estimatedMinutesWatched&startDate=2019-05-01&endDate=2020-09-01&maxResults=10&ids=channel==MINE
  • Metrics summarized by month
    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=month&metrics=views,estimatedMinutesWatched,averageViewDuration,averageViewPercentage,subscribersGained&sort=month&startDate=2019-05-01&endDate=2020-09-01&ids=channel==MINE
  • Demographic breakdown
    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=ageGroup,gender&metrics=viewerPercentage&startDate=2019-05-01&endDate=2020-09-01&ids=channel==MINE
  • View counts and watch time by traffic source
    https://youtubeanalytics.googleapis.com/v2/reports?dimensions=day,insightTrafficSourceType&metrics=views,estimatedMinutesWatched&sort=day&startDate=2019-05-01&endDate=2020-09-01&ids=channel==MINE

Many additional sample queries are provided in YouTube’s list of sample requests. In addition, they provide an API Explorer tool that simplifies the process of testing and constructing queries.

Leave a Comment