In my last blog, I wrote about AI & Sales; today, I will start the process of sales data analytics. I will be building a logistic regression model to assign a score to each of the prospects that a company can use to target potential deals. A higher score would mean that the deal is hot, i.e. is most likely to convert, whereas a lower score would mean that the deal is cold and will mostly not get converted.
When closing deals, a salesperson with a rich pipeline of potential clients must decide where to focus their time to close a particular deal. Often, salespeople make decisions based on their intuition and incomplete information. Using AI, data scientists compile historical information about a client, company, social media postings and the salesperson’s customer interaction history (e.g. emails sent, calls made, text sent etc. ); and rank the opportunities or leads in the pipeline according to their chances (probability) of closing successfully. One tool built on this methodology is Dealcode GmbH.
The data obtained was in several CSV files; I used MySQL to inspect and preprocess the data.
-- Creating NEW Table DROP TABLE IF EXISTS crm_activity; CREATE TABLE crm_activity ( id INT PRIMARY KEY, crmId INT, crmUserId INT, crmDealId INT, crmLeadId INT, crmDuration TIME NULL, crmType VARCHAR(255), crmDueTime TIME NULL, crmAddTime VARCHAR(255), crmUpdateTime VARCHAR(255), crmDueDate DATE, crmSubject VARCHAR(255), crmDone INT, isDeleted INT, createDate VARCHAR(255), companyId INT ); LOAD DATA LOCAL INFILE 'path/crm_activity.csv' IGNORE INTO TABLE crm_activity FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; SELECT * FROM crm_activity LIMIT 100;
Data type at a field like
createDate is presented as character data that is varying (
VARCHAR). Convert the data type to a
str_to_date, and insert the converted string-to-date in a new column. The string date-time column can then be deleted by using
ALTER TABLE crm_activity ADD ( crmAddTime_ts TIMESTAMP, crmUpdateTime_ts TIMESTAMP, createDate_ts TIMESTAMP ); UPDATE crm_activity SET crmAddTime_ts = str_to_date( crmAddTime, '"%Y-%m-%d %H:%i:%s"'); UPDATE crm_activity SET crmUpdateTime_ts = str_to_date( crmUpdateTime, '"%Y-%m-%d %H:%i:%s"'); UPDATE crm_activity SET createDate_ts = str_to_date( createDate, '"%Y-%m-%d %H:%i:%s"'); DELETE FROM crm_activity WHERE id=0; COMMIT;
MySQL automatically generates a database schema view from the created (now existing) database (
From the interface --> Database --> Reverse Engineer --> Follow the instructions).
Database schemas are essential because they help developers visualize how a database should be structured and give a clear point of reference about what tables and fields a project contains.
Fetching data from MySQL into Anaconda (python), requires
mysql.connector package, installed it by
pip install mysql-connector-python
mydb = mysql.connector.connect( host="localhost", user="myuser", password="yourpassword", database="name_of_the_database" ) # Loading and visualizing in Anaconda crm_deal = mydb.cursor() crm_deal.execute("SELECT * FROM crm_deal ") crm_deal_results = crm_deal.fetchall() for leads in crm_deal_results: print(leads[:10])
Continue reading - Deal Scoring - Part 2: Data processing with Python
Top comments (0)