04-26-2011 03:04 PM
So here's what I want to do. I have a Group and I'd like to create a lookup from that group of people who haven't had a history action in the last month. There is the Contact Activity lookup, but that queries from my entire database. I want to do a Contact Activity query from a group lookup. I'm especially targeting this group of prospects and I'd like to be able to look up which ones I haven't touched recently.
It seems like something a CRM software should be able to do fairly easily.
04-26-2011 09:29 PM
Is your group a dynamic group? If so, you could just add fields to the query based on all the fields that you associate with a "touch", making the dynamic group a big AND statement. e.g.
where ID/Status = "dynamic lookup value"
AND Last E-Mail older than 30 days
AND Last Attempt older than 30 days
AND Last Reach older than 30 days
AND Last Meeting ...
you get the picture.
The big issue on this working is whether or not you add Edit Date to the query. You could actually just have a query that used that one field, however, any change to the contact triggers the edit date, so that's not a real measure of marketing "touches", if that's what you're looking for.
Hope this helps a little bit...
04-28-2011 07:21 AM
Thanks, Richard, it does. It's at least a start. I'm just curious if I can create dynamic queries that pull only from a specific group. I want to know if a member of this group was contacted in the last month, but I don't want to run that query for my entire database. Is that possible?
Can I create a dynamic query for group membership? I don't know how to do that, but if I could, that would solve it right there. At least I think it would.
Really, I'd like a subgroup of my prospects group to be prospects that haven't been contacted in the last 30 days.
04-28-2011 07:36 AM - edited 04-28-2011 07:39 AM
Not sure if this will help, but if you go to the History tab of a Group, then under the tab change the "Show For" to "Group Contacts" it will show you the history of only the group members. You can then use the date filter (and the Type filter if desired) and sort the names by the Contact column to see has contacted.
Another option would be to create a new contact field that you can put the name of the Group the contact belongs to in. You can then use this field in the query that Richard suggested. To populate this field with the same group name for multiple contacts:
- Select the group in the Groups screen
- Click under the Contacts tab of the group and select Create Lookup
- Go to Edit > Replace Fields, choose the contact field you created for the group name, and type the name of the group
- This will place that value in all the contacts in the lookup
- Repeat for each group you would like to do this for
Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.
04-28-2011 11:44 AM
That Replace Field is very helpful, but I'm still having some trouble getting the query to function right. For example, the Letter Date field doesn't seem to be working for me. I have history entries for Letter Sent, but when I run a query that includes the new field I created And Letter Date Older than 30, it doesn't come up with anything. Even though I know there are contacts that have letters sent to them 3 months ago. Is the Letter Date field not querying the Letter Sent field from the History?
Is there somewhere I can go that will define these fields for me? I'm specifically interested in the "Last..." fields.
04-28-2011 11:40 PM
04-29-2011 08:57 AM
Thanks very much for the help, but it sounds like I can't do what I want to. Which is really quite disappointing. A dynamic group composed of members of an existing group that haven't had a history entry in the last 30 days.
Right clicking doesn't work on the dynamic group membership criteria. It's tough to tell what fields from the history I can query, I'd really just like to query whether or not they have any history over the last 30 days. The Contact Activity lookup function would work perfectly, If I could then sort my Contact List by Group, but I don't think that's possible. Again, seems like having Group name would be a column you should be able to view on the Contact List.
Thanks for trying, though.