This content originally appeared on DEV Community and was authored by Ochwada Linda
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 timestamp using 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 DROP.
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 Schema Generated
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.
Data Processing
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
This content originally appeared on DEV Community and was authored by Ochwada Linda
Ochwada Linda | Sciencx (2022-06-23T11:34:24+00:00) Deal Scoring- Part 1: Data Pre-processing in MySQL. Retrieved from https://www.scien.cx/2022/06/23/deal-scoring-part-1-data-pre-processing-in-mysql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.