The Texas Tax Calculator is by far one of the most important plugins I’ve developed for solving a major pain point. It is comparable to the relief brought about by making Versatile Shipping.

Unlike other plugins, this is one of those plugins that does not or did not have a good equal nor multiple options to choose from.

Other tax plugins exist, notably a few from major companies, but anyone who’s used them for calculating local use tax for intrastate Texas sales knows that there are problems with them.

For example, after getting a major company’s plugin to work correctly for my client’s tax needs (it required a custom setup, rather than being the default configuration) the company surprised my client with thousands of dollars in hidden fees that were not disclosed by the salesman.

Most of the fees were wrapped up in frequent API calls, even for a limited number of Texan sales, and it became apparent that my client could not scale their business in Texas while remaining with the major company without paying exorbitant prices.

Furthermore, there was no good protection from receiving large unexpected charges due to abusive bot farms spamming the checkout screen and sending API calls.

It was because of these reasons that it was necessary to develop a custom solution.

In steps my Texas Tax Calculator.

 

Synopsis

The Texas Tax plugin connects to the Comptroller of the State of Texas via their API, downloads two files from them that are used to calculate the local use and sales tax for Texas. My plugin extracts these files and then builds two different databases in MySQL. The first database contains a list of Tax Rate IDs or TAIDs and how much each TAID adds to the final tax rate. The second database, which is much larger, contains a full list of all of the address ranges in the state of Texas down to the street number level. Each range has an assigned TAID (or more) or no TAID. When a customer enters their shipping address at checkout, rather than doing an API call to a remote service, the plugin searches the local MySQL database and matches the customer’s shipping address with the closest fit in the database. The matching row’s TAID(s) are then compared to the TAID database and the tax rates for those jurisdictions are added to the base 6.25% sales tax in Texas to produce the total tax rate. The total tax rate is then multiplied by the shopping cart’s total and added to the order. This costs nothing to the client once installed.

 

Downloading Files

The plugin requires an API Key from the Texas Comptroller. This is free to obtain. Once acquired, the API key allows the web administrator to click the “Get File” buttons which connect to the Comptroller and download the files. The first file, the tax rate jurisdiction file, is nearly instantaneous to acquire. The second file, takes about 5-10 seconds to fully download since it contains 6 million address ranges. Once the address file is downloaded, the plugin will automatically split the file into smaller files of 250,000 rows each. This process takes another 10-15 seconds, but dramatically speeds up the time required to import the address ranges into the database because less time is spent skipping through the already processed contents of the otherwise massive file every single batch. More about batching below.

Creating the Databases

Creating the Tax Jurisdiction database was no problem, as it’s practically instantaneously. As seen in the example above, the database is constructed from 149KB of data. But the Address database is constructed out of a dataset of 6 million plus rows. A file that’s over half a GB. Attempting to build it the same way as the Tax Jurisdiction database resulted in timeout errors on the server. Instead, the database needed to be created in batches. The construction occurs in batches of 10,000 rows. Each batch has only the necessary values from the csv included in the SQL insert query, which includes the address numerical range, prefix, street, suffix, city, zip, and taid. Once the batch is imported into the database, wp_remote_post is used to call another batch 2 seconds later. Once the current address file is finished, the process opens the next file and continues. After a process which takes around an hour, the database is finished importing content from the csv files. I tried numerous things to cut down on the time, and this is the quickest I could get the import without causing problems. For example, lowering the batch delay from 2 seconds to 1, tends to frequently cause the batch to fail to start.

 

 

Visual Import Feedback

Because the address database takes so long to create, it’s important to give the web administrator visual feedback of the progress so far. To do this, I opted for an animated progress bar that shows the current progress and gives an estimated time until completion. To generate an estimated time, the last ten batches are timed, added together, and divided by 10, producing an average. The number of batches completed is then subtracted from the total number of batches and the resulting number is multiplied by the average batch time cost, giving a reasonably good estimate about how much time is left before the import is complete. That group of ten batches updates with each new batch, so that the eta updates with the current server import performance. AJAX calls are made every 3 seconds, fetching information from the server regarding the current progress, and then Javascript updates the progress bar and text.

This process continues in the background and will keep updating even if the admin leaves the page and comes back.

If desired, the web administrator can pause the import or cancel it.

Occasionally, the progress may get stuck due to some error with the server. If this happens the section turns red and alerts the admin that import was paused due to an error. Resuming the import will safely continue where the server bugged out.

 

Address Matching

One of the more difficult parts of the plugin is address matching. What the customer puts in their shipping address is rather ambiguous. They may abbreviate their address, add extra letters, extra information like “, PO Box”, misspell, etc. My plugin takes the customer’s shipping address input, splits the address into parts, does some magic to it, and is able to reliably identify the customer’s street number or letter and their street name. The magic includes looking for rows with expanded abbreviations, condensed abbreviations, fixing common misspellings, identifying extra information that wouldn’t be in the database, etc. It creates a list of possible street names based upon the customer’s input, and then runs several SQL queries looking for the best match of their street address and postal code or city.

 

Conclusion

The Texas Tax Calculator was a challenging plugin to make, but I really enjoyed developing it and especially the satisfaction of solving a major pain point that didn’t rely on paying large companies exorbitant amounts of money to do something as relatively simple as matching addresses to a database and finding their tax ids.