Get closed deals 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_closed_weekly.csv"
2
image_output = "HubSpot_closed_weekly.html"
3
html_output = "HubSpot_closed_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["closedate"] = pd.to_datetime(df["closedate"])
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='closedate')).agg({"hs_object_id": "count", "amount": "sum"}).reset_index()
12
df["closedate"] = df["closedate"] + timedelta(days=-1)
13
df = pd.melt(df, id_vars="closedate")
14
15
# Rename col
16
to_rename = {
17
"closedate": "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%U")
26
df = df[df["LABEL_ORDER"].astype(int) <= int(datetime.now().strftime("%Y%U"))]
27
28
# Calc variation
29
df_var = pd.DataFrame()
30
groups = df.GROUP.unique()
31
for group in groups:
32
tmp = df[df.GROUP == group].reset_index(drop=True)
33
for idx, row in tmp.iterrows():
34
if idx == 0:
35
value_n1 = 0
36
else:
37
value_n1 = tmp.loc[tmp.index[idx-1], "VALUE"]
38
tmp.loc[tmp.index[idx], "VALUE_COMP"] = value_n1
39
df_var = pd.concat([df_var, tmp]).fillna(0).reset_index(drop=True)
40
df_var["VARV"] = df_var["VALUE"] - df_var["VALUE_COMP"]
41
df_var["VARP"] = df_var["VARV"] / abs(df_var["VALUE_COMP"])
42
43
# Prep data
44
df_var["VALUE_D"] = df_var["VALUE"].map("{:,.0f}".format).str.replace(",", " ")
45
df_var["VARV_D"] = df_var["VARV"].map("{:,.0f}".format).str.replace(",", " ")
46
df_var.loc[df_var["VARV"] > 0, "VARV_D"] = "+" + df_var["VARV_D"]
47
df_var["VARP_D"] = df_var["VARP"].map("{:,.0%}".format).str.replace(",", " ")
48
df_var.loc[df_var["VARP"] > 0, "VARP_D"] = "+" + df_var["VARP_D"]
49
50
# Create hovertext
51
df_var["TEXT"] = ("<b>Deal closed as of " + df_var["LABEL"] + " : </b>" +
52
df_var["VALUE_D"] + "<br>" +
53
df_var["VARP_D"] + " vs last week (" + df_var["VARV_D"] + ")")
54
return df_var
55
56
df_trend = get_trend(df_deals, pipeline_id)
57
df_trend
Copied!

Output

Plotting a barchart with filters

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 - Closed deals 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.show()
77
return fig
78
79
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