CODEWITHSUNDEEP
looker-studiogoogle-analytics-4
Ian Lockwood
Ian Lockwood

Reputation: 1

Looker Studio: Conditionally add integers to aggregated metrics dependent on month

We use GA4 and Looker Studio for our reporting and for a period in Nov and Dec GA4 was not receiving transaction data. We have estimated the number and value of the missing transactions, which I now need to add in to our reports.

This means adding a fixed number to the data from GA4, e.g. there were 57 more transactions in November than GA4 reports. I want to add 57 to the reported transactions figure in Looker Studio.

I have tried using a CASE statement as a calculated field like this:

CASE
When Year month="Nov 2023" Then SUM(Transactions+57)
Else Transactions
End

That doesn't work because you can't re-aggregate metrics.

I have also tried blending the GA4 data with a simple spreadsheet containing Year Month, Transactions and Revenue (with the figures being the missing data), then using a calculated field to add the two transaction figures together. Same issue.

Another option was to have the full number of transactions for November in the sheet and conditionally use that if the month of the report in Looker was set to November, but otherwise to use the GA4 data. I can't get that to work either.

Is there any relatively simple way of doing this? I can imagine if you effectively pulled all the GA4 data in a Google Sheet and conducted the addition of the 57 transactions in there, then pulled it to Looker, that would work fine, but that's a lot of data being pulled into a sheet just to add the number 57!

It might be worth noting that the transaction and revenue figures are also used in comparative metrics in the reports, e.g. percentage increase/decrease MoM and YoY. So the "full" data (including the extra 57 transactions) needs to be available for those too.

Upvotes: 0

Views: 188

Answers (1)

Max_Stone
Max_Stone

Reputation: 634

Looker studio does not make this easy, I would use a Blend to do this.

Blending

  1. Create a google sheet with 2 columns one with the date using exactly the same wording as your in date field in GA4, the other called transactions in in date put the date in the same format used in GA4 (it should be at the lowest level so probably a date and a month) and in transactions put 57.
  2. load sheet into Looker studio and blend with your main tables explained in this link https://support.google.com/looker-studio/answer/9061420?hl=en#:~:text=Blending%20data%20lets%20you%20create,a%20single%20Looker%20Studio%20table.

This will now show and any time you have an outage you can just add the missing sales to that google sheet.

Upvotes: 0

Related Questions