Тест оптимизатора (редиректы по весам)

[vladignatyev]

Произвести тест на реальном или эмулированном трафике, который убедит нас в том, что по прошествии заданного в настройках кампании числа хитов, бОльшую часть трафика получает оффер, собирающий больше всего ревенью по конверсиям

[vladignatyev]

Как трафик одной из крупнейших площадок (dim_zone='762488') в проливе распределялся между разными всеми оферами во времени

select hour, 
hits_1 / total_hits,
hits_2 / total_hits, 
hits_3 / total_hits,
hits_4 / total_hits, 
hits_5 / total_hits,
hits_6 / total_hits,
hits_7 / total_hits,
hits_8 / total_hits,
hits_9 / total_hits,
hits_10 / total_hits,
hits_11 / total_hits,
hits_12 / total_hits
from 
(
    select* from (select* from (select* from (select* from (select* from (select* from (select* from (select* from (select * from (select * from (select * from (
        select * from (select hour, count(hit.time) as total_hits from majorka.hits as hit
        where hit.dim_zone = '762488' and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
        group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
        order by hour asc)
        join 
        (
            select hour, count(hit.time) as hits_1 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 0 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
    )
    left join 
        (
            select hour, count(hit.time) as hits_2 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 1 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
    )

    left join 
        (
            select hour, count(hit.time) as hits_3 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 2 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
    )
    left join 
        (
            select hour, count(hit.time) as hits_4 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 3 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
    )
    left join 
        (
            select hour, count(hit.time) as hits_5 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 4 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
     )
    left join 
        (
            select hour, count(hit.time) as hits_6 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 5 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
     )
    left join 
        (
            select hour, count(hit.time) as hits_7 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 6 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
     )
    left join 
        (
            select hour, count(hit.time) as hits_8 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 7 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
     )
    left join 
        (
            select hour, count(hit.time) as hits_9 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 8 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
     )
    left join 
        (
            select hour, count(hit.time) as hits_10 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 9 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
     )
    left join 
        (
            select hour, count(hit.time) as hits_11 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 10 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour
     )
    left join 
        (
            select hour, count(hit.time) as hits_12 from majorka.hits as hit
            where hit.dim_zone = '762488' and hit.destination = 11 and hit.time > toDateTime('2019-02-18 00:00:00') and hit.time < toDateTime('2019-02-21 00:00:00')
            group by concat(toString(toDate(hit.time)), ' ', toString(toHour(hit.time)), ':00:00') as hour
            order by hour asc
        )
        using hour

)

[vladignatyev]

[vladignatyev]

Что-то я просмотрел руками несколько зон и поведение оптимизатора по ним. Странным выглядит то, что первый оффер в группе всегда собирает больше траффика. Это справедливо для всех зон на которых было относительно много трафика.

Мне нужна помощь с SQL. Мне нужен запрос, который посчитает для выбранной зоны, RPM по каждому из офферов. Грубо говоря должен вернуть таблицу в которой напротив каждого оффера стоит RPM трафика по этому оферу.

[varuzhnikov]

Пишем запрос, который на выбранном интервале строит rpm для каждого оффера.