Microsoft Excel Tips & Tricks for CJA Cases: Pivot Tables for Financial Fraud Analysis

Featured

Posted on March 29, 2024 by joewanzala

By Alicia Penn and Joe Wanzala

This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.

Previous Post: Formatting Tables
Today’s Post: Pivot Tables for Financial Fraud Analysis

Introduction:

You might get a Microsoft Excel spreadsheet in your discovery. Things like bank records, call detail records, cell tower data, or tax documents are often produced in this format, as Excel (.XLSX) or .CSV files. This is good! The benefit to getting data in an Excel file, as opposed to a PDF, is that Excel will let you easily sort and filter data. For example, if you get telephone call logs in Excel format, you can easily sort the calls by date and time. You can filter that information to select specific telephone numbers and review just those calls quickly. This would be difficult to do with a PDF and would take much longer. [https://nlsblog.org/category/software-guides/excel/ ].

This blog post takes this one step further. The pivot tables function in Microsoft Excel lets you rotate and combine data otherwise confined to static columns. This allows you to manipulate existing data in order to see relationships that might not immediately be obvious in the spreadsheet. For example, if you have a spreadsheet of bank records in fraud case, you can use pivot tables to discover how much money is sent by each victim and how much money is transferred to a certain bank, by date. You can also use pivot tables to support more intricate conclusions—for example, that the government had no evidence about a particular defendant’s involvement in a fraud scheme before his arrest and subsequent search. This was a definitive issue in a real case and using pivot tables led to a successful Fourth Amendment challenge. Below we will first set out our fact pattern based on a real timeshare resale fraud scheme. Then we will show you how pivot tables can be used to understand data and answer questions.

Fact Pattern: Timeshare Resale Fraud Scheme

Our client is Ike Kando. He is indicted in a Belizian time share fraud conspiracy, along with over ten (10) codefendants. He is not the first name on the indictment but is further down—his role in the case appears to be that of a money mule. He receives checks made payable to him in the mail, deposits the checks into his own bank account, keeps a cut for himself, and sends the rest via Western Union transfer to a bank account in Belize.

Through viewing discovery, we piece together that the general scheme is this—one of the codefendants finds timeshare owners. He contacts them and offers them a deal too good to be true—that a buyer in Belize wants to buy their timeshare at a price high above market value. But! In order for the timeshare owner to take advantage of this amazing opportunity, they first have to send the Belize buyer a percentage of the purchase price to satisfy local Belize rules. The timeshare owner is told to send the money to one of several money mules used by the fraudster. The money mules deposit the checks into their own bank accounts and then wire the money to a bank controlled by the fraudsters, who really are in Belize. (Figure 1).

Relevant Timeline:

In August 2002 a fraudster contacts Victim 5 and tells them to send a check to Luke Brownson, one of the money mules. After he sends the check, Victim 5 becomes suspicious and contacts the FBI. DOJ investigates and subpoenas Brownson’s bank records.

In October 2002, Ike Kando is pulled over for a traffic violation. The local police detain him and search his car. They find suspicious bank records and take Ike Kando to the local station, where he is questioned. The next day, Ike Kando is also interviewed by an FBI agent. Law enforcement gets a search warrant for Ike Kando’s house and search it. There they find more documents that implicate him in the timeshare resale fraud scheme.

Because of the bank records and documents in Ike Kando’s car and house the FBI issue more subpoenas for the banking information of other money mules, Brownson and Ted Romanowski. Ike Kando and the other money mules are indicted.

Spreadsheet Introduction:

One piece of discovery we receive from the government is a spreadsheet created by the main case agent. It contains the bank records of the money mules in our case. Each column contains data related to different aspects of each transaction. For example, Row No. 14 tells us that on 8/22/2002, Victim 1, based in in Alabama, sent a check for $12,000 to Ike Kando in Sioux Falls, North Dakota, and Row No. 17 shows that on 8/24/2002, Ike Kando sent a wire transfer for that same amount to the bank account in Belize controlled by the fraudsters:

This spreadsheet, as is, does allow us to filter and analyze some information. We could, for example, find out which transactions are associated with certain money mules by filtering in Column E, the money mule Column and selecting only individual mules, such as Ike Kando. We could also add another filter in Colum C, the victim’s column, and further filter transactions that way. The limitation of only filtering on this spreadsheet is that the data stays tied to the columns and rows they are currently in.

Pivot Table Introduction:

Pivot tables allow you to move data out of its position in the original worksheet and create new tables using existing information. Using a pivot table on a different worksheet, you can select data relevant to your query and arrange it so you can see relationships that might not be immediately obvious on the original worksheet. You can also use other tools, such as slicers, timelines, charts, and dashboards to further filter, manipulate, and view data. The benefits to pivot tables include maintaining an original worksheet, creating and preserving different worksheets that correspond to different questions, and creating visual tools that can be used by other team members (who don’t need to understand the underlying tables themselves). Dashboards and pivot graphs based on filtered, rearranged information can also be used as demonstratives.

Video – Pivot Table Introduction

Questions of interest that cannot easily be answered by the spreadsheet, as is:

The answers to these questions could help us argue our client had a minor role, that their loss amount should be smaller, and that the evidence being used against them was the product of an unlawful search and thus should be suppressed. They are answers not easily answered by the spreadsheet as is, but with the use of pivot tables become clear and simple:

How much money was sent by each victim to the money mules, by date?
1. How much money was transferred by each money mule to the Bank of Belize, by date?
1. What did the government know about our client’s involvement in the fraud scheme prior to his arrest?
1. How much of the information obtained by the government about the fraud scheme was derivative of the subpoenas issued to our client’s bank after the arrest?

How to Answer Question 1: How much money was sent by each victim to the money mules, by date?

If you tried to answer this question only using the filter and sort functions of the existing spreadsheet, you could only calculate a single answer at a time. For example, you could filter by a specific victim, and then show their transactions with each of the money mules, and then sort by date. This might look like this, for money sent by Victim 1 to each of the money mules:

You could then run this kind of filter for each individual victim and save the results—essentially running six (6) different filters.

Or– you could answer this question more efficiently using a pivot table.

To create a pivot table, use the “insert” tab on the main menu, and select the entire table as the source of information. Use the default settings and create a pivot table in a new worksheet. The blank pivot table will look like this:

It is a new sheet in your Excel workbook. The columns from the spreadsheet are now free-floating pivot table fields. These can be rearranged from rows to columns and vice versa by moving the fields between the filters, rows, columns, and values areas.

We are interested in the money mules, so we can select that to go into the column area:

Because we want to see the relationship between the money mules and the victims, we put the victims data into the rows area:

The next piece of data we are interested in is the sum of the deposits related to the victims and money mules, so we drag the deposits column to the values area:

We also want to include date information, so let’s add that to the rows area:

Now, we can answer the question without further filters– “How much money was sent by each victim to the money mules, by date?” You can see, in this existing pivot table, that Victim 1 sent Ike Kando $12,000 in August, Luke Brownson $7,700 and Ted Romanowski $12,500 in September, etc. etc.

You can manipulate this data even further, making it simpler to read, using a type of filter called a “slicer.” A slicer lets you run one or more filters on the existing pivot table. For example, we can create a slicer to filter what we see on the worksheet by victim, money mule, or another field selected for the table.

To add a slicer, we go to the “insert” tab and select “slicer.” When the slicer dialog box pops up we select the victim and mule categories. This will create a mini-window for each category—victim in one and mule in the other:

By default it will show us all the data. We can toggle on and off the option to select individual or multiple fields. Here we de-select victims 1,2, 4, 6 and Blank in the victim slicer, and de-select Romanowski in the money mule slicer:

Now we can see only what we selected—the transactions between Romanowski and Victims 3 and 5, by date. By using the toggle and filter capabilities of the victim and money mule slicer, you can see how you can answer any questions about these relationships as broadly or narrowly as possible.

Pivot tables also allow us to use a timeline slicer to manipulate data by date. Go to the insert tab and select timeline. Select the date option and see how you can filter data by month:

Video – Money Sent by Victims

How to Answer Question 2: How much money was transferred by each money mule to the Bank of Belize, by date?

To answer this question, make a new pivot table worksheet using the data from the primary sheet. Change the information that goes into the pivot fields according to the question—because we are predominately concerned with the money mule data and with money moving to the Bank of Belize, by date, we can put the money mule data and wire transfer location in the rows section and the date data in the columns section. The withdrawals go into the values section.

To isolate the data of wire transfers to Belize, make a slicer for wire transfer location and then selected only Belize, eliminating from view the data with a blank in the wire transfer location field.

We can now easily see, from this very basic pivot table, the answer to our questions. Ike Kando transferred $12,000 to the Bank of Belize on August 24, $14,400 on August 27, etc. etc.

To make the pivot table more comprehensive, add more fields into the report. To track how much money was transferred by each money mule to the bank of Belize and when, and which victim was the source of that money, put the money mule data and months data in the row section, and the victim data in the column section. Make slicers for the money mules and victims and a timeline to filter by date. Insert a pivot chart, leading to a worksheet that looks like this:

A benefit to creating a more comprehensive worksheet with slicers, charts, and pivot tables is that you can then use the pivot tables, with their pre-filtered data, to make dashboards. Dashboards allow you to combine data already isolated in different worksheets, which means you can view pre-filtered data to answer different questions simultaneously.

Video – Transfers by Money Mule

How to Answer Question 3: What did the government know about our client’s involvement in the fraud scheme prior to his arrest?

How to Answer Question 4: How much of the information obtained by the government about the fraud scheme was derivative of the subpoenas issued to our client’s bank after the arrest?

To answer these questions, we will create a dashboard.

Dashboards

To create a dashboard, start with a blank worksheet within the same Excel file. Copy over each chart, the slicers, and timelines from the two worksheets we just created. Since the slicers and timelines are just filters, only one set of the slicers and timelines needs to be copied. It does not matter which of the two worksheets the slicers are copied from. The graphical information in the Dashboard depends on the data that has already been processed using the pivot tables within each worksheet. However, because we have only copied over one set of slicers and filters, we need to make sure that the single set of slicers and timelines are linked to the data in each of the two worksheets. If we don’t link the slicers to both worksheets, only one data set will respond.

To connect the slicers to the other worksheet so that we get responsiveness from both charts, we right click on the slicer and go to “report connections.” We can see here that the data from the other worksheet is referenced but not connected. Check off the other worksheet, so that the slicer is accessing that worksheet as well. When we return to the dashboard all the slicers or filters should be responsive across all the data on the dashboard. Do this for all the slicers and timelines on the new worksheet.

Video – Creating a Dashboard

Once the dashboard is created utilizing elements from the various worksheets, we can run broader queries across all our data. Furthermore, the dashboard can be used by other members of the case team as a reporting tool. Even if they are not familiar with building or manipulating the underlying Pivot Tables, other team members can use dashboards to easily analyze and gain insights into the data. Dashboards can also be useful during team or client meetings, and dashboard screenshots can be used as possible exhibits or demonstratives for motions, trials, or hearings.

Timeline Filters

To answer questions three and four, we will use the two timeline filters. The one entitled “Date,” tracks the dates certain transactions occurred. The one below it entitled ” Date Government Obtained Info,” tracks the dates when the government received information based on subpoenas they issued.

Using the “date” timeline, we can filter by selecting one month, or more than one month by using our shift key. As we do that the visual representation of the data in charts will revise accordingly.

We know from our case timeline that our client was arrested in October of 2002, and we can see by filtering our dashboard for Ike Kando, that he had received money from several victims in August and September and during that same time period he had also wired money to Belize.

However, the question is what did the government know and when did it obtain that information? For insight we can use our “Date Government Obtained Info” timeline. When we filter for the month of July, we see that the government had obtained no information about any aspect of the fraud scheme in July. When we filter for August we still get nothing on our dashboard. We know that the government received a tip from one of the victims in August, however, the dashboard is still blank for that month because they received no banking information based that tip off until September. When we filter for the month of September, we see that is when they received Luke Brownson’s bank records with information about his involvement in the scheme. However, they still had no information about our client, Ike Kando. It is only when we get to October that we see that the government has obtained information about Ike Kando’s involvement in the scheme.

We can therefore assert that the government did not know about our client’s involvement in the fraud scheme before the traffic stop in October and that all information the government obtained about our client’s involvement in the scheme was derivative of the documents found during the illegal traffic stop. We had a client involved in a similar fact pattern as the case we used in this hypothetical. In that case, because we could show that the case against our client was all derivative from the illegal traffic stop’s poisonous tree fruits, we ended up with a favorable negotiated agreement for our client.

Video – Using the Dashboard to Answer Questions 3 & 4

Conclusion

If you have made it through this entire blog post and the videos and run into trouble replicating our results or with your own pivot tables and dashboards, please contact us! Excel is a powerful tool but it is also easy to make a misstep that can cause your worksheets to not work as you intend. We have tried with this post and videos to give starting instructions paired with an actual case scenario, but we did not include every single step and decision here. We are happy to look at your work with you and troubleshoot, and hope that you find pivot tables as helpful as we do.

Acrobat Training Guide – Text Recognition

Featured

Posted on January 18, 2024 by joewanzala

Editor’s note: this is an update on the Acrobat Training Videos – Text Recognition video post. A related post is Three types of PDFs.

Introduction

This is a brief guide on the text recognition feature in Adobe Acrobat¹. OCR, which stands for Optical Character Recognition – is a process which adds an invisible text layer to scanned paper documents or screenshots to help make them text searchable. While OCR can be very helpful in terms of search, it is not perfect. The computer is interpreting pictures of letters and characters in documents and attempting to turn them into text. Sometimes, those translations are incorrect (Figures 1 and 2).

The quality of the OCR text depends on many factors including the accuracy of the source document, its complexity and structure, font and language variations and the sharpness of the scan. For example, a document with clear, large print font (Figure 3) will generally OCR better than a fax copy with blurry text or handwriting (Figure 4).

With newer iterations of Adobe Acrobat, the OCR text accuracy has improved. When working with sets of scanned paper documents that were processed with older OCR engines, some people will spot check the accuracy of the OCR by running simple searches. Time permitting, they may then choose to re-OCR the documents. This can lead to more accurate searchable text.

A good practice for dealing with scanned paper PDF documents we want to work with is to first make a copy of the documents. For example, if we received a flash drive or a download from USAfx of scanned paper PDF files it’s a good idea to first copy the files to a location on a computer or a network drive. This way we can work with the documents and add OCR text when needed, while still maintaining a set of the original files.

With a copy of one documents open, the next step would be to see if document already is already searchable. When we open a PDF file, we are looking at an image of the document. Since the OCR text layer is invisible, we will not know whether it is searchable just by looking at it. There are a few things we can do to see if OCR is present.

If we go to the ‘Edit’ menu and choose ‘Select All’, or the keyboard shortcut ‘Control A’, (Figure 5) and we get a no text characters warning message (Figure 6), this indicates that there is no searchable text. Alternatively, if we use our mouse and single click in a blank area of the page, if the entire page turns blue, it also means there is no searchable text layer.

We can also try to find a word on the page using one of the search features in Acrobat. For example, when we run a find for the word ‘memo’ we get the same no text characters warning that we got by going to the Edit menu and choosing ‘select all. (Figure 7).

Starting the Text Recognition Process

To add an OCR text layer to a document, go to the tools menu and click on the ‘Scan & OCR’ button (Figure 8). When you activate this tool in Acrobat an additional menu bar will appear at the top of the page. Choose the ‘In This File’ option (Figure 9). In most circumstances we will go with the ‘All pages’ default. Click on the blue ‘Recognize Text’ button to begin the process (Figure 10). A progress indicator will appear on the bottom of the bottom right-hand side as it processes each page Adobe will also automatically rotate pages, based on the optimal rotation for the text on that page (Figure 11).

While the speed at which Acrobat can OCR documents can vary depending on the complexity of the documents and the type of computer being used, a good general estimate is about 1000 pages per hour. With particularly large OCR jobs, you might want to wait until the end of the day to begin the process. Some offices have also set up a spare computer, dedicated to running various processes such as OCR, so nobody’s computer is tied up.

When the Text Recognition Process is Complete

When the OCR process is complete, we can now go back to the first page to make sure the document is now searchable. If we go back to the ‘Edit’ menu and choose ‘Select All’, the text on the document will now be highlighted in blue while the blank areas surrounding the text will remain white. (Figure 12). A single click in the blank area no longer turns whole page blue. If we search for the word ‘Memo’ again, using the find option, we will get a set of search results with the first hit on the first page of the document highlighted in blue (Figure 13).

Since we have now changed the document by adding an OCR layer to it, save the file so we lose none of the work we have just done.

Text Recognition in Multiple Files

We can also run the OCR process across multiple documents, by going to our OCR tool menu (Figure 14) and selecting ‘Or recognize text in multiple files’ (Figure 15). This is a handy option, as we often receive batches of documents that might need to be OCR’d.

You can choose to OCR an entire set of PDF files in a folder by selecting ‘Add Folder’ (Figure 16) and then navigating to where that folder is on your computer or on the server. By default, Acrobat will include all PDFs and subfolders within the selected folder (Figure 17).

When running the OCR process on multiple files, we are prompted to choose an option as to where to save the files before you run the OCR. Most users choose to save the files in the same folder selected at the start with the original file names (Figure 18). Acrobat will also launch a progress bar for this process (Figure 19).

Estimate the page volume and run the process at a break or at the end of the day, if it is a large amount of information. The Acrobat help guide (https://helpx.adobe.com/acrobat/user-guide.html ) is a great resource if you are interested in discovering more about the OCR process.

The free Adobe Acrobat Reader software does not include the ability to OCR documents. ↩︎

Acrobat Training Guide – Searching Fundamentals

Featured

Posted on January 18, 2024 by joewanzala

Editor’s note: This is an update to the Adobe Acrobat Training Videos: Searching Fundamentals post. A related post is Three Types of PDFs.

Basic Search

This is a brief guide on the fundamentals of searching PDFs using Adobe Acrobat Pro. We will review how to run searches within a single PDF and across multiple PDFs. Searches can even be run on an entire folder of documents such as one that contains all the discovery you receive in a case. Searching in Acrobat will be useful only if the PDF files have searchable text. For scanned paper, you must make sure that they have been OCR’d first. OCR stands for ‘Optical Character Recognition’, and it is a process that reads pictures and turns them in letters and words so that they can be searched.

You can search not only the text of a document, but also any Adobe comments and bookmarks made on it. Searches can be run using either the “Find” or “Advanced Search” options. The way in which search results are displayed and what additional features may be available depend on the search tool chosen. The ‘Find’ tool can perform a quick search. With a PDF file open, display the ‘Find’ toolbar by choosing ‘Find’ from the main menu (Figure 1). There is also a ‘Find’ toolbar in the upper right-hand corner of the document which can be activated by clicking on the magnifying glass icon or by pressing ‘Control+F’ on your keyboard (Figure 2).

To perform a find, type a search term – for example, the term ‘Memo’. Acrobat will provide a preview of the number of hits for that word in document (Figure 3). After you hit enter on your keyboard, the search results will be shown highlighted in blue (Figure 4). If the term appears multiple times within a document, we can use the ‘Next’ and ‘Previous’ buttons to move from hit to hit. As we navigate through the search results, note that Acrobat highlights not only the word ‘Memo’ each time it appears, but also highlights any word which includes the letters ‘M-E-M-O’, such as ‘memoranda’ and ‘memorandum’.

There is a drop-down button next to the search term where we can select ‘Whole Words Only’, ‘Case Sensitive’, or choose to include bookmarks or comments (Figure 5). We will go over these features in greater detail when we look at the ‘Advanced Search’ tool below.

If we run a new find for the term ‘Xanadu’, a message appears letting us know that no hits were found ‘No results found’ (Figure 6). We will only get this message if the document is searchable and the word appears nowhere in the document. But, if we get a scanned alert page message, Acrobat is letting us know there is no searchable text associated with the document. For you to run your search, you will first have to OCR the document. If you need guidance on how to OCR the document, refer to the Acrobat OCR tutorial on this website.

Searching Comments

To search through just the comments, we can use the find tool in the comments list menu. To access this feature, select the ‘Comments’ icon in the upper right corner of the document (Figure 7). In this particular example we have 48 comments (Figure 8). Entering the term memo in the ‘Search’ box filters the list down to 4 comments and highlights the results (Figure 9). You can also sort and filter the list based on certain criteria such as comment author and comment type. To access this feature, click on the ellipses icon to the right of the search tool. (Figure 10). This opens an ‘Options’ menu. The ‘sort comments’ menu is the first level down is (Figure 11).

Advanced Search

While the find tool is quick, easy, and useful, the ‘Advanced Search’ tool has more features and is the preferred means of searching by many people. To open the advanced search window, select ‘Advanced Search’ from the main menu, or use the ‘Shift+Control+F’ keyboard shortcut (Figure 12). Acrobat will launch a new ‘Advanced Search’ dialog box (Figure 13). To automatically adjust the sizing of this window to fit nicely alongside the one showing your document click on the ‘Arrange Windows’ button.

When we run an Advanced Search for the term ‘Memo’, Acrobat generates a list of the results with some context. We can navigate between the results by clicking on them. Acrobat will go to the page and highlight the result in blue (Figure 14).

If we click on ‘New Search’ we can now either re-run the ‘Memo’ search or type in a new search term. This time, before we click the search button, let’s consider some of the additional features that we saw earlier when we used the find tool (see Figure 5 above). The ‘Whole Words Only’ and ‘Case Sensitive’ tools will limit the search based on the criteria selected (Figure 15).

When we re-run the search now, with these options checked, we will only get ‘Memo’ with a capital ‘M’ because we typed in the term in the search box with that exact capitalization. Other words containing the letters ‘Memo’ like ‘Memorandum’ are excluded because we also limited our search to only the whole word, ‘Memo’ and not any words containing those letters (Figure 16).

While ‘Case Sensitive’ and ‘Whole words only’ will restrict the search, ‘Include Bookmarks’ and ‘Include Comments’ will expand it. When we run the ‘Memo’ search again with these options selected, any comments or bookmarks with the term will be included in the list of results (Figure 17). We can tell if the result is a comment or a bookmark by looking at the icon next to it. Just like with text results, we can navigate to a bookmark or comment result by clicking on it (Figure 18). These features can be used in various combinations to further define your search.

If we click on ‘New Search’ again, options we previously selected are still marked (Figure 19). Acrobat retains the selections you make in the ‘Advanced Search’ window until you change them. Review these options before each search, as the number and type of search results can vary greatly depending on what options are marked.

Advanced Search – Multiple PDFs

Advanced search also enables you to look for a search term in multiple PDFs. This is helpful as often we receive multiple files we want to search through. Without this option, each file would need to be opened and searched separately. From the ‘Advanced Search’ window, select ‘All PDF Documents in’ (Figure 20) and then use the down arrow to choose a location. There are ‘Desktop’, ‘My Documents’ options and drive letter options, as well as a ‘Browse for Location’ option. We recommend that you choose the ‘Browse for Location’ option and navigate to a specific folder. This option will include the PDF files within the folder and any subfolders in that location (Figure 21).

Let’s choose a location where we have our discovery materials and run a search for ‘Memo’ again (Figure 22). The results appear in page order, nested under the name of each document (Figure 23). You can expand or collapse the list of results in a specific file by alternately clicking on the small arrow next to each result. As before, each search result includes some context (Figure 24).

This time, when we click on a hit on the results list Acrobat will open the document, showing exactly where the search term appears (Figure 25).

Next to the ‘New Search’ button at the top, there is a ‘Save results to file’ button (Figure 26). Selecting this button allows us to create a report in either PDF or CSV format. We recommend saving the results to PDF as this creates a nice summary of the results with links to the documents (Figure 27).

Admissibility of Text Messages

Posted on December 5, 2023 by aliciapenn

[Editor’s Note: Alicia Penn joined the NLST in September of 2023 from the District of South Carolina Federal Defender Office, where she represented clients charged with offenses involving everything from turtles to terrorism. A career Public Defender, she was forced to become proficient in electronically stored information after being assigned a 3-terabyte lottery scam case in 2013. Ever since, she has worked to adapt to the ever-changing electronic landscape and make it work in favor of her clients. In her new role her goal is to provide practical solutions to technological problems.]

Are screenshots of text messages admissible? The answer is everyone’s favorite: it depends!

There is no federal rule expressly forbidding the use of screenshots of text messages in court, but not all relevant evidence is admissible. To get a screenshot admitted and considered by a judge or jury there are procedural hurdles to overcome. Conversely, if your goal is to keep a screenshot out, the federal rules give you a framework to argue against admission.

The Rules:

Federal Rule of Evidence 901(a): In general, the proponent of the evidence must produce evidence sufficient to support a finding that the item is what the proponent claims it is. (b) gives examples of evidence that satisfies this requirement.

Other federal rules you can make hay with are: 1002 (Best Evidence), 401 (Relevance), 801 (Hearsay).

For a thorough and beautiful rubric of how to admit or challenge admission of electronic evidence, see

Admissibility of Electronic Evidence Chart by Hon. Paul W. Grimm and Kevin F. Brady (2018) craigball.com/Grimm Brady Evid Admiss Chart 2018.pdf

In addition, an excellent primer on admitting evidence in federal court is Lorraine v. Markel American Insurance Co., 2017 U.S. Dist. LEXIS 33020 (D. Md. May 4, 2007).

The Argument:

With reliability as a framework, there are two ways screenshots of text messages are inherently unreliable.

The text message itself is fake.

With only the screenshot, there is no way to prove a text message is real. Anyone can create an image that looks like a text message using a free online program, screenshot it, and then send it or show it to someone and claim it is real. Without any other proof, such as a direct download from a cellphone, subscriber records, IP address data, etc. there is no way to prove that the text message itself was a real message that existed in the way the government witness may claim.

I made the fake chat at the beginning of this post online, for free, entering no personal identifying information, downloading no software program, in two minutes, after Googling, “fake chat generator.”

You can imagine the ways a fake text message could be used against a client. I have had a case where the government wanted to use screenshots of text messages, supposedly received from an informant, and claimed that these screenshots proved my client planned armed robberies. When I showed the screenshots to my client, he denied ever creating or sending them. The government had no other corroborating evidence or witness to show the text messages were real or received from my client. I objected and argued the supposed screenshot was unreliable.

It is a real text message, but the sender/receiver is fake.

This scenario requires only a little more effort. There are apps like TextNow that allow you to send anonymous text messages. In one of my other cases, the government shared screenshots of threatening text messages sent from an anonymous number to my client’s ex-girlfriend and claimed my client had used TextNow to send these messages. Based on these screenshots of text messages, my client was detained, even though I argued the screenshots of text messages were unreliable and shouldn’t be considered. After the hearing, my investigator subpoenaed TextNow for subscriber information linked to the numbers used at the times the texts were sent. TextNow responded within a day, and the subscriber implicated the ex-girlfriend’s new partner as the person who created the text messages and sent the threats. After explaining and presenting this new information to the government and court, my client was released on an unsecured bond.

Cases that talk about screenshots and admissibility:

So, what is enough to admit a screenshot?

In United States v. Walker, 32 F.4th 377 (4th Cir. 2022), a government agent’s personal knowledge about the original report and the comparison of that report with a screenshot of letters calling a victim a snitch was “enough to show the screenshot was authentic. See Fed. R. Evid (b)(1)(4).

In United States v. Quintana, 763 Fed. Appx. 422 (6th Cir. 2019), an account in the defendant’s name, an email address with his name and moniker, a location linked to him, dates that corresponded to witness testimony, and a picture of him, plus other circumstantial evidence, was enough to authenticate a screenshot of a Facebook page presented by the government as the defendant’s.

In United States v. Vayner, 769 F.3d 125 (2nd Cir. 2014), the court did find a screenshot, alone, was not enough. The court expressed no view on what was enough but said that the fact the page with a defendant’s name and photograph existed on the internet was not enough to show the page was created by him or on his behalf.

Conclusion:

If the government presents you with a cellphone screenshot and claims your client is responsible, do not accept this at face value! Investigate it, even if it looks legitimate. Check to see if you received the file from the government in a PDF format, versus in an image format consistent with the device that allegedly took the screenshot. In addition, make the argument that the screenshot, alone, or even with other evidence, is unreliable, unauthenticated, and inadmissible. As part of this attack, educate the court and government on how easy it is to create fake messages. This is necessary to remove the gloss of reliability given to a screenshot of a text message that looks like what we are used to text messages looking like.

Because this question incorporates a technical inquiry, it is an area where working closely with an investigator, paralegal, or expert who understands the processes of the device at issue can help you argue to keep something out or let something in. A knowledgeable source can help you identify weaknesses or vulnerabilities to bring attention to during cross-examination. They should also be able to give you a factual assessment of a device’s or processes’ strengths and reliability. Either way, the more you know and understand, the better you can formulate and convey your argument.

Shutter Encoder

Posted on October 31, 2023 by ARoberts

We are constantly on the lookout for new tools to keep in our litigation support toolbox and Shutter Encoder is quickly becoming one of our new favorites. It’s a publicly available (a.k.a. “free”) media converter program that is based on standards and functions within the open-source FFmpeg software project. Currently there are both PC and Mac versions available for download from their website: https://www.shutterencoder.com/en/.

Here are a few of our favorite things we like to do with Shutter Encoder:

Convert file formats

Media files come in lots of different formats (ex: .asf, .avi, .mpg, .mov, .wmv, .amr, .m4a, .wav, .wma, etc…). It can sometimes be a struggle to consistently get them all to play. Some files are only compatible with specific programs, operating systems, and devices while others may also require that special “codec” packages be installed. Shutter Encoder can be used to convert media files using “standard” output codecs that work with a wide variety of environments.

For most video files the output codec standard we recommend is an MP4 file using the H.264 standard. This format is compatible with many modern devices, operating systems and video playback applications.

For example, we might have some .ASF video files that play in VLC Media Player, but will not work in Trial Director. When we add these files to Shutter Encoder, choose the H.264 output codec, then select the “Start Function” button, we produce new MP4 copies of the files that will play in both programs.

With audio files, we like to use the MP3 standard when converting as it works seamlessly with most media players. When choosing an MP3 output, you can further select from a list of different audio “bitrates” (in kb/s), which will affect the quality and file size. In most situations, we choose the 128 bitrate as that offers a nice balance between the two.

Cut, Remove and Split

There are many times when only a portion of a media file is needed. To produce a clip, or remove an unwanted section, Shutter Encoder includes Cut, Remove and Split options. We can access these features from a playback screen when converting file formats (ex: after adding a video file and choosing the H.264 Output codec).

The playback screen includes a timeline band (with a waveform graphic overlay), a play control button set, and a Mode selector. To select a portion of the media being converted, click and drag from the left and/or right sides of the timeline band. Alternatively, there are Begin and End buttons within the playback button set that can be used. The Mode dropdown in the lower right includes the following options:

Cut converts the selected portion of the media (and ignores unselected parts).
Remove takes out the selected portion (and converts unselected parts).
Split (less commonly used) divides media into separate files based on a user defined number of seconds.

We can preview a Cut or Remove section in a separate window by using the “Preview” button located to the right of the Mode dropdown. The actual Cut, Remove or Split will begin once the “Start Function” button is pressed and will only affect the output file (not the original).

Reduce video file size

Larger video files can often be difficult to work with. They can take a long time to open, use up valuable drive space, and prove challenging to share with others.

In addition to the Cut, Remove and Split features listed above, there are “bitrate” adjustments that can be made to video files that affect the size of the output file during conversion. Bitrate settings affect things like the video smoothness and sound clarity. Though the default options work well in most scenarios, it is possible to adjust these settings to create a smaller sized output file. Generally speaking, the following settings allow for a more compact output file size without any noticeable quality losses:

Video bitrate: 2500 kb/s
Audio bitrate: 128 kb/s

Adjust video rotation

If certain videos (especially those recorded on mobile devices) are not playing back in the correct orientation, we can re-orient them within Shutter by 90 degree increments. When the conversion function is chosen, Image Rotation options appear above the bitrate adjustments on the right panel. The preview window shows how the new orientation will appear in the output file when adjusting these settings.

Download web video(s)

Another useful function within Shutter is Download Web Video. This will attempt to download available videos from specified webpage addresses (URLs). Though it may not work with all videos on all webpages (due to page design or video protection protocols), it seems to work well with many common sources including Youtube, Vimeo and FaceBook video posts. We can copy and paste one or more URLs and the program will attempt to download the videos to the desktop. We can even point it to a Youtube channel page and to attempt to download multiple videos (if there are a lot of videos on the channel, be mindful that it might take a long time and a lot of hard drive space).

Below is a brief video demonstrating some of the functions mentioned in this post:

When a WOTUS is not Water: Winning the War with TRIAL DIRECTOR

Posted on July 26, 2023 by joewanzala

Why Use TRIAL DIRECTOR?

Courtroom technology is a boon for attorneys conducting courtroom presentations. Perhaps its greatest advantage is that the technology allows you to present your theory of the case in a visual way. Research and experience show us that having relevant graphics is more persuasive than words alone.[1] A principal challenge for the defense in criminal cases is that we are reactive to the government. We have to adjust to how the prosecution builds its case in chief when putting on our defense. Trial Director’s[2] greatest benefit for defense practitioners is that it allows them to add a visual component to their cross examination on the fly. This feature is critical, as more often than not we will be unsure how a witnesses’ testimony will come in on direct, and what we may need to focus on during cross-examination. To illustrate how Trial Director can be a useful tool for CJA panel attorneys and Federal Defenders, we will review a real case I worked on, United States v. Lucero, No. 19-10074 (9th Cir. 2021). This trial involved explaining complex scientific and regulatory information to a jury. Let me give you an example of how we used Trial Director and visuals to assist with the cross-examination of witnesses – and then tied it together in closing. I served as the “hot seat” operator, pulling up and annotating exhibits under the attorneys’ direction, as I describe in detail below.

Dumping Debris into a WOTUS

Our client, a dirt broker, was charged with illegally dumping debris onto federally protected wetlands on an undeveloped, privately owned property in Newark, California. This area covers about 400 acres located south of San Francisco near Mowry Slough. It is next to the Don Edwards San Francisco Bay National Wildlife Refuge. At the time of the charged offense, a consortium of developers was in the process of planning a master-planned golf course community on the site.[3] The case was brought under the Clean Water Act, 33 U.S.C. § 1251 et seq. (“CWA”) which made it a crime to discharge pollutants onto wetlands, regulatorily defined as traditional navigable waters, without a permit. At trial, we conceded that our client dumped debris without a permit. The judge denied our requested knowledge instruction, so the government did not have to prove Lucero’s knowledge of the wetland issue. Multiple government experts testified that the dump sites qualified under the regulatory definition as “wetlands.” However, the sites did not appear wet at the time of our client’s conduct, because the dumping occurred during the summer and after several years of drought.

As we shall show below, we were able to set up the issue of knowledge at trial, and eventually won on appeal. Below are a series of brief examples designed to illustrate how we used TRIAL DIRECTOR during the cross-examination of witnesses.

Educating the Jury

The map at Figure 1 was one of the exhibits we used during the cross-examination of a government expert. Initially, this expert was hired by the developers to conduct an environmental analysis of the area. During direct examination, the government showed the expert a series of maps to elicit testimony designed to illustrate precisely where, on the property designated as wetlands, our client dumped debris. These two regions in the map below demarcated with red boundaries were where he dumped debris using dump trucks over a period of several months.

This map included a key indicating which parts of the area were designated as wetland (Figure 2).

During cross-examination of the expert, we used what Trial Director describes as a “callout.” The attorney directed me to zoom in on the portion of the exhibit that had the map key. Using the software program, I drew a box around the map key. The software program pulled out that portion of the exhibit, making it larger, while adding a 3-D fade-black effect on it with projection lines. NOTE: I could adjust the size and shape of the “callout” on the fly, and even scroll within it as needed. The attorney then elicited testimony about how, for example, there were significant segments of the area in question that were not designated as wetland, to support our lack-of-knowledge argument.

Tributaries, Culverts, and Navigable Waters

Figure 3 is another map of the same region, again with red demarcations showing that the area where our client dumped debris was protected wetland, but with additional details such as references to tributaries and culverts.

The map at Figure 4 is a screenshot of what I did live in front of the jury pursuant to the attorney’s instructions. This process was dynamic. The attorney asked me to do a callout, and instructed me to draw arrows to show the areas where our client was accused of dumping debris, within the “South Fill Area and the North Fill Area”.

The crucial fact in dispute was whether this area was considered a WOTUS, and that determination involved the question of whether the TNW had a “tributary;” that is, whether it showed physical features of flowing water. One of the areas of “flowing water” was this culvert. Live, in front of the jury, the attorney instructed me to use the “Zoom Region” tool in Trial Director to allow the jury a closer view of the map detail (Figure 5).

The Presentation screen is a feature of Trial Director that can be used to display multiple pieces of evidence simultaneously. It is divided into nine zones – Zone 1 is the left side of the screen, Zone 2 is the right side of the screen, Zone 5 is to the top left quadrant, and so forth. We used this feature to address the question of whether the culvert was a WOTUS during cross-examination of a witness. Figure 6 shows the aerial map of the area with the culvert in Zone 1, shown side by side with a video of the general area of the culvert Zone 2, paused to show the culvert in the foreground. The attorney then instructed me to use the annotation tools to add arrows and ellipses to indicate where the video footage of the culvert was in relation to what was shown in the aerial map. (Figure 6).

Figure 7 (below) is an iteration of Figure 6, with the aerial map still on the left side of the presentation screen (Zone 1) while on the right side of the screen (Zone 2) the attorney instructed me to again pull up the same video exhibit. However, instead of the same frame of the clip shown in Figure 6, the attorney instructed me to play the video and then pause it at a point showing a close-up of the water flowing through the culvert. The attorney also instructed me to use the annotation tools to add arrows and ellipses to indicate where the video frame of the culvert was in relation to what was shown on the aerial ma. One related and exciting feature of Trial Director is its ability to do a callout of a portion of a video while it plays. You can zoom in on a specific area of the video, do a callout, and let it continue running to show the zoomed area in detail as it plays. This feature can be particularly useful when playing surveillance or body cam videos.

A Close Reading of Topographical Maps

One of the government’s witnesses in the Lucero case was a civil engineer who had been involved in conducting a land survey of this area – again, the issue was whether the geological and ecological features of the area met the criteria for it to be considered a WOTUS. We conducted an extensive cross-examination of the civil engineer, during which we closely reviewed a series of topographical maps, such as the one shown in the video below (Figure 8). To effectively pull off this cross-examination, the testimony was tied to a close reading of the topographical maps, focusing on the significance of the gradients indicated by the contours. The ability to dynamically pivot on the fly, depending on how the witness testified, was critical.

Closing Argument

When using courtroom technology, most people will use PowerPoint for opening statements and closing arguments. However, Trial Director can be used for openings and closings as well. Many experienced practitioners will use PowerPoint as their primary tool during a closing, but then switch over to Trial Director to show an exhibit, so as to respond to a point made during the government’s closing, and then switch back to PowerPoint to continue. One of the themes we focused on during closing argument was the government’s experts’ re-writing of history. Reports prepared between 2007 and 2016 by environmental consultants hired by the developers, and by the US Army Corps of Engineers, differed in key aspects with the reports developed by the witness the government hired. For example, the 2007 reports did not find that there were any tributaries, while the government’s expert report determined that the ditches in the South Fill Area were in fact ”tributaries.” (Figures 9 & 10)[4]

Furthermore, between 2012 and 2016, California experienced the most severe drought in a millennium, and the bulk of the pictures introduced by the government at trial were from the 2007 reports and January 2017. We argued these photos of ponding, or water, did not prove water in the summer of 2014, when the charged conduct occurred (Figure 11).

Dumping Debris into “Water”

Our defense hinged on educating the jury about technical issues related to wetlands and the complex regulatory definition of WOTUS. The government had a much easier job, namely, to focus on the seemingly unambiguous visual impact of debris dumped onto this area. Our client was found guilty. (Figure 12.)

On appeal, the United States Court of Appeals for the Ninth Circuit reversed our client’s conviction and vacated his 30-month sentence. It held that the district court erred by not instructing the jury, as the defense had requested, that the government had to prove the defendant’s knowledge that the sites where he dumped debris were “water.” (Figure 13).

The Lucero trial provided an example of how using Trial Director played out in a real case and establishing a factual record for appeal. We were able to demonstrate on appeal with evidence from the record that the knowledge issue was important given how the site appeared dry at the time of the offense. The visuals provided the backdrop for our expert’s declaration, which we submitted as an offer of proof. The appellate court relied on this record to reverse on the knowledge issue.

If the government had opted to retry the case, it would have had to prove that our client knew he was dumping debris into “water” in the generally understood sense of the term, rather than into an area defined as “a water of the United States” within the meaning of the CWA. Ultimately, the government opted not to re-try the case. On October 20, 2021, our client, who had remained out of custody while the case was pending, pled to one CWA count and an agreed-upon sentence of one year probation.

The chart below sets forth practical considerations for using the software in the courtroom (Figure 14).

This blog post was designed to provide an overview of how you can utilize Trial Director. If you would like to take a deeper dive with a hands-on, one-on-one training, please contact Kelly Scribner or Joe Wanzala.

[1] “People learn more deeply from words and graphics than from words alone. This assertion can be called the multimedia principle, and it forms the basis for using multimedia instruction – that is, instruction containing words (such as spoken or printed text) and graphics (such as illustrations, charts, photos, animation, or video) that is intended to foster learning.” (Mayer, 2021, in press-a). See https://www.researchgate.net/publication/369588588_Learning_by_Teaching.

[2] As of July 2023, IPRO has renamed TrialDirector 360 to TRIAL DIRECTOR. It has also been called Trial Director and TrialDirector (one word) in the past. For the purposes of this post, we will use the term “TRIAL DIRECTOR” and “Trial Director” interchangeably to describe the software program.

[3] https://www.santacruzsentinel.com/2016/03/22/carmel-man-charged-with-illegal-dumping-in-newark-wetlands.

[4] In April 2020, the Environmental Protection Agency promulgated a new WOTUS rule which narrowed the definition of ‘waters of the United States’ and found that ditches would not be considered to be “tributaries.” The most current version of the WOTUS rule took effect March 2023, but then there was a subsequent Supreme Court decision limiting/revising the meaning of WOTUS. see, https://www.epa.gov/wotus/current-implementation-waters-united-states

Traveling Through Time in the Wayback Machine (without Mr. Peabody)

Posted on March 21, 2023 by carladamsnlst

The Internet is a dynamic place. While that is a benefit when we want new information quickly, it is a challenge in the legal field when we need a reliable, stable platform on which to build our arguments or conduct research. This backdrop is where the Wayback Machine comes in. The Wayback Machine is a service provided by the Internet Archive, a non-profit digital library dedicated to the preservation of our digital culture, which contains more than 26 years of archived web pages available for our viewing. This resource can serve our profession in two ways: first, as a tool for researching information that has disappeared from the public-facing internet; and second, as a tool for providing a stable link for use in briefs and motions. There are for-profit entities out there that will charge you for a similar service, however the Internet Archive is free, and has a proven track record of stability.

As a Research Tool

The Wayback Machine gives users the ability to go back and see what a particular webpage looked like on a certain day. For instance, here is this blog from April 27, 2012: https://web.archive.org/web/20120427202547/http://nlsblog.org/. Or here’s the front page from the New York Times on February 23, 2016: https://web.archive.org/web/20160223172920/https://www.nytimes.com/.

How can this be useful? Let’s say that part of your theory of defense hinges on Snapchat’s Privacy Policy in place on June 8, 2019, but if you visit Snapchat’s website at the time of this writing, you will see that the current privacy policy was updated in 2022.

To find the policy in effect on June 8, 2019, you can use the Wayback Machine, by going to https://web.archive.org and searching for http://www.snapchat.com.

Once you have selected the correct site, the Machine will take you to a timeline and calendar where you can select the capture for the desired date and time. First, click on the correct year on the timeline, in this example, 2019.

Next, select the date and capture time you want from the calendar. A quick note, captures and dates that are blue are better than green, so go for those if possible.

The Wayback Machine will now load snapchat.com as it looked on June 8, 2019, at the time you selected.

From here, you can navigate to the Privacy Policy and view it as it was on June 8, 2019.

To save it, you can either print it, capture it with software like WebPreserver, or link to it via the Wayback Machine. For more information on this last method, see the next section.

Stable Links for Citation

Citation to internet sources in motions or briefs can be a tricky thing. Sure, the Bluebook can tell you the “proper form” for an internet citation, but no amount of spading today guarantees that a link will work tomorrow. Not only can a website change its structure, rendering the link dead, but the site itself could disappear, taking all its data with it. The Wayback Machine can help.

In the exercise above, we located the Privacy Policy for Snapchat.com from June 8, 2019. If you needed to incorporate this page into a brief, you could either print or capture it as a PDF and attach it as an exhibit, or you can cite to the Wayback Machine’s version. This type of use is encouraged by the Internet Archive. After you arrived at the Snapchat’s privacy policy, the address bar shows an exact link to this version of this page.

Simply copy and paste the URL from the address bar into the citation in your brief. Now, when the court glowingly quotes your winning argument in a ruling, future lawyers reading it on Westlaw in ten years can click and read the original source material without encountering a dead link.

But what if the information you want is not on the Wayback Machine yet? Perhaps the website hasn’t been recently archived, or worse, has never been archived? You can trigger the Wayback Machine to take a snapshot of a page on demand, which will give you a stable link to the information you want for citation. To trigger a capture, go back to the Wayback Machine homepage (https://web.archive.org). Instead of entering a query in the search box, enter the URL you would like to preserve in the “Save Page Now” box.

For instance, this blog had not been archived since January. I entered https://nlsblog.org into the “Save Page” box, and told it to save. After a page where I confirmed what I wanted, The Wayback Machine got to work:

Now the Wayback Machine has a current snapshot. To copy the link, right-click on the “Visit Page” link and select “Copy Link Location” or visit the page itself and copy the URL from the address bar.

As a warning, the Wayback Machine will not work with all websites. Some sites use special settings (robots.txt) to prevent automatic capture or crawling by sites by search engines. For example, individual Facebook profiles are not available. A good rule is if you can’t find it with Google, you probably won’t find it on the Wayback Machine.

Conclusion

As I said in the beginning, the Internet is a dynamic place, but we do not have to let it stop us from finding the information we need or cause us to worry about the citations in our legal arguments. The Wayback Machine can be a blissful island of stability in an ever-changing world. Cite with confidence.

ChatGPT: Implications for Criminal Defense Litigation Practice

Posted on February 21, 2023 by joewanzala

eDiscovery, or electronic discovery, is the process of identifying, collecting, and analyzing electronically stored information (ESI) in order to be used as evidence in legal cases. This process can be time-consuming and costly, as it often involves manually reviewing large amounts of data. However, advances in artificial intelligence (A.I.) have opened up new opportunities for streamlining the eDiscovery process. One such technology is ChatGPT, a large language model developed by OpenAI.

ChatGPT is a powerful tool for natural language processing (NLP) that can understand and generate human-like text. This makes it an ideal candidate for use in eDiscovery, as it can quickly and accurately analyze large amounts of ESI in order to identify relevant information. For example, ChatGPT can be used to identify specific keywords or phrases within a document, classify documents by type, or even summarize the content of a document.

The introductory paragraphs above were generated by ChatGPT in response to a request to write a blog post on ChatGPT and eDiscovery. This is an example of how ChatGPT can generate text in such a way that one cannot immediately tell whether it was written by a machine or human. This blog post will provide initial takes on what the potential ramifications ChatGPT and similar Artificial Intelligence (A.I.) tools can be for the work CJA panel attorneys and federal defenders do. It is not advocating any specific position regarding A.I. technology which has wide ranging and yet to be realized implications in many fields. The goal is to provide a general idea of how this new A.I. technology might impact our work.

What is ChatGPT?

The current version of ChatGPT, 3.5 was released in late 2022 (openai.com/blog/ChatGPT). It is an artificial intelligence tool built on a natural language processing model known as a Generative Pre-trained Transformer (‘GPT’) or ‘generative A.I.’ developed by OpenAI. ChatGPT is great for generating human-like text to help solve problems. This can include answers to questions, summaries or translations of large volumes of text, generating lines of code, or providing step-by-step, conversational instructions for a wide range of complex software applications.

ChatGPT is trained on a massive corpus of datasets including many publicly available domains on the internet including Google, the Wayback Machine, Github, WordPress, Wikipedia, and so forth. However, it is not connected to the internet in real time and has limited knowledge of world and events after 2021. This means it can occasionally produce inaccurate information, a problem that OpenAI acknowledges help.openai.com/en/articles/6783457-chatgpt-general-faq. In some instances, it will tell you it doesn’t know, sometimes it will provide an answer with a disclaimer. It can also provide an authoritative sounding answer that is wrong without any qualifier. It has even been known to fill in the gap with made up information. For example, eDiscovery expert Ralph Losey asked the robot to identify the top five eDiscovery cases for 2022. Since it did not have any 2022 cases to reference – it ignored the date – listed only 2021 cases, and even made up the name of a judge! ediscoverytoday.com/2023/01/02/ai-top-cases-of-2022-doesnt-include-any-cases-from-2022-artificial-intelligence-trends/

In response to these sorts of user experiences, OpenAI recently sent out a tweet with warnings noting that ChatGPT is useful for general information in subject areas such as language, science, engineering, finance, history, culture; and less suitable for high context or niche areas such as legal advice, and real time events. twitter.com/openaicommunity.

Can ChatGPT be used for discovery review?

Artificial Intelligence models based natural language processing have been deployed extensively in eDiscovery for some time. Foremost among these approaches is Technology Assisted Review (TAR)[1] which uses algorithms to identify and highlight relevant information based on input from subject matter experts. This technique helps reduce attorney review time and thereby creating time and cost and workflow efficiencies.

Since TAR and generative A.I. are both based on the natural language processing branch of artificial intelligence (Figure 1), one might assume that ChatGPT’s ability to generate human-like information about a broad and complex range of data sets could be easily applied to eDiscovery to enhance eDiscovery review methods such as TAR. Indeed, in the second introductory paragraph above, ChatGPT generated text that describes common eDiscovery tasks that artificial intelligence software can perform with the proper conditions. But it also wrote that it, ChatGPT, could do these types of tasks. While it is true that ChatGPT can perform these tasks based on information it has been trained on, it was not designed to perform eDiscovery tasks, and OpenAI has not developed a version of the GPT technology that can be utilized for eDiscovery. Furthermore, even if the underlying GPT-3.5 model could be developed for an eDiscovery environment, the immense computing resources it currently requires, designed for vast amounts of data, would make it non-scalable and cost-prohibitive. law.com/legaltechnews/2023/01/25/what-will-eDiscovery-lawyers-do-after-chatgpt/

Figure 1.

What can ChatGPT do right now?

ChatGPT has more direct application in terms of workflow and analysis. Discovery in criminal cases increasingly includes both structured (databases, spreadsheets) and unstructured (documents, videos, audio files, phone extractions, social media, emails) data. Currently, most workflows designed to integrate and synthesize these heterogenous formats are necessarily cumbersome, requiring a patchwork of approaches. Many easily available open source tools (e.g. Openrefine, referenced below) or applications such as Microsoft Excel which can be helpful to practitioners are under-utilized, if leveraged at all. ChatGPT has the potential to help bridge the gap between the utility of these applications and practitioners’ ability utilize them.

For example, below (Figure 2) is a screenshot showing ChatGPT’s response to a question about importing a CSV file[2] into CaseMap (a fact and case organization and analysis tool – nlsblog.org/2011/10/05/cja-panel-attorney-software-discounts). Note that while ChatGPT is providing helpful feedback, it is not providing specific, practical instructions on how to carry out the importation of the CSV file into a CaseMap database. This is due to the limited information about CaseMap built into the OpenAI model. In the example above, ChatGPT was able to provide a step-by-step guide on how to import a CSV file into CaseMap. However, there are better and more efficient ways to import a CSV file into CaseMap than what ChatGPT prescribed.

Figure 2.

In our second example, (Figure 3) we see how ChatGPT can help us deal with, CSV files containing ‘messy’ data, in this case duplicate rows in a spreadsheet. It provided guidance on how to utilize a tool called Openrefine openrefine.org to ‘clean-up’ the spreadsheet.

Figure 3.

Since Openrefine is a free, open source tool, ChatGPT was able to develop more accurate information than one might expect when dealing with ‘closed’, proprietary tools such CaseMap.

Conclusion

The need to harness software to effectively work our cases will only increase as data complexity continues to ratchet up. ChatGPT can help facilitate the utilization and adoption of open source and business applications in response to these challenges; lowering the bar to access by providing on-demand, human-like support to practitioners. This can help with the ‘trees’ we believe are relevant to our cases; e.g. a subset of files responsive to a search query. This still leaves the ‘forest’; the large tranches of discovery which we load into review platforms such as Eclipse SE and Casepoint, to parse and organize the data. Whether or how the generative AI technology underlying ChatGPT will have impact in this latter arena remains to be seen.

[1] Also known as predictive coding, computer assisted review, or supervised machine learning.

[2] A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record, and usually consists of tabular data from a database. The CSV file format is supported by a wide variety of business applications including MS Excel en.wikipedia.org/wiki/Comma-separated_values

Microsoft Excel Tips & Tricks for CJA Cases: Formatting Tables

Posted on January 6, 2023 by ARoberts

This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.

Previous Post: Filename Lists
Today’s Post: Formatting Tables

CJA panel attorneys and federal defenders frequently receive some of their discovery in spreadsheet or Excel format. Call detail records and indices listing information regarding discovery productions (often called document indexes) are two examples. Having the files in Excel format instead of PDF is advantageous as spreadsheets are designed to sort and filter information, either by a single or multiple criteria. With voluminous information, this ability to sort and filter by multiple criteria can speed up review and allow you to identify the information you are interested in. For example, if you have telephone call records in Excel format, it is easy to filter by a number of phone numbers and quickly narrow the entries to review with several clicks. If the same information is in PDF format, it would have to be done manual and take much, much more time.

For those experienced with Excel, it is a welcome sight to see the data in Excel format. However, for the neophyte Excel user who only reviews PDF files, it can be frustrating to navigate Excel and review the data. This post provides quick and easy formatting options that are available within Excel that can save you review time.

Let’s look at a mock discovery document index as an example. Often the document index starts as a simple list of files with some basic information. As these lists become longer and include more document details (frequently in the form of additional columns), they can be hard to read and work with unless formats are applied. The video below demonstrates quick and simple options within Excel that can help to transform a basic list into a better looking, more functional table that include easy to use sort and filter features.

Video:

Some of the topics covered in the video above are:

Column Width
It often helps to be able to adjust the width of columns to better fit important information on your screen. To adjust the width of a column:

Move the cursor in between the column headers until it becomes a black line with two arrows.
To manually adjust the width of a column left-click then drag the black line to the right or left.
To automatically adjust a column, double left click and the column width will become as wide as the longest text entry in that column.
Multiple columns can be adjusted at the same time by selecting them before making a manual or automatic adjustment.

Wrap Text
Automatically resizing row heights helps to make the words within longer text cells visible. Select the cells, rows or columns to be adjusted then choose the “Wrap Text” button from the Home menu.

Cell Alignment
Adjusting cell alignments can sometime help make items more uniform and easier to view. By default, Microsoft Excel aligns numbers to the “Bottom-Right” of cells and text to the “Bottom-Left”. A common adjustment is to change cells that are “Bottom” aligned to become “Top” aligned, as that is generally easier to read. To do this: select the cells, rows or columns to be adjusted then choose the “Top Align” button from the Home menu. “Right” aligned cells can be adjusted to “Left” alignment through a similar process.

Freeze Panes
Selecting certain columns and rows to always be visible greatly increases the readability of longer lists. To freeze panes:

Left click on the cell to the right of, and below the rows and columns you wish to always be visible.
From the View menu, click on the “Freeze Panes” button and select the “Freeze Panes” option.

Data Filter
Data filtering is a powerful formatting option. It unlocks the ability to easily sort, filter and search within columns. To turn on filtering:

Select all of the data including any column names.
From the Data menu, select the “Filter” button.
Once data filtering has been enabled, items can be sorted, searched and filtered on by choosing the filter button from the columns.

Format as Table
Alternatively, data can be filtered by selecting a “Format as Table” style. “Table” styles are a quick way to make the data visually pleasing and they automatically include the data filtering feature. To turn a list into a “Table” style:

Select all of data including any column names.
From the Home menu, click on the “Format as Table” button and select your desired style (I like the “Medium” styles personally).
From the “Create Table” dialog box select the “My Table has headers” option then click the “OK” button.

Note: Be aware that these format changes are modifications to the original Excel file. If preserving an original copy of the file is important make sure to choose the “Save As” option when saving changes.

Electronic Exhibit Sticker

Posted on October 7, 2022 by carladamsnlst

Preparing exhibits for trial or court hearings, though not glamorous, is an essential task in the practice of courtroom litigation. Depending on the volume and type of exhibits, this necessary task can quickly turn tedious if you must print each exhibit, affix a physical sticker, fill out the exhibit and case information by hand, then scan and submit the stickered exhibit. In the heat of trial where last minute changes take place frequently, it is easy to make mistakes. However, with the right type of technology, such as Adobe Acrobat Pro (or Standard), this process can be done more smoothly, help reduce opportunities for making errors, and done more quickly than the old school method of stickers and paper If you have Adobe Acrobat*, we suggest considering using digital (electronic) exhibit stickers for your next case.

*Acrobat Standard or Pro, not the free “Reader” version.

This post will walk you through how you can create digital exhibits on your own, including the process of installing a sticker that takes the form of a custom Acrobat stamp. The stamp will allow you to quickly fill in the exhibit and case numbers for your case, and will automatically remember your previous entries the next time you use it.

First, follow the instructions below to install the electronic exhibit sticker.

Installation

Download and copy the exhibit_stickers.pdf file to a location that is easily accessible, such as your Desktop. (NOTE: You can delete this PDF file once we are finished with the installation.)

exhibit_stamps Download

Open Acrobat and press CTRL-K to open the Preferences menu. Scroll down on the left to “Security (Enhanced)”. Click the “Add File” button, which will open a file explorer window.

Type %appdata% into the address bar and press enter.

This will open a new folder. Open the “Adobe” folder, then the “Acrobat” folder. You may see folders for the different versions that have been installed like a “2017”, “2020” or a “DC” folder. Open the “DC” folder if you have that, or else the highest folder year you have. Open the “Stamps” folder. Find the “exhibit_stamps.pdf” file you saved and drag or copy and paste it into the Stamps folder. Select the file and click “Open.”

This will take you back to the Preferences screen. Verify that exhibit_stamps.pdf is listed inside the box. If the file is there, click “OK”. Then close out of all Acrobat windows.

Usage

Open the PDF that needs an exhibit sticker. Select the “Comment” tool from the list along the right side of the screen.

This will open a new toolbar. Click on the Stamp tool icon, navigate to the “Exhibit Sticker” menu, then click on the Exhibit sticker image.

The first time you use the sticker, it will pop up this window. Check “Don’t show again” and click “Complete.” There is no need to enter any information.

Your cursor will now become a floating exhibit sticker. Click where you would like to place the sticker. Do not worry if the initial placement is not perfect; you can move the sticker to a different part of the page and even resize the sticker after you have placed it.

When you click to place the stamp, a window will pop up asking you to enter an Exhibit Number. Enter the Exhibit number in the box and press OK.

Next, a window will pop up asking you for a Case number. Enter the Case number and press OK.

This will place an exhibit sticker on your PDF that contains the Exhibit Number and Case Number. You can move and resize the sticker if needed. If you need remove or change any of the information on the sticker, you can right click on the sticker, select “Delete” and create a new sticker.

To permanently affix the sticker to the document, you will need to print the document to a new PDF. Go to the File menu and select Print. Now change your printer to “Adobe PDF”, change the “Comments & Forms” selection to “Document and Stamps”, then press print and save your new copy to the location of your choosing.

That’s it. You will now have a permanently stamped PDF document. The next time you want to stamp a document, Acrobat will pre-fill your last enter Exhibit Number and Case Number, so it will be easier to keep track of your exhibits if you are marking multiple documents in one sitting, and you will not have to re-enter the case number each time.

If you need any assistance with installation, you can contact me at carl_adams@fd.org.