This repository is for a web application that aims to provide users with information about the consumer-friendliness of financial institutions, products, and services. The application was built with Ruby on Rails, Bootstrap, HTML/CSS, Oracle Database, and a hefty amount of SQL.
- The
database.ymlfile (located atsetup/database.yml) must include valid credentials for an Oracle database. Drop this file inSites/complaintapp/config. - The data required for the database is provided on Data.gov by the Consumer Financial Protection Bureau. After pre-processing the data, and then importing it into the Oracle database, the following table was obtained. Note that, within the app's code, the table is referred to as
camoen.complaint, so that all Oracle users with permissions can appropriately access the data.
- For easier querying, product and service types in the database are grouped into views. The
setup/oracle_setup.sqlfile contains the setup of these views, as well as the commands required to create and grant permissions for other Oracle users.
- Download Oracle Instant Client (32 bit) and add it to the system PATH variables.
- Install Ruby on Rails. Ruby 2.3 Installer worked well at the time of development.
- Node.js may also be required.
- Once all setup is complete, the server can be started via using the
rails scommand from Command Prompt or Git Bash (while inside the local directory).
This page includes descriptions and links to the results of six predefined queries. Additionally, the custom search feature, which provides users with the ability to filter for specific data, is located at the bottom of the page.
This page is reached by clicking the "Company Rankings" button. An SQL query is ran to determine the 5 least consumer-friendly companies for each year, as measured by their average monthly number of complaints. Then, data for all years is gathered for each company that ever appears in this top 5. Data is returned both as a line graph, allowing for easy visualization of the data, and as a table. To reduce clutter, every line or bar in the returned graphs can be toggled on or off by clicking its related label.
This page is reached by clicking the "Product Rankings" button. The average monthly number of complaints is computed and returned for each of the financial products and services in the database.
This page is reached by clicking the "Timeliness Rankings" button. There are two sections on this page, both of which return the five worst-performing companies from each year. The first section ranks companies by their yearly number of untimely responses, and the second section ranks companies by the percentage of their responses that were untimely.
This page is reached by clicking the "Dispute Rankings" button. There are two sections on this page, both of which return the five worst-performing companies from each year. The first section ranks companies by their yearly number of disputed responses, and the second section ranks companies by the percentage of their responses that were disputed.
This page is reached by selecting a company (in this case, EQUIFAX, INC.) and clicking the "Company Deep Dive" button. Note that the dropdown list allows the selection of any company that exists in the database—on the initial page load, this list is dynamically generated from the results of an SQL query. Monthly counts of complaints received by the selected company are charted for each year of data available in the database. This feature allows the user to look for trends in an institution's performance over time.
This page is reached by selecting a product or service (in this case, Credit Reporting) and clicking the "Product Deep Dive" button. Monthly counts of complaints received about a particular product are charted for each year of data available in the database. This feature allows the user to look for trends in a product's performance over time.
This feature, located at the bottom of the search directory page, aims to provide users with the ability to filter for results that interest them. Results may be returned in one of eight different ways, depending on the user's selected categories:
1) Only company selections: All data, for all selected companies, will be used in the generation of results.
2) Only product/service selections: All data, for all selected products or services, will be used in the generation of results.
3) No selections: All data will be used in the generation of results, but only the five least consumer-friendly companies for any given year will be returned. If a company only appears in the "top 5 worst performers" for one year, all of its data (for every year) will still be returned.
4) Both company and product selections: For every selected company, only complaints about the selected products or services will be used to generate results. For instance, assume that "Banking", "American Express Company", and "Discover Bank" have all been selected. When results are generated, the only complaints about American Express and Discover that will be utilized are those related to Banking. All other complaints that have been lodged about the selected companies (for example, those about credit cards) will be ignored.
Date Range: Each of these four options can return "dated" or "undated" results, based on the user's selection of a date range—this effectively doubles the number of ways that results may be returned. Undated results utilize all data in the database, and the returned results include an average monthly complaint number for every year. Dated results return explicit monthly complaint counts for every month and year represented in the database.
Filters: The "Complaint Submission Method", "Demographic", and "State Selection" filters narrow down the data used when generating results, but don't lead to any further variation in the way results are returned. For example, if "E-mail" and "Fax" are selected, only those complaints received via e-mail or fax will be considered. Note that the "Older American" and "Service Member" demographics overlap, so the "Not Older American" and "Not Service Member" filters are provided in case the user wishes to completely ignore the complaints of either demographic. The default behavior (when all filters are unselected) is to utilize all data in the database. This also applies if, for example, submission method and demographic filters are chosen, but a state filter is not selected. In this scenario, only complaints matching the submission method and demographic filters will be utilized, but data from all states will still be considered.
Similar to the Company Deep Dive dropdown list, the "Company Selection" category is dynamically populated when the page loads, to ensure that every company in the database is represented. The list is in alphabetical order but, due to the list's size, it's recommended to use Ctrl+F to locate a particular company.
For all "dated" results, the full range of dates has been selected. This range can be narrowed in accordance with the user's specific interests.
The selected companies are "American Express Company", "Bank of America, National Association", "Discover Bank", "Equifax, Inc.", "Experian Information Solutions Inc.", and "Transunion Intermediate Holdings, Inc."
The same companies are selected as in the previous results.
This is the same as the Product Rankings results, but it includes only the selected products.
In this example, all products and services have been selected.
This is the same as the Company Rankings results.
The selected companies are "American Express Company", "Bank of America, National Association", "Discover Bank", "Equifax, Inc.", "Experian Information Solutions Inc.", and "Transunion Intermediate Holdings, Inc." The selected products are "Credit Card" and "Credit Reporting"—only complaints about these two products will be considered in the results.
The same companies and products have been chosen as in the previous results.
