How can I match a sequence of numbers with the specific columns based on their names in BigQuery with the SQL query result groupped by this sequence?

Question

I am working with two tables in BigQuery and I need the achieve the following result:

ret_d	ad_arpu	pur_arpu
d1	x	x
d2	y	y
dx	etc	etc

A bit of clarification: ARPU is average revenue per user, which in this case consists of revenbue from purchases (pur_arpu) and from ads (ad_arpu).

I need to return pur_arpu and ad_arpu next to the corresponding day that customer has been retained, i.e. retention day #x (ret_dx). Arpu is revenue divided by number of customers at a certain period of time. I need arpu per user per retained day.

In the table I am working with I have the following relevant columns

C_id	ret_d1	ret_d2	ret_dx	ad_rev_d1	ad_rev_d2	ad_rev_dx	pur_rev_d1	pur_rev_d2	pur_rev_dx
id1	0 OR 1	0 OR 1	0 OR 1	x	x	x	x	x	x
id2	0 OR 1	0 OR 1	0 OR 1	y	y	y	y	y	y
idx	0 OR 1	0 OR 1	0 OR 1	etc	etc	etc	etc	etc	etc

c_id - customer id,
ret_dx - retained day #x, 1 stands for user retaining on that day,
ad_rev_dx - revenue from ads on dx,
pur_rev_dx - revenue from purchases on dx.

My idea of the SQL query was to return a sequence of numbers, ad_arpu, and pur_arpu from subquery. So something alogn these lines

SELECT

["d1", "d2", "dx"] AS ret_d,
ad_arpu AS ad_arpu,
pur_arpu AS pur_arpu

FROM
 (SELECT

SUM(ad_rev_d1)/SUM(ret_d1) AS ad_arpu_d1,
SUM(ad_rev_d2)/SUM(ret_d2) AS ad_arpu_d2,
SUM(ad_rev_dx)/SUM(ret_dx) AS ad_arpu_dx,
SUM(pur_rev_d1)/SUM(ret_d1) AS pur_arpu_d1,
SUM(pur_rev_d2)/SUM(ret_d2) AS pur_arpu_d2,
SUM(pur_rev_dx)/SUM(ret_dx) AS pur_arpu_dx

--somehow match ad_arpu_dx and pur_arpu_dx to the corresponding days from the ret_d sequence to get ad_arpu adn pur_arpu

FROM tablex)

But so far my efforts have been getting me nowhere :( Missing how to match arpu numbers and the day sequence numbers.

I really appreciate any tips that could help with this!