Procore + QuickBooks + HubSpot

[00:00] Okay, today we are going to be reviewing an integration. This one was focused mainly on Procore, QuickBooks and HubSpot. The main initiative is to have one governed source of truth and to replace all of their WIP schedules and their controller dashboards and reports that they have to create, and have those pull from the source of truth and be able to generate and validate appropriately. So I pulled in all three of these: Procore, HubSpot and QuickBooks Online. They're all using just a sandbox test environment right now, so I went through and got credentials for that. In HubSpot, I went through and created a service key, and within that service key I essentially allowed for the scopes to be

[00:45] able to read these specific things: companies, deals, line items and contacts. From there we went into Intuit, specifically for QuickBooks Online, and we went through and created an app. Then there are the keys and credentials, where we provision the client ID and client secret, which sends us through the OAuth code flow. We provision that, and then we have access for the next 100 days, after which we would have to sign in again and reauthenticate. We then have Procore, and we create a custom app in there as well and get our client ID and client secret. Right now we're just using the sandbox environment here. And so that all then links into our Fabric account. But essentially what we're going through and doing is

[01:30] we're pulling all of this. So it's automatically linking all of these sources. We're doing all of our checksums and validations. And then essentially we have run over 53 tests and done 203 offline assertions, created different reports, Power BI reports, 11 of those, and then 68 governed measures. Ultimately these were just some of the work in progress numbers from the sandbox environments, but in a sense it's showing that all three of these are linked and proven. So we have a manual override where the controller can implement a CSV file that automatically links the Procore project and the QuickBooks job together. Of course, we

[02:15] semantically match this based upon the project name, and we can also reference the ID if they're the same as well. But as of right now, we have a CSV that manually overrides the matching, so we can then provision that. We bring this data in from the source. We load it as a raw payload in a JSON blob, and then essentially we trim it, validate it, run any types of checksums through it, and then we map that into our gold schema, which is everything mapped out, validated and associated to all the respective fields. Then we run it through a quality gate to ensure that it's not missing any data and that it's all appropriate.

[03:00] We map all of this and then create some reports, and the reports I'll dive into here. There are 11 pages and 110 visuals: a bunch of different things, from work in progress, portfolio, backlog, exceptions, AR collections, cash forecast, capacity, and data quality of course. And we are utilizing no shared keys. The goal is to implement Fabric. Right now we're using our local instance to pull data from our local environment variables. The goal is to then move that over to Fabric, so we're storing our credentials in Key Vault and we'll have that hosted there. And then once we start pulling in data... this is just sample data, so we're

[03:45] probably going to have more things that we come up with once we pull in live data here, but these are just some of the things we learned and some of the discussion points that we need to discuss to validate this. From there we're going to be setting up Key Vault and then pointing this at production data. We're going to then align this with the actuals that we have in QuickBooks, and then align our HubSpot deals. There's also the opportunity to create a Power Automate workflow or integration that automatically will create a project in Procore once a HubSpot deal is moved to closed won. We can also have it create a job in QuickBooks Online as well. So we can align those and have it automatically sync them, and so we know how to

[04:30] associate it without using that controller CSV. So essentially this is all stored in GitHub. Here's the URL if you want to check it out. Where this maps to is: we built one lakehouse that stores everything, and in the notebooks there's a bootstrap, and then we go through and extract Procore, extract QuickBooks Online, extract HubSpot. Then we move it through our different querying layers, our SQL tables, to clean the data before it goes into our checks. We have our pipeline set up to move it through the different stages, and then we have our semantic model, which integrates all of the different tables together and associates them, and then finally we have our report,

[05:15] which is our Power BI dashboard that includes all the pages. It's organized quite well. It does bring all the data through the pipeline, which we've shared before. We have a naming convention for how we're referencing the data in specific fields, and then there's the crosswalk, which is the reference between QuickBooks and Procore. We can rectify this by doing a Power Automate integration. We do the gate to ensure that we're validating all of the data prior to writing it to the Power BI dashboard. We don't want incorrect data or wrong calculations to influence any decision-making or analytics and insights on the dashboard. So we have this gate where it will throw an error

[06:00] and it will only reference the most recent correct version, and so if there are any errors there will be a notification or a log within the report that will show that. We do run all of this locally, and there are different statuses that we can implement here and how we're connecting. From here, what we can dive into is deeper documentation and resources if you want to learn further. Since this is getting a bit longer, I'll just dive directly into the specific tab. And so the fun stuff here is this report. Initially, this is just bringing over the portfolio from Procore, and these are the specific projects in my sandbox environment. And

[06:45] then going into the executive KPI, this is our earned revenue and completion percentage and any backlog and over/under billings that we have. This pulls in the appropriate projects as well as any additional billings that we have in QuickBooks. I haven't been billing or doing anything in our sandbox, so there are no numbers. But when we are bringing in our work in progress, we'll be able to analyze this for all of the different projects that we have going on. We'll be able to analyze our performance across the project and all of our different cost codes broken down. And then ultimately, we have our backlog and our burn: how much revenue versus contract value we have and what's currently being booked. We then bring in our exceptions. So

[07:30] these are any at-risk or over cost codes, or unmapped projects that we need to associate to a QuickBooks project. And then we have our pipeline and forecast. This is what's currently in our pipeline from HubSpot: open deals, percentage confidence, closed, weighted pipeline, backlog, who owns it and the stage. Then our accounts receivable and collections. This is coming in from QuickBooks, so we're pulling in any outstanding or overdue accounts receivable, and then we have all of this mapped out in aging buckets and by vendor and resource. Here we then have our cash forecast, and this

[08:15] ultimately is what's in collection, what we need to receive and what's moving around. This brings in all of our data from QuickBooks as well. And then finally we have our capacity. This is our total labor hours, what we've billed and utilized for each project, and we can associate all of this at a per-project level and over a monthly or yearly duration. Of course, we can dive further into some of the specific formatting. We stored all of this within one folder, and we did our semantic model, which brings all of our data through each of the stages to clean the data and then maps it to the correct fields of the tables. As we have all these tables, we're associating and standardizing all of these fields and

[09:00] aligning them. And dim date is always our big one to align dates together. Then when we go back in here, we have our notebooks. I can just show the Procore extract here, for instance. The bootstrap is essentially setting up everything for the credentials and authentication. Then we bring in our integration to Procore, and what we're doing is loading a registry of all of the endpoints that we're referencing. We're pulling from an entire YAML file of API endpoints, so we can always update that with any versioning as Procore updates theirs, and we're referencing it in one notebook so we can always version control that.

[09:45] So then we're pulling all of these projects, linking all of them together, and then we are putting our rate limiter there, so we're ensuring that we're compiling all of that within their limits. Then we're writing that into the bronze lakehouse, dropping all of that data appropriately and storing it here. From there, we're doing the same thing for the rest of the integrations, QuickBooks and HubSpot. And then what we do is land this in bronze as a raw JSON object, convert it to silver, build our gold,

[10:30] then do our data checks. Our pipeline is going to be that ingestion of how we're feeding the different notebooks or SQL tables. So this is our extraction to ingest, and then we have our master pipeline. This pulls in the whole sequence from bronze to silver to gold and then our quality gate. With that being said, that really maps out everything. I can dive further into the lakehouse and the tables that we have and how they're mapped.

[11:15] But from that standpoint, this is really everything that's been built. So, quite robust. It works on the sandbox environments. Of course, it'd be good to get this into our working production environment so that we can do some validation on the Procore integration, making sure that we align the projects, and then reconcile QuickBooks to ensure that all of the data and calculations that we're pulling into the Power BI dashboard are appropriate and mapped accordingly. So yeah, of course, if there are any questions or anything else, happy to support. If you'd like to get this further

[12:00] integrated, let me know. But yeah, wish you all the best and thank you very much. Bye.