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 the export icon near the top-right of the page. The Export Videos Data dialog opens.
    Export Videos Data dialog
    Export Videos Data dialog
  3. Make sure All rows in the list is selected. Note that the export includes up to 15,000 rows.
  4. Optionally tick Include current URLs for video renditions if you also want rendition URLs in the file.
  5. Click Export. The CSV file is downloaded to your computer; the file name corresponds to your account ID.

Create custom Analytics report

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

  1. Open the Analytics module.
  2. In the left side menu, click Analytics Reports (under the Reports group).
  3. Click New Report in the top-right corner. The New Report dialog opens with two panels: configuration on the left and a live Report Preview on the right.
    New Report dialog
    New Report dialog with live preview
  4. In the Accounts and Dates section, accept the default Report Name or type a new one.
  5. Use the Accounts Included dropdown to pick the account(s) for the report.
  6. For Report Type, select One-time.
  7. Pick a Start Date and End Date for the report.
  8. For Format, choose CSV.
  9. Use the Email me when the report is ready toggle to control whether a notification email is sent. Add additional recipients under Additional Email Recipients if needed.
  10. Expand the Data section. In the Data Groups picker, select Video. This sets each row of the report to a single video and unlocks the video-level fields.
  11. In the Data Group Fields list, check Video Name. Scroll down to Performance Data and check Video Views and Video Impressions. The Report Preview updates with sample data as you tick the boxes; the video ID column is included automatically.
    Data section with Video Views and Video Impressions checked
    Performance Data fields selected and reflected in the preview
  12. Click Create. The report is generated; after a short delay the CSV file 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. Note that at the bottom of the spreadsheet you can see the two sheets listed.
    sample import with 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.