The company is a global group of petrochemical and energy companies with operations in over 70 countries. It has over 47,000 retail service stations and serves over 1 million commercial customers, along with 33 million retail customers, on a daily basis.
THE CHALLENGE
The company has a global loyalty program through which its customers can accrue loyalty points based on the total value of purchases that are made. However, the loyalty program for the UK market allows customers to gain points based on the number of visits to their fuel stations, instead of their basket size. As a result, the incentives and offers which are periodically rolled out for loyalty customers in the UK market differ from what is found in other global markets.
The UK Loyalty team used to track several metrics related to visits per customer, retail category, fuel sales versus retail store sales and so on, through loyalty dashboards built on a Business Intelligence (BI) tool. However, these dashboards were decommissioned 6 months ago.
Without access to these key metrics, the Loyalty team was unable to analyse the performance of the on-going programs or plan for any new marketing programs for different target segments. Since their loyalty model was different from what was followed in all other global markets, they could not refer to the performance of the other global loyalty programs in markets of similar sizes, where the customer loyalty behaviour was in line with the UK market.
Overall, the company needed a data partner to prepare the data and develop a consolidated set of loyalty dashboards which contained all the previously reported metrics, as well as define and visualize new KPIs which could help them accurately analyse the effectiveness and outcomes of all their loyalty program activities.
THE SOLUTION
The team from Prescience Decision Solutions was provided the screenshots of the previously created UK market specific loyalty dashboards, based on which they had to re-engineer the new dashboards. There was no way for the Prescience team to get insights into the previous data & dashboard preparation processes.
The UK Loyalty team wanted to move to a set of new data sources which were consistent with their global operations. Hence, the Prescience data engineering team had to reach out to different stakeholders from other countries to understand the source systems and tables, metrics being monitored by the loyalty teams in each country etc. Based on this, the dashboards were designed and the queries were created to extract all the relevant data from the global source systems.
The new dashboards for the UK market included count of registrations for the loyalty program, number of visits, customers that have responded to campaigns through the year, new registered versus new transacted loyalty customers, active customers per month, customer activity after signing up for the loyalty program, number of loyalty transactions etc. Other metrics which were created included the cumulative fuel consumption, visits per customer, average spend per customer, fuel penetration, with the corresponding daily, weekly and monthly percentage changes.
Additionally, KPIs such as the total fuel consumption per day, spend on regular fuel versus premium fuel versus retail purchases, the most popular day of the week for fuel purchases and each type of fuel, etc were represented in various charts and tables. The dashboards also included a GBP to USD converter, to help global loyalty business users access the reports and benchmark the results against other markets.
The different technologies used for this engagement included,
- Microsoft Power BI
- MS SQL
- Azure Databricks
THE IMPACT
With the comprehensive new loyalty dashboards, the company’s UK team once again had end to end visibility into the success of their ongoing programs. These updated metrics helped the team get detailed insights into their existing loyalty customers and understand their purchasing patterns. Accordingly, the UK Loyalty team now designs and implements new programs catered to the local market and users. Since their fuel stations recorded consistently higher consumption on particular days, the company can run targeted campaigns for their loyalty customers on other days, to balance the number of visitors and improve the overall customer experience.
