Table of contents of the article:
During Black Friday, one of the busiest times of the year for e-commerce sites, every millisecond counts. Platforms must be ready to handle exceptional traffic without compromising performance or user experience. Recently, we were engaged by a client to fully optimize their WooCommerce site in anticipation of the surge in traffic. During this effort, we identified and resolved a performance issue related to the SPOKI plugin , 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 SPOKI's key features that sets it apart is its ability to automate abandoned cart recovery . When a customer adds products to their cart but doesn't complete the purchase, SPOKI sends automatic notifications to remind them of the incomplete order, using targeted strategies to increase the likelihood of conversion. This process is based on intelligent analysis of user behavior, ensuring notifications are relevant and timely.
Another notable feature is the integration with WhatsApp , an essential communication channel 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 conversion rates.
Finally, SPOKI offers advanced conversion optimization tools , combining data collected during customer interactions with 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 complexity. One of its distinguishing features is its ease of use , making it suitable for both those new to e-commerce and experienced professionals. Thanks to an intuitive interface and quick setup, SPOKI allows you to implement advanced tools such as abandoned cart recovery and automation campaigns without the need for complex technical intervention.
Immediate Impact on Sales
One of SPOKI's key strengths is its immediate impact on sales . The platform is designed to generate tangible results quickly, thanks to its ability to analyze user behavior and take targeted action. For example, sending timely personalized notifications to customers who have abandoned their carts can bring a significant percentage of users back to the store, directly and measurably increasing the conversion rate.
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 anticipation of Black Friday, one of the most critical periods for e-commerce, one of our clients with a large WooCommerce store asked us to ensure their platform was optimized for the upcoming traffic spike. The site had a high volume of products and a large customer base, and was already well-configured with several performance optimizations. However, during preliminary testing, we noticed unusual behavior: the server load was unusually high , with spikes exceeding the acceptable level for ensuring a smooth user experience.
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 1 second to complete. While this may seem like a short 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 created significant server overhead , driving 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 cumbersome for its purpose, but also completely 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 resource expenditure 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 times for any e-commerce site. During these periods, site traffic can increase exponentially, putting a strain on the entire technical infrastructure. In this context, even a small bottleneck, like an inefficient query or unoptimized code, can have devastating repercussions: slowdowns, downtime, or, worse still, 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
While our core business is hosting and advanced systems engineering, there are situations where we need to go beyond our traditional role to ensure maximum efficiency for our clients. Resolving the SPOKI plugin issue is a prime example: we didn't just observe a server load anomaly, but embarked on a true reverse engineering process , starting from the symptoms and getting to the root of the problem.
The investigation began by analyzing raw server load data, then delving into database behavior to identify anomalous queries. Once the problematic query was identified, we delved even deeper, examining the plugin's source code to understand exactly why it was inefficient. This process requires a wide range of skills , from database performance analysis to understanding PHP, and the ability to assess the computational impact of operations.
In these cases, it's crucial to know how to "get your hands dirty": simply diagnosing a problem isn't enough; you need to intervene directly on the code whenever possible, always respecting the integrity of the software. This multidisciplinary approach, which combines knowledge of systems engineering, computation, algorithmic complexity, and development, is what allows us to offer comprehensive and effective solutions, even in complex scenarios. Technology has no silos , and often the key to solving a problem lies in combining skills that go beyond our core expertise.
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've 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 alert developers to the issue, but also to provide the community with a case study that can help other users identify and resolve similar issues.
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 demonstrates that every detail matters when it comes to optimizing an e-commerce site. It's not enough to focus on visible areas like the front end or marketing campaigns: the back end and database must also be efficient and ready to handle heavy loads. Investing in targeted optimizations and regular testing not only improves performance but also ensures a flawless user experience, regardless of challenges. And as our client's case demonstrates, 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're proud to have helped our client overcome this challenge and improve SPOKI's performance. If you need help optimizing your site or want to prepare for your next sales event, contact us . We're here to ensure your platform is always at peak performance.
