Get new deals created weekly
Tags: #hubspot #crm #sales #deal #scheduler #asset #html #png #csv #naas_drivers #naas
Author: Florent Ravenel

Input

1
#-> Uncomment the 2 lines below (by removing the hashtag) to schedule your job everyday at 8:00 AM (NB: you can choose the time of your scheduling bot)
2
# import naas
3
# naas.scheduler.add(cron="0 8 * * *")
4
5
#-> Uncomment the line below (by removing the hashtag) to remove your scheduler
6
# naas.scheduler.delete()
Copied!

Import libraries

1
from naas_drivers import hubspot
2
from datetime import timedelta
3
import pandas as pd
4
import plotly.graph_objects as go
5
from plotly.subplots import make_subplots
Copied!

Setup your HubSpot

👉 Access your HubSpot API key
1
HS_API_KEY = 'YOUR_HUBSPOT_API_KEY'
Copied!

Select your pipeline ID

Here below you can select your pipeline. If not, all deals will be taken for the analysis
1
df_pipelines = hubspot.connect(HS_API_KEY).pipelines.get_all()
2
df_pipelines
Copied!
1
pipeline_id = None
Copied!

Setup Outputs

1
csv_output = "HubSpot_new_deals_weekly.csv"
2
image_output = "HubSpot_new_deals_weekly.html"
3
html_output = "HubSpot_new_deals_weekly.png"
Copied!

Model

Get all deals

1
df_deals = hubspot.connect(HS_API_KEY).deals.get_all()
2
df_deals
Copied!

Create trend data

1
def get_trend(df_deals, pipeline):
2
df = df_deals.copy()
3
# Filter data
4
df = df[df["pipeline"].astype(str) == str(pipeline)]
5
6
# Prep data
7
df["createdate"] = pd.to_datetime(df["createdate"])
8
df["amount"] = df.apply(lambda row: float(row["amount"]) if str(row["amount"]) not in ["None", ""] else 0, axis=1)
9
10
# Calc by week
11
df = df.groupby(pd.Grouper(freq='W', key='createdate')).agg({"hs_object_id": "count", "amount": "sum"}).reset_index()
12
df["createdate"] = df["createdate"] + timedelta(days=-1)
13
df = pd.melt(df, id_vars="createdate")
14
15
# Rename col
16
to_rename = {
17
"createdate": "LABEL_ORDER",
18
"variable": "GROUP",
19
"value": "VALUE"
20
}
21
df = df.rename(columns=to_rename).replace("hs_object_id", "No of deals").replace("amount", "Amount")
22
df["YEAR"] = df["LABEL_ORDER"].dt.strftime("%Y")
23
df = df[df["YEAR"] == datetime.now().strftime("%Y")]
24
df["LABEL"] = df["LABEL_ORDER"].dt.strftime("%Y-W%U")
25
df["LABEL_ORDER"] = df["LABEL_ORDER"].dt.strftime("%Y%m%d")
26
27
# Calc variation
28
df_var = pd.DataFrame()
29
groups = df.GROUP.unique()
30
for group in groups:
31
tmp = df[df.GROUP == group].reset_index(drop=True)
32
for idx, row in tmp.iterrows():
33
if idx == 0:
34
value_n1 = 0
35
else:
36
value_n1 = tmp.loc[tmp.index[idx-1], "VALUE"]
37
tmp.loc[tmp.index[idx], "VALUE_COMP"] = value_n1
38
df_var = pd.concat([df_var, tmp]).fillna(0).reset_index(drop=True)
39
df_var["VARV"] = df_var["VALUE"] - df_var["VALUE_COMP"]
40
df_var["VARP"] = df_var["VARV"] / abs(df_var["VALUE_COMP"])
41
42
# Prep data
43
df_var["VALUE_D"] = df_var["VALUE"].map("{:,.0f}".format).str.replace(",", " ")
44
df_var["VARV_D"] = df_var["VARV"].map("{:,.0f}".format).str.replace(",", " ")
45
df_var.loc[df_var["VARV"] > 0, "VARV_D"] = "+" + df_var["VARV_D"]
46
df_var["VARP_D"] = df_var["VARP"].map("{:,.0%}".format).str.replace(",", " ")
47
df_var.loc[df_var["VARP"] > 0, "VARP_D"] = "+" + df_var["VARP_D"]
48
49
# Create hovertext
50
df_var["TEXT"] = ("<b>Deal created as of " + df_var["LABEL"] + " : </b>" +
51
df_var["VALUE_D"] + "<br>" +
52
df_var["VARP_D"] + " vs last week (" + df_var["VARV_D"] + ")")
53
return df_var
54
55
df_trend = get_trend(df_deals, pipeline_id)
56
df_trend
Copied!

Output

Plotting a barchart

1
def create_barchart(df, label, group, value, varv, varp):
2
# Create figure with secondary y-axis
3
fig = make_subplots(specs=[[{"secondary_y": True}]])
4
5
# Add traces
6
df1 = df[df[group] == "No of deals"].reset_index(drop=True)[:]
7
total_volume = "{:,.0f}".format(df1[value].sum()).replace(",", " ")
8
var_volume = df1.loc[df1.index[-1], varv]
9
positive = False
10
if var_volume > 0:
11
positive = True
12
var_volume = "{:,.0f}".format(var_volume).replace(",", " ")
13
if positive:
14
var_volume = f"+{var_volume}"
15
fig.add_trace(
16
go.Bar(
17
name="No of deals",
18
x=df1[label],
19
y=df1[value],
20
offsetgroup=0,
21
hoverinfo="text",
22
text=df1["VALUE_D"],
23
hovertext=df1["TEXT"],
24
marker=dict(color="#33475b")
25
),
26
secondary_y=False,
27
)
28
29
df2 = df[df[group] == "Amount"].reset_index(drop=True)[:]
30
total_value = "{:,.0f}".format(df2[value].sum()).replace(",", " ")
31
var_value = df2.loc[df2.index[-1], varv]
32
positive = False
33
if var_value > 0:
34
positive = True
35
var_value = "{:,.0f}".format(var_value).replace(",", " ")
36
if positive:
37
var_value = f"+{var_value}"
38
fig.add_trace(
39
go.Bar(
40
name="Amount",
41
x=df2[label],
42
y=df2[value],
43
text=df2["VALUE_D"] + " K€",
44
offsetgroup=1,
45
hoverinfo="text",
46
hovertext=df2["TEXT"],
47
marker=dict(color="#ff7a59")
48
),
49
secondary_y=True,
50
)
51
52
# Add figure title
53
fig.update_layout(
54
title=f"<b>Hubspot - New deals created this year</b><br><span style='font-size: 14px;'>Total deals: {total_volume} ({total_value} K€) | This week: {var_volume} ({var_value} K€) vs last week</span>",
55
title_font=dict(family="Arial", size=20, color="black"),
56
legend=None,
57
plot_bgcolor="#ffffff",
58
width=1200,
59
height=800,
60
paper_bgcolor="white",
61
xaxis_title="Weeks",
62
xaxis_title_font=dict(family="Arial", size=11, color="black"),
63
)
64
65
# Set y-axes titles
66
fig.update_yaxes(
67
title_text="No of deals",
68
title_font=dict(family="Arial", size=11, color="black"),
69
secondary_y=False
70
)
71
fig.update_yaxes(
72
title_text="Amount in K€",
73
title_font=dict(family="Arial", size=11, color="black"),
74
secondary_y=True
75
)
76
# fig.update_xaxes(rangeslider_visible=True)
77
fig.show()
78
return fig
79
80
fig = create_barchart(df_trend, "LABEL", "GROUP", "VALUE", "VARV", "VARP")
Copied!

Export and share graph

1
# Export in HTML
2
df_trend.to_csv(csv_output, index=False)
3
fig.write_image(image_output)
4
fig.write_html(html_output)
5
6
# Shave with naas
7
naas.asset.add(csv_output)
8
naas.asset.add(image_output)
9
naas.asset.add(html_output, params={"inline": True})
10
11
#-> Uncomment the line below (by removing the hashtag) to delete your asset
12
# naas.asset.delete(csv_output)
13
# naas.asset.delete(image_output)
14
# naas.asset.delete(html_output)
Copied!
Copy link
Edit on GitHub