I work as a digital analyst for an ecommerce company. We use Google Analytics (UA) to record data. There was a tracking bug whereby for any given unique purchase, the metric Product Revenue was over-recorded. Specifically, the Product Revenue for a unique purchase was recorded as Quantity * Quantity * Unit Price, whereas of course it should be Quantity * Unit Price.
For example, if a blue hat costs £5 and within the same transaction 3 blue hats were purchased, the Product revenue was recorded as 3 * 3 * 5 = 45, instead of 3 * 5 = 15.
In a Looker Studio dashboard, I would like to build a custom field that reverses this issue to show us the true Product Revenue.
This is where the order of summation issue comes in. If I build a table of each individual unique purchase, it is straightforward for me to create a custom field in which the Product Revenue for that unique purchase is divided by the Quantity. However, when this is aggregated over multiple purchases, Looker Studio FIRST sums the Product Revenue and the Quantity and THEN divides one by the other, whereas of course I need the division to happen first and THEN the summation.
For example, I have made this public Looker Studio dashboard using the Google Merch Store UA demo account data. https://lookerstudio.google.com/reporting/46bee446-d803-4221-b8dc-c6f9dcd6d694
I have a table where each row is a unique purchase. I have called the recorded product revenue value "PR Recorded". I have derived a corrected Product Revenue field called "PR Fixed" by making a calculated field of "Product Revenue / Quantity". For each individual row, this gives the correct Product Revenue value.
So, to get the total Product Revenue Fixed in the summary row at the bottom, I want Looker Studio to simply sum all of these correct individual rows, which would give 4.8 + 1.6 + 20.8 + 20.8 + 2.4 + 4.5 = $54.90
However, as you will see in the table, instead what Looker does is sums the total Product Revenue for all rows, sums the total quantity for all rows, and then divides one by the other which gives us only $310.1/65 = $4.77 in total Product Revenue.
| Date | Transaction ID | Product | Product SKU | Quantity | PR Recorded | PR Fixed |
|---|---|---|---|---|---|---|
| Feb 8, 2023 | 467979 | Google Classic Black Lanyard | GGOEGCBB208599 | 50 | $240 | $4.8 |
| Feb 8, 2023 | 467979 | For Everyone Sticker | GGOEGFKA196399 | 6 | $9.6 | $1.6 |
| Feb 8, 2023 | 467979 | Google Unisex Eco Tee Black | GGOEGAAB118915 | 1 | $20.8 | $20.8 |
| Feb 8, 2023 | 467979 | Google Women's Eco Tee Black | GGOEGALB119013 | 1 | $20.8 | $20.8 |
| Feb 8, 2023 | 467979 | Super G Small Cable Organizer | GGOEGOBB181499 | 6 | $14.4 | $2.4 |
| Feb 8, 2023 | 467976 | Android Patch Organizer | GGOEACBA198899 | 1 | $4.5 | $4.5 |
| --- | --- | --- | TOTAL | 65 | $310.1 | $4.77 |
Is it possible to change the order of summation? How can I create a calculated field that will be correct when aggregated across all unique purchases? Please let me know if you can help! Many thanks
Below is the intended output:
| Date | Transaction ID | Product | Product SKU | Quantity | PR Recorded | PR Fixed |
|---|---|---|---|---|---|---|
| Feb 8, 2023 | 467979 | Google Classic Black Lanyard | GGOEGCBB208599 | 50 | $240 | $4.8 |
| Feb 8, 2023 | 467979 | For Everyone Sticker | GGOEGFKA196399 | 6 | $9.6 | $1.6 |
| Feb 8, 2023 | 467979 | Google Unisex Eco Tee Black | GGOEGAAB118915 | 1 | $20.8 | $20.8 |
| Feb 8, 2023 | 467979 | Google Women's Eco Tee Black | GGOEGALB119013 | 1 | $20.8 | $20.8 |
| Feb 8, 2023 | 467979 | Super G Small Cable Organizer | GGOEGOBB181499 | 6 | $14.4 | $2.4 |
| Feb 8, 2023 | 467976 | Android Patch Organizer | GGOEACBA198899 | 1 | $4.5 | $4.5 |
| --- | --- | --- | TOTAL | 65 | $310.1 | $54.90 |
If you want to fix the summation of that final column, instead of using a calculated field for the subtotal you can click on your table and under the tab 'set-up' there should be a heading called 'Summary row' just under where you select the number of rows you want your visualisation to show. If you check the box it should show the subtotal for each numeric column.