Activating the Data Warehouse for an Ecommerce Retailer using Reverse ETL
In our previous blog, we introduced the concept of the modern data stack with “Reverse ETL” emerging as the missing piece in the puzzle and discussed why the businesses of today need to adopt it really fast. Let’s continue by delving into the use cases that the modern data stack can bring to life for the businesses.
There are numerous use cases across major verticals ranging from Financial Services to Retail, and even for Technology & Telecom companies. Reverse ETL can armor the operational teams with data where they need it the most i.e. on the downstream apps like Salesforce and HubSpot they are most familiar with, and at the time when they can make the maximum impact.
To illustrate this, we will reference an imaginary ecommerce retailer named “BeautyClub”. Recently launched, BeautyClub is trying to make a breakthrough in the online beauty segment already saturated with big horizontal and vertical players. Let’s assume that they have invested in the right technologies to set up the cloud data warehouse and deployed ELT/ELT solutions to move the transactional and visitation data on a near real time basis. As with most of the start-ups, they have a relatively small engineering team, focused mainly on building product features like recommendation algorithms and search engines, which are core to the success of their product in this market.
BeautyClub has also invested in setting up an Analytics Layer on top of their warehouse, following suit from other companies in the space. As a result, their Marketing and Brands teams get automated reports and dashboards with aggregated metrics including sales, new customer acquisitions and website/app traffic at a regular cadence.
So far, so good. But, while these reports are great for the higher management to track their achievements against the projections, it unfortunately does not help the teams on the ground take any meaningful actions to improve or course-correct the numbers. For example, the Brand Manager handling a critical account gets to understand that the sales of his or her brand has declined compared to previous week but has no means to change the same. Perhaps, what would be more useful for the operational teams is to get data across all the customers’ touch points on the app or website and have the ability to influence the same. Accommodating and Reacting to the customer behavior is where the real growth can happen and should be equally important to the business as developing any core capability. This is where Reverse ETL can come into play and open a range of opportunities for them.
Shaping the Customer Journey
With increased brand marketing spends through ATL campaigns, BeautyClub is able to attract a lot of new users to their app and website. Some of these users go on to create a login, while the others bounce straight off the landing screen. A few go a step further, add their selections to the cart but drop off due to pricing or other issues. And a fraction of them place their first order, but unfortunately it gets delivered late. As acquisition and retention of customers becomes extremely critical in the highly competitive world, the marketing team will need to intervene across every stage of a users’ journey on an almost real time basis to shape their journeys in a more meaningful way. This will include the following through their CRM or Ads systems:
· On creation of user login, send welcome email with tutorials on how to navigate the app and showcasing testimonials from other customers to allay any fears in the minds of new users
· Price Drop Alerts on the Abandoned Carts
· Token of Apology with additional coupon offers if the product is delivered later than the promised date or if the user experienced payment failures while placing their order
· Push Notification if any product(s) the customer was browsing over the past week is back in stock
· Targeting customers who have purchased a particular brand with a recently launched line of their products
Reverse ETL pipeline can be set up to sync each of these customer lists (for example, the customers who abandoned their carts in previous hour or those who created a new login in the previous day) to the CRM or Ads platform (HubSpot, Google Ads etc.) on a pre-decided frequency. The marketing team or brands team can easily add more pipelines as and when they discover more touch points they want to influence, and without any dependency on the engineering team.
Additionally, Reverse ETL can play a very important role in making available results from any future machine learning models developed by the Data Sciences team like the Churn Propensity Score or the customer’s Brand Affinity to the downstream apps to strengthen the messaging in their campaigns and drive objectives like cross-sell or upsell better. Integrations with A/B platforms can help scale experimentation and drive further growth for the business.
Launching the Loyalty program
Suppose one year down the line, BeautyClub decides to launch their loyalty program, with a co-branded credit card offering cashbacks, to a selected base of its customers. They invite responses from the customers to enroll themselves into the program. Since the program is meant to be an exclusive one, BeautyClub wants to audit the list of applicants and select only the ones who meet their criteria.
On the ground, the analysts of the Loyalty team will rely on a tool like Salesforce to review the applications of the customers and make decisions on whether to approve or reject the same. For this, they will need information including the customer’s recent purchases, demographics and maybe some external data like their credit score, previous loan history etc. Again, Reverse ETL can simplify this process of getting this data from the warehouse and help in formulating this rules engine. Further, it can help the team sync a list of top customers for whom the applications can be automatically approved so that BeautyClub doesn’t miss out on the top customers joining their program.
Simplifying Customer Support
Customer Experience and Support is vital to the success of any modern retailer. Given every new business wants to run a lean support organization to start with, faster query resolution becomes paramount for organizations like BeautyClub.
Recurring pipelines between the Data Warehouse and Support Tools like Intercom will ensure that the Support team has 360 view on customer’s data and past complaints history, and are able to utilize the same to resolve the customer queries in lesser time. Additionally, during big sale events characterized by peak in customer calls, they may want to prioritize the requests of their Loyalty Program members first to surgically drive better retention. A straight-forward Reverse ETL pipeline to sync customer’s loyalty program status (member or non member flag) to the Support tool will solve this easily for them.
Adhering to Data Protection Laws
Interestingly, a new yet critical use case for Reverse ETL is emerging because of stringent Data Protection Regulations and Laws across the globe (Europe’s GDPR is a good example). Now, a customer has the right to information on the data collected about him/her by the internet companies and can request for the same to be deleted. And if requested, it becomes mandatory for the companies to abide by the laws and delete the customer data across all the systems.
It may be relatively simple to delete the specified rows for a customer from the warehouse. But given the disparate downstream systems used by various teams in the organization, deleting in each of them becomes an operational headache for the data compliance team. This again can be solved easily by the Reverse ETL solution which provides an option to update or delete certain records. A pipeline with the user records to be deleted can be created and affected across all the systems in a seamless manner.
Enabling Reverse ETL using Castled
Re-emphasizing what we discussed in the last blog, many will argue that BeautyClub’s engineering team can create these connections between data warehouses and downstream systems as and when required by the business teams. However, based on prior experiences working in similar companies, we have seen that most of these ad-hoc solutions are unreliable, dependent on API calls which often timeout or reach upload limits, thus delaying and diminishing the impact of the multitude of use cases listed above. Further, a company like BeautyClub will want their engineers to implement features like cross sell recommendations or improve search on their app rather than work on creating and maintaining pipelines for their operational teams. Hence, there is a need for a dedicated solution catering to Reverse ETL just like there is one for forward ETL/ELT pipeline solutions to complete the modern data stack.
Castled is a dedicated Reverse ETL solution which can help businesses periodically sync their data & insights to their downstream tools reducing the dependency on the data & engineering teams. Anyone with basic knowledge of SQL and their data schema can set up a pipeline with minimal effort. Castled allows refreshes of pipelines at a frequency of even a minute and is optimized to transfer large volumes of records, making it ideal for ecommerce and other data-intensive companies. Castled follows all the standard security protocols to ensure that the data is encrypted the moment it leaves the warehouse until it reaches the destination tool.
Castled currently supports the major cloud data warehouses including Snowflake, Google BigQuery, Amazon Redshift and provides pre-built connectors to major apps like Salesforce, HubSpot and Intercom. And the list of apps that will be supported will grow in the coming months as part of the product roadmap.
You can visit Castled at https://castled.io/ and sign up to try the platform for free. Additionally, to know more about the platform, book a free demo here.
Update: Castled has pivoted out of a Reverse ETL solution to provide a better way to solve the same problem. Castled is a Warehouse-Native Customer Engagement Platform built natively on top of the cloud data warehouses like Snowflake, BigQuery, Redshift, etc. Please read our blog to understand why we made this pivot.
