header
Solved

Accounts without open CTAs

  • 15 June 2020
  • 4 replies
  • 97 views

Hi everyone,
We are working on improving our reports from Gainsight. Because of this, we have realised that it would be great to be able to see which accounts do not have any open CTAs.

Do any of you know of a good way to see this?

Thank you!

icon

Best answer by john_apple 15 June 2020, 16:51

Hi - you can do this by setting up a field on the Company object to hold the number of Open CTAs and then running a rule to populate this field daily.

Setup:

  1. Create a field called Open CTAs on the Company Object, set default to 0
  2. Create a rule in Rules Engine that queries the Call to Action records for Open CTAs and aggregates the data by Company - so you should get a dataset that has Company Name, Open CTA Count. This dataset won’t have all Companies since not all Companies have Open CTAs.
  3. Within the same rule, query all Company records and Merge the CTA Dataset into the all Company dataset. This will provide a combined dataset with all Companies and either a value for Open CTA Count or Null value for those that have 0 open CTAs.
  4. Set Action to Load to Company and update the Open CTAs field. This will need to be setup in such a way to populate the field if the Open CTA Count from the Combined dataset from Step 3 is not NULL as well as update the field to be 0 if the Open CTA Count is NULL. (E.g. two actions to Load to Company with different criteria to filter out Null values or filter on Null values)
  5. Schedule to run daily
View original

4 replies

Userlevel 7
Badge +1

Hi - you can do this by setting up a field on the Company object to hold the number of Open CTAs and then running a rule to populate this field daily.

Setup:

  1. Create a field called Open CTAs on the Company Object, set default to 0
  2. Create a rule in Rules Engine that queries the Call to Action records for Open CTAs and aggregates the data by Company - so you should get a dataset that has Company Name, Open CTA Count. This dataset won’t have all Companies since not all Companies have Open CTAs.
  3. Within the same rule, query all Company records and Merge the CTA Dataset into the all Company dataset. This will provide a combined dataset with all Companies and either a value for Open CTA Count or Null value for those that have 0 open CTAs.
  4. Set Action to Load to Company and update the Open CTAs field. This will need to be setup in such a way to populate the field if the Open CTA Count from the Combined dataset from Step 3 is not NULL as well as update the field to be 0 if the Open CTA Count is NULL. (E.g. two actions to Load to Company with different criteria to filter out Null values or filter on Null values)
  5. Schedule to run daily
Userlevel 7
Badge

@lerkatron is your query solved? Do you need any help here?

@john_apple appreciate your quick reply!

Userlevel 4
Badge

Schedule to run daily

Or every two hours with a CRON string :grin:

Hi everyone,

Thank you so much for this help.

This answers my question!

Reply