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
select user_id, min(transaction_timestamp) as first_transaction_timestamp from user_transaction_details
group by user_id
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.
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!