derwaro

  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)