Data Clean Up and Best Practices
      Back to home
      1. Knowledge Base
      2. HubSpot
      3. Data Clean Up and Best Practices

      Resolving HubSpot Challenges: Addressing Multiple Contacts Associations with Companies

      In the HubSpot journey, we've noticed a recurring challenge where accounts end up associated with more than one company. It's a headache for accounting and reports. Sadly, HubSpot doesn't offer a quick fix, so we're diving into a hands-on solution.

       

      To resolve this issue, it would be beneficial to have a basic understanding of Python and Excel.

       

      1. Create a Report in HubSpot:

        • Set up a report with the following columns
          • Rec ID Contact
          • Company ID
          • Company name
          • Contact - Email domain
          • Company - Company domain name.

      2. Spruce Up in Excel:

        • Download the report and give it a makeover by replacing the "www." and "/" from [Company - Company domain name].
      3. Find the wrogn associations
        • One way to find those contacts with the wrong company is by comparing the  [Contact  - Email domain]  with  [Company - Company domain name] if they are different most likely they are wrong.

          Use a IF formula to compare both columns.

          If the contact has more than one company associated and both are different, they need to be checked manually, in this step you will find duplicate companies that were not found with deduple.ly or HubSpot Data CleanUP Tool, Make sure to clean those up manually in HubSpot.

      4. Open a new Excel File and Take the Contact ID on Columna A and Company ID on Column B

      5. Python code 

        The following code can be ran on google colab, it will request the excel file created on step 4 where column A is the contact Rec ID and Column B the company Rec ID, Once the code finishes deleting all associations, it will generate an excel file with all calls and responses.

        Request the access_token to your HubSpot Admin and replace it on the code on line 6.

        This code will delete all associations saved on the excel file created on step 4.

         

        import requests

        import json

        import openpyxl

        import time

        from google.colab import files

         

        # Input your HubSpot access token here

        access_token = "access_token"

         

        # Upload your Excel file

        uploaded = files.upload()

         

        # Load data from the uploaded Excel file

        excel_file = list(uploaded.keys())[0]

        workbook = openpyxl.load_workbook(excel_file)

        sheet = workbook.active

         

        # URL for the HubSpot API

        url = "https://api.hubapi.com/crm/v4/associations/contact/company/batch/archive"

         

        # Initialize the headers with the access token

        headers = {

            "Authorization": f"Bearer {access_token}",

            "Content-Type": "application/json"

        }

         

        # Initialize the list to store API responses

        api_responses = []

         

        # Iterate through the Excel file data

        for row in sheet.iter_rows(min_row=2, values_only=True):

            from_id = row[0]

            to_id = row[1]

         

            # Create the API request data

            api_request_data = {

                "from": {"id": from_id},

                "to": [{"id": to_id}]

            }

         

            # Make the API request

            payload = {

                "inputs": [api_request_data]

            }

            response = requests.post(url, headers=headers, data=json.dumps(payload))

         

            # Append the API response to the list

            api_responses.append({

                "from_id": from_id,

                "to_id": to_id,

                "response_status": response.status_code,

                "response_text": response.text

            })

         

            # Print the API response for the current request

            print(f"API Response for from_id: {from_id}, to_id: {to_id}")

            print(f"Response Status: {response.status_code}")

            print(f"Response Text: {response.text}")

            print("--------------")

         

            # Introduce a 1-second delay between requests

            time.sleep(1)

         

        # Save the API responses to an Excel file

        response_workbook = openpyxl.Workbook()

        response_sheet = response_workbook.active

        response_sheet.append(["From ID", "To ID", "Response Status", "Response Text"])

        for response in api_responses:

            response_sheet.append([response["from_id"], response["to_id"], response["response_status"], response["response_text"]])

         

        response_file_name = "api_responses.xlsx"

        response_workbook.save(response_file_name)

         

        # Download the Excel file with the API responses

        files.download(response_file_name)

         

      6. Alright, we've sprinkled some Python magic to clean things up, but now it's time for a little human touch. Follow these easy steps to deactivate and reactivate the "Create and associate companies with contacts" feature in HubSpot, this option will automatically match the domain in a contact's email (like "sandra@example.com") with the company's domain name ("example.com"). This way, even if there was a little mishap in the Python code, and a contact got accidentally unlinked from a company, we've got a backup plan to rekindle that connection.
        • Jump into your HubSpot account and hit up the settings icon in the main navigation bar.
        • Cruise over to Objects > Companies in the left sidebar menu.
        • Look for the "Create and associate companies with contacts" checkbox in the Automation section.
        • Give it a breather—turn it off for a sec and let it catch its virtual breath.
        • Now, reignite the dance floor by turning the checkbox back on.