Page performance daily

1-- core.linkedin_pages.page_performance_daily
2-- The Updates (content) section of LinkedIn Page analytics: organic impressions,
3-- reach and engagement across all of a Page's posts, per day.
4--
5-- Grain: date_day x organization_id. Days are UTC.
6-- Depends on: staging.linkedin_pages.share_statistics_daily
7--
8-- Organic only. Sponsored impressions and clicks are in LinkedIn Ads, not here.
9-- unique_impressions is unique members per day: summing days overcounts people
10-- who saw posts on several days, so there is no monthly reach to build from it.
11--
12-- engagement_rate is LinkedIn's own figure for the day. To roll up to a week or
13-- month, divide the summed `engagements` by the summed `impressions`; averaging
14-- the daily rate weights a quiet day like a busy one.
15
16SELECT
17    date_day,
18    organization_id,
19    organization_name,
20    impressions,
21    unique_impressions,
22    clicks,
23    likes,
24    comments,
25    shares,
26    clicks + likes + comments + shares                                  AS engagements,
27    engagement_rate,
28    SAFE_DIVIDE(clicks + likes + comments + shares, impressions)        AS engagement_rate_calculated,
29    SAFE_DIVIDE(clicks, impressions)                                    AS click_through_rate
30FROM {{staging.linkedin_pages.share_statistics_daily}}