[report] Customer Acquisition and Loyalty
diff --git a/selling/page/selling_home/selling_home.js b/selling/page/selling_home/selling_home.js
index d645403..a64ed48 100644
--- a/selling/page/selling_home/selling_home.js
+++ b/selling/page/selling_home/selling_home.js
@@ -164,6 +164,11 @@
"label":wn._("Sales Funnel"),
page: "sales-funnel"
},
+ {
+ "label":wn._("Customer Acquisition and Loyalty"),
+ route: "query-report/Customer Acquisition and Loyalty",
+ doctype: "Customer"
+ },
]
},
{
diff --git a/selling/report/customer_acquisition_and_loyalty/__init__.py b/selling/report/customer_acquisition_and_loyalty/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/selling/report/customer_acquisition_and_loyalty/__init__.py
diff --git a/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.js b/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.js
new file mode 100644
index 0000000..00f935e
--- /dev/null
+++ b/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.js
@@ -0,0 +1,26 @@
+// Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+// License: GNU General Public License v3. See license.txt
+
+wn.query_reports["Customer Acquisition and Loyalty"] = {
+ "filters": [
+ {
+ "fieldname":"company",
+ "label": wn._("Company"),
+ "fieldtype": "Link",
+ "options": "Company",
+ "default": wn.defaults.get_user_default("company")
+ },
+ {
+ "fieldname":"from_date",
+ "label": wn._("From Date"),
+ "fieldtype": "Date",
+ "default": wn.defaults.get_user_default("year_start_date")
+ },
+ {
+ "fieldname":"to_date",
+ "label": wn._("To Date"),
+ "fieldtype": "Date",
+ "default": wn.defaults.get_user_default("year_end_date")
+ },
+ ]
+}
\ No newline at end of file
diff --git a/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.py b/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.py
new file mode 100644
index 0000000..77432ea
--- /dev/null
+++ b/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.py
@@ -0,0 +1,65 @@
+# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd. and Contributors
+# License: GNU General Public License v3. See license.txt
+
+from __future__ import unicode_literals
+import webnotes
+from webnotes.utils import getdate, cint
+import calendar
+
+def execute(filters=None):
+ # key yyyy-mm
+ new_customers_in = {}
+ repeat_customers_in = {}
+ customers = []
+ company_condition = ""
+
+ if filters.get("company"):
+ company_condition = ' and company=%(company)s'
+
+ for si in webnotes.conn.sql("""select posting_date, customer, grand_total from `tabSales Invoice`
+ where docstatus=1 and posting_date <= %(to_date)s
+ {company_condition} order by posting_date""".format(company_condition=company_condition),
+ filters, as_dict=1, debug=1):
+
+ key = si.posting_date[:7]
+ if not si.customer in customers:
+ new_customers_in.setdefault(key, [0, 0.0])
+ new_customers_in[key][0] += 1
+ new_customers_in[key][1] += si.grand_total
+ customers.append(si.customer)
+ else:
+ repeat_customers_in.setdefault(key, [0, 0.0])
+ repeat_customers_in[key][0] += 1
+ repeat_customers_in[key][1] += si.grand_total
+
+ # time series
+ from_year, from_month, temp = filters.get("from_date").split("-")
+ to_year, to_month, temp = filters.get("to_date").split("-")
+
+ from_year, from_month, to_year, to_month = \
+ cint(from_year), cint(from_month), cint(to_year), cint(to_month)
+
+ out = []
+ for year in xrange(from_year, to_year+1):
+ for month in xrange(from_month if year==from_year else 1, (to_month+1) if year==to_year else 13):
+ key = "{year}-{month:02d}".format(year=year, month=month)
+
+ new = new_customers_in.get(key, [0,0.0])
+ repeat = repeat_customers_in.get(key, [0,0.0])
+
+ out.append([year, calendar.month_name[month],
+ new[0], repeat[0], new[0] + repeat[0],
+ new[1], repeat[1], new[1] + repeat[1]])
+
+ return [
+ "Year", "Month",
+ "New Customers:Int",
+ "Repeat Customers:Int",
+ "Total:Int",
+ "New Customer Revenue:Currency:150",
+ "Repeat Customer Revenue:Currency:150",
+ "Total Revenue:Currency:150"
+ ], out
+
+
+
\ No newline at end of file
diff --git a/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.txt b/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.txt
new file mode 100644
index 0000000..c1f7e94
--- /dev/null
+++ b/selling/report/customer_acquisition_and_loyalty/customer_acquisition_and_loyalty.txt
@@ -0,0 +1,22 @@
+[
+ {
+ "creation": "2013-11-28 14:58:06",
+ "docstatus": 0,
+ "modified": "2013-11-28 15:02:48",
+ "modified_by": "Administrator",
+ "owner": "Administrator"
+ },
+ {
+ "add_total_row": 1,
+ "doctype": "Report",
+ "is_standard": "Yes",
+ "name": "__common__",
+ "ref_doctype": "Customer",
+ "report_name": "Customer Acquisition and Loyalty",
+ "report_type": "Script Report"
+ },
+ {
+ "doctype": "Report",
+ "name": "Customer Acquisition and Loyalty"
+ }
+]
\ No newline at end of file