9.4.1 Personal email network questions

Several questions can be asked about personal email network datasets:

• • Individuals. Who are important individuals within the network? For example, who are boundary spanners who link across clusters of contacts? Who is contacted most often? Who are the most active discussants of a particular topic? Who are unwanted or troublesome correspondents? Who are unresponsive recipients?
• • Groups. What natural subgroups exist? What collaborative activities are individuals engaged in? What are the relationships between subgroups?
• • Temporal comparisons. How have relationships changed over time? How did an event (e.g., move to a new location) affect the network? What inactive groups exist with whom I may benefit from re-establishing contact? What projects or people have I neglected?
• • Structural patterns. Are there common social roles that occur among contacts (e.g., informant, decision maker, boundary spanner)? Are there types of subgroups that occur (e.g., cliques, fans)?

9.4.2 Organizational email network questions

Several different questions can be asked about organizational email network datasets:

• • Individuals. Who are the important individuals within an organization? For example, who are the boundary spanners who link across organizational silos? Who are the influencers or topical experts? Who is not well connected and could benefit from more social ties? Who would be a good replacement for an individual? Who fills a unique niche? Who was in-the-know about an important decision?
• • Groups. How do email-based groupings differ from organizational structures? How is the “org-chart” different from the chart of the flow of email? How are groups interconnected? Which groups should be better connected? Is there a core competency that is not discussed in a particular branch or office?
• • Temporal comparisons. How does information flow through the organization? How do connections among individuals and subgroups evolve over time? How are social relations affected by a major event such as a merger or opening of a new office?
• • Structural patterns. What network properties are related to success? Can we identify up-and-coming stars or unique social roles based on their network structure? How is information on a particular topic distributed throughout the organization?

9.5.1 Preparing email

Most email clients do not export data in a format amenable to network analysis. Furthermore, the email you'd like to analyze may be stored in different formats and reside on different computers or web mail servers. As a result, you may need to prepare your email before it is ready to import into network analysis tools such as NodeXL.

The easiest way to transform email messages into network relationships (i.e., an edge list) is to use NodeXL's Import from Email Network feature. This feature relies on the Windows built-in indexing functionality on recent versions of Windows (e.g., Windows 10). By default, email files in certain formats will be indexed by Windows. You can view and change which filetypes are indexed and check indexing progress in the Indexing Options dialog accessible via the Control Panel.

You may not have the email you want to analyze on a local or shared machine. For example, you may exclusively rely on a web mail service such as Gmail or Hotmail. Nearly all web mail services allow you to download local copies of your messages via POP or IMAP to an email client such as Thunderbird or Outlook, or create an archive of the files for backup. However, in some cases you may need to purchase backup software to export into a file that can be indexed. If you are using IMAP, make sure to download the complete email message files, not just the header information. Otherwise the Window's indexing service will not download the content of the messages and allow you to import them using NodeXL (as described later). You can typically choose not to download attachments if there are space limitations. If you use IMAP you can also restrict the download by folder. For example, you may want to only download recent messages (i.e., those sent in 2018) rather than years of data. After downloading messages, it may take Windows some time to index all of the files. If you have subscribed to an email list and retained all of the messages you want to analyze, you can place them in a folder and use IMAP to download just those messages.

9.5.2 Importing email networks into NodeXL

Once Windows has indexed the email you want to analyze, you are ready to import the data directly into NodeXL. Select the From Email Network option from the Import drop-down on the NodeXL ribbon to open the importer shown in Figure 9.2.

The enormous size of many email collections often requires filtering out messages before analysis. Even when email collections are of manageable size, filtering messages can hone in on a specific subset of messages ideally suited for addressing a question of interest. There are several ways of filtering:

• • Filter based on time. Include all messages sent and received during a specific time period. In NodeXL, the Date Range fields allow you to specify a time window in which messages must appear in order to be included in results. Filtering based on time can be used to slice data into networks to facilitate comparison over time or measure the impact of an important event. In Figure 9.2, only messages sent between 1/1/2018 and 11/30/2018 are included.
• • Filter based on sender and receiver(s). Include only messages sent or received by certain people. These people may be part of a group (e.g., department, workgroup), share some characteristic in common (e.g., senior managers, located in Maryland), or have a certain relationship to another person (e.g., all those who have received a Bcc message from John). In NodeXL it is possible to specify email addresses to be found in the From, To, Cc, and Bcc fields. The default setting is a Boolean OR relationship, so if you include multiple addresses it will pull all messages with any of the addresses included. It is also possible in NodeXL to restrict messages to those that include (or don't include) any addresses in the Cc or Bcc fields through the checkboxes available the right-hand side of Figure 9.2. Filtering based on the sender and receiver(s) is ideal for focusing in on a subgroup of important people for further analysis.
• • Filter based on content. Include only messages that share some characteristic or content of interest. In NodeXL messages with or without attachments, messages within a certain size range, and messages or subjects with specified text can be filtered. The text search feature can be powerful when combined with standard naming conventions. For example, all messages from the Association of Internet Researcher's email list can be selected by searching for the text string “[air-l]” in the Subject search box. In the example shown in Figure 9.2, only messages with the word “cybersecurity” in the message body are included. This was chosen because a new Cybersecurity major was started at BYU in 2018 allowing a more focused analysis of this topic.
• • Filter based on folders and labels. Include only messages that are found in a specified folder or messages with a certain label (e.g., in Gmail). People often organize or label (i.e., tag) email into meaningful collections based on subject matter or projects. In NodeXL, messages can be restricted to those found within a certain folder. Filtering based on folders is ideal for capturing interactions about a specific project or topic that may not be identifiable from a simple keyword. The “Sample folders” link shown in Figure 9.2 shows examples of pathnames that can be entered. If you do not know the name of your email account, you can open up Outlook, right-click on the folder you are interested in, and choose Properties. For example, if I were to restrict my search to all messages in the Sent Items folder, I would type in /[email protected]/Sent Items.
• • Filter based on a combination of features. The various filtering options can be combined in intricate ways to find, for example, messages from a subset of key people sent during an important time period with a certain keyword. If more advanced filtering is needed, you can use an advanced email management tool like Aid4Mail to create a folder of desired messages and restrict the import to that folder. This enables the use of advanced search queries (e.g., regular expressions) for identifying messages (see Advanced topic: Working with large email collections).

In addition to filtering the messages included in the social network dataset, NodeXL allows the way the edge weight is calculated to be specified either based only on addresses in the To field or including those in the Cc or Bcc fields as well. This is independent of filtering. By default only those addresses in the To field are counted. In the example displayed in Figure 9.2, the Cc field is included when calculating edge weights, but not the Bcc field.

9.6.1 Remove duplicate email addresses for the same individual

If your focus is on connections between people, as opposed to specific email accounts, you will want to combine multiple email accounts from the same person into a single one. Unless you are using an advanced entity resolution software program to do this, this is likely to be a somewhat manual process.

The simplest approach is to use the Find and Replace tool familiar to most Excel and Word users. Start by choosing Show Graph and then navigate to the Vertices worksheet. Sort the Vertex column from A to Z so that email addresses that start with the same name will be next to one another (e.g., [email protected] and [email protected]). Then click on Control + F to open the Find and Replace window and enter the appropriate email addresses (Figure 9.3 presents an example). Navigate to the Edges worksheet and choose Replace All for data in the Vertex1 and Vertex2 columns. There will likely be duplicates that don't start with the same username (e.g., [email protected] is my work email address, while [email protected] is my personal email address). To find the important people based on the frequency of interaction you can sort columns by edge weight and make sure that those with a high edge weight are not duplicates. The most important duplicates to remove are your own.

After replacing the fields in the Edges worksheet, you should delete all of the rows that have data in the Vertices worksheet. Then click Show Graph, which will generate a new list of Vertices on the Vertices worksheet. If you fail to do this, you will have duplicates in the Vertices worksheet, which can cause problems later.

The problem with using Find and Replace is that it must be repeated each time the data is re-imported or updated, even if the email addresses are the same. There is also no trace of the changes once they are made, making it hard to audit mistakes. A more time intensive, but careful, approach is to use a Lookup Table as described in the Advanced topic: Performing the lookup table strategy to count and merge duplicate email addresses.

Advanced topic

Performing the lookup table strategy to count and merge duplicate email addresses

Once you have imported email data into the Edges worksheet and chosen Show Graph, you can navigate to the Vertices worksheet where there is now a list of all of the unique email addresses. Copy that list to a new worksheet and title the column Original_Addresses. Create a new column next to it called New_Addresses. When you find duplicate addresses for the same person in the Original_Addresses column, repeat the desired address in the New_Addresses column. An example is provided in the Lookup_Addresses table shown in Figure 9.4.

You can now use a VLOOKUP function to look up the New_Addresses that correspond with the Original_Addresses to create a new edge list with no duplicate email addresses. Copy the original edge list from the Edges worksheet (columns D and E) and create two new columns for a new edge list (columns F and G). The New Edge List columns will be automatically populated by the results of a VLOOKUP function. Figure 9.4 shows an example in Cell F9. The VLOOKUP function looks for the original Vertex1 address found in cell D9 ([email protected] shown in the blue outlined rectangle) within the first column of the Lookup Addresses table (cells $A$3:$B$7 shown in a red outlined rectangle). In this example, it finds an exact match in cell A7. It then returns the value of the second column in the Lookup table (because the number 2 was entered into the VLOOKUP formula), which is [email protected] found in cell B7. The FALSE in the VLOOKUP formula specifies that the value that is looked up must exactly match a value in the first column of the Lookup_Addresses table. An error is returned if an exact match is not found. Once the New Edge List is created, you can copy the new Vertex1 and Vertex2 columns (F and G) and use Excel's Paste Special feature to paste their values into a new workbook. You should also copy and paste the Edge Weights column from the original file after assuring that the Vertex1 and Vertex2 columns are in the exact same order as the original data.

VLOOKUP functions can use considerable computational resources when working with large files, so once they are calculated you may want to copy them, choose Paste Special, and select Values so they do not need to be recalculated each time changes to the workbook are made.

9.6.2 Count and merge duplicate edges

Once you have updated the email addresses in the Edges worksheet so that different addresses for the same person are replaced with a single address, you will likely have duplicate edges (e.g., more than one row that have the same values in the Vertex1 and Vertex2 columns). It can be useful to “roll up” these duplicate edges, replacing multiple connections between a pair of email addresses with a single edge. The rolled up edge has a weight equal to the total number of exchanged messages found in the data. It is important to roll up the data so that network metrics can be accurately calculated, as some of them assume that edges connecting any pair of vertices are unique. To prepare your email network for analysis, you can roll up repeated email messages from the same pair of people using the Count and Merge Duplicate Edges feature in the Prepare Data section of the NodeXL Ribbon. This will merge the duplicate edges and sum up the Edge Weights so the total Edge Weight remains the same. Before doing this, make sure the network type is set to Directed, or else it will remove the directed nature of the graph. Figure 9.5 shows the New Edge List from Figure 9.4 in Columns A and B and a merged version of it in Columns E and F shown here to illustrate the results of the Count and Merge Duplicate Edges feature. Notice that the total of the Edge Weight column is the same.

Sometimes people send email messages to themselves as a reminder, as a To Do list, or to share a file between computers. This results in a row with the same address in the Vertex1 and Vertex2 columns on the Edges worksheet and is called a self-loop. The use of multiple email addresses and the removal of duplicate addresses can also cause self-loops. Row 9 of Figure 9.4 is an example. For many analyses these self-loops are not important and can be distracting when visualizing data or calculating network metrics. You may want to remove self-loop edges such as the red pair in Figure 9.5 as an additional step after you have counted merged duplicate edges. See Advanced Topic: Automatically identifying self-loops.

9.8.1 TechABC's organizational unit email network

In this section you will analyze a sample of email traffic from a large global technology company we'll call TechABC. The company has > 100,000 employees in dozens of countries and hundreds of locations. Employees are aggregated into roughly 10,000 organizational units that have an average of 15 members. Organizational names in the visualizations have been anonymized. For privacy reasons we cannot provide the dataset.

People in each organizational unit send and receive email from people within their own unit as well as to people in other units. These events were logged in the corporate email server and were extracted for a weeklong period. An edge list of events in which an employee sent an email to another employee in the To, Cc, or Bcc fields was created. Data about each employee were then removed and replaced with the name of the organizational unit in which they were a member, helping address individual privacy concerns. Data were then aggregated (see Section 9.6.2), creating an edge weight that represents the number of messages sent from one unit to another. This process rolls messages exchanged between members of the same unit into self-loops, where the sending unit and receiving unit are the same. The total number of internally exchanged messages can be useful, but is best treated as attribute data on the Vertices worksheet rather than captured in the edge list.

9.8.2 Normalizing and filtering TechABC's data

Whole graph maps of enterprise networks are likely to be too large and dense to be informative. For example, TechABC's raw sent email network includes > 1.3 million edges and around 10,000 vertices. A process of filtering and selective display is required to peel away parts of the network that obscure structures of interest (see Chapter 7). When working with large datasets such as TechABC's, you may want to perform the first round of filtering using a database program like Microsoft Access because of size limitations in Excel.

A common edge filtering technique is to remove all connections below a threshold, helping whittle away infrequent ties to reveal the strong core skeletal structures of the company. The easiest threshold to use is the raw number of messages sent between units. However, because organizational units differ in size, this approach disadvantages smaller units with fewer members contributing to the number of messages. To account for this discrepancy, you can normalize the data by creating a new edge variable based on the number of messages sent per employee (e.g., per full-time equivalent or FTE). You'll need to decide if you want to use the number of FTEs from the sending unit, receiving unit, or some combination of the two. For the graph shown in Figure 9.8, we removed edges with fewer than 50 messages per FTE sent in a week, where we used the minimum of the sender and receiver FTE values as the denominator. This approach keeps an edge if it is important (i.e., a high number of emails per FTE) to either the sending or receiving unit (see the U.S. Senate co-voting example in Chapter 7 for another illustration of a similar technique). The resulting, filtered TechABC network includes 2303 edges and 2267 vertices. Figure 9.9 uses a similar approach, but because it focuses on a subset of units (only research units), the threshold was lowered to 10 messages per FTE sent in a week.

You could also normalize the data by calculating the number of messages sent from one unit to another unit as a percentage of all messages sent from the unit. This approach accounts for differences in a unit's overall email usage patterns, which can be desirable in some cases. For example, it would remove edges representing company announcements from a single unit (e.g., the human resources or information technology department) because the messages sent to any one unit would be a small percentage of the sending unit's overall sent messages. As with the prior example, you will need to decide if you want to use the sending or receiving unit's total message count as the denominator, or some combination of the two (e.g., maximum, minimum, average).

Other strategies for filtering data can lead to other insights. For example, showing only weak ties (edges with between 3 and 10 messages per FTE) can highlight lesser-known connections that might guide management efforts to improve connections across gaps in the company. Attributes of organizational units can be used to filter the network graph as well, helping to zoom into subsections of the larger graph. For example, you could remove all but the most central groups to reveal the network of core groups while hiding more peripheral groups. You can also focus on units within a particular department, geographic location, or similar mission. You will see this approach used in our second example (Figure 9.9), which looks at connections between research units of TechABC.

9.8.3 Creating an overview of TechABC's communication patterns

You may want to create an overview graph of an organization's email communication before moving into more detailed analyses of specific departments or groups. Overview graphs can be difficult to read because of their size. However, they are excellent for dynamically exploring by sorting on metric properties to identify important units, highlighting vertices of interest and seeing their connections on the graph, and using dynamic filters to further hone in on specific areas of interest. A highly filtered overview of TechABC is shown in Figure 9.8. Only edges with > 50 messages per FTE are shown, with additional filtering to show only the main component. You can think of this as the backbone of the company.

This graph and the accompanying data tell interesting stories about the company. Overall, the graph is sparse, largely because of the high filtering threshold we have chosen. The graph density is very low, suggesting that most units only communicate heavily with one or two other units. The average geodesic distance is 10.2 and maximum geodesic distance (i.e., diameter) is 29, both of which are quite high. If high numbers existed at a lower threshold, it would suggest that units may not be well connected with certain other units on the other “side” of the company. Increasing connections between otherwise disconnected groups may be a goal for an organization. For example, many organizations have created “communities of practice” consisting of people with similar skills who are scattered throughout different organizational units. An initiative to increase connections throughout the company could be evaluated by looking for increases in the network density and decreases in the diameter over time.

In addition to looking at global trends, it is possible to look at the role of individual units in the company. Even with the highly filtered graph shown in Figure 9.8, it is possible to identify several hubs (with high out-degree), some densely connected clusters, and units that act as bridges between other units. Some of these fill critical locations in the network, demonstrating their unique value that results from their network position. Organizational units that are connected to many other units (i.e., the hubs) perform services like IT management or library services that touch many parts of the company. Groups that are less connected but have a high betweenness centrality likely have coordination functions, bridging information between multiple groups such as specific geographical units within a larger region. Isolated groups and clusters of groups are likely specialists that perform a function for one or a few other groups to consume. Analyzing networks like the one visualized in Figure 9.8 also allows you to compare units that serve a similar function to see how they compare on various metrics, helping to identify those that could benefit from additional connections.

9.8.4 Examining TechABC's research division

Although overview maps like Figure 9.8 can be helpful, they can also be cluttered and may filter out too much of the detail for large companies. To gain actionable insights, you will typically need to focus on subsections of the network, such as units that serve a similar purpose (e.g., IT, marketing, research). In this section you will explore the organizational units within TechABC that have a research mission. They were identified by looking for organizational unit names with the word “research” in them using Microsoft Excel's Search function (a non-case-sensitive function similar to the Find function).

Although you can restrict the network analysis to only the core units that meet your criteria (i.e., research units), it is often insightful to include all units connected to the core units. For example, Figure 9.9 includes all research units (maroon boxes), as well as all units they sent or received messages to (blue disks). A cutoff point of 10 messages per FTE was used in order to account for unit size differences. Because the focus is on the research units, connections between the non-research units are not shown. The result is a collection of all of the 1.0-degree networks of the research units. To create a similar graph, we added a new column called Research to the Edges worksheet that is a 1 if either Vertex1 or Vertex2 is a research unit and a 0 otherwise. This can be set to Edge Visibility Equal to 1 (in the Research column) using the Autofill Columns feature to exclude all other edges.

The network highlights several important bridge-spanning units, as well as some disconnected units that may need to be connected. For example, research unit Specific 6 plays an important role in connecting several other research groups either directly or indirectly. The organizational unit Specific 10 is important because it is the only path connecting the large Specific 2 unit to the other research units (albeit indirectly). There are also several non-research groups that play pivotal bridge spanning roles, such as the very small unit just above General 17 that is connected to six different research groups, none of which is directly connected to another. This small unit likely plays an important role and its small size may make it vulnerable to employee turnover, suggesting that the company may consider if additional resources are needed to support the group's function. In contrast, the network shows Market Research 1 and 2 in completely different components, not even connected indirectly. More generally, few research units are directly connected to each other, suggesting that there may be potential for increased exchanges through employee swaps, internships, or other shared projects. This assumes there would be benefits from interdisciplinary projects, which content experts would need to determine. Although many of the actionable insights require knowledge about the organization, Figure 9.9 gives you some idea of the potential benefits of this type of analysis.

9.9.1 Identifying key individuals using content networks

One problem historians and lawyers face is identifying individuals who played key roles in important events. For employees that use email frequently, email networks provide a quick sense of who communicates with whom. Filtering email collections to include only those that use a particular keyword or set of words is a useful method for finding people related to some event.

You can see an example of this by analyzing the Enron email network of messages that include the term “FERC,” the commonly used acronym for the Federal Energy Regulatory Commission, an “independent agency that regulates the interstate transmission of natural gas, oil, and electricity” (see www.ferc.gov). To create this FERC network, you can use the NodeXL import tools, making sure to filter messages to include those that have “FERC” in the body of the message. The resulting network includes 370 vertices representing employee email addresses and 672 weighted edges. This is a smaller subset of the Enron message network tagged by UC Berkeley students that includes 1803 edges and 1102 vertices. The total sent and received FERC messages⁸ are included on the Vertices worksheet (see Advanced topic: Calculating total sent and received edges), along with a column called %_Received, which equals Received/(Sent + Received).

Once you calculate the graph metrics, you can use them to create a graph such as Figure 9.10 designed to highlight important individuals. The graph sets the size of each vertex based on in-degree, because those receiving FERC messages from many different individuals are likely “go to” people. Vertex color is based on the %_Received data, with greener vertices indicating that the individual received many messages but did not send out many. Individuals with something to hide may not send out messages, suggesting that focusing on the large green vertices may lead to potential violators. Indeed, one of these vertices represents Tim Belden, the head of trading in Enron Energy Services considered by many to be the mastermind of Enron's scheme to drive up energy prices in California. Belden pleaded guilty to one count of conspiracy to commit wire fraud as part of a plea bargain and ended up serving as a key witness against many top Enron executives.

Although visualizations like Figure 9.10 can help identify individuals worth following up on, they should be used cautiously. In this particular example, there are no messages sent from Tim Belden in the dataset, making it unclear if his high received ratio is due to his actual email usage patterns, purposefully deleted messages, or limitations with the original dataset. Even if the data accurately reflects actual email patterns, Figure 9.10 is imperfect in that it emphasizes many individuals aside from Tim Belden who were not accused of illegal activities. Furthermore, many of those found guilty of crimes were not included in this graph at all, perhaps because they recognized the liability of using email for sensitive communication or perhaps because of limitations in the dataset. Clearly, reading the content of the messages is of utmost importance. However, viewing the network can help identify individuals and messages of interest. Once an individual is known to be involved, mining email is an effective way to identify people with whom the suspect frequently interacts. For example, Figure 9.10 shows a strong connection from John Shelk to Tim Belden (and many other recipients), which is explained by the fact that John Shelk often reported on congressional meetings but rarely received replies to his reports. Integrating the content with network visualization tools can provide a powerful exploratory platform, as has been done with the Enron network dataset [4].