Adimpact.com
GoldMine Productivity Boosters
Name:


Primary Email:




I value your email privacy. I will never ever spam you or rent/share your email address with anyone. Period.



3-25-2009-11-03-06-pm

Spring is finally here and it’s time for some Spring cleaning on our GoldMine database!

Here’s a SQL Query that’ll show you any duplicate email addresses you have in GoldMine.  This is probably one of the most important queries you can run to ensure that any email blasts you do via GoldMine don’t result in duplicate emails being sent to the same person.

SELECT   c1.contact   ,
         c1.company   ,
         cs.contsupref,
         c1.accountno
FROM     contact1 c1
         INNER JOIN contsupp cs
         ON       c1.accountno      = cs.accountno
WHERE    ISNULL(cs.contsupref, '') <> ''
AND      cs.contsupref IN
         (SELECT  contsupref
         FROM     contsupp
         WHERE    CONTSUPP.rectype = 'P'
         AND      CONTSUPP.Contact = 'E-mail Address'
         GROUP BY contsupref
         HAVING   COUNT(*) > 1
         )
ORDER BY c1.contact

Simply copy and paste into GoldMine’s SQL Query window and hit query. Goldmine SQL

If you have two different records with the same email address do a merge/purge of the records.

How to Effectively Use ‘Dummy’ Uses in GoldMine

I got a great question on this from Susan Hallam from Hallam Internet,  basically how to do marketing calendars or other public calendars in GoldMine.

One of the great things about GoldMine is that its licensing is based on concurrent users, or who is actually logged in.  So that gives us a lot of flexibility in creating ‘dummy’ users to use for a variety of reasons.  However, for our topic today, let’s say we have a marketing calendar that includes email blasts, attendance at conferences and the like.

We can create a user in GoldMine called ‘Market’, the username is limited to 8 characters, but we can put ‘Marketing Calendar’ as the full name.

Dummy Users 1

You’ll then be able to access the Marketing Calendar from the calendaring module by changing the user.

Dummy Users 2

When scheduling activities on the calendar I’d recommend using Events since they can show up as multi-day activities, for things like conferences, trade show booths, etc.  Since the Events probably aren’t related to any specific contact just be sure to uncheck the Linked box when scheduling.  Also, be sure the user for the Event you’re scheduling is Market.

Dummy Users 3

If you’ve been using GoldMine for a while you’re probably very familiar with previewing a filter.  Here we have a list of all of our Web Import records.

Filter Drill Down 1

What you may not have noticed is the Drill Down button in the lower-right, and what it’s for.  So, once you have a filter like this previewed, go back to the Filters tab, and select another filter.  I’ve selected California.

Filter Drill Down 2

Go back to Preview and click Drill Down.

Filter Drill Down 3

You’ll see GoldMine has combined the two filters, so we’re only seeing records in California that are Web Import records.  If this is a useful filter for you, click Save to save the combined filter as anew filter.

Filter Drill Down 4

In our previous entry we discussed using the merge code fields to tag who should be getting holiday cards and gifts. Now that you’ve gone through the process of adding the appropriate merge codes to your contacts, you probably want to see who has been tagged!

If you are only working with primary contacts I suggest using a filter… Your filter will look something like this:

hc2-1

The key here is to use the Contains operator, as there may be multiple values in the field.

Once you’ve built your filter you can see the list from the Filter’s Preview tab as well as from the Contact Search Center!

You can, then, export from the search center, use GoldMine’s reports to print labels, etc…

You can also use GoldMine’s Export Wizard available under File | Import/Export | Export Contact Records (Or Tools | Import/Export Wizard | Export Contact Records in 7.x or below).

hc2-2

The wizard gives you a variety of options of the export format (dBASE, ASCII, etc.) as well as whether you want to export primary and/or secondary contacts as well as limiting the export to a merge code.

hc2-3

Once exported, you can use MS Word® to print labels, etc. My only complaint with the Export wizard here is that if the additional contact doesn’t have an address listed the Export wizard won’t automatically pick up the address of the primary contact.

Which brings us to our last method of export, a SQL Query, actually three.

Here’s one for primary contacts:

select company, contact, title, address1, address2, address3, city, state, 
zip, phone1, contact1.accountno from contact1 where mergecodes like '%HC%' 
order by company, contact

Here’s one for secondary contacts without an address (It’ll pick up the address from the primary contact):<

select contact1.company, contsupp.contact, contsupp.title, 
contact1.address1, contact1.address2, contact1.address3, contact1.city, 
contact1.state, contact1.zip, contsupp.phone, contact1.accountno 
from contact1, contsupp where rectype = 'C' and 
(contsupp.address1 is null or contsupp.address1 <= '') 
and contact1.accountno = contsupp.accountno and contsupp.mergecodes like 
'%HC%' order by company, contsupp.contact

Finally one for secondary contacts with an address:

select contact1.company, contsupp.contact, contsupp.title, contsupp.address1, 
contsupp.address2, contsupp.address3, contsupp.city, contsupp.state, 
contsupp.zip, contsupp.phone, contact1.accountno from contact1, contsupp where 
rectype = 'C' and contsupp.address1 > ' ' and 
contact1.accountno = contsupp.accountno and contsupp.mergecodes like '%HC%'
 order by company, contsupp.contact

Just change the ‘HC’ in each query to the mergecode you’re using. After running each you, can do the Right-Click | Output to Excel trick and you’re on your way!

GoldMine Blog Notification Form

Tree

It’s getting to be that time of year again, and with it the challenge of figuring out who should be getting what during the holiday season.

There are several different ways of doing this in GoldMine, but let me show you my favorite… This method uses the “Merge” field – and is usable for both primary and secondary contacts.

Primary Contacts:

GoldMine_Holiday1

Secondary Contacts:

GoldMine_Holiday2

To configure the merge field for holiday use we just have to change the lookup values for the primary contact, the same merge codes will automatically be used for secondary contacts.

Within the Merge field, either use the Right-Arrow or use the F2 key on the keyboard to see the Lookup list.

GoldMine_Holiday3

Single Left-Click on the New button to add different merge codes.  You may want to use a single one or multiple depending on how you handle cards and gifts.  The two I have here are:

HC for Holiday Card and
HG for Holiday Gift.

We use the format:

HC;//Holiday Card (Code, semi-colon, two slashes, and then description).

Only the code will be placed in the field.  With this format you can fit up to 5 codes in the merge code field.  Select one code, as you normally would when using the lookup list, then select a second in the same manner.  GoldMine will automatically place a comma and space between the two instead of replacing one code with another.

GoldMine_Holiday4

You can use any number of letters or numbers for the code but all codes should have the same number of characters.  I prefer two which allows up to 5 codes in the merge field but also makes it easy to remember what each is for.  You may want to use HC for Holiday Cards, but H1, H2, and H3 for different levels of gifts…

Get started on this and in our next  entry we’ll discuss getting that list out of GoldMine using SQL queries and the export wizard.

GoldMine’s alarms, in conjunction with scheduled activities, are a great way to remind yourself, or someone else, to do something.

GoldMine Alarms 1

Unless you turn your alarms on by default for certain activities, say Next Actions and Calls, though, you have to remember to check the box on the activity for the alarm to be turned on for the activity.

GoldMine Alarms 2

To set certain activity types to have their alarm defaulted to on simple go to Tools | Options then the Alarms tab.  Then click on More Options.  Here you can choose which activities will have their alarms on by default.

GoldMine Alarms 3

 

 

What’s New in GoldMine Premium Edition 2013.1

GoldMine Premium Edition now supports 32-bit editions of Microsoft Office 2013. This includes support for the Word Link, Excel Link and Outlook Link.

GoldMine Premium Edition now includes an integration with the popular Email Marketing service Constant Contact. Please see the Marketing Tools chapter of the Online Help.

Full Release Notes are available here: http://www.thegoldmineblog.com/files/ReleaseNotes-GMPE2013-1.rtf

Obviously, the most exciting thing here is the new integration with Constant Contact.  FrontRange put together a nice demo on the new feature which can be viewed on YouTube – http://www.youtube.com/watch?v=JQ47ANluBn4.

IntelliClick Webinar

September 3, 2013

in Misc.

IntelliClick is one of the great 3rd party addons for GoldMine for tracking open rates, etc. for the emails you send out.  They have a new great feature for tracking your prospects activity on your web site!

Register Now: New IntelliClick for GoldMine Email Marketing Capability IntelliClick now offers “on click tracking” within your company website pages.  Tue, Sep 10, 2013 10:00 AM – 11:00 AM CDT
Registration Web Link: https://www2.gotomeeting.com/register/383742458

This capability has just been released and is included with all IntelliClick for GoldMine subscriptions that have website navigation tracking. If you offer other media that visitors can click on within your website pages, such as videos, .pdf literature files, website forms etc., you will now be able to track them back to GoldMine in real time. Please join this webinar to learn how you can implement this capability on behalf of your company.

I’ve gotten a couple of emails in the last week that go something like this:

“Hi Chad… I am having a bit of an issue that the Goldmine folks couldn’t help me with after over an hour on the phone.  All I need is a report showing Completed appointments (and calls if possible) during a certain date range with columns: userid, company, contact, date.”

It’s pretty simple.  Here’s the SQL Query you’ll need for something very similar, I’ll add a few more columns that I think many people will want.  To change the date range change the first (beginning date) and second date (ending date) in the FROM clause.

 SELECT   conthist.userid                                            ,
         CONVERT(VARCHAR(10), conthist.ondate, 101) AS activity_date,
         conthist.ref                                               ,
         CASE
                  WHEN srectype = 'A'
                  THEN 'Appt'
                  WHEN srectype = 'C'
                  THEN 'Call'
                  WHEN srectype = 'O'
                  THEN 'Other'
                  WHEN srectype = 'T'
                  THEN 'Action'
         END AS activity_type,
         contact1.company    ,
         contact1.contact    ,
         conthist.actvcode   ,
         conthist.resultcode
FROM     contact1
         INNER JOIN conthist
         ON       contact1.accountno = conthist.accountno
         AND      conthist.ondate BETWEEN '1/1/2010' AND      '1/31/2013'
WHERE    srectype IN ('A',
                      'C',
                      'O',
                      'T')
ORDER BY userid,
         ondate

The Export Wizard

  • Records To Export
    • Primary Contacts
    • Secondary Contacts
  • Export Methods
    • CSV
    • DBF
    • ASCII
    • SDF
  • Export Filter
  • Fields to Export
  • Saving and Reusing Export Profiles

 

Over the years, I’ve post many SQL Queries that GoldMine users have found very useful.  To see the entire list check out the SQL Category on the right or just use this link: http://www.thegoldmineblog.com/?cat=8

For any of those, here’s how to export those results.  Right-click in the results area, then choose Output To and Excel.  While you can output the results to Word or the Clipboard, you’ll find the formatting off… Excel works the best.  From there you can massage the data, print, save, email, etc.

GoldMine Output SQL Query

The only times you’ll run into trouble is if the SQL Query includes notes, those tend not to transfer well.

If you are responsible for sales for your company a great way to manage your pipeline is through forecasting sales.

To forecast a sale in GoldMine:

1) Be on the contact record you want to forecast the sale with.

2) Choose Schedule | Sale from the menu.

3) Enter in as much information as you have, but I think the minimum would be the items circled below.

4) Then Click OK.

5) Your Forecasted Sale will appear on the Contact’s Pending tab as well as your Calendar and Activity List.

6) To mark the sale as Won or Lost, complete the activity. It’ll be removed off of the contact’s Pnding tab and onto their History tab as either a Won or Lost Sale.

7) To see all of your Forecasted Sales use your Activity List.

GoldMine Activity List Forecasted Sales

 

Email Blasts from GoldMine

  • Create The Email Template (What you’re going to send)
  • Filter or Group (Who to send the blast to)
  • Test Email
  • Email Blast
    • Queue for Delivery

 

GoldMine’s Forecasted Sales allow you to keep track of your open sales pipeline.  If you’re not currently using Forecasted Sales check out this post for the basics: http://www.thegoldmineblog.com/?p=2017

Here’s a handy SQL Query to show all of the past due Forecasted Sales for the team.  To run the query go to Tools | SQL Query:

Past Due Forecasted Sales

Then copy and paste this in:

SELECT   ca.userid                 ,
         c1.company                ,
         c1.contact                ,
         ca.ondate                 ,
         ca.number1  AS Amt        ,
         ca.duration AS Probability,
         ca.ref                    ,
         c1.accountno
FROM     cal ca
         INNER JOIN contact1 c1
         ON       ca.accountno = c1.accountno
WHERE    rectype               = 'S'
AND      ondate               <= GETDATE()
ORDER BY userid

GoldMine’s Peg Board allows you to know who’s logged in to GoldMine, who’s not, and everyone’s last login or logout time.  You can also use the “Log Away” function to denote where you are if you’re not at your desk.

To view the Peg Board open up the GoldMine calendar then choose the Peg Board tab.

GoldMine Peg Board 1

You may or may not see everyone listed, if you don’t, then do a Right-Click and select Users.

GoldMine Peg Board 2

Here you can add individual users or user groups by double-clicking on them.

You’ll also notice the “Whereabouts” area.

This lets you leave a message to other GoldMine users to let them know where you are and when you’ll be back.  To use this, use File | Log Away in GoldMine.

GoldMine Peg Board 3

After choosing Logout you will be logged out of GoldMine, you’ll show back in after you login to GoldMine.  If you choose Wait for me, GoldMine will be minimized, but still running, when you maximize GoldMine, the Peg Board will be updated to display that you are back in the office.

Happy New Year’s Everyone!

I’m looking forward to another great year with GoldMine, and if you have any suggestions for blog postings you’d like to see please drop me a line at chad.smith(@)thegoldmineblog.com!

GoldMine’s Opportunity Manager gives you a great place to track all of your open opportunities.  You can even record your calls and appointments against an opportunity, which are all tracked within the Opportunity Manager.

GoldMine Opportunity Manager History Tab

Of course, we can’t remember all the opportunities we have and open and should be following up on, so here’s a SQL Query to show you all of your open opportunities sorted by the last time you have a history record recorded for them.  To run the query go to Tools | SQL Query:

GoldMine Neglected Opportunity SQL Query

Use this SQL Query:

SELECT   ch.lasthistory,
         o.userid      ,
         o.company     ,
         o.contact     ,
         o.name        ,
         o.status      ,
         o.cycle       ,
         o.stage       ,
         o.source      ,
         o.startdate   ,
         o.foramt      ,
         o.forprob     ,
         o.accountno
FROM     opmgr o
         LEFT OUTER JOIN
                  (SELECT  MAX(ondate) AS LastHistory,
                           loprecid
                  FROM     conthist
                  GROUP BY loprecid
                  )
                  ch
         ON       o.recid = ch.loprecid
WHERE    rectype          = 'O'
ORDER BY ch.lasthistory

Just copy and paste it into the query window and hit Query

I think this concept would be very applicable to some GoldMine Dashboards as well.  If you’re looking for some dashboard help please drop me a line at chad.smith@thegoldmineblog.com.

The Delete Wizard

  • Archive Option
    • Video: Creating Additional Contact Sets
  • Quick SQL Backup
  • Delete Wizard
    • Entire Records
    • Old History

 

Here are a few great keyboard shortcuts to use in GoldMine to help you be a little bit more productive:

F2- Brings up the picklist for the field you’re currently on
F4 – Opens the Contact Search Center
F5 – Opens the Email Center
F6 – Opens your Activity List
F7 – Opens your Calendar
F9 – Opens the Opportunity Manager
Control-Shift-E – Send an Email to the Current Contact

Purchase a yearly subscription to GoldMineTraining.net for just $99/year.  Click below for more information.