Last active
August 1, 2022 13:34
-
-
Save stanasiukcom/c5ea8eb4863ca3939e21860d0572af08 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import imp | |
| import requests | |
| import jwt | |
| from datetime import datetime, timedelta | |
| from google.cloud import bigquery | |
| import pandas as pd | |
| import base64 | |
| import json | |
| def main(event, context): | |
| """Triggered from a message on a Cloud Pub/Sub topic. | |
| Args: | |
| event (dict): Event payload. | |
| context (google.cloud.functions.Context): Metadata for the event. | |
| """ | |
| print(event) | |
| if event.__contains__('data'): | |
| event_data = json.loads(base64.b64decode(event['data']).decode('utf-8')) | |
| else: | |
| event_data = event | |
| if event_data['report_type'] in ('sales', 'subscription'): | |
| # Initial setup | |
| client = bigquery.Client() | |
| table_id = 'appstore.' + event_data['report_type'] | |
| filter_version = '' | |
| if event_data['report_type'] == 'sales': | |
| filter_version = '1_0' | |
| elif event_data['report_type'] == 'subscription': | |
| filter_version = '1_3' | |
| report_date = '' | |
| today_date = datetime.today().strftime('%Y-%m-%d') | |
| yesterday_date = datetime.today() - timedelta(days = 1) | |
| yesterday_date = yesterday_date.strftime('%Y-%m-%d') | |
| if event_data.__contains__('report_date'): | |
| report_date = event_data['report_date'] | |
| else: | |
| report_date = yesterday_date | |
| # Authentication, enter your API credentials here | |
| issuer_id = '' | |
| api_key_id = '' | |
| api_key = b'' | |
| issue_timestamp = int(round(datetime.now().timestamp())) | |
| expiration_time = datetime.now() + timedelta(minutes = 10) | |
| expiration_timestamp = int(round(expiration_time.timestamp())) | |
| jwt_header = { | |
| "alg": "ES256", | |
| "kid": api_key_id, | |
| "typ": "JWT" | |
| } | |
| jwt_payload = { | |
| "iss": issuer_id, | |
| "iat": issue_timestamp, | |
| "exp": expiration_timestamp, | |
| "aud": "appstoreconnect-v1", | |
| } | |
| token = jwt.encode(jwt_payload, api_key, algorithm='ES256', headers=jwt_header) | |
| # Report setup | |
| filters = { | |
| 'filter[frequency]': 'DAILY', | |
| 'filter[reportSubType]': 'SUMMARY', | |
| 'filter[reportType]': event_data['report_type'].upper(), | |
| 'filter[vendorNumber]': '90778121', | |
| 'filter[version]': filter_version, | |
| 'filter[reportDate]': report_date | |
| } | |
| # Report download and processing | |
| r = requests.get('https://api.appstoreconnect.apple.com/v1/salesReports', params=filters, stream=True, headers={ | |
| 'Authorization': 'Bearer ' + token | |
| }) | |
| print(r.content) | |
| open('/tmp/report.gzip', 'wb').write(r.content) | |
| df = pd.read_csv('/tmp/report.gzip', compression='gzip', sep='\t', header=0) | |
| df.columns = df.columns.str.replace(' ', '_') | |
| df.columns = df.columns.str.replace('-', '_') | |
| df = df.replace(r'^\s*$', None, regex=True) | |
| df['Date'] = report_date | |
| df['_DATA_DATE'] = report_date | |
| df['_ADD_DATE'] = today_date | |
| if event_data['report_type'] == 'sales': | |
| job_config = bigquery.LoadJobConfig(schema=[ | |
| bigquery.SchemaField('Provider', 'STRING'), | |
| bigquery.SchemaField('Provider_Country', 'STRING'), | |
| bigquery.SchemaField('SKU', 'STRING'), | |
| bigquery.SchemaField('Developer', 'STRING'), | |
| bigquery.SchemaField('Title', 'STRING'), | |
| bigquery.SchemaField('Version', 'STRING'), | |
| bigquery.SchemaField('Product_Type_Identifier', 'STRING'), | |
| bigquery.SchemaField('Units', 'INTEGER'), | |
| bigquery.SchemaField('Developer_Proceeds', 'FLOAT'), | |
| bigquery.SchemaField('Begin_Date', 'DATE'), | |
| bigquery.SchemaField('End_Date', 'DATE'), | |
| bigquery.SchemaField('Customer_Currency', 'STRING'), | |
| bigquery.SchemaField('Country_Code', 'STRING'), | |
| bigquery.SchemaField('Currency_of_Proceeds', 'STRING'), | |
| bigquery.SchemaField('Apple_Identifier', 'STRING'), | |
| bigquery.SchemaField('Customer_Price', 'FLOAT'), | |
| bigquery.SchemaField('Promo_Code', 'STRING'), | |
| bigquery.SchemaField('Parent_Identifier', 'STRING'), | |
| bigquery.SchemaField('Subscription', 'STRING'), | |
| bigquery.SchemaField('Period', 'STRING'), | |
| bigquery.SchemaField('Category', 'STRING'), | |
| bigquery.SchemaField('CMB', 'STRING'), | |
| bigquery.SchemaField('Device', 'STRING'), | |
| bigquery.SchemaField('Supported_Platforms', 'STRING'), | |
| bigquery.SchemaField('Proceeds_Reason', 'STRING'), | |
| bigquery.SchemaField('Preserved_Pricing', 'FLOAT'), | |
| bigquery.SchemaField('Client', 'STRING'), | |
| bigquery.SchemaField('Order_Type', 'STRING'), | |
| bigquery.SchemaField('Date', 'DATE'), | |
| bigquery.SchemaField('_DATA_DATE', 'DATE'), | |
| bigquery.SchemaField('_ADD_DATE', 'DATE') | |
| ]) | |
| df = df.astype({ | |
| 'Provider': 'string', | |
| 'Provider_Country': 'string', | |
| 'SKU': 'string', | |
| 'Developer': 'string', | |
| 'Title': 'string', | |
| 'Version': 'string', | |
| 'Product_Type_Identifier': 'string', | |
| 'Units': 'int64', | |
| 'Developer_Proceeds': 'float64', | |
| 'Begin_Date': 'datetime64', | |
| 'End_Date': 'datetime64', | |
| 'Customer_Currency': 'string', | |
| 'Country_Code': 'string', | |
| 'Currency_of_Proceeds': 'string', | |
| 'Apple_Identifier': 'string', | |
| 'Customer_Price': 'float64', | |
| 'Promo_Code': 'string', | |
| 'Parent_Identifier': 'string', | |
| 'Subscription': 'string', | |
| 'Period': 'string', | |
| 'Category': 'string', | |
| 'CMB': 'string', | |
| 'Device': 'string', | |
| 'Supported_Platforms': 'string', | |
| 'Proceeds_Reason': 'string', | |
| 'Preserved_Pricing': 'float64', | |
| 'Client': 'string', | |
| 'Order_Type': 'string', | |
| 'Date': 'datetime64', | |
| '_DATA_DATE': 'datetime64', | |
| '_ADD_DATE': 'datetime64' | |
| }) | |
| elif event_data['report_type'] == 'subscription': | |
| job_config = bigquery.LoadJobConfig(schema=[ | |
| bigquery.SchemaField('App_Name', 'STRING'), | |
| bigquery.SchemaField('App_Apple_ID', 'STRING'), | |
| bigquery.SchemaField('Subscription_Name', 'STRING'), | |
| bigquery.SchemaField('Subscription_Apple_ID', 'STRING'), | |
| bigquery.SchemaField('Subscription_Group_ID', 'STRING'), | |
| bigquery.SchemaField('Standard_Subscription_Duration', 'STRING'), | |
| bigquery.SchemaField('Subscription_Offer_Name', 'STRING'), | |
| bigquery.SchemaField('Promotional_Offer_ID', 'STRING'), | |
| bigquery.SchemaField('Customer_Price', 'FLOAT'), | |
| bigquery.SchemaField('Customer_Currency', 'STRING'), | |
| bigquery.SchemaField('Developer_Proceeds', 'FLOAT'), | |
| bigquery.SchemaField('Proceeds_Currency', 'STRING'), | |
| bigquery.SchemaField('Preserved_Pricing', 'STRING'), | |
| bigquery.SchemaField('Proceeds_Reason', 'STRING'), | |
| bigquery.SchemaField('Client', 'STRING'), | |
| bigquery.SchemaField('Device', 'STRING'), | |
| bigquery.SchemaField('State', 'STRING'), | |
| bigquery.SchemaField('Country', 'STRING'), | |
| bigquery.SchemaField('Active_Standard_Price_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Active_Free_Trial_Introductory_Offer_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Active_Pay_Up_Front_Introductory_Offer_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Active_Pay_As_You_Go_Introductory_Offer_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Free_Trial_Promotional_Offer_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Pay_Up_Front_Promotional_Offer_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Pay_As_You_Go_Promotional_Offer_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Free_Trial_Offer_Code_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Pay_Up_Front_Offer_Code_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Pay_As_You_Go_Offer_Code_Subscriptions', 'INTEGER'), | |
| bigquery.SchemaField('Marketing_Opt_Ins', 'INTEGER'), | |
| bigquery.SchemaField('Billing_Retry', 'INTEGER'), | |
| bigquery.SchemaField('Grace_Period', 'INTEGER'), | |
| bigquery.SchemaField('Subscribers', 'INTEGER'), | |
| bigquery.SchemaField('Date', 'DATE'), | |
| bigquery.SchemaField('_DATA_DATE', 'DATE'), | |
| bigquery.SchemaField('_ADD_DATE', 'DATE') | |
| ]) | |
| df['Subscribers'] = df['Subscribers'].fillna(0) | |
| df['Subscribers'] = df['Subscribers'].astype(float).astype(int) | |
| df = df.astype({ | |
| 'App_Name': 'string', | |
| 'App_Apple_ID': 'string', | |
| 'Subscription_Name': 'string', | |
| 'Subscription_Apple_ID': 'string', | |
| 'Subscription_Offer_Name': 'string', | |
| 'Subscription_Group_ID': 'string', | |
| 'Standard_Subscription_Duration': 'string', | |
| 'Customer_Price': 'float64', | |
| 'Customer_Currency': 'string', | |
| 'Developer_Proceeds': 'float64', | |
| 'Promotional_Offer_ID': 'string', | |
| 'Preserved_Pricing': 'string', | |
| 'Proceeds_Currency': 'string', | |
| 'Proceeds_Reason': 'string', | |
| 'Client': 'string', | |
| 'Device': 'string', | |
| 'State': 'string', | |
| 'Country': 'string', | |
| 'Subscribers': 'int64', | |
| 'Active_Standard_Price_Subscriptions': 'int64', | |
| 'Active_Free_Trial_Introductory_Offer_Subscriptions': 'int64', | |
| 'Active_Pay_Up_Front_Introductory_Offer_Subscriptions': 'int64', | |
| 'Active_Pay_As_You_Go_Introductory_Offer_Subscriptions': 'int64', | |
| 'Free_Trial_Promotional_Offer_Subscriptions': 'int64', | |
| 'Pay_Up_Front_Promotional_Offer_Subscriptions': 'int64', | |
| 'Pay_As_You_Go_Promotional_Offer_Subscriptions': 'int64', | |
| 'Free_Trial_Offer_Code_Subscriptions': 'int64', | |
| 'Pay_Up_Front_Offer_Code_Subscriptions': 'int64', | |
| 'Pay_As_You_Go_Offer_Code_Subscriptions': 'int64', | |
| 'Marketing_Opt_Ins': 'int64', | |
| 'Billing_Retry': 'int64', | |
| 'Grace_Period': 'int64', | |
| 'Date': 'datetime64', | |
| '_DATA_DATE': 'datetime64', | |
| '_ADD_DATE': 'datetime64' | |
| }) | |
| # Load report to BigQuery | |
| job = client.load_table_from_dataframe(df, table_id, job_config=job_config) | |
| print(job.result()) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment