CRM/ILM Lead Tracking Reports vs Excel Spreadsheet Tracking Reports: Which do you rely on for accoun

[andwrig]

Just curious to hear opinions on solely relying on the lead management reports that your CRM/ILM provides vs manually tracking everything in an excel spreadsheet. Which one do you use or do you use both for check and balance purposes? If you use both, how often do you find that there are drastic differences.

If you are tracking manually, what are you tracking and why? How much time do you think you (or your BDC manager) spends tabulating the data on the manual report? Is it worth it?

 

[mattwatson81]

Just curious to hear opinions on solely relying on the lead management reports that your CRM/ILM provides vs manually tracking everything in an excel spreadsheet. Which one do you use or do you use both for check and balance purposes? If you use both, how often do you find that there are drastic differences.

If you are tracking manually, what are you tracking and why? How much time do you think you (or your BDC manager) spends tabulating the data on the manual report? Is it worth it?

Seems like every dealer has some weird spreadsheet in a unique format they keep track of. Usually some form of a sold log.

I think if you have a CRM that allows you to create custom reports you can probably get the exact fields you want and how you want them. As long as you can trust in the accuracy of the system you should be able to ditch Excel.

But a backup never hurts either!

 

[Alex Snyder]

As long as you can trust in the accuracy of the system you should be able to ditch Excel.

That's the key ingredient right there. Too often, it seems, a dealership technology is built with reports as an afterthought. Also, too often, the reports and technology are built by technologists who have a different way of looking at the world than the people in dealerships do. Either you end up with reports that don't add up (usually because one report says one thing while another report says something different) or you have reports that don't make sense to the user. I would say that most dealership technologies have reports that don't add up nor make sense to a car dealer.

To take it a step further there are so many different technologies a dealership uses. This is exponentially increased as you move into a dealer group environment because the OEM's haven't figured it out either. That was my world up until 6 months ago, and these are all the reasons why I was an Excel expert for 10 years before that.

Now that I'm sitting on the technology side I'm starting to understand the how's and why's of reporting failures and most of it stems from the original data structures the technology was built on. If it isn't flexible or encompassing enough then it isn't going to deliver innovative reporting in the future. This is also a big reason why you see stagnation with some technology vendors.

 

[Alex Jefferson]

I have to use a combination because of inadequacies in my CRM/ILM tool. Another factor involves your sales team, management team, and F&I team which can cause otherwise consistent and relevant data to be corrupted at some point during the sales process. This is something we continue refine. @Alex - You are total right...even if we refine it to the point where our data is 100% clean, the platform we sit on is highly inflexible and does not provide all the data we are looking for in reports.

 

[mattwatson81]

That's the key ingredient right there. Too often, it seems, a dealership technology is built with reports as an afterthought. Also, too often, the reports and technology are built by technologists who have a different way of looking at the world than the people in dealerships do. Either you end up with reports that don't add up (usually because one report says one thing while another report says something different) or you have reports that don't make sense to the user. I would say that most dealership technologies have reports that don't add up nor make sense to a car dealer.

To take it a step further there are so many different technologies a dealership uses. This is exponentially increased as you move into a dealer group environment because the OEM's haven't figured it out either. That was my world up until 6 months ago, and these are all the reasons why I was an Excel expert for 10 years before that.

Now that I'm sitting on the technology side I'm starting to understand the how's and why's of reporting failures and most of it stems from the original data structures the technology was built on. If it isn't flexible or encompassing enough then it isn't going to deliver innovative reporting in the future. This is also a big reason why you see stagnation with some technology vendors.

Structuring data for how the software needs it and how reporting needs it can be very different things indeed. We have done many projects where we convert data from one format to another for this vary reason.

For example, dealers want on one report data about their leads, showroom visits, appointments, response times, and more. It is impossible to get all of that data in one simple database query. It is even harder to do it and make it load very quickly across a 20 store group. Not to mention making sure the numbers are accurate! These are the types of challenges we battle against.

The amount of data we capture just continues to increase as well.... it just gets more and more complicated. We have invested a lot of time in to our reporting capabilities to be able to do all of this type of stuff. Then the servers can get overloaded from all of this number crunching. It is an ongoing battle for sure!

 

[Marc McGurren]

Me? I use both. I am a firm believer in you can’t fix what you don’t know and when I stepped into my role approximately 2 years ago my current CRM/ILM didn’t have what I wanted. I then made a monster spreadsheet (that is pretty freaking cool if you ask me) to track and figure out what I want to know.

I now have a CRM/ILM that is much more robust and can get me much better reports but the system we have in place doesn’t take too much time and it allows me to have a better “feel” of what is going on since I am manually inputting the data.

So why use both?

1) My current provider can’t get me exactly what I want. My excel spreadsheet can.
2) It’s my old school way of having my log. That’s what I know, trust, and love.
3) Flexibility – I can change things on the fly and don’t have to worry with my technology to work with me

My process might sound like it takes quite a bit of time but it actually doesn’t when done as each vehicle is sold. Here is our process.

Salesperson sells car – they get “Recap sheet” that tells me all about the deal. They make copy, write on the copy where and when the lead came from.

In my spread sheet I then write everything out on a traditional log. This is where knowing excel gets cool. Then to my right I have all my data that I want tabulated from my traditional log. This includes tabulating where the car was sold (we have 7 dealerships), was there a trade, what was the front, back, and net of the sale, along with lead source and date lead came in.

My fields that probably need clarification are "U" - that is was it a full or split deal by the salesperson? "SR" - This is to let me log what dealership I sold the vehicle in (ie. CN = Chevy New, TN = Toyota New, etc). "Comm" is commission on recap - I then can compare that to what they are actually getting paid after unit bonuses, etc. "H/B" = Hold Back.

Then once a week I pull my lead data from my CRM/ILM and input how many leads I have received from each lead provider along with how many leads each salesperson has.

By inputting those three things on a continual basis – the time it takes is very minimal and it allows me to keep my pulse on what is going on.

I then get all my data below:

Hope that helps…

 

[ajsalesman]

we use a CRM/ILM tool from arkona that works pretty well for what we need. As you say though, we haven't found any CRM that has every report or the flexibility/configurability that we need so we still track several things by hand.

 

[andwrig]

@Marc

That's a great job. Any CRM/DMS companies that read your thread entry should take notice and frankly be ashamed. I think Alex spoke to that issue in his thread post. Nonetheless, we spend thousands on CRM and DMS systems yet we are still forced to spend time manually tabulating data in excel for reporting/tracking purchases. I know you said it doesn't take you much time to compile this information, but if you added up the time and think about what you could be doing that would make you more money (i.e. phone skills training, etc) I think you could surmise that the lack of functionality in the reporting features of your DMS/CRM are actually costing you money or preventing you from making more.

I asked this question because, IMO, we are being asked to spend more money on technology in the interest of increased productivity, yet dealerships are still largely hampered by technology in many of the same ways we have been historically and reporting is a big one.

Thanks again for sharing your spreadsheets. If anyone else has any good spreadsheets that they use and are willing to share, please do.

 

[GrantG]

I came across this post from a few years ago. I am curious how the data analytics culture has evolved in today's dealerships. First off, I still use a combo of Excel and CRM data to get the info I want.

What do you do?

 

[Sarah Udelhofen]

I came across this post from a few years ago. I am curious how the data analytics culture has evolved in today's dealerships. First off, I still use a combo of Excel and CRM data to get the info I want.

What do you do?

I still track both, some manually and some through our CRM. It's really crazy though because I have experienced some CRM's that have awesome reports and data that I did trust very much.....but now I'm back to kicking it old school with Autobase and I've been having a really hard time with my reporting and tracking. I must admit though that even with a great CRM I still track some things manually (BDC reports are always my old school paper log that's color coordinated). I think it really depends on your CRM capabilities and how comfortable you are with them.