Building Awesome CRM – Part 1

If you are an organization with a product portfolio for other businesses, your sales team must be interacting with prospective customers. These interactions might stretch over weeks, months, and at times even years. It becomes therefore important for the organization to have CRM (Customer Relationship Management) systems that empower sales team to stay on top of their client interactions and have visibility so that they can prioritize accordingly. At the same time, it becomes imperative for the management to have a view of what’s happening, have an ability to forecast accurately, and detect patterns in the sales pipelines.

For startups, investing in expensive off-the-shelf CRM solutions is usually not an easy choice to make. Even if there are solutions that meet the price ask, startups typically require need high degree of customization, which is difficult to achieve with a 3rd party solution. If this sounds familiar, you can follow this section for building an awesome CRM that’s 100% customizable (you got the source code!), can be easily integrated with other applications and extended to meet growing needs. I must, however, mention that it is no substitute to the mature CRM solutions such as SalesForce, by any stretch of imagination. My startup (ePayLater) used both the bespoke solution and the off-the-shelf solution (we did SalesForce), and the strategy served us well.

I have used Streamlit – which is almost like a discovery of the year for me :), to build the CRM functionalities. It’s a fantastic tool wherein you can use power of Python at the backend to build beautiful and interactive dashboards. In short, it’s magic.

Figure 1: Screenshot of Add Merchant Section

For the starters, we will build placeholders for core modules. This would include modules for adding merchant (‘Add Merchant’), adding or updating opportunity (‘Add/Update Opportunity’), and reports (‘Scorecards’). In my implementation, I have included creating contacts within ‘Add Merchant’. You may want to keep it separate if that works better for you. I have built an Introduction section (‘Introduction’) which is the like the home page of the application. I had used this section to remind the team about company values, business goals, product videos, motivational quotes etc. Well, Streamlit allows you to embed media – video, images etc. so you can make this section as rich as you like. More on that later. Module ‘View Raw Data’ allows user to slice and dice the pipeline and self-assess performance, make a day plan etc. Lastly, ‘CRM Admin’ module is for carrying out system administration tasks such as deleting/modifying database tables etc. It was required in our case as few users were more comfortable using Google Sheets prior to transition and we wanted to ensure that the switch to be gradual for the users, and not requiring downtime of any sort.

Once the application is done, you can deploy it on AWS, Azure or any other PaaS platform. I found that Heroku makes it super easy to deploy Streamlit apps. While I will not get into the deployment part in my blog here, but let me tell you it’s very easy and shouldn’t take more than 45 mins even in the first go, and less than 10 thereafter. I promise to share a link to an excellent tutorial to help you with this.

def CRMLauncher()
option = st.sidebar.radio('CRM', ['Introduction', 'Scorecard','Add Merchant', 'Add/Update Opportunity', 'View Raw Data','CRM Admin'])
if option == 'Introduction':
    st.title('Welcome to CRM')
    st.subheader('To get started, select one of the options from the menu on bottom left')
elif option == 'Scorecard':
    CRM_summary()
elif option == 'Add Merchant':
    CreateMerchant()
elif option == 'Add/Update Opportunity':        
    SalesUpdate()
elif option == 'View Raw Data':
    ViewRawData()
elif option == 'CRM Admin':
    CRMAdmin()

In above code, have just created the main navigation menu. You should be able to view it in the figure 1 above. If the user selects "Add Merchant", I call CreateMerchant() function below

def CreateMerchant():
    df = pd.read_csv('Validations.csv')
    M_types = df['Type'].dropna()
    M_sizes = df['Size'].dropna()
    M_industries = df['Industry'].dropna()
    M_categories = df['Category'].dropna()
    M_subcategories = df['Sub_Category'].dropna()   
    M_lead_sources = df['Lead_Source'].dropna()
    
    M_name = st.text_input('Enter Merchant Name')
    if M_name: 
        while True:                            
            if not DeDupeCheck(M_name):
                m_id = assignMID(M_name)
                break
    else:
        m_id = 0
        st.write("Please enter merchant name")

    st.markdown("_**Basic Details**_")
    M_type = st.selectbox("Merchant Type", M_types)
    M_size = st.selectbox("Merchant Size", M_sizes)
    M_industry = st.selectbox("Industry", M_industries)
    M_category = st.multiselect("Category", M_categories)
    M_sub_category = st.multiselect("Sub Category", M_subcategories)               
        
    st.write("_**Lead Source**_")
    M_lead_source = st.selectbox("Lead Source", M_lead_sources)

    st.write("_**Additional Information**_")
    M_addinfo = st.text_input("Merchant details or any other additional information")          
            
    M_record = [[m_id, M_name, M_type, M_size, M_industry, M_category, M_sub_category, M_lead_source, M_addinfo]]
    cols = ['Merchant ID', 'Merchant Name', 'Merchant Type', 'Size', 'Industry', 'Category', 'Sub Category', 'Lead source', 'Addl Info']
    df = pd.DataFrame(M_record, index = [m_id], columns = cols)
    for col in df.columns:
        df[col] = df[col].astype('str')

    st.markdown("_**Merchant Summary**_")
    st.table(df.T)
    if st.button("Add Merchant to DB"):
        if m_id > 0:
            UpdateMerchantDB(df)
        else:
            st.warning("Please enter the required fields")
           
    st.write("-----------------------------------------------------------------------------------------------")
    st.markdown("_**Contact Manager**_")
    if st.checkbox("Add Contact", value = True):
        AddContact(m_id)     

Am presuming there would be certain specific industries or sub-industries your products would be catering to. Further, your business development team might be reaching out to different types of entities. For e.g in our business, we on-board online merchants and also financial institutions, both of which are enterprise/B2B sales. Depending upon you want to bucketize your target clients, you can create filters accordingly. I have also done so, and recorded the potential industries, sub-industries, type and size (one of the 3 – large, medium, small in our case) in a csv sheet called validations.csv. I have saved the sheet in the project folder and read from it as first step before creating a new merchant.
Since I wanted to assign a unique ID to each merchant, I have used 2 functions – DeDupeCheck() and assignMID(). Have created a Database MerchantDB.db for recording Merchant details as entered by the user. Have also created a bunch of other functions that will help us achieve the desired functionality of Add Merchant. These include:
* UpdateMerchantDB(): To add Merchant to the Database
* ReadMerchantDB(): To read Merchant details from the table. Will use it to create reports.
* AddContact_toDB(): Add contacts to Database
* ReadContactsDB(): Reading contacts
* getCustList(): For fetching customer list

def DeDupeCheck(M_name):
    return False
    M_list = getCustList()
    if M_name in M_list:
        return True
    else:
        return False

def assignMID(M_name):
    MID_list = getCustMIDs()        
    MID_list = [int(x) for x in MID_list]
    while True:
        MID = random.randint(10000,99999) #number between these 2
        if MID in MID_list:
            continue
        break
    return(MID)

def UpdateMerchantDB(df_merchant):
    conn = sqlite3.connect('MerchantDB.db')
    c = conn.cursor()
    df_merchant.to_sql('MERCHANTS', conn, if_exists = 'append', index = False)
    st.success("Successfully updated Merchants table")
    conn.commit()
    conn.close()

def ReadMerchantDB():
    conn = sqlite3.connect('MerchantDB.db')
    st.write("Header records")
    df = pd.read_sql_query("""SELECT * from MERCHANTS""", conn)
    st.write(df.head())
    if st.checkbox("Show data types"):
        st.write(df.dtypes)
    query = ''
    if st.checkbox("Show entire merchant database"):
        query = "SELECT * from MERCHANTS"
    if st.checkbox("Write your own query"):
        query = st.text_area("Write your query") 
    
    if query:
        try:
            df = pd.read_sql_query(query, conn)
            st.write(df)
        except Exception as e:
            st.warning("Problem with query. Please check")
    else:
        st.info("Please select an option from above") 
    conn.close()

def AddContact(m_id):
    name = st.text_input("name", key = '1')
    designation = st.text_input("designation", key = '2')
    email = st.text_input("email", key = '3')
    phone = st.text_input("phone", key = '4')

    M_contact = [[m_id, name, designation, email, phone]]
    cols = ['Merchant ID','Name', 'Designation' ,'Email', 'Phone']
    df_contact = pd.DataFrame(M_contact, index = [m_id], columns = cols)

    if st.button('Add Contact to DB'):
        AddContact_toDB(df_contact)

def AddContact_toDB(df):    
    conn = sqlite3.connect('MerchantDB.db')
    c = conn.cursor()    
    df.to_sql('CONTACTS', conn, if_exists = 'append', index = False)
    st.success("Contact updated successfully!")
    conn.commit()
    conn.close()
    
def ReadContactsDB():    
    conn = sqlite3.connect('MerchantDB.db')    
    df = pd.read_sql_query("""SELECT * from CONTACTS""", conn)
    st.write(df)   
    conn.close()

def getCustList():
    conn = sqlite3.connect('MerchantDB.db')
    df = pd.read_sql_query("""SELECT "Merchant Name" from MERCHANTS""", conn)
    conn.close()
    return(df['Merchant Name'].tolist()) 

Leave a Reply

Your email address will not be published. Required fields are marked *