For those trying to fit together disparate systems the old joke: “If I were you, I wouldn’t start from here” would seem apposite. We may not have the luxury (or time, budget and perhaps enthusiasm) to create a new greenfield mega-app. Instead, we will need to cobble together what we have as best we can. More DIY SOS than Grand Designs. This is my chance to relate some of what I have been doing for a client, dubious design decisions and why I made them. In this case we have added tables to an external copy of the hubspot CRM database.
Generally, when extending cloud systems like a CRM, we should aim to keep all our data in one database, and that database is the source of truth. Extending a CRM we are leveraging the client and deal data, and if we can keep any new objects/tables related to that inside the CRM then we should not have referential integrity issues. We can also provide a single customer view to the user, via a single user interface, login etc. If we copy the data for reporting purposes, then we need to take extra care if we start to write back any updates. Linking from the CRM to new objects/tables held in an external database is sub-optimal, but does give us lots of flexibility. We can now build our own tools that further leverage that client and deal data, and potentially use our own system to link to other cloud systems through their APIs. We need to be clear as to where the source of truth is for each object/table, and possibly trigger updates from one set of data to the other to ensure the truth is promulgated. Deletions are awkward. We should regularly reconstruct any copied data to ensure it remains in sync. This will enforce the single source of truth maxim, brutally if you have not got it right.
The client is a small company, with no IT department or technical person. They run hubspot CRM, Sage accounting and some bits and pieces related to sharing content with their clients. They do not wish to pay for the full bells and whistles version of hubspot. They love Microsoft Excel, in both its desktop and online form. At any one time they have had an internal advocate helping to make the case and get things done.
Historically, they had some complex reporting requirements that required two passes of the data. We couldn’t do this with hubspot’s reporting, so I wrote some Excel VBA that extracted data to tables in Excel using hubspot’s API, and then ran some Microsoft SQL queries on that Excel data. This was a big hit. This approach enabled us to then run a whole range of parameterised queries on our Excel copy of the hubspot data. By providing hyperlinks back to individual objects in hubspot, the underlying detail was only a click away, and, particularly given that familiarity and love of Excel, they could use Excel as an index into the hubspot data. There was an Excel menu item that could call a partial (recent changes) or full refresh of the Excel data from hubspot. The full refresh might run for 2 or 3 minutes.
There were some concerns and limitations with this approach. Of particular concern was that each user had a copy of the client data and the hubspot API key on their laptop. Using Microsoft’s SQL in Excel also had some limitations with number of columns, and occasional errors when parsing column types from a column that was empty in the first few records. We also had the overhead that any change required a re-issue of an updated version of the Excel workbook with the macros and its distribution to all users (you can’t run macros in the online version of Excel).
When the customer wished to further extend their hubspot instance by utilising hubspot tasks as projects, we took the opportunity to create a hosted MySQL copy of the hubspot data, and operate on that instead. This meant we had one copy of the data in the cloud, rather than multiple copies in Excel, and we could then time a daily full refresh and have shared incremental refreshes during the day. Query response times are very good, faster than running the queries in Excel, although the queries are still initiated from, and the results returned to Excel workbooks. Adding new fields in hubspot has an overhead because we have to update MySQL and the update routines as well as the presentation in Excel, but then we had to do all of this in Excel previously.
What this new structure has enables us to do is to add new tables into the MySQL database to join with the copy of the hubspot data to extend the functionality. In particular, we have added project scheduling data. This was in Excel (sound familiar). It is still presented in Excel, but actually all the cell data is coming from the MySQL database, and is reconstructed on startup or request. There it is joined with the project data that is copied from hubspot. Cell changes in Excel are written to MySQL, and if there are material changes to the overall project these are also written to the project record in hubspot as well as updating the image of that record in MySQL. This happens fast enough that there is no noticeable delay in Excel. We also provide HTML versions of the schedule, or various subsets of it, for those who should not have rights to update it. Project summary data, including dates, personnel, status and profitability is now automatically available in hubspot, rather than being reported manually once the project was complete. We now have the data to report on personnel utilisation and capacity, so this can be compared with the project and deal pipelines.
The system here has evolved as customer requirements have grown over the years. The incremental low-risk approach is one the customer has been comfortable with, and also they have paid gradually. There has been some resistance to change, so we have had to make changes whilst keeping things looking the same. Excel is a powerful tool, and we have the components to use it as both front-end and reporting tool, but I feel sure we have created some views that could have been done on-line in hubspot, and front-end input that could have been done in HTML with some JavaScript. Nevertheless, it is what the customer wanted, it works, and they are happy.