Working with an E-commerce subscription model brand in the words of the head of marketing the business was naive about data and analytics. The brand was rich in data but poor in insights. The insights were siloed within different Martech vendors which made it challenging to provide a 360-degree view of the business and the customer. Insights were taken at a very top level from each of the Martech vendors providing a partial view on performance. The insights which were done in excel were used in isolation so when it came to i.e., planning for the next financial year they were blinded.
The Martech vendors in the current stack did not provide a solution to answer the key business questions, the solution was to take data out of each vendor and bring them together to provide a single view of business and marketing performance. The single source of truth was the P&L which looked healthy but there was a lot more to unpack in terms of insights. With overall performance strong but blinded by insights, the plan was to develop key insights to help for 24/25 planning with increased marketing budgets.
Diagnosis
The first step was to understand the business and marketing requirements was critical to ensure the project is delivered against expectations.
To understand the core business and marketing needs this was done in the form of:
- One to one stakeholder interviews: gather their challenges + pain points so far, what they hoped to achieve and if they had a north star goal what would it be.
- Brainstorming sessions: using the insights from the one to one and share those insights and iron out any differences to ensure everyone is on the same page. Also provided an opportunity to dive into what data is available and how they use the data.
This was coming from the following teams: Marketing, E-commerce, and Finance. In total there were 4 stakeholder interviews taken that also contributed to the brainstorming session.
Coming out of the stakeholder interviews the main challenges faced was a common theme:
- There was no alignment with a central measurement framework.
- Not having trust in how LTV:CAC was calculated which undermined performance.
- Data siloed in different Martech vendors, Excel spreadsheets, Google docs. No central place to host the data and provide a ‘single source of the truth’.
- Insights from different teams were taken from the core Martech platforms were siloed but struggled to provide a top-down view on performance.
There was an alignment between stakeholders in what the end goal was which can be summarised at a high level they wanted to understand:
- Understand the behaviours between new customers and returning customers.
- The relationship between retention and churn.
- The channels that are driving membership and product revenue.
- The time it takes for a member to make their first purchase after signing up.
- Understand the behaviours of different customer segments.
- Marketing channel performance and true incremental impact.
- The non-marketing channel impact on performance.
The key areas of focus coming out of the sessions:
- Measurement Framework
- Data Warehouse
- Insights
It was not possible to deliver against all mainly due to time and costs constraints which required buy-in from the business and developing a longer-term plan. What was decided that the focus would be on insights, it was the bigger need for the brand who are data rich but poor in insights. With the right insights it would show value in the performance and help drive the need for an analytics strategy that underpins the business which becomes the long-term play.
Understanding their requirements and pain points the insights would be built around:
- Cohort Analysis
- Time Lag Analysis
- A top level Customer Segmentation
RFM analysis a deep dive into customer segmentation was also considered but pushed back mainly down to the business going through the process of re-calculating lifetime value which they felt was key to better understanding customer segments. Also hoping to hire a Data Analyst who can piece this together so it can also be embedded into the process within the business and relevant teams.
There was also discussion if Attribution or Econometrics would be worth exploring for the maturity stage, size of budgets + split budget within channels it was not a viable option and data availability also made it challenging.
One of the recommendations was too on-board a platform like statlas.io or similar which will allow the team and business to be far nimbler and go deep into the numbers then worrying about investing time into crunching the numbers.
Analytics
Cohort Analysis
It was clear that Cohort Analysis will provide the insights to understand the characteristics around how users are being acquired and purchasing behaviours over the course of the membership. Which aligned to the head of marketing goal in being able to understand historic performance to help plan and Cohort Analysis provides insights into the long-term health of the business.
Cohort Analysis provides the ability to gain a deeper understanding of the customers and their behaviours. What will come out of the Cohort Analysis:
- Analysis of the business health: With the focus on revenue and growing revenue being able to understand how and when customers are getting acquired and retained. The behaviours of cohorts of customers during their membership lifecycle and this will help focus on what the priorities should be.
- Understand customers better: Tracking customers behaviour over a period will help identify any patterns and trends.
- Better segmentation: Understanding customer cohorts and their behaviours, use those insights to executing marketing campaigns.
- Improve performance of key metrics: Understand how different metrics are impacting business performance and plan how to improve those metrics.
Identifying the questions ensuring there is a focus on the insights that can be delivered:
- What impact does seasonality have on acquiring new customers and retaining customers?
- How much revenue is generated from 1st purchases in a month?
- How long does it take a customer who signed up to make their 1st purchase?
- What is the AOV of the 1st purchase v 2nd purchase?
- What is the distribution of how the customers cohorts spend?
- Which channels are driving revenue and how does this vary for memberships v products?
In addition to the Cohort Analysis will be looking expand out the insights to look at Time Lag and Customer Segments.
Time Lag Analysis
Time lag is critical to understand trends and how consumer behaviours are changing. The key events being when a user signs up to be a member to when they make their first purchase.
The key insights to understand:
- Time to make first purchase after signing up to be a member
- AOV for the first purchase
- Time to make second purchase after making first purchase
- AOV for the second purchase
These insights then allow marketing comms to be adapted to drive the first purchase and second purchase while trying to push up the AOV.
Customer Segments
A more detailed customer segmentation is needed to be undertaken through RFM but at a high level wanting to understand the % revenue and % customers against each segment
| No of Items | Customer Segment |
| 7+ | Cannot lose them |
| 6 | Champions |
| 4-5 | Loyal Customers |
| 2-3 | Needs Attention |
| 1 | At Risk |
| 0 (signed up member but not purchased) | Hibernating |
KPI’s
Alignment of KPI’s was a big step in getting stakeholders to understand what KPI’s mean where the insights will be very different. There was an over focus on transactional KPI’s where it had to be focused on subscription focused KPI’s. Conversion rate was a common KPI used to understand performance which has value in a transactional model not as much in a subscription model.
With the right questions being asked adopting the right KPI’s determines the success. The core KPI’s to understand performance
- Churn Rate
- Retention Rate
Insights
With the core KPI’s agreed it helped mapping out the insights in terms of a decision tree. This helped with getting stakeholders aligned in the insights that will be delivered.
Collecting the right data
Getting the right data and the right structured data was critical for the analysis. It was imperative to have the following data:
- Customer ID
- Order ID
- Date of Order
- Revenue
- Type of Purchase
- Source & Medium (if possible)
The ideal dataset would look like this:
| Customer ID | Order ID | Date of Order | Revenue | Type of Purchase |
| ABC123 | 74561 | 01/02/2022 | £75.00 | Membership |
| ABC123 | 75120 | 10/02/2022 | £90.00 | Product |
The challenge came there was no one source that provided all the data there were multiple Martech vendors in play that had access to the required data.
The key Martech Vendors:
- Google Analytics: provides a marketing channel and campaign view.
- Big Commerce: provides a customer, orders, and product view.
- Net Suite: source of truth for all orders & inventory.
The requirement was to connect different data sources together which was connected by the Order ID which is the Primary Key.
The first step is to merge the different datasets to get this view before any code can be built for analysis.
| Customer_ID | Order_ID | Order_Date | Purchase_Type | GA_Source-Medium | NetSuite
_Revenue |
Process
Having a clear process ensuring there is transparency for all in driving the success of the project.
All the stages were key in process, the main ones were:
- Post the merging of the data sources providing a top line view of the data. This was important as the merged view has not been available to the team so making sure they are aware of the data that is going to be modelled against.
- Providing a snapshot view of the data that’s being modelled against to get some feedback from the team also to ensure there’s no major surprises when the data is presented back.
The 
