-
Notifications
You must be signed in to change notification settings - Fork 0
/
report_week.py
161 lines (141 loc) · 6.34 KB
/
report_week.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
from DrissionPage import ChromiumPage
import gspread
from oauth2client.service_account import ServiceAccountCredentials
URL_CONVERSION_AND_TRAFFIC = "https://admin.shopify.com/store/e39ad7/analytics/reports/6717725?ql=FROM+sessions%2C+sales%0ASHOW+conversion_rate%2C+average_order_value%2C+net_sales%2C+sessions%2C+returning_customers%2C+added_to_cart_rate%2C+average_session_duration%0AGROUP+BY+hour%0AWITH+TOTALS%0ASINCE+startOfWeek%280w%29%0AUNTIL+today%0AORDER+BY+hour+ASC%0ALIMIT+1000%0AVISUALIZE+conversion_rate+TYPE+bar"
"https://admin.shopify.com/store/e39ad7/analytics/reports/6717725?ql=FROM+sessions%2C+sales%0ASHOW+conversion_rate%2C+average_order_value%2C+net_sales%2C+sessions%2C+returning_customers%2C+added_to_cart_rate%2C+average_session_duration%0AGROUP+BY+week%0AWITH+TOTALS%0ASINCE+startOfMonth%280m%29%0AUNTIL+today%0AORDER+BY+week+ASC%0ALIMIT+1000%0AVISUALIZE+conversion_rate+TYPE+bar"
URL_ONE_TIME = "https://admin.shopify.com/store/e39ad7/analytics/reports/6750493?ql=FROM+sales%0ASHOW+gross_margin%2C+orders%0AGROUP+BY+hour%0ATIMESERIES+HOUR%0AWITH+TOTALS%0ASINCE+startOfWeek%280w%29%0AUNTIL+today%0AORDER+BY+hour+ASC%0ALIMIT+1000%0AVISUALIZE+gross_margin+TYPE+line"
URL_SUBSCRIPTION = "https://admin.shopify.com/store/e39ad7/analytics/reports/6619421?ql=FROM+sales%0ASHOW+net_sales%2C+gross_margin%0AGROUP+BY+hour%0ATIMESERIES+HOUR%0AWITH+TOTALS%0ASINCE+startOfWeek%280w%29%0AUNTIL+today%0AORDER+BY+hour+ASC%0ALIMIT+1000%0AVISUALIZE+net_sales+TYPE+line"
URL_SUBSCRIPTION_EXISTING = "https://admin.shopify.com/store/e39ad7/analytics/reports/6783261?ql=FROM+sales%0ASHOW+net_sales%2C+orders%0AGROUP+BY+hour%0ATIMESERIES+HOUR%0AWITH+TOTALS%0ASINCE+startOfWeek%280w%29%0AUNTIL+today%0AORDER+BY+hour+ASC%0ALIMIT+1000%0AVISUALIZE+orders+TYPE+line"
URL_SUBSCRIPTION_NEW = "https://admin.shopify.com/store/e39ad7/analytics/reports/6652189?ql=FROM+sales%0ASHOW+net_sales%2C+orders%0AGROUP+BY+hour%0ATIMESERIES+HOUR%0AWITH+TOTALS%0ASINCE+startOfWeek%280w%29%0AUNTIL+today%0AORDER+BY+hour+ASC%0ALIMIT+1000%0AVISUALIZE+net_sales+TYPE+line"
urls = [URL_CONVERSION_AND_TRAFFIC, URL_SUBSCRIPTION, URL_SUBSCRIPTION_NEW, URL_SUBSCRIPTION_EXISTING, URL_ONE_TIME]
page = ChromiumPage()
report_website_of_this_week = {
"conversion_rate" : "",
"average_order_value" : "",
"net_sales" : "",
"sessions" : "",
"returning_customers" : "",
"add_to_basket" : "",
"average_session_duration" : "",
"shopify_store_speed" : "",
"revenue_from_subscriptions": "",
"gross_margin_subscription": "",
"new_subscription_added_value": "",
"of_new_sub_orders": "",
"existing_subscription_added_value": "",
"of_existing_sub_orders": "",
"gross_margin_one_time": "",
"of_one_time_orders": ""
}
date = ""
for index, url in enumerate(urls):
page.get(url)
data_summary = page.ele("@data-summary-row=true").text.split("\n")
print(data_summary)
if index == 0:
date = data_summary[0]
report_website_of_this_week["conversion_rate"] = data_summary[1]
report_website_of_this_week["average_order_value"] = data_summary[2]
report_website_of_this_week["net_sales"] = data_summary[3]
report_website_of_this_week["sessions"] = data_summary[4]
report_website_of_this_week["returning_customers"] = data_summary[5]
report_website_of_this_week["add_to_basket"] = data_summary[6]
report_website_of_this_week["average_session_duration"] = data_summary[7]
report_website_of_this_week["shopify_store_speed"] = "null"
elif index == 1:
report_website_of_this_week["revenue_from_subscriptions"] = data_summary[1]
report_website_of_this_week["gross_margin_subscription"] = data_summary[2]
elif index == 2:
report_website_of_this_week["new_subscription_added_value"] = data_summary[1]
report_website_of_this_week["of_new_sub_orders"] = data_summary[2]
elif index == 3:
report_website_of_this_week["existing_subscription_added_value"] = data_summary[1]
report_website_of_this_week["of_existing_sub_orders"] = data_summary[2]
else:
report_website_of_this_week["gross_margin_one_time"] = data_summary[1]
report_website_of_this_week["of_one_time_orders"] = data_summary[2]
COLS = {
"Jan 1, 2024" : 4,
"Jan 8, 2024" : 5,
"Jan 15, 2024" : 6,
"Jan 22, 2024" : 7,
"Jan 29, 2024" : 8,
"Feb 5, 2024" : 9,
"Feb 12, 2024" : 10,
"Feb 19, 2024" : 11,
"Feb 26, 2024" : 12,
"Mar 4, 2024" : 13,
"Mar 11, 2024" : 14,
"Mar 18, 2024" : 15,
"Mar 25, 2024" : 16,
"Apr 1, 2024" : 17,
"Apr 8, 2024" : 18,
"Apr 15, 2024" : 19,
"Apr 22, 2024" : 20,
"Apr 29, 2024" : 21,
"May 6, 2024" : 22,
"May 13, 2024" : 23,
"May 20, 2024" : 24,
"May 27, 2024" : 25,
"Jun 3, 2024" : 26,
"Jun 10, 2024" : 27,
"Jun 17, 2024" : 28,
"Jun 24, 2024" : 29,
"Jul 1, 2024" : 30,
"Jul 8, 2024" : 31,
"Jul 15, 2024" : 32,
"Jul 22, 2024" : 33,
"Jul 29, 2024" : 34,
"Aug 5, 2024" : 35,
"Aug 12, 2024" : 36,
"Aug 19, 2024" : 37,
"Aug 26, 2024" : 38,
"Sep 2, 2024" : 39,
"Sep 9, 2024" : 40,
"Sep 16, 2024" : 41,
"Sep 23, 2024" : 42,
"Sep 30, 2024" : 43,
"Oct 7, 2024" : 44,
"Oct 14, 2024" : 45,
"Oct 21, 2024" : 46,
"Oct 28, 2024" : 47,
"Nov 4, 2024" : 48,
"Nov 11, 2024" : 49,
"Nov 18, 2024" : 50,
"Nov 25, 2024" : 51,
"Dec 2, 2024" : 52,
"Dec 9, 2024" : 53,
"Dec 16, 2024" : 54,
"Dec 23, 2024" : 55,
"Dec 30, 2024" : 56
}
MAP_WEBSITE_ROWS = {
"conversion_rate" : 4,
"average_order_value" : 5,
"net_sales" : 6,
"sessions" : 14,
"returning_customers" : 15,
"add_to_basket" : 16,
"average_session_duration" : 18,
"shopify_store_speed" : 19,
"revenue_from_subscriptions": 22,
"gross_margin_subscription": 24,
"new_subscription_added_value": 27,
"of_new_sub_orders": 29,
"existing_subscription_added_value": 31,
"of_existing_sub_orders": 33,
"gross_margin_one_time": 41,
"of_one_time_orders": 42
}
scope = [
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/drive.file'
]
file_name = 'service-account-file.json'
creds = ServiceAccountCredentials.from_json_keyfile_name(file_name,scope)
client = gspread.authorize(creds)
spreadsheet = client.open('Shopify Report')
[sheet_website, sheet_ads, sheet_crm] = spreadsheet.worksheets()
for key, row in MAP_WEBSITE_ROWS.items():
sheet_website.update_cell(row, COLS[date], report_website_of_this_week[key])
print("recorded successfully")