topic Re: Counting/Labelling Repeat Transactions in Reporting & Analytics

Counting/Labelling Repeat Transactions

ataf — Sat, 27 Feb 2021 06:54:23 GMT

Hi there,

I am struggling to find a solution for counting repeat transactions of a customer. We are not a subscription based business and one transaction is counted when a deal has moved in the closed-won stage. We have two transactional pipelines for 2 markets following the same deal stage logic.

We want to label repeat transactions through a custom property. I will also want to label the existing 2k+ transactions with as 'repeat' or 'first'.

Finally, under contacts I would like to build a property that can tell me which of the two markets did the customer first made a transaction.

Any help and ideas on how to accomplish all this is greatly appreciated!

cheers,
Ataf

Re: Counting/Labelling Repeat Transactions

karstenkoehler — Sat, 27 Feb 2021 08:18:45 GMT

Hi @ataf,

Going forward, you could set up two workflows to log the information you're interested in.

In a deal-based workflow, enroll deals when the contact property Associated deals is known. If this number is equal to 1, then set your custom deal property Transaction type to "First". If this number is greater than one, then set your custom property Transaction type to "Repeat.

In a contact-based workflow, enroll contacts when the the contact property Associated deals is equal to 1. If the deal property Pipeline is [pipeline 1], then set your custom contact property Pipeline of first transaction to [pipeline 1]. If the deal property Pipeline is [pipeline 2], then set your custom contact property Pipeline of first transaction to [pipeline 2].

For all existing deals, I can't think of any way to backfill the information. HubSpot workflows currently don't allow for comparing two objects, so you wouldn't be able tell the first transactions apart from the repeat transactions, unfortunately. For existing deals, Excel is probably going to be your best friend: export existing deals, manually going through them to assign first and repeat, re-import.

After import, you could run a contact-based workflow, enrolling all contacts with deal property Transaction type known. If deal property Transaction type is "First" and Pipeline is [pipeline 1], set contact property Pipeline of first transaction to [pipeline 1]. If deal property Transaction type is "First" and Pipeline is [pipeline 2], set contact property Pipeline of first transaction to [pipeline 2]. (This workflow would actually do the job of the contact-based workflow from my second paragraph as well.)

I haven't tested any of the above, so make sure to test it on a few records before unleashing it onto your whole database.

Hope this helps!

Re: Counting/Labelling Repeat Transactions

ataf — Sat, 27 Feb 2021 14:05:29 GMT

@karstenkoehler thanks a lot for such a detailed solution. The first two would have definitely worked if the Associated Deals of contact were actually equal to the no. of transactions. My bad for not giving enough details, but we have a different prospecting pipeline and even in the transactional pipeline the deal has a chance of moving to the lost stage. An additional filter for only closed-won deals would help do this job. I actually do these operations almost on a quarterly basis in sheets, but having it in HubSpot would help save a lot of time.

Although this gave me an approach that I'm gonna try. I'm thinking of going for a deal-based workflow that triggers when the deal is moved to the closed-won stage. The next step would be to check if the contact's lifecycle is customer and tag deals as first or repeat. Will have to be mindful of choosing the close date as after 'm/d/yr' threshold date. For deals before the threshold date, good old 'export-sheets-import' would do.

The third workflow looks very promising and I think it should do the job. Gonna try it all out very soon and back here.

Thanks again for the help!