Ocean.io
Dragos PetriaDecember 20, 2019

CRM data analysis for Marketing and Sales

The data that you have in your CRM is a treasure trove of valuable information that can be used to define your sales and marketing focus. Most companies out there never use their CRM for more than keeping track of leads and pushing them from stage to stage in one pipeline or another.

In this article, I’ll go through two types of analysis that can give you actionable insight to drive better marketing, sales, and even product development. It requires you to roll up your sleeves and do some manual work, but it’s worth it. You can find a link to copy the Google Spreadsheet I’ve used for the calculations at the bottom of the article. I hope it helps!

Alternatively, you could use Ocean.io Premium and automate this whole process. Book a free demo with our team to see how we can help you identify the right audiences to focus on in your sales and marketing.

Before we dive in, I want to state that two things will become abundantly clear after reading this blog post. One is the fact that I am terrible at Excel & Google Spreadsheets. From the way my tables look to the method I have used to extract the data – I know, please don’t hold it against me. The second thing is that you are not using your CRM data to its fullest potential.

Let’s change that, shall we?

Using CRM segmentation to understand conversion rates per segment

Not all segments of your customers are created equal. If you analyze your transactional data on a segment basis, you will have a clear picture of which companies are worth focusing on based on how well they convert into paying customers and at which approximate currency value.

However, companies structure their CRM systems differently so we can’t give you a one-size-fits-all and this might be more work if your CRM is fairly unstructured.

Here’s how to get started:

1. Export your CRM data for 2019 (or whatever period of time you consider relevant) and structure it like this in a spreadsheet:

Account | Account manager | Segment | Won/Lost | Deal amount (if available in CRM)

2. Fill in the data. The “segment” column will be a manual effort most of the time. Write down the type of company – for example, “Marketing agency” or “IT development”, etc.

3. Calculate conversion rate as the percentage of deals won out of total deals per segment – so if you have 100 accounts that are marketing agencies, and you’ve won 25, you have a 25% conversion rate for that segment.

4. Compare segments in terms of conversion rate. If two segments are close in terms of conversion rate and you have deal amount, favor the segment with the higher deal amount.

Here’s a simplified example of a Pivot table calculating conversion rate per segment (link to copy the Sheet at the end of the article):


Here we’re taking a look at the value of accounts per segment:

Based on this, it becomes fairly clear that we should focus more on IT development companies, as we’re closing them at a much higher rate and at a higher amount.

5. Compare the conversion rate of each account manager per segment. You have a 60% conversion rate for IT development companies, but you have multiple account managers per segment, check their conversion rates. Looking at their conversion rates, we can see that Mark is converting 100% of IT development companies, whereas Sally is only converting 50%:


Moreover, Henry seems to be the only one closing Marketing agencies.

6. This gives you two actionable insights: try to get more leads corresponding to your best segments in terms of conversion rate and deal amount; assign leads to the account managers with the highest conversion rates on that segment.

All of this is done automatically in Ocean.io as we analyze transactional data based on our AI segmentation capabilities, meaning we can both spare you this manual analysis and we have more accurate segmentation than current industry standards. You can always book a demo if you want to see it in action!

Using lost accounts for product feature planning and reactivation

Not all companies do this but they all should. When you lose an account, make sure you get a reason for why the account didn’t convert with you. It’s usually about a feature that you’re missing or data not being accurate enough, or the price being too high. Write this down as a note or custom field in your CRM system.

You’ve paid for these leads in both time and money and it’s a shame for all of them to be lost. Having a reason for losing an account salvages some of that data, giving you actionable insights into why you lose accounts.

How to get started with this analysis:

1. Export all of the lost accounts over the past year into a spreadsheet with this format:

Account | Contact info | Reason | Deal amount (if available).


2. Calculate how many lost accounts there were for each reason. In this example, we have four accounts lost for lack of CRM integration and four for not having dashboards in the platform.


3. If available, instead of number of accounts, calculate the amount you could’ve won if these accounts were won. In our example, although we’ve had four accounts lost for each of the two reasons, looking at the value paints a different picture:



4. Two actionable insights arise:

I. Pass this info along to your product team to take into account when prioritizing feature development. They will have to take into account development-specific variables, such as time to develop, resources available, etc, but this serves as a valuable extra piece of data.

That being said, not taking other reasons into account, building a system for dashboards in the platform would yield more revenue.

II. As soon as you release new features for your product, you have a list of people to reach out to.

As mentioned earlier, you’ve paid for these leads and they were lost, but now you have a list of companies that would have converted if you had these features.

At this point, you can export all of the accounts’ contact info per “lost reason” and set up an email campaign and pick up the phone and start calling them, starting from the ones with the highest deal amount and working your way down.

You can also create a Custom Audience in LinkedIn Ads by creating a .csv file with company name and company domain and uploading it to LinkedIn. You can then target these lost accounts with a creative that specifically talks about that feature that you know is important for them.

Here’s a link to copy my spreadsheet to understand how to structure the Pivot tables, if you’re not very technical: CRM Analysis Template.

As I’ve already said, this type of calculation is not really optimal in spreadsheets. If you have a Data Scientist in your company, they can do this for you at a much larger scale in minutes. If you have a Business Intelligence department, ask them to run this report on a monthly basis to understand the impact of your changes.

Whenever you’re ready to take your CRM analysis to the next level, Ocean.io performs all of the above (and more!) automatically, with the added benefit that our industry classification is automated and extremely granular, and takes the size, technographic profile, web traffic, and a bunch of other metrics into account when segmenting.

Moreover, we use our AI and a huge database of companies to identify more companies like your best-performing segment, making them instantly available for your Sales and Marketing needs.

In the mood for sharing?