Thorn Technologies leverages Amazon Redshift to develop a solution that provides a top-tier wireless carrier insight into customer engagement with SMS messages.
The Problem
Thorn Technologies designed and developed a top-tier wireless carrier’s SMS marketing platform, which runs on Amazon Web Services and delivers over 4 million messages a day to subscribers. With so many messages delivered, the client wanted a better way to measure user-level engagement and learn more about how recipients interacted with their messages.
Many of the messages sent by the client contain URLs linking to mobile web content. But in order to tie click behavior to a particular recipient, the URL needs to be unique for each message sent. To facilitate the insertion of unique URLs into each message and the collection and processing of this massive amount of click behavior data, Thorn Technologies had to take a different approach in designing the client’s database architecture.
The Solution
Thorn Technologies designed a robust database architecture to log each outbound message, its unique URL, and click behavior metrics on a massive scale.
The architecture of the SMS messaging system involves several services with independent but related datasets. All of this data needed to be combined to produce several customer reports and to support real-time queries from a reporting application. Redshift was a great fit as a Data Warehouse since it can support datasets with billions of records in a performant manner.
To gather all the data into one store, JSON logs from the independent services were saved to a single S3 bucket. Each time a file was created on S3, a message was posted to the SQS queues. The queues were periodically checked with a Lambda and an ECS task started when there were new files in the queue. Those files are then batch-loaded into Redshift staging tables and processed into their final Data Warehouse format using SQL commands inside the ECS task.
As the dataset grows and demand for reporting increases, the number of Redshift nodes in the cluster can be scaled to meet any requirement. The current 6-node cluster handles loading of over 9 million records per day while averaging 20% CPU usage evenly distributed across the nodes.
Across the tables, there are 384 Million records for January to April 2018 alone. The reports that aggregate data for the past 30 days finish processing in less than a minute. The ECS ETL process has reliably transferred every record in S3 to the Redshift Data Warehouse.
The Results
Because of this robust data warehouse architecture, the client now has near real-time insight into the effectiveness of their SMS campaigns and can use this knowledge to create more targeted, relevant marketing campaigns.
The client can:
- Easily determine whether a recipient engaged with a particular text message. Based on the recipient’s behavior, the client can determine follow-up messages to send or other actions to take.
- Count how many times a recipient clicked through to mobile web content, further understanding customer behavior.
- Determine whether a user has received the message on their phone or if it failed to deliver.
- Assess the overall success of a campaign
This solution allows the client to better understand customer behavior extremely quickly and has unleashed the power of SMS marketing for this wireless carrier.
