Originally posted by Tiana Antul:
Most of the time you'll know which type of chart best suits your data and what it is that you're trying to convey. For those times when you're not so sure, here's a quick reference guide from the book "Good Charts" by Scott Berinato.
For when you're making comparisons in data: Bars, Bumps, Lines, Slopes, and Small multiples
For when you're illustrating distributions: Alluvials, Bubbles, Histograms, Sankeys, Scatter Plots
For when you're showing data composition: Pie charts, Stacked areas, Stacked bars, Treemaps, and Units
And for when you want to diagram relationships, illustrate space and networks etc.: Flow charts, Maps, Hierarchies, 2X2s, and Network diagrams.
Whether you’ve been doing crime analysis for one year or fifteen years, chances are that at some point in your career you’ve seen the intentional skewing of crime statistics, or perhaps more commonly, the flawed and incorrect interpretation of crime statistics by well-meaning people who simply don’t understand them.
Well one Police Chief in Texarkana, Texas is firing back. Watch this video of Police Chief Shiner responding intelligently to some misrepresented data about his City at a City Council meeting this past Monday. He makes some great points including:
While he doesn’t mention the use of crime analysis specifically, the Department seems to be doing some great work in the areas of problem-oriented policing, community policing, and involving the public in its police work. Chief Shiner also seems to understand a thing or two about crime statistics and the responsible interpretation and representation of those statistics.
When I first started working at my Department our Crime Analysis Unit ran a regular report called Unit Measures, which was intended to measure the performance of each Operations shift. It had been a report staple for some time before I began working there, but as years passed it eventually faded away into the black hole of report oblivion.
Looking back, it’s easy to see why. It provided basic stats on how many incidents took place on each shift, how many arrests were made on each shift, etc. Like many other reports it was probably really well-received when it was new, and then Crime Analysis dutifully continued to run it on some agreed upon schedule for years, not realizing until long after the fact that interest in it had faded if not disappeared entirely, and people had long stopped looking at it. It had become nothing more than a time-wasting perfunctory task.
The report, despite good intentions, had problems from the start. Its content had a very narrow focus. Aggregate incident and arrest numbers do very little to measure performance or showcase the actual work and successes of a police organization. Moreover, normal human activity patterns (work, play, sleep, repeat) caused natural fluctuations in incident and arrest volumes across the three shifts which had the effect of pitting each Operations shift against one another. The busiest shift would boast that they handled the lion’s share of the work, tacitly implying that the other shifts had it easy and didn’t work as hard.
Years later I now find myself asking the important question of how performance should be measured. Shortly after the New Year our Operations Captains were asked to develop a report on what they had done in the 2015 calendar year. There was no real discussion or consensus about what the report should include so each shift Captain began forging their own path based on what they thought was important. Some of them came to me asking for specific information. Namely they wanted to know the number of incidents and the number of arrests made by each shift. Then during a conversation with one official, the old Unit Measures report came up, and he asked me a question that got me thinking critically about how we measure things. Regarding the old Unit Measures report, he simply asked, “Yeah, but what did that actually tell us?”. The answer was even simpler. Nothing. That old report told us absolutely nothing.
I’d like to use this as an opportunity to re-invent the Unit Measures report from the ground up. To create a new report which serves as a canvas or platform from which to truly showcase our Agency’s achievements and accomplishments and reflect the wide-ranging activities that our Officers engage in. I started doing research and quickly realized that the rich data I need for such a report is not the type of data that I’m personally in a position to provide. What I found and learned is that all of the usual indicators used by Police Departments to measure performance (reductions in crime, number of cases investigated/cleared, response times, arrests made, etc.) reveal absolutely nothing about whether or not an agency is policing intelligently, using proper methods, or having a positive impact.
So if traditional indicators are wholly insufficient at measuring performance, then which indicators would tell the story and do it justice? An NIJ paper on the topic cites a work by Herman Goldstein in which he explores assessing performance in seven dimensions:
Whether these are dimensions you happen to agree on or not, I think most would agree that these serve as far better indicators of performance than aggregate crime statistics. But what metrics can we, and should we, use to measure along these dimensions? This is the question that needs to be answered if we’re to develop a valuable performance-based analysis of police work. I encourage you to think about this and comment below with your thoughts on the topic.
Are you familiar with the Armed Career Criminal Law? Essentially the Armed Career Criminal Law is designed to target offenders who are known for gun-related violent crimes and/or serious drug offenses. The law allows stricter, lengthier sentences to be imposed once these offenders pass a certain threshold for these particular types of convictions. You can read more about the details of the Armed Career Criminal Law here: https://malegislature.gov/Laws/GeneralLaws/PartIV/TitleI/Chapter269/Section10G
Because we know it’s a relatively small “few” who account for the majority of these types of offenses, this is a great tool for Courts to use to keep these types of offenders off of our streets for greater periods of time. If used judiciously (pun intended) it can effectuate real long-term reductions in gun violence and drug related crimes.
But what can the analyst do? Clearly you’re not going to BOP every known offender in your jurisdiction and hand tally their convictions to see who might be on the edge of reaching enough convictions to qualify. And unless you have the capacity to electronically query your Courthouse’s records for case dispositions (you might have better luck finding a magical Unicorn), then you’re probably wondering how you can help your agency when it comes to identifying serious offenders who might be ideal for future prosecution under ACCL.
While we might not be able to query conviction data, we can do the next best thing which is to use Police Data to identify those whose arrest charges point to the possibility of there being enough qualifying convictions to move forward with trying someone under ACCL for future qualifying offenses (you know… assuming they haven’t turned their lives around and are now living a crime-free life). This way we can at least narrow our focus and come up with a list of offenders to have on our radar should they commit another ACCL qualifying crime.
First, you’ll want to create a collection of all the applicable charges which you will use in your query. I’m still in the process of working with my Commanders to finalize the list, but to start I suggest being more inclusive than exclusive. You can always fine-tune your list of charges later once you have the query in working order. So go ahead and gather your gun-related charges as well as your drug offenses, mainly those pertaining to the possession, manufacture, and distribution of controlled substances.
Next create a query that fetches all the people with arrests involving these charges and tally them up. At minimum we’re looking for two qualifying violent crimes, 2 qualifying drug offenses, or any combination thereof that adds up to two but which arise from separate incidences. The list that results will be a good starting point. You can now get input from Officers as to who on that list your Town or City would benefit most from being put away for a longer period of time the next time they commit a similar offense. You can also sort these by each person’s most recent activity date and number of recent incident involvements to flesh out the ones who are most active right now. The more you dive in to your data, the finer you will be able to tune the list to a manageable number of offenders who might be exactly the types of candidates for whom the Armed Career Criminal Law was intended. With the blessing of your superior, start running directed BOPs (or have an Intern do it) and see if these individuals have the number of convictions needed to qualify for prosecution under ACCL. Once you've finalized your list using conviction data, these are people your Officers will want to keep on their radar, particularly your violent crime Detectives and Vice Officers. And as an analyst, you’ll have helped your Department keep its commitment to reducing gun violence and drug crimes by providing a very specific focus that can effectuate real long term reductions.
Originally posted by MACA15:
The recent attacks in Paris are a good reminder that we now live in a world where mass murder for nearly any number of reasons is possible in nearly any number of places.
Crime Analysts can play an active role in planning for and disrupting such attacks. By sharing information and identifying trends and patterns a good analyst can shed light on things that might otherwise be missed in normal police operations. By identifying critical infrastructure and soft targets within a community a good analyst can also make recommendations that make their communities less vulnerable.
Beyond these incidents of violence an analyst can also be very helpful in helping their agency and their community be better prepared for all hazards. Whether it is a terror attack, an active shooter, a chemical spill, a tornado or a blizzard a crime analyst has the tools and training that are easily transferrable into skills required for identifying vulnerabilities, developing useful response plans and sharing information that can be used to disrupt issues before they happen, mitigate the damage from incidents that occur and aid in a faster recovery after an incident has happened.
The analysts role can basically be broken down into what they can do before, during and after a crisis.
Become an expert at networking and intelligence sharing – you should have contacts and be receiving information from your nearest Fusion Center(s) – in Massachusetts that would be the Boston Regional Intelligence Center (BRIC) and the Massachusetts State Police Fusion Center, you should also do the same with your nearest HIDTA and RISS. You should also have access to the HSIN and LEEP – these resources are free.
For information on terrorist organizations and training check out SLATT and the Homeland Security Digital Library HSDL
You can also increase your knowledge and training with free FEMA training as well as Ready.Gov and you don't need to make your own mistakes when you can learn lessons from other incidents at the Lessons Learned information Sharing Program LLIS
Also let your officers know about the Nationwide Suspicious Activity Reporting Initiative SAR
A good analyst can also create maps showing vulnerable targets, resources, staging areas and evacuation routes.
Perhaps the best resource you can share with your community on what to do in an active shooter situation is the Run, Hide, Fight video from DHS. There are also a large number of free materials here
There is a free tool from MEMA (Massachusetts Emergency Management System) called the Web EOC (Emergency Operations Center) that you should be familiar with. MEMA resources are here
Updated power outage maps can be found online here
You should be skilled with using social media during a disaster. There is free training here and here and here
You may also be called upon to use your skills to assist investigators in gathering information during rapidly unfolding investigations. If your agency has not thought about how they will use you before a disaster you should be part of the planning process. You need to know what everyone in your agency can do and they need to know what you can do. Unfortunately, a lot of crime analysts are under-utilized in day-to-day operations but this can be even more the case during man made and natural disasters.
After a disaster or terror incident the goal is recovery. This is where maps and lists of resources are very helpful. Finally, even if you aren't directly involved at the scene of an incident you may find yourself having difficulty if members of your community or agency were hurt or killed. There is no shame in asking for help and despite the tough working environment many crime analysts find themselves in you can't help your agency and your community if you do not take care of yourself. When these incidents run around the clock over several days you need to remember to eat and rest.
Good preparation is the key to surviving these incidents and if you have done everything you can before anything ever happens you will have no regrets.
MACA has a lot of experts in a lot of areas and you should use our resources to help you prepare. Feel free to reach out to your fellow MACA members on the MACA-L to share ideas on how we can all better prepare for the worst. Let's hope we never need to use these skills and resources.
– Lieutenant Glen Mills – MACA President
Recently several people have inquired about analyzing the amount of time their officers spend on calls. I’m personally not aware of any established standards for response times, but having this data, or at least being able to get it quickly, might serve a variety of useful purposes to your agency.
The first thing you’ll need to do is make sure that your date and time fields are combined. Some RMS’s already combine their dates and times while others parse them out. If yours are parsed out, you can combine these fields right in your query by using an expression like the following:
CallRecieved_DateTime: [CallRecieved_Date] & “ “ & [CallRecieved_Time]
Repeat this for any date/time fields you plan to use, for example Dispatch date/time, Arrival date/time, Clear date/time, naming the new fields whatever you would like.
Now, to calculate the difference between any of these date/time fields, simply use the following function which calculates the difference between two dates and times. I like to see the difference in minutes because it makes the most sense for police response time data:
Response Time in Minutes: DateDiff(“n”,[ Dispatch_Datetime],[Arrival_Datetime])
Now the fun begins. For instance, you can calculate the “total time spent on calls” using arrival and clear times. Of course this doesn’t necessarily account for time officers spend writing reports after they clear a call, etc. But it will give you a rough sense of how much time is spent at each call.
Here are some other questions to contemplate…
Do response times get longer when your agency’s compliment is down? Can you use this information to make a public safety case for more officers when your compliment is low?
Which types of calls consume the most time from officers?
How do response times vary by call priority? *You can use a crosstab query to determine this.
Is there any correlation between call priority, response time, and cruiser accidents?
What other uses can you think of for this type of analysis?
So you’re back from the 2015 MACA conference. No doubt you learned some valuable skills, had fun, and returned to work refreshed and inspired… that little nerd spark deep inside rekindled by a presentation you saw or an idea that was born of a late night conversation in the hospitality suite. Coffee in hand you settle back into that oh-so cozy office chair and gear up for a week of catching up on all your work and putting a dent in that inbox that filled up with 48,926 emails.
It’s tempting. I know, I know. “But I was gone for a week and I have so many reports to read through and so much coding to do”, you say. “I need to respond to these emails asap”, you protest. It seems we put a lot of pressure on ourselves to get caught up. But trust me here. If you jump right back into it, you might quickly snuff out that spark you found at the conference, and there’s also a good chance that a year from now you might find yourself dusting off that notepad filled with inspiring conference notes that you never put to use, and what's worse, now you don’t even remember what half of them were about. Please, don’t let that happen. Instead, act on that inspiration while it’s still dancing inside, and check out my 6 step post-conference guide.
Show your gratitude! Show your gratitude to your employer. Even if you went to the conference on your own dime, your employer still allowed you to go. There was work that didn’t get done. There were requests that came in that didn’t get addressed right away. And if it’s anything like my agency, there were a few copy machines that didn’t get unjammed, a coffee maker that didn't get cleaned and there's an official somewhere in the building still trying to figure out how to expand a collumn in Excel (we sure do wear lots of hats, don’t we?). So take the time to send a thank you email and/or to say thank you in person to those in your chain of command who made the conference a possibility for you. Employers love a show of gratitude. It’s also a great way to underscore the benefits of sending you to future trainings and to plant the seed for next year’s conference (“Hey, look what I learned how to do! I’m going to start working on this for our next [insert your agency’s Compstat meeting title here].”). And let’s be honest- above all else, saying thank you is simply the kind and decent thing to do.
Share what you learned. Chances are your department didn’t send a team of analysts, police and command staff to MACA. Nope. They sent you. The onus is on you to come back and share what you learned with your colleagues, or if you’re a “Unit of one”, demonstrate what you’ve learned to your boss(es). Don't tell them about all the great stuff you learned. Show them. There are so many benefits of this not only to you personally and professionally, but also to your agency and ultimately to your community. So talk it up and start putting it all into action.
Contact speakers. Was there a session you particularly liked and you want to personally reach out to the speaker? Do you have some lingering questions about a topic? Or maybe you’re back to work and find that you’re running into some road blocks as you try to implement something you learned. Remember that handy contact list you were given on your first day when you came in and completed conference registration? This is what that’s for. Use it.
Create an Action Item List. This one sort of speaks for itself. Look over all your notes and scribbles. Get them organized. Make a list of new things you want to try and hang it up at your desk somewhere where it will stare you in the face and you can’t avoid seeing it. Prioritize them if you’d like. Alphabetize them. Translate them into binary code. Whatever you do, get them on paper in some sort of organized fashion and keep them some place highly visible.
Contact the people you met. A lot of great networking is done at the MACA conference. At sessions, between sessions, at dinner, at Trivia, in the hospitality suite. Drop a line to say, “It was nice to meet you”. Also, did you make promises to send someone some SQL code, a file, to get them someone’s contact information, or to help them get something up and running. Make good on those promises.
Prepare for the future. But next year's conference is a year from now, you say. Yes, 'tis true. But immediately post conference is the perfect time to start preparing for the future. Do you want to attend or possibly even present at next year’s conference? Start planning now. Do you want to get CLEA certified? Attending the conference gained you a point towards being eligible to sit for the exam. Assess whether you have enough points to sit for the exam. Are you already CLEA certified? Start a document that includes your conference-related activities that might count towards your recertification. And of course, keep the conference momentum going with MACA’s monthly training meetings. Whether it's at the next monthly meeting or next year's conference, we hope you enjoyed the conference and we hope to see you again soon!
I thought that this would be an interesting topic to write about as it lines up nicely with next month's meeting presentation by HIDTA on Fentanyl-laced heroin. In recent months there has been a lot of interest surrounding the quickly escalating heroin epidemic in Massachusetts. I personally have fielded recent requests for heroin overdose data from within my Department, from the Dept. of Public Health, and from the US Attorney General's Office.
This past Monday I attended a meeting in Boston at the US Attorney General's Office to discuss the current state of the issue and what data, if any, is available in order to shed light on the scope of the problem. The meeting was attended by several other major cities including Springfield and Boston PDs, Mass. State Police, the Department of Public Health, Drug Enforcement Administration, the Medical Examiner's Office, and several representatives from the US Attorney General's Office.
Generally speaking, law enforcement recognized similar limitations across the board as far as being able to provide real-time accurate statistics on heroin overdoses. Namely, overdoses are often reported as medical calls, for which reports are generally not taken by police, and when the incident is known to be an overdose, the specific drug involved might or might not be mentioned. Other agencies in attendance reported a variety of other problems that prevented them from having accurate statistics as well.
While I am limited by the data as far as being able to query heroin overdoses specifically, I am able to query incidents reported as overdoses (not to a drug-specific level). I expected to see an upward trend, but what I found amazed me. Here are the highlights:
Stuck in a rut? So many of the tasks we perform are done on a daily or other routine basis that it's easy to find yourself doing the same thing day in and day out. And while that might get you by, try to never fall victim to adopting the "good enough for government work" adage; working yourself into a rut of mundane routine can result in stale products and an audience that quickly loses interest in your work. Instead, challenge yourself every week or two do something new that will keep things "fresh", keep your PD interested in the work that you do and impressed with your dedication to develop and hone your skills. This is not only an investment in your community and your Agency, but an important investment in yourself.
It won't take much to wow your audience. You need not spend weeks working on an intricate link analysis for a crime series that will be long over with by the time you finish charting it. Simply stay connected with the people in your agency and you'll discover endless ways to support them with celerity and ease. In my experience, many people don't know what it is that they want, that they can ask for it, or how to ask for it. Don't be afraid to take the initiative to do something that you weren't asked for and say, "Here, I thought this might be useful to you for your next meeting". People will be impressed with your initiative and professionalism, never mind the work that you've done. It will inspire *some* people to come up with ideas of their own and to ask you for more assistance. Building these bridges is what keeps analytical gears moving smoothly in your agency.
As a short example to this point, I used to work with a Detective Sergeant when I first started working as a crime analyst, providing statistical and analytical support on domestic violence cases. My responsibilities broadened over the years to include much more than just domestic cases, his specialized Unit eventually got absorbed into our Detective Bureau, and ultimately another analyst took over my former role. Several years went by without much contact between him and I other than a passing "hello" in the hall here and there (which can easily happen in a department this size). Recently I did some work on escalating offenders using arrests as an indicator, and decided to apply the same concept to domestic offenders using incident involvement as an indicator. This yielded a short list of about a dozen or so names of people who had had 3 consecutive years of increased domestic violence incident involvements as an arrestee/suspect. No one asked me for this, but I thought it might be useful.
I brought the list of "escalating offenders" up to this Detective Sergeant, who with appreciation took the list, looked it over, and then pulled a 3X5" index card out of his pocket (old school record keeping in Worcester dies hard with the veteran cops) that coincidentally had the name of one of the offenders on my list written on it… It was someone he was currently looking into for a domestic-related incident.
He then asks if we (an of course by "we" he meant "yours truly") can expand on the list and add any other violent history outside the domestic realm. "Yes", I reply. Then another detective walks in and the Detective Sergeant makes a copy of the list for him. Turns out this Detective attends a monthly meeting with other local agencies and groups to identify high risk/dangerous domestic offenders. He asks if we can find out which offenders are gang-involved. "Yes", I reply, with a grin. "Can we get a list of all the offenders who have prior firearm charges?". "Yes", I reply again, more than a little excited to head back downstairs and get to work building a few new queries. Today he emailed me and asked if we could incorporate attempted murder and stalking charges to the existing weapons data I pulled. "Yes", I reply, excited to have another query to build. And just like that, with a small amount of unsolicited thoughtfulness, the door of communication flung wide open, a conversation began, ideas started flowing and haven't stopped flowing, and new life was breathed into a topic.
Happy Friday to everyone!
It was wonderful to see such a great turnout to the Watertown meeting this month. A huge thank you to Lloyd Burke for what I thought was an outstanding presentation.
How outstanding was it? I went back to work and toiled and tried and erred and today I got my first batch file to run properly! He was right… it’s so easy a monkey could do it.
Once you’ve written your first successful batch file and gotten over the little bumps that you’re likely to run into along the way, you’re going to want to automate everything you do on a regular basis. I thought I’d share my experience and some of the mistakes I made (including one rather LARGE rookie one) to spare others the same headaches.
I started with a new report I was recently asked to provide to the Department of Public Health. They are analyzing trends in opiate use and were interested in getting weekly reports on all overdose incidents reported to our Police Department.
I started with a relatively simple select query to pull the data. Knowing that I intend to create a scheduled task that will run this report every Sunday for the previous week, I set the date parameters to “Between Date()-7 and Date()-1 + #11:59:59#”. I have to add the #11:59:59# because in our RMS the date field is a combined date and time. This ensures that I’m capturing everything that was reported from the previous Sunday all the way through midnight on following Saturday.
Next step? Macro time! I created a macro that would run the above-mentioned query and export the results to my local drive in Excel file format.
For the intents of creating a batch file that you will run by using Task Scheduler, you will want this Macro to automatically execute when Access is opened. There are two ways to do this (that I know of), but the easiest way is to simply name the Macro “AutoExec”. The name will bold face and the macro will automatically run whenever you open the database.
Lesson Learned #1: If you use this database for any other work, DO NOT use an AutoExec Macro to shut down Access when it finishes running. Rather, have the batch file shut down Access. I made this mistake and nearly had a panic attack the next time I went to open my database and it immediately closed on me because of this epic mistake. There’s really no way to delete or edit a macro in a database without being able to open the database and keep it open (not that I’m aware of anyway). Thank goodness for computer backups (I guess those IT people we complain about all the time actually do come in handy now and then! Phew!).
Okay, step 3. Write your batch file. For our intents and purposes, these really truly are easy to write. I promise you. I ran into one glitch where I was asking the batch to delete a file on my PC before running my macro so that Access could write over the existing file without having to ask an end user if it was okay. It took me a while, but I figured it out, and this is lesson #2:
Lesson Learned #2: Make sure that all your files are located in the same directory. This makes the batch file happy.smiley I keep my database, the notepad text file, batch file, and the Excel file that gets exported from Access, all in the same folder on my local drive.
The next problem I ran into was the batch file not being able to locate folders and files that I was trying to point to by using a directory. Solution?
Lesson Learned #3: When pointing to a file or folder in batch script, make sure that there are no spaces in the folder or file names. I always knew this to be good programming practice, but it honestly never caused me any issues until now, and apparently I got sloppy in my naming practices. Once I removed the spaces and tried running the batch file again, everything worked like a charm. And this stuff runs fast!
The next step was to create the scheduled task. I couldn’t see my task scheduler when I opened up my control panel so I had to do a search for it. Scheduling a task is kind of fool proof since you’re walked through every step. I told my computer to run the batch file every Sunday at noon. There was even an option for the task to run whether I was logged in or not. (This coming Monday I’ll find out if it ran as expected). With any luck the report will sitting on my computer waiting for me to email it off. If not, then I know the issue is with the scheduled task and I will trouble shoot from there.
The next and final step is to look for a way to include script in the batch file that will email this report automatically. My initial research points to SMTP, but most solutions involve using a 3rd party program, which isn’t an option for me because I don’t have Admin privileges and I can’t download software on my work PC.
So this is what I’ve done a la the Great and Powerful Lloyd Burke, and my missteps which hopefully help you to avoid the same mistakes that I made. Feel free to call or email me if you want help doing this. I can’t guarantee to know the answer but there’s no problem we can’t figure out a solution to. Please share your experiences too! This is great stuff and Lloyd is leading the automation revolution.
The Massachusetts Association of Crime Analysts
P.O. Box 6123, Chelsea, MA 02150