Entry Name: VRVIS-Vuckovic-MC2
VAST Challenge 2021
Mini-Challenge 2
Team
Members:
Milena Vuckovic, VRVis,
vuckovic@vrvis.at PRIMARY
Johanna Schmidt, VRVis,
schmidt@vrvis.at
Student
Team: NO
Tools
Used:
Microsoft Power BI
Excel
QGIS
Adobe Illustrator
Approximately how many hours were
spent working on this submission in total?
40 hours
May we post your submission in the Visual Analytics
Benchmark Repository after VAST Challenge 2021 is complete? YES
Video
As a visual analytics expert assisting law enforcement, your
mission is to identify which GASTech employees made
which purchases and identify suspicious patterns of behavior. You must cope
with uncertainties that result from missing, conflicting, and imperfect data to
make recommendations for further investigation.
Use visual analytics to
analyze the available data and develop responses to the questions below. In
addition, prepare a video that shows how you used visual analytics to solve
this challenge. Submission instructions are available here. Entry forms are
available for download below.
Questions
1 –– Using just the credit and loyalty card data, identify the most popular
locations, and when they are popular. What anomalies do you see? What
corrections would you recommend to correct these anomalies? Please limit your
answer to 8 images and 300 words.
We approached the challenge by using the existing
functionalities and graphical visualizations of Microsoft Power BI (for visual
analytics) to create unique multifaceted visualizations, enhanced with Adobe
Illustrator (for graphic finalization), that emulate drill down functionality
in a static (image) format.
We identified 4 of the most
frequently visited locations (Fig.1.1 composite radial chart, Fig.1.2 composite
bar chart), out of which 3 (Katerina’s Café, Hippokampos,
Guy's Gyros) are being popular on both working and weekend days (Fig.1.3) and
are also being visited during similar hours over the observed period (Fig.1.4
composite radial chart). The fourth location (Brew've Been Served) is being regularly visited on
working days alone (no weekend visits), but only during the morning hours
(Fig.1.3, 1.4).
Looking at the both credit and loyalty card data, we observed anomalies in price
values between two dataset. We noted some different sums recorded for, what we matched to be, the same credit and loyalty card
transactions (illustrated for ccnum 7384 / L3800 in
Fig.1.5). The errors were recurrent for the tens place in two- and
three-digit numbers (e.g., 52.56 vs. 12.56), but being of a different degree
(e.g., 20, 30, 40). Furthermore, not all transactions were matching, while
missing entries were noted for both credit card and
loyalty card data (Fig.1.5).
To correct these anomalies, we would derive a subset of those
credit/loyalty card instances where errors were noted (e.g., ccnum 7384 / L3800) and further look into those pairs that
had no errors. We would derive summaries about tendencies and regularities
using descriptive statistics, to assert which are the most common spending
behaviors and later adjust the original dataset based on this knowledge. This
would allow us to identify the correct transaction records. For the missing
entries, we would combine both datasets, thus having a larger pool of records.
2 – Add
the vehicle data to your analysis of the credit and loyalty card data. How does
your assessment of the anomalies in question 1 change
based on this new data? What discrepancies between vehicle, credit, and loyalty
card data do you find? Please limit your answer to 8 images and 500 words.
The main discrepancy we noticed is that probably not all the
customers whose transactions are recorded in the
credit/loyalty card data are employees of GAStech.
This observation is based on a clearly noted inconsistency between the number
of GAStech employees in the car-assignments dataset (total of 44 employees), loyalty card data (54 unique IDs),
and credit card data (55 unique credit cards) (Fig. 2.1). Here we can also see
the discrepancy in recorded amount of unique car IDs, credit and loyalty card
numbers as per their daily usage (Fig. 2.1).
We should note that not all the vehicle IDs are recorded in
the GPS and car assignment datasets (truck IDs are incomplete). However, some
additional car IDs (starting from 101) are found in the GPS dataset, which we
assume to belong to the trucks. However, not all the trucks are
being monitored and equipped by a GPS device, as per records from the GPS
dataset. Namely, 4 truck IDs are missing and
only IDs 101, 104-107 are given in the GPS dataset (9 trucks in total). We
additionally noted that not all the cars’ movements are being monitored from
the starting date (June 6th). For example, the car ID
31 is only being tracked from June 17th, while car ID
18 has a missing or a non-driving day on June 12th (weekend). Trucks are not being driven over the weekend.
We also noted a time-based misalignment of car
movement/location data (from GPS dataset) and credit cards usage, illustrated
for daytime hours (7 AM to 9 PM) for 3 consecutive
days in Fig. 2.2. This may indicate stopping at a certain location and making a
transaction at the end of the immobile period (e.g., stopping for lunch). The
obvious regular peak in credit card usage between 12 noon and 1 PM might
further support this fact. In some other cases, this may point to potential
errors in log times for either credit card transactions or GPS records. To
illustrate, we noticed some instances where the recorded credit card
transaction date and time appear to be wrong. This is clearly
evident for the location Kronos Mart, as all the transactions are done
at the early morning hours (i.e., 3 AM and 9 AM), while the car movement data
indicate visitations to this particular location at 3PM and 9PM on the day
before the transaction was recorded (illustrated for car ID 32 in Fig. 2.3).
The error appears to be regular and shifted for 12 hours. Furthermore, in case
of 4 locations, transactions were always recorded at
12 noon (Fig 2.4), even if the visits were happening at other times.
For this purpose, we needed to derive a number of sample
points of interest (POI). We used the QGIS software where we cross-referenced
the geospatial map of Abila with street names and provided tourist map of
Abila. However, this method may not bring the desired level of precision and
may introduce some uncertainties when locations under study are in a close
vicinity.
3 – Can
you infer the owners of each credit card and loyalty card? What is your
evidence? Where are there uncertainties in your
method? Where are there uncertainties in the data?
Please limit your answer to 8 images and 500 words.
To answer this question, we built a custom dashboard in Power
BI that combines spatial maps and diverse visuals, with time-based,
location-based and ID-based filters (Fig. 3.1). These are highly responsive to
changes/selections across all views, which allowed us to focus on specific localities,
observe moving/stopping timeframes and transaction patterns, and identify
matching events.
We followed 2 approaches:
location-based and time-based.
For approach 1, we began isolating some of the locations
using the location-based filters (i.e., longitude/latitude) to declutter the
pool of potential customers frequenting the highlighted locations. To
illustrate, consider the case of Abila airport (Fig. 3.1 A) where we
immediately identified 3 distinct car IDs – 101, 104,
106 (Fig. 3.1 B). We cross-referenced this information with credit card data
(Fig. 3.1 C) and noticed 5 unique credit card numbers
recorded at the same location (Fig. 3.1 D). We also noticed that out of these 3 car IDs, one individual (ID 104) was visiting the airport
only (Fig. 3.2 A). We further brought in the time dimension of each transaction
and aligned this to the car movement data (Fig. 3.2 B, C).
We noticed that the ccnum 8642 was only used at Abila airport at the exact times when car
ID 104 was there (Fig. 3.2 C, B). Hence, we paired the ID 104 with ccnum 8642 and L2769. While answering the question 1, we
established the relationships between the credit/loyalty card data based on
price and location, which helped us identified all credit card/loyalty card
pairs (illustrated on Abila airport case in Fig. 3.3).
Due to the limited number of retrieved points of interest
(POI), we also followed the time-based approach explained below.
For approach 2, we started by focusing on both weekends (June
11th-12th, 18th-19th) and noticed the same movement pattern of car ID 5: from a
home location to Ouzeri Elian (illustrated for June
11th-12th in Fig 3.4 A). These visits aligned with a usage pattern of
credit/loyalty card 6899/L6267 (Fig 3.4 B). To validate this, we looked at
other transactions of the card 6899 and found another match for e.g. Katerina’s
Café (Fig 3.5).
Using both approaches, we continued identifying all matching
pairs. To illustrate, we matched the pairs for the Executives:
ID4/ccnum7688/L4164; ID10/ccnum8332/L2070; ID31/ccnum5010/L2459;
ID32/ccnum8156/L5224; ID35/ccnum2463/L6886.
The first uncertainty in the underlying data is the fact that
not all the vehicles’ movements are being monitored, and some not even from the
starting date (June 6th). The second uncertainty comes
from the previously noted missing credit card records, which may align with
visits to a particular location. Lastly, the free interpretation of likely
position of POIs may not be as precise as if these coordinates would be originally provided. As mentioned before, the POI
retrieval method may not bring the desired level of precision and may introduce
some uncertainties when locations under study are in a close vicinity.
4 –– Given the data sources provided, identify potential informal or unofficial relationships among GASTech personnel. Provide evidence for these relationships. Please limit your response to 8 images and 500 words.
To assert the degree of potential relationships between GAStech personnel, we built another custom dashboard in
Power BI, with paired views of spatial maps (colored by car ID and by time),
time-based movement frequency distribution and employee information (Fig. 4.1).
One map has an overlay of spatial points indicating the identified POIs. We
used these paired views to isolate/track distinct movements of numerous
vehicles in different times and localities.
Looking at the GPS data and the car movement patterns, we
noticed many employees taking breakfast at same locations and same time, thus
we assumed they were going there together (illustrated in Fig. 4.1 A, B for one
sample location). We also noticed employees taking a lunch break during the
working week in the period between 12:30 and 1:30 PM (Fig. 4.2 A). By isolating
both periods during the working week, we promptly observed some frequently
visited locations (Fig. 4.2 B, heatmap).
To exemplify, on June 6th 3 particular vehicles (IDs 11, 23,
24) visited the same lunch location and stayed there during the same time
period (Fig. 4.3, here we reverted back to location points for the lower map),
having also identical paths in this period. We assumed these cars were meeting.
To validate this assumption, we looked at their movement patterns during the
remaining days and noticed these particular car IDs were meeting quite
frequently during the lunchtime hours at various locations, but interchangeably
(sometimes paired IDs 11 / 23, other times IDs 23 / 24). And during these
times, they were also meeting other employees. We
further noticed that car IDs 3 and 27 went together to
diverse lunch locations for four consecutive days at the same time (illustrated
on one location in Fig. 4.4).
Looking at the nighttime, we noticed some personnel having
the same home location. Specifically, car IDs 6, 25, 29. They also tend to frequently go to the same location for dinner (Katerina’s
Café) and at the same time (Fig. 4.5). The same can be said
for IDs 14 and 18, which have the same home location and quite frequently go
together to Katerina’s Café for dinner, and often while IDs 6, 25, 29 are there
(Fig. 4.5). Thus, we assumed they have informal relationship. The same can be said for car IDs 13, 15, 16, as they also appear to
have the same home location and frequently visit the same breakfast and evening
locations (Fig. 4.6). We also concluded that car IDs 17, 24, 33 have the same
home location. However, we noticed that these individuals were more prone to go
to dinner/evening events together, rather than lunch events (Fig. 4.7).
Lastly, looking at the weekend period, we noticed Executives
going to the golf course together (Fig. 4.8), on both days when this location
had recorded transactions (June 12th and 19th). We also
noticed they favor going to evening events together at the same location and
always around the same time during the evening.
5 –– Do
you see evidence of suspicious activity? Identify 1-
10 locations where you believe the suspicious activity is occurring, and
why Please limit your response to 10
images and 500 words.
To find potentially suspicious activities, we used the built
dashboards to analyze nighttime activities in the period between 11 PM and 4 AM
(Fig. 5.1). We immediately noticed some amount of movement, with especially
significant activity levels on June 10th after 11 PM. Concerning this event, a
large number of employees visited the same location (identified as home of ID
2) and spend the same amount of time there (Fig. 5.2).
We also noticed that car ID 1 often goes to GAStech on late night and stays there until early morning
hours, which might be classified as suspicious as no one else visits GAStech during night hours (Fig 5.3 A). Often, these visits
are connected with a previous visit to Ouzeri Elian and presumably Hippokampos,
where some kind of exchange might be happening. This employee also engaged in
the June 10th event (Fig 5.2).
We also noticed general suspicious activities involving car
IDs 15, 16, 21, 24. They often also went to 2 locations ID 1 visited and at the same time.
On the night/morning of June 6th/7th, IDs 15 and 16 went to
what is previously identified as the home location of
ID 10 (Fig. 5.4). Afterwards, they had breakfast at the location other than IDs
15 and 16 normally go to, although ID 10 arrived before and left as IDs 15 and
16 came. IDs 15 and 16 didn’t pay, as they might have
been with someone else.
Later, we noticed car ID 15 meeting up with ID 24 in
presumably Taxiarchon Park between June 8th midnight and 9th early morning. ID 24 came around
midnight and left at 3.40 AM, while ID 15 came to the same location at 3.32 AM
and left at 7.20 AM (Fig. 5.5). Again, ID 15 had breakfast on June 9th at the location different than usual and didn’t pay.
On June 10th night, ID 16 visited home location of ID 35, and
left around 3.30 AM (June 11th). As ID 16 left, ID 21 came to home location of
ID 35, after leaving from home location of ID 16 to which this individual came
on the night before (Fig. 5.6). Actually, home location of ID 21 was
inconclusive as this individual often spent nights at home location of IDs 13,
15, 16, but also IDs 14, 18. ID 21 was also regularly going to home location of
IDs 14 and 18 between 5.30 and 7 PM (Fig. 5.7).
On June 13th night, ID 21 came to Spetson
Park around 11 PM and left on the following morning at 3.30 AM, when ID 24 came
to the same location and stayed until 7.50 AM (Fig. 5.8). Afterwards, ID 24 had
breakfast at the same location IDs 15 and 16 had on June 7th,
not the usual breakfast location for ID 24 and also didn’t pay.
6 –– If
you solved this mini-challenge in 2014, how did you approach it differently
this year?
We did not solve
this challenge in 2014.