PostgreSQL营销报告

Question

我正在写一个查询，它从Google Ads、Microsoft和Taboola获取广告营销数据，并将其合并到一个表中。

该表应该有3行，每个广告公司有4列:流量来源(广告公司)、花费的钱、销售额和每次转换的成本。现在我只处理前两个，直到我把它们弄对为止。整个表的数据应该在给定月份的数据中进行分组。

现在，我得到的结果是来自每个流量来源的多行，其中一些是将几个月的数据合并到成本列中，而不是将给定月份内的成本相加。

WITH google_ads AS
  ( SELECT 'Google' AS traffic_source,
           date_trunc('month', "day"::date) AS month,
           SUM(cost / 1000000) AS cost
   FROM googleads_campaign AS g
   GROUP BY month
   ORDER BY month DESC),

     taboola AS
  ( SELECT 'Taboola' AS traffic_source,
           date_trunc('month', "date"::date) AS month,
           SUM(spent) AS cost
   FROM taboola_campaign AS t
   GROUP BY month
   ORDER BY month DESC),

     microsoft AS
  ( SELECT 'Microsoft' AS traffic_source,
           date_trunc('month', "TimePeriod"::date) AS month,
           SUM("Spend") AS cost
   FROM microsoft_campaign AS m
   GROUP BY month
   ORDER BY month DESC)

SELECT (CASE
            WHEN M.traffic_source='Microsoft' THEN M.traffic_source
            WHEN T.traffic_source='Taboola' THEN T.traffic_source
            WHEN G.traffic_source='Google' THEN G.traffic_source
        END) AS traffic_source1,
        SUM(CASE
            WHEN G.traffic_source='Google' THEN G.cost
            WHEN T.traffic_source='Taboola' THEN T.cost
            WHEN M.traffic_source='Microsoft' THEN M.cost
        END) AS cost,
        (CASE
            WHEN G.traffic_source='Google' THEN G.month
            WHEN T.traffic_source='Taboola' THEN T.month
            WHEN M.traffic_source='Microsoft' THEN M.month
        END) AS month1
FROM google_ads G
LEFT JOIN taboola T ON G.month = T.month
LEFT JOIN microsoft M ON G.month = M.month
GROUP BY traffic_source1, month1

这是我得到的结果的一个例子。month列只是用于测试目的。

| traffic_source1 |    cost    |      month1    |
|:----------------|:-----------|:---------------|
| Google          | 210.00     | 01/09/18 00:00 |
| Google          | 1,213.00   | 01/10/18 00:00 |
| Google          | 2,481.00   | 01/11/18 00:00 |
| Google          | 3,503.00   | 01/12/18 00:00 |
| Google          | 7,492.00   | 01/01/19 00:00 |
| Microsoft       | 22,059.00  | 01/02/19 00:00 |
| Microsoft       | 16,958.00  | 01/03/19 00:00 |
| Microsoft       | 7,582.00   | 01/04/19 00:00 |
| Microsoft       | 76,125.00  | 01/05/19 00:00 |
| Taboola         | 37,205.00  | 01/06/19 00:00 |
| Google          | 45,910.00  | 01/07/19 00:00 |
| Google          | 137,421.00 | 01/08/19 00:00 |
| Google          | 29,501.00  | 01/09/19 00:00 |

相反，它应该看起来像这样(例如，今年7月份)：

| traffic_source |    cost   |
|----------------|-----------|
| Google         | 53,901.00 |
| Microsoft      | 22,059.00 |
| Taboola        | 37,205.00 |

如有任何帮助，将不胜感激，谢谢！

Answer 1

您可以尝试这样做：

WITH google_ads AS
  ( SELECT 'Google' AS traffic_source,
           date_trunc('month', "day"::date) AS month,
           SUM(cost / 1000000) AS cost
   FROM googleads_campaign AS g
   GROUP BY month
   ORDER BY month DESC),

     taboola AS
  ( SELECT 'Taboola' AS traffic_source,
           date_trunc('month', "date"::date) AS month,
           SUM(spent) AS cost
   FROM taboola_campaign AS t
   GROUP BY month
   ORDER BY month DESC),

     microsoft AS
  ( SELECT 'Microsoft' AS traffic_source,
           date_trunc('month', "TimePeriod"::date) AS month,
           SUM("Spend") AS cost
   FROM microsoft_campaign AS m
   GROUP BY month
   ORDER BY month DESC)

SELECT (CASE
            WHEN M.traffic_source='Microsoft' THEN M.traffic_source
            WHEN T.traffic_source='Taboola' THEN T.traffic_source
            WHEN G.traffic_source='Google' THEN G.traffic_source
        END) AS traffic_source1,
        SUM(CASE
            WHEN G.traffic_source='Google' THEN G.cost
            WHEN T.traffic_source='Taboola' THEN T.cost
            WHEN M.traffic_source='Microsoft' THEN M.cost
        END) AS cost,
        (CASE
            WHEN G.traffic_source='Google' THEN G.month
            WHEN T.traffic_source='Taboola' THEN T.month
            WHEN M.traffic_source='Microsoft' THEN M.month
        END) AS month1      
FROM google_ads G
LEFT JOIN taboola T ON G.month = T.month
LEFT JOIN microsoft M ON G.month = M.month
GROUP BY traffic_source1, month1
HAVING EXTRACT(month from month1) = ... desired month (July is 7)

Answer 2

对每个广告源使用不同的表格的概念真的是一个非常糟糕的想法。它极大地增加了需要合并的查询的复杂性。将源与其他列放在一个表中会更好。想想当营销部门决定使用30-40家或更多的广告供应商时会发生什么。

如果您不能创建单个表，那么至少标准化列名和类型。还可以构建视图、物化视图或表函数(如下所示)，将所有流量源组合到单个源中。

create or  replace function consolidated_ad_traffic()
 returns table( traffic_source   text
              , ad_month         timestamp with time zone   
              , ad_cost          numeric(11,2)
              , ad_sales         numeric(11,2)
              , conversion_cost  numeric(11,6)
              )
 language sql 
 AS $$
 with ad_sources as
    ( select 'Google' as traffic_source
           , "date"   as ad_date
           , round(cast (cost AS numeric )  / 1000000.0,2)  as cost
           , sales
           , cost_per_conversion   
        from googleads_campaign 
     union all
     select 'Taboola'  
          , "date" 
          , spent  
          , sales
          , cost_per_conversion             
       from taboola_campaign  
     union all 
     select 'Microsoft' 
          , "TimePeriod"
          , "Spend"  
          , sales
          , cost_per_conversion 
      from microsoft_campaign 
    )
select * from ad_sources;
$$;

有了数据的合并视图，您现在可以编写普通的selects，就像拥有一个表一样。例如：

select * from consolidated_ad_traffic();

select distinct on( traffic_source, to_char(ad_month, 'mm'))
       traffic_source
     , to_char(ad_month, 'Mon') "For Month" 
     , to_char(sum(ad_cost) over(partition by traffic_source,  to_char(ad_month, 'Mon')), 'FM99,999,999,990.00') monthly_traffic_cost
     , to_char(sum(ad_cost) over(partition by traffic_source), 'FM99,999,999,990.00')   total_traffic_cost 
  from consolidated_ad_traffic();

select traffic_source, sum(ad_cost) ad_cost
  from consolidated_ad_traffic()
 group by traffic_source
 order by traffic_source;

 select traffic_source
      , to_char(ad_month, 'dd-Mon') "For Month"
      , sum(ad_cost) "Monthly Cost"
   from consolidated_ad_traffic()
  where date_trunc('month',ad_month) = date_trunc('month', date '2019-07-01')
    and traffic_source = 'Google'
  group by traffic_source, to_char(ad_month, 'dd-Mon') ;

现在，这不会做太多的更新，但将极大地简化选择。