W
W
WebAnalytics12018-04-13 11:07:39
Google Sheets
WebAnalytics1, 2018-04-13 11:07:39

Google Query: how to write a query that sums cells by a certain parameter?

There is an array of data `A` "Browser" `B` "Browser Version" `C` "Device Category" `D` "Sessions" `E` "Ecommerce Conversion Rate"
Task: split browsers (with versions) by device type, relevant and outdated (by sessions and conversions).
Problem: you need to split only into PC and Mobile + Tablet (Tablet is not needed separately).
Browser versions in Mobile and Tablet can be the same (but have different values ​​of `D` and `E`), so you need to concatenate Mobile and Tablet by `B`, summing up `D` (for example, "65.0.3325.112" tablet has 95 sessions and hits old and "65.0.3325.112"; mobile has 115 sessions and hits new; we need "65.0.3325.112" to hit new with session 210.)
The separating part could be written:

Query(Import;"Select B Where A = '"&A1&"' and C !='desktop' and ((D < "&'Данные для формул'!$B$1&") or (D > "&'Данные для формул'!$B$1&" and E =0)) order by B desc")

Where `A1` = browser names
`Data for formulas'!$B$1` = number of sessions

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question