Solved

Roll up contact level fields to the account level

  • 10 February 2021
  • 3 replies
  • 85 views

Badge +1

Hey everyone!

I’m working on a dashboard for our Customer Success field which will show a variety of different webinar metrics including polling responses, attendance, program related CTAs, etc. The majority of webinar related reports are being pulled from a newly developed SFDC object, which is being populated by our On24 platform - fields include the attendee name, account name, event name, duration, etc. This SFDC object is on the contact level, whereas a handful of my reports need to be visualized on the account level.

Example: Our CSMs define attendance as at least one person from the account attending the webinar. Right now, when I filter my reports to the relevant webinar and drag in the associated fields (webinar name, contact name, attended (yes/no), etc.), I might have 50 contacts from ABC Inc. where 30 attended the webinar and 20 did not.

I want to be able to visualize that the account, ABC Inc., attended the session, since at least one person attended the webinar from their account, but all my attempts to visualize the data still bring in all the contact level rows. I’ve tried various methods of grouping by account name, count distinct of account id, etc. which allows me to get closer to my goal, but I’m still unable to have a clean visualization. The ideal state would be having a field that shows whether or not someone from the account has attended the webinar...not showing the individual 30 attendees and 20 no shows. I’ve created a data design with custom fields derived from case statements/transforms to calculate all the different conditions of attendance, but any yes/no custom field will still be tied to the individual contact, not the account as a whole. So if I filter out the no-shows and group by count distinct account id, I will still be left with the 30 attendees driving the visualization...not a single “yes” tied to the account of ABC Inc.

Any advice to wrap up contact level metrics to the account? Any idea is welcome including MDA/field/rule/DD/etc. to consolidate these contact level attendance rows into the account.

Thanks in advance!

Chris H. - Workday

icon

Best answer by phani_kumar 12 February 2021, 09:20

View original

3 replies

Userlevel 7
Badge +1

@chrishunt Thanks for writing your request. I am working on it andI will get back to you. 

Userlevel 6
Badge +1

Hi Chris, 

I have one idea, if you use concat formula field to create a duplicate Account Name field, you might be able to use a transformation task and do a Max(Account Name) grouped by duplicate Account Name. So you can have Max(Account Name) grouped by (Webinar details + Account Name) combination. Let us know if this approach works.

Userlevel 5
Badge +3

Hi @chrishunt 

Assuming your Input data like below.

 

If so, we can achieve your required output using Rules. 

Asuming your required result shold be like below.

 

To get the above result set, we have to create a Transform task and use all your required fields in the Group By Section and use the Contact Name field to the Show Me Section and apply “Count” function.

Screenshot:

 

Let me know if this helps.

Thanks.

Reply