need a quick query to know how many times we’ve contacted a customer

(0) Share

Report

Posted on by Rod C

Hello,

With the Dynamics CRM database, I need a quick query to know how many times we’ve contacted a customer (in the last 30 days, 90 days, 180 days, etc).

At first, I thought there might be a SQL view of "Activity History" that could be leveraged but cannot find anything.

Any ideas?

Thanks

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

ashlega 34,477 on at

Like (0)

Report

Something like this maybe:

SELECT COUNT(DISTINCT ActivityId) FROM

ActivityPartyBase

WHERE PartyId = <accountid>

Although, this will likely give you all activities (incoming/outgoing, phone calls/emails/letters/etc), so you may want to join in ActivityPointerBase(using activityid) and filter by ActivityTypeCode and some of those directon code fields (EmailDirectionCode etc)

Was this reply helpful? Yes No
Zach Mast 70 on at

Like (0)

Report
Here is a working query to get you started:

select ap.[Subject], ap.[Description], email.DirectionCode --select count(*) --If you just want the count from activitypointer ap join account on ap.RegardingObjectId = account.accountid left join Email on ap.activityid = email.activityid where ap.activitytypecode in (4202) --Emails, update this to have the desired activity types and account.name = 'A Datum Corporation' --Update to your account name, or use accountid instead --and email.directioncode = 1 --1 = Outgoing, 0 = Incoming

Was this reply helpful? Yes No
Rod C on at

Like (0)

Report

Thanks for both replies. I tried your queries even with no where clause and only see a few rows. So, perhaps, there is something else in our system created by one of our vendors. I will keep investigating.

Was this reply helpful? Yes No
Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

SELECT COUNT(*) FROM

ActivityPartyBase

WHERE PartyId = 'GUID_OF_ACCOUNT'

Should give you the total number of interactions with activities for that account

Was this reply helpful? Yes No
Rod C on at

Like (0)

Report

Thanks for your suggestions. I think I have more research to understand which tables / filtered views to join together.

Was this reply helpful? Yes No