Reference outer variables in nested select in bigquery vs mysql
Try removing the GROUP BY from the main query and converting the join to PRFXad_reports to an EXISTS subquery:

SELECT
    ads.ad_id,
    ( SELECT count(*)
      FROM `PRFXvisits` AS v
      INNER JOIN `PRFXvisit_data` AS vd
      ON vd.visit_id = v.id
      WHERE v.date_time &gt;= @xday AND v.date_time &lt; @xdaytomorrow
      AND ads.id = vd.ad_id
    ) AS clicks
FROM `PRFXads` AS ads
WHERE ads.is_archive = 0
  AND EXISTS
      ( SELECT 1
        FROM `PRFXad_reports` AS r
        WHERE r.ad_id = ads.id
          AND r.rdate = @xday
      )
ORDER BY ads.ad_id ;