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!

Get all pipelines and dealstages

Update followers from linkedin

Last modified 2mo ago

Copy link

Edit on GitHub

Contents

Input

Model

Output