Start Collecting Your Dealership’s Data So You Can Understand & Improve Your Store’s Results

A post to describe how you can maximize your store’s performance.


What I am about to explain to you will change your life if you are at all frustrated with your store’s performance?

It may sound strange but usually performance issues arise from a lack of feedback to the workgroups contained within your operations. I use the term workgroups because there are numerous groups of employees in your dealership that need feedback to perform optimally. These groups of employees (workgroups) include:

  • An Individual
  • A Team – (usually front end sales teams but this can include techs and other employees).
  • A Department
  • A Store
  • Same stores – a workgroup important for comparing Year over Year (YoY) results.
  • A Division – ( a group of stores by franchise or geographical location)
  • A Region – ( usually geographical)
  • A Country (if you have stores in different countries)

How would you like to improve the profitability of all your store or stores right now?

We will need to use your data from your DMS (Dealership Management System) extracted properly for analysis and presented in a way that ranks your workgroups. The following SharePoint Online interactive report has a tab that ranks the performance of two workgroups; stores and individual F&I Managers:


The premier business data presentation platform, SharePoint Online, is free with the Enterprise Office 365 subscriptions. With SharePoint Online you can control who is able to see each individual report based on their logon. Those who don’t have permission will not even see the report link on the site.

Feedback is extremely important if you want your employees to improve. If your employees don’t get feedback they will either:

  • Get the data on their own or ask for it or,
  • Just maintain the status quo (under-performing) or,
  • In all cases, leave your employ.

The sad part is, the good ones leave (because they are not recognized) along with the not-so-good (because they are not motivated).

We need to take advantage of a person’s or workgroup’s natural competitive instincts!

Critical to reaping the performance improvement that feedback can create is ranking the workgroups. Feedback can also be fine using objectives but that doesn’t get the competitive juices flowing. We have to rank them!

In my experience, when you rank workgroups they all rise, even the bottom individual or workgroup. That’s because everyone wants (on some level) to do good.

There are three steps to making this happen:

  1. Automate the Collection of the Data.
  2. Train your CFO and Controllers on how to use the latest tools to analyze the data efficiently
  3. Train your CFO and Controllers on how to create reports with the latest tools and publish them to SharePoint and Office365.

I have a lot of experience with CDK, InfinitiNet and DealerTrack and so that is what I will stick with here. You can access your data on these platforms but I am not so sure about Reynolds anymore.

Step 1:

To automate the collection of your data all you really need is an ODBC connection, some knowledge of the DMS data model and a subscription to Microsoft Azure. You would then ask someone like me to write a PowerShell script to run on your server to extract the data from the DMS at least daily and move it to a SQL Azure database (or on-premise).

You can skip this step if you are on CDK and you have signed up for their wonderful MDA (Managed Data Access) or DDA (Dealer Data Access) product. Those products serve up daily refreshes of data on a PostgreSQL database on your CDK server already.

Step 2:

CFOs and Controllers and even advanced Business Managers are the ones to analyze the data and create the reports. They need training and I will be setting up some courses here in Las Vegas to train them. I don’t advocate having IT people do this because they just don’t have the knowledge of how these numbers are generated; where they come from and how they inter-relate. So they need training in:

  • The DMS data model i.e., where the data resides and what transactions put it there.
  • How to use Excel 2013 and 2016 with PowerPivot and PowerQuery and other tools to create interactive reports so they refresh automatically when the underlying data is updated. You don’t want a set of gorgeous reports that require your people to manually update them. We will let SharePoint Online handle that.

Step 3:

Because this data is confidential and because we need to make sure it is correct this step also falls on the responsibility of the financial side of the business. I recommend this be handled by the CFO, Controller and/or Business Manager. In larger dealer groups the CFO or Controller can hire someone with this expertise but they need to understand car dealership transactions and accounting. There are two kinds of reporting in the dealership ( I teach all the competencies needed for both):

  1.  Permanent periodic reports. These include rankings, Over-Age Inventory,  Heat sheets, A/R Aging, CIT, Incentives, Upcoming Lease Terminations Reporting etc. These reports need to be built once and automated by reporting in their own SharePoint page with automatic refresh with proper permissions.
  2. Ad-Hoc reports. These are reports that you need to create one time or on-the-fly to do analytics prior to making a major business decision. In either case properly trained financial people need to prepare them.

I will be sending out an email announcing a seminar in the spring to lay this out for Dealers, CFOs and Controllers in detail. If you want to be on the list you can email me at jdonnelly at jdonnelly dot com or give me a call at 702.498.8777

PowerShell Cmdlets for Auto Dealerships

I am getting more and more calls for help with CDK Global, DealerTrack, and InfinitiNet data extraction and I want to post now about how I can help best:

  • I will write you a PowerShell cmdlet that will exactly meet your needs

I find the Microsoft PowerShell platform the best for folks that are not technically adept to extract data when, where and as often as they want. It helps to know how to run a PowerShell cmdlet but that is not critical. I can get you up and running with a 15 minute phone call.

The beauty of PowerShell is that I can write you a cmdlet to do whatever it is you want done. We then import those into your PowerShell ISE and then you can run it with different parameters as often as you want. The output you get back can be opened directly in Excel (if that is how you want it) by PowerShell. I can even write the cmdlet so that it emails the information around or in many other ways.

The reason I like PowerShell is because running the report is easy for those who don’t know PowerShell and yet I can make PowerShell do pretty much anything you need.

I will write your custom cmdlet quickly and for a fixed fee because, not only am I adept at writing PowerShell cmdlets and modules, but I know DMS data models very well.

I would estimate that most extraction cmdlets will be quoted in the $150 to $350 range but if you want a cmdlet to not only extract the data but send email reports or marketing it could (depending on how elaborate) cost upwards of $1,000 – $2,000.

Monthly support for the first 3 cmdlets is free and add $100 a month for each batch of 3 thereafter.

Email me now for a quote at jdonnelly at jdonnelly.com


The “Poor Man’s” CDK GLOBAL (ADP) Business Intelligence

If you’re like me, a multi-dealership executive manager, you need data. You count on your sources in the dealership to provide you data. Most often your only source is the accounting office; but let’s face it, most dealership accounting offices are not the GAO. So we get it where we can.

In the single point auto dealership there is usually not the expertise to access, analyze and/or vet the data, let alone build a data warehouse. However, in larger stores and in multi-point operations we sometimes see either Data Analysts working for the Controller or Group Controller, or the Controllers themselves staying on top of advances in business intelligence data and reporting. They do this to do their jobs better and to support executive managers such as the UVD, FID, FOD, CFO, COO, & CEO.

Very rarely will one find a Business Manager (automobile dealership title for Chief Accountant) that has acceptable Microsoft Excel skills let alone the ability to use new self-service BI tools such as PowerPivot. That is not a criticism because there are many a CPAs, Group Controllers and Controllers that I have found in the same condition. It just has not been a priority.

In fact, there are many executive managers that don’t see a need. They don’t see a need to understand completely what goes on in their organizations and to ferret out opportunities to improve or build competitive advantage. Untrained and inexperienced executive managers often respond to needs to assess situations through data analysis by asserting that such methods would be “too complicated”. In other words, if the dumbest guy in the room (them or another) can’t understand it, they don’t want to talk about it. Rather than bring their people up, they bring the organization down.

I have always said with enthusiasm, when the public companies get involved in our business, then the relatively unsophisticated dealership will use these methods or suffer a disadvantage. Those days are here.

I have chosen to work primarily with the CDK Global DMS (dealership management system) and currently limit my involvement to dealerships who use it. CDK Global has a very extensive data model and access to it has become, of late, very robust. There are very smart people at CDK Global who I have had the pleasure of working with over the years including Jerry Farnsworth, Bill Schwab, Mark Roman, Gary Thompson, and Mark Kryzwonos and many more who know the value of this data and are getting access in the hands of people who need it. They know and understand the importance of the dealer getting open access to his data  unlike other DMS providers in the arena.

Not many dealerships or dealership groups have the budget for business intelligence (I want to meet those who do) and I am not going to argue for one. This post is about assigning a project to your Controller to setup the “Poor Man’s” BI solution. Basically, you want your Controller to be able to use interactive BI reporting to answer all the operational questions in all departments. And, besides the CDK Global data model, you also want him or her to start keeping all the manually collected data in your new data warehouse as well.

If your dealership(s) are not using Office 365, SharePoint or SharePoint Online, I suggest you email me (see below). I am a Microsoft Partner and I can show how such services can solve most of your IT issues and improve the operating results (profits) of your stores. SharePoint is perfect for reporting information (ranking etc) to keep competition alive in the dealership. I will show you how.

What I am about to tell you could always be done with CDK Global (ADP) (ever since the late 1980s). However, a “Flat File” repository was never very accessable using Microsoft Excel, nor was the end product very presentable. Microsoft has included with Excel 2013 three key new tools; PowerQuery (for the accessability), PowerPivot (for the analysis) and PowerView (for the presentability). Now your people can handle data in flat files as deftly as they can a full fledged database.

The idea is to create a share folder with all these flat files whether CDK Global originated or manually gathered and use this “repository” as a data source to create a data warehouse in the Excel data model. So as an executive manager here is what you want to do:

  1. Tell your Controller to setup a “Flat File” repository of all the major tables available in CDK Global’s data model. This includes (but is not limited to):
    1. All Car Deal Sales Data (F&I)
    2. All Vehicle Inventory Data
    3. All Service Sales Data
    4. All Parts Sales Data
    5. All Parts Inventory Data
    6. On Demand Check Data
    7. All General Ledger Transaction Data
    8. Payroll Data
  2. They can do this using CDK Global’s Report Generator Extract (RPX) function and setting up unattended downloads nightly (hourly?) to keep the data current. These downloaded files (csv) are kept in the repository.
  3. Tell your Controller to create Excel Workbooks using PowerQuery as the ETL (extract, transform and load) agent as it specifically accepts flat files as data sources. They then create a PowerQuery query to ETL each of the flat files downloaded into the Excel data model in an Excel workbook that will be your data warehouse. (Note: They will need a new PC with 32 GB of RAM, an i7 processor with a large SSD and a large hard drive)
  4. Tell your Controller then to join these tables that are now in the Excel data model so that they are no longer isolated “Flat Files” but rather tables that have relationships in your new Excel based data warehouse (tabular model).
  5. Tell your Controller then to do the same ETL using PowerQuery (in the same workbook) with manually collected data (e.g., daily sales logs) and create relationships to incorporate that information in your new Excel based dealership data warehouse.
  6. Tell your Controller to start exploring the data warehouse and getting familiar with the relationships so they can be your go to for business intelligence.

If you need more information, don’t hesitate to contact me at jdonnelly at jdonnelly.com (turn that into an email address).

Generating ADP (CDK Global) Accounting Schedules in Excel PowerPivot

ADP (CDK Global) Technical

Some background

Last year when I was evaluating the new PostgresQL data server for ADP, I asked them after at about the 6 month mark if they had a way to query the appropriate General Ledger tables such that we could analyze accounting schedules in Excel. In particular I was very intent on utilizing new Business Intelligence add-in, PowerPivot. Sadly, there was not a one among the dev team that was an accountant and they showed little enthusiasm. I challenged them to write a SQL query that could bring into a PowerPivot data model what I needed to review 28 dealership’s accounting schedules (to make sure the books are clean you see) in one day.

I am a CPA by education (UCLA Accounting), experience (practicing California Automotive CPA for years) and training (keeping up with technology). I knew early on that to be effective in financial…

View original post 2,187 more words

ADP (CDK Global) Expertise – Your Team, Making You More Money

ADP (CDK Global) has recently announced a new data server product available to their customer base. The product is currently called Managed Data Access (MDA).

I was considering writing a series of blog posts about this exciting new offering on my main site here but then I thought that this rather extensive opportunity needs a place of its own.

I am sure you will decide to take advantage of this offering and when you do ADP (CDK Global) will perform an initial full ETL (extract, transform and load) of your dealership’s data into a PostgresQL relational database server. ADP will then keep the database current with at least daily differential updates.

It’s true that some dealership groups have been doing their own ETLs into a SQL Server database for many years utilizing the ADP DAP program. DAP (Data Access Program) basically offered a way for dealerships to access APIs (application programming interfaces) to pull data. The DAP APIs do not lend themselves to object relational mapping and hence only the simplest data extractions were possible without some programming effort. These groups that have been doing the ETLs to SQL Server have had to have some pretty sharp Database Admins on staff to build the relational data model. Attempting this process was worth it though so these dealership groups could write queries and perform the joins necessary to get relevant actionable data.

The power of the MDA product though is in the connectivity of powerful data analysis and presentation software to your ADP data, namely Microsoft Excel. Beginning with Excel 2010 and continuing with Excel 2013, Microsoft has made Excel into a premier self-service data analysis (aka business intelligence) platform. Microsoft has done so with the advent of a few key free Excel add-ins namely:

  • Power Query – Makes adding data to Excel simple from just about anywhere.
  • PowerPivot – Makes joining data from disparate sources (or the same source) effortless for truly actionable data analysis.
  • PowerView – Makes data analysis beautiful and unbelievably easy with effortless slicing, dicing and drill-down capability.

With a little help from us in the initial setup, a dealership manager (whether it be the Dealer, the GM or a Department Manager or all of the above), will have access through Excel to a level of understanding of their operation heretofore unknown in the car business.

We will post here those projects that have an immediate, profound effect on the dealership’s or dealership group’s growth and/or profitability, based on sound principles, utilizing ADP (CDK Global) Data and Microsoft Excel. Simple projects that make a big difference.

In the mean time, give us a call if you have any questions.