My Flooded Basement

When the basement in my recently purchased home flooded during heavy Oregon rains, it rattled my cage.

Luckily it’s an unfinished basement, but there were still costs associated with cleanup and damage to items stored there. Money was also spent replacing the failed sump pump that led to the flooding.

Never wanting to relive the experience, the first thing I did was install some WiFi-enabled water sensors to alert me if water was detected outside the sump basin.

However being a data oriented individual, I began asking myself questions such as:

• How often was my sump pump firing?
• When it did fire, how much water was it moving?
• How much rain was necessary to make the pump expel water?

In this post, I’ll share how I began to answer those questions and others.

Rainfall Data

After some Googling, I learned that the National Oceanic and Atmospheric Administration (NOAA) provided historical rainfall data via a free API.

Using Power Query and Power BI, I was able to hit a NOAA endpoint to learn:

• How many NOAA data collection stations were available in my zip code
• The date range of available data for those stations

With known-good station IDs, I hit different endpoints to capture annual, monthly, and daily historical precipitation data.

These visualizations are helpful to quickly see how the current year, month, and daily rain compare to historical trends.

Sump Pump Data

For sump pump activity data, I plugged my sump into a smart plug with energy monitoring and exported the results.

Combining the two data sets, I produced the below.

As you can see, I started capturing sump activity in March 2022 and had a very dry summer and early fall. Some interesting observations include:

• Small amounts of rain following a dry period do not cause the sump to fire
• It took approximately 3 cumulative inches of rain over 4 days for the sump to first fire after a dry summer.
• Following heavy rains, the sump can continue to fire for over 24 hours after rain stops
• Because I used Power Query, I can now easily bring in new data and update my visualizations as time goes by.

What’s Next

In later posts, I :

• Explain how I use Power Query to call the NOAA endpoints and transform the data received so it’s visualization ready
• Explain how I estimate pump cycles and gallons pumped
• Build the same visualizations in Tableau and share my opinions on each tool

Please let me know if you have questions or ideas for other ways to look at the data – thanks for reading!