Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
14 - Magnetar
14 - Magnetar



Have you ever been stuck at home for days on end with an Alteryx license and nothing to do? Do you ever find yourself wishing you could just grow your own data at home?


You’re in luck. There is data all around you. If you’re lucky enough to have a smart home or you are interested in learning how to do some minor electrical work to pass the time, you have a lot of data at your disposal.


Before going over the data though, there’s some history you need to know. In 2012 I bought my first home and decided it was a good time to get lazy. One night I forgot to turn off some lights before going to bed and just did not want to get up. Instead, I did what any reasonable person would do and spent the rest of the night, up until the morning, reading about ways to remotely control light switches.


There are two main types of hardware to be aware of: controllers and devices.


A controller is like the CPU of your home. It runs software that communicates with devices in your home. It can record status of devices and send commands to devices.


Devices interface directly with your electrical system and some are battery operated. They can be a sensor which only sends information back to the controller, a controlled device which receives commands from the controller, or both.


Controllers use communication protocols to exchange information with devices. The protocols can be transmitted via wireless radio frequency (like Bluetooth, Z-wave, Zigbee), over hardwired lines (Ethernet, RS-232, Universal Powerline Bus/UPB) or both (Insteon). Most of the common plug and play platforms (like SmartThings) communicate on the Z-wave protocol.


When I began my journey into home automation, the plug and play solutions weren’t widely used and those that existed had very limited and specific uses. Devices used to automate simple things (like a light switch or an appliance outlet) were generally available on every automation platform. But if you wanted something more specialized, like a sensor that would use the amount of sunlight in a room to control the setting of vertical blinds, then you needed a specialized device. Such a device may only exist on one of the platforms or use a proprietary communication protocol which would lock you into using only one company’s products throughout your home, assuming you didn’t want a dozen different apps and controllers to keep track of.


After a lot of research, I decided that the devices which used the Insteon protocol seemed to have the most options for different uses. It was a bonus that Insteon was in the process of converting their devices over from a single communication protocol (powerline or radio signal) to a dual protocol (powerline and radio signal). This allows for more redundancy in signaling, which decreases the chance that interference will disrupt the flow of data.


Although Insteon was a proprietary system, they did offer the flexibility to use their devices with a different brand of controller. They offer a powerline modem which another controller could use to interface with their devices. After some trial and error, I settled on the ISY-994i by Universal Devices for my controller. To me it was the perfect combination of everything I needed. Not only did it communicate with my Insteon devices, but had the flexibility to make API calls, receive infrared commands, and they had just added an optional attachment which allowed it to also communicate with Z-wave devices. This opened a whole new world of device options. Another cool thing about the ISY is that there are some very well made smartphone apps which enable additional integration like security cameras, geofencing, etc. Best of all, it’s not a cloud-based service. I own my own data and it doesn’t get reported to anyone, which is important to me. Also, if the internet goes out but your electricity is on, everything still works.


Anyway, we were talking about data… And using a controller like this is a great way to get some real-world data that you can use and learn from home.


One of my use cases was the automatic lights in our kitchen. The kitchen is sort of a central spot in our house and you can pretty much see anything downstairs at night if those lights are on. So, I installed a controlled light switch for them. Initially, the plan was to make our own automated on/off function using a series of motion sensors. The motion sensors would trigger if someone was in the kitchen or walking to the kitchen from somewhere else. That trigger on the ISY would cause two actions:


  1. The lights above the kitchen table would gradually turn on.
  2. A second program would start.


The second program was to turn off the lights after 10 minutes of being on. But, every time motion is detected inside of the kitchen the second program starts the countdown over again so the lights don’t turn off while anyone is in the kitchen.


That worked well for a while, but thanks to the Sense Energy Monitoring device we installed, we found out that the dogs walking around downstairs during the day were turning on the lights. Obviously, the dogs do not need the lights on, especially not during the day. Thankfully we can automate a solution for this as well. Adding a light sensor allows us to track how much ambient light is already available. In the program that turns the lights on, we added a condition that the measurement of light (in lux) has to be over a certain threshold for the program to run.


Now, to fine tune the program, we can pull log data from the controller. Since the log just contains the actions that happened and the sensor readings received by the controller, we can use Alteryx to bring it to life.




Now that we’ve converted a log of state changes into time series data, let’s take a look in Tableau.


The yellow line represents natural sunlight measured in lux. The blue squares represent the state of the light (off/0 or on/1).


As the amount of sunlight goes up, the light should stop turning on and only resume operation once the natural light has decreased.


On day one with a new sensor, it isn’t really easy to tell exactly what light measurement is the magical number where you don’t need a light to turn on, but clearly I guessed wrong since the light is turning on even after the natural light has peaked for the day.




Throughout the day, I made notes on a piece of paper when I thought there was enough light in the room naturally and when I would have turned the light on myself.


Once I compared my notes to this data it was obvious how I needed to adjust the threshold. It only took a few minutes to adjust, and we were ready for day two.


The next day didn’t have as much natural light, but the new settings were spot on – right around 2800 lux the lights stopped activating. Another problem solved by Alteryx.




The fun of automation is never over, though. Phase two will be enabling Bluetooth beacons on the dogs’ collars to avoid turning on lights just for them.