def make_pivot(dont_push: bool = True):
Ge = GC_e()
df1 = Extract.read("../exports/GC_deals_payed.p")
sumcol = "Оплачено"
cols = [
"HC_HT_mapped",
"date_mapped",
"Площадка_mapped",
]
df1[sumcol] = df1[sumcol].astype(float)
df2 = df1[df1[sumcol] > 0]
df2 = df2[cols + [sumcol]]
df3 = df2.pivot_table(
columns=cols[-1],
index=cols[:-1],
values=sumcol,
aggfunc=["sum", "count"],
)
df3 = df3.fillna("")
# df3.columns = df3.columns.reorder_levels([i for i in range(len(
# cols))] )
df3 = df3.reset_index()
df3 = df3.sort_values(
["HC_HT_mapped", "date_mapped"],
ascending=[False, True],
)
if not dont_push:
l = Load("GC_pivot")
l.load(GC_t.finalize(df3))
Extract.write("../exports/GC_pivot.p", df3)
def extract(self, full: bool = True):
ans = self.get("ads.getCampaigns")
campaigns = pd.json_normalize(ans)
campaigns_l = campaigns["id"].to_list()
campaigns_l = [str(e) for e in campaigns_l]
ans = self.get_ads(ids=campaigns_l)
Extract.write("../exports/VK_extracted.p", ans)
return ans
def extract(
self,
from_pickle=False,
write: bool = False,
**kwargs,
):
"""
lets wrap because server answers 1k of rows.
count:int
offset:int
date_first_from:str YYYY-MM-DD HH:MM:SS
date_first_to:str YYYY-MM-DD HH:MM:SS
https://help.senler.ru/api/spisok-metodov/podpischiki/poluchenie-podpischikov
"""
if from_pickle:
return Extract.read(
"../exports/SE_extracted.p"
)
if "date_first_from" not in kwargs:
kwargs[
"date_first_from"
] = datetime.today().strftime("%Y-01-01")
def get_group_names():
_ = self.get(endpoint="subscriptions/get")
assert _["success"] == True
assert len(_["items"]) < 1000
_ = _["items"]
_ = {
int(ele["subscription_id"]): ele["name"]
for ele in _
for k, v in ele.items()
}
Extract.write(
"../exports/SE_groups_map.p", _
)
get_group_names()
_ = self.get(**kwargs)
_ = _["count"]
rows = int(_)
retval = []
for i in range(0, rows, 1000):
ans = self.get(**kwargs, offset=i,)
retval.extend(ans["items"])
if write:
Extract.write(
"../exports/SE_extracted.p", retval
)
return retval
def get_group_names():
_ = self.get(endpoint="subscriptions/get")
assert _["success"] == True
assert len(_["items"]) < 1000
_ = _["items"]
_ = {
int(ele["subscription_id"]): ele["name"]
for ele in _
for k, v in ele.items()
}
Extract.write(
"../exports/SE_groups_map.p", _
)
def extract(self, short=False):
# todo adset and ad
me = User("me")
accounts = list(me.get_ad_accounts(self.accs_fields))
if short:
accounts = [accounts[2]]
self.insights_params["time_range"]["since"] = "2020-07-17"
self.insights_params["time_range"]["until"] = "2020-07-23"
campaigns = []
for ele in accounts:
campaigns.extend(list(ele.get_campaigns(self.camps_fields)))
insights = {}
for e in campaigns:
insights[e["id"]] = list(
e.get_insights(
self.insights_fields,
self.insights_params,
is_async=False,
))
if self.year_start != self.year_n_week_start:
params2 = dict(
time_increment=7,
time_range=dict(
since=self.year_start,
until=self.year_n_week_start,
),
)
for e in campaigns:
insights[e["id"]] += list(
e.get_insights(
self.insights_fields,
params2,
is_async=False,
))
accounts = {ele["id"][4:]: ele["name"] for ele in accounts}
campaigns = {ele["id"]: dict(ele) for ele in campaigns}
ans = [
dict(
**campaigns[k],
account_name=accounts[campaigns[k]["account_id"]],
data=[dict(ele) for ele in v],
) for k, v in insights.items() if len(v) > 0
]
Extract.write("../exports/FB_extracted.p", ans)
return ans
def change(self, data: list):
grp_names = Extract.read(
"../exports/SE_groups_map.p"
)
retval = [
{
**e1,
**{
k: v
for k, v in e.items()
if k in self.cols_naming
},
}
for e in data
for e1 in e["subscriptions"]
if len(e["subscriptions"]) > 0
]
retval = pd.DataFrame.from_dict(retval)
retval["sub_name"] = retval[
"subscription_id"
].apply(lambda x: grp_names[x])
retval["sub_name_-3:"] = retval["sub_name"].str[
-3:
]
retval.date = retval.date.str[:10]
return retval
def extract(
self,
task: str = "users",
date_from: str = None,
date_to: str = None,
status: str = None,
export_id: int = None,
from_pickle: bool = False,
write: bool = False,
):
"""
Args:
export_id: could b received from error
task: ['users','deals']
date_from: 'YYYY-MM-DD'
date_to: 'YYYY-MM-DD'
from_pickle: bool
status: {users:[active,in_base],
write: bool
"""
status_list = [
"new",
"payed",
"cancelled",
"in_work",
"payment_waiting",
"part_payed",
"waiting_for_return",
"not_confirmed",
"pending",
]
def check_args():
if task not in ["users", "deals"]:
raise TypeError
if task == "users":
if status not in [
None,
"active",
"in_base",
]:
raise TypeError
if task == "deals":
if status not in [
None,
"new",
"payed",
"cancelled",
"in_work",
"payment_waiting",
"part_payed",
"waiting_for_return",
"not_confirmed",
"pending",
]:
raise TypeError
fname = f"../exports/GC_{task}"
if status is not None:
fname += "_" + status
fname += "_extracted.p"
if from_pickle:
return Extract.read(fname)
@retry(wait=wait_fixed(30))
def get_export_id():
ans = self.get(endpoint=task, **kwargs)
if "error_code" in ans:
print(ans["error_message"])
raise
export_id = ans["info"]["export_id"]
return export_id
@retry(wait=wait_fixed(200))
def get_export(export_id):
ans = self.get(endpoint="exports/" + str(export_id), )
if "error_code" in ans:
print(ans["error_message"])
raise
return ans
def date_check(data, date: str):
data = data["info"]
check = data["fields"].index("Создан")
check = data["items"][-1][check]
check = check[:10]
return check
def merge_json(ans1: dict, ans2: dict):
data = ans1["info"]
col = data["fields"].index("Создан")
check = data["items"][-1][col]
ans1["info"]["items"] = [
e for e in ans1["info"]["items"]
if not e[col].startswith(check)
]
ans1["info"]["items"].append(ans2["info"]["items"])
return ans1
check_args()
kwargs = populate_kwargs(
("created_at[to]", date_to, today),
(
"created_at[from]",
date_from,
year_start,
),
("status", status),
)
if export_id is None:
export_id = get_export_id()
sleep(30)
ans = get_export(export_id)
# export <= 50k of rows
if len(ans["info"]["items"]) > 49997:
date_from_new = date_check(data=ans, date=date_to)
ans2 = self.extract(
task=task,
date_from=date_from_new,
date_to=date_to,
status=status,
export_id=export_id,
)
if (ans2["info"]["fields"] != ans["info"]["fields"]):
raise
else:
ans = merge_json(ans, ans2)
if write:
Extract.write(fname, ans)
return ans
def prepare_data():
work = [
"VK",
"FB",
"SE",
"GC",
]
params = {}
for ele in work:
if ele == "GC":
ele += "_pivot"
params[ele] = Extract.read(
f"../exports/{ele}.p"
)
def vk_p(df1):
sumcol = ["Потрачено"]
cols = ["date_mapped"]
df1[sumcol] = df1[sumcol].astype(float)
df2 = df1.pivot_table(
index=cols, values=sumcol, aggfunc="sum",
)
df2["Потрачено с учетом бонуса"] = (
0.7 * df2["Потрачено"]
)
prefix = "vk_"
c = list(df2.columns)
c = [
(prefix + e)
for e in c
if e != "date_mapped"
]
df2.columns = c
return df2
def fb_p(df1):
sumcol = ["spend"]
cols = ["date_mapped"]
df1[sumcol] = df1[sumcol].astype(float)
df2 = df1.pivot_table(
index=cols, values=sumcol, aggfunc="sum",
)
prefix = "fb_"
c = list(df2.columns)
c = [
(prefix + e)
for e in c
if e != "date_mapped"
]
df2.columns = c
return df2
def se_p(df1):
sumcol = ["vk_mapped"][0]
cols = ["date_mapped"][0]
df1 = df1[df1[sumcol] == "vk"][[sumcol, cols]]
df2 = df1.groupby(cols).count().reset_index()
df2.columns = [
"date_mapped",
"se_users_reg_count",
]
return df2
def gc_p(df1):
df2 = df1.set_index(
["date_mapped", "HC_HT_mapped"]
)
df2 = df2.unstack()
df2.columns = df2.columns.reorder_levels(
[1, 2, 0]
)
df2 = df2.reset_index()
return df2
ans = [
fb_p(params["FB"]),
se_p(params["SE"]),
vk_p(params["VK"]),
gc_p(params["GC_pivot"]),
]
ans = reduce(
lambda x, y: pd.merge(
x, y, on="date_mapped", how="outer"
),
ans,
)
ans = ans.fillna("")
Extract.write("../exports/final_pivot.p", ans)
return ans