Going Spatial: Creating a Map of Prop 37 Votes

When charts bore you, create a map!

Spatial Analysis is increasingly importantAs I continue my sabbatical, one of the projects I am working on is earning a certification in Geographic Information Systems (GIS).  Why?  For much of my career I have been creating charts… charts showing revenue growth over time, charts showing sales rep performance, and charts showing the health of a SaaS startup.   After 15-years of charts, I felt it was time to explore another form of data visualization.

I enrolled in the GIS Specialization offered on Coursera and created by UC Davis.  I am all too familiar with data transformation, data management, and blending of data, so I was really curious how different a GIS would be compared to the likes of Salesforce.com of Tableau (which does offer mapping).

The Fundamentals of GIS course itself is much more about learning to use ArcGIS and ArcMap.  We did learn about projections, GIS best practices, what spatial analysis really is and how to open the ArcMap software.  Aside from learning the tool and file types, there really wasn’t much different from what I already knew as a data and insights analyst.

Take the final peer-graded assignment for Fundamentals of GIS as an example.  The course provides data including a counties data file defining counties in California.  The course also provides a second data file including voting precincts and the voting results for Prop 37.   The goal is to combine the two data files and create a normalized map showing the ratio of Yes votes to total votes. Seems simple enough?

It was fairly simple.  As with any data related project, the first thing you do is to download and validate the data.  Can you open the zip files?  Is the data there in its entirety? Once you know the data is usable, get to know the data.  Look at the metadata to see what fields are included and what they mean.  Since we have two files which need to be combined, we need to find a primary key to join them.

While it took me a few minutes to review the data, it took a bit longer to understand the connection between the two data sets.  It was clear that we needed to have a one to many join and a spatial join.  There are a few different ways to do this. I first decided to summarize the precinct data and output a table which showed the total votes per precinct.  I can then join this table to the Counties data as a one to one join.

Alternatively, you can join the two data sets using the Spatial Join Tool.  Instead of joining on a common key (I joined on County number), you can join them based on their proximity such as an intersect or contains.

Prop 37 Voter Map created with ArcGISOnce the data is ready to display on the map, you can use “Symbology” of the joined data layer to display a normalized ratio.  Showing absolute numbers of Yes votes does not really tell the whole story as some precincts and counties have greater populations. Normalize the Yes votes by calculating the ratio of Yes votes to total.  This produces the map we were looking for.  Once we add the required metadata, scale, etc, we can export it. (view my map online here)

What did I learn from taking this course?  Spatial analysis is a specialized field which does not differ too much from more traditional data analytics.  The course taught me the special files formats, terminology, and ArcGIS basics.  What is most interesting to me is this map could be made with other platforms like Tableau and PowerBI.  The only difference is the data must be manipulated outside the software (in Excel, maybe) and then visualized.

This brings up a great point.  In traditional analytics and business intelligence, you work with specialized tools which handle a specific part of data.  From the ETL (Talend or Kettle) to analysis (Excel or Python) to visualization (Tableau or Qlik), each segment of the data journey required different software.  Today, the lines are blending a bit.  Solutions like Alteryx combines ETL with analysis, but leaves a lot to be desired in terms of visualization.  Tableau is also able to connect to and blend a variety of data sources, but leaves some to be desired in analysis.

After taking this course I am left with a profound sense of how specialized GIS is. I can understand why it is well worth investing in, especially for geospatial analysis consisting of multiple data layers.  When you consider ArcGIS (or GIS in general) is capable of global level analysis, it takes your breathe away.

My eyes are open to how I can leverage GIS and merge it with my interest in History. Perhaps creating a historic spatial database which illustrates the speed at which Manifest Destiny occurred?  Maybe we can start with a map of Texas and how it was settled over time? Stay tuned…

Thinking Spatial

Thinking Spatial

Spatial analysis has come of ageAs time goes on, our world becomes more and more global.  We also capture more and more data as each day goes by.  Linking the location of this data with time and other attributes, can reveal very profound patterns; patterns at various scales like community to global.

We can answer numerous questions about a lot of different things using GIS software like ArcGIS.  Using the concept of data layers, we can start to analyze data in exponential ways.  We can go beyond statistics on a data table and evaluate changes over geographic space.  We can also use GIS to find the best locations and features with certain characteristics.

For example, Whole Foods uses many different data layers to identify the best locations for their store fronts.  They want the best location which has a population of 200,000 within 20-minutes. They also look for locations with at least 20,000 sq ft, a decent sized parking lot and ease of access along with highly visible (source).

Thinking spatial about some of my own interests, I have come up with two focus areas. The first being related to the “walkabout” I have been on over the past few years.  Where do I want to live as my forever place?  This GIS would take into account numerous data layers such as population, elevation, incomes, education, and access to parks and rivers. Using these data layers, and a few more, I can begin to scientifically hone down where I could settle down.

The second spatial project centers around my love of history.  I am currently reading a book about Red Cloud titled, “The Heart of Everything That Is.”  What piqued my interest was the impact of European Settlers had on the spatial and temporal changes in the new world.  With the arrival of settlers in the east, drove waves of Native Americans west as they fled.  But they fled with muskets, blades and disease.   As the book described this change, I was mesmerized trying to visualize this on a map and in the context of the time.  Throw in some explorers, desperadoes, and outlaws and you have quite a story. But I want to build an interactive story map to illustrate these profound changes.

To think spatial opens the mind, builds the curiosity and becomes a book of its own right. What ways can spatial analysis impact your life? Your curiosity?

 

X-Plane Logbook Stats for 2017

As an aviation geek and armchair pilot, I wanted to have some fun with Tableau Public and my X-Plane Logbooks.   Where have I flown the virtual skies in 2017?  The answer isn’t too shocking, but there are some interesting patterns.  X-Plane Logbook Tableau Data VisualizationCheck out the image below and then head over to the live workbook.

  • 90.7 flight hours with 73% flown in X-Plane 11
  • 57 unique aircraft flown across 178 flights
  • Top aircraft flown include the VSkyLabs Douglas DC-3, Carenado B200 XP11, and FlyJSim 727Adv (version 1 for xp10)
  • Most flights occurred during the day
  • KPAE and KBFI were the most flown airport pairs

Where will 2018 take me?  Not sure.  Perhaps getting out of the western US would be a start.  Maybe even a few international flights are in order.

And, if you need some help with visualizing your data, check out my Tableau page at Spiral Analytics.

 

Revisiting Triathlon Intelligence

triathlon intelligenceDuring my triathlon years, I was amazed with the impact data has on a training program.  GPS devices, wearables, and tracking apps seriously changed how triathletes viewed their training.   Rather than going by feel, triathletes could “see” their workouts with data visualizations.  Areas for improvement were quickly identified and brought to the front for full attention.

As technology continues to improve, our wearables get more complex and accurate, and triathlons become more competitive, we need a better way to digest our data. Very much as Tableau has created a better and more robust platform for visualizing and forecasting business data, this same functionality must come triathlon.

What is the real problem?  It is the same problem I tried to address with TrainingMetrix, combining all of a triathlete’s data into a single source to derive insights and forecast future workouts.  To this day, we still deal with separate databases and apps for our workouts and nutrition.  Companies like Garmin and MyFitnessPal have improved integration, bringing nutrition and workout data a tad closer. But, we are still missing the insights… the indicator of diet quality, the indication of over training, and the ability to see progress at the highest level.

This is where my dream of triathlon intelligence comes in.  Combining each data set not just for visualization, but combining the data set in a way which tells the future.  Perhaps I want the crystal ball of triathlon training…  nothing big.  lol

Where does this go from here?  It starts a new era in research and passion.  For myself re-entering triathlon training has renewed my search for the ultimate solution.  In future posts, we will explore some of the solutions on the market including what is good and what is bad.

The Math Every Sales Must Do

As a sales rep you need to deliver closed won deals to meet your quota.  As with all journeys to a goal, there is a hard, rough road and a superhighway, fast and smooth as a baby’s butt.  To earn your commission the most efficient way possible, wouldn’t you want to be on the superhighway? Of course!

The Math Every Sales Rep Must Do

Let me show you how to do some math to start you down your sales superhighway.  The key is to leverage data as much as possible along your journey.  To get started, you’ll need a few data points.  If you don’t have historical trends to use, an estimation is fine.  In fact, you might want to do the math a few times using different number so you understand the impact each variable might have.

Here’s what you need to get started:

  • Monthly, Quarterly, and Annual Quota
  • Average Deal Size
  • Sales Cycle
    • Ideally, Lead Create to Opp Close, but Opp Create to Opp Close can work for expansion reps
  • Win Rate / Close Ratio
    • Both Count of Opps and Value of Opps
  • Lead to Opp Conversion Rate

We will use these metrics and KPIs to calculate a few additional data points.  The first is translate our quota numbers to the number of deals we’ll need to close.  The second is to understand what size pipeline we’ll need to target to hit our number.  Finally, we’ll calculate how many quarters we need to project out and how much pipeline we need.

  1. The Deal Count

The first calculation is quite simple and uses quota and average deal size.  Simply divide the quota for the period by the average deal size and it will tell you how many deals you need to hit your number.  As a best practice, add 1 to the number you get:

(Quota for period / Average Deal Size ) + 1 = number of deals you need to hit your quota

Write these numbers down in a book or journal so you can refer back to them.  You may also want to use an Excel spreadsheet and keep track of the number of deals you need and which accounts will give you those deals.

2. What Size Pipeline Do I Need?

Once we know how many deals we need, we also need to know what size pipeline we need to close those deals.  This is where win rate (also known as Close Ratio) comes in.  You should have two win rate numbers, one based on  COUNT of opportunities and another based on DOLLAR VALUE of opportunities.

Depending on which you want to calculate, use the appropriate set for count of deals and quota.  The math is:

Count of Pipeline Size:  number of deals needed to hit quota +1  / win rate of count

Dollar value of pipeline needed:  quota for period +  Avg Deal Size / win rate of dollar value

Again, write these number down.  This is the size of the pipeline you will need to build to make sure you hit the quota number based on your historical win rate.

3. How Far Do You Plan Ahead?

You may be wondering why we haven’t used Sales Cycle yet.  While we aren’t going to use it in a calculation, we will use to see how far ahead we need to be planning. to hit our number.

Sales cycle can be calculated  a number of ways so be careful and understand what the number you have means.  For instance, many clients I have worked with in the past have used a sales cycle which measures Opportunity close age, i.e. Opp Close Date minus Opp Create Date. This is misleading if your business includes prospecting.  A true sales cycle uses either Lead/Contact create date or Account First Activity Date.    Make sure the number you are using a sales cycle which represents the true time frame you need to work your leads/contacts and close your opportunities.

quota period in days / sales cycle in days

If your sales cycle is 45 days, planning one quarter ahead is sufficient.  But if your sales cycle is 105 days, you must plan two quarters ahead.

It’s a Wrap

With these three pieces of math in mind, you are well on your to establishing the foundation for your superhighway to 100%.  Understanding what it takes to hit your quota number, how long and planning far enough ahead is huge and gives you a head start against your peers.  You may be amazed at how many reps don’t DO THE MATH.

 

Understanding Our Past: Support LIDAR Mapping at El Pilar

In the late 1990’s and early 2000’s, while I was attending University of California, Santa Barbara, I had the honor of working with Dr. Anabel Ford and her resilient crew on various projects surrounding the Maya site of El Pilar.   From archaeological excavation to mapping, to cutting trails, analyzing artifacts, and building predictive models, the time I spent on this project was phenomenal.

Recently posted on my Facebook page was a notification the Dr. Ford is undertaking a new project, mapping El Pilar with LIDAR to better understand Maya settlements beneath the thick rain forest canopy.  Please follow this link for more details.

Support the El Pilar LIDAR Mapping Project

They are currently seeking $2,700 in funding via Experiment.com, a crowdfunding platform for scientific research.  $2,700 is a bargain for the wealth of data and insight this team of researchers will acquire.  At 30% funded with 22 days left, let’s push it to 100% and beyond!

Cheers!

Top 5 Best Practices for Rolling Out Sales Rep Scorecards

Sales rep scorecards are that golden unicorn of any sales organization.  The scorecard is a compilation of Key Performance Indicators (KPIs) which are measured against thresholds.  In a rep scorecard, we see a visual interpretation of how a rep is doing for each of the KPIs. An example of which is below:

A Simple Sales Rep Scorecard with three KPIs

Sample Sales Rep Scorecard

Before I dive into best practices, a word on why not a lot of sales organizations have scorecards.  The primary reason is due to organizations struggling with data which best represents the business which makes it difficult for them to setup a KPI, let alone establish effective targets.   An understanding of the analytics continuum is also helpful for understanding the evolution of data practices which need to met prior to rolling out KPIs and Scorecards:

The Analytics Continuum: a blueprint for adoption

Top 5 Best Practices for Sales Rep Scorecards

  • Sales reps, Mangers, VPs, and CROs must all have agreement on the KPI definition, targets, and thresholds.  If one level of the KPI hierarchy is not on the same page as the others, there is very value in using the Scorecard to represent an ideal.
  • Targets and thresholds must be reasonable.  When rolling out KPIs, we often realize that actual performance is far from the corporate ideal. For instance, a Sales Cycle of 45 days is thought to be ideal, but the rep actual is north of 60 days.  Don’t hold this against them, consider rolling out a target of 55 and stepping the target down to 45 days within three quarters of launch.  Be kind to the reps and allow them to catch up.
  • Scorecards must be part of a larger sales communication strategy.  Rolling out a scorecard alone will have an impact on the organization, but the most impressive will happen if scorecards are a part of the larger communication strategy.  For instance, a weekly email can call out wins by reps, it should call out performance, and it needs to call out what needs to be done to hit the goal.  Scorecards are just one piece of the story in sales.
  • Scorecards need to be updated as the business evolves.  Scorecards can never be truly static, recurring reports.  Part of the role of your analytical team is maintain reports as the business changes and evolves.  Scorecards are no different.  From a subtle change of keeping thresholds and targets up to date, to swapping out KPIs for new ones, scorecards are a living animal and requires food to stay alive.
  •  Scorecards are a coaching opportunity, not a punishment tool.  While HR and managers will look at a scorecard and see a rep with all red for their KPIs, this doesn’t mean the rep needs to immediately be put on a performance improvement plan or, worse yet, fired.  Scorecards are coaching tool and enable the manager to work with the sales rep and ask questions like “why do you think your sales cycle is double the average?”  Work with the rep, train the rep, and allow the rep the chance to go for green.

As your team rolls out scorecards across the sales organization, keep these best practices in mind.  Be kind to your reps, get agreement on definition, use scorecards as part of a larger strategy, keep them updated, and use them as a coaching tool.