Custom Calculation Property for Contacts

[THalfast]

Hi there! I am not sure if this is the correct place to ask this question but....here goes.

My team has asked me to look at a couple of data points to help refine their processes and I am trying to come up with the best way to accomplish what they want.  One of the goals is to potentially leverage a couple of "calculated / formula" fields to assist with lead scoring and active list management.

One example would be calculating something like an engagement score.  I know there are fields for # of marketing emails clicked or # of marketing emails opened. However, these two fields are constantly incrementing over time and are not time-bound.

Due to our business model having many long-standing contacts as well as new contacts, a simple incrementing field that counts "# clicked" is less useful for comparison if it can't be normalized somehow.  Doing an average over a span of time, helps to potentially normalize that comparison.

We'd like to be able to calculate something like "Avg # of marketing emails clicked over the last 90 days" and then use that calculated value to do some things (triggers for active lists, lead scoring adjustments, etc.)

Does anyone have any suggestions as to how best to accomplish something like this at the contact property level?  And if it is a calculated field, perhaps an example of how you might actually build the forumla/calculation

Thanks in advance!

[SFei]

@PamCotton How would you go about collecting the total clicks in 90 days? I can create a workflow to add to the total number but cannot figure out how to remove as they fall out of scope. Thanks!

[danmoyle · Accepted solution]

@THalfast would it be helpful to create this as a report and email the report weekly to the sales teams or sales folks? 

Custom report: Contacts and Marketing email activities as the data sources (I'd play with primary data source, too), and add the filter of "events timestamp is less than 90 days ago." Then you can configure the marketing email activities as an average. 

I think this might help get you started. 

[THalfast]

Dan,

Having a custom report is something I have considered and will likely do regardless of other options - so thanks for that. 

I was more hoping to calculate a discrete data point as a contact property that could be used in lead scoring or workflow branch management.....which is the challenge I am facing since event timestamps other than ones like "last seen" or "last clicked" don't seem to be available to forumlas for calculations related to scoring properties.  And then of course, there's always the lack of a "today" field which I know there are some solutions out there to address that.

The other option I am considering is perhaps using an active list based on filters that can do what I was hoping do to in calculations.  For instance having a list where "last clicked is within the last fiscal quarter" and then for scoring, if list membership = "that list" they get a point allotment.  This would save me from having to create a constantly updating "today" field and would allow the list membership rules to manage the rolling date calculation. 

Ultimately, I am just looking for the easiest way to calculate some time-bound engagement metrics that could be discretly used as a calculation within a scoring system.

[PamCotton]

Hello, @THalfast. Thank you for posting in our Community!

I would recommend addressing your goa, starting by calculating the 'Average # of marketing emails clicked over the last 90 days,' and consider creating a custom field using the formula. 

For example: 

Avg Clicks 90 Days =

Total Clicks Last 90 Days

Number of Days in 90 Days

Avg Clicks 90 Days = Number of Days in 90 Days

Total Clicks Last 90 Days

Additionally, I'd like to invite our top experts to collaborate and share their insights on this. @danmoyle,@JustinPerkinsC, @TomM2 could you please provide your thoughts on optimizing this calculation for our business model?

Thank you,

Pam