Short Stories

Dashboarding for 2 CRM systems’ data combined

Are you frustrated by your inability to get the information and insight you need about the performance of your sales pipeline?

I worked with a client that had two different CRM systems in place…one from their core business, and one from a recent acquisition. They needed to see a unified view of their sales pipeline now, not some time in the future when the two systems might be integrated. And they wanted to incorporate their own, specific measures of performance into a flexible, dashboarding and self-service business intelligence solution. Enter Power BI.

Using Power BI’s data prep functionality, we joined Client, Lead and Opportunity data from a Salesforce CRM System with Client, Lead and Opportunity data from a Microsoft Dynamics CRM system, giving them one, unified view of their sales pipeline.

Using Power BI’s data modeling functionality, we incorporate data from other systems and create custom “measures” of sums, counts, averages and ratios that are the most meaningful to their organization and sales processes without having to make expensive and complex changes to both CRM systems.

Finally, using Power BI’s cloud based, collaborative dashboarding capabilities puts modern, interactive data visualizations into the hands of the people who need to have better information to make better decisions that lead to better outcomes and higher performance.

Automated Data Prep with Excel “Get & Transform” (aka Power Query)

If you or your peers spend multiple hours every month copying and pasting together new and updated data in the same way over and over again using Excel, you might be pleasantly surprised to learn that Excel has free features that can automate that work for you, and improve the quality of your work.

For example, every week the State of Colorado provides, on a subscription basis, a text file that lists new business entities formed in the prior week, by virtue of their submission of appropriate paperwork to the Secretary of State. If you are able to “glue” these weekly updates together into a single table of data, you can use that data as a potential source of leads. This lead source could be very productive if you are a B2B business that offers services every new business is going to need, e.g. digital marketing, legal, IT, etc.

With the built in “Get & Transform” data features of Excel (aka Power Query), you can automate the steps needed to A) clean up each week’s data and then B) combine it with all previous data. I do this for a handful of clients, and I literally spend 5 minutes twice a month providing this update. I automate all the steps using Excel, so I don’t spend 30 or 40 minutes on this task and make errors that would affect its quality and usability. I just drop the new weekly files into a folder on my hard drive with the existing weekly files and run the automated process I’ve created in Excel to create a nice, clean and complete table in an Excel workbook for my clients to use as a leads list.

Automated Data Prep with Alteryx Designer

When you know the data you need to answer a question lives in separate systems and was never really meant to be combined together, you can fight through the fog and do a lot to bring that data together with Alteryx Designer.

I have a client that wanted to reduce its total cost of software. I suggested we start with a picture of current state, and offered a framework for gathering and connecting relevant data describing labor, non-labor, direct and indirect costs to answer the question “how much do we spend on these software titles today, and where can we find future savings”? Pulling together data from multiple sources via Excel and CSV files and automating that process with Alteryx Designer allowed us to see, in one place, all significant cost components associated with their software titles.

In this instance, to understand and take action to reduce the total cost of software, I suggested we incorporate amortization, annual maintenance and allocated labor costs associated with helpdesk and change activity. Using Alteryx Designer, we effectively created a “Desktop Data Warehouse” with an “MDM light” implementation of a master “Software Titles” table to join all data sources together into one picture of cost with many related dimensions to analyze. This led to the discovery of significant expenditure that flew under their radar, associated with a software title having low unit cost but large installed cost when the relevant labor component of cost and number of installations was incorporated into the analysis.

Power BI with Google Analytics

If you are struggling to make sense and get value from your Google Analytics data, you may want to check out what Power BI dashboards can for you, at no cost.

I’ve used Google Analytics for a while now, to understand the behavior of visitors to my website and what content is drawing the most traffic. I was not …”overly enthused”… with the out of box user Google Analytics interface for reporting on what’s going on. While there are many ways to improve this experience with a variety of different tools, I turned to Power BI. Power BI has a connector to Google Analytics, and lets you import the tables of data you are interested in. You can see, in one place, what you want to see with interactive data visualization components that help you understand what content is working and what is not for visitors to your website.

You can read more in my blog post “Power BI + Google Analytics = Dashboard WOW”, which includes a couple of screen shots of the quick dashboard I built for myself.

Spatial Analytics for Best Donors

If you suspect that location and distance are a significant factor in your ability to attract new donors to support your non-profit organization, how can you find the boundary of your effective geographic reach to test and validate your hunch?

In doing a “Best Donor” analysis for a non-profit client, one of the factors we wanted to understand was the distance from the donor’s address to the address of the non-profit headquarters location (“HQ”). We wanted to see if there was a strong predictive relationship between a donor’s distance from HQ to how frequently and how much they give. We also wanted to assess whether or not an effective geographic perimeter existed for productive donors. Using freely available geocoding tools to create lat/long coordinates for each donor address and HQ, the Alteryx Distance Tool was used to calculate distance in miles between donor address and headquarters location for all donors. With that information, we could see if any pattern existed between our identified “Best Donors” and a particular radial distance where the percentage of “Best Donors” falls off to a point where continuing investments in new donor acquisition require a rethinking of associated investments.