Repository files navigation

Over $2 billion has been raised using the massively successful crowdfunding service, Kickstarter, but not every project has found success. Of the more than 300,000 projects launched on Kickstarter, only a third have made it through the funding process with a positive outcome.

Getting funded on Kickstarter requires meeting or exceeding the project's initial goal, so many organizations spend months looking through past projects in an attempt to discover some trick for finding success. For this week's homework, you will organize and analyze a database of 4,000 past projects in order to uncover any hidden trends.

1. Create a new space for this project calledexcel-challenge in either DropBox or Google Drive.Do not add this homework to an existing space.

2. Store your excel workbooks in here and create a sharable link for submission.

Using the Excel table provided, modify and analyze the data of 4,000 past Kickstarter projects as you attempt to uncover some market trends.

• Use conditional formatting to fill each cell in thestate column with a different color, depending on whether the associated campaign was successful, failed, or canceled, or is currently live.

  • Create a new column O calledPercent Funded that uses a formula to uncover how much money a campaign made to reach its initial goal.

• Use conditional formatting to fill each cell in thePercent Funded column using a three-color scale. The scale should start at 0 and be a dark shade of red, transitioning to green at 100, and blue at 200.

  • Create a new column P calledAverage Donation that uses a formula to uncover how much each backer for the project paid on average.

  • Create two new columns, one calledCategory at Q and another calledSub-Category at R, which use formulas to split theCategory and Sub-Category column into two parts.

  

  • Create a new sheet with a pivot table that will analyze your initial worksheet to count how many campaigns were successful, failed, canceled, or are currently live percategory.

  • Create a stacked column pivot chart that can be filtered by country based on the table you have created.

  

  • Create a new sheet with a pivot table that will analyze your initial sheet to count how many campaigns were successful, failed, or canceled, or are currently live persub-category.

  • Create a stacked column pivot chart that can be filtered by country and parent-category based on the table you have created.

• The dates stored within thedeadline andlaunched_at columns use Unix timestamps. Fortunately for us,there is a formula that can be used to convert these timestamps to a normal date.

  • Create a new column namedDate Created Conversion that will usethis formula to convert the data contained withinlaunched_at into Excel's date format.

  • Create a new column namedDate Ended Conversion that will usethis formula to convert the data contained withindeadline into Excel's date format.

  

  • Create a new sheet with a pivot table with a column ofstate, rows ofDate Created Conversion, values based on the count ofstate, and filters based onparent category andYears.

  • Now create a pivot chart line graph that visualizes this new table.

• Create a report in Microsoft Word and answer the following questions.

1. Given the provided data, what are three conclusions we can draw about Kickstarter campaigns?
2. What are some limitations of this dataset?
3. What are some other possible tables and/or graphs that we could create?

• Create a new sheet with 8 columns:

  • Goal
  • Number Successful
  • Number Failed
  • Number Canceled
  • Total Projects
  • Percentage Successful
  • Percentage Failed
  • Percentage Canceled

• In theGoal column, create 12 rows with the following headers:

  • Less than 1000
  • 1000 to 4999
  • 5000 to 9999
  • 10000 to 14999
  • 15000 to 19999
  • 20000 to 24999
  • 25000 to 29999
  • 30000 to 34999
  • 35000 to 39999
  • 40000 to 44999
  • 45000 to 49999
  • Greater than or equal to 50000

  

• Using theCOUNTIFS() formula, count how many successful, failed, and canceled projects were created with goals within the ranges listed above. Populate theNumber Successful,Number Failed, andNumber Canceled columns with this data.

• Add up each of the values in theNumber Successful,Number Failed, andNumber Canceled columns to populate theTotal Projects column. Then, using a mathematical formula, find the percentage of projects that were successful, failed, or canceled per goal range.

• Create a line chart that graphs the relationship between a goal's amount and its chances at success, failure, or cancellation.

If one were to describe a successful crowdfunding campaign, most people would use the number of campaign backers as a metric of success. One of the most efficient ways that data scientists characterize a quantitative metric, such as the number of campaign backers, is by creating a summary statistics table.

For those looking for an additional challenge, you will evaluate the number of backers of successful and unsuccessful campaigns by creatingyour own summary statistics table.

• Create a new worksheet in your workbook, and create a column each for the number of backers of successful campaigns and unsuccessful campaigns.

  

• Use Excel to evaluate the following for successful campaigns, and then for unsuccessful campaigns:

  • The mean number of backers.

  • The median number of backers.

  • The minimum number of backers.

  • The maximum number of backers.

  • The variance of the number of backers.

  • The standard deviation of the number of backers.

• Use your data to determine whether the mean or the median summarizes the data more meaningfully.

• Use your data to determine if there is more variability with successful or unsuccessful campaigns. Does this make sense? Why or why not?

• To submit your homework, upload the solution and files to a GitHub repo and submit the link tohttps://bootcampspot.com/.

Students who are marked as employer-ready gain access to our employer referral program, additional workshops, and other resources. Work with your Career Director to become employer-ready. At a minimum, you must have:

• A clear, concise, and compelling resume. Submit via your learning platform for review.
• A polished GitHub profile:
  • 3 - 6 pinned repositories (instructions here)
  • at least 20 commits per repository
  • professional titles, i.e. not "Homework #1"
  • thorough README.md files for each repository
  • clean code

© 2019 Trilogy Education Services