E-Commerce Analytics – How Datameer Spotlight helps improve Marketing ROI by identifying High Lifetime Value Customers

  • Benoite Yver
  • January 13, 2020

Increased online shopping means more e-commerce analytics than ever before.

It is a well known and undisputed fact that customers are doing more and more of their shopping online. Traditional brick and mortar stores continue to lose market share to e-commerce. 

While increasing customer convenience, online retail has also helped reduce costs such as those associated with owning or leasing a physical store in a potentially prime location, hiring staff for customer service and checkout and requiring to carry almost all SKUs at all times on the shelves for a better customer experience.

Challenges to e-commerce retailers

It has, however, created other challenges that e-commerce retailers need to deal with.

Less opportunity for impulse shopping

There is no real equivalent of foot-traffic to the store, users can easily check for the best price for a product without having to walk from store to store and there is less opportunity for impulse/unplanned shopping that typically happens while walking through a store, unless the merchant can provide a sophisticated e-commerce experience including product recommendations.

Lack of historical customer data

E-commerce giants such as Amazon have optimized the online shopping experiences over time with the help of several years of customer purchase data and artificial intelligence-based recommendation engines making accurate product recommendations.

This can be hard for smaller merchants due to either the lack of historical purchase data or the availability of a sophisticated recommendation engine or both. Another problem that e-commerce merchants grapple with is cart abandonment. Users can easily get distracted by other activities on their devices such as notifications from news or social media apps and navigate away from the e-commerce site, leaving behind a full cart.

Increase Cost Per Acquisition

The challenges associated with e-commerce can significantly increase the cost per acquisition (CPA or CAC) of a customer and reduce the overall return on marketing investment (ROI), particularly if the customers don’t come back and shop at the online store again, i.e., they have a low Lifetime Value (LTV).

Hence it is essential for an ecommerce merchant to know who their highest lifetime value customers are and what are the most efficient ways to acquire them in order to run a profitable online retail business.

The ability to identify high LTV customers, knowing their CPA/CAC and consequently the ROI depend heavily on the availability of the right data from various sources, the ability to easily combine and analyse it and finally leverage it to make marketing decisions to optimize towards acquiring the highest LTV customers.

Where to find E-commerce Analytics

E-commerce analytics and marketing data is often spread across disparate sources such as,

  • Transaction data in an e-commerce platform such as Shopify or Magento
  • Advertisement spend data in individual ad platforms such as Facebook, Google, Instagram, Youtube and more
  • Attribution data, i.e., which channel/campaign drove how many transactions in a platform like Google Analytics
  • Customer demographics and lifetime spend data from a CRM system such as Salesforce or Hubspot

E-commerce Analytics Use Case

Let’s look at a use case and framework that we could use to identify the highest lifetime value customers and how to acquire them most efficiently by combining and analyzing the data sources outlined above.

The Business Questions

  • What are the characteristics of customers that drive the highest ROI on marketing spend over their lifetime?
  • Which marketing channels and campaigns are most effective at acquiring customers that drive the highest ROI?

E-commerce Analytics Approach

Coming up with the right analytics approach or framework for answering a business question is the crucial first step in any analytics project.

For this use case, we will use the following framework.

  1. Essential metrics to measure
    1. Cost Per Acquisition
    2. 12-month spend since acquisition (a proxy for LTV)
  2. Granularity at which to compute these metrics
    1. Marketing Channel and Campaign
    2. Customer Age Range and Gender
  3. The Key Performance Indicator (KPI) measured at the desired granularity
    1. ROI = (12-month spend / Cost Per Acquisition) – 1

Data Required

Data is typically scattered across disparate sources within an organization. Moreover, different teams own different data and have expertise in the understanding and usage of that data. Discovering the right data to use for an analysis requires close collaboration between analytics teams within an organization. You can read about the importance of collaboration in analytics in this blog post.

Here’s the data the use case under discussion uses,

  • Customer Profile (Source: Salesforce)
      • Demographics (age and gender)
      • Purchase History – Date of acquisition, subsequent purchases, 12-month spend since acquisition
  • Ad Spend (Source: Individual Ad Platforms)
      • Facebook Ads, Google Ads, Other display and social channels
  • Customer Acquisition Data
    • Number of customers acquired by channel and campaign (utm parameters)  (Source: Google Analytics Last Non-Direct Touch Attribution)
    • Acquisition Channel (utm parameters) for each customer (Source: Shopify)


Data Preparation

In the final dataset we require the following information for each customer – customer ID, Age Range, Gender, 12-month spend since acquisition, Acquisition Channel, Acquisition Campaign, Cost per Acquisition for the channel and campaign, Computed ROI. Blending the various sources requires us to have at least one column between data sources we intend to blend. We could blend the datasets mentioned above on matching columns as follows to create the final dataset.

  • Shopify and Salesforce data on order ID to create an intermediate dataset, say Intermediate Dataset 1
  • Google Analytics and Ad Spend data on utm parameters for campaign and channel to create another intermediate dataset, say Intermediate Dataset 2
  • We then create the final dataset by blending IntermediateDataset1 and IntermediateDataset2 on utm parameters


Combining siloed data sources to create a unified dataset required for your analysis can be quite cumbersome involving multiple extracts from different systems, manual blending of the datasets in a tool like microsoft excel and analysing the data either right in excel or loading the final dataset back into a source that can be accessed by a business intelligence (BI) or Data Science tool. A solution such as Datameer Spotlight can let analytics teams virtually access, blend and prepare datasets from siloed data sources from a single point of access without having to move the underlying data and connect the resulting dataset to any BI or Data Science tool of their choice.

Analysis and Insights

Once we have the final dataset, it can easily be analysed in Excel or any Business Intelligence (BI) tool such as Tableau or Looker to get granular insights on who the high LTV customers are and which channels and campaigns have been responsible for acquiring them. Here are a few examples of insights that can be derived from such an analysis.

  • 25-44 year old customers drive the highest ROI over their lifetime, with women driving a slightly higher ROI than men in this age group
  • Facebook drives higher ROI among women, while Instagram does so among men
  • Heavily promotional Black Friday campaigns drive lower ROI than Father’s Day campaigns

One can get more granular and slice channel and campaign data by demographics to see which channels and campaigns have driven the highest ROI among which gender and/or age group. Insights such as these can help marketing teams allocate the appropriate budget to specific channels and campaigns and fine-tune the audiences that the campaigns are targeting. One could also think about bringing in other customer data such as engagement with email marketing programs or transactional emails to see the incremental impact of these on LTV. The breadth of insights is limited only by the breadth of one’s hypotheses and the actionability of the insights.

See it in Action

We have created a demo that illustrates how Datameer Spotlight can let analysts run such analyses in siloed data landscapes by seamlessly connecting to disparate data sources, preparing the required dataset, collaborating with other team members and analyzing and sharing the insights using Tableau.

Instant Access To Our Free Library Of Resources

Discover the Top ETL and Data Integration Platforms


Featured Blog Posts

Five Critical Success Factors To Migrate Data to Snowflake
Five Critical Success Factors To Migrate Data t...

You’ve decided to modernize your data and analytics stack and migrate analytics workloads to the ...

  • John Morrell
  • May 10, 2021
ETL++: Reinvigorating the Data Integration Market

(This article first appeared on Medium on April 6, 2021.) The definition of “++” means incrementa...

  • John Morrell
  • April 12, 2021
Spectrum ETL
Disrupting the no-code cloud ELT market: Datame...

More than just loading Data: Datameer launches Datameer Spectrum ETL++ to disrupt the no-code clo...

  • Press Release
  • February 9, 2021
Google Partners with Datameer
Datameer Partners with Google Cloud to Deliver ...

Datameer is now a Google Cloud migration partner The partnership will help customers build secure...

  • Press Release
  • December 2, 2020

More Resources We Think You Might Like

Top 5 Fivetran competitors

Top 5 Fivetran Competitors and Alternatives

What is Fivetran?  Fivetran is a cloud-based ELT integration tool that teams can use to synchroni...

  • Justin Reynolds
  • June 15, 2021
The Simplest Road to a Modern Data Stack with Snowflake

The Simplest Road to a Modern Data Stack with S...

The first building block of a cloud data stack starts with Snowflake.  Your analytics engine and/...

  • John Morrell
  • June 14, 2021
Top 5 Matillion Competitors

Top 5 Matillion Competitors and Alternatives

Matillion ETL Review Matillion is a cloud-based ETL tool that enables teams to create and orchest...

  • Justin Reynolds
  • June 10, 2021

Updating your ETL? Your guide to the 10 things to consider when modernizing your ETL.