Case Study: Comparing Prices of Competing Storage Units

Overview:

One of our clients from the Self Storage industry was looking to build a report that would compare its pricing to the local competitors it has for each store, with a daily refresh in the data. This involved over 100 properties they owned, each with 2-6 competitors nearby.

Approach:

Through Power BI’s web scrape tool, our plan was to create a number of pbix files that would pull data from each competitors website, then write an R Script that would pass the data from Power BI to their SQL database. From there, we would create one Power BI App that would pull in the SQL database and create the necessary reporting.

Required Items:

Our client provided us with a list of all competitors and the fields needed. From there we were able to use Power BI to build out our database using the following tools…

  • Power BI Desktop

  • Power BI Pro License (or higher)

  • R Script Code

  • SQL Database

  • Power Update

Using Power BI Desktop we built the dataset. From the R Script code we were able to pass it to the SQL database. And then with Power Update, we refreshed the report each day, which in turn stored the newest data to the database daily.

Power BI Web Scrape to SQL

The Challenge:

The challenge with the web scrape is that no two sites are formatted the same way (see below). So, some Power BI magic is necessary to get your dataset together. To do this we brought in the tables automatically when we could, but most times had to build these datasets by creating examples and letting Power BI match the formatting.

From there we transformed the data using custom and conditional columns to get a consistently formatted table. Following that pattern for all stores we built a dataset that could be combined into one.

Results:

We were able to build an app that allowed users to view a pricing comparison table with filters for store, unit size, unit type and more. Each result also had a link out to the store’s website and how far away the competitor was from their store.

A map was also built that had the locations of each store, their competitors and the locations of their current customers. Using this would help determine which competitors they should be comparing against most.

We also added in additional data from their CRM to help encompass everything that goes into pricing a unit into one report.

And then to help identify when actions needed to be taken, we built a dashboard of where each store ranked in average price and set up alerts that would notify the pricing manager each time a ranking moved up or down.

Overall, this project saved our client quite a bit of money. The only costs were the $9.99 per Power BI Pro license, $500 for Power Update (plus a $100 Power Update yearly license), and our consulting fee.

Using this tool the Pricing Manager is now able to more easily set the pricing at the best value for the area, creating a large advantage in the market.

Create Your Own:

Think this might be something you’d be interested in doing? Contact us today. We can help you build it or just provide the roadmap. Our team is here to help in any way possible.