09-20-2010 02:23 PM
Looking for suggestion in Contact Table how to better design it's field(s), layout and product list to help track one or more US mailers going to +1000 contact record and many contacts being sent more then one time?
Example I sent out 4 mailers so I have four mailer fields and placed them on the Contact layout and they refer to a Mailer Product list with the mailer codes. When I send a mailer a code is like 020 that I insert in mailer_Field#1 for the +1000 contacts doing Edit | Replace field with 020. The next mailer and if some contacts will be a repeat I will be inserting the next code 030 into the next Field#2, ect..
A) Issue I hate is to create new fields with three Charc length each time I do mailer. Other option is a 1 charc field check box y/n with X and place than on a new Tab layout.
AA) If I want to search for all contacts with mailer code 020 I have to create a Group and define the query to check all fields for data and than from contact list view need to sort out the records not containing the code 020. I could also just define in query or do multiple Lookup up's Add for only 020 code but that to is work.
B) I can't insert into a History record the code for the contacts because when I search for all the contacts in History type for code 020 ACT limit is 1000 records to create the contact list view.
C) I can't have one field who's length is say 20 charc long and points to a Mailer codes Product check boxes list because after I do the first replace field with a code you can't do Insert only Replace without clearing the past code(s) in the field.
For marketing I'm trying to track who and how many mailers it took and what style worked for a contact to strike.
It would be great if possible to Insert more codes (data) into a field rather than ovcer right existing codes (data).
09-21-2010 09:17 AM
You touch on some of the real world problems of tracking this kind of data when you are using a flat file system.
I might suggest that you take a look at the trial for TopLine Designer. This snap in (compatible with Act! 10.02 and up) will let you create custom tabs off contact, groups, and companies (and oppties if Act! 2010+) or any combo of all.
With this ability in place, you could then have a "Mailer History" table, or the like, with fields for let's say, Mailer Code, along with other relevant fields that you may come up with...
The benefit is that the Mailer Code values are always in the same field, that a contact can have no mailer records or 500 (no limit really), and you can search in just one place.
The one area where you will have to do some work is creating the records in the custom table after you do your mailing - this will take a few steps, but the benefits for recording history and being able to create clean lookups should outweigh the extra up front stuff.
Here's a thought on that:
Keep your Mailer Code field as you have it now on the contact record. (Or create a new one if that is more desirable.)
Prior to doing a mailing, use the Act! mass edit/update tool to make sure the field you will be using is empty for ALL records in the database. (Note I said for ALL contacts, not just the ones that you are mailing to - this is key.)
After doing a "mailing", use the Act! edit function to update the field for all of the contacts in the group as you mentioned in your post.
Next, using the TopLine Designer Migrate Data tool that is installed on the File menu, map your contact field you just updated to the Mailer Code field (use whatever name you actually called it) in your custom TopLine table and run it.
This will scan through the entire database, any contacts it finds that have data in the field you mapped, will trigger a new record to be created in the custom Mailer History table.
Once you have done it once or twice, won't take much time at all and will allow you to create as much mailer history as you want...
09-21-2010 10:54 AM
Thanks for taking the time to offer this suggestion. I will run the trial to see if it will be more time efficient than what I currently do.
I am stuck on what you said for:
1. The one area where you will have to do some work is creating the records in the custom table after you do your mailing.
1Q1. Are you saying for each mailer code I would create a record for it?
1Q2 BTW: I have 65,500 contact records which represents a location to service that contains the location details like service date, address, spec's, Billing and other contact info. Yes there are duplicate contact records so one could cover more than one contact record. I create a Company record and link the contact records I manually find to the company. This helps show me in the contact list view with a Blue Underline a record is linked to same contact. I bring this up because I wonder if the DB size will grow big % by creating 65,500 mailer code records one for each contact. Multiplied 65,500 X the number of mailer codes I come up with? Reading further down I assume not since Designer will scan all contact records mailer code field and if there is a value it would than create a history record within that Contact record?
2. Prior to doing a mailing, use the Act! mass edit/update tool to make sure the field you will be using is empty for ALL records in the database.
2Q1. Are you referring to Menus' Edit | Replace, Copy, Swap? to clear the field and than I insert the mailer code.
3. One thing I report on is showing in Excel all Contact records location address and the units ID# with the one or more mailer codes by exporting from Contact list view the defined Options (fields).
3Q1. Will it be possible to get the same result but in a different way with no more working it?
4. I will also confirm the licensing assuming the basic version being $99 I would have to multiple that times the number of ACT User licenses to cover my remote users and ACT for Web server to be sure mailer info propagates during ACT Sync. Meaning even if I am the only one doing the mailers I need it installed on each remote PC so they can see if a contact record received a mailer if the contact calls in, or they can search for all specific mailer code and call those contacts.
Many thanks for contributing help,
09-22-2010 09:45 AM
What about creating a history for each mailer. You can create a history to an entir lookup (for example Mailer09102010 or somethign else unique) then you can do a lookup of everyone who received that mailer by doing a keyword search to lookup that unique history.
09-22-2010 11:59 AM
There is a keyword search limit about 1000 records found.
I wish it was that simple and wish Sage would remove that limit at least for a 64bit OS and 4GB system what good is SQL if you can't maximize it?
I'm going to post the below to Share Your Ideas as won and later confirm if not posted for each create them.
I only hope Sage for 2013 focuses on reduce steps to increase productivity like the keyword search limitation, store Options list views fro all tables, and after Create Lookup on a Group allow you to Lookup on Contact record field for the current lookup having ADD or NARROW remain there to select.
Bug fixes still needed in ACT 2012 like ACTDIAG Reindex does not compress DB after removing data.
Like this post a better way to track for marketing a mailer or tradeshow campaign.
Integrate Address verification so we don't have to do it external of ACT with import /export at field with 3rd party add-on to processes for Mass mailers and insertion of GEO X/Y position for Mapping.
It would correct addresses format for more accurate Maps.Google.com results, tag records with a problem finding address for bulk rate postage, performed this verification on multiple address (Billing & Shipping,ect..) Contact and Company records.
Develop more ACT integration options with Maps.Google.com by leveraging its GEO mapping support of multiple routes no limit from a contact list view to help with targeted mailers buy given radiuses, extracted out of contact DB other records in a given radius of one or more multiple radius points. As a person plans a trip by car or fly's from point to point the road Sales Reps. radius in miles of potential clients to pop-up.
09-24-2010 10:32 AM
You could create a group for each mailing and then just put all those who received the mailing in that group. Future lookups will be easy. Just go to the group, right click in the contacts tab, and choose create lookup. In the contact view, if you go to the groups/companies tab, all the groups the contact is in will be listed.
09-24-2010 11:53 AM
What you state works if you want to see an individual contact through the groups/companies tab of how many and what mailers they received with a Static defined Group list. You're also limited seeing only one group of that code at a time. Unless you spend more time creating more Groups that contains the others from other group mailers. Those that received more than one mailer you will not be able to see what mailer codes they got.
Also you don't have a couple of clicks to generate a report showing the marketing results of responses to specific mailers and who they were and how much in sales, unless you start exporting out to Excel and merge sort ect..
What I'd like to see is more simplification from what I do now to get overall stats for the total number of contacts and by codes and what codes they received. Also to still see from contact list view who got what mailers and sort by mailer codes.
The issue is not finding mailer codes as much as how to insert more than one code in to a field without overwriting past code that a contact may get over time. Also not have to create multiple fields to store a code every time one is sent that may be more than once goes to a contact.
I thank you for your post because it made me think outside of the box. I may be better off exporting my contact records ID# and some other field and just manage the reports in Excel.