def main(): """ Update Mixpanel and HubSpot profiles with Baremetrics MRR data. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email("Initiate Baremetrics MRR to Mixpanel & Hubspot") #execute update df_upload = execute_data_push() #send completed email send_email("Baremetrics MRR to Mixpanel & Hubspot Completed") return
def main(): """ Update app creator's active milestone in Mixpanel. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email("Initiate Adoption Milestone Update") try: #date range for usage metrics to_date_usage = date.today() - timedelta(days=1) from_date_usage = to_date_usage - timedelta(days=30) #pull creators profile df_creators = app_creator_pull() #only keep creators who did not have active_milestone set to true df_creators = df_creators[df_creators.active_milestone != True] #pull active creators active_creators_list = get_usage_metrics(from_date_usage, to_date_usage) #only keep active app creators df_creators_active = df_creators[df_creators.app_owner_id.isin( active_creators_list)] print(len(df_creators_active)) #update Mixpanel profile with status update_mixpanel_active_status(df_creators_active) except: time.sleep(30) #send completion email send_email("Active Milestone Update Completed") return
def main(): """ Update MQL pipeline metrics dashboards. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email("Initiate MQL Pipeline Dashboard Update") #generate date range dataframe date_beginning_quarter = datetime.strptime('2020-04-01', '%Y-%m-%d') df_date_range = generate_data_range_df(date_beginning_quarter) #pull contact form submissions, number of deals associated to contacts, and number of BAP/GCP sourced deals in Q2 2020 df_contact_forms = pull_contact_forms() df_associated_deals = pull_hubspot_contacts(df_contact_forms) df_hubspot_deals, df_BAP_hubspot_deals, df_GCP_hubspot_deals = get_hubspot_deals( ) #merge contact forms and associated deals contacts df_contact_forms = pd.merge(df_contact_forms, df_associated_deals, on='email', how='left') #process data to generate df with cumulative sums of all the datapoints df_final_cumsum = process_data(df_date_range, df_contact_forms, df_hubspot_deals, df_BAP_hubspot_deals, df_GCP_hubspot_deals) #authenticate to Google drive API and update spreadsheet main_sheet = auth_google_services() update_google_sheet(df_final_cumsum, main_sheet) #send email when completed send_email("MQL Pipeline Dashboard Update Completed") return
def main(): """ Update Mixpanel profiles with Clearbit company and contact data. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email("Initiate Clearbit Enrichment") #get all users mixpanel and new users from yesterday yesterday = date.today() - timedelta(days=1) df_new_users = get_new_signup(yesterday) df_new_users.drop_duplicates('email', keep='first') #remove contacts with @gmail.com, hotmail.com, yahoo.com, and outlook.com since Clearbit wouldn't be able to enrich them df_new_users = df_new_users[~df_new_users['email'].str.contains("gmail.com" )] df_new_users = df_new_users[~df_new_users['email'].str. contains("hotmail.com")] df_new_users = df_new_users[~df_new_users['email'].str.contains("yahoo.com" )] df_new_users = df_new_users[~df_new_users['email'].str. contains("outlook.com")] df_new_users = df_new_users[df_new_users['user_id'] != 0] #get user email and ID email_list = df_new_users.email.tolist() user_id_list = df_new_users.user_id.tolist() #execute data migration get_clearbit_data(email_list, user_id_list) #send email when completed send_email("Clearbit Enrichment Completed") return
def main(): """ Identify app creators who originally signed up as app users. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email('Initiate User to Creator Tag Update') #initialize date range today = date.today() yesterday = today - timedelta(days=1) #pull new app sign up, editor sessions, and app creator and user profiles df_New_Signup_App, df_Editor, df_creator, df_app_users = pull_data( yesterday) #identify app creators who originally signed up as app users df_user_to_creator = identify_user_to_creator(df_New_Signup_App, df_Editor, df_creator, df_app_users) #update app creator profile in Mixpanel and store data in csv push_user_to_creator(df_user_to_creator) df_user_to_creator.to_csv( '/home/phuong/automated_jobs/user_to_creator/user_to_creator.csv', index=False) #send completed email send_email('User to Creator Tag Update Completed') return
def main(): """ Notify Hyung of APAC Code Users Parameters ---------- Global Variables ---------- Returns ---------- None """ #sending initating email send_email('Initiate APAC Code Tagging') email_list = pull_stripe_customers() #get customer with COVID19 discount email_list = set(email_list) #remove duplicates from list email_list = [email.lower() for email in email_list] #lowercase all emails df_creators = creator_pull() #pull creators from Mixpanel df_creators['email'] = df_creators['email'].str.lower( ) #lowercase all email #only keep contacts with COVID19 promo df_creators = df_creators[df_creators.email.isin(email_list)] #update COVID19 status in Mixpanel update_mixpanel_apac_promo_status(df_creators) push_contacts_to_hubspot(email_list) #send completion email send_email('APAC Code Tagging Completed') return
def main(): """ Update app creator COVID promo status in Mixpanel Parameters ---------- Global Variables ---------- Returns ---------- None """ #sending initating email send_email('Initiate COVID19 Mixpanel Tagging') email_list = pull_stripe_customers() #get customer with COVID19 discount email_list = set(email_list) #remove duplicates from list email_list = [email.lower() for email in email_list] #lowercase all emails df_creators = creator_pull() #pull creators from Mixpanel df_creators['email'] = df_creators['email'].str.lower( ) #lowercase all email #only keep contacts with COVID19 promo df_creators = df_creators[df_creators.email.isin(email_list)] #update COVID19 status in Mixpanel update_mixpanel_covid19_status(df_creators) #send completion email send_email('COVID19 Mixpanel Tagging Completed') return
def main(): """ Update Mixpanel app creator profiles with company data, use case, and tags from Intercom. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email('Initiate Intercom-Mixpanel Update') #pull app creator profiles in Mixpanel df_users = user_pull() #pull app creator Intercom tags, company url, and use case df_user_tags, df_user_company_url, df_user_use_case = pull_intercom_users() # execute updates df_tags_pushed = execute_intercom_tag_updates(df_user_tags, df_users) df_clearbit_enriched_company_url_push = execute_intercom_company_updates( df_user_company_url, df_users) df_use_case_push = execute_intercom_use_case_updates( df_user_use_case, df_users) #send complete email send_email('Intercom-Mixpanel Update Completed') return df_use_case_push
def main(): """ Update HubSpot contacts with Mixpanel email unsubscribe status. Parameters ---------- Global Variables ---------- Returns ---------- None """ #sending initating email send_email('Initiate Mixpanel to HubSpot Unsubscribe Email Sync') #pull list of unsubscribed app creators df_creators = creator_pull() df_creators['email'] = df_creators['email'].str.lower( ) #lowercase all email email_list = df_creators.email.tolist() #update hubspot contacts update_contacts_to_hubspot(email_list) #send completion email send_email('Mixpanel to HubSpot Unsubscribe Email Sync Completed') return
def main(): """ Update adoption metrics dashboard. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email("Initiate Adoption Metrics Update") #set status status = 1 while status == 1: #add to rerun job if it fails for any reason try: #date range for new signups to_date_signup = date.today() - timedelta(days=1) from_date_signup = date.today() - timedelta(days=1) #date range for usage metrics to_date_usage = date.today() - timedelta(days=1) from_date_usage = to_date_usage - timedelta(days=30) print(from_date_signup) print(to_date_signup) #pull creator profiles df_creators = app_creator_pull() #get metrics today_new_signup = get_signup_metric(from_date_signup, to_date_signup, df_creators) today_total_number_of_active_creators, today_monthly_active_app_users, num_active_apps, num_of_active_app_users_by_num_of_active_apps = get_usage_metrics( from_date_usage, to_date_usage) arr = get_arr_metric(to_date_signup) num_deals, deal_value = get_hubspot_deals( to_date_usage) #pull deal metrics for deals closed yesterday #update Google Sheet main_sheet = auth_google_services() update_google_sheet( today_new_signup, today_total_number_of_active_creators, today_monthly_active_app_users, num_active_apps, num_of_active_app_users_by_num_of_active_apps, arr, num_deals, deal_value, to_date_usage, main_sheet) status = 0 except: print('Job failed. Rerunning...') #send email when completed send_email("Adoption Metrics Update Completed") return
def main(): """ Update Mixpanel profiles with HubSpot specific company and contact data. Update HubSpot profiles with Mixpanel specific contact data. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email('Initiate HubSpot-Mixpanel Update') #pull app creator information from Mixpanel df_creators_mixpanel = pull_creators() #email list email_list = df_creators_mixpanel.email.tolist() mixpanel_distinct_id_list = df_creators_mixpanel.mixpanel_distinct_id.tolist( ) #initialize list to record errors errors = [] #update Mixpanel profiles with HubSpot data and vice versa for i in tqdm(range(len(email_list))): #get user ID and email user_email = email_list[i] mixpanel_distinct_id = mixpanel_distinct_id_list[i] #update Mixpanel profile try: update_fields(user_email, mixpanel_distinct_id) except Exception as e: print(e) print(user_email) errors.append(user_email) #generate dataframes containing contacts who were caused an error data = {'emails': errors} df_errors = pd.DataFrame(data) #save dataframe as a csv file_path = '/home/phuong/automated_jobs/hubspot_mixpanel/email_errors.csv' df_errors.to_csv(file_path, index=False) #send completed email send_email('HubSpot-Mixpanel Update Completed', file_path) return
def main(): """ Update Number of Users Metrics in Sales CS Dashboard. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email("Initiate Sales CS Update") #auth and access Google sheet user_sheet = auth_google_services() #generate date range today = date.today() to_date = today - timedelta(days=1) from_date = to_date - timedelta(days=30) #pull app creator info, Editors, AppStarts and app users AppStarts df_user_AppStart = user_appstart_pull(from_date, to_date) df_creators = creator_pull() #merge creator and AppStart data df_final = pd.merge(df_user_AppStart, df_creators, on='creator_id', how='left') df_final = df_final.dropna() #remove nulls #lowercase emails for domain extractions df_final['email'] = df_final.email.str.lower() #extract creator email domains df_final['user_email_domains'] = df_final['email'].str.split( '@').str[1].fillna('') #removing gmail, hotmail, outlook, and yahoo df_final = df_final[~df_final.user_email_domains.isin([ 'appsheet.com', '1track.com', 'gmail.com', 'hotmail.com', 'outlook.com', 'live.com', 'yahoo.com' ])] #count the number of users for by domains and store as dictionary df_final = df_final.groupby(['user_email_domains' ]).app_user_id.count().reset_index() num_user_dictionary = dict( zip(df_final.user_email_domains.tolist(), df_final.app_user_id.tolist())) #update spreadsheet with new num_user data update_spreadsheet(user_sheet, num_user_dictionary) #send email when completed send_email("Sales CS Update Completed") return
def main(): """ Update app creator journey stage in Mixpanel. Parameters ---------- Global Variables ---------- Returns ---------- dataframe Dataframe contains app creator info and current journey stage. """ #send initial email send_email('Initiate App Creator Journey Stage Update') #initialize date range today = date.today() date_since = date(year=2014, month=8, day=8) date_since_user_project = date(year=2017, month=3, day=1) yesterday = today - timedelta(days=1) #pull all app creator and user profiles from Mixpanel df_users = user_pull() df_app_users = app_user_pull() #pull Editor, AppStart and New Signup Web events from Mixpanel df_editor_action = EditorAction_pull(date_since, today) df_user_AppStart, df_top_appstarts = user_appstart_pull( date_since_user_project, today, df_app_users) df_New_Signup_Web = New_Signup_Web_pull(yesterday, today) #pull in all time sign up web. It is stored to prevent having to pull the same data every day df_New_Signup_All_Time = pd.read_csv( '/home/phuong/automated_jobs/user_journey/new_sign_up_since_beginning_final.csv' ) #merge new and old sign up to create new all-time frames = [df_New_Signup_All_Time, df_New_Signup_Web] df_New_Signup_Web_final = pd.concat(frames) #drop duplicate app creator signups df_New_Signup_Web_final = df_New_Signup_Web_final.drop_duplicates( 'user_id') #update new sign up all time csv df_New_Signup_Web_final.to_csv( '/home/phuong/automated_jobs/user_journey/new_sign_up_since_beginning_final.csv', index=False) #merge app creator profiles, app user domains, top app AppStarts, and EditorAction-Save dataframes df_final = pd.merge(df_users, df_New_Signup_Web_final[['user_id', 'new_sign_up']], on='user_id', how='left') df_final = pd.merge( df_final, df_user_AppStart[['user_id', 'num_app_users', 'user_email_domains']], on='user_id', how='left') df_final = pd.merge(df_final, df_top_appstarts[['user_id', 'appstart_by_top_app']], on='user_id', how='left') #merge existing df_final = pd.merge(df_final, df_editor_action[['email', 'EditorAction']], on='email', how='left') #remove app creators with missing emails df_final = df_final[~df_final.email.isnull()] #assign empty list to app creators without users df_final = df_final.fillna(0) df_final.loc[df_final['user_email_domains'] == 0, ['user_email_domains']] = df_final.loc[ df_final['user_email_domains'] == 0, 'user_email_domains'].apply(lambda x: []) #convert string mrr to int df_final.loc[df_final.hs_mrr == '', 'hs_mrr'] = 0 df_final['hs_mrr'] = df_final.hs_mrr.astype(float) #categorize app creators journey stage based on given conditions df_final['user_journey_stage'] = '' df_final['user_journey_stage'] = np.where( (df_final['num_app_users'] >= 2) | (df_final['appstart_by_top_app'] >= 40), 'Commit', df_final['user_journey_stage'] ) #40 is 70th percentile of users with appstart df_final['user_journey_stage'] = np.where( (df_final['num_app_users'] < 2) & (df_final['appstart_by_top_app'] < 40) & (df_final['EditorAction'] > 0), 'Explore', df_final['user_journey_stage']) df_final['user_journey_stage'] = np.where(df_final['hs_mrr'] > 0, 'Convert', df_final['user_journey_stage']) df_final['user_journey_stage'] = np.where( df_final['user_journey_stage'] == '', 'Consider', df_final['user_journey_stage']) df_final['sign_up_channel'] = np.where((df_final['new_sign_up'] > 0), 'Web', 'App') #extract app creator distinct ID, number of app users, app user domains, sign up channel, journey stage, and top app AppStarts to lists distinct_id_list = df_final.distinct_id.tolist() num_app_users_list = df_final.num_app_users.tolist() email_domains_list = df_final.user_email_domains.tolist() user_journey_stage_list = df_final.user_journey_stage.tolist() user_sign_up_channel_list = df_final.sign_up_channel.tolist() appstart_by_top_app_list = df_final.appstart_by_top_app.tolist() #update Mixpanel profiles for i in tqdm(range(len(distinct_id_list))): #extract app creator distinct ID, number of app users, journey stage, sign up channel, top app AppStarts, and user email domains distinct_id = distinct_id_list[i] num_app_users = num_app_users_list[i] user_journey_stage = user_journey_stage_list[i] user_sign_up_channel = user_sign_up_channel_list[i] appstarts_by_top_app = appstart_by_top_app_list[i] user_domains = email_domains_list[i] #generate parameters and update Mixpanel profile try: params = { 'user_domains': user_domains, 'num_app_users': num_app_users, 'num_appstarts_by_top_app': appstarts_by_top_app, 'user_journey_stage': user_journey_stage, 'sign_up_channel': user_sign_up_channel } mp.people_set(distinct_id, params, meta={ '$ignore_time': 'true', '$ip': 0 }) except Exception as e: print(e) print(distinct_id) #send completed email send_email('App Creator Journey Stage Update Completed') return df_final
def main(): """ Update HubSpot profiles with current CS Healthblast metrics. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email('Initiate CS Healthblast Update') # pulling 46 days for now to generate the first batch today = date.today() from_date = today - timedelta(days=22) to_date = today - timedelta(days=1) #generate dataframe with dates address for days when there is no activity numdays = 23 base = to_date date_list = [ pd.Timestamp(base - timedelta(days=x)) for x in range(numdays) ] data = {'date': date_list} df_date_range = pd.DataFrame(data).sort_values('date', ascending=True) print('starting') #pull app creator info, Editors, AppStarts and app users AppStarts df_user_AppStart = user_appstart_pull(from_date, to_date) df_Editor = Editor_pull(from_date, to_date) df_AppStart = AppStart_pull(from_date, to_date) df_creators = creator_pull() #gather metrics for contacts df_final_creator_usage = get_rolling_average_creator_usage( df_AppStart, df_Editor, df_date_range, df_creators) df_final_active_users = get_rolling_average_num_app_users( df_user_AppStart, df_date_range, df_creators) df_final_user_convo = get_intercom_convo_data(df_date_range, from_date) #merge final dataframes for the 3 metrics df_final = pd.merge( df_final_creator_usage[[ 'DATE', 'user_id', 'OWNER', 'EMAIL', 'ACTIVE_USAGE_ROLLING' ]], df_final_active_users[['user_id', 'DATE', 'ACTIVE_USER_ROLLING']], on=['user_id', 'DATE'], how='left') df_final = pd.merge( df_final, df_final_user_convo[['DATE', 'EMAIL', 'NUM_CONVO_ROLLING']], on=['EMAIL', 'DATE'], how='left') #fill any nulls with zeroes df_final = df_final.fillna(0) #get contact current metrics and update HubSpot profiles df_creator_current_metrics_final = get_current_metrics( df_final, df_creators) # identify anomaly for each metric df_metric_status = identify_anomaly(df_final) #merge current metric with anomaly status df_creator_current_metrics_final_status = pd.merge( df_creator_current_metrics_final, df_metric_status, on='user_id', how='left') #Fill empty gap values with 0's. Then fill null values with '' to indicate that there is no anomaly. df_creator_current_metrics_final_status[ 'ACTIVE_USAGE_ROLLING_ANOMALY_GAP'] = df_creator_current_metrics_final_status.ACTIVE_USAGE_ROLLING_ANOMALY_GAP.fillna( 0) df_creator_current_metrics_final_status[ 'ACTIVE_USER_ROLLING_ANOMALY_GAP'] = df_creator_current_metrics_final_status.ACTIVE_USER_ROLLING_ANOMALY_GAP.fillna( 0) df_creator_current_metrics_final_status[ 'HEALTH_STATUS'] = df_creator_current_metrics_final_status.HEALTH_STATUS.fillna( 'INACTIVE') df_creator_current_metrics_final_status[ 'ALERT_REASON'] = df_creator_current_metrics_final_status.ALERT_REASON.fillna( '') df_creator_current_metrics_final_status = df_creator_current_metrics_final_status.fillna( '') #lower case email addresses and remove duplicates. Keep account with high current activity df_creator_current_metrics_final_status[ 'email'] = df_creator_current_metrics_final_status.email.str.lower() df_creator_current_metrics_final_status = df_creator_current_metrics_final_status.sort_values( 'ACTIVE_USAGE_ROLLING_CURRENT', ascending=False).drop_duplicates('email', keep='first') print(len(df_creator_current_metrics_final_status)) update_hubspot_profiles(df_creator_current_metrics_final_status) #send completed email send_email('CS Healthblast Update Completed') return
def main(): """ Update app users' creator and active_user statuses in Mixpanel. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email('Initiate App User Creator and Active Statuses Update') #initialize date range yesterday = date.today() - timedelta(days=1) prior_30_days = yesterday - timedelta(days=30) date_since = date(year=2014, month=8, day=8) #generate current app user creator and active_user statuses df_users_creator_active_status_current, df_app_user = generate_current_user_active_creator( prior_30_days, date_since, yesterday) #merge current statuses with app user profiles df_app_user_merged_current = pd.merge( df_app_user[['email', 'creator_current', 'active_user_current']], df_users_creator_active_status_current, on='email', how='left') df_app_user_merged_current = df_app_user_merged_current[ ~df_app_user_merged_current.email.isnull()] #remove null emails df_app_user_merged_current = df_app_user_merged_current[ ~df_app_user_merged_current.user_id.isnull()] #remove null user_id #only need to update app users whose current statuses differ from records in Mixpanel df_app_user_merged_current = df_app_user_merged_current[ (df_app_user_merged_current. creator_current != df_app_user_merged_current.creator) | (df_app_user_merged_current. active_user_current != df_app_user_merged_current.active_user)] #avoid duplicate accounts and only focus on ones with activities df_app_user_merged_current = df_app_user_merged_current.sort_values( 'creator_current', ascending=False).drop_duplicates('user_id', keep='first') #push updates to Mixpanel push_update( df_app_user_merged_current[['distinct_id', 'creator', 'active_user']]) #send completed email send_email('App User Creator and Active Statuses Update Completed') return
def main(): """ Enrich new Mixpanel profiles. Parameters ---------- Global Variables ---------- Returns ---------- None """ #send initial email send_email("Initiate DiscoverOrg Enrichment") #authenticate with Google client = auth_google(GOOGLE_APPLICATION_CREDENTIALS) #get all users mixpanel and new users from yesterday yesterday = date.today() - timedelta(days=1) df_new_users = get_new_signup(yesterday) df_new_users= df_new_users.drop_duplicates('email', keep='first') df_creators = creator_pull() df_new_users = pd.merge(df_new_users, df_creators, on='user_id', how='left').drop_duplicates('distinct_id') #remove contacts with @gmail.com, hotmail.com, yahoo.com, and outlook.com since Clearbit wouldn't be able to enrich them df_new_users = df_new_users[~df_new_users['email'].str.contains("gmail.com")] df_new_users = df_new_users[~df_new_users['email'].str.contains("hotmail.com")] df_new_users = df_new_users[~df_new_users['email'].str.contains("yahoo.com")] df_new_users = df_new_users[~df_new_users['email'].str.contains("outlook.com")] df_new_users = df_new_users[~df_new_users['email'].str.contains("googlemail.com")] df_new_users = df_new_users[~df_new_users['email'].str.contains("hotmail.co.uk")] df_new_users = df_new_users[~df_new_users['email'].str.contains("outlook.com")] df_new_users = df_new_users[~df_new_users['email'].str.contains("aol.com")] df_new_users = df_new_users[~df_new_users['email'].str.contains("icloud.com")] df_new_users = df_new_users[df_new_users['user_id']!=0] #separate out domains df_new_users['user_email_domains'] = df_new_users['email'].str.split('@').str[1].fillna('') #get user email and ID email_domains_list = df_new_users.user_email_domains.tolist() distinct_id_list = df_new_users.distinct_id.tolist() #enrich profiles in Mixpanel enrich_profiles(client, email_domains_list, distinct_id_list) #send email when completed send_email("DiscoverOrg Enrichment Completed") return