Building and importing the SubDNA Client file into HubSpot
Step by Step process to import SubDNA client data into HubSpot.
Step 1 - SubDNA (Requires access)
Click on Manage Subscriptions (left Menu)
Scroll to bottom of the page. Click EXCEL to export.
Save file.
Click on Users (left Menu)
Click on All Subscribers
Scroll to bottom of the page. Click EXCEL to export.
Save file.
Click on Statistics (left Menu)
Click on Login Statistics (middle Menu)
Scroll to bottom of the page. Click EXCEL (X image) to export.
Save file.
Step 2 Update file: SubDNA_Hubspot_Master_Template_2022.xltm
File Location: Dropbox(FTR)\FTR Public\HUBSPOT INFO\Data Management\SubDNA\1_Final Files: Master Template 2 (updated in 2024)
Open Subscriptions Excel file
Then, select and copy all columns
In Subs tab, click on A1 and select Paste Value (123 image). If colored columns disappear, undo and re-paste.
In Subs2 tab, click on A1 and select Paste Value (123 image). If colored columns disappear, undo and re-paste.
In Subs tab, click on D2 (passwords). Click Shift/Control and down arrow. Click delete (to remove all passwords, but not the column header).
In Subs tab, click on Invoice column (A). Filter A to Z.
In Subs2 tab, click on D2 (passwords). Click Shift/Control and down arrow. Click delete (to remove all passwords, but not the column header).
In Subs2 tab, Select Custom Sort (Data ribbon, Fort & Filter, Sort). Level 1 sort: UserID (AV) as A to Z. Level 2 sort: Status (AB) as A to Z.
-- If ? Box comes up, select 'Sort anything that looks like a number, as a number' (option 1).
Close Subscriptions Excel file.
Open Subscribers Excel file
Then, select all columns and copy
In Users tab, click on A1 and select Paste Value (123 image). If colored columns dissapear, undo and re-paste.
In Users2 tab, click on A1 and select Paste Value (123 image). If colored columns dissapear, undo and re-paste.
In Users tab, click on G2 (passwords). Click Shift/Control and down arrow. Click delete (to remove all passwords, but not the column header).
In Users tab, click on Group ID column (B). Filter A to Z.
In Users2 tab, click on G2 (passwords). Click Shift/Control and down arrow. Click delete (to remove all passwords, but not the column header).
In Users2 tab, click on Date Reg column (D). Filter A to Z.
Close Subscriber Groups Excel file.
Open Login Statistics Excel file
Select all columns and copy
In Login tab, click on A1 and select Paste Value (123 image). If colored columns dissapear, undo and re-paste.
Update Pivot Tables
-- Data ribbon: Queries & Connections, Refresh dropdown, REFRESH ALL -- will Update ALL TABLES
In Login tab, sort ID column (H) A to Z.
In User_Output tab, review User output file to make sure fields are populated correctly.
Then click 'Select/Copy' button.
-- Paste into New Document as values and save file to:
Dropbox (FTR)\FTR Public\HUBSPOT INFO\Data Management\SubDNA\1_Final Files\Master Compiler Daily Versions (current year)
In Company_Output tab, review User output file to make sure fields are populated correctly.
Then click 'Select/Copy' button.
-- Paste into New Document as values and save file to:
C:\Users\Michael Starks\Dropbox (FTR)\FTR Public\HUBSPOT INFO\Data Management\SubDNA\1_Final Files\Master Compiler Daily Versions (current year)
User Compiler and Company Compiler - Do Not Touch
Save Master document into Master Compiler Daily Versions (current year) as Macro Enabled Worksheet.
Client Service Cards
Open Subs 2 tab - Remove all Group Member Service - Delete all rows.
Select all and copy
Open Client Service cards - Upload Template
Paste as value. Delete all colored header columns.
Verify that SubID shows correct IDs (01,02,03)
Save into Client Service Cards - Upload completed
Close master compiler, but Do Not Save.
Step 3 – HubSpot Import
Users
Import a file
Repeat a past import
Find the most recent Users file. Use as template.
Upload Users file
Create a contacts list from this import.
Click I agree checkbox
Verify number of new contacts on import page
Click on View contacts
Update marketing contact status of new contacts to Marketing Contact
Company
Import a file
Repeat a past import
Find the most recent Company file. Use as template.
Upload Company file
Click I agree checkbox
Client Service Cards
Import a file
Repeat a past import
Find the most recent Client Service file. Use as template.
Upload Client Service Card file
Click I agree checkbox
Go to Lists
Move import to Folder - SubDNA import list
Rename the file SubDNA Import month-day-year
Archive
Matching properties (not needed for repeat imports)
Match property Last Login = Subscriber Last Login
Fix for date: Year - Month - Day
Subscription 2 Renewal Date - IGNORE error
Don't import 1 column
Re-verify date format Year - Month - Day
Import - Do NOT select set as marketing contact