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
