Integrating More Than 20 Million Public Data Records With WordPress

Brian Bourn Speaking at BigWP SF

As a VIP gold agency partner, 10up was recently invited to speak at BigWP SF, the enterprise WordPress meetup series that brings together developers, business leaders, and product managers who work with high-scale WordPress applications every day.

My presentation, Managing Millions of Public Data Records With WordPress, highlighted the challenges we encountered when integrating millions of public data records with the WordPress-powered website for ElectricityPlans, a broker in the deregulated Texas electricity market, and how we used solutions like ElasticPress to overcome those challenges.

The Business Of Electricity

Seventeen states have deregulated energy markets, with Texas boasting the largest market for residential electricity. In these states, residents can shop for electricity plans just as they would shop for a cell phone plan and licensed and regulated electricity brokers have permission to resell electricity plans and earn sales commissions.

Brokers, like our client ElectricityPlans, operate as referral partners for utility providers and have two options for earning commissions:

  1. A traditional referral link model
  2. An API-based checkout model

ElectricityPlans originally launched using the referral link model; once a visitor left the site, they had no control over the conversion process. Outdated, non-responsive provider websites with poor user experiences and cross-domain link tracking errors caused a significant loss in sales commissions.

For the long-term health and sustainability of the business, the solution was to integrate an API-based checkout inside of their WordPress site. This approach enabled ElectricityPlans to craft a custom shopping experience, where the entire conversion process happens on their website and the data is passed via API to the utility providers.

An API-based Checkout Leveraging Elasticsearch

Every electric meter in the state of Texas has a unique Electric Service Identifier (ESID), that allows residents to shop for electricity online, change providers, and update their service. To engineer the new API checkout solution, 10up needed access to every address and ESID for the entire state of Texas. Unfortunately, there was no readily available source for the electric meter data and the address lookup services we tested returned data that was incompatible with the electricity providers’ API requirements.

To engineer the API checkout solution, we needed to create a custom database containing every address and ESID in the state of Texas, which meant working with more than 20 million public data records that are updated nightly.

10up built an automated daily process to access public address and ESID data from ERCOT, validate the data, and convert it into a scalable AWS database. We then built a custom Elasticsearch integration, connecting the database with WordPress. Elasticsearch delivers a near-instantaneous search experience and autocomplete minimizes errors and bypasses issues related to inconsistent data structure.

The custom-built API checkout solution ensures the website is always up-to-date with the most accurate address and ESID data. Complete control over the data structure also establishes compatibility with the utility provider APIs. This allows ElectricityPlans to not only convert all existing referral link partners to the new API-based checkout, but create new lucrative partnerships with providers who only offer an API options.

The Results Speak For Themselves

With AWS and Elasticsearch in place, ElectricityPlans has a scalable, cost effective infrastructure for the new database, search, and checkout system and is seeing revenue increase month over month. Adoption of the API checkout saw conversion rates increase by more than 40%.

Watch the full talk to learn more about this particular WordPress and Elasticsearch project or contact us to learn more about 10up’s Elasticsearch consulting services and, our fully managed solution for Elasticsearch and WordPress.

Leave a Comment

Finely crafted websites & tools that make the web better.