CODEWITHSUNDEEP
sqlgoogle-bigquery
Tom E
Tom E

Reputation: 421

Getting a unique count of events

I'm looking for some guidance as I'm struggling to create a script for the fiddle below which will output a high level table of results.

fiddle = http://sqlfiddle.com/#!9/c4b136/5

Based on the fiddle, I can work out in a spreadsheet that we have:

I want the output to show as something like this, but when I've tried this myself I've had trouble. enter image description here

Upvotes: 0

Views: 121

Answers (2)

forpas
forpas

Reputation: 164064

You should group only by start_date and campaign_name and include DISTINCT and the CASE expressions inside COUNT():

select start_date, campaign_name,
       count(distinct case when status = 'Success' then userid end) as Unique_Sent,
       count(distinct case when status = 'opens' then userid end) as Unique_Opened,
       count(distinct case when status = 'clicked' then userid end) as Unique_Clicked,
       round(100 * count(distinct case when status = 'opens' then userid end) /
              count(distinct case when status = 'Success' then userid end)) as `% Opened`,
       round(100 * count(distinct case when status = 'clicked' then userid end) /
             count(distinct case when status = 'Success' then userid end)) as `% Clicked`
from Main
group by start_date, campaign_name;

See the demo.

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Consider below approach (BigQuery)

select campaign_name, success as TotalSent, opens as UniqueOpens, clicked UniqueClicked,
  round(100 * opens / success, 2) as PercentOpened, 
  round(100 * clicked / success, 2) as PercentClicked 
from (
  select * except(start_date, status_date)
  from Main
)
pivot (count(distinct Userid) for lower(status) in ('success', 'opens', 'clicked'))

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

Related Questions