How to report on all fields (Contact and Company), with Field Description and Record Count?
Good morning, Community!
We are currently doing some data normalization in preparation for starting direct meetings with our internal teams to help them fully leverage all that HubSpot can do. Having an ad hoc report that inclues field names, field descriptions, and a count of how many times that field has been used will help us on both fronts. Is there a simple way to export this data natively from HS?
How to report on all fields (Contact and Company), with Field Description and Record Count?
There is no simple export with that data. I've taken two different approaches depending on how much collaboration is needed to determine the effacacy of currently used (or created) properties. Both require some amount of manual labor and it isn't really all that bad. In both approaches I always separate out custom properties from HubSpot default.
1. Add to columns in your properties Sheet; one for a link to the custom property, and another to show how many records contain data for that property. This will help to quickly narrow down properties that may either be populated by API (possibly showing no Usages) or properties that just don't have much data associated with them (what is the purpose then?).
2. The other approach is good for when you need a higher amount of colaboration on each individual property, especially if the property is used in multiple places; using Google Sheets, create a Google Aps Script to create a document (from a template) for each key value you are reporting on from the standard HubSpot output and then insert a link to that newly created document into the sheet itself. Once you have this doc created, open the doc add a link to the HubSpot property (manually) after searching for it in HubSpot, and then copy and paste in all related places the property is used. The benefit of this approach is that you now have one link to click in your properties Sheet that takes you directly to a document with all relevant information. By inviting collaborators to these docs, additional information and context is easily added in to create an idea of why and how the property is working for the organization (also a good foundation for creating Knowledge Base articles).
Here's an example of the code I used in a project recently:
function createNewGoogleDocs() {
//This value should be the id of your document template that we created in the last step
const googleDocTemplate = DriveApp.getFileById('13edniG...');
//This value should be the id of the folder where you want your completed documents stored
const destinationFolder = DriveApp.getFolderById('14WGXgd...')
//Here we store the sheet as a variable
const sheet = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName('deal')
//Now we get all of the values as a 2D array
const rows = sheet.getDataRange().getValues();
//Start processing each spreadsheet row
rows.forEach(function(row, index){
//Here we check if this row is the headers, if so we skip it
if (index === 0) return;
//Here we check if a document has already been generated by looking at 'Document Link', if so we skip it
if (row[1]) return;
//Using the row data in a template literal, we make a copy of our template document in our destinationFolder
const copy = googleDocTemplate.makeCopy(`${row[0]}` , destinationFolder)
//Once we have the copy, we then open it using the DocumentApp
const doc = DocumentApp.openById(copy.getId())
//All of the content lives in the body, so we get that for editing
const body = doc.getBody();
//In this line we do some friendly date formatting, that may or may not work for you locale
//const friendlyDate = new Date(row[3]).toLocaleDateString();
//In these lines, we replace our replacement tokens with values from our spreadsheet row
body.replaceText('{{Type}}', row[3]);
body.replaceText('{{Description}}', row[4]);
body.replaceText('{{Usages}}', row[6]);
body.replaceText('{{Created user}}', row[16]);
body.replaceText('{{Name}}', row[0]);
body.replaceText('{{Internal name}}', row[2]);
//We make our changes permanent by saving and closing the document
doc.saveAndClose();
//Store the url of our new document in a variable
const url = doc.getUrl();
//Write that value back to the 'Document Link' column in the spreadsheet.
sheet.getRange(index + 1, 2).setValue(url)
})
}
How to report on all fields (Contact and Company), with Field Description and Record Count?
@jspencerdav and @MAnderson42 I think you may have done similar properties work today, would you be able to answer MBooker's most recent question above?
This sheet export will not give you the number of records that have values for each property but you will be able to see where these properties are being used in (for example, workflows and reports).
To see the number of records with values, I would build a report to view the count of [object] with [property] known.
How to report on all fields (Contact and Company), with Field Description and Record Count?
Hi, James - thanks for the information.
The Properties export is very helpful - but it does not include the field Decription (the one found on the "Basic Info" tab in HS). That description is extremely useful when we are doing high-level planning.
Does your Dev Team have an internal table or other documentation that can be shared?
