Skip to content

Instantly share code, notes, and snippets.

@rosdyana

rosdyana/gist:74ed857c29c80712c1ad274a8d15e6a5

Created January 14, 2020 03:30
Show Gist options
  • Save rosdyana/74ed857c29c80712c1ad274a8d15e6a5 to your computer and use it in GitHub Desktop.
Save rosdyana/74ed857c29c80712c1ad274a8d15e6a5 to your computer and use it in GitHub Desktop.
Download ZIP
ruby_report_0109.py
Raw
gistfile1.txt
from omnidata.models import *
from ma_audience.models import *
from django.db.models import *
from omnidata.models import Product as d_Product, Organization as d_Organization, Conversion, PageView
import collections
from datetime import timedelta
org = Organization.objects.get(id=1) # Ruby
d_org = d_Organization.objects.get(id=1)
all_audience = Audience.objects.filter(organization=org)
all_audience.count() # Out[136]: 134436
tag = AudienceTag.objects.get(name="0103_Send_Line")
tag_n = AudienceTag.objects.get(name="NASLD/Group_New")
tag_a = AudienceTag.objects.get(name="NASLD/Group_Active")
tag_s = AudienceTag.objects.get(name="NASLD/Group_Sleep")
tag_l = AudienceTag.objects.get(name="NASLD/Group_Lost")
tag_d = AudienceTag.objects.get(name="NASLD/Group_DDD")
tag_o_aud = Audience.objects.filter(id__in=tag.current_audiences.difference(
tag_n.current_audiences.all()).difference(
tag_a.current_audiences.all()).difference(
tag_s.current_audiences.all()).difference(
tag_l.current_audiences.all()).difference(
tag_d.current_audiences.all()).values('id'))
tag_o_aud.current_audiences = tag_o_aud.all()
tag_0103_n = AudienceTag.objects.get(name="0103_New")
tag_0103_a = AudienceTag.objects.get(name="0103_Active")
tag_0103_s = AudienceTag.objects.get(name="0103_Sleep")
tag_0103_l = AudienceTag.objects.get(name="0103_Lost")
tag_0103_d = AudienceTag.objects.get(name="0103_Dead")
tag_0103_o_aud = Audience.objects.filter(id__in=tag.current_audiences.difference(
tag_0103_n.current_audiences.all()).difference(
tag_0103_a.current_audiences.all()).difference(
tag_0103_s.current_audiences.all()).difference(
tag_0103_l.current_audiences.all()).difference(
tag_0103_d.current_audiences.all()).values('id'))
tag_0103_o_aud.current_audiences = tag_0103_o_aud.all()
tag_0109_n = AudienceTag.objects.get(name="0109_New")
tag_0109_a = AudienceTag.objects.get(name="0109_Active")
tag_0109_s = AudienceTag.objects.get(name="0109_Sleep")
tag_0109_l = AudienceTag.objects.get(name="0109_Lost")
tag_0109_d = AudienceTag.objects.get(name="0109_Dead")
tag_0109_o_aud = Audience.objects.filter(id__in=tag.current_audiences.difference(
tag_0109_n.current_audiences.all()).difference(
tag_0109_a.current_audiences.all()).difference(
tag_0109_s.current_audiences.all()).difference(
tag_0109_l.current_audiences.all()).difference(
tag_0109_d.current_audiences.all()).values('id'))
tag_0109_o_aud.current_audiences = tag_0109_o_aud.all()
tag_0109_o_aud.current_audiences.count()
tag_lv_n = Audience.objects.filter(member_level='新進會員')
tag_lv_s = Audience.objects.filter(member_level='專屬會員')
tag_lv_g = Audience.objects.filter(member_level='夢幻女神')
tag_lv_a = Audience.objects.filter(member_level='甜心天使')
tag_lv_n.current_audiences = tag_lv_n.all()
tag_lv_s.current_audiences = tag_lv_s.all()
tag_lv_g.current_audiences = tag_lv_g.all()
tag_lv_a.current_audiences = tag_lv_a.all()
all_tags = [tag_n, tag_a, tag_s, tag_l, tag_d, tag_o_aud]
all_0109_tags = [tag_0109_n, tag_0109_a, tag_0109_s, tag_0109_l, tag_0109_d, tag_0109_o_aud]
all_0103_tags = [tag_0103_n, tag_0103_a, tag_0103_s, tag_0103_l, tag_0103_d, tag_0103_o_aud]
# all_tags = [tag_o_aud]
# all_tags = [tag_lv_n, tag_lv_s, tag_lv_g, tag_lv_a]
send_audience = tag.current_audiences.all()
send_audience.count() #27033
tag_n.current_audiences.count() # 5570
tag_a.current_audiences.count() # 2485
tag_s.current_audiences.count() # 963
tag_l.current_audiences.count() # 2875
tag_d.current_audiences.count() # 13888
tag_0109_n.current_audiences.count() # 6229
tag_0109_a.current_audiences.count() # 2675
tag_0109_s.current_audiences.count() # 731
tag_0109_l.current_audiences.count() # 3207
tag_0109_d.current_audiences.count() # 14606
tag_0103_n.current_audiences.count() # 6266
tag_0103_a.current_audiences.count() # 2649
tag_0103_s.current_audiences.count() # 667
tag_0103_l.current_audiences.count() # 3032
tag_0103_d.current_audiences.count() # 14828
# 各group人數
[t.current_audiences.count() for t in all_tags] #
[t.current_audiences.count() for t in all_0109_tags] # [6229, 2675, 731, 3207, 14606, 16583]
[t.current_audiences.count() for t in all_0103_tags] # [6266, 2649, 667, 3032, 14828, 16584]
# 各group有LineID人數
[t.current_audiences.filter(line_id__isnull=False).exclude(line_id="").count() for t in all_tags] #
[t.current_audiences.filter(line_id__isnull=False).exclude(line_id="").count() for t in all_0109_tags] # [2006, 1210, 316, 1066, 5854, 16583]
[t.current_audiences.filter(line_id__isnull=False).exclude(line_id="").count() for t in all_0103_tags] # [2062, 1196, 288, 1013, 5891, 16584]
# 各group發送人數
[t.current_audiences.filter(line_id__isnull=False, id__in=send_audience).exclude(line_id="").distinct().count() for t in all_tags] #
[t.current_audiences.filter(line_id__isnull=False, id__in=send_audience).exclude(line_id="").distinct().count() for t in all_0109_tags] # [2006, 1210, 316, 1066, 5853, 16583]
[t.current_audiences.filter(line_id__isnull=False, id__in=send_audience).exclude(line_id="").distinct().count() for t in all_0103_tags] # [2062, 1196, 288, 1013, 5890, 16584]
# 各group有PageView在1/2以後含line的人數
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="line",
user__pageview__datetime__date__gte="2020-1-3")
.exclude(line_id="").distinct().count() for t in all_tags] # [391, 526, 39, 59, 0, 274]
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="line",
user__pageview__datetime__date__gte="2020-1-3",
user__pageview__datetime__date__lte="2020-1-6")
.exclude(line_id="").distinct().count() for t in all_0103_tags] # [281, 356, 29, 41, 220, 0]
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="line",
user__pageview__datetime__date__gte="2020-1-9",
user__pageview__datetime__date__lte="2020-1-13")
.exclude(line_id="").distinct().count() for t in all_0109_tags] # [207, 356, 25, 36, 155, 0]
# 有line PV的conversion次數
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="line",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-3",
user__ma_audience__user__pageview__datetime__date__lte=F('datetime'),
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3").distinct().count() for t in all_tags] #
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="line",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-3",
user__ma_audience__user__pageview__datetime__date__lte="2020-1-6",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3", datetime__date__lte="2020-1-6").distinct().count() for t in all_0103_tags] # [24, 80, 3, 6, 34, 0]
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="line",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-9",
user__ma_audience__user__pageview__datetime__date__lte="2020-1-13",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-9", datetime__date__lte="2020-1-13").distinct().count() for t in all_0109_tags] # [8, 40, 1, 3, 13, 0]
# 有line PV的conversion金額
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="line",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-3",
user__ma_audience__user__pageview__datetime__date__lte=F('datetime'),
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3").distinct().aggregate(Sum('transaction_revenue')) for t in all_tags] #
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="line",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-3",
user__ma_audience__user__pageview__datetime__date__lte="2020-1-6",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3", datetime__date__lte="2020-1-6").distinct().aggregate(Sum('transaction_revenue')) for t in all_0103_tags] #
# [{'transaction_revenue__sum': Decimal('35429.00')},
# {'transaction_revenue__sum': Decimal('114185.00')},
# {'transaction_revenue__sum': Decimal('5800.00')},
# {'transaction_revenue__sum': Decimal('7769.00')},
# {'transaction_revenue__sum': Decimal('36791.00')},
# {'transaction_revenue__sum': None}]
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="line",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-9",
user__ma_audience__user__pageview__datetime__date__lte="2020-1-13",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-9", datetime__date__lte="2020-1-13").distinct().aggregate(Sum('transaction_revenue')) for t in all_0109_tags] #
# [{'transaction_revenue__sum': Decimal('16197.00')},
# {'transaction_revenue__sum': Decimal('100045.00')},
# {'transaction_revenue__sum': Decimal('4589.00')},
# {'transaction_revenue__sum': Decimal('3809.00')},
# {'transaction_revenue__sum': Decimal('29594.00')},
# {'transaction_revenue__sum': None}]
# 各group有PageView在1/2以後含bcpush的人數
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="bcpush",
user__pageview__datetime__date__gte="2020-1-3")
.exclude(line_id="").distinct().count() for t in all_tags] #
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="bcpush",
user__pageview__datetime__date__gte="2020-1-3",
user__pageview__datetime__date__lte="2020-1-6")
.exclude(line_id="").distinct().count() for t in all_0103_tags] # [114, 135, 11, 16, 80, 0]
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="bcpush",
user__pageview__datetime__date__gte="2020-1-9",
user__pageview__datetime__date__lte="2020-1-13")
.exclude(line_id="").distinct().count() for t in all_0109_tags] # [88, 145, 8, 17, 62, 0]
# 有bcpush PV的conversion次數
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="bcpush",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-3",
user__ma_audience__user__pageview__datetime__date__lte=F('datetime'),
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3").distinct().count() for t in all_tags] # [212, 273, 30, 32, 176]
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="bcpush",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-3",
user__ma_audience__user__pageview__datetime__date__lte="2020-1-6",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3", datetime__date__lte="2020-1-6").distinct().count() for t in all_0103_tags] # [5, 18, 0, 2, 10, 0]
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="bcpush",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-9",
user__ma_audience__user__pageview__datetime__date__lte="2020-1-13",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-9", datetime__date__lte="2020-1-13").distinct().count() for t in all_0109_tags] # [4, 20, 0, 2, 3, 0]
# 有bcpush PV的conversion金額
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="bcpush",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-3",
user__ma_audience__user__pageview__datetime__date__lte=F('datetime'),
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3").distinct().aggregate(Sum('transaction_revenue')) for t in all_tags] # [212, 273, 30, 32, 176]
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="bcpush",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-3",
user__ma_audience__user__pageview__datetime__date__lte="2020-1-6",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3", datetime__date__lte="2020-1-6").distinct().aggregate(Sum('transaction_revenue')) for t in all_0103_tags]
# [{'transaction_revenue__sum': Decimal('6532.00')},
# {'transaction_revenue__sum': Decimal('24490.00')},
# {'transaction_revenue__sum': None},
# {'transaction_revenue__sum': Decimal('926.00')},
# {'transaction_revenue__sum': Decimal('10314.00')},
# {'transaction_revenue__sum': None}]
[Conversion.objects.using('readonly').filter(
user__ma_audience__user__pageview__url__icontains="bcpush",
user__ma_audience__user__pageview__datetime__date__gte="2020-1-9",
user__ma_audience__user__pageview__datetime__date__lte="2020-1-13",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-9", datetime__date__lte="2020-1-13").distinct().aggregate(Sum('transaction_revenue')) for t in all_0109_tags]
# [{'transaction_revenue__sum': Decimal('10545.00')},
# {'transaction_revenue__sum': Decimal('58618.00')},
# {'transaction_revenue__sum': None},
# {'transaction_revenue__sum': Decimal('2433.00')},
# {'transaction_revenue__sum': Decimal('6188.00')},
# {'transaction_revenue__sum': None}]
# 各group有PageView在1/2以後含omnicha的人數
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__iregex="20200103_bcpush_.*_omnicha.*",
user__pageview__datetime__date__gte="2020-1-3")
.exclude(line_id="").distinct().count() for t in all_tags] # [93, 116, 12, 13, 76]
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__iregex="20200103_bcpush_.*_omnicha.*",
user__pageview__datetime__date__gte="2020-1-3",
user__pageview__datetime__date__lte="2020-1-6")
.exclude(line_id="").distinct().count() for t in all_0103_tags] # [111, 132, 11, 15, 75, 0]
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__iregex="20200109_bcpush_.*_omnicha.*",
user__pageview__datetime__date__gte="2020-1-9",
user__pageview__datetime__date__lte="2020-1-13")
.exclude(line_id="").distinct().count() for t in all_0109_tags] # [65, 105, 6, 13, 47, 0]
# 有omnicha PV的conversion次數
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-3") &
Q(user__ma_audience__user__pageview__datetime__date__lte=F('datetime'))&
Q(user__ma_audience__user__pageview__datetime__date__gte=F('datetime') - timedelta(days=2)),
user__ma_audience__user__pageview__url__iregex="20200103_bcpush_.*_omnicha.*",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3").distinct().count() for t in all_tags] # [212, 273, 30, 32, 176]
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-3") &
Q(user__ma_audience__user__pageview__datetime__date__lte="2020-1-6")&
Q(user__ma_audience__user__pageview__datetime__date__gte=F('datetime') - timedelta(days=2)),
user__ma_audience__user__pageview__url__iregex="20200103_bcpush_.*_omnicha.*",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3", datetime__date__lte="2020-1-6").distinct().count() for t in all_0103_tags] # [3, 12, 0, 1, 7, 0]
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-9") &
Q(user__ma_audience__user__pageview__datetime__date__lte="2020-1-13")&
Q(user__ma_audience__user__pageview__datetime__date__gte=F('datetime') - timedelta(days=2)),
user__ma_audience__user__pageview__url__iregex="20200109_bcpush_.*_omnicha.*",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-9", datetime__date__lte="2020-1-13").distinct().count() for t in all_0109_tags] # [3, 9, 0, 2, 1, 0]
# 有omnicha PV的conversion金額
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-3") &
Q(user__ma_audience__user__pageview__datetime__date__lte=F('datetime')) &
Q(user__ma_audience__user__pageview__datetime__date__gte=F('datetime') - timedelta(days=2)),
user__ma_audience__user__pageview__url__iregex="20200103_bcpush_.*_omnicha.*",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3").distinct().aggregate(Sum('transaction_revenue')) for t in all_tags] # [212, 273, 30, 32, 176]
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-3") &
Q(user__ma_audience__user__pageview__datetime__date__lte="2020-1-6") &
Q(user__ma_audience__user__pageview__datetime__date__gte=F('datetime') - timedelta(days=2)),
user__ma_audience__user__pageview__url__iregex="20200103_bcpush_.*_omnicha.*",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3", datetime__date__lte="2020-1-6").distinct().aggregate(Sum('transaction_revenue')) for t in all_0103_tags]
# [{'transaction_revenue__sum': Decimal('4585.00')},
# {'transaction_revenue__sum': Decimal('16760.00')},
# {'transaction_revenue__sum': None},
# {'transaction_revenue__sum': Decimal('339.00')},
# {'transaction_revenue__sum': Decimal('7974.00')},
# {'transaction_revenue__sum': None}]
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-9") &
Q(user__ma_audience__user__pageview__datetime__date__lte="2020-1-13") &
Q(user__ma_audience__user__pageview__datetime__date__gte=F('datetime') - timedelta(days=2)),
user__ma_audience__user__pageview__url__iregex="20200109_bcpush_.*_omnicha.*",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-9", datetime__date__lte="2020-1-13").distinct().aggregate(Sum('transaction_revenue')) for t in all_0109_tags]
# [{'transaction_revenue__sum': Decimal('6057.00')},
# {'transaction_revenue__sum': Decimal('14466.00')},
# {'transaction_revenue__sum': None},
# {'transaction_revenue__sum': Decimal('2433.00')},
# {'transaction_revenue__sum': Decimal('1653.00')},
# {'transaction_revenue__sum': None}]
# 各group有PageView在1/2以後含line & menu的人數
[t.current_audiences
.filter(Q(user__pageview__url__icontains="menu")&
Q(user__pageview__url__icontains="line")&
Q(user__pageview__datetime__date__gte="2020-1-3"),
line_id__isnull=False, id__in=send_audience)
.exclude(line_id="").distinct().count() for t in all_tags] # [84, 127, 13, 15, 73]
[t.current_audiences
.filter(Q(user__pageview__url__icontains="menu")&
Q(user__pageview__url__icontains="line")&
Q(user__pageview__datetime__date__gte="2020-1-3"),
Q(user__pageview__datetime__date__lte="2020-1-6"),
line_id__isnull=False, id__in=send_audience)
.exclude(line_id="").distinct().count() for t in all_0103_tags] # [126, 207, 13, 22, 100, 0]
[t.current_audiences
.filter(Q(user__pageview__url__icontains="menu")&
Q(user__pageview__url__icontains="line")&
Q(user__pageview__datetime__date__gte="2020-1-9"),
Q(user__pageview__datetime__date__lte="2020-1-13"),
line_id__isnull=False, id__in=send_audience)
.exclude(line_id="").distinct().count() for t in all_0109_tags] # [91, 205, 12, 16, 64, 0]
# 有line&menu PV的conversion次數
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__url__icontains="menu") &
Q(user__ma_audience__user__pageview__url__icontains="line") &
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-3"),
user__ma_audience__user__pageview__datetime__date__lte=F('datetime'),
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3").distinct().count() for t in all_tags] # [212, 273, 30, 32, 176]
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__url__icontains="menu") &
Q(user__ma_audience__user__pageview__url__icontains="line") &
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-3"),
user__ma_audience__user__pageview__datetime__date__lte="2020-1-6",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3", datetime__date__lte="2020-1-6").distinct().count() for t in all_0103_tags] # [12, 54, 2, 1, 28, 0]
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__url__icontains="menu") &
Q(user__ma_audience__user__pageview__url__icontains="line") &
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-9"),
user__ma_audience__user__pageview__datetime__date__lte="2020-1-13",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-9", datetime__date__lte="2020-1-13").distinct().count() for t in all_0109_tags] # [6, 18, 1, 0, 7, 0]
# 有line&menu PV的conversion金額
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__url__icontains="menu") &
Q(user__ma_audience__user__pageview__url__icontains="line") &
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-3"),
user__ma_audience__user__pageview__datetime__date__lte=F('datetime'),
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3").distinct().aggregate(Sum('transaction_revenue')) for t in all_tags] # [212, 273, 30, 32, 176]
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__url__icontains="menu") &
Q(user__ma_audience__user__pageview__url__icontains="line") &
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-3"),
user__ma_audience__user__pageview__datetime__date__lte="2020-1-6",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-3", datetime__date__lte="2020-1-6").distinct().aggregate(Sum('transaction_revenue')) for t in all_0103_tags]
# [{'transaction_revenue__sum': Decimal('17102.00')},
# {'transaction_revenue__sum': Decimal('74865.00')},
# {'transaction_revenue__sum': Decimal('2352.00')},
# {'transaction_revenue__sum': Decimal('1365.00')},
# {'transaction_revenue__sum': Decimal('27671.00')},
# {'transaction_revenue__sum': None}]
[Conversion.objects.using('readonly').filter(
Q(user__ma_audience__user__pageview__url__icontains="menu") &
Q(user__ma_audience__user__pageview__url__icontains="line") &
Q(user__ma_audience__user__pageview__datetime__date__gte="2020-1-9"),
user__ma_audience__user__pageview__datetime__date__lte="2020-1-13",
user__ma_audience__in=t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience)
.exclude(line_id=""),
datetime__date__gte="2020-1-9", datetime__date__lte="2020-1-13").distinct().aggregate(Sum('transaction_revenue')) for t in all_0109_tags]
# [{'transaction_revenue__sum': Decimal('11037.00')},
# {'transaction_revenue__sum': Decimal('40730.00')},
# {'transaction_revenue__sum': Decimal('4589.00')},
# {'transaction_revenue__sum': None},
# {'transaction_revenue__sum': Decimal('11396.00')},
# {'transaction_revenue__sum': None}]
# 各group有PageView在2019-9-1~2020-1-1以後含line的人數
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="line",
user__pageview__datetime__date__gte="2019-9-1",
user__pageview__datetime__date__lte="2019-12-31")
.exclude(line_id="").distinct().count() for t in all_tags] # [1361, 949, 174, 297, 2231]
# 各group有PageView在2019-9-1~2020-1-1以後含bcpush的人數
[t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="bcpush",
user__pageview__datetime__date__gte="2019-9-1",
user__pageview__datetime__date__lte="2019-12-31")
.exclude(line_id="").distinct().count() for t in all_tags] # [597, 640, 109, 175, 998]
# 各group有PageView在2019-9-1~2020-1-1以後含line & menu的人數
[t.current_audiences
.filter(Q(user__pageview__url__icontains="menu")&
Q(user__pageview__url__icontains="line")&
Q(user__pageview__datetime__date__gte="2019-9-1")&
Q(user__pageview__datetime__date__lte="2019-12-31"),
line_id__isnull=False, id__in=send_audience)
.exclude(line_id="").distinct().count() for t in all_tags] # [871, 776, 123, 195, 1500]
# 各月NASLD PV含Line人數
for m in [9,10,11,12]:
print(m, [t.current_audiences
.filter(line_id__isnull=False, id__in=send_audience,
user__pageview__url__icontains="line",
user__pageview__datetime__date__gte=f"2019-{m}-1",
user__pageview__datetime__date__lte=f"2019-{m}-30")
.exclude(line_id="").distinct().count() for t in all_tags])
# 9 [583, 658, 119, 180, 1058]
# 10 [675, 736, 117, 183, 1086]
# 11 [722, 696, 108, 166, 1196]
# 12 [602, 547, 65, 89, 663]
# 1/2有點擊的在9/10/11/12月也有點擊的人數
for m in [9,10,11,12]:
print(m, [t.current_audiences
.filter(Q(id__in=send_audience) &
Q(id__in=Audience.objects.filter(
id__in=send_audience,
user__pageview__datetime__date__gte=f"2019-{m}-1",
user__pageview__datetime__date__lte=f"2019-{m}-30",
user__pageview__url__icontains="line")),
line_id__isnull=False,
user__pageview__url__icontains="line",
user__pageview__datetime__date__gte="2020-1-3")
.exclude(line_id="").distinct().count() for t in all_tags])
# m月有點擊的在m-1月也有點擊的人數
for m in [9,10,11,12]:
print(m, [t.current_audiences
.filter(Q(id__in=send_audience) &
Q(id__in=Audience.objects.filter(
id__in=send_audience,
user__pageview__datetime__date__gte=f"2019-{m-1}-1",
user__pageview__datetime__date__lte=f"2019-{m-1}-30",
user__pageview__url__icontains="line")),
line_id__isnull=False,
user__pageview__url__icontains="line",
user__pageview__datetime__date__gte=f"2019-{m}-1",
user__pageview__datetime__date__lte=f"2019-{m}-30")
.exclude(line_id="").distinct().count() for t in all_tags])
# 總Conversion
Conversion.objects.filter(organization=d_org).count() # 30015
# Conversion之前(7天內)會看的次數
stat = collections.defaultdict(int)
for c_data in Conversion.objects.using('readonly').filter(
organization=d_org,
datetime__date__gte="2019-9-1",datetime__date__lte="2019-12-31",
user__isnull=False, user__ma_audience__isnull=False).all():
c = PageView.objects.using('readonly').filter(user__in=c_data.user.ma_audience.user_set.all(),
datetime__lte=c_data.datetime, datetime__gte=c_data.datetime-timedelta(days=7),
products__in=c_data.products.all()).distinct().count()
stat[c] += 1
print(c)
print([f"{k}: {stat[k]}" for k in sorted(stat.keys())])
# from 2020/1/1
# ['0: 6', '1: 19', '2: 44', '3: 41', '4: 39', '5: 37', '6: 34', '7: 36', '8: 31', '9: 18', '10: 30',
# '11: 22', '12: 9', '13: 13', '14: 12', '15: 9', '16: 7', '17: 10', '18: 10', '19: 8', '20: 6',
# '21: 6', '22: 6', '23: 6', '24: 5', '25: 3', '27: 4', '28: 2', '29: 2',
# '31: 13', '32: 2', '33: 3', '34: 3', '35: 3', '36: 2', '37: 3', '38: 1', '39: 2',
# '41: 1', '45: 2', '46: 1', '47: 2', '49: 1', '55: 3', '56: 1', '58: 1', '66: 1', '68: 1',
# '71: 1', '75: 1', '118: 1', '232: 1', '246: 1', '373: 1', '532: 1']
# from 2019/12/1
# ['0: 115', '1: 166', '2: 288', '3: 297', '4: 308', '5: 337', '6: 293', '7: 265', '8: 222', '9: 220', '10: 208',
# '11: 182', '12: 144', '13: 149', '14: 135', '15: 99', '16: 84', '17: 89', '18: 78', '19: 90', '20: 57',
# '21: 71', '22: 52', '23: 56', '24: 50', '25: 58', '26: 34', '27: 39', '28: 27', '29: 35', '30: 23',
# '31: 56', '32: 28', '33: 20', '34: 30', '35: 28', '36: 27', '37: 17', '38: 14', '39: 21', '40: 13',
# '41: 13', '42: 20', '43: 15', '44: 15', '45: 14', '46: 5', '47: 16', '48: 6', '49: 9', '50: 6',
# '51: 6', '52: 5', '53: 8', '54: 6', '55: 16', '56: 7', '57: 1', '58: 12', '59: 7', '60: 5',
# '61: 7', '62: 3', '63: 3', '64: 3', '65: 5', '66: 3', '67: 4', '68: 4', '69: 7',
# '71: 6', '73: 2', '74: 1', '75: 3', '76: 5', '77: 2', '78: 3', '79: 2', '80: 1',
# '81: 2', '82: 5', '83: 2', '84: 4', '86: 2', '88: 1', '89: 3', '90: 1',
# '91: 2', '92: 4', '93: 3', '94: 3', '95: 1', '96: 2', '97: 1', '98: 1',
# '102: 2', '103: 1', '104: 2', '105: 2', '106: 1', '109: 2', '110: 1'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment