If the GoldMine Administrator doesn’t take responsibility for helping the firm to deploy SQL queries this feature in GoldMine will probably not get done; Which would be a real shame because this is one of the most valuable features in GoldMine Corporate and Premium Editions!

I’ve written about SQL queries in previous issues of the GM-Success.com newsletter.  Here’s a link to a “Managing Smarter” article on SQL queries that appeared in GM-Success as well. In the last issue I even published a case study about a firm that transformed their business with GoldMine Premium and SQL queries (Read case).  

Because SQL Queries can search any of the tables in GoldMine (history, calendar, fields, opportunities, etc.) at once, you can get to information that is not available through the ‘out-of-the-box’ views GoldMine gives you otherwise. 

Here’s an example of an SQL query:

The query below returns completed activities in the past 30 days with an activity code of “DEM.”

Select conthist.ondate, conthist.userid, conthist.actvcode, contact1.company, contact1.contact, conthist.ref
from conthist
join Contact1 on contact1.accountno=conthist.accountno
where (conthist.ondate <= getdate() and conthist.ondate > getdate() – 30) and conthist.actvcode=’DEM’
order by conthist.ondate

The query below returns pending calls older than the today with an activity code of “SI.”

select convert(varchar(10), cal.OnDate, 101 )as Date, cal.userid, cal.ref,
contact1.Company, contact1.key1 as ‘Record Type’, contact1.key5 as ‘Classification’,
contact1.key1 as ‘Source’
from contact1
inner join cal on cal.accountno=contact1.accountno
where cal.actvcode=’SI’ and cal.rectype=’C’ and datediff(day, cal.ondate,
getdate()) > 0
Order by cal.ondate

With SQL queries you can select records you want and specify the information you want displayed.  There’s much more that’s possible with SQL queries – so it really calls for a combination of technical skills and your imagination to achieve what is most useful to your business.  

If your organization is not using SQL queries – why not?!  “We don’t need them” is not valid — there are just too many good reasons to be using SQL queries with GoldMine!  By the same token, if the answer is “we don’t understand how to implement them,” then please contact First Direct Corp. and let us give you a hand.  Email us at info@1stdirect.com or call (800)935-4386.

Listen to another customer!

Here is what Tracey had to say about her upgrade to GoldMine Premium …

Recently First Direct Corp. helped our customer (Integrated Marketing Concepts, Inc.) to upgrade from GoldMine Standard to GoldMine Premium.  The GoldMine Administrator, Tracey, is a very capable person which makes her feedback that much more credible.  We’ve had a lot of positive feedback from customer, but this particular email is as good as it gets and it should offer up encouragement for those who are still considering the decision of whether to upgrade their GoldMine.  With her permission I’ll quote her verbatim below:

Bob,

The transition has been much smoother than I’d anticipated, Thank Heavens!  I was doing the “Prepare for the worst, hope for the best” thing with all of this b/c I just wasn’t sure…  But it’s been a wonderful change!  My users like the GUI so much better and they’re all so excited about poking around that I can’t keep up!  LOL

I’m still doing a little “behind the scenes” clean up of course; call it re-organization if you will.  So for the time being I’m going to say no thanks to the training, but I’d like to keep the option of future training and assistance if I may, please.

My users are actually calling me and saying, “Wow!  Did you know you can… now?  That’s great!”  So they’re thoroughly enjoying scoping things out, and that way I can focus on the changes I need to make.  It’s really nice to give them something they like so much that they want to learn on their own for the time being.

Again, thanks much to you and Max (he’s terrific!) and I’m sure I’ll be catching up with at least one of you in the future for some assistance.

Thanks,

Tracey Bethel
Integrated Marketing Concepts

One of the simplest and most effective ways to improve your CRM system’s design is to use color to flag users’ attention. For example, if there’s a field you want users to notice when it is filled in, make the value of the data red. On the other hand, if there’s a field that you want users to notice if it is NOT filled in, make the label red. Here’s how to do both.

The first example, (see image #1 to the left) making the data in a field a color is the easiest.
In the Field Properties “Color” tab, just assign a “Fixed Color” to the data.

To make the label stand out if the field IS EMPTY requires an expression ( see image #2 to the left) so that the field will not apply a color to the label when it has been filled in. Here’s the expression you need and where it goes.

Note: The following example will make the “Source” field label red if it is empty and black if it is not.

if(trim(contact1->source)=”,color(“RED”),color(“BLACK”))

Your expressions can be much more advanced and even control the color of one field based on the value in another field. The point is that color is about the most visual tool a CRM administrator has available to them. So, let’s use it – don’t settle for the default color setting. Use color to catch your users’ attention.

Digital Dashboards, also known as Business Intelligence (BI) Dashboards, Enterprise Dashboards, or Executive Dashboards, are visually-based summaries of business data that show at-a-glance as a roll-up of business conditions through metrics and Key Performance Indicators (KPIs).  Digital Dashboards have become a very popular BI tool that has arisen in the last few years because of the understanding they can quickly convey to management and others. 

You can think of a Digital Dashboard as a graphic summary of some aspect of your data, potentially with the ability to drill down into the component data.  For example, a chart of certain sales activities by sales person. 

GoldMine Premium and Corporate editions both operate on a Microsoft SQL Database which includes MS SQL Server Reporting Services (SSRS), a server-based report generation software system from Microsoft that can produce Digital Dashboards.  Because Digital Dashboards can be accessed via a web interface, they can be viewed from most any Internet Browser, as well as right within the GoldMine GM+ Views or GM+ Browser.   

This all sounds rather complicated and, technically, it certainly does require skills.  But from a user perspective the concept is simple and easy.  Digital Dashboards will give GoldMine users a fast and friendly way to get information on activity in your GoldMine database, or any accessible SQL database for that matter.  

Digital Dashboards accessed from your GM+ Views or GM+ Browser can help your company to work smart and efficiently.  Putting these highly intuitive and expressive sources of information at the fingertips of your organization is going to increase the ROI of your CRM and go a longer way toward garnering greater usage. 

For the report developers out there I’ll describe what it is like to create digital dashboards and reports using SQL Reporting Services.  First, when MS-SQL Server 2005 is installed it will give you the option to install Reporting Services. The RS designer installs as a snap-in to Visual Studio .NET. If you do not already have VS .NET installed that’s ok because the RS Designer will run as a standalone application. There are three major steps to building a report: query, layout, and deployment.

In step 1 you build a SQL query by selecting a datasource and then using the Query Builder (Figure 1) to select the fields and tables for your report. Then you are presented with a wizard (Figure 2) that will bring you to step 2, allowing you to set the major aspects of the report’s layout. Here you select what fields to group on, whether you want totals to appear, and select which fields you want to see in your details section. After going through this wizard you will then be brought to the actual layout view (Figure 3) and it is here where you will fine-tune your report. The best way I can describe this part of the interface is it is a cross between a traditional report designer like Crystal Reports and an Excel spreadsheet with a bunch of formulas. Although the wizard will take care of a lot of the grunt work for you, be prepared to roll up your sleeves and write some formulas in order to get the report to be the finished product you are looking for. Once you get the report looking how you want, it is time for step 3 – deployment. To deploy your report all you have to do is specify the web server address and folder that is to be the destination for your report and simply run the “Deploy” option from the main menu, that’s it. All your users need to view your new report is a web browser.

So those are the major steps to building your report. And a digital dashboard is really just a single report comprised of smaller reports all arranged on a single page (Figure 4).

Figure 1. Query Builder

Figure 2. Report Wizard Layout

Figure 3. Layout Fine-Tuning

Figure 4 – Dashboard made of several smaller reports

Digital Dashboards, are essentially just graphical reports which can offer the ability to drill down for details.  They are very popular now because they provide businesses with a highly visually and intuitive take on data.  Because they allow the viewer to access the dashboard from their Internet browser, it offers decision makers a very quick way to gain understanding and insight.  In essence, dashboards a great tool for managing smarter!

If you’d like to see a demonstration of Digital Dashboard or if you need help putting this idea to use, First Direct Corp. is here to help you.  Call (845) 221-3800 or email info@1stdirect.com to request assistance!

Once you start using GoldMine Automated Processes, no doubt you will run into situations where you realize you have a problem with your process, or for some reason you need to identify all the records in your database with certain Automated Processes. Here’s what you need to know to do that.

To begin with, you’ll want to be aware of where and how GoldMine stores information about Automated Processes in the GoldMine Record. Information on Automated Processes attached to records in GoldMine is stored in the “CONSUPP” Table under the RecType “E.” Here is how the ConSupp Fields are used:

Here’s an effective way to give a “mass email” a personal touch and thereby lift response.   In other words, if your firm has developed fancy HTML email templates in your GoldMine Document Template library this is an easy way to enhance them when you want to give them a ‘one-to-one’ tone.

Start by making a clone (copy) of the template so that the original is not altered.   In the new template simply add lines (using your “return key”) above the designed template.  Then you can edit the top of the email with GoldMine merge fields to enter the first name and perhaps a short message — the shorter & sweeter the better.  I like to keep such a message to two or three sentences at most and to make the tone of the message feel like it was written to just one person. 

Because the formatting at the top of the email, where you want to add the text, will be dictated by HTML coding inside the template, you probably will need to alter the font and even the alignment of what you’re adding.  This can be done inside the GoldMine email client without altering the style of the actual template below.

With a personal salutation and the right personal note at the top of an HTML template, even if you broadcast this to group or filtered list of GoldMine records, this approach will still create the sense that you wrote to the recipient individually.   Doing so tends to invite a dialogue that doesn’t occur generally with mass broadcast emails.  So whether you send to one contact or a thousand records, this method can trigger a level of response that may pleasantly surprise you. 

Given the “personal feel” of the message, you’re apt to get responses which may not even relate to the primary marketing message.  Recipients may take the occasion of your email to ask you for something or to tell you something that was on their mind.  Either way, any response is an interaction that marketers want and sales people need!

(NOTE: If you’re managing replies to your campaigns with GoldMine Email Rules, you may need to pay more attention to what the respondent is saying.)

For those using GoldMine Email Merge codes to identify additional contacts, it’s going to be helpful for you to know how you can search for additional contacts based on merge code. This could be useful to get a count, or even to generate a targeted list for some purpose.

The query below can be pasted into GoldMine’s query window. Before you run the query you just need to enter the Merge Code you’re looking for in the query where I identified it to go.

Select Contact1.Company, Contact1.Contact, Contsupp.contsupref, Contsupp.address2 from Contact1, Contsupp where Contact1.Accountno = Contsupp.Accountno and Contsupp.contact like “E-mail Address” AND contsupp.mergecodes like ‘%ENTER MERGE CODE%’

After you run it and test it, make sure to press “Save” so you can use it again in the future. Then by just changing the Code you can get results for any Email Merge Code you’d like.

To learn more about SQL queries and for a list of over 60 sample queries I recommend “The GoldMine Guide to SQL Queries”

One of the basic lessons for a journalists learns is the “five W’s” of journalism (who, what, where, when and why).  Marketers could take a lesson from this adage as it relates to their CRM database.  Can your database answer the “five W’s” about your business?  Let’s consider this.

A database is worthless without the information it is designed to store.  And that information is more valuable if everyone does their job, from your webmaster to your sales force to your administrative staff in collecting details that need to go into your CRM database.   If all goes well you’ll be able to dig into your database to answer important questions such as:

Who are your new prospects this month?  What is the source of the leads that have been entered into your database?  Where are they located?   Why are they interested in your product or service?  The more valuable or useful the knowledge is, the more you’ll want to be able to rely on your database for the answers!   Don’t stop with the “W’s” — for example, how many prospecting calls or sales visits has each member of our sales force completed this month?  How much is in our sales pipeline?  Has anyone followed up on new leads from last month?   When was the last time anyone spoke to a particular prospect?  Which competitors’ products or services are our prospects using today?

Of course your ability to get the answers to intelligent questions depends on several factors including: 

• The effectiveness of your reporting skills and resources
• The efforts by your database administrator and webmaster to capture data from sources such as list services, website forms, tradeshow traffic, etc.
• The diligence of your sales team in entering information after their research and sales activities

GoldMine Software offers the features you’ll need to capture the information necessary to answer questions about the marketplace, your business activities, and more.  You’ll be able to use:

• Fields and tables to capture information
• Web import capability to capture completed web forms from visitors to your site
• Scripts that your telemarketers and sales people can use to capture information and automatically update fields
• Tools to track interactions (calls, appointments, actions, emails, etc.)  
• Forecasting functions let you manage your sales pipeline
• Various reporting tools (Native report writer, Crystal Reports, SQL Reporting Services, add-ons) will enable you to push our the answers you need to evaluate results and make decisions

Like a journalist who has a story to tell, a CRM databases has a lot of insights to offer up!  Ultimately, this is the end result of a well designed database that is used properly and diligently!