GoogleBigQueryで強引に売上の着地見込みを集計する

目的

• GoogleBigQueryで強引に売上の着地見込みを集計します。非エンジニア向けです。
• もっとスマートな書き方もあるはずなので、エンジニアのみなさんお手すきでレビューください。
• toBなら土日のユーザー数は減りますし、toCなら増えるので、ちゃんとやるなら土日とかを考慮すべきだと思います。

Query

SELECT
    month,
    DATE_DIFF(last_day_of_month,last_date, DAY) as remaining_days/*当月残日数*/,
    passing_days/*経過日数*/,
    revenue/passing_days as ad_revenue_per_day/*1日あたりの売上*/,
    ROUND(revenue+((revenue/passing_days)*(DATE_DIFF(last_day_of_month,DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY), DAY)))) as landing_forecast /*着地見込み*/
FROM
    (
    SELECT
        CONCAT(SUBSTR(CAST(dt as string),1,7),"-01") as month,
        COUNT(distinct CAST(dt as date)) as passing_days /*経過日数*/,
        cast(FORMAT_DATE('%Y-%m-%d',  DATE_ADD(DATE_ADD(DATE_TRUNC(CURRENT_DATE("Asia/Tokyo"), MONTH), INTERVAL 1 MONTH), INTERVAL -1 DAY)) as DATE)as last_day_of_month /*月末日*/,
        cast(max(CAST(_PARTITIONTIME as date))as date) as last_date /*レポート最終日*/,
        SUM(revenue) as revenue
    FROM `revenue`
    WHERE CONCAT(SUBSTR(CAST(dt as string),1,7),"-01") = CONCAT(SUBSTR(CAST(CURRENT_DATE() as string),1,7),"-01") /*当月*/
    GROUP BY
        month,
        last_day_of_month
    )

考え方

①　集計当月のyyyy/MM/ddの売上
②　1日あたりの売上（①÷経過日数）
③　月末までの残日数
④　①+（②*③）= 着地見込み

• 1日あたりの売上を求める

revenue/COUNT(distinct CAST(dt as date)) as ad_revenue_per_day/*1日あたりの売上*/

• 月末の日付を求める

cast(FORMAT_DATE('%Y-%m-%d',  DATE_ADD(DATE_ADD(DATE_TRUNC(CURRENT_DATE("Asia/Tokyo"), MONTH), INTERVAL 1 MONTH), INTERVAL -1 DAY)) as DATE)as last_day_of_month /*月末日*/