Data Studio
1.9K members online now
1.9K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Can we use SUMPRODUCT formula any way? (weighted cost/conv)

Follower ✭ ✭ ☆
# 1
Follower ✭ ✭ ☆

Hi,

 

I'm trying to get weighted cost per conversion. Let's say this is my data:

 

Conversions (per keyword) Cost. Conv

100$22
10$1

 

If I use "average", I get $11.5 as average cost conv. Which is not real. In spreadsheets I can use SUMPRODUCT this way:

 

=SUMPRODUCT(conversions,cost conv)/SUM(conversions)

 

As in here:

 

Orange Audience October - Google Sheets 2016-11-04 17-17-22.jpg

 

 

I tried creating a caculated field in Data Studio, but it won't work :-/ Any ideas? Thanks!

 

 

Can we use SUMPRODUCT formula any way? (weighted cost/conv)

Follower ✭ ✭ ☆
# 2
Follower ✭ ✭ ☆

Hi again,

 

I just came up with a workaround. It's not really what I need, but...

 

I added a new column to the data source (I named it "CPL"):

 

=Conversions * Cost/Con.

 

Then, in Data Studio I added a new calculated field (named "CPL SUMPRODUCT):

 

=CPL/Conversions, which aggregates as SUM.

 

It worked :-)