We’re currently using Stitch to bring in companies data into BigQuery. However, the total records in the resulting companies table is 110K while HubSpot lists 96K. I was able to omit merged records using properties.hs_merged_object_ids but I’m still showing 98K.
The remaining 2K records seem to be companies that were archived or deleted, but I can’t find a dimension to identify these archived / deleted records so I can filter them out. In searching other posts, tt sounds like there should be a isDeleted or isArchived field but I can’t seem to find that column in the companies table. I am pulling all fields in Stitch so are those fields just not available via Stitch?
Hi! I’m facing the same issue, I don’t know how to identify contacts deleted in Hubspot in the tables extracted by Stitch. Any chance there was a solution to the above issue?
Unfortunately, I wasn’t able to find a solution and the links provided by BerangereL weren’t very helpful.
What I ended up doing for my Company issue was this … I added a new date field to Companies and Bulk Edited in the UI to be that current date. The records were updated in BQ with this property and I exported the company IDs without a date into a separate table (exclude records created after the bulk edit date to prevent new records from being filtered out). Then, I joined this table into my first transformation query so that it filters out these companyIDs. I am now telling my team to not delete Companies but to merge them instead (as that’s the main reason why Company deletions were originally occurring). This way, the merged company ID fields will now capture any future Company duplicate cleanup.
Not sure if this applies to your Contacts issue since you might actually need to delete Contacts instead of merging them. You could add a new date field but add a workflow to update this field every day for all records and add a condition in your queries to exclude records that don’t have the most recent date?
