Using Excel as a Simple CRM
I tend to keep track of data in Excel, and lately I've been using it as a CRM. I keep different sheets in order to answer different kinds of questions: Who asked about cell formatting? Who did I first talk to on our online chat? How many users do we have in Chicago?
In theory, each sheet is synced with a master sheet containing all customer data. In practice, maintaining everything by hand was too time-consuming, and my data became scattered.
When I realized this system wasn't scalable, I began looking for a replacement. My two main requirements were flexibility and ease-of-use. If I suddenly need to know which customers are on the West Coast, I want to be able to do it without inputting the data repeatedly or cross-checking a single sheet.
After trying some other products, I was inspired by my friend Peter's blog post to build my own solution in Datanitro. This let me extend my original ad-hoc system within Excel by keeping all my data synced in a spreadsheet, while letting me interact with any view I want.
The design is straightforward - the user interacts with one sheet, which displays and organizes data on the fly. Another sheet stores all data not currently displayed. For example, here's a spreadsheet Darth Vader might have on the Rebel Alliance:
(Download the sample to follow along with the example. You'll need to have DataNitro installed.)
This is the 'View' sheet, which displays the active part of the data. The full set of data is on the 'Data' sheet.
To add a new record, just type it in the 'View' sheet:
Here's how to add a note on Yoda. Open the Python shell and run
from db import * show_col('notes')This will add the 'notes' column from the database to the view. If there were no notes column, one would be created. We can then type in the note.
Let's say we want to pick the Death Star's first target. We can sort the records by homeworld by running
Alderaan looks like a good candidate - let's investigate it further. The
filter_records matches records to a
regular expression. It can match either against records in the current view, or
against all records in the database. (The last parameter is filter_from_view, which is True
filter_records('Alderaan', 'homeworld', False)
Whether you're suppressing the Rebel Alliance or planning a barbecue, try out db.py to keep track of your data.
Is Excel too slow? Speed it up!