Customer Lifetime Value Analysis: Nexus by Omniconvert vs Excel vs Google Analytics

The main difference between a customer intelligence platform, an Excel sheet and web analytics for customer lifetime value analysis is depth versus effort. Nexus by Omniconvert calculates CLV automatically and lets you go from segments to single customers. Excel gives you a customizable formula, but every calculation is manual. Analytics platforms such as GA4 add automated reports on lifetime revenue and cohorts, but they measure visitors, not customers.

Before we built our own customer intelligence tool, we performed CRO and retention audits for our eCommerce clients with Excel sheets, Google Analytics reports and CRM data. The analysis of customer segments was overwhelming, because the data sets varied a lot from one eCommerce platform to another, and we lacked the flexibility to extract insights when new attributes were added.

We were wasting a lot of hours on tasks such as:

• Segmenting the customer base.
• Constructing models to identify the most profitable cohorts.
• Calculating the retention rate and customer lifetime value.

These tedious tasks made us start building a platform that does the work on autopilot. That tool, Reveal, is now the Customer Intelligence layer inside Nexus, with RFM segmentation, lifetime value, NPS analysis, cohort analysis and buying patterns. Below, we compare the three options, walk through a CLV analysis in Nexus, build the Excel formula, and look at what GA4 offers.

Nexus vs Excel vs analytics: which should you use for CLV analysis?

The options also work together. If you have custom attributes in your CRM, such as industry data or different segmentation criteria, you can export the reports and refine them in your preferred business analytics software.

How do you analyze customer lifetime value in Nexus?

The customer lifetime value report is generated automatically once your store is connected. The example below comes from a real webshop analysis we ran for a client, with data from 2019.

What does the CLV calculation take into account?

• The average customer lifespan.
• The customer retention rate.
• The profit margin per customer.
• The discount rate.
• The average customer value per selected interval.

CLV is a predictive measure, so the aggregated data represents the most recent 12 months of your selected reporting interval. For how margin, retention and discount rate fit together, see MIT Sloan Management Review on how you should calculate customer lifetime value.

Start with the CLV report

For this webshop, the average lifetime of a customer was 1.5 years, and the average CLV was around 850 euros. The most valuable customer segment, the Soulmates, had a much higher CLV than the next segment, the Lovers. The remaining segments were almost negligible. The report also showed a spike in December 2019, when the client ran themed campaigns.

Note: Soulmates and Lovers are the segment names used in that 2019 analysis. The current RFM segments in Nexus are Soulmates, Loyal, New, Promising, About-to-Dump and Breakups.

These numbers are useful, but they don't give the full picture yet. The next question is why the difference between the two top segments is so big.

Customer segments overview: what stands out?

The order and customer counts showed that although Soulmates were less numerous than Lovers, they placed more orders. Revenue and margin were also highest for Soulmates, which suggested they order more products, and of higher value, so their average order value is likely higher too.

If we can figure out the profile of our Soulmates, and the brands and products they buy, we can replicate these patterns to turn Lovers into Soulmates.

Buying habits per segment: are there patterns?

We looked only at these two segments, and only at products ordered 10 or more times. Two SKUs stood out: Soulmates bought significantly more of them, yet Lovers bought them too. That is the basis for a campaign that promotes these SKUs and brands to Lovers.

The customer list gave a second insight. Among the top 10 customers there were 2 Lovers. They had placed fewer orders, but their revenue and margin were very good, so they had high potential to become Soulmates. The next step is to look at their order history and preferred brands, then create personalized email campaigns to get them to buy again, or target them with loyalty offers.

Gaps and opportunities: are there quick wins?

One of these Lovers placed an order every 10 days or so. For comparison, one of the Soulmates had an ADBT (average days between transactions) of 1.99 days. Comparing the two customers showed a gap: the Soulmate very often chose Personal Pickup as the delivery method.

That is a growth opportunity: a focused campaign that tells Lovers about Personal Pickup. Nexus pushes RFM segments directly to Klaviyo, Meta Ads and Google Ads, so you can act on this kind of insight without exporting lists.

Cohort analysis: which customers stick?

So far we looked at customers one by one. You can also analyze cohorts to see how sticky customers are from acquisition to their first and second months. In this webshop, the cohort acquired in October 2019 stood out, generating 67.3K in its second month, and the November cohort behaved similarly.

Both cohorts performed best in their first month, immediately after acquisition. In the second month, however, the October customers bought more than the November customers. With this insight, the client could check how each cohort was acquired and what their onboarding looked like. Perhaps one channel acquired less sticky customers, or perhaps October new customers received a discount.

The 2019 analysis also used Universal Analytics for visitor data. There, only 2–3% of a cohort came back in week 1, which pointed to poor retention: onboarding issues, unhappy customers, or customers acquired with discounts. The analysis also found an acquisition channel that brought in only 341 customers, but with much higher revenue per customer than the other channels.

1. Read the CLV report

   Note the average customer lifespan, the average CLV, and any spikes linked to campaigns.
2. Compare the top two RFM segments

   Look at customer count, order count, revenue, margin and average order value.
3. Compare buying habits

   Find products and brands the top segment buys much more often than the next segment.
4. Find high-potential customers

   Look for customers in the lower segment with strong revenue and margin but fewer orders.
5. Compare frequency and preferences

   Check average days between transactions, delivery methods and brands against top customers, and turn each gap into a campaign.
6. Check cohort stickiness

   Compare acquisition cohorts in their first and second months, then trace the best and worst ones back to their channels and offers.

As you can see, this type of analysis needs more than one dashboard. To see the full picture, you need the broader context and the entire customer journey. For Shopify stores, Nexus is available in the Shopify App Store.

How do you calculate CLV in Excel?

If you prefer to analyze customer lifetime value in a spreadsheet, this is the formula we use:

CLV = Avg. monthly transactions per customer x AOV x Avg. gross margin x Avg. customer lifespan (months)

Step by step: 2.4 x 166.67 = 400 in monthly revenue per customer; 400 x 21% = 84 in monthly gross margin; 84 x 8 months = 672 in gross margin over the customer's lifetime.

Keep the limits of a spreadsheet in mind:

• Use one source for transactions and revenue. If the order count behind AOV differs from the transaction count, the inputs no longer describe the same customers.
• This formula does not discount future value. For long lifespans, apply a discount rate, as the MIT Sloan article above explains.
• Every segment is a new sheet. To compare Soulmates with Lovers, or one cohort with another, you repeat the export and the calculation.

To raise the result, work on the inputs: increase average order value, purchase frequency and retention. For how these rates relate, see repeat purchase rate vs retention rate vs churn rate.

How do you analyze CLV and cohorts in Google Analytics 4?

Web analytics is a useful complement, especially for smaller shops that want to start with a tool they already have. Keep one difference in mind: a customer data tool shows how paying customers behave, while analytics data includes visitors as well, identified by browser or app.

Cohort exploration in GA4

In Cohort exploration, you choose who enters a cohort (for example first touch, a transaction or a key event), the granularity (daily, weekly or monthly) and the calculation type (standard, rolling or cumulative). You can break cohorts down by a dimension such as device category.

How to read it: a cohort that few users return from in its first weeks has poor retention. That can point to onboarding issues, customers who weren't happy with the goods, or customers acquired with discount campaigns. To find the reason, compare the cohort's acquisition channels, devices and conversions with a better cohort.

Lifetime value in GA4

The Retention overview report includes a Lifetime value card with the average revenue generated by new users over their first 120 days. For a channel view, the User lifetime exploration shows which sources, mediums and campaigns drove users with the highest lifetime revenue. Lifetime data is available only for users active after August 15, 2020.

How to read it: a channel that brings fewer users but much higher lifetime revenue per user can be worth growing. Check that finding against customer data before you move budget.

Now you have an idea of how the three options work for customer lifetime value analysis. We encourage you to opt for the automated, time-efficient solutions: Nexus for customer data, plus analytics for visitor data.

FAQ: customer lifetime value analysis