In Google Data Studio, I'm reporting on data which shows purchases (well, really donations) over a particular time frame. Each purchase shows a transaction ID, the customer ID who made that purchase, and the date of their first purchase.
What I'm trying to do is to get the number of first-time purchases over the course of the report date range. So I want to see how many purchases were made over the date range AND how many of those purchases were customers that were purchasing for the very first time. ie, "Dec 3, 2020: Purchases: 40. First-time purchases: 17."
How can I do that in a Data Studio field when that first-purchase field is always populated with something?
Thanks!
With the information you provided, I suppose your Data Source also has a column named Purchase Date (which stores when that particular transaction happened).
Assuming this is true, you can create a new calculated column with the difference, in seconds, between the purchase date and the first purchase date:
Column First Purchase Diff: DATETIME_DIFF(Purchase Date, First Purchase Date, SECOND)
With this column, you can easily detect if that particular transaction is the first purchase (=zero difference between dates), with a new calculated column:
Column Is First Purchase: CASE WHEN First Purchase Diff = 0 THEN 1 ELSE 0 END
Now, just summarize the number in this column to show how many first purchases (or donations) you have in a particular period.