Recently, we have added our Google Adword and other Marketing spend in to Prism and have linked this with our bookings, giving a ROI (Return on Investment) at Company Level, Destination, Resort and Hotel Level, so we use Prism to drill down showing the Gross Profit and Cost down to Hotel Level, we also then link in competitor pricing data in to the report also, this by far is the most useful report in the Business so worth some sort of mention.
There is a bit of history to this report, we contracted a Developer who had been working for the company for a while to use the Google Adword API to get to the stage where we can use this data linked with our booking data to automatically adjust bid prices, and also use this to add new campaigns etc, and the dashboard reporting was a big part of the development, we use MongoDB within the business for storing user sessions and some caching stuff as its great for handling big data of that type and is widely used for this purpose within the travel industry, and the developer decided to build the Adword data in to MongoDB from the API and also build the booking data in to MongoDB from the SQL database as this is fairly big data and thought using this technology would allow fast reporting. He built an entire website to handle this process and created reporting on the website, this whole process took about 9 Months and then the contractor decided to leave (for tax purposes as he was coming up to 2 years continually working in the same place), there was then a process for about 4-6 weeks of other developers and the MD (who has a Developer background) trying to get this MongoDB & website to report how we wanted it.
So 11 months down the line I and Prism got involved, I had to come up with a way of getting the Google Adword data into SQL and in the format required with additional information, for speed I downloaded a report from Adwords client reporting with the information required and set up a stored procedure to automatically upload this report, link it to additional data from our databases and create the reporting tables in SQL.
We then put all this data into a Prism Elasticube and I recreated all of the reporting plus a number of the backlog requests outstanding on the old method into the Prism dashboards, It took about a day of effort to do SQL work and about half a day to do all of the Prism work, and due to changes in how we were going to alter bid prices within a couple of days of it going live in Prism we ended up scrapping what was a six figure development, although some one does have to run a couple of reports each day and paste them onto a Server, and kick off a job that runs all the SQL stuff and at the end kicks off the Elasticube build using Prism Shell all from a SQL Server job. This process takes a couple of minutes a day.
This is another demonstration of how you should use the right tool for the right job.