Build Internal Apps in Minutes with Retool and Rockset: A Customer 360 Example
December 17, 2020
Rockset and Retool are teaming up to help you build internal apps in minutes. Rockset allows developers to turn complex analytics into data APIs simply, while Retool delivers the UI building blocks to quickly launch high-performance internal apps. Together, they empower developers to build performant internal tools, such as customer 360 and logistics monitoring apps, by solely using data APIs and pre-built UI components.
In this blog, we’ll be building a customer 360 app using Rockset and Retool. Customer journeys are complex: customers may browse multiple products while shopping, interact with product reviews and emails in various ways, exhibit changing purchasing behavior over time, and more. This customer 360 app provides real-time insights into customers’ activities that enable a company to provide better customer support and personalized experiences.
Overview of the Customer 360 App
Our app will make use of real-time data on customer orders and events. We’ll use Rockset to get data from different sources and run analytical queries that power our app in Retool. We won’t need to build any data pipelines or do any ETL, and recently generated data will in fact show up in our analysis within a matter of seconds.
For our example, DynamoDB will store customers’ orders, and we will get the customer_events stream through Amazon Kinesis. Each source contains:
- DynamoDB:What the customer bought, returned, ordered, the product they bought, their purchase date, and their returned date.
- Amazon Kinesis: Events that reflect various customer interactions, including
customer_id, event type (whether they left a product review, whether they responded to an email), and event details (review ratings, customer satisfaction survey results).
Essentially, Rockset is an indexing layer on top of DynamoDB and Amazon Kinesis, where we can join, search, and aggregate data from these sources. From there, we’ll create a data API for the SQL query we write in Rockset. Retool will make an API request to Rockset so we can visualize how customers interact with products and services.
Here’s a diagram of how data will flow in the customer 360 setup:
Rockset: Turn real-time analytical queries into data APIs
Rockset is a real-time indexing database that allows you to run fast analytics—search, aggregations, and joins—across multiple data sources, like DynamoDB and Amazon Kinesis, and much more. If you need to create a custom integration, you can use the Write API to perform streaming ingest into Rockset. Rockset automatically builds multiple indexes on the data you’ve ingested to speed up a wide range of analytical queries.
In our example, we’ll provide READ permissions to Rockset, so that we can stream data from DynamoDBand Amazon Kinesis into Rockset collections. Once you connect a data source to Rockset, you can start constructing queries via the Query Editor. From there, you can turn your SQL queries into APIs with just a button click via Query Lambdas. Query Lambdas are named, parameterized SQL queries stored in Rockset that apps can execute from a dedicated REST endpoint. We’ll configure Retool to hit our Query Lambda endpoints, so we can execute our queries, retrieve the results, and visualize them.
Retool: Build internal tools by easily connecting to backend APIs
Retool is a low-code platform that allows you to connect pre-built drag-and-drop UI components, like tables and charts, to custom backend functions like REST APIs. Retool handles all the overhead logic, such as security, so you can focus on your apps.
Retool provides ready-made templates of internal tools you may want to build. For this blog, we’ll be using the customer support tool template. In this template, we’ll view and manage all our customer support interactions. Retool allows you to interact with most databases via a REST, GraphQL, or gRPC API. For our example, we’ll be using REST to pull data from Rockset. When we run a query on Retool, it will proxy the request to Rockset using a Query Lambda. Throughout this process, Retool won’t store any data that is coming from Rockset.
Now that we’ve laid the groundwork for how everything works together, let’s start building our app!
Our First Query in Rockset and Retool
In this first part of our example, we’ll focus on a simple SQL query and familiarize ourselves with the Rockset and Retool environments. Afterwards, we’ll focus on more complex queries and create an internal tool to visualize how our customers are interacting with products and services.
Deploy a SQL Query as an API on Rockset
Once we’ve connected our data sources and created data collections in Rockset, we can start writing queries. On Rockset, we can use SQL queries to extract meaningful insights from raw semi-structured data ingested without a predefined schema. In other words, Rockset does not require a schema but is nevertheless schema-aware, coupling the flexibility of schemaless ingest at write time with the ability to infer the schema at read time. For example, we don’t need to understand how data in your data source is structured upfront, but once data flows in from DynamoDB to Rockset, we’re able to see the Available Fields in our collection and construct queries based on these fields:
Embedded content: https://www.youtube.com/embed/FXsfWLa9j6E
When we navigate to the Query Editor, we can write a simple query with those fields: Embedded content: https://gist.github.com/nfarah86/3133f1d99a98142bcf24720b11ed60f5
Once we write our queries, we can run it and receive the results:
Embedded content: https://www.youtube.com/embed/V7wvshFexVc
But, you’ll notice we won’t be able to filter for specific customers, which would be useful if a customer called customer support with a question. We’ll need to adjust this query to have parameters for a customer’s name and email: Embedded content: https://gist.github.com/nfarah86/fd4bf950877a33ec50347737b20b1443
On lines 11-12, you’‘ll notice that we are using a parameter for :email and :name. Rockset allows you to add parameters so you can dynamically pass in values of interest—the customer’s name and email in this case. At the bottom, you’ll see a parameters tab where you can add custom parameters:
Embedded content: https://www.youtube.com/embed/NCA4JGhkgmY
In Retool, the parameters will be used to filter for a specific customer. From here, we can turn this SQL query into a data API endpoint via a Query Lambda. On top, click on Create Query Lambda, and fill out the details. Once created, Rockset will take you to another page that will provide instructions on how you can use the endpoint. This is the endpoint we’ll be using in Retool:
Embedded content: https://www.youtube.com/embed/3g6rUDSGXp8
Populating our Retool app with data from Rockset
Once you’ve logged into Retool, go ahead and launch the customer support tool. This is one of many templates that Retool created so we can build internal tools fast. We are going to use this as a foundation of our Customer 360 dashboard. The template looks like the image below:
To keep the customer support tool simple, we’ll focus on usersTable and userHeader and remove the other UI components. It should look like this:
You'll see the table is populated by pre-seeded data from Retool. However, we’re going to change this, and populate the data with our data from Rockset. At the top of the Queries side bar, create a new query. We’re going to create a RESTQuery and input the information from Rockset’s Query Lambda:
Embedded content: https://www.youtube.com/embed/2h3Gmhp9KoY
However, the table is not updated! We’ll have to update where the table is pulling data from—-currently it’s pulling from Retool’s pre-seeded database. Click on the usersTable and change {{users.data}} to {{display_customers.data.results}}. By doing this, we change which Retool query we use and, thus, which backend Retool calls from. The query, display_customers, is the query we created on Retool that calls Rockset’s Query Lambda’s endpoint:
Embedded content: https://www.youtube.com/embed/i0AvG0an5-U
The parameter in Retool needs to be passed with emailSearch.value and nameSearch.value. Why are we passing it these particular values?
When you click on the top of the usersTable, you’ll see an Email label that allows you to type the customer’s email. This particular item is named emailSearch on Retool. Similarly, when you click on the Name label, you’ll see the item is named nameSearch:
Embedded content: https://www.youtube.com/embed/-WBfw_cV5WQ
Now, when we type a customer’s name or email in the input box, the respective search terms are passed as a parameter to the Rockset query. Afterwards, the table dynamically updates with the customer’s information that’s coming from Rockset.
Building Out Our Customer 360 App
We’re going to continue building a customer 360 app where a customer support associate can view customers’ activities: what they bought, what was refunded, emails they opened, surveys they’ve given, and more. When an associate converses with the customer, they can handle the customer’s situation appropriately.
Deploy data APIs to see customers’ activities
Rockset is best suited for analytical queries where you need to join, search, and aggregate data sources to get real-time insights. Earlier, we wrote a simple query to understand Rockset’s and Retool’s environments. Now, we're going to get hands-on with more complex analytics.
We will query the customer_events stream from Amazon Kinesis and the orders table from DynamoDB to see who our customer is and their activity:
- What items they purchased
- Whether they bought items through a store or online
- Their surveys and ratings on products
- If they opened an email
- If they got refunded for a particular item
The analytical query we will write that extrapolates these critical questions looks like this: Embedded content: https://gist.github.com/nfarah86/d584770565a9ede40fb88f4e672b0b6b
In Retool, the parameter, :customer_id will be used to filter for a specific customer. Now, let’s go ahead and create a Query Lambda called find_customer_events.
Visualize customers’ activities in Retool
Let’s navigate back to our UI board on Retool, where we have the modified customer success tool template from earlier. Similar to before, create a Retool query where we will put the Query Lambda find_customer_events details into the request information. I named this query display_customer_events:
Embedded content: https://www.youtube.com/embed/uGjm_Mj2P1w
The parameter on Retool is replaced with the value from the row that’s selected in the usersTable, {{usersTable.selectedRow.data.customer_id}}. For example, when I select kelly@email.com, you’ll see she has a customer_id that’s 2 in the parameter. This is the customer_id that will be specified to the Rockset query when it is run.
Now, let’s drag a new table component to our board. The new table we just dragged and dropped should have the Data value that calls {{display_customer_events.data.results}}:
Embedded content: https://www.youtube.com/embed/EL2zU9sFVXg
Now, when we select the row that has kelly@email.com, the customer event data in the table is updated with Kelly’s activities.
Here, I show the customer_id so you can see the relationship between the 2 tables:
If you wanted to write more analytical queries that gets additional insights, you can have a customer 360 application that looks like this:
Embedded content: https://www.youtube.com/embed/BrX28XIj8v8
The bar chart displays categories Kelly makes frequent purchases. The line chart shows her average daily sales for January and February. This will provide a customer support associate a better view of what products Kelly would most likely be interested in and how valuable a customer she is.
This wraps up our customer 360 app with Rockset and Retool! In this example, we saw how users can easily create data APIs in Rockset, using complex SQL queries directly on any data, and build high-performance internal tools using Retool’s pre-built UI components. The combination of Retool and Rockset allows anyone to build incredibly useful internal tools in a matter of minutes.
Authors:
Ben Rogojan is a data engineer at Archeron Analytics.
Nadine Farah is a senior developer advocate at Rockset.