“At some point it felt as if the light had been turned on in a dark room; this understanding was worth so much to us.”
REDUCTION IN QUERY WAIT TIME
ENGINEERING HOURS SAVED PER YEAR
Potenza Innovations is a company that specializes in advertising, marketing, and data for automotive dealerships. The Potenza application allows dealers to see all marketing campaigns in one place, track their leads, and get the most out their ad spend.
Via a wholesale relationship with Sinclair Broadcast Group, Potenza helps thousands of dealers showcase vehicle inventory on digital channels and connect them to their target audience.
Aaron Lozier is a co-founder and CTO of Potenza. Aaron’s team builds the platform and analytics dashboards that dealerships use. Daisy Glankler is the Director of Wholesale Operations at Potenza, her team is responsible for sales and customer success.
Potenza has built their analytics application on top of Amazon Redshift. Potenza does the heavy lifting of combining clickstream data from web visits with other usage data, such as phone calls, email leads, and transactions. The dashboards provide insights to dealerships they can’t get anywhere else.
With a growing list of customers, and growing data volume, the Potenza engineering team had a few challenges to tackle. As data volume grew, they ran into situations where their ETL service stopped syncing data, leading to data loss. On the user side, dashboards started hanging, leaving customers frustrated, and blocking the engineering team with troubleshooting the complaints.
For Daisy’s team, existing customers would complain about poor service quality. In meetings with new prospects, sales reps were constrained to showing static screenshots of the application vs. a live demo.
”Our customers were threatening to cancel their contracts because our dashboards wouldn’t load fast enough”
Aaron laid out three clear goals to make customers happy again and help his teams get a heads up about spikes in wait times.
The Potenza team had tried using the Amazon AWS native diagnostic tools, but found they weren’t enough. Redshift consultants offered their services for a fee as high as over ten thousand dollars per week—with no guarantee that their solutions would work for the long term.
Aaron’s team installed our agent-less collector in their data infrastructure and was immediately able to visualize their key bottlenecks. Aaron prioritized the bottlenecks in order of urgency and had his team attack them as part of their sprints. We also hosted an on-site workshop on Redshift best practices, with a deep dive into queries and workflows.
Aaron’s team had built a popular service and the dealerships were running lots of reports. By using our “Discovery” feature, Aaron’s team saw that especially on Monday mornings sales reps would run reports to get new leads for the week. That high volume of concurrent queries was a major cause for hanging dashboards, because queries got stuck in the queue.
Potenza used our “Throughput Analysis” and “Memory Analysis” features to re-configure the Workload Management (WLM) queues. Queues, concurrency, and memory settings were balanced with the different workloads. The result was a drop in peak queue time for queries from over twenty minutes down to zero. Dashboards were fast again.
Again with our “Discover” feature, Aaron’s team identified the reason for the ETL failures. As the number of customers had gone up, the data pipeline provider Potenza was using would push more data more frequently. As a result, they hit the maximum connection limit of 500 that Amazon Redshift allows. When that limit is reached, more connection attempts will fail with the error “connection limit 500 exceeded for non-bootstrap users”.
Aaron also identified groups of very ad-hoc large queries. Refactoring those queries into smaller steps and moving parts into separate transformations in a different queue took more pressure off the cluster.
By creating custom saved searches in intermix.io for key metrics such “query execution time” or “rows scanned”, the team could track the impact of their changes and measure the success of their work. By tracking this set of SLAs in real-time, the team could address problems before customers would feel any impact.
The end result was healthy Redshift cluster that remains responsive all day. More so, the new cluster configuration and its performance served as the starting point for further growth. Potenza’s reps no longer have to worry about conversations with customers about lagging load times. Instead, they’ve been armed with confidence and are able to offer support to their customers in all the aspects of their various ad campaigns.
”At some point it felt as if the light had been turned on in a dark room; this understanding was worth so much to us.”