In the previous post, I created overall structure and the module for add Merchant. In this post, I will cover Module that will help us create or update an opportunity. Let’s say we have a merchant “SampleMerchant” we have added using “Add Merchant” module, and now we want to create an opportunity under the merchant.

On selection of “Add/Update Opportunity”, we invoke a function SalesUpdate() that shall handle the rest for us.

def SalesUpdate():
    cust_list = getCustList()
    conn = sqlite3.connect('MerchantDB.db')
    df = pd.read_sql_query("""SELECT "Merchant Name" from MERCHANTS""", conn)
    conn.close()
    cust_list = df['Merchant Name'].tolist()    
    mname = st.selectbox("Select Customer", cust_list)
    M_id = FindMerchantIDfromName(mname)

    M_currentstatus = CheckinPipeline(M_id)
    if M_currentstatus[0] == -2:
        st.info("This is 1st entry into Pipeline. Creating Table")
        RecordUserUpdate(M_id)   
    elif M_currentstatus[0] == -1:
        st.info("Selected Merchant not in current pipe. Now adding")    
        RecordUserUpdate(M_id)
    else:
        st.info("Selected Merchant in Pipeline. Fetching details")
        RecordUserUpdate(M_id, M_currentstatus[1:])   

SalesUpdate() checks whether the merchant already has opportunity created against it or not, to begin with using CheckinPipeline(). In case it doesn’t, it prompts the user to add opportunity. In case it does, it loads the opportunity status and allows the user to update. If the merchant is not there in the database at all (FYI: this wouldn’t happen in my implementation since I have restricted the merchant input to the existing list), it returns appropriate code.

def CheckinPipeline(M_id):
    conn = sqlite3.connect('MerchantDB.db')
    c = conn.cursor()
    c.execute(''' SELECT count(name) FROM sqlite_master WHERE type='table' AND name='PIPELINE' ''')
    if c.fetchone()[0] == 1:                                            #this means that table exists
        cursor = conn.execute("""SELECT * FROM PIPELINE where "Merchant ID"=?;""", (M_id,))
        m_record = cursor.fetchall()
        if len(m_record) == 0:
            return([-1])                                             #table exists but not merchant record
        else:
            return([1] + m_record)                
    else:                                                               #table doesn't exist
        return([-2])

Figure 2.1 shows scenario wherein for the selected merchant, we are creating any opportunity for the first time. In case the sales team discovers new opportunities with the same merchant, they can update the same in the Sales Pitch(es) section. It is therefore, a multiselect frame. Additionally, owner, sales stage, comments, engagement start date, expected closure date can be added as well. Sales stage and Comments carry timestamps to allow assessment of pipeline movement, and to also provide handy notes to the sales team. While comments auto-picks date (of today), sales stage date is configurable with today as default. Commercials can also be added during the sales stages or at closure. You can customize this field in Streamlit to suit your usage. For example, in our case, revenue is a % of transaction so I have configured it as a % field, and defined a floor which ensures no one is making a loss making sale :). In case the opportunity is being created afresh or is being updated, we invoke function RecordUserUpdate() to handle the updation.

Please note: I have not used the field “Addl Info” in my production script, and have instead used few other ones as that’s what our use case demanded. I have hidden those fields since those are specific and confidential to our business. However, I hope the idea is clear.

def RecordUserUpdate(M_id, *args):
    df = pd.read_csv('Validations.csv')
    M_owners = df['Owners'].dropna()
    M_pitches = df['Pitch'].dropna()
    M_stages = df['Stage'].dropna()
    cols = ['Merchant ID', 'Owner','Pitch', 'Stage', 'Stage_timelines','Comments', 'Engagement_Start', 'Expected Closure', 'Commercials','Addl Info']
    
    M_owners = M_owners.tolist()
    M_pitches = M_pitches.tolist()
    M_stages = M_stages.tolist()    

    M_newstatus = [] * len(cols)
    if len(args) > 0:
        M_currentstatus = args[0][0]  
        st.markdown("_**Current Status**_")
        st.table(pd.DataFrame([M_currentstatus], index = [M_id], columns = cols).T)
        owner_current, pitch_current, stage_current, stage_timelines, comments  = M_owners.index(M_currentstatus[1]), M_currentstatus[2], M_currentstatus[3], M_currentstatus[4],M_currentstatus[5]         
        pitch_current = re.findall(r'[A-Z][A-Za-z\s\-\"]{5,}',pitch_current)        
        stage_current_index =  M_stages.index(re.findall(r'[1-9][A-Za-z\s/\-]{3,}',stage_current)[0])        
        engagement_start_date = M_currentstatus[6]
        expected_closure_date = M_currentstatus[7]
        commercials = M_currentstatus[8]      
    else:
        owner_current, pitch_current, stage_current_index, comments = 0, M_pitches[0], 0, ""
        engagement_start_date = date.today()
        expected_closure_date = None
        commercials = None
        stage_current = M_stages[stage_current_index]
        stage_timelines = str(date.today()) + ": " + M_stages[stage_current_index] + ";" + "\n"    
    
    st.write("-----------------------------------------------------------------------------------------------")
    st.markdown("_**Update Status**_")
    owner = st.selectbox("Select owner", M_owners, owner_current)
    pitch = st.multiselect("Sales Pitch(es)", M_pitches, default = pitch_current)
    stage_new = st.selectbox("Sales Stage", M_stages, stage_current_index)      
    
    if M_stages.index(stage_new) != stage_current_index:
        stage_date = st.date_input("Date of change to stage: " + stage_new)
        stage_timelines = str(stage_date) + ": " + stage_new  + ";" + "\n" + str(stage_timelines or '')
    
    comment = st.text_area("Enter merchant update")
    if comment:
        comments = str(date.today()) + ": " + comment + ";" + "\n" + str(comments or '')
              
    expected_closure_date = st.date_input("Expected Closure")
    commercials = st.number_input("Commercials")

    M_newstatus[0:8] = [M_id, owner, pitch, stage_new, stage_timelines, comments, engagement_start_date, expected_closure_date, commercials ]
    if len(args) == 0:
        M_newstatus[9:] = [None] * (len(cols) - len(M_newstatus))
    else:
        M_newstatus[9:] = M_currentstatus[9:]
    df = pd.DataFrame([M_newstatus], index = [M_id], columns = cols)
    
    st.write("-----------------------------------------------------------------------------------------------")
    st.markdown("_**Update Summary**_")
    st.table(df.T)
    st.write("-----------------------------------------------------------------------------------------------")
    if st.button("Update to DB"):
        for col in df.columns:
            df[col] = df[col].astype('str')
        if len(args) == 0:
            PipelineUpdatetoDB(df, 'Insert')
        else:
            PipelineUpdatetoDB(df, 'Update')

In case you are not familiar with Python, *args is used to send a variable count of parameters to a function. It creates a list, and I have used the length check to determine if it is a new opportunity or an update (if length = 0 => new opportunity else update). Since Stage is a selectbox, it takes just one input at a time. Since I want to show existing stage in case of opportunity stage, I have used regex to pull out index of current stage (it follows a LIFO, so 1st element, as referenced by [0] gives us current stage. Have used regex for finding the various product pitches that are active, and used the list output to pre-load the Pitch field. As mentioned earlier, Comments and Stage_timelines carry timestamps of successive updates.

Once done, I have created a quick preview just in case user wants to make changes before pushing the update to Database.

On the click of “Update to DB” button, I call PipelineUpdatetoDB() function which handles the update into database.

def PipelineUpdatetoDB(df, action):
    conn = sqlite3.connect('MerchantDB.db')
    cursor = conn.cursor()
    if action == 'Insert':
        df.to_sql('PIPELINE', conn, if_exists = 'append', index = False)
        st.info("Successfully added record")
    elif action == 'Update':
        cols = ['Merchant ID', 'Owner', 'Pitch', 'Stage', 'Stage_timelines','Comments', 'Engagement_Start', 'Expected Closure', 'Commercials','Addl Info']
        query = """UPDATE PIPELINE SET "Merchant ID" = ?, Owner = ?,Pitch = ?, Stage = ?, Stage_timelines = ?,Comments = ?, Engagement_Start = ?, "Expected Closure" = ?, Commercials = ?, Addl_Info = ? WHERE "Merchant ID" = ?"""
        columnValues = (*df.iloc[0].tolist(), df.iloc[0,0])
        cursor.execute(query, columnValues)
        st.info("Record Updated")
    conn.commit()
    conn.close()

Since we are on the topic, here’s the function which reads from database table with Pipeline information. It shall be used at the time of creating scorecard and few other places.

def ReadPipelineDB():    
    conn = sqlite3.connect('MerchantDB.db')    
    df = pd.read_sql_query("""SELECT * from PIPELINE""", conn)
    st.write(df)
    conn.close()

Cool, we can now add merchants and add/update opportunities. With this in place, focus now shifts to reports.

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.

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())