Week 4: Course 2 end-of-course project

Overview

When you approach a project using structured thinking, you will often find that there are specific steps you need to complete in a specific order. The end-of-course projects in the Google Business Intelligence certificate were designed with this in mind. The challenges presented in each course represent a single milestone within an entire project, based on the skills and concepts learned in that course. 

The certificate program allows you to choose from different workplace scenarios to complete the end-of-course projects: the Cyclistic bike share company or Google Fiber. Each scenario offers you an opportunity to refine your skills and create artifacts to share on the job market in an online portfolio. 

You will be practicing similar skills regardless of which scenario you choose, but you must complete at least one end-of-course project for each course to earn your Google Business Intelligence certificate. To have a cohesive experience, it is recommended that you choose the same scenario for each end-of-course project. For example, if you chose the Cyclistic scenario to complete in Course 1, we recommend completing this same scenario in Course 2 and 3 as well. However, if you are interested in more than one workplace scenario or would like more of a challenge, you are welcome to do more than one end-of-course project. Completing multiple projects offers you additional practice and examples you can share with prospective employers.

Course 2 end-of-course project scenarios

Cyclistic bike-share

Background: 

In this fictitious workplace scenario, the imaginary company Cyclistic has partnered with the city of New York to provide shared bikes. Currently, there are bike stations located throughout Manhattan and neighboring boroughs. Customers are able to rent bikes for easy travel among stations at these locations. 

Scenario:

You are a newly hired BI professional at Cyclistic. The company’s Customer Growth Team is creating a business plan for next year. They want to understand how their customers are using their bikes; their top priority is identifying customer demand at different station locations. Previously, you gathered information from your meeting notes and completed important project planning documents. Now you are ready for the next part of your project! 

Course 2 challenge:

• Use project planning documents to identify key metrics and dashboard requirements
• Observe stakeholders in action to better understand how they use data
• Gather and combine necessary data
• Design reporting tables that can be uploaded to Tableau to create the final dashboard

Note: The story, as well as all names, characters, and incidents portrayed, are fictitious. No identification with actual people (living or deceased) is intended or should be inferred. The data shared in this project has been created for pedagogical purposes.

Google Fiber

Background: 

Google Fiber provides people and businesses with fiber optic internet. Currently, the customer service team working in their call centers answers calls from customers in their established service areas. In this fictional scenario, the team is interested in exploring trends in repeat calls to reduce the number of times customers have to call in order for an issue to be resolved. 

Scenario:

You are currently interviewing for a BI position on the Google Fiber call center team. As part of the interview process, they ask you to develop a dashboard tool that allows them to explore trends in repeat calls. The team needs to understand how often customers call customer support after their first inquiry. This will help leadership understand how effectively the team can answer customer questions the first time. Previously, you gathered information from your meeting notes and completed important project planning documents. Now you’re ready for the next part of your project! 

Course 2 challenge:

• Use project planning documents to identify key metrics and dashboard requirements
• Consider best tools to execute your project
• Gather and combine necessary data
• Design reporting tables that can be uploaded to Tableau to create the final dashboard

Key Takeaways

In Course 2, The Path to Insights: Data Models and Pipelines, you focused on understanding how data is stored, transformed, and delivered in a BI environment. 

Course 2 skills:

• Combine and transform data
• Identify key metrics
• Create target tables
• Practice working with BI tools

Course 2 end-of-course project deliverables: 

• The necessary target tables

Now that you have completed this step of your project and developed the target tables, you are ready to work on your final dashboard in the next course!

Previously,  you started working with a fictional bike-share company, Cyclistic, to provide their team with key business intelligence insights. At the end of the last course, you consulted with stakeholders to develop project planning documents that establish their needs and expectations. The strategy and planning documents are key to helping you understand important details about this project, so you’ll want to make sure you have completed this step before moving on. Refer to the exemplar documents here: Activity Exemplar: Complete the business intelligence project documents for Cyclistic. 

Use these exemplars to ensure that your own project planning documents are completed with enough detail to move on to the next part of the project. 

Coming up, you are going to build on previous work to combine data from the tables you received for this project into one reporting table you will use to develop a dashboard you can share with stakeholders. The activities will guide you through uploading the data into your own project space, using SQL code in Dataflow or BigQuery, observing how stakeholders interact with data, and finalizing a reporting table to be used for the dashboard. There will be an exemplar project for you to review so you can check your work and continue to iterate on your own project.

This next step in your project will provide more opportunities to create additional workplace samples you can share with potential employers to demonstrate your BI skills and knowledge. This is also a great opportunity to practice the skills you have been learning so far. 

Key takeaways 

The end-of-course project is designed for you to practice and apply course skills in a fictional workplace scenario. By completing each course’s end-of-course project, you will have work examples that will enhance your portfolio and showcase your skills for future employers.

Understanding your stakeholders and how they use the data is key to developing business intelligence solutions that will address their specific needs. In addition to meeting with stakeholders to discuss their project requirements, it can also be useful to observe the team at work and identify any patterns or frequently asked questions. 

In this reading, you’ll discover some of the ways that the Cyclistic team uses the data. This can inform the BI solution you develop for them and help you design the final reporting dashboard to be specific to their needs and useful to the whole team. As a BI professional, recognizing a team’s needs and customizing their systems can support their work as they make key business decisions. 

The team at work

As you learned during your previous meeting with Cyclistic, the product development team has begun planning for the next year of Cyclistic’s bike-sharing program. Cyclistic’s Customer Growth Team is creating a business plan for next year. The team wants to understand how their customers are using their bikes; their top priority is identifying customer demand at different station locations. The Cyclistic team posed an important primary question:

• How can we apply customer usage insights to inform new station growth?

Answering these questions starts with the data from the Cyclistic bikes themselves, which the team has provided you, and the reporting dashboard the team uses to gain insights. In addition to the explicit requests the stakeholders made, you realize a few key things about the team’s current processes. 

First, you realize that there are stakeholders from a variety of different departments accessing and using this data with different levels of technical expertise. There are stakeholders from these teams:

• Product development
• Customer data
• Engineering
• Data analytics
• Data warehousing
• API
• IT
• Cyclistic executive
• Project management

For example, you realize that Earnest Cox, the VP of product development, is often requesting high-level insights into the data and rarely needs detailed overviews of the data. Alternatively, Tessa Blackwell from the data analytics team does explore the data in-depth and spends a lot more time reviewing the dashboard views. As you develop your reporting tools, you will want to find a way to answer both of these stakeholders’ needs. 

Additionally, one of your coworkers finds out you’re working on this project and shares a dataset they created recently for a project of their own that they think might help you: NYC zip codes. This dataset provides the zip codes for the different neighborhoods and boroughs in New York City; this will let you compare the bike data to the weather data more easily since you will be able to match the locations more accurately. It will also help you develop your map visualization later on.  

Key takeaways

As you prepare to create the pipeline system that will deliver data to your reporting tables and eventually your dashboard, recognizing the different kinds of users, their specific needs and questions, and how they are currently using the data can help guide your development process. In this case, observing the Cyclistic team in action revealed that there are users who need different levels of detail and have different technical abilities. Additionally, you gained a useful tool from a colleague that will help you explore multiple datasets for this project. You can use these discoveries to design a BI solution that really addresses this organization’s unique needs–and demonstrate your skill and flexibility to future employers!

Activity Overview

In this activity, you will use your knowledge of SQL and potentially Google Dataflow to combine and move the key datasets you identified for the Cyclistic project into a target table. This represents the extraction phase of an ETL pipeline, when data is pulled from different sources and moved to its destination. You will use the table you create in this activity to develop the final dashboard for stakeholders. As you complete this activity, remember to refer to the previous work you did when  completing the business intelligence project documents for Cyclistic  for details about the Cyclistic project, as well as the activity to  create a target table in BigQuery  for a refresher on target tables. 

Be sure to complete this activity before moving on. The next course item will provide you with a completed exemplar to compare to your own work. You will not be able to access the exemplar until you have completed this activity.

Scenario

Review the following scenario. Then, complete the activity. As a reminder, the end-of-course project activities are more open to your personal interpretation than other activities in this program. This is to give you an opportunity to practice the skills you have been learning in your own way. If you need help or feel stuck, you can always discuss your work with other learners in the discussion forums or review the exemplar to help guide your process.

The product development team at Cyclistic has begun developing their business plan for next year. In order to build a better Cyclistic, the team needs to understand how customers are currently using the bikes, how location and other factors impact demand, and what stations get the most traffic. The Cyclistic team has a few goals:

• Understand current customers needs, what makes a successful product, and how new stations might alleviate demand in different geographical areas
• Understand current usage of bikes at different locations 
• Apply customer usage insights to inform new station growth
• Understand how different users (subscribers and non-subscribers) use the bikes

You met with stakeholders to complete project planning documents and uploaded the necessary tables into your BigQuery project space.

Instructions

Follow the instructions and answer the following question to complete the activity. Then, go to the next course item to compare your work to a completed exemplar.

Step 1: Log into your GCP tool

To begin this activity, log into your Google Cloud account and navigate to the BigQuery console. You can complete this activity using the BigQuery Sandbox, which does not require a Google Cloud billing account. You can learn more about enabling the Sandbox from the BigQuery help guide. You can also use Dataflow to execute SQL code as a Job by navigating to the Dataflow console instead; this will require you to have a Google Cloud account. Both tools are useful for this project, so choose the tool you are more interested in working with for this project.

Step 2: Querying your data

For this step, keep in mind the key metrics you and your stakeholders have identified, their business questions, and what data you’ll need to develop the final dashboard. Previously, you explored the different public datasets your stakeholders provided and uploaded the zip code table your colleague shared with you. For the final dashboard, you will need to create two target tables: a table to capture the entire year and a table that focuses on summer trends. Here is an example of a query to capture a table with data from the entire year:

SELECT
  TRI.usertype,
  ZIPSTART.zip_code AS zip_code_start,
  ZIPSTARTNAME.borough borough_start,
  ZIPSTARTNAME.neighborhood AS neighborhood_start,
  ZIPEND.zip_code AS zip_code_end,
  ZIPENDNAME.borough borough_end,
  ZIPENDNAME.neighborhood AS neighborhood_end,
  -- Since this is a fictional dashboard, you can add 5 years to make it look recent
  DATE_ADD(DATE(TRI.starttime), INTERVAL 5 YEAR) AS start_day,
  DATE_ADD(DATE(TRI.stoptime), INTERVAL 5 YEAR) AS stop_day,
  WEA.temp AS day_mean_temperature, -- Mean temp
  WEA.wdsp AS day_mean_wind_speed, -- Mean wind speed
  WEA.prcp day_total_precipitation, -- Total precipitation
  -- Group trips into 10 minute intervals to reduces the number of rows
  ROUND(CAST(TRI.tripduration / 60 AS INT64), -1) AS trip_minutes,
  COUNT(TRI.bikeid) AS trip_count
FROM
  `bigquery-public-data.new_york_citibike.citibike_trips` AS TRI
INNER JOIN
  `bigquery-public-data.geo_us_boundaries.zip_codes` ZIPSTART
  ON ST_WITHIN(
    ST_GEOGPOINT(TRI.start_station_longitude, TRI.start_station_latitude),
    ZIPSTART.zip_code_geom)
INNER JOIN
  `bigquery-public-data.geo_us_boundaries.zip_codes` ZIPEND
  ON ST_WITHIN(
    ST_GEOGPOINT(TRI.end_station_longitude, TRI.end_station_latitude),
    ZIPEND.zip_code_geom)
INNER JOIN
  `bigquery-public-data.noaa_gsod.gsod20*` AS WEA
  ON PARSE_DATE("%Y%m%d", CONCAT(WEA.year, WEA.mo, WEA.da)) = DATE(TRI.starttime)
INNER JOIN
  -- Note! Add your zip code table name, enclosed in backticks: `example_table`
  `(insert your table name) zipcodes` AS ZIPSTARTNAME
  ON ZIPSTART.zip_code = CAST(ZIPSTARTNAME.zip AS STRING)
INNER JOIN
  -- Note! Add your zipcode table name, enclosed in backticks: `example_table`
  `(insert your table name) zipcodes` AS ZIPENDNAME
  ON ZIPEND.zip_code = CAST(ZIPENDNAME.zip AS STRING)
WHERE
  -- This takes the weather data from one weather station
  WEA.wban = '94728' -- NEW YORK CENTRAL PARK
  -- Use data from 2014 and 2015
  AND EXTRACT(YEAR FROM DATE(TRI.starttime)) BETWEEN 2014 AND 2015
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  12,
  13

Note that this query includes a DATE_ADD function to add five years to the data. The public data you are using to create this dashboard is from 2014 and 2015, so this is a way to make your dashboard appear more recent. Normally, you would not change the dates in a dataset, but because this is a fictional project, you can include this in your own query. This part of the query is optional; however, your exemplar will appear differently if you don’t include it. You will need to develop a similar query to capture a table with data from July through September to explore summer trends specifically.

Step 3: Finish the job

Once you execute the code, it will take a few moments to process. After the query has finished running, you will be able to download the tables as CSV files by using the Save Results dropdown and selecting the appropriate file type.

This might take a few minutes. Once you have downloaded the table, you will be ready to upload it to Tableau to create your dashboard!

What to Include in Your Response

Be sure to address the following criteria:

• Necessary tables are successfully combined into summary tables
• Appropriate tables are downloaded and ready to upload to Tableau

In this activity, you created target tables to consolidate and store the data you pulled from the Cyclistic datasets. These tables will allow you to develop a dashboard using Tableau in the upcoming end-of-course project activities in the next course. As a BI professional, you will need to be able to use programs such as BigQuery and Dataflow to move and analyze data with SQL. This end-of-course project showcases your ability to do just that.

The exemplar you are about to review will help you evaluate whether you completed the activity correctly. In this case, you might have discovered a solution that works just as well as the exemplar. That’s great! This exemplar is an example of how a BI professional might have approached this challenge. As long as your process achieved the same results, you can move on to the next phase of the project. 

If you find that the result you received is different from the exemplar provided, use the exemplar to iterate and adjust your own code.

Exploring the exemplar code

For this activity, you could run the following SQL query to create a summary table for the entire year:

SELECT
TRI.usertype,
 ZIPSTART.zip_code AS zip_code_start,
 ZIPSTARTNAME.borough borough_start,
 ZIPSTARTNAME.neighborhood AS neighborhood_start,
  ZIPEND.zip_code AS zip_code_end,
  ZIPENDNAME.borough borough_end,
 ZIPENDNAME.neighborhood AS neighborhood_end,
  DATE_ADD(DATE(TRI.starttime), INTERVAL 5 YEAR) AS start_day,
  DATE_ADD(DATE(TRI.stoptime), INTERVAL 5 YEAR) AS stop_day,
  WEA.temp AS day_mean_temperature, -- Mean temp
 WEA.wdsp AS day_mean_wind_speed, -- Mean wind speed
  WEA.prcp day_total_precipitation, -- Total precipitation
 -- Group trips into 10 minute intervals to reduces the number of rows
  ROUND(CAST(TRI.tripduration / 60 AS INT64), -1) AS trip_minutes,
  COUNT(TRI.bikeid) AS trip_count
FROM
 `bigquery-public-data.new_york_citibike.citibike_trips` AS TRI
INNER JOIN
  `bigquery-public-data.geo_us_boundaries.zip_codes` ZIPSTART
 ON ST_WITHIN(
 ST_GEOGPOINT(TRI.start_station_longitude, TRI.start_station_latitude),
ZIPSTART.zip_code_geom)
INNER JOIN
  `bigquery-public-data.geo_us_boundaries.zip_codes` ZIPEND
  ON ST_WITHIN(
ST_GEOGPOINT(TRI.end_station_longitude, TRI.end_station_latitude),
ZIPEND.zip_code_geom)
INNER JOIN
`bigquery-public-data.noaa_gsod.gsod20*` AS WEA
  ON PARSE_DATE("%Y%m%d", CONCAT(WEA.year, WEA.mo, WEA.da)) = DATE(TRI.starttime)
INNER JOIN
  -- Note! Add your zip code table name, enclosed in backticks: `example_table`
  `(insert your table name) zipcodes` AS ZIPSTARTNAME
  ON ZIPSTART.zip_code = CAST(ZIPSTARTNAME.zip AS STRING)
INNER JOIN
  -- Note! Add your zipcode table name, enclosed in backticks: `example_table`
  `(insert your table name) zipcodes` AS ZIPENDNAME
  ON ZIPEND.zip_code = CAST(ZIPENDNAME.zip AS STRING)
WHERE
 -- This takes the weather data from one weather station
 WEA.wban = '94728' -- NEW YORK CENTRAL PARK
 -- Use data from 2014 and 2015
 AND EXTRACT(YEAR FROM DATE(TRI.starttime)) BETWEEN 2014 AND 2015
GROUP BY
 1, 
 2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10,
  11,
  12,
  13

The result of this query is a merged target table that JOINs the public datasets and the zip code table you uploaded. 

Additionally, you needed to execute a query that captured data from just the summer season:

SELECT
 TRI.usertype,
 TRI.start_station_longitude,
 TRI.start_station_latitude,
 TRI.end_station_longitude,
 TRI.end_station_latitude,
 ZIPSTART.zip_code AS zip_code_start,
 ZIPSTARTNAME.borough borough_start,
 ZIPSTARTNAME.neighborhood AS neighborhood_start,
 ZIPEND.zip_code AS zip_code_end,
  ZIPENDNAME.borough borough_end,
  ZIPENDNAME.neighborhood AS neighborhood_end,
 -- Since we're using trips from 2014 and 2015, we will add 5 years to make it look recent
  DATE_ADD(DATE(TRI.starttime), INTERVAL 5 YEAR) AS start_day,
 DATE_ADD(DATE(TRI.stoptime), INTERVAL 5 YEAR) AS stop_day,
  WEA.temp AS day_mean_temperature, -- Mean temp
 WEA.wdsp AS day_mean_wind_speed, -- Mean wind speed
  WEA.prcp day_total_precipitation, -- Total precipitation
  -- We will group trips into 10 minute intervals, which also reduces the number of 
rowsROUND(CAST(TRI.tripduration / 60 AS INT64), -1) AS trip_minutes,
 TRI.bikeid
FROM  
 `bigquery-public-data.new_york_citibike.citibike_trips` AS TRI
INNER JOIN
`bigquery-public-data.geo_us_boundaries.zip_codes` ZIPSTART
ON ST_WITHIN(
ST_GEOGPOINT(TRI.start_station_longitude, TRI.start_station_latitude),
 ZIPSTART.zip_code_geom)
INNER JOIN
`bigquery-public-data.geo_us_boundaries.zip_codes` ZIPEND
ON ST_WITHIN(
 ST_GEOGPOINT(TRI.end_station_longitude, TRI.end_station_latitude),
ZIPEND.zip_code_geom)
INNER JOIN
 -- https://pantheon.corp.google.com/bigquery?p=bigquery-public-data&d=noaa_gsod
 `bigquery-public-data.noaa_gsod.gsod20*` AS WEA
 ON PARSE_DATE("%Y%m%d", CONCAT(WEA.year, WEA.mo, WEA.da)) = DATE(TRI.starttime)
INNER JOIN
-- Note! Add your zipcode table name, enclosed in backticks: `example_table`
`legalbi.sandbox.zipcodes` AS ZIPSTARTNAME
ON ZIPSTART.zip_code = CAST(ZIPSTARTNAME.zip AS STRING)
INNER JOIN
 -- Note! Add your zipcode table name below, enclosed in backticks: `example_table`
  `legalbi.sandbox.zipcodes` AS ZIPENDNAME
   ON ZIPEND.zip_code = CAST(ZIPENDNAME.zip AS STRING)
WHERE
-- Take the weather from one weather station
  WEA.wban = '94728' -- NEW YORK CENTRAL PARK
 -- Use data for three summer months
AND DATE(TRI.starttime) BETWEEN DATE('2015-07-01') AND DATE('2015-09-30')

This query results into a similar table as the previous query, except it focuses on trends from July through September.

Key takeaways

Storing data from multiple sources in target tables allows you to access and use consolidated data for reporting purposes. In the Course 3 end-of-course project, you will use the table you’ve created in this activity to design a dashboard and share insights with the Cyclistic product development team in order to help guide their process and make informed decisions.

Previously,  you interviewed with the customer call center team at Google Fiber,  a provider of fiber optic internet. Your interviewers asked you to complete a mock project based on the work performed by their business intelligence team. At the end of the last course, you consulted with stakeholders to develop project planning documents that establish their needs and expectations. The strategy and planning documents are key to helping you understand important details about this project, so you’ll want to make sure you have completed this step before moving on. Refer to the exemplar documents here: Activity Exemplar: Complete the business intelligence project documents for Google Fiber. Use these exemplars to ensure that your own documents are completed with enough detail to move on to the next part of the project. 

Coming up, you are going to build on previous work to combine data from the tables into one reporting table you will use to develop a dashboard for stakeholders. The activities will guide you through uploading the data into your own project space, using SQL code in Dataflow or BigQuery, observing how stakeholders interact with data, and finalizing a reporting table to be used for the dashboard. There will be an exemplar project for you to review so you can check your work and continue to iterate on your own project.

This next step in your project will provide more opportunities to create additional workplace samples you can share with potential employers to demonstrate your BI skills and knowledge. This is also a great opportunity to practice the skills you have been learning so far. 

Key takeaways

The end-of-course project is designed for you to practice and apply course skills in a fictional workplace scenario. By completing each course’s end-of-course project, you will have work examples that will enhance your portfolio and showcase your skills for future employers. 

In this activity, you created a target table to consolidate and store the Google Fiber datasets. This table will allow you to develop a dashboard using Tableau in the upcoming end-of-course project activities in the next course. As a BI professional, you will need to be able to use programs such as BigQuery and Dataflow to move and analyze data with SQL. This end-of-course project showcases your ability to do just that.

The exemplar you are about to review will help you evaluate whether you completed the activity correctly. In this case, you might have discovered a solution that works just as well as the exemplar. That’s great! This exemplar is an example of how a BI professional might have approached this challenge. As long as your process achieved the same results, you can move on to the next phase of the project. 

If you find that the result you received is different from the exemplar provided, use the exemplar to iterate and adjust your own code. 

Exploring the exemplar code

For this activity, you could run the following SQL query to create a single combined table that merged all three of the datasets your were given:

SELECT
  date_created,
  contacts_n,
  contacts_n_1,
  contacts_n_2,
  contacts_n_3,
  contacts_n_4,
  contacts_n_5,
  contacts_n_6,
  contacts_n_7,
  new_type,
  new_market
FROM `your project.fiber.market_1`
UNION ALL
SELECT
  date_created,
  contacts_n,
  contacts_n_1,
  contacts_n_2,
  contacts_n_3,
  contacts_n_4,
  contacts_n_5,
  contacts_n_6,
  contacts_n_7,
  new_type,
  new_market
FROM `your project.fiber.market_2`
UNION ALL
SELECT
  date_created,
  contacts_n,
  contacts_n_1,
  contacts_n_2,
  contacts_n_3,
  contacts_n_4,
  contacts_n_5,
  contacts_n_6,
  contacts_n_7,
  new_type,
  new_market
FROM `your project.market_3`

The UNION ALL statement is applied here instead of a JOIN statement because the tables already have matching columns, making them easy to merge completely. 

After you have run this query, you should have a combined table like this:

Create a target table for Google Fiber exemplar

Key takeaways

Storing data from multiple sources in target tables allows you to access and use consolidated data for reporting purposes. In the Course 3 end-of-course project, you will use the table you’ve created in this activity to design a dashboard and share insights with the Google Fiber stakeholders in order to help guide their process and make informed decisions.

Coming up next…

Congratulations on completing another course in the Google Business Intelligence Certificate! In this part of the program, you learned more about data modeling and ETL processes for extracting data from source systems, and transforming it into formats that better enable analysis and drive business processes and goals.

The entire program has three courses: 

1. Foundations of Business Intelligence: In this course, you will discover the role of business intelligence professionals within an organization, as well as typical career paths. You will explore core BI practices and tools and how BI professionals use them to make an impact.
2. The Path to Insights: Data Models and Pipelines: This is the course you’ve just completed. 
3. Decisions, Decisions: Dashboards and Reports: In this course, you will apply your knowledge of business intelligence and data modeling to create dynamic dashboards that track KPIs to meet stakeholder needs.

Now that you have completed this course, you are ready to move on! To continue with the program, you can go to the next course by clicking this link: Decisions, Decisions: Dashboards and Reports.

Keep up the great work!