gistfile1.sql

SELECT audit.start_migration_log('$Id: //src/trunk/sql/migration/2014/Henri/NG-8837/proc/36_summary_v2_get_audience_delivery_details_report.sql#1 $');
--
-- Audience Delivery Details Report
-- ============================================================================

/*
CREATE TYPE summary_v2.audience_delivery_details_report_trackingv2_v5 AS (

        -- key and rbatch will pull the label base on taxonomy_attribute_id
        taxonomy_attribute_id   bigint,

        -- dimenstions
        impressions             bigint,
        clicks                  bigint,
        click_actions           bigint,
        view_actions            bigint,
        uu_impressions          bigint,
        uu_clicks               bigint,
        uu_conversion           bigint,

        -- additional display only IDs
        audience_definition_id  bigint,

        site_activity           bigint,
        tpasns_id               bigint,
        campaign_external_id    bigint
);

ALTER TYPE summary_v2.audience_delivery_details_report_trackingv2_v5 OWNER TO summary_rw;
*/
-- ............................................................................

-- ============================================================================
--  common 
-- ============================================================================

CREATE TYPE summary_v2.audience_delivery_details_report_trackingv2_common_v5 AS (

        -- key and rbatch will pull the label base on taxonomy_attribute_id
        taxonomy_attribute_id   bigint,

        -- dimenstions
        impressions             bigint,
        clicks                  bigint,
        click_actions           bigint,
        view_actions            bigint,
        uu_impressions          bytea,
        uu_clicks               bytea,
        uu_conversion           bytea,

        -- additional display only IDs
        audience_definition_id  bigint,

        site_activity           bigint,
        tpasns_id               bigint,
        campaign_external_id    bigint
);

ALTER TYPE summary_v2.audience_delivery_details_report_trackingv2_common_v5 OWNER TO summary_rw;


CREATE OR REPLACE FUNCTION summary_v2.get_audience_delivery_details_report_trackingv2_common_v5 (campaign_id_in bigint,
                                                                                                 tpasns_id_in    bigint,
                                                                                                 from_date date,
                                                                                                 to_date date
                                                                                                )
RETURNS SETOF summary_v2.audience_delivery_details_report_trackingv2_common_v5 AS
$BODY$

DECLARE
    function_name           varchar(100);
    run_id                  bigint;
    sql_ins                 varchar;
    debug                   boolean = false;
    return_row              summary_v2.audience_delivery_details_report_trackingv2_common_v5;
    sql_conversion_codes    varchar;
BEGIN
    -- load debug status
    -----------------------------------------------------------------------------------------------------------
    select COALESCE((select rez.debug from summary_v2.function_mode AS rez), false) into debug;

    -- function name and run id for debugging.
    -----------------------------------------------------------------------------------------------------------
    function_name:= summary_v2.get_main_function_name();
    SELECT NEXTVAL('summary_v2.report_debug_run_id_seq') INTO run_id;

    -- set sort_mem FOR session.
    PERFORM summary_v2.set_sort_mem();

    IF debug THEN
        PERFORM summary_v2.debug_loger(run_id, function_name, 'Started: campaign_id = ' || campaign_id_in || ',tpasns_id_in = ' || tpasns_id_in || ', start_date = ' || from_date || ', end_date = ' || to_date);
    END IF;

    IF (campaign_id_in IS NULL OR campaign_id_in= -1) THEN
      IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'Empty input parameter'); END IF;
         RETURN;
    END IF;

    --get condition by conversion_types
    select summary_v2.get_conversion_types_condition(tpasns_id_in, campaign_id_in) into sql_conversion_codes;

    -- create select statement to execute
    -- NOTE: timestamps are not used in conditions. summary tables have data with date not timestamp
    -----------------------------------------------------------------------------------------------------------
    sql_ins := '
        SELECT
            taxonomy_attribute_id,
            COALESCE(impressions, 0),
            COALESCE(clicks, 0),
            COALESCE(click_actions, 0),
            COALESCE(view_actions, 0),
            uu_impressions,
            uu_clicks,
            uu_conversion,
            audience_definition_id,
            COALESCE(site_activity, 0),
            '||tpasns_id_in||',
            '||campaign_id_in||'
        FROM (
        SELECT
            r.taxonomy_attribute_id,
            r.audience_definition_id,
            SUM(r.impressions_total)::bigint AS impressions,
            SUM(r.clicks)::bigint AS clicks,
            SUM((r.adv_engagement_attributed_conversions_by_type_data::summary_v2.actions_data).actions)::bigint AS click_actions,
            SUM((r.adv_impression_attributed_conversions_by_type_data::summary_v2.actions_data).actions)::bigint AS view_actions,
            summary_v2.akcms_union_agg(r.uu_cms) AS uu_impressions,
            summary_v2.akcms_union_agg(r.uu_clicks_raw) AS uu_clicks,
            summary_v2.akcms_union_agg(summary.akcms_union((r.adv_impression_attributed_conversions_by_type_data::summary_v2.actions_data).uu_actions_raw,
                                                           (r.adv_engagement_attributed_conversions_by_type_data::summary_v2.actions_data).uu_actions_raw)) AS uu_conversion,
            SUM(r.site_activity) AS site_activity
        FROM (
            SELECT
                taxonomy_attribute_id,
                audience_definition_id,
                impressions_total,
                click_engagement_count AS clicks,
                uu_impression_total AS uu_cms,
                ak_uu_click_engagement_count AS uu_clicks_raw,
                (SELECT row(action_uu_count, action_count, 0)::summary_v2.actions_data FROM
                   (SELECT COALESCE(summary_v2.akcms_union_agg((item::summary_v2.conversion_type_detail).action_uu_count),summary_v2.get_uu_default()) as action_uu_count,
                           COALESCE(SUM((item::summary_v2.conversion_type_detail).action_count),0) AS action_count
                          FROM
                          (SELECT item FROM
                             (SELECT unnest(adv_impression_attributed_conversions_by_type) AS item) AS items  '||sql_conversion_codes||'
                          )as items_de_duplicated WHERE (item::summary_v2.conversion_type_detail).duplicate = false
                    ) as j_raw) AS adv_impression_attributed_conversions_by_type_data,
                (SELECT row(action_uu_count, action_count, 0)::summary_v2.actions_data FROM
                   (SELECT COALESCE(summary_v2.akcms_union_agg((item::summary_v2.conversion_type_detail).action_uu_count),summary_v2.get_uu_default()) as action_uu_count,
                           COALESCE(SUM((item::summary_v2.conversion_type_detail).action_count),0) AS action_count
                          FROM
                          (SELECT item FROM
                             (SELECT unnest(adv_engagement_attributed_conversions_by_type) AS item) AS items  '||sql_conversion_codes||'
                          )as items_de_duplicated WHERE (item::summary_v2.conversion_type_detail).duplicate = false
                    ) as j_raw) AS adv_engagement_attributed_conversions_by_type_data,
                adv_impression_attributed_conversions+adv_click_attributed_conversions AS site_activity
            FROM summary_v2.audience_taxonomy_attribute_details_daily_v2_view
            WHERE report_date BETWEEN '''||from_date||''' AND '''||to_date||''' AND campaign_id = '||campaign_id_in||' AND third_party_adserver_id ='||tpasns_id_in||'
          ) r
        GROUP BY 1, 2
        ) as gr
    ';

    -- return results from temp table
    -----------------------------------------------------------------------------------------------------------
    IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'RETURN QUERY'); END IF;
    FOR return_row IN EXECUTE sql_ins
    LOOP
        RETURN NEXT return_row;
    END LOOP;

    IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'Finished'); END IF;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

GRANT EXECUTE ON FUNCTION summary_v2.get_audience_delivery_details_report_trackingv2_common_v5 (bigint,bigint, date, date) TO summary_r;
ALTER FUNCTION summary_v2.get_audience_delivery_details_report_trackingv2_common_v5 (bigint,bigint, date, date) OWNER TO summary_rw;

/*
select * from summary_v2.get_audience_delivery_details_report_trackingv2_common_v5(284300, 22, '2014-05-20', '2014-05-20') order by 1
*/

-- ============================================================================
--  Campaign 
-- ============================================================================

CREATE OR REPLACE FUNCTION summary_v2.get_audience_delivery_details_report_trackingv2_v5 (campaign_id_in bigint,
                                                                                          tpasns_id_in    bigint,
                                                                                          from_date date,
                                                                                          to_date date
                                                                                         )
RETURNS SETOF summary_v2.audience_delivery_details_report_trackingv2_v5 AS
$BODY$

DECLARE
    function_name           varchar(100);
    run_id                  bigint;
    sql_ins                 varchar;
    debug                   boolean = false;
    return_row              summary_v2.audience_delivery_details_report_trackingv2_v5;
    sql_conversion_codes    varchar;
BEGIN
    -- load debug status
    -----------------------------------------------------------------------------------------------------------
    select COALESCE((select rez.debug from summary_v2.function_mode AS rez), false) into debug;

    -- function name and run id for debugging.
    -----------------------------------------------------------------------------------------------------------
    function_name:= summary_v2.get_main_function_name();
    SELECT NEXTVAL('summary_v2.report_debug_run_id_seq') INTO run_id;

    IF debug THEN
        PERFORM summary_v2.debug_loger(run_id, function_name, 'Started: campaign_id = ' || campaign_id_in || ',tpasns_id_in = ' || tpasns_id_in || ', start_date = ' || from_date || ', end_date = ' || to_date);
    END IF;

    RETURN QUERY
       SELECT 
           taxonomy_attribute_id,
           impressions,
           clicks,
           click_actions,
           view_actions,
           summary_v2.adjust_uu_for_error(summary_v2.akcms_cardinality(uu_impressions)::bigint,impressions) AS uu_impressions,
           summary_v2.adjust_uu_for_error(summary_v2.akcms_cardinality(uu_clicks)::bigint,clicks) AS uu_clicks,
           summary_v2.adjust_uu_for_error(summary_v2.akcms_cardinality(uu_conversion)::bigint,click_actions+view_actions) AS uu_conversion,
           audience_definition_id,
           site_activity,
           tpasns_id,
           campaign_external_id
        FROM summary_v2.get_audience_delivery_details_report_trackingv2_common_v5(campaign_id_in, tpasns_id_in, from_date, to_date);

    IF debug THEN PERFORM summary_v2.debug_loger(run_id, function_name, 'Finished'); END IF;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

GRANT EXECUTE ON FUNCTION summary_v2.get_audience_delivery_details_report_trackingv2_v5 (bigint,bigint, date, date) TO summary_r;
ALTER FUNCTION summary_v2.get_audience_delivery_details_report_trackingv2_v5 (bigint,bigint, date, date) OWNER TO summary_rw;

/*
select * from summary_v2.get_audience_delivery_details_report_trackingv2_v5(284300, 22, '2014-05-20', '2014-05-20') order by 1
*/


-- ============================================================================
--  Campaign Groups
-- ============================================================================

CREATE OR REPLACE FUNCTION summary_v2.get_audience_delivery_details_groups_report_trackingv2_v5(group_id_in bigint, from_date date, to_date date)
  RETURNS SETOF summary_v2.audience_delivery_details_report_trackingv2_v5 AS
$BODY$
DECLARE
   return_row    record;
BEGIN

   DROP TABLE IF EXISTS tmp_audience_delivery_details_report_trackingv2;
   CREATE temporary TABLE tmp_audience_delivery_details_report_trackingv2 OF summary_v2.audience_delivery_details_report_trackingv2_common_v5 ON COMMIT DROP;

   FOR return_row IN
      SELECT
          tc.external_id,
          pr.tpasns_id
       FROM ref_data.tracking_campaign AS tc
       JOIN ref_data.campaign AS c ON c.id = tc.campaign_id
       JOIN ref_data.provider AS pr ON pr.id=tc.tracking_data_provider_id
       JOIN ui.campaign_group_map AS cg ON cg.campaign_id=tc.campaign_id
       WHERE c.active='Y' AND summary_v2.empty_to_null(tc.external_id) IS NOT null AND (tc.external_id ~ E'^\\d+$') AND pr.tpasns_id IS NOT NULL
       AND cg.campaign_group_id=group_id_in
   LOOP
      INSERT INTO tmp_audience_delivery_details_report_trackingv2
         SELECT 
           taxonomy_attribute_id,
           impressions,
           clicks,
           click_actions,
           view_actions,
           uu_impressions,
           uu_clicks,
           uu_conversion,
           NULL AS audience_definition_id,
           site_activity,
           NULL AS tpasns_id,
           NULL AS campaign_external_id
          FROM summary_v2.get_audience_delivery_details_report_trackingv2_common_v5(return_row.external_id::bigint, return_row.tpasns_id, from_date, to_date);
   END LOOP;

   RETURN QUERY
      SELECT 
           taxonomy_attribute_id,
           SUM(impressions) AS impressions,
           SUM(clicks) AS clicks,
           SUM(click_actions) AS click_actions,
           SUM(view_actions) AS view_actions,
           summary_v2.adjust_uu_for_error(summary_v2.akcms_uu_agg(uu_impressions)::bigint,impressions) AS uu_impressions,
           summary_v2.adjust_uu_for_error(summary_v2.akcms_uu_agg(uu_clicks)::bigint,clicks) AS uu_clicks,
           summary_v2.adjust_uu_for_error(summary_v2.akcms_uu_agg(uu_conversion)::bigint,click_actions+view_actions) AS uu_conversion,
           audience_definition_id,
           SUM(site_activity) AS site_activity,
           tpasns_id,
           campaign_external_id      
       FROM tmp_audience_delivery_details_report_trackingv2 
       GROUP BY tpasns_id, campaign_external_id, audience_definition_id, taxonomy_attribute_id
       order by tpasns_id, campaign_external_id, audience_definition_id, taxonomy_attribute_id;
END
$BODY$
LANGUAGE 'plpgsql';

-- ****************************************************************************

SELECT audit.end_migration_log('$Id: //src/trunk/sql/migration/2014/Henri/NG-8837/proc/36_summary_v2_get_audience_delivery_details_report.sql#1 $');

/*

for testing

fill group for testing
insert into ui.campaign_group (id,name, provider_id) values (7,'AlexO test group',7);
insert into ui.campaign_group_map (campaign_group_id, campaign_id) VALUES (7,8041);
insert into ui.campaign_group_map (campaign_group_id, campaign_id) VALUES (7,8545);
insert into ui.campaign_group_map (campaign_group_id, campaign_id) VALUES (7,8484);

run
select * from summary_v2.get_audience_delivery_details_groups_report_trackingv2_v5(7, '2013-10-01', '2013-10-01')

*/