header
Solved

Is it possible to build a report to show which accounts have NO sponsors being tracked?

  • 19 December 2018
  • 5 replies
  • 151 views

Userlevel 4
Badge
Our CSMs have requested a report to show which accounts do NOT have any sponsors being tracked so it can easily be brought to their attention as they each have a large number of accounts they handle. Has anyone done this before?

Thanks!

Joe

icon

Best answer by liam_nicholls 20 December 2018, 12:24

I've had this on my todo list for awhile, so worked on it this morning - thanks for the reminder! This might suit your use case?

My preference would be to bring to the CSMs attention that the customer has no sponsor tracking records, or the last tracked sponsor is x days ago (which could mean the account might need some maintenance)

I'd send an internal email to the CSM rather than create a CTA (dependant on the volume of the issue)

** the screengrabs can't be displayed larger (which is odd) - but you can zoom in with your browser to see them clearer**

I created two fields on the Customer info object.

1) Date of last created sponsor tracking (date field)

2) Days since last created sponsor tracking (formula field)

re: Days since last created sponsor tracking field. The formula I used is: today()-

Date of last created sponsor tracking. This produces a number which can be used to display in semi-regular internal email reports about a customer. Or utilised by a rule, when the number gets to 180 (180 days since last sponsor tracking) you might then create a CTA or send email notification.

Next steps

I created a rule to gather the date of the LAST created sponsor tracking record. Set the show field config to MAX.



The report

This show the customers with sponsor tracking, and it's been a long time since another record was made



Sorting the other way - this shows the customers with sponsor tracking missing. You could build CTAs/rules of either of the two custom fields



This was a useful exercise for me as it showed that out of our nearly 560 customers, approx 135 have no sponsor tracking records



View original

5 replies

Userlevel 7
Badge +1
Hi Joe - you could create a bionic rule that pulls all the accounts that have at least one sponsor tracked and then merge that dataset into another dataset that contains your entire account list. You could then take action on the list of accounts that DON'T have at least one sponsor. Does that help?

Userlevel 4
Badge
Hey Spencer,

There may be push back on that one if the intention is to create a CTA that would be assigned to a CSM since they have a large number of accounts it would create a lot of noise for them. Is there any way to create it into a report that I could put on a dashboard that the CSM can passively review and address when time permits? What they are looking for is a simple visual that they can access when they have time.

Thanks,

Joe

Userlevel 1
I've had this on my todo list for awhile, so worked on it this morning - thanks for the reminder! This might suit your use case?

My preference would be to bring to the CSMs attention that the customer has no sponsor tracking records, or the last tracked sponsor is x days ago (which could mean the account might need some maintenance)

I'd send an internal email to the CSM rather than create a CTA (dependant on the volume of the issue)

** the screengrabs can't be displayed larger (which is odd) - but you can zoom in with your browser to see them clearer**

I created two fields on the Customer info object.

1) Date of last created sponsor tracking (date field)

2) Days since last created sponsor tracking (formula field)

re: Days since last created sponsor tracking field. The formula I used is: today()-

Date of last created sponsor tracking. This produces a number which can be used to display in semi-regular internal email reports about a customer. Or utilised by a rule, when the number gets to 180 (180 days since last sponsor tracking) you might then create a CTA or send email notification.

Next steps

I created a rule to gather the date of the LAST created sponsor tracking record. Set the show field config to MAX.



The report

This show the customers with sponsor tracking, and it's been a long time since another record was made



Sorting the other way - this shows the customers with sponsor tracking missing. You could build CTAs/rules of either of the two custom fields



This was a useful exercise for me as it showed that out of our nearly 560 customers, approx 135 have no sponsor tracking records



Badge

Hi @liam_nicholls, thank you for sharing! This is really helpful and I have a quick question.

What was your “Setup Action” in the rule in order to populate “Date of last sponsor tracking”?
 

Thanks,

Elizabeth

Userlevel 7
Badge

@elizabeth_04d7d9  - I’m not 100% sure but I believe that @liam_nicholls added the two fields (1. Date of last created sponsor tracking, and 2. Days since last created sponsor tracking) onto the Company object. The task above defines what the date of the last sponsor tracking is. Therefore, the Setup Action should use Load to Company to fill in the field on the Company object.

Hope this helps!

Reply