Video is HD Capable
16 minute technical analysis is here: https://www.youtube.com/watch?v=mp5NaDlP-Ac

The solution created is 100% open source, even GSA spreadsheets were edited using OpenOffice.

This web solution is responsive/tablet ready and mobile ready with an amazing mobile edition, as well as having a desktop Google Page Speed Ranking over 90+, which is effectively Google signing off on coding professional efficiency to display content quickly to the end user.

Answers to the questions posed is listed @ http://testserver1901.3owl.com/2014/02/this-is-great-but-give-me-advice-based-on-d3/ (and below).

Use the biggest and most powerful open source network to plan future CMS installations: http://trends.builtwith.com/cms

Solution has a five-vector attack on data to derive total omniscience via D3.JS, DC.JS, Visualizer, MySQL RDBMS/Graphical UI, TablePress Spreadsheets, wrapped in a collaborative environment to display this information, as well as store it long term.

Six different graphing applications, two with advanced and customized JavaScript libraries, spreadsheet embedding, MySQL edits and updates, document repositories, voting and version control, automated BI publications. Log-in security and automatic banning on incorrect passwords, automatic emails to the administrator, multi-level password protections to the main distribution, database optimizing scripts, best practice management studios. Enterprise scale and security.

Solution requires a browser of IE9+ or FireFox circa 2009+ (which 90% of agencies have currently). http://testserver1901.3owl.com/

Code being used is JavaScript, HTML5, PHP 5.3, MySQL 5.5, CSV files, MySQL Workbench 6.1. All code, libraries, applications, including the travel data warehouse portion is certified open source, both GPL and Apache 2.0. Web-server OS is being run off of full open source Apache 2.0 certified Linux / Apache / PHP, FTP client used is FileZilla, also open source, and website browsed and check using all browsers, but predominantly used FireFox (open source). Solution can be repackaged in a matter of minutes into a complex zip file using open source software, then redeployed using a custom PHP script (included in section titled duplicator).

A short video reminding us to not forget Open Government
https://www.youtube.com/watch?v=Bq_ZWl1ZXA0

Are travelers booking airline reservations far in advance to secure low cost airfare? How many days in advance are travelers booking their trips, taking into consideration industry standards and benchmarks? For example, is there a correlation between booking time and cost? --The ARC report that tickets purchased six weeks in advance save an average of 5.8% – Did GSA Employees follow this best practice? While the average difference in purchase confirmation to departure was found to be 3.75 days, this data is incorrect as numerous data points showed ticket purchases after departures, 1485 of these occurrences is inside the dataset sample reservation data. When these values are removed and the average is recalculated, the buy ahead day metric stands at just 4.8 days in advance. When applying this to the sum prices, if the GSA required tickets to be purchased six weeks ahead of time, they would have saved the invoice prices of air travel on tax payers $935,551 since late 2010.

Are travelers utilizing travel services, such as FedRooms®? --Room Codes titled “GOV” were cheaper than other codes, in the set provided with an average rate of $77 a night.

Are travelers booking online? --Travellers are booking online 48% of the time, to offline 52% over 2011-2013 Hotel data, these statistics are flipped for airline ticket purchasing, but overall, GSA employees are split evenly in their use of the internet to purchase travel requirements. Booking online correlates to a savings of $10 a flight. The correlation here does not imply causation. Hotels booked offline cost $97 while online purchased hotels cost $94 a 3.4% savings. If GSA employees purchased online strictly they would save [.07*.48*1,273,887] which equates to $22,586 over the sample size, all else equal, While purchasing an airline ticket online if calculated the same method (percent saved not # of tickets by dollar) would save 3.4% or $264.136.5 over the set period, all else equal. Correlation does not mean causation. In sum, if GSA employees were restricted to only online purchases for travel, there is a correlation of savings in excess of $300,000 during 2011-2013 years.

With regard to data visibility issues, is key data being missed? Highlight where data is missing, e.g., where a traveler may have not used our existing systems, therefore, data is lacking. --A key issue is there may be corrupt data currently in the spreadsheets. The sheets provided show some of this one year into the other, some with dates ranging into 1995 and nearly millions of dollars in early 2010 listed in the 2011 spreadsheet.

What data elements are missing that could be valuable to an agency travel manager or chief financial officer? --The goals are one in the same, to drive omniscience through data and data visualization. The data missing here is not really an issue for me, the issue is conceptualizing it easily without useless columns so as to present it to decision makers in an easy to consume fashion. Analysts providing data analytics under SES and leadership must use PivotTables and triple check their spreadsheet work before presenting it to management (Note: LibreOffice and OpenOffice are open source office suites that also have these features).

How much could an agency save if they adjusted one or a set of cost-driving behaviors such as, time of year of travel, booking online, travel to certain cities during certain times, booking in advance? --You can safely estimate over a million dollars in savings over the next set period i.e. from 2014-2017 by applying these simple best practices (all else equal i.e. CPI). GSA should perhaps not separate these data sets into Travel Card and Voucher purchases, and we should drill into what they purchased specifically as well, this should merely be an indication column T or V in a single table within whatever DB you are using, and no splitting of years should be done into separate sheets either in my view. The Voucher data puts Planes and Trains in the same column, for an analytics spreadsheet on travel data this can cloud the data type, as no method specific plane or train data can be pulled from this column. The Travel Card Average Hotel 2013 Cost is $25 while the average travel voucher Hotel 2013 cost is $170, which is curious. There are concerns with the quality and completeness of this data. For instance, the card usage doesn’t account for meals at all, which was the third largest cost on the voucher spreadsheet for 2013. That is the biggest gap for data perhaps. Here is a great tip, check out PowerPivot - it works with most major databases and it is free if you have Microsoft Excel. Make an ODBC connection to MySQL from Excel with the PowerPivot addin, and now you have big data pulls easily, free.

If GSA were to book their flights at the so-called “Magic Number” of 54 days in advance, they could realize further gains beyond the ARC calculations, all else equal into the millions of dollars over the same period late 2010 to 2013.

CheapAir spent the last year analyzing over four million airline trips. They tracked ticket prices from 320 days before takeoff all the way up until the day before, calculating precisely which day each one hit its lowest point.

“magic numbers” for international flights

Europe: 151 days before your flight Asia: 129 days before your flight The Caribbean: 101 days before your flight Mexico: 89 days before your flight Latin America: 80 days before your flight

There is a missing column in the sample travel voucher data FY 11 – FY 13 on sheet 2011 missing (expected “Total Estimate”). There is missing data like ticket date purchases, or tickets purchased with no price.

Thanks to,

Ben Balter - Matt Mullenweg - Mike Bostock - Jason Davies - PageLines - Madpixels - LAMP Community - Git Community - Nick Zhu

Share this project:
×

Updates