어떻게 도와드릴까요?

검색

Data Clean Room - 동적 쿼리 프리미엄

  • 업데이트 시간

요약:

개요

참고

 

      • appsflyer_id
      • idfa
      • idfv
      • advertising_id
      • android_id
      • 고객_유저_ID
      • ip
    • collect_list
    • collect_set
    • last_value
    • first_value
    • 최대
    • 최소

참고

Group 42053

설명
설치
re_installs
re_engagements
re_attribution
organic_installs

Group 42053 인앱 이벤트:

설명
in_app_events
in_app_organic_installs

Group 42053 노출

설명
노출


  3.  

SELECT app_id, COUNT(*) AS reinstall_count
FROM re_installs
WHERE media_source = 'snapchat_int' AND dt BETWEEN DATE_SUB(current_date(), 3) AND current_date()
GROUP BY app_id
ORDER BY reinstall_count DESC
LIMIT 3;

SELECT media_source, COUNT(*) AS total_installs, ROUND((SUM(CASE WHEN idfa IS NULL THEN 1 ELSE 0 END) / COUNT(*)) * 100, 2) AS null_idfa_rate
FROM installs
WHERE dt BETWEEN DATE_SUB(current_date(), 3) AND current_date()
GROUP BY media_source
ORDER BY total_installs DESC
LIMIT 10;

SELECT media_source,campaign,geo_mean(GREATEST(1, unix_timestamp(install_time, "yyyy-MM-dd HH:mm:ss") - unix_timestamp(attributed_touch_time, "yyyy-MM-dd HH:mm:ss.SSS")))
FROM installs
GROUP BY 1,2

SELECT media_source, sum(c.score)
FROM client_input c JOIN install i on i.appsflyer_id = c.appsflyer_id
GROUP BY media_source;

SELECT conversion_type, SUM(sum_score), MAX(max_age)
FROM (SELECT media_source, conversion_type, SUM(score) as sum_score, MAX(age) as max_age
FROM (SELECT * from install union all select * FROM installRetarget) af
JOIN client_input c on c.appsflyer_id = af.appsflyer_id
JOIN client_age ca on ca.appsflyer_id = c.appsflyer_id
GROUP BY media_source, conversion_type)
GROUP BY conversion_type

SELECT *
FROM install;

-----------------------------------------------------

SELECT media_source, appId
FROM client_input c
JOIN install af on af.appsflyer_id = c.appsflyer_id;

SELECT appsflyer_id, count(*)
FROM install
GROUP BY appsflyer_id

-----------------------------------------------------

SELECT substr(appsflyer_id, 0, 5), count(*)
FROM install
GROUP BY substr(appsflyer_id, 0, 5)

-----------------------------------------------------

SELECT appsflyer_id, count(*)
FROM install
GROUP BY 1


SELECT my_id, count(*)
FROM installs
JOIN my_source on my_source.my_id = installs.appsflyer_id
GROUP BY my_id

SELECT App_id, collect_list(appsflyer_id)
FROM install
GROUP BY app_id

-----------------------------------------------------

SELECT app_id, collect_list(campaign)
FROM (select app_id, campaign
  FROM Install
  GROUP BY 1, 2)
GROUP BY app_id

-----------------------------------------------------

SELECT app_id, collect_list(c.score)
FROM client_input c
GROUP BY app_id

Tags