Author: Tim

If you engage in any direct-to-consumer agriculture, you’ll need a point of sale system to process credit card payments, track sales, etc. Square is a popular choice for many small businesses and is what we’ve used with our strawberry/blueberry patch.

While Square’s built-in reports provide a decent insight into overall performance, I had trouble drilling down to item-specific performance over varying periods of time like hour of day, day of week, etc.

To answer these questions and guide setting business hours, I created a spreadsheet to answer 3 questions:

• How many sales occurred in each hour of an average week (all weeks overlaid)
• Was there a shift in popular hours as the season progressed?
• What was the typical pattern of sales during the first weekend of a crop? How does that compare to the second, third, etc, or holidays.

I additionally created two item-specific reports to show the most popular hours of a day or days of a week for every item.

Spreadsheet Setup

I created this in Google Sheets. You can add a blank copy of this spreadsheet to your drive here

Download CSV Data from Square

1. From your Square dashboard, select Transactions

2. Then pick your timeframe for the data. The spreadsheet will allow you to filter by time period later, so unless you have a good reason to limit the input data, select an entire year at a time. (1 year is the maximum for one export)

3. On the right side, click Export, and select Item Detail CSV

4. (Repeat this for as many years as you have data you want to be able to query)

Importing CSV Data to Spreadsheet

1. If you haven’t already, add a copy of the spreadsheet to your Google Drive via this link

When you open the spreadsheet, you should land on the Raw Data sheet. Columns A – J are formula helper functions, so insert the Square data starting at column K.

2. With K2 selected, go to File > Import > Upload, and select the CSV you downloaded from Square.

3. After the file uploads, you should see the Import file modal. For import location, select “Replace data at selected cell”. Everything else can be left as is. Click import data.

You’ll notice that the first row of the inserted data contains column labels. Double check that these map to the existing labels in the top row (but don’t delete this row yet, it has formulas that need to be copied). Square has not changed their column format in the two years since I first made this spreadsheet, but you never know. ¯\_(ツ)_/¯

4. You’ll need to copy the formulas in A2 – J2 to all the rows below. The most reliable way to do this at scale is to select A2 – J2, and copy. Then select A3, scroll all the way to bottom of the sheet, hold Shift, and select the bottom cell in column J. Now do a regular paste. This should apply the formula across all the cells without having to drag and scroll down potentially thousands of rows.

5. Delete the header row at A2.

6. Repeat this process for each CSV you need to import and remember to delete the column headings each time

Go to the Query Page sheet when you’re finished. If all has gone well, you should see each year you’ve imported under the Year list, and you should see your product categories and items/price points under the respective lists. Now you’re ready to use the querying page and reports.

Using the Spreadsheet

Query Page is what you’d expect- a landing page to select filters on what data you want to view. The checkbox to the left of each item includes/excludes it from the data. Depending on the size of your dataset and the kind of device you’re viewing from, filter applications could take a bit to apply, although I’m usually under a 5 second digest time with my 19,000 row dataset on a decent laptop.

The Metric dropdown controls what numbers are used in the reports: count, quantity, revenue, or transactions.

Once you’ve picked the metric you want to see and selected/unselected your filters, you can visit any of the subsequent report pages and those filters will already be applied.

These reports are all two-dimensional matrices with conditional formatting across the matrix to highlight highs/lows. There is some repetition and overlap between reports, but I’ll step through each report so you can see what each does well.

Day of Week / Hour of Day

Note that the hours noted in these column headings mean the hour portion of the time. For example, 2 PM accounts for all sales from 2:00 – 2:59 PM.

Day of Year / Hour of Day

The left axis in this view is first aligned by day of week and week of year. Then after the fact, Approximate Date is calculated for an average calendar date. In our business “a Saturday in late May” means more than a literal date like “May 27th”. I’d guess this is the case for many businesses and thus being weekday centric makes sense for fair interyear comparisons.

Week of Year / Day of Week

Think of this like a calendar with columns as days and rows as weeks. The same date fuzziness is at play here since these are aligned by week of year. The start/end dates listed on the left are averages, not extremes.

Product / Hour of Day

Product / Day of Week

Expansion

The report pages all reference the hidden Filtered Data sheet which is generated by applying the Query Page filters to the Raw Data sheet. If you’d like to build your own reports, referencing this Filtered Data sheet will allow you piggyback off all the built-in querying.

Pivot Table Weirdness

In this project I discovered how pivot tables in Google Sheets misbehave when the source data is changed. Specifically, if you have conditional filters applied, then delete and insert fresh data to the data range, the pivot table will not process the data until you remove and reapply the filters. I ended up rewriting my report pages (which originally used pivot tables) with a manual SUMIFS matrix so that it would reliably work when copied and populated with someone’s new data. This also drastically sped up the digest time of queries (I had load times of 60+ seconds on my 19,000 row dataset, down to under 5 seconds with the bare SUMIFS matrices).

I never went back to test a pivot table on the Filtered Data sheet as that method of filtering postdated the use of pivot tables, but you may encounter problems on reloads.

One of the first resources I found in my quest to quantify the local basis market was this dataset from the University of Illinois. It provides historical basis and cash price data for 3 futures months in 7 regions of Illinois going back to the 1970s.

On its own, I haven’t found any region in this dataset to be that accurate for my location. Perhaps I’m on the edge of two or three regions, I thought. This got me thinking- what if I had the actual weekly basis for my local market for just a year or two and combined that with the U of I regional levels reported for the same weeks? Could I use that period of concurrent data to calibrate a regression model to reasonably approximate many more years of historical basis for free?

I ended up buying historical data for my area before I could full test and answer this. While I won’t save myself any money on a basis data purchase, I can at least test the theory to see if it would have worked and give others an idea for the feasibility of doing this in their own local markets.

Regression Analysis

If you’re unfamiliar with regression analysis, essentially you provide the algorithm a series of input variables (basis levels for the 7 regions of Illinois) and a single output to find the best fit to (the basis level of your area) and it will compute a formula for how a+b+c+d+e+f+g (plus or minus a fixed offset) approximates y. Then you can run that formula against the historical data to estimate what those basis levels would have been.

We can do this using Frontline Solver’s popular XLMiner Analysis Toolpak for Excel and Google Sheets. Here‘s a helpful YouTube tutorial for using the XLMiner Toolpak in Google Sheets and understanding regression theory, and here’s a similar video for Excel.

Input Data Formatting

You’ll want to reformat the data with each region’s basis level for a given contract in separate columns (Columns E-K below), then a column for your locally observed values on/near those same dates (Column N). To keep things simple starting out, I decided to make one model that includes December, March, and July rather than treat each contract month as distinct models. To account for local-to-regional changes that could occur through a marketing year, I generated Column L to account for this, and since anything fed into this analysis needs to be numerical, I called it “Delivery Months after Sept” where December is 3, March is 6, and July is 10. I also created a “Days Until Delivery” field (Column M) that could help it spot relationship changes that may occur as delivery approaches.

Always keep in mind: what matters in this model isn’t what happens to the actual basis, but the difference between the local basis and what is reported in the regional data.

⚠️ One quirk with the Google Sheet version- after setting your input and output cells and clicking the OK button, nothing will happen. There’s no indication that the analysis is running, and you won’t even see a CPU increase on your computer as this runs in Google’s cloud. So be patient, but keep in mind there is a hidden 6 minute timeout for queries (this not shown anywhere in the UI, I had to look in the developer console to see the API call timing). If your queries are routinely taking several minutes, set a timer when you click OK and know that if your output doesn’t appear within 6 minutes you’ll need to reduce the input data or use Excel. In my case, I switched to Excel part way through the project because I was hitting this limit and the 6 minute feedback loop was too slow to play around effectively. Even on large datasets (plus running in a Windows virtual machine) I found Excel’s regression output to be nearly instantaneous, so there’s something bizarrely slow about however Google is executing this.

Results & Model Refinement

For my input I used corn basis data for 2018, 2019, and 2020 (where the year is the growing season) which provided 9 contract months and 198 observations of overlapping data between the U of I data and my actual basis records. Some of this data was collected on my own and some was a part of my GeoGrain data purchase.

Here’s the regression output for the complete dataset:

The data we care most about is the intercept (the fixed offset) and the coefficients (the values we multiply the input columns by). Also note the Adjusted R Squared value in the box above- this is a metric for measuring the accuracy of the model- and the “adjustment” applies a penalty for additional input variables since even random data can appear spuriously correlated by chance. Therefore, we can play with adding and removing inputs in an attempt to maximize the Adjusted R Squared.

0.597 is not great- that means this model can only explain about 60% of the movement seen in actual basis data by using the provided inputs. I played around with adding and removing different columns from the input:

Data	Observations	Adjusted R Squared
All Regions	198	0.541
All Regions + Month of Marketing Year	198	0.596
All Regions + Days to Delivery	198	0.541
All Regions + Month of Mkt Year + Days to Delivery	198	0.597
Regions 2, 6, 7, + Month of Mkt Year + Days to Delivery	198	0.537
Regions 1, 3, 4, + Month of Mkt Year + Days to Delivery	198	0.488
All Data except Region 3	198	0.588
December Only	95	0.540
March Only	62	0.900
July Only	41	0.935
December Only- limited to 41 rows	41	0.816

Looking at this, you’d assume everything is crap except the month-specific runs for March and July. However, it seems like a good part of those gains can be attributable to the sample size though as shown in the December run limited to the last 41 rows, a comparable comparison to the July data.

Graphing Historical Accuracy

While the error metrics output by this analysis are quantitative and helpful for official calculations, my human brain finds it helpful to see a graphical comparison of the regression model vs the real historical data.

I created a spreadsheet to query the U of I data alongside my actual basis history to graph a comparison for a given contract month and a given regression model to visualize the accuracy. I’m using the “All Regions + Month of Marketing Year” model values from above as it had effectively the highest Adjusted R Square given the full set of observations.

In case it’s not obvious how you would use the regression output together with the U of I data to calculate an expected value, here’s a pseudo-formula:

= intercept + (region1UofIValue * region1RegressionCoefficient) + (region2UofIValue * region2RegressionCoefficient) + ...  + (monthOfMktYear * monthOfMktYearRegressionCoefficient)

Now here are graphs for 2017-2019 contracts using that model:

Here’s how I would describe this model so far:

Keep in mind, the whole point of a regression model is to train it on a subset of data, then run that model on data it has never seen before and expect reasonable results. Given that this model was created using 2018-2020 data, 2017’s large errors seem concerning and could indicate futility in creating a timeless regression model with just a few years of data.

To take a stab at quantifying this performance, I’ll add a column to calculate the difference between the model and the actual value each week, then take the simple average of those differences across the contract. This is a very crude measure and probably not statistically rigorous, but it could provide a clue that this model won’t work well on historical data.

Contract Month	Average Difference
July 2021	<insufficient data>
March 2021	<insufficient data>
December 2020	0.05
July 2020	0.03
March 2020	0.04
December 2019	0.03
July 2019	0.05
March 2019	0.03
December 2018	0.04
– – – – – – –	– – – – – – – – – –
July 2018	0.04
March 2018	0.05
December 2017	0.08
July 2017	0.15
March 2017	0.06
December 2016	0.08
July 2016	0.10
March 2016	0.06
December 2015	0.07
July 2015	0.07
March 2015	0.04
December 2014	0.07

The dotted line represents the end of the data used to train the model. If we take a simple average of the six contracts above the line, it is 4.3 cents. The six contracts below the line average 7.6 cents.

Is this a definitive or statistically formal conclusion? No. But does it bring you comfort if you wanted to trust the historical estimation without having the actual data to spot check against? No again.

To provide a visual example, here’s December of 2016 which is mathematically only off by an average of $0.08.

How much more wrong could this seasonal shape be?

Conclusion: creating a single model with 2-3 years of data does not consistently approximate history.

Month-Specific Model

Remember the month-specific models with Adjusted R Squares in the 80s and 90s? Let’s see if the month-specific models do better on like historical months.

Contract Month	Average Difference (Old Model)	Average Difference (Month-Model)
July 2021	<insufficient data>
March 2021	<insufficient data>
December 2020	0.05	0.03
July 2020	0.03	0.02
March 2020	0.04	0.01
December 2019	0.03	0.03
July 2019	0.05	0.02
March 2019	0.03	0.02
December 2018	0.04	0.03
– – – – – – –	– – – – – – – – – –	– – – – – – –
July 2018	0.04	0.04
March 2018	0.05	0.03
December 2017	0.08	0.05
July 2017	0.15	0.11
March 2017	0.06	0.06
December 2016	0.08	0.05
July 2016	0.10	0.08
March 2016	0.06	0.08
December 2015	0.07	0.04
July 2015	0.07	0.04
March 2015	0.04	0.03
December 2014	0.07	0.05

Well, I guess it is better. ¯\_(ツ)_/¯ Using the month-specific model shaved off about 2 cents of error overall. The egregious December 2016 chart is also moving in the right direction although still not catching the mid-season hump.

Even with this improvement, it’s still hard to recommend this practice as a free means of obtaining reasonably accurate local basis history. What does it mean that July 2017’s model is off by an average of $0.11? In a normalized distribution, the average is equal to the median and thus half of all occurrences are above/below that line. If we assume the error to be normally distributed, that would mean half of the weeks were off by greater than 11 cents. You could say this level of error is rare in the data, but it’s still true that this has been shown to occur, so if you did this blind you have no great way of knowing if what you were looking at was even 10 cents within the actual history. In my basis market at least, 10 cents can be 30%-50% of a seasonal high/low cycle and is not negligible.

Conclusion: don’t bet the farm on this method either. If you need historical basis data, you probably need it to be reliable, so just spend a few hundred dollars and buy it.

Last Ditch Exploration- Regress ALL the Available Data

Remember how Mythbusters would determine that something wouldn’t work, but for fun, replicate the result? That’s not technically what I’m doing here, but it feels like that mood.

Before I can call this done, let’s regression analyze the full history of purchased basis data on a month-by-month basis to see if the R Squared or historical performance ever improves by much. I’ll continue to use the basis data for corn at M&M Service for consistency with my other runs. It also has the most data (solid futures from 2010-2020) relative to other elevators or terminals I have history for.

Here are the top results of those runs:

Month	Number of Observations	Adjusted R Squared	Standard Error
December	329	0.313	0.049
March	247	0.342	0.055
July	175	0.455	0.116
All Months Together	751	0.305	0.078

Final Disclaimer: Don’t Trust my Findings

It’s been a solid 6-7 years since my last statistics class and I know I’m throwing a lot of numbers around completely out of context. Writing this article has given me a greater appreciation for the disciplined work in academic statistics of being consistent in methodology and knowing when a figure is statistically significant or not. I’m out here knowing enough to be dangerous and moonlighting as an amateur statistician.

It’s also possible that other input variables could improve the model beyond the ones I added. What if you added a variable for “years after 2000” or “years after 2010” that would account for the ways regional data is reflected differently over time? What about day of the calendar year? How do soybeans or wheat compare to corn? The possibilities are endless, but my desire to bark up this tree for another week is not, especially when I already have the data I personally need going forward.

I’d be delighted for feedback about any egregious statistical error I committed here or if anyone get different results doing this analysis in their local market.

This project started while my wife and I were managing spring frost protection in our strawberry patch and trying to better understand how the temperature experienced at the bloom level compares to the air temperature reported by local weather stations on Weather Underground. In the year that has passed since building this temperature logger for that purpose, I’ve encountered several other scenarios where remotely monitoring a temperature can be really helpful:

• Air temperature in a well pump house during a cold snap
• Field soil temperature during planting season
• Air temperature in a high tunnel to guide venting decisions

Whatever you might use this for, the fundamentals are application agnostic and the microcontroller board from Particle used in this project can be connected to a wide array of sensors if you’d like to expand down the road. This tutorial lays out of the basics to get up and running with a single temperature probe.

Particle: Internet-Connected Arduino

If you’re not familiar, Arduino is an open source, single board microcontroller with numerous general purpose input/output (GPIO) pins that allow for easy interfacing between the physical world and code. It might seem rudimentary, but working at the physical/virtual boundary can be really satisfying; of course code can make an object on screen move, and of course touching a motor’s wiring to a battery can make it move, but the ability to use code to manipulate something in the physical world is a special kind of thrill. Absent expansion modules, an Arduino is a standalone board whose software is loaded via a USB cable and cannot natively publish data to the internet.

Particle essentially takes the Arduino hardware, adds a wifi (or cellular) internet connection, and marries it to their cloud software to create an easy to use, internet connected microcontroller. Not only does this make it easier to upload your code, but you can publish data back to the Particle Cloud, where it can trigger other web services.

Components

What you’ll need for this project:

• Particle development board
  • I used a Photon, which is the older wifi-only board (now replaced by Argon). Particle has since added a free cellular plan for up to 100,000 device interactions per month (~135/hour), plenty for simple data logging. Unless you know you’ll always be near a wifi network, I’d now recommend a cellular-capable model like the Boron.
• Outdoor temperature sensor probe
• 4.7 kΩ resistor
• Breadboard
• External antenna (wifi or cellular, depending on your Particle hardware)
  • I used these 2.4 GHz antennas for wifi on the Photon. The threaded base and nut make them easy to install through an enclosure wall.
• Enclosure
  • I used a plastic ammo case which worked well
• Power supply
  • I used a USB charger block

Additional supplies to consider:

• A weather-resistant way to connect the power supply:
  • I ran some lamp wire through a small hole in the bottom of the box, then added plugs to each end.
• Extension to the temperature probe wire
  • I used Cat5 cable
• Do you need to keep the box upright and out of mud/water?
  • I added some 4″ bolts as legs/stakes to secure the box upright and still ~2″ off the ground. This also allowed me to run the cables out of the bottom where they wouldn’t be subjected directly to rain
• Silicone/caulk to seal holes

Setup

Particle has a great Quickstart guide for each model of board, so I won’t reinvent the wheel explaining it here.

Also spend some time on Particle’s Hardware Examples page to acclimate yourself with the setup() and loop() structure of the code, how to read and write GPIO pins, the use of delays, etc.

Hardware Configuration

As a practical matter, you’ll also want to consider how the probe wire is entering the box and how to provide strain relief to prevent it from (easily) pulling out of the breadboard. In my case, I made one large arc with the Cat5 cable which was anchored to the wall with several zip ties. I sealed these zip tie holes as well as the cable holes on the bottom with silicone.

Software Configuration

First, we’ll need to read the temperature from the probe and publish it to Particle’s cloud. Secondly, we’ll need to configure the Particle cloud to publish that data to a ThingSpeak dashboard for collection and graphing.

More buck-passing: I followed this tutorial from Particle, but I’ll list all my steps here as they diverge in the use of ThingSpeak.

Particle

The DS18B20 thermometer requires use of the 1-Wire protocol which is understood but the author in theory if not in practice. 1-Wire’s setup and error checking takes up the majority of the code.

Paste the following code into your Web IDE:

// This #include statement was automatically added by the Particle IDE.
#include <OneWire.h>


OneWire ds = OneWire(D4);  // 1-wire signal on pin D4

unsigned long lastUpdate = 0;

float lastTemp;

void setup() {
  Serial.begin(9600);
  
  pinMode(D7, OUTPUT);
}


void loop(void) {
  byte i;
  byte present = 0;
  byte type_s;
  byte data[12];
  byte addr[8];
  float celsius, fahrenheit;

  if ( !ds.search(addr)) {
    Serial.println("No more addresses.");
    Serial.println();
    ds.reset_search();
    delay(250);
    return;
  }

  // The order is changed a bit in this example
  // first the returned address is printed

  Serial.print("ROM =");
  for( i = 0; i < 8; i++) {
    Serial.write(' ');
    Serial.print(addr[i], HEX);
  }

  // second the CRC is checked, on fail,
  // print error and just return to try again

  if (OneWire::crc8(addr, 7) != addr[7]) {
      Serial.println("CRC is not valid!");
      return;
  }
  Serial.println();

  // we have a good address at this point
  // what kind of chip do we have?
  // we will set a type_s value for known types or just return

  // the first ROM byte indicates which chip
  switch (addr[0]) {
    case 0x10:
      Serial.println("  Chip = DS1820/DS18S20");
      type_s = 1;
      break;
    case 0x28:
      Serial.println("  Chip = DS18B20");
      type_s = 0;
      break;
    case 0x22:
      Serial.println("  Chip = DS1822");
      type_s = 0;
      break;
    case 0x26:
      Serial.println("  Chip = DS2438");
      type_s = 2;
      break;
    default:
      Serial.println("Unknown device type.");
      return;
  }

  // this device has temp so let's read it

  ds.reset();               // first clear the 1-wire bus
  ds.select(addr);          // now select the device we just found
  // ds.write(0x44, 1);     // tell it to start a conversion, with parasite power on at the end
  ds.write(0x44, 0);        // or start conversion in powered mode (bus finishes low)

  // just wait a second while the conversion takes place
  // different chips have different conversion times, check the specs, 1 sec is worse case + 250ms
  // you could also communicate with other devices if you like but you would need
  // to already know their address to select them.

  delay(1000);     // maybe 750ms is enough, maybe not, wait 1 sec for conversion

  // we might do a ds.depower() (parasite) here, but the reset will take care of it.

  // first make sure current values are in the scratch pad

  present = ds.reset();
  ds.select(addr);
  ds.write(0xB8,0);         // Recall Memory 0
  ds.write(0x00,0);         // Recall Memory 0

  // now read the scratch pad

  present = ds.reset();
  ds.select(addr);
  ds.write(0xBE,0);         // Read Scratchpad
  if (type_s == 2) {
    ds.write(0x00,0);       // The DS2438 needs a page# to read
  }

  // transfer and print the values

  Serial.print("  Data = ");
  Serial.print(present, HEX);
  Serial.print(" ");
  for ( i = 0; i < 9; i++) {           // we need 9 bytes
    data[i] = ds.read();
    Serial.print(data[i], HEX);
    Serial.print(" ");
  }
  Serial.print(" CRC=");
  Serial.print(OneWire::crc8(data, 8), HEX);
  Serial.println();

  // Convert the data to actual temperature
  // because the result is a 16 bit signed integer, it should
  // be stored to an "int16_t" type, which is always 16 bits
  // even when compiled on a 32 bit processor.
  int16_t raw = (data[1] << 8) | data[0];
  if (type_s == 2) raw = (data[2] << 8) | data[1];
  byte cfg = (data[4] & 0x60);

  switch (type_s) {
    case 1:
      raw = raw << 3; // 9 bit resolution default
      if (data[7] == 0x10) {
        // "count remain" gives full 12 bit resolution
        raw = (raw & 0xFFF0) + 12 - data[6];
      }
      celsius = (float)raw * 0.0625;
      break;
    case 0:
      // at lower res, the low bits are undefined, so let's zero them
      if (cfg == 0x00) raw = raw & ~7;  // 9 bit resolution, 93.75 ms
      if (cfg == 0x20) raw = raw & ~3; // 10 bit res, 187.5 ms
      if (cfg == 0x40) raw = raw & ~1; // 11 bit res, 375 ms
      // default is 12 bit resolution, 750 ms conversion time
      celsius = (float)raw * 0.0625;
      break;

    case 2:
      data[1] = (data[1] >> 3) & 0x1f;
      if (data[2] > 127) {
        celsius = (float)data[2] - ((float)data[1] * .03125);
      }else{
        celsius = (float)data[2] + ((float)data[1] * .03125);
      }
  }

  // remove random errors
  if((((celsius <= 0 && celsius > -1) && lastTemp > 5)) || celsius > 125) {
      celsius = lastTemp;
  }

  fahrenheit = celsius * 1.8 + 32.0;
  lastTemp = celsius;
  Serial.print("  Temperature = ");
  Serial.print(celsius);
  Serial.print(" Celsius, ");
  Serial.print(fahrenheit);
  Serial.println(" Fahrenheit");

  // now that we have the readings, we can publish them to the cloud
  String temperature = String(fahrenheit); // store temp in "temperature" string
  digitalWrite(D7, HIGH);
  Particle.publish("temperature", temperature, PRIVATE); // publish to cloud
  delay(100);
  digitalWrite(D7, LOW);
  delay((60*1000)-100); // 60 second delay
}

Several things to note:

• D4 is declared as the data pin in line 5.
• The first argument in Particle.publish() can be any string label you choose. This event name is what you’ll use in the Particle cloud to identify this piece of data.
• D7 on the Photon I’m using is the onboard LED. I set it to blink for at least 100 milliseconds each time a temperature is published. This helps in debugging, as you can verify by watching the device if there is a temperature reading being taken.
• I’m publishing a temperature every 60 seconds. The delay function takes in milliseconds, hence the 60 * 1000. To be petty, I subtract 100 milliseconds from the delay total to account for the 100 milliseconds of D7 illumination I added above.

Once you flash the above code to your Particle device, navigate to the Events tab in the Particle console. You should start seeing data published at whatever interval and name you configured.

Software- ThinkSpeak

Once the data is publishing to Particle’s cloud, you can go many places with it. I chose to use ThinkSpeak so I could easily view the current temperature from a browser and see a simple historical graph.

Set up a free ThinkSpeak account at thingspeak.com

Create a new Channel and label Field 1 as temperature

After creating the channel, click on API Keys. Make note of the Channel ID and Write API Key.

Back at the Particle Console, click on the Integrations tab.

Click New Integration, and select Webhook.

Webhook fields:

• Event Name: the event name to trigger this web hook- whatever you set as the first argument in the Particle.publish() function
• URL: https://api.thingspeak.com/update
• Request Type: POST
• Request Format: Web Form
• Device: can be left to Any, unless you only want specific devices in your cloud to trigger this webhook

Click on Advanced settings, and under Form Fields, select Custom.

Add the following pairs:

• api_key > your api key
• field1 > {{{PARTICLE_EVENT_VALUE}}}

Save this and navigate back to ThingSpeak. You should start seeing data publishing to your channel.

ThingSpeak Data Display Options

A ThingSpeak channel can be populated with Visualizations and Widgets which can be added with the corresponding buttons at the top of the channel. A widget is a simple indicator of the last known data value, while visualizations allow for historical data, *ahem*, visualization. You can also create your own MatLab visualizations if you want to do more sophisticated analysis or charting.

Chart Visualization

The Field 1 Chart should be added to channel automatically- if not, click Add Visualization and choose the Field 1 Chart. This visualization can be customized by clicking the pencil icon in the upper right corner and has a lot of configurable options:

• Title: chart title that appears directly above graph (there’s no way to change the “Field 1 Chart” in the blue bar)
• X-Axis: text label for x axis
• Y-Axis: text label for y axis
• Color: hex color code for chart line/points
• Background: hex color code for chart background
• Type: choice between:
  • Line (shown in my example)
  • Bar
  • Column
  • Spline
  • Step
  • Not sure what Spline and Step do as they look the same as the Line graph, so you’ll have to play around and see what you prefer
• Dynamic: not sure what this does, I see no effect when toggling
• Days: how many days of historical data to show (seems to be capped at 7 days regardless of larger values being entered)
• Results: limit the chart to a certain number of data points. If you collect data once a minute, limiting to 60 results would give you one hour of data. Leave this field blank to not apply result limit.
• Timescale / Average / Median / Sum:
  • These are mutually exclusive ways of aggregating the data together into fewer graph points. Timescale and Average seem to be the same, so effectively you’re choosing between the average, median, and sum of every X number of points. This simplifies the graph compared to showing 7 days worth of minutely data.
  • I like to use Median with 60 points to get a median hourly data. I chose median because occasionally for unknown reasons, the Particle will report of temperatures of several thousand degrees which is obviously an error. When using average I would see spikes on any hours with such an error, whereas median eliminates that pull.
• Rounding: number of decimal places to display when clicking on a point
• Data Min: The minimum value that should be included in the data set. Any data points lower than this won’t be attempted to be graphed. This also helps with the bizarre temperature error I mentioned above, so I often set this to temperature ranges safely at the far edges of what would be normal for my application
• Data Max: Opposite of data min, sets an upper bound for included data
• Y-Axis Min: Impacts the visual rendering of the y axis. Leave blank to automatically adjust the axis according to the data being graphed.
• Y-Axis Max: Opposite of Y-Axis Min, sets upper bound for y-axis rendering

Numeric Display Widget

The numeric display will simply show the last recorded value as well as an indicator for how old that data is. I made this the first widget on the channel so I can see right away what the “current” value is.

This widget has several configurable options, like the name that will appear in the blue title bar, which field is being displayed, and how often the widget should refresh with new data. I set this to 15 seconds which is plenty often for data that should only change every 60 seconds. You can also round to a certain number of decimal places.

Gauge Widget

The gauge widget adds some visual context for the data and allows you to customize numerical ranges and colors associated with ranges. Here’s an example of a gauge with color ranges for freezing or near freezing temperatures.

Configurable options for this include the widget title, minimum and maximum gauge range, and start/stop values for color bands.

Lamp Widget

This is the most limited display type, but I guess its cute. It allows you to turn on or off a virtual light bulb above/below a given threshold. The logic is very similar to the gauge but simpler, so I won’t spend time re-explaining the functionality.

Adding Additional Sensors

Every step in this data pipeline (the Particle hardware, the Particle Cloud, and ThingSpeak’s channels) are ready to accept multiple pieces of data if you want to add more sensors to the box.

It’s easy to imagine some of the additional data that could be added to the board and collected.

• Humidity
• Soil moisture
• Multiple temperature probes (the 1Wire library used by the probes in this tutorial allow for this to happen over the same pin)
• Motion detection
• Liquid flow meter

In the future, I’d like to buy a Boron board with cellular connectivity and make a standalone box with a solar panel and battery that could be flexibly placed anywhere. I’m also interested in the idea of wiring up the board to a handful of plugs through the side of the box and being able to easily add and remove sensors based on the need of the use. I’ll keep you posted if I do this.

If you have any use case ideas or want to share your own project or implementation, please leave a comment or get in touch!