Wednesday, March 5, 2014

The Crime Analyst's Blog Has Been Retired

The Crime Analyst's Blog has been retired. I've had a great time blogging but after six years I am in need of a break.

Thanks for your interest over the years!

I am still posting crime and policing related stories on Twitter. You can follow them here.

Monday, March 3, 2014

Quick Regressions in Excel or How to Predict the Future the Easy Way

Data becomes information when it’s analyzed. Information becomes knowledge when it’s communicated.
I like to tell people who ask what a crime analyst does that my job is provide the information that people at my agency need to make good decisions.

This may be on a tactical level such as the information a detective needs to investigate a case he or she is working on, or it may be on a more strategic level such as to help my Chief plan for future staffing levels.

One scenario that often applies with that last example is making a prediction about the number of calls, crimes, accidents, etc. that will occur at some point in the future. For example, at the agency where I work we are faced with the challenge of being located in an area that is consistently one of the 10 fastest growing areas of the United States. When I started with my agency in 1991, our population was about 57,000. Now just over twenty years later we are over 130,000.

To keep up with this we try to maintain a consistent officer to population ratio. But as any police chief will tell you, hiring police officers is a long process. From the time you announce you’re hiring, to offering the test for police officer, background investigations, hiring boards, selection, police academy, etc. until your officer actually pins on his or her badge might take a year.

This doesn’t even cover the year it may take to get new police officer positions included in an upcoming fiscal budget. All told, it may take two years from the time you decide to add an officer position to the police budget until they actually hit the street.

One way to help your Chief plan for the future is to make a prediction about the metrics your agency uses to measure the amount of work. At my agency, we look at population served, calls for service, crime stats, etc. to try and understand the amount of work we do.

But just how do you make a prognostication about the future?

One way I’ve seen people make an estimate of a future year’s Calls for Service is to take the percentage increase or decrease from the previous year and apply that to a future year. In other words, if the difference between the number of Calls last year as compared to the year before was a 3% increase. Then just assume that you’ll have a similar difference this year and add 3% to last year’s numbers. Some people get a bit more sophisticated and average the difference for several previous years and then apply that difference to the current year.

But there is a better and easier way, that would be to use a statistical technique called a Regression.

Back when I was in junior high school I found out that math was not my strong suit. When I first started in law enforcement, one thing I was relieved to find out was there isn’t a significant amount of higher level math involved. I know that some of you are like me and when I mention the word “statistics” your eyes begin to glaze over. Don’t panic. This is going to be easy.

My go to tool for crime analysis is Microsoft Excel. You can do a tremendous amount of analysis in Excel. A crime analysis unit that had no software other than Microsoft Office can still be a very capable unit. Don’t let the fact that your agency may not have all the specialized crime analysis tools that a larger agency has discourage you. I am very fortunate that my agency has provided some really great tools. However, even with all these tools I spend more time in Excel than anything else except maybe my email client.

The first step is to get your data collected and entered into Excel. For the purpose of this post I am going to use Uniform Crime Reports Murder data from 1995-2012 to make a prediction about the numbers for 2013 which have not been released yet.

In this example I have arranged the data with one column for the Year and one column for the number of murders for that year. I find that the vertical columns are less unwieldy than putting it into rows but that is your choice.

Once you’ve got the data entered select the columns of data and on the Windows version of Excel you can hit the Insert Chart button on the ribbon. On the Mac version select the Insert menu the the Chart command. For chart type, select a line chart. Once you’ve done this you should have something like this:



Now that you’ve got your chart Select the Chart Layout menu and look for the Add Trendline command. Using the Mac version of Excel it looks like this (the Windows version is slightly different).



Now you have a couple of options; you can pick a Linear, Exponential or Moving Average trendline. For now let’s pick Linear as it’s probably the best one to start with. We’ll fine tune this in just a bit. We do want to get to Trendline Options. You can either select the Linear trendline and then right click on the trendline itself and select Trendline Options or you can just select Trendline Options from the very beginning. Once you get that look for a checkbox that says “Display R-squared value on chart”.

The R-squared value is the coefficient of correlation. It is a measurement of how closely the trendline fits the existing data points. The R-squared value is a decimal number. The closer that number is to 1, the closer the trendline fits the data and the more accurate a prediction will likely be. This R-squared number will help us choose which trendline to use. (If you need to you can click on the R-squared number to highlight it and drag it to another part of the chart to make it easier to see.)

In my example table, a Linear trendline gives an R-squared value of 0.52987. Not bad, but it could be better. If I change the trendline to Logarithmic I get an R-squared value of 0.73639. This is much better and should make for a pretty accurate forecast.

Since I selected the year 2013 when I created the chart, Excel automatically extended the trendline to 2013 even though the number of murders cell is empty. If you didn’t leave an empty cell and stopped at 2012 you can select the option “Forecast Forward __ Periods” and fill in the number of periods you want the trendline to forecast. In this case, one would extend the trendline to 2013.

Now to come up with an approximate number for our 2013 Murder forecast you can select the Gridlines option in Chart Layout to add both Major and Minor gridlines. I’ve also changed the color of the trendline and thickened it to make it easier to see.

The final chart looks like this:



Looking at the trendline we can see the Regression we added is forecasting just under 15,000 murders for 2013.

A couple of notes, the more past data your chart has, the more accurate your Regression will be forecasting forward. Also, shorter forecasts will be more accurate than one farther out. One or two periods is best. Trying to forecast more than about 4 or 5 periods could get problematic using this method.

Spend some time playing with the charts, trendlines and options to get comfortable. Then apply this to some real world data from your agency. What metric would you like to predict?

Monday, February 24, 2014

Eat, breathe, and sleep the 60 Steps

One of the most useful things a crime analyst can do for their department is to become a problem solver. What I mean is that crime analysts should be the first ones to identify crime problems in the community they serve. While being a clarion and sounding the alarm is important, a crime analyst becomes far more useful when they not only identify a crime problem, but they help shape the strategy to solve it. In fact, a whole policing methodology has developed from this idea. That methodology is called Problem Oriented Policing.

But just what is Problem Oriented Policing? Professor Herman Goldstein described it this way:
“Problem-oriented policing is an approach to policing in which discrete pieces of police business (each consisting of a cluster of similar incidents, whether crime or acts of disorder, that the police are expected to handle) are subject to microscopic examination (drawing on the especially honed skills of crime analysts and the accumulated experience of operating field personnel) in hopes that what is freshly learned about each problem will lead to discovering a new and more effective strategy for dealing with it. Problem-oriented policing places a high value on new responses that are preventive in nature, that are not dependent on the use of the criminal justice system, and that engage other public agencies, the community and the private sector when their involvement has the potential for significantly contributing to the reduction of the problem. Problem-oriented policing carries a commitment to implementing the new strategy, rigorously evaluating its effectiveness, and, subsequently, reporting the results in ways that will benefit other police agencies and that will ultimately contribute to building a body of knowledge that supports the further professionalization of the police.”
One thing I really want to accomplish with my posts this year is to provide guidance for those crime analysts that may have been placed in the job with little or no formal training. When I was planning the series I contacted a number of crime analysts about my idea. I was really shocked by the number of responses I got from crime analysts who said that they never received formal training when they started their career. I even had one that told me she was working in a clerical position in her department and one day her Chief walked in and told her she was now her agency’s crime analyst! While this isn’t the best way to bring a crime analyst on board, it is sometimes a reality.
It is my belief that becoming a problem solving crime analyst is one of the most important things an analyst can do to make a difference at the agency where they work. Identifying (scanning), analyzing, formulating a response and then assessing your effectiveness is the SARA model which is the heart of Problem Oriented Policing.
Ronald Clark and John Eck wrote a book for the University College London’s Jill Dando Institute of Crime Science. The book was titled Become a Problem Solving Crime Analyst in 55 Small Steps. This book was later revised and slightly expanded for US law enforcement as Crime Analysis for Problem Solvers in 60 Small Steps.
Regardless of which version you prefer, you really need to get one of these books. In the welcome or “Read This First” section of the book it has this statement:
“This 60-step manual assumes that you are an experienced analyst and that you are accustomed to providing the kind of information needed to support police operations.”
It then goes on to list a number skills that an “experienced” crime analyst has. If you’re a new crime analyst or haven’t mastered all (or any) that list of skills don’t let this warning scare you away. There is still a lot this book has to offer you. In fact, very few of those skills are needed to digest the 60 steps.
To show you how important I think the 60 Steps are to crime analysis, back in 2009 (and again in 2012) I went through each of the 60 Steps in a series of posts here on The Crime Analyst’s Blog. You can go through those posts here.
Here’s one of the best things about the 60 Steps. You can get the book free as download or even order a bound copy for free. There is also an online version. I believe that every crime analysis unit should have a copy of this book and every crime analyst should read it.
This brings up a related tip, as a crime analyst you need to set aside a regular part of your workweek for professional reading. Shut down the email client, turn down the ringer on the phone and shut your office door if you have to but spend time learning the craft of crime analysis. If you need something to read the Problem Oriented Policing Center’s website has plenty of great material to keep you busy and it’s all completely free.
What are you waiting for?

Monday, February 17, 2014

Crime Patterns, Crime Sprees, and Crime Series

One important job for crime analysts is reviewing crime reports that come into your agency. Every  morning one of the first things I do is review the reports submitted by our officers for the previous day. When I first started as a crime analyst, I was able to review every arrest report and every incident report that came in. Now that the community where I work has grown, the number of crime reports has grown too. However, while I don’t get the opportunity to read each and every report, I do hit all the important ones. 

When I review these reports I’m looking for a number of things. I am looking for reports that may be of concern to my Chief or our city government. I am looking for reports that may indicate an emerging crime threat to the community. Most importantly I am looking for reports that may be related to previous cases. Every report that you get is another piece of information that could help you clear that case. 

There are a number of ways that crimes can be related to each other. Understanding these relationships can help you determine the relevance of these subsequent cases and help you determine a strategy for using the information they contain. Multiple crime reports can document a crime pattern, a crime spree or a crime series. 

The International Association of Crime Analysts has a white paper that standardizes the definitions of these terms. 

“A crime pattern is a group of two or more crimes reported to or discovered by police that are unique because they meet each of the following conditions:

1. They share at least one commonality in the type of crime; behavior of the offenders or victims; characteristics of the offender(s), victims, or targets; property taken; or the locations of occurrence;
 2. There is no known relationship between victim(s) and offender(s) (i.e., stranger-on-stranger crime);
3. The shared commonalities make the set of crimes notable and distinct from other criminal activity occurring within the same general date range;
4. The criminal activity is typically of limited duration, ranging from weeks to months in length; and
5. The set of related crimes is treated as one unit of analysis and is addressed through focused police efforts and tactics.” 

One of the most dramatic types of crime patterns is the crime spree. These sprees are the types of crimes that often make the news. A criminal robs a store, then carjacks someone in the parking lot to obtain a getaway vehicle and soon gets into a vehicle pursuit with police, he crashes the car and then takes someone hostage.  Sprees are a group of crimes committed by the same actor or group over an extremely short period of time. Dramatic, but often one that isn’t terribly worrisome to a crime analyst with the exception of making sure that all the potential crimes and victims are identified so they can be handled appropriately. 

Another type of crime pattern has several different but related definitions. The are Hot Prey, Hot Product, Hot Spot, Hot Place and Hot Setting. They are related in that these crimes all have a similarity whether it is a type of victim, targeted product, proximity, a specific location or environment. Criminals that targets only immigrant families for home invasion robberies would be a Hot Prey. Thieves that target iPhones for theft is a Hot Product pattern. Burglars that targets homes in the same neighborhood is a Hot Spot. A Hot Place may a bar that has numerous assaults, vehicle burglaries in the parking lot and pickpocket thefts. And finally a Hot Setting may be robberies of convenience stores by different offenders. 

One of the most important things and analyst can do is to identify a crime series. These are similar crimes committed by the same offender or group of offenders as part of continuing series of events. What makes the identification of them so important is that if a crime series is identified and analyzed you can often times predict future events in these series with some degree of accuracy. Additionally, by putting all the pieces together from each crime in the series, you can gain a fuller picture of the offender responsible. Regardless of what type of crime pattern you identify, they are important to understand because they each lend themselves to specific tactics deal with them. 

What types of crime patterns have you identified in the reports that come into your agency?

Monday, February 10, 2014

Introduction to Pivot Tables

Every now and then you make a discovery that turns out to be mindblowingly wonderful. They don’t happen often but when they do, they are great. I made one of those discoveries one day shortly after I became a full time crime analyst.

As a beginning analyst I quickly found that while it was pretty easy to get data out of a database it was a whole lot harder to analyze these records and turn them into something meaningful. I would export a bunch of records out of our records management system and pull them into a spreadsheet. But then when it came to operations like counting records meeting certain criteria I’d resort to sorting records into categories and either physically counting rows or using the row numbers to determine the number of records meeting my criteria. There had to be a better way.

As it turns out, there was. 

I was playing around in Microsoft Excel, the spreadsheet application we use at my office. I became curious about the “Insert Pivot Table” command. So I hit the F1 or Help key and looked up the help file entry for Pivot Table. This is what it said:

“A PivotTable report is an interactive tool that combines and compares data. You can rotate its rows and columns to see different summaries of the source data and display the details for areas of interest. Use a PivotTable when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. Because a PivotTable is interactive, you can freely experiment with layout of the data to focus on specific details or calculate different summaries, such as counts or averages.”

That sounded exactly like the tool that could make my life easier when it comes to analyzing the data I extracted from our records management system. 

A Pivot Table helps you take a table full of records like this:



and turn it into something like this with just a few mouse clicks.



The real beauty of a Pivot Table lies in that last sentence from the help file description:
“Because a PivotTable is interactive, you can freely experiment with layout of the data to focus on specific details or calculate different summaries, such as counts or averages.”

Interactivity is what really gives a Pivot Table power. It’s pretty easy to create a report in an application like Crystal Reports that will spit out a count of the number of burglaries you had in a given month. But what if as you are analyzing the data you notice that a large number of those burglaries appear to be happening at apartment complexes. 

You can go back and filter out all the burglaries at different types of locations and then re-run the report one at a time or by using the interactivity of a Pivot Table you can drop the Location Type field into your Pivot Table and quickly parse out the numbers of burglaries at all different types of locations. A couple of clicks and you’re looking at a lot of different facets of your dataset. Don’t find the answer you’re looking for, add or remove different fields to look at the data another way. 

Want to know the day of the week frequencies of those apartment burglaries?  Throw the Day of Week field in the mix and within in the space of a few minutes you are determining what day is most common for apartment complex burglaries. 

There are a bunch of online tutorials on how to create and use Pivot Tables. Here’s a text based one from Microsoft. There are also You Tube videos and other websites that can teach you how to be really proficient with them. There is also an excellent book by Mark Stallo and Chris Bruce of the International Association of Crime Analysts called Better Policing with Microsoft Office 2007 that can help you wring the most out of Microsoft Office. It includes a section on Pivot Tables. 

What data could you understand better with a Pivot Table?

Monday, February 3, 2014

How to calculate crime rate

One question I get all the time is “What’s the crime rate?” However, most of the time when people ask that question they don’t really understand the question they are asking. When citizens call my office and ask “What’s the crime rate?” they are usually asking it in relation to a particular neighborhood. The community where I work is right next to a large military base so we have people moving in and out of the community on a regular basis. What they usually mean is “I want to know if the neighborhood where I am considering purchasing a house is safe?” The answer to that one is complex. They ask about crime rate because they hear the term used in the media and think it will tell them something about the community.

The strict definition of mathematical rate is: “is a ratio between two measurements with different units”. Calculating Rate is a way of normalizing between different units. Crime rate is the number of crimes that occur in a given population. Let’s look at it this way.

  • Community A has a population of 50,000. Last year they had 5 murders. 
  • Community B has a population of 5,000. Last year they had 2 murders. 

Which community had a bigger problem with murders last year? Calculating the crime rate can help us normalize the populations between these two very different sized towns.

Crime rate is normally expressed as the number of crimes per 100,000. In order to calculate this the formula would look like this:

(Number of Crimes / Population) x 100,000 = Crime Rate Per 100,000

Remember your order of operations. You need to do the part of the equation enclosed in parenthesis first. Now let’s look at our two communities and see what their murder rate is.

Community A

(5 / 50,000) x 100,000 = 10 murders per 100,000 population

Community B

(2 / 5,000) x 100,000 = 40 murders per 100,000 population

This is quite the difference. Community B’s murder rate is four times greater than Community A even though Community A had three more murders than Community B. Of course this brings up a whole other discussion on whether it’s fair to directly compare crime statistics in two different communities. The FBI has a warning on their Uniform Crime Reports site that goes into detail about why this isn’t a good practice. However, rates can also be used to compare a city against itself at different times to account for a population growth or decline.

For instance, the community where I work is consistently listed as one of the 10 fastest growing areas of the United States. When I started my law enforcement career in 1991 the population of Killeen, TX was around 57,000. Now, 23 years later the population is over 134,000. Given than kind of population growth it’s a valid question to ask if we are doing better or worse in our crime suppression efforts now than we did back then. But you don’t have to limit calculating rate to just crimes. You can use this to normalize traffic accidents, citations, arrests, calls for service or any other metric.

What metric could you understand better by calculating it’s rate?

Monday, January 27, 2014

Password management for crime analysts

For law enforcement agencies, the threat of unauthorized access to records management systems, criminal intelligence databases or other systems containing sensitive information is very real. Law enforcement systems are a very juicy target for hackers with a malicious intent. One of the ways these systems are protected from unauthorized access is through requiring passwords to log in to these systems.

With the growth of online services, we’ve all had an increase in the number of passwords that we need to keep up with. As a crime analyst, I have ended up with a staggering number of passwords to manage. Nearly every application or source of information I use is protected by a password. If this weren’t enough, in order to maintain compliance with the FBI’s Criminal Justice Information Services (CJIS) rules most of these systems require that passwords being changed on a regular basis and meet certain criteria for complexity. Because of this proliferation of password protected systems it becomes nearly impossible to remember all these complex passwords.

First, I want to discuss some password management practices that are critical to maintaining the security of these systems. These recommendations are based on a piece by computer security expert Bruce Schneier.


  • Don’t use simple or dictionary words for your password. Hackers have created tools that use word lists to try and guess passwords. Many of these tools use dictionaries in several languages as well as lists of passwords stolen from compromised previously sites. Some of these word lists contain millions of possible passwords. 
  • Don’t use passwords based on easily guessed personal information such as your children’s names, pet names, etc. 
  • Don’t reuse passwords. If your password is compromised one one site, then the unauthorized user will often try these credentials on other sites you use. If you reuse passwords you’ve just given them the keys to the kingdom. 
  • Make your passwords more complex by using more than just letters and numbers. Use special characters too and don’t repeat characters. Use the longest password the application will allow. Every additional character in a password increases the time and computing power needed to crack it. 


Probably the most important advice I can give you is to use a password manager to keep up with your passwords.

A password manager is an application used to securely create, store and manage a large number of logins and passwords. Many of them come with password generators that can create truly secure passwords. Others have features that can automatically enter these passwords into applications that use password logins. The real beauty in a password manager is that you just have to remember one password, the one that opens the password manager in order to keep up with hundreds of passwords. (Yes, I really have several hundred passwords to keep up with.) The password manager’s database is encrypted and if properly implemented can keep nearly anyone short of the NSA from obtaining the data contained therein.

There are a number of password managers out there. Two of my favorites are 1Password a paid commercial application and KeePass a free, open source application.

1Password got it’s start as a Mac OSX application. Not long after, they also created an iOS application for iPhone and iPad. They just recently launched a Windows version. The strong suit for 1Password is that if your devices are part of the Apple ecosystem, they seamlessly work together. For instance, 1Password on my MacBook syncs the database to my iPhone and iPad. If I make changes or add a password/login on one device, it will be synced to the others. It also integrates with the OSX web browser Safari to save new logins/passwords as well as automatically offering to enter them into sites you visit.

Additionally, 1Password will store encrypted notes, credit card numbers, driver’s license info, software license keys and WiFi authentication information in it’s database as well. This makes it a great choice for storing all those sensitive bits of information you need to keep track of, but need to keep private.

My other favorite is KeePass. KeePass got it’s start as an open source project for Windows OS. Lots of creative people volunteered their time and skills to create KeePass. Since the programming code is open for any to look at, many security and programming experts have vetted the code to ensure that the encryption behind it is secure and does not contain any “backdoors” to allow people to break the encryption.

Additionally, KeePass comes in both an installed version and a version that will run completely off a USB drive. The USB version is also helpful for those whose network security policy prevents them from installing software on their work machine. The best part is that it’s free. Since my work machines are all Windows OS, I use KeePass to keep up with passwords on these machines. There are also compatible versions for smartphones and Linux machines as well as many other devices.

There are certainly other password managers out there. I’ve even known people to create an encrypted spreadsheet or other document to keep up with them. If you’re going to go that route though, make sure you know what you’re doing in implementing the encryption.

Here’s a couple of tips from my workflow that might help. One, back up your password database religiously. Also, keep it accessible on several devices that way if one device is unavailable, you can still get to your passwords. I keep a version on my workstation and a backup on a USB drive.

How do you manage your passwords?

Monday, January 20, 2014

Understanding UCR

When I was in the Navy I learned about something called Dead Reckoning navigation. This was the way that ships navigated before the days of GPS. The way it works is that if you start from a known point and you accurately record the direction, speed and time you travel you can navigate to any point on the globe. The first, and most important thing is starting from a known point.

In order to know where you are going, you have to know where you have been. 

Crime stats are a lot like this. In order to know if your agency’s crime suppression efforts are effective, you have to know where you started from. The FBI’s Uniform Crime Reports program or UCR is an important tool in collecting crime statistics from police agencies across the country. These crime statistics help you to know where your agency’s crime suppression efforts started from and where they are going.

First, a little background is in order. 

Way back in 1927 the International Association of Chiefs of Police (IACP) began work on a program to collect crime statistics from law enforcement agencies across the United States. In the early 1930’s this program was taken over by the US government and responsibility was given to the FBI to collect and analyze these crime statistics. Now, nearly every law enforcement agency in the United States reports crime data to the UCR program. 

There is a number of different types of crime data collected by the UCR program. Crimes are broken up into two broad categories Part 1 crimes and Part 2 crimes. However, Part 1 data is the most commonly used crime data in the UCR program. The crimes counted as Part 1 crimes are:

  • Murder
  • Rape
  • Robbery
  • Aggravated Assault
  • Burglary
  • Larceny
  • Vehicle Theft
  • Arson

Murder, Rape, Robbery and Aggravated Assaults are further classified as Violent Crimes while the remainder of the list are classified as Non-Violent Crimes. Obviously these aren’t all types of crime but they are a pretty good representative. 

So just what are these UCR stats good for?

Some people (often lazy journalists) think crime stats are to be used to pillory police chiefs. Others, usually small publishing companies, believe that UCR data is to be complied into simplistic lists which are then used to declare a city as the “most dangerous” in a press release which also just happens to announce the release of their paid “report”. The latter examples has gotten so common that the FBI’s UCR Program now issues prominent warnings on their UCR publications warning against the practice. These types of comparisons rarely take into account the differences in a community that can affect crime. 

The prevalence of crime in a community has quite a number of factors such as population demographics, economic and education factors, residents’ historic attitudes about crime, funding and support of police, etc. Since no two cities are identical, it’s really not a valid assessment to compare UCR crime numbers directly. 

The real value in UCR stats goes back to my illustration about dead reckoning navigation: In order to know where you are going, you have to know where you have been. UCR crime stats allow an agency to track consistent metrics year to year to determine how their agency is doing in their mission of crime suppression. While it isn’t terribly useful to compare one jurisdiction’s UCR crime numbers to another, it is a valid technique to compare a jurisdiction’s performance to their numbers in previous years. 

It can also be helpful to look at other agencies around yours to see if they are seeing the same increases or decreases in crime categories. If burglary is up in your jurisdiction, has it also gone up in a neighboring community? If so, the problem may be more widespread than just your jurisdiction. This may point to the need for a collaborative effort between these two jurisdictions to develop a solution. You aren’t directly comparing your burglary numbers to theirs but are seeing if there are similarities in the trends at the two jurisdictions. 

It is also worth keeping this next point in mind about UCR crime stats or any type of performance measures. Don’t freak out if you have a bad month or year. Not to belabor the point with nautical illustrations but this may be helpful. 

When you steer a ship, there is what called a compass repeater in the pilothouse that displays the direction or heading the ship is going. The helmsman will usually be given orders to steer a course of a specific direction. However, if you’ve ever used a compass you know that the needle will often move around a bit. It also takes some time for the ship’s rudder to have an effect on turning the ship. If the helmsman moves the wheel every time the compass needle bounces or he doesn’t allow for the time it takes for the rudder to have an effect on the ship’s course he’ll have a heck of a time steering the desired course. By the time the ship starts turning the compass needle may have bounced around in another direction or he’ll have oversteered the ship as he kept adding rudder input trying to get the ship to respond. New helmsman are often cautioned against “chasing the needle”. 

Crime stats are like that too. If every time the crime numbers spike your agency changes course, you will never be effective. Instead the question to ask is: “Is this increase part of a larger trend or just an isolated incident?” If it is an isolated incident, the numbers will likely smooth out the next month. Police agencies like big ships take time to change direction so don’t chase the needle. 

UCR crime statistics aren’t the only a performance measure an agency should use but they are probably one of the most important. 

Monday, January 13, 2014

What makes a good BOLO?

One common task for crime analysts is the creation of BOLOs. BOLO is an acronym for Be On the LookOut and is a bulletin that may be distributed within an agency or to multiple law enforcement agencies. BOLOs are commonly used to highlight Wanted Persons, specific crimes or officer safety information. BOLOs usually contain sensitive law enforcement information and are not distributed to the public.



But what makes a good BOLO? In my 23 years in law enforcement I've seen some good BOLO's, some mediocre BOLOs and some that fall somewhere in between. 

Before we get to the elements that make a good BOLO it will probably be helpful that we talk a little about what makes good design. A well designed publication is attractive and easy to read. It draws the eye to important details. But most importantly good page design doesn’t get in the way of what you are trying to communicate. 

About twenty years ago I read a slim but outstanding book The Non-Designer’s Design Book by Robin Williams. No, not Robin Williams the comedian but the designer and author. Her slim volume opened my eyes to what makes good page design. In her book she says there are four basic principles of page design. They are:

  • Contrast
  • Repetition
  • Alignment
  • Proximity

Contrast draws our eyes to the page by reducing monotonous elements. Think book chapter titles that use different fonts, thickness or colors from the page text. Repetition unifies design elements across multiple pages. Kind of like a logo or header that is on every page of a document. Alignment and proximity help to group like items together and connect the different groups to make one coherent document. 

Since I have been both a reader and producer of law enforcement BOLOs and have been for quite some time I have some thoughts on what else makes a good BOLO. 

Make your BOLO visually appealing. Include a picture, map or other graphic to catch the reader’s attention. For a Wanted Person BOLO this is pretty easy, include a picture of your wanted person. If your BOLO is about a crime or crime series, a still from surveillance footage or crime scene photo will work. In an Officer Safety BOLO you can either use a suspect’s photo if the BOLO is about a person or a picture of the seized weapon or evidence. If all else fails and you have no other photos or graphics you can always fall back to a map of the area you’re talking about in your BOLO. 

It helps to learn some basic photo editing skills such as how to crop a photo to remove extraneous elements, or how to adjust brightness or contrast to make the photos or graphics look better. 

Keep your BOLOs to one page whenever possible. Keep in mind that your officers have limited room in their patrol cars and a whole lot of other crap to carry. Keeping a BOLO to a one pager makes it more likely that your officers are going to read it and keep them handy. They won’t read a novella length BOLO no matter how pretty it is. You may have to ruthlessly edit your BOLO to get it down to only the most essential facts to keep it to one page. To quote Sgt. Joe Friday: “Just the facts, ma’am.” This is perfectly OK because you’re going to include information on how they can contact you for more info. 

Think about branding. This means that you include a patch or badge logo that readily identifies the BOLO as coming from your agency. This will help officers from other agencies recognize the origin of the BOLO. You also need to include your contact information. In addition to giving readers a person to contact for more info, this will also help you to establish a reputation as a resource for your agency as well as for other agencies. 

You also need to include a statement or disclaimer that indicates how or if this document can be redistributed or protected. I know that seems like a no-brainer but I’ve seen law enforcement sensitive bulletins with sensitive criminal intelligence information posted on public social media sites or even forwarded to the media. 

What software you should use to create BOLOs? I avoid using word processing applications such as Microsoft Word for BOLOs. Instead I use a page layout application like Microsoft Publisher. The reason is that a page layout application gives you much greater control on where you can place layout elements on a page than a word processing application. If you don’t have publisher you can use an open source page layout application like Scribus. In a pinch you could use a drawing application like LibreOffice Draw. I’ve even seen folks with limited options use a presentation application like Microsoft PowerPoint. All of these types of applications will give you much greater control over the placement of page elements. 

Lastly, if you distribute these files electronically, use a standard file format such as PDF. Nearly any computer, tablet or smartphone can view PDF’s without having to have any additional software. If you send it in a proprietary format such as a Microsoft Publisher file, you run the risk of the recipients not being able to view your BOLO. 

The image above is a mock BOLO I created for this post using the open source software Scribus. It’s modeled after the actual BOLO template I use I normally use for BOLOs at my agency. Don’t be afraid to copy, modify or mash-up my design when you create your next BOLO. 

Monday, January 6, 2014

How to calculate an increase or decrease

Probably the biggest promise that crime analysis holds for a police agency is that it can make an agency more efficient at its mission of making the community you serve safer. Efficiency should be the new buzzword for police. If our methods of crime fighting aren’t efficient, then we are wasting the resources we have been entrusted with. I don’t know about your agency, but in the sleepy little burg where I work, we have precious few resources to waste.

But how do you know if your agency is being effective?

The beginning of the process to determine effectiveness is to measure certain crime or Call for Service metrics and ask yourself: Has this metric increased or decreased? Before I became a crime analyst I was a police officer. I signed up to catch bad guys and not “do math” (I hated math in school). That being said, calculating increase or decrease is pretty easy. It’s even easier if you setup a spreadsheet with the formula or use a tool like Wolfram Alpha.

Usually when police agencies are concerned with calculating an increase or decrease it’s comparing one time period to another. For instance an agency wants to know whether vehicle thefts this year have increased or decreased over last year. For an example we’re going to use a real life example of Uniform Crime Reports data for Vehicle Thefts from the agency where I work, Killeen, Texas. We’ll compare the number of reported vehicle thefts from 2011 to those in 2012.

2011 - 187
2012 - 192

The formula for calculating this looks like this:

(New Value - Old Value) / Old Value = Change

Don’t forget when you see a parentheses in a math equation to solve the operation inside the parentheses first. Once you’ve solved the whole equation, then multiply the result (Change) x 100  to turn that number into a Percentage of Increase or Decrease.

Plugging our Vehicle Theft numbers in that formula we get:

(192-187) / 187 = 0.0267

We then multiply 0.0267 x 100 to convert that number to a percentage and get an increase of 2.67%. If the Change result had been a negative number it would indicate a decrease from the previous year.

It gets even easier to calculate this with Wolfram Alpha. Go to the Wolfram Alpha website, and input this into the input box:

187, 192


Keep in mind that the order in which you input these numbers is VERY important. If you are wanting to compare the numbers for two time periods, you must put the Old Value first. Wolfram Alpha will give a whole list of calculations using these two numbers and one of them will be the result of our desired increase / decrease calculation.



It’s also pretty easy to do these calculations in a spreadsheet like Microsoft Excel. By the way, as a crime analyst, learning to use Excel and learning to use it proficiently is a must. It can make your life so much easier and is almost always my go-to tool. Here’s an example of a simple way to lay out a spreadsheet to make these calculations:



A caution is in order here. You must be very careful with comparing values that are zero. If you had 1 murder in 2011 and 0 murders in 2012 you had a -100% decrease. However, if you had 0 murders in 2011 and 1 in 2012 you did not have a 100% increase. Why is this?

Can you divide by zero? The answer is that you can’t. If you don’t believe me, plug the numbers into the formula and see for yourself. The proper way to represent this would be to indicate that the value is Not Calculable. If you input this into Excel it will give you a Divide by Zero error ( #DIV/0! ) in the results cell.