Formula Help

[saiyamvora13]

Hello,
I needed some help with a few formula's. Here is my data.
Per Advertiser ID, I need to find

Total requests sent with user data

Percent requests sent with user data

Bid volume

Bid rate

Avg bid value (multiply your answer by 1000 to get to CPM)

Win volume

Win rate

Impressions

Avg spend (multiply your answer by 1000 to get to CPM) All help is highly appreciated

<colgroup><col></colgroup><tbody>
</tbody>

Date	advertiser_id	site_id	domain	datacenter	geo	user_accepts_cookies?	user_has_data?	impressions	spend	requests_sent	bid_volume	total_bid_value	total_wins
20150106	537073285	69302	DOMAIN 1	DC 1	us	TRUE	TRUE	0	0	564	0	0	0
20150106	537073285	69302	DOMAIN 1	DC 2	us	FALSE	FALSE	0	0	762	1	1.5	0
20150106	537073285	69302	DOMAIN 2	DC 3	us	FALSE	FALSE	3	5.305	8367	9	14.5	3
20150106	537073285	69302	DOMAIN 3	DC 3	us	TRUE	FALSE	0	0	150	0	0	0
20150106	537073285	69302	DOMAIN 4	DC 3	us	TRUE	FALSE	2	3.125	4109	6	10.5	2
20150106	537073285	69302	DOMAIN 5	DC 3	us	TRUE	FALSE	4	6.18	1853	14	17.5	4
20150106	537073285	69302	DOMAIN 6	DC 2	ca	TRUE	TRUE	0	0	61	0	0	0
20150106	537073285	69302	DOMAIN 6	DC 4	ph	TRUE	TRUE	0	0	1	0	0	0
20150106	537073285	69302	DOMAIN 7	DC 3	ca	TRUE	TRUE	0	0	4	0	0	0
20150106	537073285	69302	DOMAIN 8	DC 5	us	FALSE	FALSE	2	2.5	7	3	5	2
20150106	537073285	69302	DOMAIN 9	DC 1	ca	TRUE	TRUE	0	0	2	0	0	0
20150106	537073285	69302	DOMAIN 9	DC 2	ca	TRUE	FALSE	0	0	32	0	0	0
20150106	537073285	69302	DOMAIN 9	DC 4	au	TRUE	TRUE	0	0	5	0	0	0
20150106	537073285	69302	DOMAIN 9	DC 4	sg	FALSE	FALSE	0	0	6	0	0	0
20150106	537073285	69302	DOMAIN 10	DC 3	us	TRUE	TRUE	16	27.611	31452	50	73.869	16
20150106	537073285	69302	DOMAIN 11	DC 1	us	FALSE	FALSE	0	0	275	1	2	1
20150106	537073285	69302	DOMAIN 12	DC 2	us	TRUE	TRUE	0	0	451	1	2.94	0
20150106	537073285	69302	DOMAIN 13	DC 1	us	TRUE	FALSE	2	3.125	260	2	4	2
20150106	537073285	69302	DOMAIN 13	DC 1	us	TRUE	TRUE	5	10.145	1511	14	23	5

<colgroup><col span="7"><col><col span="6"></colgroup><tbody>
</tbody>

20150106	536871256	18965	DOMAIN 154	DC 3	us	TRUE	FALSE	0	0	8	0	0	0
20150106	536871256	20001	DOMAIN 1064	DC 3	us	TRUE	FALSE	0	0	41854	0	0	0
20150106	536871256	22520	DOMAIN 1065	DC 3	us	TRUE	FALSE	0	0	50	23	6.9	0
20150106	536871256	30395	DOMAIN 1066	DC 3	us	TRUE	FALSE	0	0	1	0	0	0
20150106	536871256	30395	DOMAIN 1067	DC 1	us	TRUE	FALSE	5507	1650.442	399709	380860	114258	5705
20150106	536871256	30395	DOMAIN 1067	DC 2	us	TRUE	FALSE	0	0	345067	1893	567.9	0
20150106	536871256	30395	DOMAIN 1068	DC 3	us	TRUE	FALSE	0	0	1	0	0	0
20150106	536871256	53058	DOMAIN 154	DC 3	us	FALSE	FALSE	0	0	30	0	0	0
20150106	536871256	61666	DOMAIN 1069	DC 3	us	TRUE	FALSE	0	0	1	0	0	0
20150106	536871256	61666	DOMAIN 1070	DC 2	us	TRUE	FALSE	0	0	2783	0	0	0
20150106	536871256	61666	DOMAIN 1071	DC 2	us	TRUE	FALSE	0	0	178	0	0	0
20150106	536871256	61666	DOMAIN 1072	DC 1	us	FALSE	FALSE	0	0	1	0	0	0

<colgroup><col span="7"><col><col span="6"></colgroup><tbody>
</tbody>

 

[Tetat12]

This data set is perfect for a pivot table! However, pivot tables do have some restrictions and if you want more freedom in formatting i suggest using the sumifs formula or sumproduct.

 

[saiyamvora13]

How would I get the win rate and bid rate using a pivot table?
Would you be able to give me the formula?

 

[Tetat12]

You would add total_wins to the pivot table column twice and on the second column right click and hit show values as... % of grand total. is that how you are calculating win rate?

 

[saiyamvora13]

Bid rate needs to be a ratio of the number of bids compared to the number of qualified events to bid on.
Win rate would be a ration of the no of wins bids compared to the no of bids placed.