Skip to main content
All CollectionsMobile and TV AppsManaging Apps
Reconcile financial reports between OTT marketplaces and Uscreen
Reconcile financial reports between OTT marketplaces and Uscreen

Use spreadsheet software such as Microsoft Excel to combine and reconcile data from different sources

Updated over 3 years ago

At Uscreen, we understand the importance of having sufficient and accurate analytical data. When it comes to OTT marketplaces, we must abide by their limitations or difficulties integrating certain features because every marketplace has its specificities.

Thanks to its "universal" approach, importing CSV files into Microsoft Excel is commonly used to combine data from various sources.

For example, in one spreadsheet, you can combine the user identifying data from Uscreen and the accurate sales data from marketplaces.

Example of data you can merge

Pre-requisites

  • You requested the OTT ownerships reports to the Uscreen support team (via support@uscreen.tv)

  • You downloaded the reports from the marketplaces.

Disclaimer: Please keep in mind that you need some proficiency in using Microsoft Excel as an analytical tool to follow this guide.

Please consider that the accurate sales information is from the marketplaces. You may want to merge the data collected from Uscreen to their report and not the other way around. As a result of this, you will add the user identifying information collected by Uscreen to the sales information collected by the marketplace.

Instructions

  1. Download the reports from the marketplaces. Please find more information here:

    Note: Apple (iOS and tvOS) do not provide this information to you or Uscreen.

  2. Request the OTT ownerships reports from Uscreen - our team can generate them for you upon request. Please write to support@uscreen.tv.

  3. Follow the steps to Import CSV data to Microsoft Excel and import the CSV file from the marketplace (Amazon, Google, or Roku) and the OTT ownerships reports provided by Uscreen.

  4. If necessary, use search-replace to convert the decimal delimiter from period to comma. For example, Microsoft Excel will not recognize the value 9.99 as a number, and you need to change it to 9,99. Select the numerical columns, then use Ctrl + F (Cmd + F on Mac) and insert a period in search and a comma in the replace field.

  5. Combine the data from both files using a VLOOKUP formula on an empty column.


    The VLOOKUP formula looks like this:

    =VLOOKUP(V2;Sheet2!A:I;3;FALSE)


    - V2 is the lookup value. In this example, it refers to the Receipt ID from Amazon.
    - Sheet2 is the sheet that contains the data from Uscreen
    - A:I is the range of columns that contain the data from Uscreen
    - The number 3 is the column that contains the email address. You can use the number 4 to get the full name.
    - FALSE returns a value only if there's an exact match.

Finally, you can explore Microsoft Excel's features, such as pivot tables to create custom reports.

Related Articles
Work with Uscreen Data in CSV Format
Work with Uscreen and Stripe Data in CSV Format
Did this answer your question?