Send closed deals weekly
Tags: #hubspot #crm #sales #deal #scheduler #asset #html #png #csv #naas_drivers #naas #analytics #automation #image #plotly
Last update: 2023-04-12 (Created: 2022-11-23)
Description: This notebook send a weekly email based on your deals closed.
from naas_drivers import hubspot
from datetime import datetime, timedelta
import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import naas
👉 Starting November 30, 2022, HubSpot API keys no longer enable access to HubSpot APIs, so in Naas version 2.8.3 and above, you need create a private app and use the access token.
# Enter Your Access Token
HS_ACCESS_TOKEN = naas.secret.get("HS_ACCESS_TOKEN") or "YOUR_HS_ACCESS_TOKEN"
Here below you can select your pipeline.
If not, all deals will be taken for the analysis
df_pipelines = hubspot.connect(HS_ACCESS_TOKEN).pipelines.get_all()
df_pipelines
pipeline_id = "8432671"
name_output = "HubSpot_closed_weekly"
csv_output = f"{name_output}.csv"
image_output = f"{name_output}.png"
html_output = f"{name_output}.htlm"
naas.scheduler.add(cron="0 8 * * *")
# -> Uncomment the line below (by removing the hashtag) to remove your scheduler
# naas.scheduler.delete()
df_deals = hubspot.connect(HS_ACCESS_TOKEN).deals.get_all()
df_deals
def get_trend(df_deals, pipeline):
df = df_deals.copy()
# Filter data
df = df[df["pipeline"].astype(str) == str(pipeline)]
# Prep data
df["closedate"] = pd.to_datetime(df["closedate"])
df["amount"] = df.apply(
lambda row: float(row["amount"])
if str(row["amount"]) not in ["None", ""]
else 0,
axis=1,
)
# Calc by week
df = (
df.groupby(pd.Grouper(freq="W", key="closedate"))
.agg({"hs_object_id": "count", "amount": "sum"})
.reset_index()
)
df["closedate"] = df["closedate"] + timedelta(days=-1)
df = pd.melt(df, id_vars="closedate")
# Rename col
to_rename = {"closedate": "LABEL_ORDER", "variable": "GROUP", "value": "VALUE"}
df = (
df.rename(columns=to_rename)
.replace("hs_object_id", "No of deals")
.replace("amount", "Amount")
)
df["YEAR"] = df["LABEL_ORDER"].dt.strftime("%Y")
df = df[df["YEAR"] == datetime.now().strftime("%Y")]
df["LABEL"] = df["LABEL_ORDER"].dt.strftime("%Y-W%U")
df["LABEL_ORDER"] = df["LABEL_ORDER"].dt.strftime("%Y%U")
df = df[df["LABEL_ORDER"].astype(int) <= int(datetime.now().strftime("%Y%U"))]
# Calc variation
df_var = pd.DataFrame()
groups = df.GROUP.unique()
for group in groups:
tmp = df[df.GROUP == group].reset_index(drop=True)
for idx, row in tmp.iterrows():
if idx == 0:
value_n1 = 0
else:
value_n1 = tmp.loc[tmp.index[idx - 1], "VALUE"]
tmp.loc[tmp.index[idx], "VALUE_COMP"] = value_n1
df_var = pd.concat([df_var, tmp]).fillna(0).reset_index(drop=True)
df_var["VARV"] = df_var["VALUE"] - df_var["VALUE_COMP"]
df_var["VARP"] = df_var["VARV"] / abs(df_var["VALUE_COMP"])
# Prep data
df_var["VALUE_D"] = df_var["VALUE"].map("{:,.0f}".format).str.replace(",", " ")
df_var["VARV_D"] = df_var["VARV"].map("{:,.0f}".format).str.replace(",", " ")
df_var.loc[df_var["VARV"] > 0, "VARV_D"] = "+" + df_var["VARV_D"]
df_var["VARP_D"] = df_var["VARP"].map("{:,.0%}".format).str.replace(",", " ")
df_var.loc[df_var["VARP"] > 0, "VARP_D"] = "+" + df_var["VARP_D"]
# Create hovertext
df_var["TEXT"] = (
"<b>Deal closed as of "
+ df_var["LABEL"]
+ " : </b>"
+ df_var["VALUE_D"]
+ "<br>"
+ df_var["VARP_D"]
+ " vs last week ("
+ df_var["VARV_D"]
+ ")"
)
return df_var
df_trend = get_trend(df_deals, pipeline_id)
df_trend
def create_barchart(df, label, group, value, varv, varp):
# Create figure with secondary y-axis
fig = make_subplots(specs=[[{"secondary_y": True}]])
# Add traces
df1 = df[df[group] == "No of deals"].reset_index(drop=True)[:]
total_volume = "{:,.0f}".format(df1[value].sum()).replace(",", " ")
var_volume = df1.loc[df1.index[-1], varv]
positive = False
if var_volume > 0:
positive = True
var_volume = "{:,.0f}".format(var_volume).replace(",", " ")
if positive:
var_volume = f"+{var_volume}"
fig.add_trace(
go.Bar(
name="No of deals",
x=df1[label],
y=df1[value],
offsetgroup=0,
hoverinfo="text",
text=df1["VALUE_D"],
hovertext=df1["TEXT"],
marker=dict(color="#33475b"),
),
secondary_y=False,
)
df2 = df[df[group] == "Amount"].reset_index(drop=True)[:]
total_value = "{:,.0f}".format(df2[value].sum()).replace(",", " ")
var_value = df2.loc[df2.index[-1], varv]
positive = False
if var_value > 0:
positive = True
var_value = "{:,.0f}".format(var_value).replace(",", " ")
if positive:
var_value = f"+{var_value}"
fig.add_trace(
go.Bar(
name="Amount",
x=df2[label],
y=df2[value],
text=df2["VALUE_D"] + " K€",
offsetgroup=1,
hoverinfo="text",
hovertext=df2["TEXT"],
marker=dict(color="#ff7a59"),
),
secondary_y=True,
)
# Add figure title
fig.update_layout(
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>",
title_font=dict(family="Arial", size=20, color="black"),
legend=None,
plot_bgcolor="#ffffff",
width=1200,
height=800,
paper_bgcolor="white",
xaxis_title="Weeks",
xaxis_title_font=dict(family="Arial", size=11, color="black"),
)
# Set y-axes titles
fig.update_yaxes(
title_text="No of deals",
title_font=dict(family="Arial", size=11, color="black"),
secondary_y=False,
)
fig.update_yaxes(
title_text="Amount in K€",
title_font=dict(family="Arial", size=11, color="black"),
secondary_y=True,
)
fig.show()
return fig
fig = create_barchart(df_trend, "LABEL", "GROUP", "VALUE", "VARV", "VARP")
# Export in HTML
df_trend.to_csv(csv_output, index=False)
fig.write_image(image_output)
fig.write_html(html_output)
# Shave with naas
naas.asset.add(csv_output)
naas.asset.add(image_output)
naas.asset.add(html_output, params={"inline": True})
# -> Uncomment the line below (by removing the hashtag) to delete your asset
# naas.asset.delete(csv_output)
# naas.asset.delete(image_output)
# naas.asset.delete(html_output)
Last modified 1mo ago