😎
Templates
Google Analytics
Google Drive
Google Search
Google Sheets
Hugging Face
Johns Hopkins
Jupyter Notebooks
Metrics Store
Microsoft Teams
Microsoft Word
Naas Auth
Send sales brief
Tags: #hubspot #crm #sales #deal #naas_drivers #notification #asset #emailbuilder #scheduler #naas
Input
Import library
1
from naas_drivers import emailbuilder, hubspot
2
import naas
3
import pandas as pd
4
from datetime import datetime
Copied!
Setup your HubSpot
1
HS_API_KEY = 'YOUR_HUBSPOT_API_KEY'
Copied!
Email parameters
1
# Receivers
2
email_to = ["your_email_adresse"]
3
4
# Email subject
5
email_subject = f"🚀 Hubspot - Sales Brief as of {datetime.now().strftime('%d/%m/%Y')} (Draft)"
Copied!
Sales target
1
objective = 300000
Copied!
Pick your pipeline
Get all pipelines
1
df_pipelines = hubspot.connect(HS_API_KEY).pipelines.get_all()
2
df_pipelines
Copied!
Enter your pipeline id
1
pipeline_id = "8432671"
Copied!
Constants
1
HUBSPOT_CARD = "https://lib.umso.co/lib_sluGpRGQOLtkyEpz/na1lz0v9ejyurau2.png?w=1200&h=900&fit=max&dpr=2"
2
NAAS_WEBSITE = "https://www.naas.ai"
3
EMAIL_DESCRIPTION = "Your sales brief"
4
DATE_FORMAT = "%Y-%m-%d"
Copied!
Schedule automation
1
#-> Uncomment the 2 lines below (by removing the hashtag) to schedule your job every monday at 8:00 AM (NB: you can choose the time of your scheduling bot)
2
# import naas
3
# naas.scheduler.add(cron="0 8 * * 1")
4
5
#-> Uncomment the line below (by removing the hashtag) to remove your scheduler
6
# naas.scheduler.delete()
Copied!
Model
Get dealstages from pipeline
1
df_dealstages = df_pipelines.copy()
2
3
# Filter on pipeline
4
df_dealstages = df_dealstages[df_dealstages.pipeline_id == pipeline_id]
5
6
df_dealstages
Copied!
Get deals from pipeline
1
properties = [
2
"hs_object_id",
3
"dealname",
4
"dealstage",
5
"pipeline",
6
"createdate",
7
"hs_lastmodifieddate",
8
"closedate",
9
"amount"
10
]
11
df_deals = hubspot.connect(HS_API_KEY).deals.get_all(properties)
12
13
# Filter on pipeline
14
df_deals = df_deals[df_deals.pipeline == pipeline_id].reset_index(drop=True)
15
16
df_deals
Copied!
Formatting functions
1
def format_number(num):
2
NUMBER_FORMAT = "{:,.0f} €"
3
num = str(NUMBER_FORMAT.format(num)).replace(",", " ")
4
return num
Copied!
1
def format_pourcentage(num):
2
NUMBER_FORMAT = "{:,.0%}"
3
num = str(NUMBER_FORMAT.format(num))
4
return num
Copied!
1
def format_varv(num):
2
NUMBER_FORMAT = "+{:,.0f} €"
3
num = str(NUMBER_FORMAT.format(num)).replace(",", " ")
4
return num
Copied!
Create sales pipeline database
1
df_sales = pd.merge(df_deals.drop("pipeline", axis=1),
2
df_dealstages.drop(["pipeline", "pipeline_id", "createdAt", "updatedAt", "archived"], axis=1),
3
left_on="dealstage",
4
right_on="dealstage_id",
5
how="left")
6
df_sales
Copied!
1
df_sales_c = df_sales.copy()
2
3
# Cleaning
4
df_sales_c["amount"] = df_sales_c["amount"].fillna("0")
5
df_sales_c.loc[df_sales_c["amount"] == "", "amount"] = "0"
6
7
# Formatting
8
df_sales_c["amount"] = df_sales_c["amount"].astype(float)
9
df_sales_c["probability"] = df_sales_c["probability"].astype(float)
10
df_sales_c.createdate = pd.to_datetime(df_sales_c.createdate)
11
df_sales_c.hs_lastmodifieddate = pd.to_datetime(df_sales_c.hs_lastmodifieddate)
12
df_sales_c.closedate = pd.to_datetime(df_sales_c.closedate)
13
14
# Calc
15
df_sales_c["forecasted"] = df_sales_c["amount"] * df_sales_c["probability"]
16
17
df_sales_c
Copied!
Create sales pipeline agregated by dealstages
1
df_details = df_sales_c.copy()
2
3
# Groupby
4
to_group = [
5
"dealstage_label",
6
"probability",
7
"displayOrder"
8
]
9
to_agg = {
10
"amount": "sum",
11
"dealname": "count",
12
"forecasted": "sum"
13
}
14
df_details = df_details.groupby(to_group, as_index=False).agg(to_agg)
15
16
# Sort
17
df_details = df_details.sort_values("displayOrder")
18
19
df_details
Copied!
Calculate email parameters
1
forecasted = df_details.forecasted.sum()
2
forecasted
Copied!
1
won = df_details[df_details["probability"] == 1].forecasted.sum()
2
won
Copied!
1
weighted = df_details[df_details["probability"] < 1].forecasted.sum()
2
weighted
Copied!
1
completion_p = forecasted / objective
2
completion_p
Copied!
1
completion_v = objective - forecasted
2
completion_v
Copied!
1
today = datetime.now().strftime(DATE_FORMAT)
2
today
Copied!
Get pipeline details
1
df = df_details.copy()
2
3
details = []
4
5
for _, row in df.iterrows():
6
# status part
7
dealstage = row.dealstage_label
8
probability = row.probability
9
detail = f"{dealstage} ({format_pourcentage(probability)})"
10
11
# amount part
12
amount = row.amount
13
number = row.dealname
14
forecasted_ = row.forecasted
15
if (probability < 1 and probability > 0):
16
detail = f"{detail}: <ul><li>Amount : {format_number(amount)}</li><li>Number : {number}</li><li>Weighted amount : <b>{format_number(forecasted_)}</b></li></ul>"
17
else:
18
detail = f"{detail}: {format_number(amount)}"
19
20
details += [detail]
21
22
details
Copied!
Get inactives deals
1
df_inactive = df_sales_c.copy()
2
3
df_inactive.hs_lastmodifieddate = pd.to_datetime(df_inactive.hs_lastmodifieddate).dt.strftime(DATE_FORMAT)
4
5
df_inactive["inactive_time"] = (datetime.now() - pd.to_datetime(df_inactive.hs_lastmodifieddate, format=DATE_FORMAT)).dt.days
6
df_inactive.loc[(df_inactive["inactive_time"] > 30, "inactive")] = "inactive"
7
df_inactive = df_inactive[(df_inactive.inactive == 'inactive') &
8
(df_inactive.amount != 0) &
9
(df_inactive.probability > 0.) &
10
(df_inactive.probability < 1)].sort_values("amount", ascending=False).reset_index(drop=True)
11
12
df_inactive
Copied!
1
inactives = []
2
3
for _, row in df_inactive[:10].iterrows():
4
# status part
5
dealname = row.dealname
6
dealstage_label = row.dealstage_label
7
amount = row.amount
8
probability = row.probability
9
inactive = f"{dealname} ({dealstage_label}): <b>{format_number(amount)}</b>"
10
inactives += [inactive]
11
12
inactives
Copied!
Create pipeline waterfall
1
import plotly.graph_objects as go
2
3
fig = go.Figure(go.Waterfall(name="20",
4
orientation = "v",
5
measure = ["relative", "relative", "total", "relative", "total"],
6
x = ["Won", "Pipeline", "Forecast", "Missing", "Objective"],
7
textposition = "outside",
8
text = [format_number(won), format_varv(weighted), format_number(forecasted), format_varv(completion_v), format_number(objective)],
9
y = [won, weighted, forecasted, completion_v, objective],
10
decreasing = {"marker":{"color":"#33475b"}},
11
increasing = {"marker":{"color":"#33475b"}},
12
totals = {"marker":{"color":"#ff7a59"}}
13
))
14
15
16
fig.update_layout(title = "Sales Metrics", plot_bgcolor="#ffffff", hovermode='x')
17
fig.update_yaxes(tickprefix="€", gridcolor='#eaeaea')
18
fig.show()
Copied!
1
fig.write_html("GRAPH_FILE.html")
2
fig.write_image("GRAPH_IMG.png")
3
4
params = {"inline": True}
5
6
graph_url = naas.asset.add("GRAPH_FILE.html", params=params)
7
graph_image = naas.asset.add("GRAPH_IMG.png")
Copied!
Create email
1
def email_brief(today,
2
forecasted,
3
won,
4
weighted,
5
objective,
6
completion_p,
7
completion_v,
8
details,
9
inactives
10
):
11
content = {
12
'title': (f"<a href='{NAAS_WEBSITE}'>"
13
f"<img align='center' width='100%' target='_blank' style='border-radius:5px;'"
14
f"src='{HUBSPOT_CARD}' alt={EMAIL_DESCRIPTION}/>"
15
"</a>"),
16
17
'txt_intro': (f"Hi there,<br><br>"
18
f"Here is your weekly sales email as of {today}."),
19
20
'title_1': emailbuilder.text("Overview", font_size="27px", text_align="center", bold=True),
21
"text_1": emailbuilder.text(f"As of today, your yearly forecasted revenue is {format_number(forecasted)}."),
22
"list_1": emailbuilder.list([f"Won : {format_number(won)}",
23
f"Weighted pipeline : <b>{format_number(weighted)}</b>"]),
24
"text_1_2": emailbuilder.text(f"You need to find 👉 <u>{format_number(completion_v)}</u> to reach your goal !"),
25
"text_1_1": emailbuilder.text(f"Your yearly objective is {format_number(objective)} ({format_pourcentage(completion_p)} completion)."),
26
'image_1': emailbuilder.image(graph_image, link=graph_url),
27
28
'title_2': emailbuilder.text("🚀 Pipeline", font_size="27px", text_align="center", bold=True),
29
"list_2": emailbuilder.list(details),
30
31
'title_3': emailbuilder.text("🧐 Actions needed", font_size="27px", text_align="center", bold=True),
32
'text_3': emailbuilder.text("Here are deals where you need to take actions :"),
33
'list_3': emailbuilder.list(inactives),
34
'text_3_1': emailbuilder.text("If you need more details, connect to Hubspot with the link below."),
35
'button_1': emailbuilder.button(link="https://app.hubspot.com/",
36
text="Go to Hubspot",
37
background_color="#ff7a59"),
38
39
'title_4': emailbuilder.text("Glossary", text_align="center", bold=True, underline=True),
40
'list_4': emailbuilder.list(["Yearly forecasted revenue : Weighted amount + WON exclude LOST",
41
"Yearly objective : Input in script",
42
"Inactive deal : No activity for more than 30 days"]),
43
44
'footer_cs': emailbuilder.footer_company(naas=True),
45
}
46
47
email_content = emailbuilder.generate(display='iframe', **content)
48
return email_content
49
50
email_content = email_brief(today,
51
forecasted,
52
won,
53
weighted,
54
objective,
55
completion_p,
56
completion_v,
57
details,
58
inactives)
Copied!
Output
Send email
1
naas.notification.send(email_to,
2
email_subject,
3
email_content)
Copied!