O J Blackbourn

— PROJECTS COMPANIES


- Google Fiber

- Minnesota Transport

- Athens Airbnb


— ROLE

Business Intelligence


— DATE

2024

Laid out below are the Google business intelligence projects that I have carried out using SQL, Big Query and Tableau. This involved data modelling, analysis, screens and custom dashboards for an end client.


The projects cover these areas:


Google Fiber customer service problem resolution


Minnesota Traffic Department interstate infrastructure resolution


Airbnb Athens accommodation hotspots and pricing



Tools


I intensively used the Google data warehouse tool BigQuery, SQL and the Salesforce visualisation software Tableau Public.


Process: CAM

Business Intelligence Process stages


CAPTURE


ANALYSE


MONITOR


Projects


As part of Google training I carried out 3 Google business intelligence projects in early 2024. These projects covered these areas –


- Google Fiber customer service repeat calls pattern resolution covering key markets and key problems types and trends, covering problems resolution.


- Minnesota Transportation Department interstate traffic volume and patterns. Trends in travel over time periods, national holidays and under weather patterns.


- Airbnb Accommodation hotspots and pricing. Trends in vacancies, areas and costs.


Google Fiber




Background


About the company

Google Fiber/GFiber is a high-speed broadband internet service that uses fiber optic cable, and wave tech to deliver fast internet right to homes and businesses.

Started by Google, Google Fiber Inc. is now a subsidiary of Alphabet and services a growing number of households in 13 cities in 10 states across the United States. In 2024, Google Fiber is estimated to deliver internet to about 4.1 million people.


Products


Residential and business internet provider, with broadband services with both fiber-optic and fixed wireless technology. Customer service department focus for this project.


Scenario

Google business intelligence:

The stakeholder is the Google Fiber Customer Service Team. The team needs to understand how often customers phone customer support again after their first inquiry; this will help leaders understand whether the team is able to answer customer questions the first time.

Further, leaders want to explore trends in repeat calls to

identify why customers are having to call more than once, as well as how to

improve the overall customer experience. I will create a dashboard to reveal

insights about repeat callers. 


Problem Statement

The team wants to answer these questions:


  • - How often does the customer service team receive repeat calls from customers?
  • - What problem types generate the most repeat calls?
  • - Which market city’s customer service team receives the most repeat calls?


Business Case

- Understanding repeat calls helps to pinpoint a plethora of issues.

- Firstly understand volumes and the landscape

- Understand the problem major issues causing calls

- See which customer teams are carrying which calls

- Improve resolution to increase customer satisfaction

- Increase training and resources to increase not only first call resolution but also product satisfaction.

- Motivate staff

- Feed the information back to product and documentation teams

- Update FAQs


Goals/Metrics

The team’s ultimate goal is to reduce call volume by increasing customer satisfaction and improving operational optimization. My dashboard should demonstrate an understanding of this goal and provide stakeholders with insights about repeat caller volumes in different markets and the types of problems they represent. 


Dashboard Dataset

The project data consisted of 3 separate datasets. These were imported into Big Query, checked and via Union All were combined into one dataset. This CSV dataset was then imported into Tableau for visualisation.


Dashboard and results

As an early project I decided to give a give a multi set dashboard. This combined not only a day of the week and weekly operational level but also an annual level. This makes it easy for all levels to see what is happening at several different points. Monthly and annual results are quite different.

The client can then pick options and allocate levels. I anticipate that the annual page would not be available for some users.

See also the executive summary.


The dashboard and executive summary can be seen below.

Minnesota Traffic Patterns

Background


About the company

The Minnesota Department of Transportation oversees transportation by all modes including land, water, air, rail, walking and bicycling in the U.S. state of Minnesota. The cabinet-level agency is responsible for maintaining the state's trunk highway system, funding municipal airports and maintaining radio navigation aids, and other activities. It was established in 1976.


Products

Government services.


Scenario

Google business intelligence:

The stakeholder is the Minnesota Department of Transport.

The brief is in creating a business intelligence visualization to help the Minnesota Department of Transportation improve highway infrastructure.


• Interstate traffic volume continues to grow

• There is no end in sight to this

• Infrastructure is limited and needs to be utilised carefully

• Future infrastructure needs to be planned strategically


Problem Statement

The team wants to answer these questions:


  • - Traffic volume throughout the year; ideally organized by year, month, -week, day, and hour
  • - Traffic volume in different weather conditions
  • - Traffic volume on different holidays

Business Case

Knowing traffic patterns under these conditions will help the team make important infrastructure decisions. These decisions will ensure that any construction in the future won’t cause problems for drivers.


Goals/Metrics

The team’s ultimate goal is to optimise infrastructure for traffic.


Dashboard Dataset

The project data consisted of data supplied by the state and dated from 2015 onwards.


Dashboard and results

  • Some very specific requests came in. The data was to be from 2017 onwards, with a clear filter and download options.


The chart design guidelines were laid out as follows:


  • - Chart 1: Traffic Volume and Date Time
  • - Chart 2: Traffic Volume, Date Time, and Weather Main
  • - Chart 3: Traffic Volume, Date Time, and a custom measure for Holidays


 The traffic analysis covered 3 areas:

 

• Monthly volumes with an annual benchmark

• Weather effects

• Holiday travel spikes


Results


• Upgrades and repairs to roads need to be carried out at low traffic cycles in winter in all conditions apart from snowy periods. A surprise result of the data was that despite annual data showing that snowy periods have low traffic, the winter month level data shows that it does not deter drivers. Storms, rain, fog, haze and drizzle do however deter drivers.


• High density months require all lanes and exits/entrances. This is August, spring and fall.


• Further research into snowy months and effects on traffic – particularly the Jan/Feb drop off.


• Not all holidays have the same effects. Some major holidays see interstate travel after or before, these are Christmas and Thanksgiving, people want to either be with their families or are taking local roads. Labor Day, MLK Day and Memorial Day are the biggest traffic causers, along with Independence Day and New Year’s.

See the results in this presentation.



Transport 1st implementation, larger version and full year sets. Highlight markers for the hours volume on the right. This was good but oversized and unpractical. Each chart has a filter. This was replaced later with 1 filter for all by month,

Second version. Heatmap markers for the hours volume on the right, allowing a tighter dashboard.

Year sets reduced to 2015 – Filters in place. Links to the year overview.

Weather traffic year sheet linked to the front page. This is available on click for the annual view.

Athens Accommodation

Background


About the company

An accommodation business that purchases properties in an

area, converts them into rentals, and offers them to people on vacation in

Athens, Greece.


Products

Athenian holiday accommodation


Scenario

Find data insights on accommodation trends. Airbnb data is the starting point.


Problem Statement

Answer these questions


  • - What is the average price per night in each neighbourhood?
  • - Where in Athens are the highest concentrations of currently-available rentals?


Business Case (what are you

solving for)

Buying the right properties and pricing in a smart way to make a profit and enable a portfolio buildout.


Goals/Metrics

Insights by neighbourhood and by accommodation type. Vacancy rates. Price vs availability. Simple easy graphs and map intelligence.


Dataset/results

Your contact gives you some recent data from Airbnb about the

rentals in the city. This data includes the price to stay at each listing, the

durations of each stay, the locations of these rentals, and more.


Some areas were massively more expensive than others. This outlier was left in as it provides the most interesting information.


There was useful information about where the hotspots are and the filters made it easy to get key pricing and availability trends.

What was not so apparent is any clear link across the board about high prices and high availability. In some neighbourhoods yes, in others no.




You can use this layout block to write as much or little as you'd like.


You may also paste in embed codes from popular services like Vimeo, SketchFab, and YouTube.

My Journey

Journey

My journey to business intelligence is somewhat different to other students on this course. I originally studied history and the analysis that goes with that, then moved into tourism and tourism information provision. Followed by several years in the UK civil service and intelligence analysis in Berlin for some very big corporations. I worked in marketing for many years and specialised in research analysis, a perfect combination of my several degrees in analysis, business and information management. I also took several course in website management, editing, content, online marketing etc


By the end of the 2010s I decided to join a team working in web admin, community management and weekly email provision. I did that for several years for a major international school and became the team leader. This was very enjoyable and involved a lot of quick reaction web work, content management and graphics. When the chance arose to take a data course I immediately knew that it was a great way for me to add a lot to my skill set and specialise in design. It has been a real eye opener.


I have been able to use my previous research analysis skills to look very deeply at competitor research and my previous postgrad masters experience in interviewing people and getting results to here deal with the different research parts of this project.


I will now be looking to use my skills in a job in data, intelligence work and marketing.


Data is something that is really exciting is an opportunity to stretch oneself in all sorts of new directions.


Attributions

Attributions

Eugene Golovesov on Unsplash

Erol Ahmed on Unsplash


Under construction pictures attributed on the UX Project page with thanks






Awards

  • It’s Nice That
  • AIGA
  • Fonts In Use
  • The Dieline

Contact

email@domain.com

000-000-000


— Instagram

— Twitter

— Facebook