Daily number of prescriptions displayed in a custom report

[MarinoEnglish]

Hi,

I'm facing a problem to display a specific data in my Dashboard.
I'm working with doctors and I'm using Hubspot to follow my clients activity on my website. I've linked Hubspot with my website using API so, when a user creates an account on the website, his profile is directly created in Hubspot. And when he prescribes from my website, the "prescription" property in his profile is automatically updated and gets +1.

Now I'd like to create a daily prescription report that will show how many new prescriptions were made by my clients (total number of new prescriptions) every day.

I created a new custom reports and decided to display Create date measured by the Total number of prescriptions.

Unfortunately, it seems that the create date is the Contact's create date, and not the Prescription's create date. So the graph show the total number of prescriptions according to their doctor's account creation day.

Is there a way to do what I want to do and display the daily number of prescriptions ? Do I need to replace "Create date" by something else ?

Thank you for your help.

[MFrankJohnson · Accepted solution]

>>"Is there a way to ... display the daily number of prescriptions ?"

Short A: Not with your current data -- which isn't separately logging new prescription DATES.

Longer A:
Looks like you're trying to use HubSpot as a database to store date-centric entities and associate each entity with a specific contact (or company), but without storing the DATES separately.

Based on the limited information available, you could...

- upgrade your integration to datestamp each new prescription. -- this is not scalable in your current configuration. Meaning, datestamping each new prescription in your current configuration would require adding a separate Contact custom prescription date property for each new prescription. Again, not scalable.

- store EACH new prescription as a trackable HubSpot entity -- e.g., a Deal.

If it were possible, we'd probably just create a new Prescriptions table with a one-to-many relationship between Contacts and Prescriptions. However, HubSpot doesn't permit direct modification of entity relationships within HubSpot -- i.e., how tables relate to each other. As such, your solution is to repurpose an existing entity -- i.e., the only current candidate being HubSpot Deals!

- see HubSpot Entity Relationship Diagram (ERD)

- build a smart list of all contacts whose Prescription count has been updated within the last 1 day. The total number of contacts in that list would closely reflect the total number of prescriptions for the day -- i.e., presuming one prescription per contact. Reporting this list COUNT would not be available within HubSpot.

[MarinoEnglish]

Hi Frank,

Thank you very much for your answer.

It's really clear and I'll forward it to the developers I'm working with so they have all the informations.

Regards,

Romain

[MFrankJohnson]

Your welcome Romain. Have them reach out if they have questions. Happy to help.