topic Building Conversion Formulas in Data Hub

Building Conversion Formulas

CWat44 — Fri, 03 Nov 2023 20:15:16 GMT

I'm attempting to build out an MQL to SAL conversion rate, which in theory, is very easy, but I need to add a few caviats for them. I am attempting to pull only SALs from our "default" pipeline that have been created this year and have mql influence as a known value for one field in the dataset. This is what the AI keeps writing for me, but I keep getting an error about the syntax (which seems silly since the AI wrote it).

DISTINCT_COUNT(IF(((([DEAL.pipeline] = "default") AND (DATEPART("YEAR", [DEAL.createdate]) = DATEPART("YEAR", NOW()))) AND [DEAL.mql_influence] [IS NOT NULL]), [DEAL.hs_object_id], NULL))

Anybody have any thoughts on how to better write this?

Re: Building Conversion Formulas

kvlschaefer — Mon, 06 Nov 2023 09:16:13 GMT

Hi @CWat44,

Thanks for reaching out to the Community!

This is very interesting about the AI! Are you using ChatSpot or another AI tool?

Did you try modifying the syntax after the AI wrote it? Do you have a screenshot of the error you are seeing, please? If you could share a bit more on any steps you have taken so far on your end and examples, this can help the community offer better advice 🙂

Thank you!

Best,

Kristen

Re: Building Conversion Formulas

CWat44 — Mon, 06 Nov 2023 17:07:13 GMT

I've tried updating the properties in the formula to make sure they are correct and I get the same error issues. I'm not quite sure what about the syntax would be off with this section "((([DEAL.pipeline] = "default") AND (DATEPART("YEAR", [DEAL.createdate]) = DATEPART("YEAR", NOW()))) AND [DEAL.mql_influence] [IS NOT NULL])"

I tried to screenshot, but it's too long of a string to get in one pic (:

I've tried a myriad of ways to attempt to get the AI to write it differently, but I get errors there as well.

DISTINCT_COUNT(IF(( AND (YEAR([DEAL.createdate]) = YEAR(NOW()))), [DEAL.mql_influence] [IS NOT NULL], ([DEAL.pipeline] = "default")), [DEAL.createdate], NULL) )

Re: Building Conversion Formulas

kvlschaefer — Tue, 14 Nov 2023 13:40:58 GMT

Hi @CWat44,

Thank you for the additional information and the examples!

I wanted to invite our subject matter experts to see if they have insight.

Hi @louischausse, @johnelmer, @ChristinaKay - Have you created a similar dataset in HubSpot? Do you have any advice for @CWat44? Thank you!

Best,

Kristen