[Files] Customer Database- what Dean May does

[Dean May]

In my Microsoft Works flat file database (which could just as easily  
be done in a spreadsheet like Excel- demo attached) I have three  
fields relating to service*: date tuned, tuning interval, next service  
due. I format them respectively as yyyymmdd, mm, yyyymm. Using this  
kind of format enables meaningful sorting on these fields. So if I  
tune a piano today, for example, I would update the date tuned field  
to today: I would enter 20080412. If the tuning interval field was 9  
for this client, then I would update the next service due field to  
200901. When I printed out this month’s call back report (demo  
attached) I chose every record that had a next service due value of  
less than or equal to 200804. I sort that report on zip code and  
subdivision which groups the clients by neighborhoods.

If I have a client that does not want called back I stick 999999 in  
the next service due field so they will never show up on the callback  
list.

When generating my post card reminder mail list it is a little more  
complicated. First I import all the records into Excel- it’s just a  
copy and paste actually. Excel is a little handier at sorting and  
selecting. I choose the records that have a next service due month  
equal to the current month, then every record that has a month  
serviced equal to the current month. I then add every record that has  
a month serviced equal to 6 months difference from the current month.  
I also pick some from the 999999 group so that they get a postcard  
every 12 months.

So for April I would choose those records with a next service due   
equal to 200804, then every record that started with the following in  
the date tuned field: 200710dd, 200704dd, 200610dd, 200604dd,… etc.  
For the 999999 group I’d only pick those with months equal to 04. You,  
of course, could come up with your own strategy on how often to send  
reminders.

Picking those months from the date tuned field is actually pretty easy  
to do. I add another field that culls the month serviced from the date  
tuned field**. Then I sort on that field and it groups all the same  
months serviced together, so I can just pick all the 04 months and all  
the 10 months. If I was an Excel programmer this could easily be done  
with a macro, but I’m not.

When I have my post card mailing list all done, I sort on the email  
field and all those records with emails bubble to the top and presto,  
I have my email reminder list which I save as a separate file for my  
email merging.

I use Word to do my post card mail merging and I have a Word template  
for the email merging which uses Outlook to send them (demo attached.  
When opening choose the Piano Customer Database-Demo file as the  
source file and it should work). If anyone wants to do this for  
themselves and has trouble I’d be happy to help as I’m able.

[Files at:]

http://tinyurl.com/5cdzxg

https://www.moypiano.com/ptg/files/2008-April/000303.html

Dean

Dean May             cell 812.239.3359
PianoRebuilders.com   812.235.5272
Terre Haute IN  47802

*Here all the fields I use: Name, First Name, Address, Mailing  
Address, Mailing city/state/zip, City, State, Zip, Home Phone, Email,  
Work phone, subdivision, Date Tuned, Tuning Interval, Next Service  
Due, Mail, Note.
The Mail field gets a “y” if they are on the mailing list, an “x” if  
they aren’t.
The note field is an extra long field for any kind of notes about the  
customer.
The post office really doesn’t like punctuation marks in addresses so  
the Mailing City/State/Zip field would look like: Terre Haute IN47802.

** I actually first create a field with a date that has standard mm/dd/ 
yyyy format for mail merge insertion into the postcard. So if the date  
tuned field of 20061103 is in cell M2 the code looks like this:  
==CONCATENATE(MID(M2,5,2),"/",RIGHT(M2,2),"/",LEFT(M2,4))  which  
yields 11/03/2006. If that is in field Q2 then the code to cull the  
month looks like, =LEFT(Q2,2), which yields: 11

  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://www.moypiano.com/ptg/pianotech.php/attachments/20080412/845c9831/attachment.html