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
You requested the OTT ownerships reports to the Uscreen support team (via email@example.com)
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.
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.
Request the OTT ownerships reports from Uscreen - our team can generate them for you upon request. Please write to firstname.lastname@example.org.
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.
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.
Combine the data from both files using a VLOOKUP formula on an empty column.
The VLOOKUP formula looks like this:
V2is the lookup value. In this example, it refers to the Receipt ID from Amazon.
Sheet2is the sheet that contains the data from Uscreen
A:Iis the range of columns that contain the data from Uscreen
- The number
3is the column that contains the email address. You can use the number
4to get the full name.
FALSEreturns 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.