I recently was tasked to write a custom python script importing some given .csv file into Close.com via their API. It was the first time I had to use their API, so there was a lot of trial and error involved. Additionally the input data had to be serverly cleaned up to be compatible with the API requirements. Apart from the mere import the script also should create a little report based on the same input data.

The full solution including setup, sample input file can be found at: derwaro/CloseAPItask

  1#!/usr/bin/env python3.8
  2import babel.numbers
  3import csv
  4from closeio_api import Client
  5from datetime import datetime
  6from dateutil.parser import parse as parse_date
  7from decimal import Decimal
  8from dotenv import load_dotenv
  9from email_validator import validate_email, EmailNotValidError
 10from statistics import median
 11import os
 12
 13# load the .env file containing your api key and custom field ids
 14load_dotenv()
 15
 16# set locale so that numbers are correctly formated in the report
 17currency = os.getenv("BABEL_CURRENCY") or "USD"
 18locale = os.getenv("BABEL_LOCALE") or "en_US"
 19
 20
 21# vars for close api. enter your key and ids in ""
 22api_key = os.getenv("API_KEY")
 23custom_company_founded_api_id = "custom." + os.getenv("CUSTOM_COMPANY_FOUNDED_API_ID")
 24custom_company_revenue_api_id = "custom." + os.getenv("CUSTOM_COMPANY_REVENUE_API_ID")
 25
 26
 27# vars general
 28csv_raw_data = []
 29leads = {}
 30contacts = []
 31titles = ["Dr.", "Mr.", "Ms.", "Mrs."]
 32
 33# vars for report
 34correctStart = False
 35correctEnd = False
 36states = []
 37report = []
 38dstates = {}
 39
 40
 41# initialize closeio_api as per documentation
 42api = Client(api_key)
 43
 44# extracts the csv into a list for easier handling
 45# every row from the csv will be a list within csv_raw_data. every column within a row will be a string inside this list of lists
 46with open("import.csv", "r") as f:
 47    reader = csv.reader(f, delimiter=",")
 48    for row in reader:
 49        csv_raw_data.append(row)
 50# remove title row
 51csv_raw_data.pop(0)
 52
 53# clean up and prepare names and titles
 54for r in csv_raw_data:
 55    if r[1]:
 56        if "  " in r[1]:
 57            r[1] = r[1].replace("  ", " ")
 58    r[1] = r[1].title()
 59
 60# clean up and prepare emails:
 61for r in csv_raw_data:
 62    if "," in r[2]:
 63        tmp = r[2].split(",")
 64        r[2] = [i for i in tmp]
 65    elif ";" in r[2]:
 66        tmp = r[2].split(";")
 67        r[2] = [i for i in tmp]
 68    elif r[2] == "":
 69        r[2] = [""]
 70    else:
 71        r[2] = [r[2]]
 72    if r[2] != [""]:
 73        tmp = []
 74        for e in r[2]:
 75            try:
 76                validate_email(e)
 77                tmp.append(e)
 78            except EmailNotValidError:
 79                pass
 80            if tmp != []:
 81                r[2] = tmp
 82            else:
 83                r[2] = [""]
 84
 85# clean up and prepare phone numbers:
 86for r in csv_raw_data:
 87    if r[3].find("+") == -1:
 88        r[3] = [""]
 89    else:
 90        tmp = r[3]
 91        if len(tmp) < 5:
 92            r[3] = [""]
 93        if "\n" in tmp:
 94            r[3] = [i for i in tmp.split("\n")]
 95            r[3] = ",".join(r[3])
 96            num = ""
 97            for i in r[3]:
 98                for j in i:
 99                    if j.isdigit():
100                        num += j
101                    if j == ",":
102                        num += j
103            r[3] = num.split(",")
104        else:
105            num = ""
106            for i in tmp:
107                if i.isdigit():
108                    num += i
109                if i == ",":
110                    num += i
111                r[3] = num.split(",")
112    if r[3] != [""]:
113        r[3] = ["+" + p for p in r[3]]
114
115
116# clean up and prepare company founded date:
117for r in csv_raw_data:
118    if r[4] != "":
119        tmp = r[4].split(".")
120        for j, i in enumerate(tmp):
121            if len(i) == 1:
122                tmp[j] = "0" + i
123            r[4] = ".".join(tmp)
124
125# clean up and prepare company revenue:
126for r in csv_raw_data:
127    if r[5] != "":
128        if "," in r[5]:
129            r[5] = r[5].replace(",", "")
130        if "$" in r[5]:
131            r[5] = r[5].replace("$", "")
132
133# clean up and prepare company state:
134for r in csv_raw_data:
135    if r[6] != "":
136        if "\n" or "\r" in r[6]:
137            r[6] = r[6].replace("\n", "")
138            r[6] = r[6].replace("\r", "")
139
140
141# prepare payload
142
143# this gets a list of all leads, ready for import
144for i in csv_raw_data:
145    if i[0] not in leads.keys():
146        leads[i[0]] = {}
147
148# add name, addresses/state, company founded date and company revenue to the payload. if there are contacts a contact key will be added too.
149for i in leads.keys():
150    for c in csv_raw_data:
151        if i == c[0] and leads[i] == {}:
152            leads[i]["name"] = c[0]
153            if c[6]:
154                leads[i]["addresses"] = [{"state": c[6]}]
155            if c[4] != "":
156                leads[i][custom_company_founded_api_id] = str(parse_date(c[4]))
157            if c[5] != "":
158                leads[i][custom_company_revenue_api_id] = float(c[5])
159            if c[1] != "" or c[2] != [] or c[3] != [""]:
160                leads[i]["contacts"] = []
161
162# add the contacts to the corresponding lead
163for i in leads.keys():
164    for c in csv_raw_data:
165        if i == c[0]:
166            if "contacts" in leads[i]:
167                title_and_name = c[1].split(" ")
168                if title_and_name[0] in titles:
169                    title = title_and_name[0]
170                    name = [n for n in title_and_name[1:]]
171                    name = " ".join(name)
172                else:
173                    title = ""
174                    name = " ".join(title_and_name)
175                emails = []
176                for e in c[2]:
177                    if c[2] != [""]:
178                        emails.append({"email": e})
179                    else:
180                        pass
181                    # if c[2] == [""]:
182                    #    emails.append({"email": "no@mail.com"})
183                    # else:
184                    #    emails.append({"email": e})
185
186                phones = []
187                for p in c[3]:
188                    if c[3] != [""]:
189                        phones.append({"phone": p})
190                    else:
191                        pass
192                # print(phones)
193                # for j in i:
194                #    phones.append({"phone": j})
195                tmp = {"name": name, "title": title, "emails": emails, "phones": phones}
196                # print(tmp)
197                leads[i]["contacts"].append(tmp)
198
199# remove empty contact details from contacts
200for company in leads.values():
201    for contact in company.get("contacts", []):
202        if contact["emails"] == []:
203            del contact["emails"]
204        if contact["phones"] == []:
205            del contact["phones"]
206        if contact["name"] == "":
207            del contact["name"]
208        if contact["title"] == "":
209            del contact["title"]
210
211# check if the former loop removed all contact fields and removes the contacts key from the lead to comply with api requirements
212for company in leads.values():
213    if company["contacts"] == [{}]:
214        del company["contacts"]
215
216
217# post the payload to the close api
218for key in leads.keys():
219    lead = api.post("lead", data=leads[key])
220
221
222# create report
223print("Report by US State/Lead/Revenue")
224# gather startdate and enddate from user
225while correctStart == False:
226    startdate = input("Please choose a start date in format DD.MM.YYYY: ")
227    try:
228        startdate = datetime.strptime(startdate, "%d.%m.%Y").date()
229        correctStart = True
230    except ValueError:
231        correctStart = False
232while correctEnd == False:
233    enddate = input("Please choose a end date in format DD.MM.YYYY: ")
234    try:
235        enddate = datetime.strptime(enddate, "%d.%m.%Y").date()
236        correctEnd = True
237    except ValueError:
238        correctEnd = False
239
240
241# list of unique US States for report
242for i in csv_raw_data:
243    if i[-1] not in states and i[-1] != "":
244        states.append(i[-1])
245
246# prepare content of report according to csv headline
247for i in states:
248    dstates[i] = {
249        "USState": i,
250        "Leads": 0,
251        "MostRevenue": ["", 0.0],
252        "TotalRevenue": 0.0,
253        "MedianRevenue": [],
254    }
255
256# add the content to each state, if the lead/company was founded between startdate and enddate and has the necessary fields (i.e. addresses/state, company founded date and company revenue)
257for l in leads.keys():
258    if (
259        "addresses" in leads[l]
260        and custom_company_founded_api_id in leads[l]
261        and custom_company_revenue_api_id in leads[l]
262    ):
263        if (
264            startdate
265            <= datetime.strptime(
266                leads[l][custom_company_founded_api_id], "%Y-%m-%d %H:%M:%S"
267            ).date()
268            <= enddate
269        ):
270            state = leads[l]["addresses"][0]["state"]
271            if state in dstates:
272                dstates[state]["Leads"] += 1
273            if custom_company_revenue_api_id in leads[l]:
274                if state in dstates:
275                    dstates[state]["TotalRevenue"] += leads[l][
276                        custom_company_revenue_api_id
277                    ]
278                    dstates[state]["MedianRevenue"].append(
279                        leads[l][custom_company_revenue_api_id]
280                    )
281                    if (
282                        leads[l][custom_company_revenue_api_id]
283                        > dstates[state]["MostRevenue"][1]
284                    ):
285                        dstates[state]["MostRevenue"][0] = leads[l]["name"]
286                        dstates[state]["MostRevenue"][1] = leads[l][
287                            custom_company_revenue_api_id
288                        ]
289
290# remove states with no companies founded within startdate and enddate:
291for s in dstates.copy().keys():
292    if dstates[s]["Leads"] == 0:
293        del dstates[s]
294
295# use median function to set the MedianRevenue right and format the number according to babel/locale with currency sign, thousands-separator and comma
296for s in dstates.keys():
297    if dstates[s]["MedianRevenue"] != []:
298        dstates[s]["MedianRevenue"] = babel.numbers.format_currency(
299            Decimal(median(dstates[s]["MedianRevenue"])), currency, locale=locale
300        )
301    if dstates[s]["TotalRevenue"] != 0.0:
302        dstates[s]["TotalRevenue"] = babel.numbers.format_currency(
303            Decimal(dstates[s]["TotalRevenue"]), currency, locale=locale
304        )
305
306# set the key MostRevenue to only be the Leadname:
307for s in dstates.keys():
308    dstates[s]["MostRevenue"] = dstates[s]["MostRevenue"][0]
309
310# set the filename for the csv/report
311filename = f"Report_Leads_between_{startdate}_and_{enddate}.csv"
312
313# write the report to the csv, store it inside of current directory
314with open(filename, "w", newline="") as csvfile:
315    writer = csv.writer(csvfile)
316    writer.writerow(
317        [
318            "US State",
319            "Total number of leads",
320            "The lead with most revenue",
321            "Total revenue",
322            "Median revenue",
323        ]
324    )
325
326    for state, state_data in dstates.items():
327        row = [
328            state_data["USState"],
329            state_data["Leads"],
330            state_data["MostRevenue"],
331            state_data["TotalRevenue"],
332            state_data["MedianRevenue"],
333        ]
334        writer.writerow(row)