Return the total and average customer spend
Last updated
Last updated
When we look up a customer, we’ll want to know how much they have spent and what is their average spend. This helps determine how much value the business is providing them.
1. Navigate to the Query Editor.
2. Open a new tab by clicking on the + sign:
3. We’ll be querying AggregateCustomerPurchaseActivities collections in order to calculate this. When we created the collection, we created daily rollups based on the user_id. Instead of grabbing the customer’s spend based on the daily timeframe, we’re interested in their total and average spend. Our query will look like this:
Action Item: In the new empty editor, paste this code:
Major SQL code highlights:
We added lines 1-10.
On lines 2-3 we are summing and averaging the customer's spend. We’re using the ROUND() function so the values go up to 2 decimal places.
On lines 7-8 we are specifying the parameter, :userid, we want the query results to return for.
On lines 9-10 we are grouping by the user_id.
4. Run the query. You should get results similar to this:
NOTE: You can find us on the Rockset Community if you have questions or comments about the workshop.
