#Downloads all the orders and payments in general def getOrders(): sql = "SELECT * FROM analytics.prod_modeled.orders where YEAR(created_at) = 2023" conn = snowflake.connector.connect( account=account, user=user, password=password, database=database, schema=schema ) cursor = conn.cursor() cursor.execute(sql) results = cursor.fetchall() df = pd.DataFrame(results, columns=[desc[0] for desc in cursor.description]) conn.close() return df

def filterOrders(df, status_Completed=None, payment=None): if status_Completed is not None: if status_Completed: df = df[df['STATUS'] == 'completed'] else: df = df[df['STATUS'] != 'completed'] if payment is not None: df = df[df['PAYMENT'].isin(payment)] return df

def getOrdersbyMonthCountry(df): df['CREATED_AT'] = pd.to_datetime(df['CREATED_AT']) df['YEAR_MONTH'] = df['CREATED_AT'].dt.to_period('M') df_orders_by_country = df.groupby(['YEAR_MONTH', 'SOURCE_COUNTRY']).size().reset_index(name='ORDER_COUNT') return df_orders_by_country

#### #Capture the data #### #All Orders dfAllOrders = getOrders() #Orders completed for conciliation dfAllOrdersNotCanceled = filterOrders(dfAllOrders, status_Completed=True) #order in credit for recovery payment_recovery = ['Boleto', 'CrediTUL'] dfAllOrdersdCredit = filterOrders(dfAllOrders, status_Completed=True, payment = payment_recovery) #order in risk for fraud payment_risk = ['Tarjeta de crédito/débito', 'Tarjeta de crédito / débito', 'Boleto', 'CrediTUL'] dfAllOrdersdRisk = filterOrders(dfAllOrders, status_Completed=None, payment = payment_risk ) #--- #### #split by month #### #Concilition dfConcilitionMonth = getOrdersbyMonthCountry(dfAllOrdersNotCanceled) #Recovery dfRecoveryMonth = getOrdersbyMonthCountry(dfAllOrdersdCredit) #Risk dfRiskMonth = getOrdersbyMonthCountry(dfAllOrdersdRisk)

#Key assumptions percentageConciliation = 0.70 minutesConciliation = 5 percentageRisk = 0.20 minutesRisk = 15 percentageNotPaid = 0.20 minutesRecovery = 25