API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 28th, 2016
84
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.68 KB | None | 0 0
raw download clone embed print report
  1. SELECT st.*,
  2. SUM(coalesce(st.media_spend / st.mobile_conversions, 0)) AS cost_per_mobile_conversion,
  3. SUM(coalesce(st.media_spend / st.mobile_conversion_installs, 0)) AS cost_per_install,
  4. SUM(coalesce(st.media_spend / st.app_clicks, 0)) AS cost_per_app_click,
  5. SUM(coalesce(st.media_spend / st.follows, 0)) AS cost_per_follow,
  6. SUM(coalesce(st.media_spend / st.qualified_impressions, 0)) AS cost_per_qualified_impression,
  7. SUM(coalesce(st.media_spend / st.engagements, 0)) AS cost_per_engagement,
  8. SUM(coalesce(st.media_spend / st.retweets, 0)) AS cost_per_retweet,
  9. SUM(coalesce(st.media_spend / st.clicks, 0)) AS cost_per_click,
  10. SUM(coalesce(st.media_spend / st.impressions, 0)) AS cost_per_impression,
  11. SUM(coalesce(st.clicks / st.impressions, 0)) AS click_thru_rate,
  12. SUM(coalesce(st.media_spend / st.media_views, 0)) AS cost_per_media_view,
  13. SUM(coalesce(st.media_spend / st.video_total_views, 0)) AS cost_per_video_view,
  14. SUM(coalesce(st.video_total_views / st.impressions, 0)) AS video_view_rate,
  15. SUM(coalesce(st.url_clicks / st.impressions, 0)) AS click_rate,
  16. SUM(coalesce(st.media_spend / st.url_clicks, 0)) AS cost_per_link_click,
  17. SUM(coalesce(st.media_spend / st.web_conversions, 0)) AS cost_per_conversion
  18. FROM
  19. (SELECT
  20. to_char(st.start_time, 'YYYY-MM-DD') date,
  21. st.id campaign_id,
  22. twc.name campaign_name,
  23. SUM(coalesce(st.impressions, 0)) impressions,
  24. SUM(coalesce(st.qualified_impressions, 0)) qualified_impressions,
  25. SUM(coalesce(st.app_clicks, 0)) app_clicks,
  26. SUM(coalesce(st.clicks, 0)) clicks,
  27. SUM(coalesce(st.url_clicks, 0)) url_clicks,
  28. SUM(coalesce(st.replies, 0)) replies,
  29. SUM(coalesce(st.retweets, 0)) retweets,
  30. SUM(coalesce(st.likes, 0)) likes,
  31. SUM(coalesce(st.follows, 0)) follows,
  32. SUM(coalesce(st.card_engagements, 0)) leads,
  33. SUM(coalesce(st.video_total_views, 0)) video_total_views,
  34. SUM(coalesce(st.video_content_starts, 0)) video_content_starts,
  35. SUM(coalesce(st.billed_charge_local_micro :: FLOAT, 0) /1000000.0) media_spend,
  36. SUM(coalesce(st.conversion_downloads__metric, 0)) conversion_downloads,
  37. SUM(coalesce(st.conversion_sign_ups__metric, 0)) conversion_sign_ups,
  38. SUM(coalesce(st.conversion_site_visits__metric, 0)) conversion_site_visits,
  39. SUM(coalesce(st.conversion_purchases__metric, 0)) conversion_purchases,
  40. SUM(coalesce(CAST(st.conversion_custom__metric AS INT), 0)
  41. + coalesce(st.conversion_site_visits__metric, 0)
  42. + coalesce(st.conversion_sign_ups__metric, 0)
  43. + coalesce(st.conversion_downloads__metric, 0)
  44. + coalesce(st.conversion_purchases__metric, 0)) conversions,
  45. SUM(coalesce(st.engagements, 0)) engagements,
  46. SUM(coalesce(st.video_cta_clicks, 0)) video_cta_clicks,
  47. SUM(coalesce(st.video_views_25, 0)) video_views_25,
  48. SUM(coalesce(st.video_views_50, 0)) video_views_50,
  49. SUM(coalesce(st.video_views_75, 0)) video_views_75,
  50. SUM(coalesce(st.video_views_100, 0)) video_views_100,
  51. SUM(coalesce(st.video_3s100pct_views, 0)) video_full_views,
  52. SUM(coalesce(CAST(st.conversion_custom__metric AS INT),0)) conversion_custom,
  53. SUM(coalesce(st.conversion_downloads__metric, 0)) conversion_downloads,
  54. SUM(coalesce(st.conversion_purchases__order_quantity, 0)) conversion_order_quantity,
  55. SUM(coalesce(st.conversion_purchases__metric, 0)) conversion_purchases,
  56. SUM(coalesce(st.conversion_purchases__sale_amount, 0)) conversion_sale_amount,
  57. SUM(coalesce(st.conversion_sign_ups__metric, 0)) conversion_sign_ups,
  58. SUM(coalesce(st.conversion_site_visits__metric, 0)) conversion_site_visits,
  59. SUM(coalesce(st.billed_charge_local_micro, 0)) billed_charge_local_micro,
  60. SUM(coalesce(st.mobile_conversion_achievements_unlocked__assisted, 0)
  61. + coalesce(st.mobile_conversion_achievements_unlocked__post_engagement,
  62. 0)
  63. + coalesce(st.mobile_conversion_achievements_unlocked__post_view,
  64. 0)) mobile_conversion_achievement_unlocked,
  65. SUM(coalesce(st.mobile_conversion_payment_info_additions__assisted, 0)
  66. + coalesce(st.mobile_conversion_payment_info_additions__post_engagement,
  67. 0)
  68. + coalesce(st.mobile_conversion_payment_info_additions__post_view,
  69. 0)) mobile_conversion_added_payment_infos,
  70. SUM(coalesce(st.mobile_conversion_add_to_carts__assisted, 0)
  71. + coalesce(st.mobile_conversion_add_to_carts__post_engagement,
  72. 0)
  73. + coalesce(st.mobile_conversion_add_to_carts__post_view,
  74. 0)) mobile_conversion_add_to_cart,
  75. SUM(coalesce(st.mobile_conversion_add_to_wishlists__assisted, 0)
  76. + coalesce(st.mobile_conversion_add_to_wishlists__post_engagement,
  77. 0)
  78. + coalesce(st.mobile_conversion_add_to_wishlists__post_view,
  79. 0)) mobile_conversion_add_to_wishlist,
  80. SUM(coalesce(st.mobile_conversion_checkouts_initiated__assisted, 0)
  81. + coalesce(st.mobile_conversion_checkouts_initiated__post_engagement,
  82. 0)
  83. + coalesce(st.mobile_conversion_checkouts_initiated__post_view,
  84. 0)) mobile_conversion_checkout_initiated,
  85. SUM(coalesce(st.mobile_conversion_content_views__assisted, 0)
  86. + coalesce(st.mobile_conversion_content_views__post_engagement,
  87. 0)
  88. + coalesce(st.mobile_conversion_content_views__post_view,
  89. 0)) mobile_conversion_content_views,
  90. SUM(coalesce(st.mobile_conversion_installs__assisted, 0)
  91. + coalesce(st.mobile_conversion_installs__post_engagement,
  92. 0)
  93. + coalesce(st.mobile_conversion_installs__post_view,
  94. 0)) mobile_conversion_installs,
  95. SUM(coalesce(st.mobile_conversion_invites__assisted, 0)
  96. + coalesce(st.mobile_conversion_invites__post_engagement,
  97. 0)
  98. + coalesce(st.mobile_conversion_invites__post_view,
  99. 0)) mobile_conversion_invites,
  100. SUM(coalesce(st.mobile_conversion_levels_achieved__assisted, 0)
  101. + coalesce(st.mobile_conversion_levels_achieved__post_engagement,
  102. 0)
  103. + coalesce(st.mobile_conversion_levels_achieved__post_view,
  104. 0)) mobile_conversion_level_achieved,
  105. SUM(coalesce(st.mobile_conversion_logins__assisted, 0)
  106. + coalesce(st.mobile_conversion_logins__post_engagement,
  107. 0)
  108. + coalesce(st.mobile_conversion_logins_credits__post_view,
  109. 0)) mobile_conversion_logins,
  110. SUM(coalesce(st.mobile_conversion_rates__assisted, 0)
  111. + coalesce(st.mobile_conversion_rates__post_engagement,
  112. 0)
  113. + coalesce(st.mobile_conversion_rates__post_view,
  114. 0)) mobile_conversion_rates,
  115. SUM(coalesce(st.mobile_conversion_re_engages__assisted, 0)
  116. + coalesce(st.mobile_conversion_re_engages__post_engagement,
  117. 0)
  118. + coalesce(st.mobile_conversion_re_engages__post_view,
  119. 0)) mobile_conversion_re_engages,
  120. SUM(coalesce(st.mobile_conversion_reservations__assisted, 0)
  121. + coalesce(st.mobile_conversion_reservations__post_engagement,
  122. 0)
  123. + coalesce(st.mobile_conversion_reservations__post_view,
  124. 0)) mobile_conversion_reservations,
  125. SUM(coalesce(st.mobile_conversion_searches__assisted, 0)
  126. + coalesce(st.mobile_conversion_searches__post_engagement,
  127. 0)
  128. + coalesce(st.mobile_conversion_searches__post_view,
  129. 0)) mobile_conversion_searches,
  130. SUM(coalesce(st.mobile_conversion_spent_credits__assisted, 0)
  131. + coalesce(st.mobile_conversion_spent_credits__post_engagement,
  132. 0)
  133. + coalesce(st.mobile_conversion_spent_credits__post_view,
  134. 0)) mobile_conversion_spent_credits,
  135. SUM(coalesce(st.mobile_conversion_tutorials_completed__assisted, 0)
  136. + coalesce(st.mobile_conversion_tutorials_completed__post_engagement,
  137. 0)
  138. + coalesce(st.mobile_conversion_tutorials_completed__post_view,
  139. 0)) mobile_conversion_tutorial_completes,
  140. SUM(coalesce(st.mobile_conversion_updates__assisted, 0)
  141. + coalesce(st.mobile_conversion_updates__post_engagement,
  142. 0)
  143. + coalesce(st.mobile_conversion_updates__post_view,
  144. 0)) mobile_conversion_updates,
  145. SUM(coalesce(st.mobile_conversion_shares__assisted, 0)
  146. + coalesce(st.mobile_conversion_shares__post_engagement,
  147. 0)
  148. + coalesce(st.mobile_conversion_shares__post_view,
  149. 0)) mobile_conversion_shares,
  150. SUM(coalesce(st.mobile_conversion_key_page_views__assisted, 0)
  151. + coalesce(st.mobile_conversion_key_page_views__post_engagement,
  152. 0)
  153. + coalesce(st.mobile_conversion_key_page_views__post_view,
  154. 0)) mobile_conversion_key_page_views,
  155. SUM(coalesce(st.media_views, 0)) media_views,
  156. SUM(coalesce(st.mobile_conversion_achievements_unlocked__assisted, 0)
  157. + coalesce(st.mobile_conversion_achievements_unlocked__post_engagement, 0)
  158. + coalesce(st.mobile_conversion_achievements_unlocked__post_view, 0)
  159. + coalesce(st.mobile_conversion_add_to_carts__assisted, 0)
  160. + coalesce(st.mobile_conversion_add_to_carts__post_engagement, 0)
  161. + coalesce(st.mobile_conversion_add_to_carts__post_view, 0)
  162. + coalesce(st.mobile_conversion_add_to_wishlists__assisted, 0)
  163. + coalesce(st.mobile_conversion_add_to_wishlists__post_engagement, 0)
  164. + coalesce(st.mobile_conversion_add_to_wishlists__post_view, 0)
  165. + coalesce(st.mobile_conversion_checkouts_initiated__assisted, 0)
  166. + coalesce(st.mobile_conversion_checkouts_initiated__post_engagement, 0)
  167. + coalesce(st.mobile_conversion_checkouts_initiated__post_view, 0)
  168. + coalesce(st.mobile_conversion_content_views__assisted, 0)
  169. + coalesce(st.mobile_conversion_content_views__post_engagement, 0)
  170. + coalesce(st.mobile_conversion_content_views__post_view, 0)
  171. + coalesce(st.mobile_conversion_invites__assisted, 0)
  172. + coalesce(st.mobile_conversion_invites__post_engagement, 0)
  173. + coalesce(st.mobile_conversion_invites__post_view, 0)
  174. + coalesce(st.mobile_conversion_key_page_views__assisted, 0)
  175. + coalesce(st.mobile_conversion_key_page_views__post_engagement, 0)
  176. + coalesce(st.mobile_conversion_key_page_views__post_view, 0)
  177. + coalesce(st.mobile_conversion_levels_achieved__assisted, 0)
  178. + coalesce(st.mobile_conversion_levels_achieved__post_engagement, 0)
  179. + coalesce(st.mobile_conversion_levels_achieved__post_view, 0)
  180. + coalesce(st.mobile_conversion_logins__assisted, 0)
  181. + coalesce(st.mobile_conversion_logins__post_engagement, 0)
  182. + coalesce(st.mobile_conversion_logins_credits__post_view, 0)
  183. + coalesce(st.mobile_conversion_rates__assisted, 0)
  184. + coalesce(st.mobile_conversion_rates__post_engagement, 0)
  185. + coalesce(st.mobile_conversion_rates__post_view, 0)
  186. + coalesce(st.mobile_conversion_re_engages__assisted, 0)
  187. + coalesce(st.mobile_conversion_re_engages__post_engagement, 0)
  188. + coalesce(st.mobile_conversion_re_engages__post_view, 0)
  189. + coalesce(st.mobile_conversion_reservations__assisted, 0)
  190. + coalesce(st.mobile_conversion_reservations__post_engagement, 0)
  191. + coalesce(st.mobile_conversion_reservations__post_view, 0)
  192. + coalesce(st.mobile_conversion_searches__assisted, 0)
  193. + coalesce(st.mobile_conversion_searches__post_engagement, 0)
  194. + coalesce(st.mobile_conversion_searches__post_view, 0)
  195. + coalesce(st.mobile_conversion_shares__assisted, 0)
  196. + coalesce(st.mobile_conversion_shares__post_engagement, 0)
  197. + coalesce(st.mobile_conversion_shares__post_view, 0)
  198. + coalesce(st.mobile_conversion_spent_credits__assisted, 0)
  199. + coalesce(st.mobile_conversion_spent_credits__post_engagement, 0)
  200. + coalesce(st.mobile_conversion_spent_credits__post_view, 0)
  201. + coalesce(st.mobile_conversion_tutorials_completed__assisted, 0)
  202. + coalesce(st.mobile_conversion_tutorials_completed__post_engagement, 0)
  203. + coalesce(st.mobile_conversion_tutorials_completed__post_view, 0)
  204. + coalesce(st.mobile_conversion_updates__assisted, 0)
  205. + coalesce(st.mobile_conversion_updates__post_engagement, 0)
  206. + coalesce(st.mobile_conversion_updates__post_view, 0)
  207. + coalesce(st.mobile_conversion_payment_info_additions__assisted, 0)
  208. + coalesce(st.mobile_conversion_payment_info_additions__post_engagement, 0)
  209. + coalesce(st.mobile_conversion_payment_info_additions__post_view, 0)
  210. + coalesce(st.mobile_conversion_installs__assisted, 0)
  211. + coalesce(st.mobile_conversion_installs__post_engagement, 0)
  212. + coalesce(st.mobile_conversion_installs__post_view, 0)) mobile_conversions,
  213. 0 mobile_conversion_order_quantity,
  214. 0 mobile_conversion_purchases,
  215. 0 mobile_conversion_sale_amount_local_micro,
  216. 0 mobile_conversion_sign_ups,
  217. SUM(coalesce(st.web_conversions, 0)) conversions,
  218. SUM(coalesce(st.card_engagements, 0)) card_engagements
  219. FROM
  220. twitter_campaign_stats st
  221. INNER JOIN
  222. twitter_campaigns twc ON st.id = twc.campaign_id
  223. WHERE
  224. st.id = ANY(ARRAY['2s4jk']) AND st.placement IS NOT NULL
  225. and st.start_time between '2015-01-01' and '2016-06-30 23:59:59.999999'
  226. GROUP BY
  227. st.start_time, st.id, twc.name
  228. ORDER BY st.start_time, st.id
  229. )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!