=LET( lastRow, MAX(FILTER(ROW('Daily Report'!B:B), 'Daily Report'!B:B<>"")), _startRow, lastRow - 27, _numRows, 28, campaign, CHOOSEROWS('Daily Report'!B:B, SEQUENCE(_numRows, 1, _startRow)), date, CHOOSEROWS('Daily Report'!C:C, SEQUENCE(_numRows, 1, _startRow)), impression,CHOOSEROWS('Daily Report'!E:E, SEQUENCE(_numRows, 1, _startRow)), click, CHOOSEROWS('Daily Report'!F:F, SEQUENCE(_numRows, 1, _startRow)), downloading,CHOOSEROWS('Daily Report'!G:G, SEQUENCE(_numRows, 1, _startRow)), downloaded,CHOOSEROWS('Daily Report'!H:H, SEQUENCE(_numRows, 1, _startRow)), install, CHOOSEROWS('Daily Report'!I:I, SEQUENCE(_numRows, 1, _startRow)), panel_cpi, ARRAYFORMULA(IFS( campaign="Prime", 15000, campaign="SearchAds", 10000, campaign="SmartAds", 15000, campaign="SearchAds - Branded", 10000, TRUE, 0 )), cost, MAP(install, panel_cpi, LAMBDA(ins, cpi, ins * cpi)), cvr, MAP(install, impression, LAMBDA(ins, imp, IFERROR(ins / imp, 0))), data, HSTACK(campaign, date, cost, impression, click, downloading, downloaded, install, panel_cpi, cvr), QUERY(data, "select *", 0) )
=LET(
lastRow, MAX(FILTER(ROW('Daily Report'!B:B), 'Daily Report'!B:B<>"")),
_numRows, 124,
_startRow, lastRow - _numRows + 1,
campaign, CHOOSEROWS('Daily Report'!B:B, SEQUENCE(_numRows, 1, _startRow)),
date, CHOOSEROWS('Daily Report'!C:C, SEQUENCE(_numRows, 1, _startRow)),
impression, CHOOSEROWS('Daily Report'!E:E, SEQUENCE(_numRows, 1, _startRow)),
click, CHOOSEROWS('Daily Report'!F:F, SEQUENCE(_numRows, 1, _startRow)),
downloading, CHOOSEROWS('Daily Report'!G:G, SEQUENCE(_numRows, 1, _startRow)),
downloaded, CHOOSEROWS('Daily Report'!H:H, SEQUENCE(_numRows, 1, _startRow)),
install, CHOOSEROWS('Daily Report'!I:I, SEQUENCE(_numRows, 1, _startRow)),
panel_cpi,
ARRAYFORMULA(
IFS(
campaign="Prime", 15000,
campaign="SearchAds", 10000,
campaign="SmartAds", 15000,
campaign="SearchAds - Branded", 10000,
TRUE, 0
)
),
cost, MAP(install, panel_cpi, LAMBDA(ins, cpi, ins * cpi)),
cvr, MAP(install, impression, LAMBDA(ins, imp, IFERROR(ins / imp, 0))),
data,
HSTACK(
campaign,
date,
cost,
impression,
click,
downloading,
downloaded,
install,
panel_cpi,
cvr
),
QUERY(data, "select *", 0)
)
=LET( lastRow, MAX(FILTER(ROW('Daily Report'!B:B), 'Daily Report'!B:B<>"")), _startRow, lastRow - 27, _numRows, 28, campaign, CHOOSEROWS('Daily Report'!B:B, SEQUENCE(_numRows, 1, _startRow)), date, CHOOSEROWS('Daily Report'!C:C, SEQUENCE(_numRows, 1, _startRow)), impression,CHOOSEROWS('Daily Report'!E:E, SEQUENCE(_numRows, 1, _startRow)), click, CHOOSEROWS('Daily Report'!F:F, SEQUENCE(_numRows, 1, _startRow)), downloading,CHOOSEROWS('Daily Report'!G:G, SEQUENCE(_numRows, 1, _startRow)), downloaded,CHOOSEROWS('Daily Report'!H:H, SEQUENCE(_numRows, 1, _startRow)), install, CHOOSEROWS('Daily Report'!I:I, SEQUENCE(_numRows, 1, _startRow)), panel_cpi, ARRAYFORMULA(IFS( campaign="Prime", 15000, campaign="SearchAds", 10000, campaign="SmartAds", 15000, campaign="SearchAds - Branded", 10000, TRUE, 0 )), cost, MAP(install, panel_cpi, LAMBDA(ins, cpi, ins * cpi)), cvr, MAP(install, impression, LAMBDA(ins, imp, IFERROR(ins / imp, 0))), data, HSTACK(campaign, date, cost, impression, click, downloading, downloaded, install, panel_cpi, cvr), QUERY(data, "select *", 0) )
| =LET( lastRow, MAX(FILTER(ROW('Daily Report'!B:B), 'Daily Report'!B:B<>"")), _startRow, lastRow - 27, _numRows, 28, campaign, CHOOSEROWS('Daily Report'!B:B, SEQUENCE(_numRows, 1, _startRow)), date, CHOOSEROWS('Daily Report'!C:C, SEQUENCE(_numRows, 1, _startRow)), impression,CHOOSEROWS('Daily Report'!E:E, SEQUENCE(_numRows, 1, _startRow)), click, CHOOSEROWS('Daily Report'!F:F, SEQUENCE(_numRows, 1, _startRow)), downloading,CHOOSEROWS('Daily Report'!G:G, SEQUENCE(_numRows, 1, _startRow)), downloaded,CHOOSEROWS('Daily Report'!H:H, SEQUENCE(_numRows, 1, _startRow)), install, CHOOSEROWS('Daily Report'!I:I, SEQUENCE(_numRows, 1, _startRow)), panel_cpi, ARRAYFORMULA(IFS( campaign="Prime", 15000, campaign="SearchAds", 10000, campaign="SmartAds", 15000, campaign="SearchAds - Branded", 10000, TRUE, 0 )), cost, MAP(install, panel_cpi, LAMBDA(ins, cpi, ins * cpi)), cvr, MAP(install, impression, LAMBDA(ins, imp, IFERROR(ins / imp, 0))), data, HSTACK(campaign, date, cost, impression, click, downloading, downloaded, install, panel_cpi, cvr), QUERY(data, "select *", 0) ) | عالی |
| =LET( lastRow, MAX(FILTER(ROW('Daily Report'!B:B), 'Daily Report'!B:B<>"")), base, INDEX('Daily Report'!B:K, lastRow-27+SEQUENCE(28), ), campaign, INDEX(base,,1), date, INDEX(base,,2), impression, INDEX(base,,4), click, INDEX(base,,5), downloading, INDEX(base,,6), downloaded, INDEX(base,,7), install, INDEX(base,,8), panel_cpi, IFS( campaign="Prime", 15000, campaign="SearchAds", 10000, campaign="SmartAds", 15000, campaign="SearchAds - Branded", 10000 ), cost, install * panel_cpi, cpi, panel_cpi, cvr, IFERROR(install / impression, 0), data, { campaign, date, cost, impression, click, downloading, downloaded, install, panel_cpi, cvr }, QUERY(data, "select *", 0) ) |