thomas-shirley.com

Rapidly generating EMA avails and CSV files

For those who are unaware, Entertainment Merchants Association (EMA) avails are documents that contain details about a piece of licensed video content. EMA Avails have a standardised structure that allow both digital video retailers (e.g. Amazon) and media producers (e.g. Paramount Pictures) to have an agreed way of exchanging information about licensed video content. Details like pricing, release dates, withdrawal dates and other associated metadata are contained in these documents.

These documents are industry standard - Amazon Prime Video and Disney+ can expect the same standardised documents from Warner Brothers, Universal and Lionsgate alike.

I recently decided to automate some hitherto (very boring) generation of these EMA compliant avails documents.

Despite being standardised, different retailers have squeezed their own requirements into these spreadsheets. The result is repetitive copying, pasting and mental gymnastics to reconcile the differences between them all.

It is clear that this process could be automated. I suspect that this solution will be useful to few people. I hope it helps someone, somewhere.

Shell Script

It is not possible to completely avoid direct data input in this process, but we can:

Remove the need to open any sort of spreadsheet software
Automate the repetitive copying of data into the same .CSV or Excel document
Prompt for input sequentially and automate copying
Increment numbered fields automatically
Generate associated metadata documentation in parallel

The focus of my shell script is for a fictional digital platform. The script could be easily adapted for any digital video retailer.

Define EMA avail row headings first

Starting with the EMA avails documents, we include in the necessary row headings.

You can find the latest versions on the Movie Labs website.

The STUDIO_ROW_HEADINGS line of data will vary between organisations. I have intentionally left the studio specific fields as 'Generic'.

##Create the EMA avails headings & Rows
headings1=(
"Avail" "Avail" "AvailTrans" "AvailTrans" "AvailTrans" "AvailAsset" "Disposition" "AvailMetadata" "AvailMetadata" "AvailMetadata" "AvailMetadata" "AvailMetadata" "AvailMetadata" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailAsset" "AvailAsset" "AvailAsset" "AvailAsset" "Avail" "Avail" "Avail" "Avail" "AvailAsset" "Avail" "AvailTrans" "AvailTrans" "AvailTrans" "AvailMetadata" "AvailMetadata" "AvailMetadata" "Avail" "AvailMetadata" "AvailMetadata" "AvailMetadata" "AvailTrans" "AvailTrans" "AvailMetadata" "AvailMetadata" "AvailMetadata" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "AvailTrans" "Avail" "AvailMetadata" "AvailTrans" "AvailTrans" "AvailTrans" "Avail" "AvailMetadata" "AvailMetadata" "AvailMetadata" "Avail" "Avail" "Avail"
)
## create an array with the headings for the second row
headings2=(
"DisplayName" "Licensee" "AssetLanguage" "Territory" "TerritoryExclusion" "WorkType" "EntryType" "TitleInternalAlias" "TitleDisplayUnlimited" "LocalizationType" "CompanyDisplayCredit" "GroupIdentity" "Director" "LicenseType" "LicenseRightsDescription" "FormatProfile" "HDR" "WCG" "HFR" "NGAudio" "Start" "StartLag" "End" "EndLag" "WindowDuration" "PriceType" "PriceValue" "PriceCurrency" "SRP" "Description" "Download" "OtherTerms" "OtherInstructions" "TitleID" "EditID" "AltID" "ContentID" "AvailID" "UV_ID" "DMA_ID" "ReportingID" "Metadata" "ALID" "SuppressionLiftDate" "SpecialPreOrderFulfillDate" "AnnounceDate" "ReleaseYear" "ReleaseHistoryOriginal" "ReleaseHistoryPhysicalHV" "ExceptionFlag" "RatingSystem" "RatingValue" "RatingReason" "RentalDuration" "WatchDuration" "CaptionIncluded" "CaptionExemption" "Any" "ContractID" "TitleStatus" "Exclusive" "ExclusiveAttributes" "BrandingRights" "BrandingRightsAttributes" "ServiceProvider" "TotalRunTime" "HoldbackLanguage" "AllowedLanguages" "RequiredFulfillmentLanguages" "BundleALIDs" "RetailerID1" "RetailerID2" "RetailerID3" "RetailerSpare1" "RetailerSpare2" "RetailerSpare3"
)
headings3=(
"//REQ" "//Opt" "//Opt" "//REQ" "//Opt" "//REQ" "//REQ" "//REQ" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//REQ" "//Opt" "//REQ" "//Opt" "//Opt" "//Opt" "//Opt" "//REQ" "//Opt" "//REQ" "//Opt" "//Opt" "//REQ for all but SVOD LicenseType" "//REQ for all but SVOD LicenseType" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//REQ" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//REQ" "//REQUIRED for pre-orders" "//Optional for preorders. Should be blank for all other license types. " "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//REQUIRED for episodes in US. " "//REQUIRED in US if captions are *not* included." "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt" "//Opt"
)

Next we need to define the territories the generator will support. Once we've done that, split the heading strings up at the spaces and conform them into comma separated values:

TERRITORIES=("GB","AU","DE","FR","IT","ES","MX","BR","JP");

## join each array with commas to create the CSV rows
csv_row1=$(printf "%s," "${headings1[@]}")
csv_row1=${csv_row1%,}

csv_row2=$(printf "%s," "${headings2[@]}")
csv_row2=${csv_row2%,}

csv_row3=$(printf "%s," "${headings3[@]}")
csv_row3=${csv_row3%,}

That's all our variables with fixed values taken care of. Next we need to write the code which will collect user input, based on the desired output:

echo -e ${green_on}'Which territory is this for?'${green_off};
for i in $TERRITORIES; do
       echo -e "$i \n";
done

read TERRITORY_NAME;
echo -e ${green_on}'What is the name of the piece?'${green_off} "INFO: e.g. My Cool Free Bonus."
read LOCAL_TITLE_NAME;
echo -e ${green_on}'What is the piece titled locally?' ${green_off} "INFO: e.g. My Cool Free Bonus - UK Edition";
read LOCAL_PEICE_TITLE;
echo -e ${green_on}'What is the local release date? (YYYY-MM-DD)'${green_off};
read LOCAL_RELEASE_DATE;
echo -e ${green_on}'What is the SKU identifier?' ${green_off};
read LOCAL_PEICE_SKU_IDENTIFER;

That's all the data we need to capture from the user. This process isso much more succinct than trawling through excel documents to input data field-by-field. There aren't actually many fields to complete, just a lot of copying. Therein lies the beauty of this script - it handles the duplicate entries.

Define the output data, interpolating the fields with user-input:

MARKET_DATA_GB="STUDIO_NAME, , EN, GB, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , FVOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , , , , , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";
MARKET_DATA_MX="STUDIO_NAME, , es-419, MX, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , VOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , ,WSP,0, , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";
MARKET_DATA_AU="STUDIO_NAME, , EN, AU, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , VOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , ,WSP,0, , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";
MARKET_DATA_BR="STUDIO_NAME, , PT, BR, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , VOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , ,WSP,0, , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";
MARKET_DATA_DE="STUDIO_NAME, , DE, DE, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , FVOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , , , , , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";
MARKET_DATA_FR="STUDIO_NAME, , FR, FR, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , VOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , ,WSP ,0 , , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";
MARKET_DATA_ES="STUDIO_NAME, , ES, ES, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , VOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , ,WSP ,0 , , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";
MARKET_DATA_IT="STUDIO_NAME, , IT, IT, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , VOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , ,WSP ,0 , , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";
MARKET_DATA_JP="STUDIO_NAME, , JP, JP, , Movie, Full Extract, ${LOCAL_TITLE_NAME}, , , , , , FVOD, New Release, HD, , , , , ${LOCAL_RELEASE_DATE}, , Open, , , , , , , , , , , , ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, ${LOCAL_PEICE_SKU_IDENTIFER}, , , , , ,${LOCAL_PEICE_SKU_IDENTIFER} , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,";

Next up, we start writing the data to files:

echo "Generating EMA compliant avails for Example_Global_Store in ${TERRITORY_NAME} with the filename: ${LOCAL_PEICE_SKU_IDENTIFER}.csv";

##Write the basic row headings to the csv file first.
echo $csv_row1 > ${LOCAL_PEICE_SKU_IDENTIFER}.csv
echo $csv_row2 >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
echo $csv_row3 >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv

And finally, add the requested row, including the data input by the user:

case $TERRITORY_NAME in 
    GB)
    echo $MARKET_DATA_GB >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
    AU)
    echo $MARKET_DATA_AU >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
    MX)
    echo $MARKET_DATA_MX >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
    BR)
    echo $MARKET_DATA_BR >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
    FR)
    echo $MARKET_DATA_FR >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
    ES)
    echo $MARKET_DATA_ES >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
    IT)
    echo $MARKET_DATA_IT >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
    JP)
    echo $MARKET_DATA_IT >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
    DE)
    echo $MARKET_DATA_DE >> ${LOCAL_PEICE_SKU_IDENTIFER}.csv
    ;;
esac

Notes

If the details you enter into the generator contain commas - you must wrap the sentence in quote marks "Like This" otherwise when the CSV file is written, the fields will not be interpolated properly.

You can download the script here.

Thomas - 10-03-2023