Hubspotでエクスポートしたcsvを可視化したときの話

この記事の対象者

Hubspot からエクスポートした csv を元に何か議論したい、けどうまい方法がわからず力作業でコピペしたりしてる人。
「QiitaにHubspotについて投稿している人おるのかな」と思って検索すると、API駆使している記事が出てきて、「いや、難しすぎぃ」と思った人。
そんなあなたにお届け。

ちなみに僕は、HubspotにAPIが搭載されているのをさっき知りました。

はじめに

アルバイトに慣れてくると、色々と仕事を任されるようになってくる。（いつもありがとうございます）
今回は、Hubspot にて本登録を済ませた人がどれくらいフォームからエントリーしてるかを可視化してみます。
可視化する具体的な内容は以下の通り。

• 登録初月にn回エントリーした人が、mヶ月後に、何回エントリーしているかを見たい
• 面談済みの人が、どのようにエントリーしなくなっていってるかを知りたい。
• n月に仮登録から本登録に以降した人が、どれくらいエントリーしてくれているか知りたい。

これの可視化のために

1. 各月のエントリー数を書き出す
2. 仮登録と本登録の月がひと目で分かるようにする

そんなファイルを吐き出せば良いと考えました。

csvをエクスポートする

まずは hubspot からエクスポートした csv を見てみる。今回の場合、こんな感じ。これが2行目以降も続く。

Eメール	お名前	エントリー内容	Conversion Date	Conversion Page	Conversion Title
hoge@hoge .com	ドラえもん	hoge	2000-11-11 11:11:11 AM	https://...	Hoge

ファイル名は、hubspot-form-submissions... .csv として出力。

エクスポートは、一つ一つ手作業でする（API を使えるなら、他にうまい方法がありそう）。

エクスポートに抜け漏れが無いか、check 関数を定義した。

check関数

def check():
    # 自分が持ってるやつを取得（ついでにダブりっぽいやつを出力）
    no_list_i_have = i_have()
    
    # エクスポートすべきやつを取得（別途、dashboardなるcsvを出力する）
    no_list_in_hubspot,csvs = hubspot_have()

    # まだエクスポートしてないやつを出力
    un_export(no_list_i_have,no_list_in_hubspot,csvs)

上で定義した関数の中身は以下の感じ。

check関数 その2

def i_have():
    files = glob("hubspot*.csv")

    no_list_i_have = []
    no_list_i_have_dup = []
    for file in files:
        if "no-" in file:
            no_list_i_have.append(re.findall("no-\d+", file)[0][3:])

    """ダブりを出力する """
    print("------ ↓ 自分が持っているファイルでダブりが疑われるファイル ↓ ---------",end="\n\n")
    for x in [x for i, x in enumerate(no_list_i_have) if i != no_list_i_have.index(x)]:
        for file in files:
            if x in file:
                no_list_i_have_dup.append(file)

    no_list_i_have_dup = list(set(no_list_i_have_dup))
    for dup_f in no_list_i_have_dup:
        print(dup_f)
    return no_list_i_have


def hubspot_have():
    if len(glob("hubspot-form-dashboard*")) != 1:
        print("どのdashboardを参照しますか？")
        return
    else:
        dash_file = glob("hubspot-form-dashboard*")[0]

    with open(dash_file) as f:
        csvs = [x[1].strip("\"") for x in list(map(lambda s: s.strip().split(","), f.readlines()))]


    no_list_in_hubspot = []
    for csv in csvs:
        if "案件No." in csv:
            no_list_in_hubspot.append(re.findall("No.\d+",csv)[0].replace("No.","no-")[3:])
        
    return no_list_in_hubspot,csvs

def un_export(no_list_i_have,no_list_in_hubspot,csvs):
    print("\n------ ↓ hubspot よりエクスポートできていないと思われるファイル ↓ ---------",end="\n\n")
    for f in no_list_in_hubspot:
        if not f in no_list_i_have:
            for csv in csvs:
                if f in csv:
                    print(csv)

出力結果は下のような感じ。

ソースコードは汚いけど、アウトプットには関係ない。なんとか出力した。

ところで、ダブりが疑われるファイルの定義は、ファイル名の中に含まれる_no-mmdd\d\d_の部分が一致しているかどうか。上の例で言うと、061702がそれにあたる。正規表現で抽出した。

何月何日のcsvかを正規表現で読みとる

def when_csv(file_name):
    month = re.search("no-\d\d\d\d",file_name).group()[3:5]
    year = "2022"
    return year, month

今回は全部違う csv ファイルでダブってないから OK。

この関数を定義したはいいものの、完全自動化を目指すなら API 叩けた方がよいのも事実。早くつよつよエンジニアになりたい。

欲しい情報のみを取得

さて、この csv にある必要な情報とは、ずばりメルアド。

csv にメルアドがある ＝ エントリーしてくれている

というわけだから、メルアドがあるかどうかをカウントしてあげれば良い。
と、言うわけでglob.globで全ての csv を取得し、 pandas で読み込んでメルアドのカラムだけに絞り、数字をカウント。

メルアドがあるかチェック

def mail_exist_check(mail,csv_path): # csvは、pathで入れる必要あり
    try:
        df = pd.read_csv(csv_path, encoding="cp932") # utf-8でないと開けないやつあったので
    except:
        try:
            df = pd.read_csv(csv_path, encoding="utf-8")
        except:
            print(f"Encording error!: {csv_path}")
            
    return (df["Eメール"] == mail).sum()

あとは、mail_exist_check()で返却される数字を格納していくdfを定義しておいてやる。

countを全てしたdfを作成

def make_df_add(name,mail,register_date,temp_date,csv_files):
    
    # [name, mail, 登録日, 仮登録日,仮→本？,0, ..., 0]のリストを作成
    d_list = [name,mail,register_date,temp_date]
    while len(d_list) < len(initial_columns):
        d_list.append("")
    
    for _ in range(diff_month(start,end)+1):
        d_list.append(0)
    
    for csv_file in csv_files:
        year, month = when_csv(csv_file)
        count = mail_exist_check(mail,csv_file)
    
        # カウントを追加
        n = diff_month(datetime.date(int(year), int(month), 1),start)
        d_list[n+len(initial_columns)] += count
    
    return pd.DataFrame(d_list,index=columns).T

ここで、diff_monthはこちらを丸パクリした。

あとは、name、mail、仮登録日、本登録日をmake_df_addに渡していってやる、main関数を定義する。
※ 私のコーディング能力不足で、global文が入って読みにくいデス

main関数

def main():
    global columns, datas
    with open(input_file, encoding="utf-8") as f:
        datas = list(map(lambda s: s.strip().split("\t"), f.readlines()))
    columns = copy.deepcopy(initial_columns)
    
    for i in range(diff_month(start,end)+1):
        columns.append((start + relativedelta(months=i)).strftime('%Y/%m'))
    df = pd.DataFrame(columns=columns)

    for data in datas:
        while len(data) < len(initial_columns):
            data.append("")
        df_add = make_df_add(data[1],data[0],data[2],data[3],glob("hubspot-form-submissions*.csv"))
        df = pd.concat([df,df_add])
    
    if filename[-3:] == "csv":
        df.to_csv(filename,index=False,encoding='shift-jis', date_format='%Y%m%d')
    elif filename[-3:] == "lsx":
        df.to_excel(filename,index=False,encoding='shift-jis')

# ----------   自分で変えるところ ----------------- #
initial_columns = ["名前", "メルアド", "本登録日","仮登録日","仮→本が同月？","登録初月のエントリー数"]

# メルアド\t名前\t本登録日\t仮登録日\nのn行4列のテキストファイルを想定
input_file = "data.txt"

start, end = datetime.date(2022, 5, 1), datetime.date(2022, 12, 1)

filename = "本登録の人の推移.xlsx" # csvも可
sheet_name = "本登録の人の推移"
# ------------------------------------------------ #

main()

可視化

さて、これで集計が終わったがこれだけだと可視化とは言いにくいと思う。数字の羅列だし。

ってことで、数字に色を付けるという可視化を選択した。もっと良い可視化が絶対あるはず。恐縮ですが、良いアイデアをお持ちの方はコメントにてご教示くださると幸いです。

数字に色を付ける関数を以下に定義。
色の内訳は
#C0C0C0：エントリーの無い月（=0 のところ）
#0067C0：仮登録のあった月
#FF0000：本登録のあった月
デフォルトのカラー（黒）：エントリーのある月（≠0 のところ）

def add_color_sum():
    wb = openpyxl.load_workbook(filename)
    ws = wb['Sheet1']
    ws.title = sheet_name

    font = Font(bold=True,color='FF0000') #赤色
    font2 = Font(color='C0C0C0') # 灰色
    font3 = Font(bold=True,color='0067C0') # 青色

    for row in ws.iter_rows(min_col=len(initial_columns)+1, min_row=2):
        for cell in row:
            if cell.value ==0:
                ws[cell.coordinate].font = font2

    for row in ws.iter_rows(min_row=2):
        for cell in row:
            

            if (row[2].value is None) or (int(re.findall("/\d+/", row[2].value)[0].replace("/","")) < len(initial_columns)):
                regi_col = start.month
            else:
                regi_col = int(re.findall("/\d+/", row[2].value)[0].replace("/",""))
            
            if (row[3].value is None) or (int(re.findall("/\d+/", row[3].value)[0].replace("/","")) < len(initial_columns)):
                temp_col = start.month
            else:
                temp_col = int(re.findall("/\d+/", row[3].value)[0].replace("/",""))

            red_col = regi_col - start.month + len(initial_columns) +1
            blue_col = temp_col - start.month + len(initial_columns) +1

            
            # 仮登録＝本登録？
            if cell.col_idx == 5:
                ws[cell.coordinate].value = "o" if red_col==blue_col else "x"
            
            if cell.col_idx == blue_col:
                ws[cell.coordinate].font = font3
            
            if cell.col_idx == red_col:
                ws[cell.coordinate].font = font
                ws["F"+str(cell.row)].value = ws[cell.coordinate].value
      
    for i in range(len(row)):
        Col = row[i].coordinate[0]
        Row = int(row[0].coordinate[1:])
      
    wb.save(filename)

吐き出したExcelはこんな感じ。

可視化というとグラフが定番だけど、これはこれで耐えてるかな。
あとはフィルターなりで絞るなり、ヒストグラム描くなり、自由に分析してください！

ってことで社員さんにパス。

終わりに

全然プログラミング素人だけど、自分なりの仕事ができてよかった。
こういう、手作業でもできるけど、プログラミングして楽ちんにするってのがとても好き。

ソースコード全部

import pandas as pd
import re, datetime, openpyxl, copy
from glob import glob
from dateutil.relativedelta import relativedelta
from openpyxl.styles import Font
from openpyxl.styles.borders import Border, Side

def diff_month(d1, d2):
    if d1 > d2: d1, d2 = d2, d1
    return (d2.year - d1.year)*12 + d2.month - d1.month 

def mail_exist_check(mail,csv_path): # csvは、pathで入れる必要あり
    
    try:
        df = pd.read_csv(csv_path, encoding="cp932")
    except:
        try:
            df = pd.read_csv(csv_path, encoding="utf-8")
        except:
            print(f"Encording error!: {csv_path}")
            
    return (df["Eメール"] == mail).sum()

def when_csv(file_name):
    month = re.search("no-\d\d\d\d",file_name).group()[3:5]
    year = "2022"
    return year, month

def make_df_add(name,mail,register_date,temp_date,csv_files):
    
    # [name, mail, 登録日, 仮登録日,仮→本？,0, ..., 0]のリストを作成
    d_list = [name,mail,register_date,temp_date]
    while len(d_list) < len(initial_columns):
        d_list.append("")
    
    
    for _ in range(diff_month(start,end)+1):
        d_list.append(0)
    
    for csv_file in csv_files:
        year, month = when_csv(csv_file)
        count = mail_exist_check(mail,csv_file)
    
        # カウントを追加
        n = diff_month(datetime.date(int(year), int(month), 1),start)
        d_list[n+len(initial_columns)] += count
    
    return pd.DataFrame(d_list,index=columns).T

def add_color_sum():
    wb = openpyxl.load_workbook(filename)
    ws = wb['Sheet1']
    ws.title = sheet_name

    font = Font(bold=True,color='FF0000') #赤色
    font2 = Font(color='C0C0C0') # 灰色
    font3 = Font(bold=True,color='0067C0') # 青色

    for row in ws.iter_rows(min_col=len(initial_columns)+1, min_row=2):
        for cell in row:
            if cell.value ==0:
                ws[cell.coordinate].font = font2

    for row in ws.iter_rows(min_row=2):
        for cell in row:
            

            if (row[2].value is None) or (int(re.findall("/\d+/", row[2].value)[0].replace("/","")) < len(initial_columns)):
                regi_col = start.month
            else:
                regi_col = int(re.findall("/\d+/", row[2].value)[0].replace("/",""))
            
            if (row[3].value is None) or (int(re.findall("/\d+/", row[3].value)[0].replace("/","")) < len(initial_columns)):
                temp_col = start.month
            else:
                temp_col = int(re.findall("/\d+/", row[3].value)[0].replace("/",""))

            red_col = regi_col - start.month + len(initial_columns) +1
            blue_col = temp_col - start.month + len(initial_columns) +1

            
            # 仮登録＝本登録？
            if cell.col_idx == 5:
                ws[cell.coordinate].value = "o" if red_col==blue_col else "x"
            
            if cell.col_idx == blue_col:
                ws[cell.coordinate].font = font3
            
            if cell.col_idx == red_col:
                ws[cell.coordinate].font = font
                ws["F"+str(cell.row)].value = ws[cell.coordinate].value
    
    
    for i in range(len(row)):
        Col = row[i].coordinate[0]
        Row = int(row[0].coordinate[1:])
        
        # 総計の上の線
        ws[Col+str(Row+1)].border = Border(top=Side(style='thin', color='000000'))
        
        if i < len(initial_columns):
            continue
        else:
            ws[Col+str(Row+1)].value = f"=SUM({Col}2:{Col}{Row})" # 総計
            ws[Col+str(Row+2)].value = "" # 成約数
            ws[Col+str(Row+3)].value = f"=IFERROR({Col}{Row+2}/{Col}{Row+1}*100,)"

    ws.cell(row=Row+1, column=len(initial_columns), value="総計")
    ws.cell(row=Row+2, column=len(initial_columns), value="成約数")
    ws.cell(row=Row+3, column=len(initial_columns), value="成約率(%)")
    
    wb.save(filename)

def i_have():
    files = glob("hubspot*.csv")

    no_list_i_have = []
    no_list_i_have_dup = []
    for file in files:
        if "no-" in file:
            no_list_i_have.append(re.findall("no-\d+", file)[0][3:])

    """ダブりを出力する """
    print("------ ↓ 自分が持っているファイルでダブりが疑われるファイル ↓ ---------",end="\n\n")
    for x in [x for i, x in enumerate(no_list_i_have) if i != no_list_i_have.index(x)]:
        for file in files:
            if x in file:
                no_list_i_have_dup.append(file)

    no_list_i_have_dup = list(set(no_list_i_have_dup))
    for dup_f in no_list_i_have_dup:
        print(dup_f)
    return no_list_i_have


def hubspot_have():
    if len(glob("hubspot-form-dashboard*")) != 1:
        print("どのdashboardを参照しますか？")
        return
    else:
        dash_file = glob("hubspot-form-dashboard*")[0]

    with open(dash_file) as f:
        csvs = [x[1].strip("\"") for x in list(map(lambda s: s.strip().split(","), f.readlines()))]


    no_list_in_hubspot = []
    for csv in csvs:
        if "案件No." in csv:
            no_list_in_hubspot.append(re.findall("No.\d+",csv)[0].replace("No.","no-")[3:])
        
    return no_list_in_hubspot,csvs

def un_export(no_list_i_have,no_list_in_hubspot,csvs):
    print("\n------ ↓ hubspot よりエクスポートできていないと思われるファイル ↓ ---------",end="\n\n")
    for f in no_list_in_hubspot:
        if not f in no_list_i_have:
            for csv in csvs:
                if f in csv:
                    print(csv)

def check():
    no_list_i_have = i_have()
    no_list_in_hubspot,csvs = hubspot_have()
    un_export(no_list_i_have,no_list_in_hubspot,csvs)

""" ※ dashboardのcsvを予めエクスポートする必要があります
"""
check()

def main():
    global columns, datas
    with open(input_file, encoding="utf-8") as f:
        datas = list(map(lambda s: s.strip().split("\t"), f.readlines()))
    columns = copy.deepcopy(initial_columns)
    
    for i in range(diff_month(start,end)+1):
        columns.append((start + relativedelta(months=i)).strftime('%Y/%m'))
    df = pd.DataFrame(columns=columns)

    for data in datas:
        while len(data) < len(initial_columns):
            data.append("")
        
        # print(data)
        df_add = make_df_add(data[1],data[0],data[2],data[3],glob("hubspot-form-submissions*.csv"))
        df = pd.concat([df,df_add])
    
    if filename[-3:] == "csv":
        df.to_csv(filename,index=False,encoding='shift-jis', date_format='%Y%m%d')
    elif filename[-3:] == "lsx":
        df.to_excel(filename,index=False,encoding='shift-jis')
    
    add_color_sum()



# ----------   自分で変えるところ ----------------- #
initial_columns = ["名前", "メルアド", "本登録日","仮登録日","仮→本が同月？","登録初月のエントリー数"]

# メルアド\t名前\t本登録日\t仮登録日\nのn行4列のテキストファイルを想定
input_file = "data.txt"

start, end = datetime.date(2022, 5, 1), datetime.date(2022, 12, 1)

filename = "本登録の人の推移.xlsx"
sheet_name = "本登録の人の推移"
# ------------------------------------------------ #

main()