You’re using Google’s Universal Analytics — That’s great! They key to make sure that you get the most out of it is to make sure that you incentivize your users to log-in aka authenticate. First step in doing that is to figure out percentage of users that are authenticated…Here’s how you can see that report:
1. Login to Google Analytics
2. Select your view > Go to “Reporting” section
3. Navigate to Audience > Behavior > User-ID coverage
4. On this report, you can see authenticated vs unauthenticated sessions:
In this post, we talked about how to run a report that shows you percentage of authenticated users. (In google’s Universal analytics)
How would get first/last transaction details for each user account?
Here’s a design pattern:
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
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!