Table of contents of the article:
During Black Friday, one of the most critical times of the year for e-commerce sites, every millisecond counts. Platforms must be ready to handle an exceptional load of traffic without compromising performance or user experience. We were recently engaged by a customer to fully optimize their WooCommerce site in anticipation of the increase in requests. In the process, we identified and resolved a performance issue related to the plugin SPOKE, an Italian solution for abandoned cart management and sales automation.
In this article, we walk you through the process of identifying the problem, how we addressed it, and how this optimization improved overall system performance.
What is SPOKI and what is it for?
SPOKI is a platform developed in Italy, designed to help e-commerce improve their sales performance thanks to the automation and optimized management of abandoned carts. It is a modern and versatile solution, integrable in WooCommerce, the most popular e-commerce system in the world, and designed to facilitate the connection between online stores and customers.
One of the main features that distinguishes SPOKI is its ability to Automate abandoned cart recovery. When a customer adds products to the cart but does not complete the purchase, SPOKI sends automatic notifications to remind them of the incomplete order, using targeted strategies to increase the probability of conversion. This process is based on an intelligent analysis of user behavior, ensuring that notifications are relevant and timely.
Another notable feature is theWhatsApp integration, a communication channel that has become essential for interacting with customers. SPOKI uses this platform to send direct messages, cart recovery notifications, personalized promotions and order updates. This approach allows for a more direct and personal relationship with users, improving the customer experience and increasing the conversion rate.
Finally, SPOKI offers advanced tools forConversion Optimization, combining data collected during customer interactions and features to automate marketing campaigns. The result is a platform that not only facilitates the recovery of lost sales, but actively contributes to the overall growth of the online store's performance.
Among its main features are:
- Abandoned Cart Recovery: SPOKI sends automated notifications to customers who have left the site without completing the purchase, encouraging them to return.
- WhatsApp Integration: The platform leverages the popular messaging channel to interact with customers in real time.
- Conversion optimization: Through a mix of tools and data, SPOKI helps online stores maximize sales.
SPOKI is designed to integrate with WooCommerce, the world's most widely used e-commerce system, and is highly regarded for its ease of use and ability to increase the conversion rate of online stores.
However, like any software, SPOKI faces challenges that come with high-traffic environments, especially during sales events like Black Friday.
Why Choose SPOKI?
SPOKI is an ideal choice for those who manage an online store and want to improve their performance without having to deal with technical complexities. One of its distinctive features is the ease of use, making it suitable for both those new to e-commerce and experienced professionals. Thanks to an intuitive interface and quick configuration, SPOKI allows you to implement advanced tools such as abandoned cart recovery and automation campaigns without the need for complex technical interventions.
Immediate Impact on Sales
One of the main strengths of SPOKI is its immediate impact on sales. The platform is designed to generate tangible results in a short time, thanks to its ability to analyze user behavior and intervene in a targeted manner. For example, sending timely personalized notifications to customers who have abandoned their cart can bring a significant percentage of users back to the store, increasing the conversion rate in a direct and measurable way.
Designed for the Italian and international market
Being a product developed in Italy, SPOKI is particularly suited to local e-commerce, understanding its dynamics, needs and peculiarities. This knowledge of the market translates into features designed to respond to specific challenges, such as managing local privacy regulations or adapting communication campaigns to Italian but also international consumer habits.
The Black Friday Challenge and an Abnormal Database Load
In view of Black Friday, one of the most critical periods for e-commerce, one of our clients with a large WooCommerce store asked us to verify that their platform was optimized to handle the upcoming traffic spike. It was a site with a high volume of products and a large customer base, already well configured with several optimizations for performance management. However, during preliminary tests, we noticed an anomalous behavior: the server load was unusually high, with peaks that exceeded the acceptable level to ensure a smooth experience for users.
The situation required a thorough investigation. We started by analyzing server logs and monitoring database resources for bottlenecks. That's when we discovered a recurring pattern: a SQL query generated by the SPOKI plugin that was consuming a disproportionate amount of resources.
The problematic query was as follows:
SELECT COUNT(*) FROM wp_spoki_setting
This call, executed repeatedly during the plugin initialization process, turned out to be the main cause of the overload. SPOKI, used by the client to handle abandoned carts and automate customer interactions, accessed a specific database table (wp_spoki_setting) to check for configuration data.
The Impact on the System
The query SELECT COUNT(*), which is used to count all the records in the table, may seem harmless on small databases. However, in the specific case of our client, the table wp_spoki_setting it contained approx 14 million records, due to the high volume of activity generated by e-commerce over the years. This context has transformed a simple operation into a very expensive process for the database.
Each query execution took approximately according 1 to complete. While this may seem like a small amount of time, the repetitive nature of the call during the plugin's operations had a devastating cumulative impact. During testing, we observed the query being executed so frequently that it generated a significant server overhead, bringing the system load average to values above 9, well above the recommended threshold for a stable environment.
An absolutely useless query
By analyzing the SPOKI plugin code in detail, we found that the problematic query SELECT COUNT(*) FROM wp_spoki_setting had a surprisingly limited role: its sole purpose was to check for the presence of records in the plugin's configuration table (wp_spoki_setting). It was not really used to get the exact number of records, nor was it used for subsequent operations that required that specific data.
This query, therefore, was not only excessively heavy compared to its purpose, but it was also totally redundant. To determine the presence of data in a table, it is not necessary to count all the records, especially in a context where the total number can exceed millions of rows, as in the case of our client. This approach was particularly inefficient and generated a significant impact on the database, with a waste of resources disproportionate to its usefulness.
The use of SELECT COUNT(*) It is only justified when you need an accurate count of records for statistical purposes or subsequent operations, but this was not the case. SPOKI simply required a Boolean check, that is, checking whether one or more records were present in the table, without any need to know the quantity. The reason was to check only if it was installed correctly and the table was present as we can see from the PHP code below that you can check in /wp-content/plugins/spoki/modules/abandoned-carts/spoki-abandoned-carts-db.php.
public function init_tables()
{
global $wpdb;
$spoki_setting_tb = $wpdb->prefix . SPOKI_SETTING_TABLE;
if ($wpdb->get_var("SHOW TABLES LIKE '$spoki_setting_tb'") !== $spoki_setting_tb) {
error_log('Error: Table does not exist: ' . $spoki_setting_tb);
return;
}
$meta_count = $wpdb->get_var("SELECT COUNT(*) FROM $spoki_setting_tb");
if ((!$meta_count)) {
$env_file_path = SPOKI_DIR . '/.env';
if (file_exists($env_file_path)) {
$meta_data = parse_ini_file($env_file_path);
if ($meta_data === false) {
error_log('Error: Failed to parse .env file at ' . $env_file_path);
return;
}
$meta_data["access_token"] = md5(uniqid(wp_rand(), true));
foreach ($meta_data as $meta_key => $meta_value) {
$wpdb->insert(
$spoki_setting_tb,
array('meta_key' => $meta_key, 'meta_value' => $meta_value),
array('%s', '%s')
);
}
} else {
error_log('Warning: .env file not found at ' . $env_file_path);
}
}
}
This highlighted not only an optimization opportunity, but also an implementation that was not very attentive to best practices, which can become problematic in high-traffic environments or on large databases.
The Solution: A More Efficient Approach to SQL Querying
To fix the problem, we changed the code by replacing the query SELECT COUNT(*) with a lighter and more optimized version:
$meta_count = $wpdb->get_var("SELECT 1 FROM $spoki_setting_tb LIMIT 1");
The new query simply checks whether there is at least one record in the table, without having to count all the records. By using LIMIT 1, the query stops as soon as it finds the first record, dramatically reducing the load on the database.
The Results: Improved Performance
After applying this change, we ran a series of tests to evaluate the performance impact. Here are the results:
- Query execution time: Reduced from about 1 second to a few milliseconds.
- Average server load: Decreased from 9 to 1,4.
- General site responsiveness: Significantly improved, even under high load.
This optimization not only reduced database response time, but also made the entire system more stable and ready to handle the Black Friday traffic surge.
Why This Optimization Is Important
Sales events like Black Friday are crucial moments for any e-commerce. During these times, site traffic can increase exponentially, putting a strain on the entire technical infrastructure. In this context, even a small bottleneck, such as an inefficient query or unoptimized code, can have devastating repercussions: slowdowns, downtime, or, even worse, abandoned carts due to a poor user experience.
The SQL query optimization in the SPOKI plugin, which we performed for our client, highlights some key lessons for e-commerce platform managers, especially during periods of high intensity.
Monitoring Your Load Is Key
A site that appears to be working fine under normal conditions may be hiding pitfalls that only become apparent under stress. Load testing and resource monitoring are indispensable tools for identifying bottlenecks before they become a problem. In our case, the problematic query would never have raised alarms during typical usage, but as Black Friday approached and concurrent requests increased, its negative impact became apparent.
Carefully Analyze SQL Queries
SQL queries are one of the most critical elements for the performance of an e-commerce site. Even a single inefficient query can slow down the entire system, especially when operating on large databases. The habit of using queries like SELECT COUNT(*), seemingly innocuous, can become a significant problem in high-traffic contexts. Analyzing each query to ensure it is optimized and necessary is essential to avoid wasting resources.
Targeted Optimizations Make the Difference
The changes we made to the SPOKI plugin are a perfect example of how a small, targeted intervention can lead to significant improvements. Replacing the inefficient query with a lighter one dramatically reduced the load on the database, improving the overall performance of the site. This type of intervention is particularly useful because it does not require radical or expensive changes, but focuses on specific areas that have a high impact.
Reduce Risk During Traffic Peaks
Events like Black Friday are unforgiving. Customers expect a fast and seamless experience, and a slow site can quickly lose credibility and sales. Optimizing every aspect of the platform before these events is not just a good practice, but a necessity. In our case, optimizing the SPOKI plugin prevented issues that could have compromised the client's entire operation.
Ensuring Long-Term Scalability
Optimizations aren’t just about emergency management; they’re also about ensuring that your system can scale up over time without issues. An inefficient query might not be a problem today, but it will inevitably become a problem as traffic and data volumes grow. By acting early, you’re preparing to handle not just Black Friday, but any future challenges.
A Multidisciplinary Approach
Although our core business is hosting and advanced systems engineering, there are situations where it is necessary going beyond our traditional role to ensure maximum efficiency for customers. The resolution of the problem related to the SPOKI plugin is an example of this: we did not limit ourselves to observing a load anomaly on the server, but we undertook a real path of reverse engineering, starting from the symptoms to get to the root of the problem.
The investigation began by analyzing raw server load data, then delving into database behavior to spot anomalous queries. Once the problematic query was identified, we dug even deeper, examining the plugin's source code to understand exactly why it was inefficient. This process requires transversal skills, ranging from database performance analysis to understanding the PHP language, to the ability to evaluate the computational impact of operations.
In these cases, it is essential to know how to “get your hands dirty”: it is not enough to diagnose a problem, you need to intervene directly on the code when possible, always respecting the integrity of the software. This multidisciplinary approach, which combines knowledge of systems, calculation, algorithmic complexity and development, is what allows us to offer complete and effective solutions, even in complex scenarios. Technology has no watertight compartments, and often the key to solving a problem lies in the union of skills that go beyond our core field.
Reporting to the manufacturing company
After completing our tests and verifying the positive impact of the optimization, we immediately took action to report the issue to the company that produces SPOKI. The next day, we sent a detailed email to their technical support, explaining the problem encountered and providing the implemented solution. In the message, we emphasized the importance of including this optimization in the next release of the plugin, hoping that the change can benefit all SPOKI users, especially in high-traffic contexts such as Black Friday.
In addition to direct email communication, we have also opened an official support request in the WordPress plugin directory, sharing our analysis and optimization suggestions. The thread is publicly available at: https://wordpress.org/support/topic/optimization-suggestion-for-spoki-plugin-to-improve-woocommerce-performance/. This step was taken not only to report the issue to the developers, but also to provide the community with a case study that can help other users recognize and solve similar problems.
With this double action, we wanted to make sure that the issue is not only taken care of by the manufacturing company, but that it can generate a long-term positive impact on the entire user base of the plugin.
Conclusions and Suggestions
This experience shows that every detail counts when it comes to optimizing e-commerce. It’s not enough to focus on visible areas like the frontend or marketing campaigns: the backend and database also need to be efficient and ready to handle high loads. Investing in targeted optimizations and regular testing not only improves performance, but also ensures that the user experience remains flawless, no matter the challenges. And as our client’s case shows, these optimizations can make the difference between a successful Black Friday and a missed opportunity.
Our analysis revealed that even advanced tools like SPOKI could benefit from a performance overhaul, especially in high-traffic situations. We shared our solution with the SPOKI development team, suggesting that this change be integrated into future versions of the plugin.
For those who manage an e-commerce, here are our tips:
- Prepare in advance for traffic peaks: Plan load testing and optimizations before sales events.
- Analyze installed plugins: Even the best plugins can hide performance issues.
- Collaborate with experts: Turning to a specialized team can make the difference in critical moments.
We are proud to have helped our client overcome this challenge and to have contributed to improving the performance of SPOKI. If you also need support in optimizing your site or want to prepare for the next sales event, contact us. We are here to make sure your platform is always at its best.
