Google Data Studio Case Statement To Identify Bots & Cell Phone Towers

The below case statement with rewrite your cities from Google Analytics. Often times a user will be bouncing off a cell phone tower. Each city will be renamed to cell phone provider so we understand that we really don’t know where they are. This script will also identify bots.

CASE 
WHEN REGEXP_MATCH(Service Provider,".*verizon.*")AND REGEXP_MATCH(City,".*.*") AND REGEXP_MATCH(Device Category,".*mobi.*")THEN "Verizon Cell Phone"
WHEN REGEXP_MATCH(Service Provider,".*t-mobile.*")AND REGEXP_MATCH(City,".*.*") AND REGEXP_MATCH(Device Category,".*mobi.*")THEN "T-Mobile Cell Phone"
WHEN REGEXP_MATCH(Service Provider,".*at&t.*")AND REGEXP_MATCH(City,".*.*") AND REGEXP_MATCH(Device Category,".*mobi.*")THEN "AT&T Cell Phone"
WHEN REGEXP_MATCH(Service Provider,".*sprint.*")AND REGEXP_MATCH(City,".*.*") AND REGEXP_MATCH(Device Category,".*mobi.*")THEN "Sprint Cell Phone"
WHEN REGEXP_MATCH(Service Provider,".*google fiber.*")AND REGEXP_MATCH(City,".*Irvine.*") AND REGEXP_MATCH(Device Category,".*desktop.*")THEN "Bot"
WHEN REGEXP_MATCH(Service Provider,".*amazon.*|.*microsoft.*")AND Source="(direct)" THEN "Bot"
WHEN REGEXP_MATCH(Network Domain,".*amazon.*|.*unknown.*")AND Source="(direct)" THEN "Bot"
ELSE City
END
Google data studio case statement for bots and cell phone towoers