Identify and Delete Low Performing Videos

In this topic, you will learn how to use Video Cloud Studio and a spreadsheet to identify and delete low performing videos.

Introduction

 

For a number of reasons, it is probably best to not have videos in your library that are not viewed, and you guess they won't be viewed in the future either. In this document you will see how to identify these videos and remove them from your library. The following bullet points provide a high level overview of the process. The detailed steps then follow.

  • Use the Media module to export a file that contains a row of information for every video in your library.
  • Use the Analytics module to create a custom report that includes the video ID, title, video impressions and video views. The report will be exported in a comma separated values (CSV) format and list only those videos with non-zero video views and/or impressions.
  • Import both the CSV files into a Google Docs spreadsheet.
  • Add a column to the all videos spreadsheet that determines if the video detailed in a particular row has video views or not. This is done by checking to see if an ID in the all videos spreadsheet has a matching entry in the Analytics report spreadsheet.
  • Copy the IDs of the videos with no views to a Google Doc and manipulate the format to use in a bulk deletion process.

Export all videos data

From the Media module you can get a report on all media in your library. This will be used later in a spreadsheet.

  1. Open the Media module.
  2. Click on the Export button near the top-right of the page.
    export button
  3. In the dialog that opens, be sure All videos in the list is selected.
  4. Click the Export Data button. Note that this report will export information for a maximum of 15,000 videos.
  5. When the report has finished building, click the Download File button.
  6. Note that the file name corresponds to your account ID, and the file is CSV (comma separated value) formatted.
  7. Click Cancel to close the dialog.

Create custom Analytics report

To create the Analytics report that includes videos with views, perform the following steps:

  1. Open the Analytics module.
  2. On the left side menu, click Custom.
  3. Use the Include Accounts dropdown to choose the desired account(s) for the report.
  4. Choose the Report Type to be One-time.
  5. Select a Date Range for the report using the calendars.
  6. For the Data Groups choose Video.
  7. For the Data Fields choose the three fields Video Name, Video Views and Video Impressions:
    data fields
  8. Below the fields you see a preview of your report. Note that the video ID is automatically included in the report:
    report preview
  9. Supply a Report Name or accept the default.
  10. Choose the Format to be CSV.
  11. Choose to get an email or not when the report is ready. Mostly likely you will just wait for the report unless your account contains a very large number of videos.
  12. Click the Request Report button.
  13. After some delay, you will see the report is downloaded.

Import the all videos file

In this section of the document you will open the exported all videos report created from the Media module.

  1. Open a Google Docs blank spreadsheet.
  2. Select File > Import > Upload, then either drag the all videos CSV file (named after your account ID) to the proper location or browse to select it.
  3. On the Import file dialog, accept the defaults and click Import data:
    import csv
  4. Your imported data will appear similar to the following:
    sample import all videos

Import Analytics report

You will now import into another sheet (in the same spreadsheet group) the Analytics report you created.

  1. Select File > Import > Upload, then either drag the Analytics report CSV file to the proper location or browse to select it.
  2. On the Import file dialog, BE SURE to change the Import location to Insert new sheet(s). Accept the other defaults and click Import data:
    import analytics
  3. Your imported data will appear similar to the following:
    sample import
  4. Note that at the bottom of the spreadsheet you see the two sheets listed.
    two sheets

Create a named range

You will now add a column that will determine if a video has any views. To do that, you will need to reference the IDs in the Analytics report (those videos with views). To make the process easier you will now create and name a range.

  1. While still in the Analytics report sheet, click on the uppercase A at the top of the first column.
  2. Right click somewhere in the column and select View more column actions > Define named range
  3. A panel will open on the right side of the spreadsheet. Change the default name of NamedRange1 to IDsWithViews.
  4. After pressing enter, you will see the range defined.
    named range

Add column for no views determination

You will now add a column and add a VLOOKUP() function which will report if a specific video has any views.

  1. Return to the all videos sheet, named with your account ID.
  2. Click on column B.
  3. From the spreadsheet menu, select Insert > Column left. This creates a new, blank column B.
  4. Click in cell B2.
  5. Enter =VLOOKUP(. This starts the use of the function.
  6. Just after the opening parentheses, click on the A2 cell. That cell location will be inserted. This defines for what the function will be looking.
  7. Enter a comma after the cell, then type id. You will see you can select the named range, and do select it.
    select named range
    This defines where the function will be looking for the value in A2.
  8. Enter a comma after the named range, then type the number 1. This defines the column index to be returned. For you, this means that if the ID in A2 is found in the named range, the ID will be placed in the cell.
  9. Enter a comma after the number 1, then enter FALSE, then type the closing parentheses. This option tells the VLOOKUP() function if columns are sorted and the exact value cannot be found, the closest value should be returned. Since you do not want this to occur, FALSE is the correct value to enter.
  10. Ensure your function is defined correctly, as shown here, and press Enter.
    complete vlookup
    Cell B2 will either display the ID from A2 , or #N/A.
  11. Click back in cell B2 and be sure it is highlighted.
  12. To copy the formula down the entire spreadsheet, double click on the blue square at the bottom right of the highlighted cell, which is currently B2.
    blue square
    In column B you will now see either IDs repeated from column A, or #N/A. If you see the ID, it tells you that the video represented by that ID was found in the Analytics report. In other words, it has video views. And as you can surmise, if you see #N/A the video represented by the ID in column A has no views.

Delete videos with no views

You will now sort the spreadsheet so all videos IDs with no views are grouped together. You will then copy those IDs and format for bulk video deletion.

  1. Highlight column B, then from the spreadsheet menu select Data > Sort sheet by column B, Z -> A. This groups all the videos with no views at the top of the spreadsheet, represented by #N/A in the cell.
  2. You now have two options on how to proceed. They are:
    • If you have a small number of videos to delete, you can simply go into Studio and manually delete in the MEDIA module.
    • If you have a significant number of videos to delete, you may wish to continue to perform the following steps to ease the burden of deleting the videos.
  3. Highlight, then copy, ONLY the video IDs for the videos you wish to delete. Do not include any videos that are missing an ID or name as this means the video is already deleted.
    copy IDs
  4. Open a Google Doc document.
  5. From the document menu, select Edit > Paste without formatting. You will simply see the video IDs in a column. To bulk erase the videos they must be in the format id:12345678 id:23456789 .... Rather than editing the list by hand, continue with these steps.
  6. Select Edit > Find and replace...
  7. For the Find value enter \n. This represents a carriage return.
  8. For the Replace with value enter a space followed by id:. The space in front of the id: is crucial for correct formatting.
  9. Check the Match using regular expressions checkbox.
  10. Click the Replace all button, then close the dialog.
  11. Ensure your list of video IDs now appears similarly formatted to following: 
      1754261702001 id:4082515861001 id:5185914204001 id:5324448023001
  id:1754261492001 id:2180442442001 id:2535301903001 id:4713495235001
  id:4874718207001 id:4997278349001 id:5686632029001 id:5998203290001
  id:5998205402001 id:5998658922001 id:5998662608001 id:
  12. To finish the formatting, you will need to place an id: in front of the first video ID. Then remove the id: at the end of the list that has no matching actual video ID.
  13. Copy the correctly formatted list of video IDs. 
      id:1754261702001 id:4082515861001 id:5185914204001 id:5324448023001
  id:1754261492001 id:2180442442001 id:2535301903001 id:4713495235001
  id:4874718207001 id:4997278349001 id:5686632029001 id:5998203290001
  id:5998205402001 id:5998658922001 id:5998662608001
  14. Open the MEDIA module.
  15. Paste your list of video IDs into the search box:
    media module search
  16. Press Enter.
  17. You will now see all the videos listed that match the IDs you selected earlier. You can now individually select videos to be deleted or check the select all box and delete the videos.