Google Play

25Şub

Google Play Geliştirici Raporlarını BigQuery’ye Aktarma

Google Play geliştirici raporlarını BigQuery’ye aktarmak için kullandığım bash script.

#!/bin/bash

if [ $# -eq 0 ]
then
	if [ $(date +%d) -gt 3 ]
	then
		d=$(date +%Y%m)
	else
		d=$(date +%Y%m -d '1 month ago')
	fi
else
	d=$1
fi

apps=('com.example.myapp1' 'com.example.myapp2')

for i in ${!apps[*]}
do
	gsutil cp gs://pubsite_prod_rev_1234567890/stats/installs/installs_${apps[i]}_${d}_overview.csv /home/username/google-play/reports/
	iconv -f UTF-16 -t UTF-8 /home/username/google-play/reports/installs_${apps[i]}_${d}_overview.csv > /home/username/google-play/reports/installs_${apps[i]}_${d}_overview_utf8.csv
	if [ "${d}" -lt "201609" ]
	then
		bq load --format csv --skip_leading_rows=1 --field_delimiter "," --replace google_play.raw_${apps[i]//./_}_${d} /home/username/google-play/reports/installs_${apps[i]}_${d}_overview_utf8.csv date:DATE,package_name:STRING,current_device_installs:INTEGER,daily_device_installs:INTEGER,daily_device_uninstalls:INTEGER,daily_device_upgrades:INTEGER,current_user_installs:INTEGER,total_user_installs:INTEGER,daily_user_installs:INTEGER,daily_user_uninstalls:INTEGER
	elif [ "${d}" -lt "201612" ]
	then
		bq load --format csv --skip_leading_rows=1 --field_delimiter "," --replace google_play.raw_${apps[i]//./_}_${d} /home/username/google-play/reports/installs_${apps[i]}_${d}_overview_utf8.csv date:DATE,package_name:STRING,current_device_installs:INTEGER,daily_device_installs:INTEGER,daily_device_uninstalls:INTEGER,daily_device_upgrades:INTEGER,current_user_installs:INTEGER,total_user_installs:INTEGER,daily_user_installs:INTEGER,daily_user_uninstalls:INTEGER,active_device_installs:INTEGER
	elif [ "${d}" -lt "201711" ]
	then
		bq load --format csv --skip_leading_rows=1 --field_delimiter "," --replace google_play.raw_${apps[i]//./_}_${d} /home/username/google-play/reports/installs_${apps[i]}_${d}_overview_utf8.csv date:DATE,package_name:STRING,daily_device_installs:INTEGER,daily_device_uninstalls:INTEGER,daily_device_upgrades:INTEGER,total_user_installs:INTEGER,daily_user_installs:INTEGER,daily_user_uninstalls:INTEGER,active_device_installs:INTEGER
	else
		bq load --format csv --skip_leading_rows=1 --field_delimiter "," --replace google_play.raw_${apps[i]//./_}_${d} /home/username/google-play/reports/installs_${apps[i]}_${d}_overview_utf8.csv date:DATE,package_name:STRING,daily_device_installs:INTEGER,daily_device_uninstalls:INTEGER,daily_device_upgrades:INTEGER,total_user_installs:INTEGER,daily_user_installs:INTEGER,daily_user_uninstalls:INTEGER,active_device_installs:INTEGER,install_events:INTEGER,update_events:INTEGER,uninstall_events:INTEGER
	fi
done

bq query --destination_table=google_play.all_apps --replace "SELECT date, package_name, daily_device_installs, daily_device_uninstalls, daily_device_upgrades, total_user_installs, daily_user_installs, daily_user_uninstalls, CASE WHEN active_device_installs IS NULL THEN 0 ELSE active_device_installs END AS active_device_installs, CASE WHEN current_device_installs IS NULL THEN 0 ELSE current_device_installs END AS current_device_installs, CASE WHEN current_user_installs IS NULL THEN 0 ELSE current_user_installs END AS current_user_installs FROM TABLE_QUERY([project-1234:google_play], 'REGEXP_MATCH(table_id, r\"^raw_com_example_myapp1_[\d]\")'), TABLE_QUERY([project-1234:google_play], 'REGEXP_MATCH(table_id, r\"^raw_com_example_myapp2_[\d]\")') ORDER BY date"