Calculate iOS sales per-app

calculate.sh

#!/bin/bash

if [[ $# -eq 0 ]]
	then
	echo Usage: download financial_report.csv, then run $0 year month
	exit
fi

# Before running this you need a token.
# A token lasts six months, but I regenerate it every time, so I can just forget about it.

# You're meant to be able to use this command to generate a new token:
# java -jar Reporter.jar p=Reporter.properties Sales.generateToken
# But it never works for me, not sure why

# Now I'm using itc-reporter to do it:
# https://github.com/fedoco/itc-reporter
# It sometimes returns a 503 error, but sometimes works
# Since a token is good for 6 months, hopefully at some point in that 6 month period it'll work

# username/password for generating a token is stored in this file
. ./config.sh

result=$(./itc-reporter/reporter.py -u ${itc_username} generateToken -P ${itc_password})

token=$(echo "$result" | grep AccessToken | sed s/:/=/)

if [[ "$token" == AccessToken* ]]
then
	cat > Reporter.properties << EOF
$token

Mode=Normal

SalesUrl=https://reportingitc-reporter.apple.com/reportservice/sales/v1
FinanceUrl=https://reportingitc-reporter.apple.com/reportservice/finance/v1
EOF
else
	echo "Warning: couldn't generate new token"
	echo "$result"
	echo "You can generate a token manually on App Store Connect:"
	echo "sales & trends -> sales & trends reports -> about reports -> generate token"
	echo "https://appstoreconnect.apple.com/trends/reports"
fi

# Now we have a token, on with the rest of the work

year="$1"
month="$2"

dir="${year}-${month}"

# remove leading zeroes which can be treated as octal
month=$((10#$month))

mkdir -p "$dir"

month=$(($month + 3))

if [[ $month -ge 13 ]]
	then
	year=$(($year + 1))
	month=$(($month - 12))
fi

file=$dir/Z1.csv

rm -f $file

echo $(date) Downloading ${country} for $year $month

java -jar Reporter.jar p=Reporter.properties a=864442 Finance.getReport 86876171, Z1, FinanceDetail, ${year}, ${month}

if [ $? -eq 0 ]
	then
	
	mv *.gz ${file}.gz
	gzip -d $file
	
	else
	
	touch $file
fi

rm -f $dir/tmp.csv

awk -F $'\t' 'BEGIN { OFS="\t"; OFMT="%0.2f" } /Draknek|LOKUnlock|FULL_GAME/ { if (NR > 1) { game = $4; thisincome = $11; currency = $12; income[currency][game] += thisincome; } } END { for(currency in income) { for (game in income[currency]) { print game, income[currency][game], currency } } }' $file > $dir/tmp.csv

# $5 = SKU
# $8 = income
# $9 = currency
#awk -F $'\t' 'BEGIN { OFS="\t"; OFMT="%0.2f" } /Draknek/ { income[$5]+=$8; currency = $9 } END { for(i in income){ print i, income[i], currency } }' $file > $dir/tmp.csv

#sed 's/,//g' $dir/conversions.csv > $dir/conversions2.csv

# $1 = currency
# $9 = conversion rate
# $5 = tax
#awk -F $'\t' 'NR==FNR { conversion[$1] = $9; tax[$1] = $5 } NR!=FNR { subtotal = ($2 + tax[$3]) * conversion[$3];print $3, $1, subtotal; total += subtotal; income[$1] += subtotal } END { print ""; OFMT="%0.2f"; for(i in income){ print i, income[i], "/", total } }' $dir/conversions2.csv $dir/tmp.csv

if [[ ! -f "$dir/financial_report.csv" ]]
	then
	cp financial_report.csv $dir/financial_report.csv
fi

# output currency, Exchange Rate, Withholding Tax, Pre-Tax Subtotal
sed 's/"//g' $dir/financial_report.csv | awk -F $',' 'BEGIN { OFS="\t" } NR>3 { print substr($1, index($1, "(")+1, 3), $9, $7, $4 }' > $dir/conversions2.csv

#sed 's/,/\t/g' $dir/financial_report.csv > $dir/conversions2.csv

#sed 's/,/\t/g' $dir/financial_report.csv > $dir/conversions2.csv

awk -F $'\t' -f /dev/stdin $dir/conversions2.csv $dir/tmp.csv > $dir/conversions3.txt <<- "EOF"
NR==FNR {
	conversion[$1] = $2
	tax[$1] = $3
	pretax[$1] = $4
}
NR!=FNR {
	if (pretax[$3] == 0) { next }
	subtotal = ($2 + (tax[$3] * $2 / pretax[$3])) * conversion[$3]
	total += subtotal
	income[$1] += subtotal
	if ($1 == "org.draknek.bundle.classics") {
		income["cosmic-express"] += 5*subtotal/18
		income["agoodsnowman"] += 5*subtotal/18
		income["sokobond"] += 5*subtotal/18
		income["hearts"] += 3*subtotal/18
	}
	if ($1 == "draknek-puzzle-collection") {
		income["cosmic-express"] += 5*subtotal/13
		income["agoodsnowman"] += 5*subtotal/13
		income["hearts"] += 3*subtotal/13
	}
	if ($1 == "org.draknek.bundle3games") {
		income["cosmic-express"] += 5*subtotal/15
		income["agoodsnowman"] += 5*subtotal/15
		income["sokobond"] += 5*subtotal/15
	}
	if ($1 == "org.draknek.bundle4games") {
		income["cosmic-express"] += 6*subtotal/28
		income["agoodsnowman"] += 6*subtotal/28
		income["sokobond"] += 6*subtotal/28
		income["com.monsterexpedition"] += 10*subtotal/28
	}
	if ($1 == "org.draknek.bundle5games") {
		income["cosmic-express"] += 6*subtotal/34
		income["agoodsnowman"] += 6*subtotal/34
		income["sokobond"] += 6*subtotal/34
		income["com.monsterexpedition"] += 10*subtotal/34
		income["com.josehzz.Sokobond-Express"] += 6*subtotal/34
	}
}
END {
	OFMT="%0.2f"
	for (i in income) { print i, income[i], "/", total }
}
EOF

awk -F $' ' -f /dev/stdin $dir/conversions3.txt <<- "EOF"
{
	if ($1 != "org.draknek.bundle.classics" && $1 != "draknek-puzzle-collection" && $1 != "org.draknek.bundle3games" && $1 != "org.draknek.bundle4games" && $1 != "org.draknek.bundle5games") {
		total += $2; income[$1] += $2
	}
}
END {
	OFMT="%0.2f"
	for(i in income){ print i, "=", income[i], "/", total }
}
EOF

Hey Alan how are things? :D Thanks for this script!... I'm having a weird issue with reporter...keeps telling me that it "Cannot find properties file. Make sure it resides in the same directory." and won't run the script because it cannot be authenticated...also if I run calculate.sh it also tells me I have no .csv file but all is there in the correct directory. Did you have a similar issue? it's like files outside the script I currently run are not existing :=0