Who is the Client

The client is a US-based skincare product manufacturer. They have millions of customers in the United States, Canada, and Australia, who exclusively depend on this brand for their skincare and makeup routine.

The Challenge

The client’s marketing team runs various email campaigns using Salesforce Marketing Cloud, to promote their products and keep the customers engaged with their brand. Customers' responses to these campaigns are analyzed by the number of clicks, bounced messages, email open rates, and more. These responses are collected in Salesforce Marketing Cloud as valuable data that is later used to evaluate the campaign's success, analyze what is working, and plan subsequent campaigns.

Data plays a critical role in these campaigns, making data accessibility a powerful and strategic enabler for marketers. To ensure that this data is accessible to everyone, it is first reported from Salesforce Marketing Cloud into Enterprise Data Warehouse (EDW) and then in MicroStrategy platform (a Business Intelligence software) where it is converted into highly formatted reports that enable quick and logical understanding. All stakeholders access the data from there to make informed decisions.

However, the client conducted an audit of the last two-year campaign data and discovered that the entire data was not getting reported from Salesforce Marketing Cloud into MicroStrategy. This inaccurate data reporting led to incorrect campaign monitoring that resulted in: inadequate assessment, poor decision making, and missed opportunities. The client wanted to prevent such a scenario in the future and ensure accurate data reporting.

The Solution

We analyzed the challenge and deduced that the client needed better alignment between the platforms from where the data was flowing, i.e., the Salesforce Marketing Cloud to Enterprise Data Warehouse to the MicroStrategy platform. We identified the scenarios where data was not reported by developing custom audit hooks in the process flow. Once all these incidences are captured, an alert is sent out (daily) as data files (with a complete report of the missed data), notifying the marketing team that a specific event needs to be investigated by raising a Jira ticket on the incidence.

The solution was delivered by leveraging Dell Boomi which generates Salesforce Marketing Cloud data as files. These files are then ingested into Enterprise Data Warehouse from where the reports are generated on the missed data files. This built-in stored procedure is used to identify the incidences.

The automated email alerts enabled the client to capture the right data at the right time and conduct a detailed analysis of the campaigns. This empowered their team to analyze customers’ responses to their campaigns, make informed decisions, and optimize campaign performance and the marketing team’s productivity.

Business Impact

  • Automated email triggers with data files ensured accurate reporting and actionable insights.
  • Access to accurate campaign data enabled the stakeholders to make informed decisions.
  • Observed a significant improvement in the overall campaign performance.

Technologies Used

SQL Server Management Studio (SSMS). An integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database
SSIS Package. A SQL Server Integration Services that include components such as the connection manager, tasks, control flow, data flow, parameters, event handlers, and variables, to execute a specific ETL task
Dell Boomi AtomSphere. A cloud-based, multi-tenant platform used to integrate various software applications, enabling the transfer of data between on-premise apps and the cloud

Related Capabilities

Utilize Actionable Insights from Multiple Data Hubs to Gain More Customers and Boost Sales

Unlock the power of data insights buried deep within your diverse systems across the organization. We empower businesses to effectively collect, beautifully visualize, critically analyze, and intelligently interpret data to support organizational goals. Our team ensures good returns on the big data technology investment with effective use of the latest data and analytics tools.

Do you have a similar project in mind?

Enter your email address to start the conversation