Using ACT! Reader & Microsoft Access to read ACT! Data

Using Excel OLE/DB is a very quick and easy way to get data out of ACT! for reporting or analysis. However, this method is constrained somewhat by the pre-defined views that are available for use. If you need more data, or more detailed data, you might want to look into using the ACT! Reader utility, which opens up ACT! data to be used via ODBC.

Note: ACT! Reader is only available to ACT! Premium users, or those who purchase the utility.

So, the quick and dirty instructions to getting this to work. (I am assuming you have some familiarity with Access and ODBC.)

• Open ActReader.exe. This utility is used to set a password on the SQL Server instance that the ACTReader "user" will use for an ODBC connection.
• Open Microsoft Access. We will be using this in our example, but you can use other tools, I prefer Access.
• Go to External Data>More>ODBC Database
• Choose Import, or Link tables, then OK
• Click the New button to Create a New Data Source (File or Machine - does not matter)
• Choose SQL Server for the driver
• Create a name, and description for the data source. The Server can be any machine, but if you are running ACT! locally, choose your machine. Also, append ACT7 to the end of the machine name. e.g. MYLAPTOP\ACT7
• Click the "With SQL Server authentication..." radio button.
• Login ID is "ActReader", and password is whatever you set using the utility.
• Change the default database to the ACT! database you would like to work with

After Finishing, you will be presented with all the tables in the ACT! database. You can now use them like any Access table - build a report, application, analysis, etc. For example, I created an application for project and time tracking. Certain ACT! users are tagged with a Yes/No field in ACT! that sets them to an "active" status. Then, in Access, I created a macro to import *only* those contacts with the field set to "Yes", and append them to the master table in Access, which is the cornerstone for the tracking system. This way, I always have the most current contact data in Access, directly from ACT!.

Hope this helps!!!

Using Notes vs. History

History and Notes are two quite different things. History is for tracking everything you’ve done with the contacts in your database… emails you’ve sent, calls you’ve made, meetings you’ve had, letters you’ve written, etc. It’s for recording the day-to-day history of your relationships. Notes are more general - useful for tracking impressions of a person or business or recording more long term items of interest.

Notes and Histories need to be separated as uniquely different. An ACT! Note should only be used for commentary regarding the contact. It should not include any information regarding any action that has occured. Consider using notes for commentary such as likes and dislikes, product preferences, personal highlights, etc - generally for those things you want to remember or reference quickly.

On the other hand, History is the audit trail of the things you have done for a contact - a chronological accounting of all things that have been done with or for the contact. It is the point of true “contact management”. A completed call and held meeting history will automatically update fields on the contact screen labeled as Last Reach and Last Meeting. These fields are searchable, sortable and reportable.

A few more differences:

• Unlike Histories, Notes cannot be categorized. There is no Regarding field for a note.
• Second, Notes do not have a classification or status such as Call Completed, Call Left Message, Meeting Held, To-Do Done, etc.
• Third, Notes do not have a field to track duration. How much time did this activity take?
• Fourth, Notes do not update system fields like histories such as “Last Reach” or “Last Meeting” thus making it harder to determine when the last ‘true’ contact or touch was made.

Separating Notes from Histories is smart if the user understand the difference between the two. Instead of simply typing a note the next time a contact calls you, right mouse click and choose “Record History” (anywhere on the contact record), or use the icon on the history tab (see below). Over time you will be glad you did.

Utilizing Group Membership effectively

Often I run across users that manually add and delete members from a group based on certain criteria. It could be the state they are in, the "type" of contact with which they are working, or the status based on where a prospect is in the opportunity cycle.

If you find yourself performing the same actions, or repetitive actions on a regular basis, that is a signal to look at your program and find out if what you are doing could be automated.

Groups in ACT! can be populated by something called a dynamic query. Here's the quick directions to get started:

1. In the Group window, create a new group called Opportunities, then on the right-hand side, click the Add/Remove Contacts... button
2. The top half of the window is where you may have been manually adding contacts. What we want is the bottom half, labelled "Dynamic Members"
3. Click the Edit Criteria... button
4. Create the following using the drop down boxes: Type: Opportunity, Field Name: Status, Operator: Equal to (=), Value: Open
5. Click Add to list, and if desired, click the Preview button.

You have just created a dynamic group, and any contact where an opportunity is opened will be automatically added, and a contact where an opportunity is closed or removed will not be incuded in this group. There are many options with both the Opportunities and Contact fields to help you manage groups of contacts more effectively.

p.s. it is totally possible to have both Dynamic and manually added contacts.

ACT! Reporting using Excel with OLEDB

Sounds confusing for the non-technical user, but once set up, this functionality can be extremely helpful. Furthermore, if you need data formatted/reported on using specific calculations or parameters, automating with macros or Visual Basic can prove to be an incredible time-saver.

The first couple steps of getting OLEDB setup is a bit different depending on which version of Excel you are using, so here’s a quick start for 2003 and 2007 versions.

Once set up, you can reuse the connection, so this does not need to be done every time!

• 2003: Data->Import External Data->Import Data
• 2003: Click “New Source” button
• 2007: Data->From Other Sources->From Data Connection Wizard
• following are common steps for both versions
• Choose Other/Advanced, then Next
• choose Provider as “ACT! OLE DB Provider for Reporting“, click Next
• choose a database file (e.g. *.PAD file), fill in User Name and Password if necessary
• Test Connection if you want, then click OK

You will now be presented with all the available tables. Choose one specific for reporting, or uncheck the “Connect to a specific table” option, which will allow you to go back and pick other tables using the same connection. Personally, if you are setting this up for automation, you will most likely have one table in mind. Click Next to choose a File Name under which to save this connection, add a description, and click Finish.

Now, here’s the fun part. You can leave the settings of the “Import Data” pop-up as-is, and it will import all the data from the table into a “table” grid in Excel, or choose to create a Pivot Table or Chart directly from the new connection, or Edit Query, limiting the subset of data imported into Excel. The table is pretty straight-forward, and the Pivot Table/Chart creation is outside the scope of this blog (but is inevitably is the end result - so if you aren’t familiar with them, get familiar!) I want to finish by going over the Edit Query option, as that will help limit the extra/excessive data you do not need in a report.

In 2003, on the Import Data pop-up, click the Edit Query button. In 2007, on the Import Data pop-up, click Properties, and then the Definition tab. Change the Command Type to SQL, and change the Command Text to:

SELECT "Contact Contact", "Contact Company", "Contact ID/Status"
FROM "ACT11Demo"."dbo"."VRP_CONTACT"

This limits the data to just Contact, Company, and ID/Status info. Of course, from there we can create a Pivot Table if needed, perform any analysis functions, or whatever else is desired.

Hope this help you better utilize the power of Excel accessing and reporting on ACT! data…

p.s. In Excel 2003, if you go to Data->Import External Data->Import Data again, your saved connection will be there. In Excel 2007, it’s Data->Existing Connections that will bring up the saved connections.

Whitepaper - Relationship Between Social Networking & Business

For those of you who Blog, Twitter, Facebook, and more, a very interesting Whitepaper from Forrester about how social networking is impacting business in a positive way.

Definitely a must read for those of us who are in the technology services, but the some of the main points relate to all avenues of online B2B marketing.

Click to download:
Social Technographics of Business Buyers.pdf