Customer

SQL: How to get first/last transaction details for each user account?

Standard

Looking at user’s first/last transaction details is a common requirement. So given that you have a “user_transaction_details” table which looks something like:

Transaction_id | user_id | transaction_date | purchase amount

How would get first/last transaction details for each user account?

Here’s a design pattern:

[code language=”SQL”]

select AllTransactions.user_id,AllTransactions.purchase_amt from user_transaction_details AllTransactions
inner join
(
select user_id, min(transaction_timestamp) as first_transaction_timestamp from user_transaction_details
group by user_id
) FirstTransaction
on AllTransactions.user_id = FirstTransaction.user_id and AllTransactions.transaction_timestamp = FirstTransaction.first_transaction_timestamp

[/code]

To get the last transaction details for each user account, replace min function with max and that should be it.

Note: Depending on your data model and how you used it in the join, it might be that there would be multiple rows marked as “first/last” transaction and so would want to spend some time figuring out how to deal with these multiple row situation especially if you’re planning to further join this data.

Conclusion:
In this post, I shared a design pattern to write a SQL query to get first/last transaction details for each user account type.

Question for you:
How would you solve this problem? Share you query in the comments section!

Example of using segmentation to identify low-margin service offerings:

segmentation data Improve profitability low margin service offerings customers
Standard

Problem:

Need advanced data analytics techniques to analyze profitability data

Solution:

Here’s an example of how customer segmentation helped identify some low margin service offerings:

Improve profitability low margin service offerings customers

New Digital Marketing Analytics Report shows social media is not the best source of acquiring customers:

Standard

It’s great to see Insights that data can uncover. I saw a nice insight in a report I read about Analyzing customer acquisition channels for e-commerce sites and in this blog post, I am sharing it with you. So what are the top customer acquisition channels for Commerce sites? The Top channels are Organic Search, Emails & Paid Search.Here’s the report: E-Commerce Customer Acquisition Snapshot

It was not surprising to me to see Organic Search and Emails being among the Top customer acquisition channels but what surprised me was  relatively poor performance of social media in acquiring customers. Here’s the chart showing performance of various online channels for acquiring customers:

ecommerce analytics percentage of customer acquired vs. channel

Data Source: http://blog.custora.com/2013/06/e-commerce-customer-acquisition-snapshot/

Note #1: The post is NOT about devaluing the benefits of social media and it comes to down to understanding the goals of having a social media presence in the first place. While computing the ROI of social media, there are other factors like increased brand awareness, customer loyalty to be considered. But I posted this data because it’s a great way to show how data can uncover insights and sometimes it may surprise you

Note #2: The percentage of customers acquired does not add up to 100% for a year because the data does not include things like direct traffic. The author of the report confirmed it over an email w/ me.

That’s about it for this post. Your comments are very welcome!

Business Metrics #2 of N: Customer Retention Rate

Standard

In this post, We’ll explore a Business metric called “Customer Retention Rate”

What is it?

It is a metric that helps an organization monitor the % of customers retained.

Let me give you an example:

YearNumber of CustomersRetention Rate
0100100%
18585%
27070%
36565%
46161%

Do you notice the third column that keeps a tab on the percentages of customer retained? This is the basic Idea behind customer retention rate.

How is it used?

This metric correlates with other key business performance measures like: customer service, product quality, customer loyalty. Think about it. If the customer retention rate is higher than the organization must be doing “something” right – that something could be: great loyalty program, great customer service or great product quality! If it’s low then it requires some action from decision makers – they would want to know the reasons so that they could fix the situation.

In earlier post, we talked about Customer Lifetime Value – now higher customer retention rate would also help us have a higher customer lifetime value.

Also it’s important to realize that the cost of acquiring a new customer is typically higher than keeping existing customer – and so organization that sells products/service like to measure the customer retention rate.

Also, if you customer data then you can drill down to find trends in the retention rate. Questions like: Which Age group has the highest retention rate? or which has lower? Retention rate for male customers? And also predicting customer retention rate of a new customer?

Conclusion:

In this post, we learned about a business metric “customer retention rate”.

And as a reminder, This series is meant to understand Business Metrics from Analytics Perspective.

Business Metrics #1 of N: Customer Lifetime Value

Standard

This post will briefly describe an important marketing metric called “Customer Lifetime value”.

What is it?

It’s an important metric in the world of marketing. It helps businesses measure a customer’s worth to a business during the entire business relationship. In other words, it helps a business calculate net profit associated with a customers relationship starting from first purchase AND subsequent purchases along with expected future purchases.

How is it used?

It’s used to measure return on investment when formulating marketing strategies. Here’s an example: If your strategy costs $100 to acquire a customer and the average lifetime value of customer is $400 – then well, that’s a great thing, isn’t it?

It also helps business focus on making the most out of the existing customer relationships.

To extend these examples in the Internet marketing world, let’s take an example:

Suppose that the cost of acquiring a customer via Internet marketing is $25. The customer buys a $10 worth of goods. Is this good? Not from what we’ve seen so far. But the lifetime value of customer is $120 – see, now it does makes sense to spend $25 to acquire a customer.

Conclusion:

In this post, I wrote about a key Business Metric that should be of help when you work on your Marketing analytics project. Note that accurately measusring this metric is NOT an addition of couple of numbers and there is some thinking involved. To that end, I would leave you thinking about this critical business metrics that could be used in marketing analytics project! Your comments are very welcome!