Upgrade to Pro — share decks privately, control downloads, hide ads and more …

フリルのCMを支える分析 (メディアバイイング編)

shoby
December 19, 2017

フリルのCMを支える分析 (メディアバイイング編)

「テレビCMプロモーション 改善事例共有会」の発表資料です。
https://fablic.connpass.com/event/71186/

shoby

December 19, 2017
Tweet

More Decks by shoby

Other Decks in Technology

Transcript

  1. ϑϦϧͷ$.Λࢧ͑Δ෼ੳ
    ʢϝσΟΞόΠΠϯάฤʣ

    View Slide

  2. ࣗݾ঺հ
    2

    View Slide

  3. 3
    ࣗݾ঺հ
    shoby @shobyshoby
    w 4IPUB*[BLB
    w 'BCMJD JOD
    w $.෼ੳJ04

    View Slide

  4. ֓ཁ
    4

    View Slide

  5. 5
    ֓ཁ
    w ελʔτΞοϓʹͱͬͯͷ$.
    w վળͷͨΊͷԾઆݕূ
    w ϝσΟΞόΠΠϯάͷվળ
    w ༵೔ɾ࣌ؒͷ࠷దԽ
    w ౎ಓ෎ݝͷ࠷దԽ

    View Slide

  6. ελʔτΞοϓʹͱͬͯͷ$.
    6

    View Slide

  7. 7
    ελʔτΞοϓʹͱͬͯͷ$.
    w αʔϏεΛٸ੒௕ͤ͞ΔͨΊͷେ͖ͳ౤ࢿ
    w $.ͷ੒ޭࣦഊͰαʔϏεͷߦํ͕ܾ·Δ
    w ݶΒΕͨඅ༻Ͱ࠷େݶϢʔβʔΛ֫ಘ͢Δ

    View Slide

  8. 8
    ελʔτΞοϓʹͱͬͯͷ$.
    w த௕ظతͳ੒௕ΛૂͬͨϓϩϞʔγϣϯ
    w தظɿϢʔβʔେྔ֫ಘʹΑΔαʔϏε׆ੑԽ
    w ௕ظɿαʔϏεೝ஌཰ͷ޲্
    w $1*͚ͩߟ͑ΔͱΦϯϥΠϯ޿ࠂͷํ͕͍҆

    View Slide

  9. վળͷͨΊͷԾઆݕূ
    9

    View Slide

  10. 10
    վળͷͨΊͷԾઆݕূ
    w ߏஙɺܭଌɺֶशͷαΠΫϧΛճ͢
    w ߏஙɿ޿ࠂ಺༰ɺ౤Լεϙοτ
    w ܭଌɿ%-਺ɺ$1*ɺαʔϏεೝ஌཰
    w ֶशɿ਺஋มԽͷཁҼߟ࡯

    View Slide

  11. 11
    վળͷͨΊͷԾઆݕূ

    View Slide

  12. ϝσΟΞόΠΠϯάͷվળ
    12

    View Slide

  13. 13
    ϝσΟΞόΠΠϯάͷվળ
    w අ༻ରޮՌͷߴ͍εϙοτͷ౤ԼྔΛ૿΍͢
    w ༵೔ɾ࣌ؒͷ࠷దԽ
    w ౎ಓ෎ݝͷ࠷దԽ

    View Slide

  14. 14
    ϝσΟΞόΠΠϯάͷվળ
    ༵೔ɾ࣌ؒ ౎ಓ෎ݝ

    View Slide

  15. ༵೔ɾ࣌ؒͷ࠷దԽ
    15

    View Slide

  16. 16
    ༵೔ɾ࣌ؒͷ࠷దԽ
    w શ೔Ͱങ͍ɺαʔϏεͷϐʔΫλΠϜʹدͤΔ
    w ίͷࣈ΍ٯ-Ͱ͸୯Ձ͕໿ʙഒʹͳΔͨΊɺ
    $1*͕ѱԽ͢Δ
    w ʮϚεϓϩϞʔγϣϯʯͱʮಛఆλʔήοτʯ
    ͸૬ੑ͕ѱ͍ҹ৅
    w λʔήςΟϯά͸ΦϯϥΠϯ޿ࠂͰ΍Δ

    View Slide

  17. 17
    ༵೔ɾ࣌ؒͷ࠷దԽ
    w طଘϢʔβʔͷϐʔΫλΠϜΛௐ΂Δ
    w ϐʔΫλΠϜɿλʔήοτ૚ͷ༨Ջ࣌ؒ
    w $.͕ࢹௌ͞Ε΍͍͢ˍ%-ʹ݁ͼ͖ͭ΍͍͢
    w ೥ྸɾੑผ৘ใ΋׆༻ͯ͠෼ੳ

    View Slide

  18. ౎ಓ෎ݝͷ࠷దԽ
    18

    View Slide

  19. 19
    ౎ಓ෎ݝͷ࠷దԽ
    w $1*ͱਓޱΛߟྀͯ͠౤ԼྔΛௐ੔͢Δ
    w αʔϏεͱͷ૬ੑ͕ྑ͚Ε͹$1*΋ྑ͍
    w ਓޱʹରͯ͠౤Լྔ͕ଟ͗͢Δͱ$1*͕ѱԽ͢Δ

    View Slide

  20. 20
    ౎ಓ෎ݝͷ࠷దԽ
    w ౎ಓ෎ݝผ$1*ͷܭଌํ๏
    w ౎ಓ෎ݝ͝ͱͷΠϯετʔϧ਺Λऔಘ
    w ϕʔεϥΠϯ͔Βͷ૿෼Λग़͢
    w $1*Λग़ͯ͠૬ରൺֱ

    View Slide

  21. 21
    ౎ಓ෎ݝͷ࠷దԽ
    w ౎ಓ෎ݝผͷΠϯετʔϧ਺Λऔಘ
    w 'JSFCBTFͱ#JH2VFSZΛ༻͍Δ
    w શϢʔβʔͷੜσʔλΛूܭՄೳ
    w ౎ಓ෎ݝ৘ใ͸ࣗಈܭଌͯ͘͠ΕΔ

    View Slide

  22. 22
    ౎ಓ෎ݝͷ࠷దԽ
    SELECT
    STRFTIME_UTC_USEC(DATE_ADD(event_dim.timestamp_micros, 9, 'HOUR'),
    '%Y-%m-%d') AS install_date,
    user_dim.geo_info.region AS region,
    EXACT_COUNT_DISTINCT(user_dim.device_info.resettable_device_id) as
    install_count
    FROM
    your_table_name
    WHERE
    user_dim.geo_info.country = 'Japan'
    AND event_dim.name = 'first_open'
    GROUP BY install_date, region
    ORDER BY install_date ASC, install_count DESC
    ౎ಓ෎ݝผͷ%BJMZΠϯετʔϧ਺Λऔಘ

    View Slide

  23. 23
    ౎ಓ෎ݝͷ࠷దԽ
    ౎ಓ෎ݝผͷ%BJMZΠϯετʔϧ਺Λऔಘ

    View Slide

  24. 24
    ౎ಓ෎ݝͷ࠷దԽ
    w ϕʔεϥΠϯ͔Βͷ૿෼Λऔಘ
    w $.Λߦͳ͍ͬͯͳ͍࣌ظͷฏۉ஋ͱൺֱ
    IUUQXXXJOUFSOFUSJYDPNBVCMPHNFBTVSJOHUIFJNQBDUPGUWBEWFSUJTFNFOUPOXFCUSBGGJDΑΓҾ༻

    View Slide

  25. 25
    ౎ಓ෎ݝͷ࠷దԽ
    w $1*Λग़ͯ͠૬ରൺֱ
    w ౎ಓ෎ݝผʹਪఆ$1*Λग़͢
    w ྨࣅͨ͠౎ಓ෎ݝͱ૬ରൺֱ
    w ౤Լྔ͕ద੾͔ͩͬͨ൑அ

    View Slide

  26. ·ͱΊ
    26

    View Slide

  27. 27
    ·ͱΊ
    w ελʔτΞοϓʹͱͬͯ͸$.͸େ͖ͳ౤ࢿ
    w த௕ظతͳࢹ఺ͰɺόΠΠϯάͷվળΛߦ͏
    w ༵೔ɾ࣌ؒͱ౎ಓ෎ݝͰ౤ԼྔΛௐ੔͢Δ
    w ༵೔ɾ࣌ؒɿαʔϏεͷϐʔΫλΠϜʹدͤΔ
    w ౎ಓ෎ݝɿ$1*ͱਓޱΛߟྀͯ͠౤ԼྔΛௐ੔

    View Slide