Margins and Markups: how to handle multiple discounts/rebates

Following on from an old blog post about Margins and markups What everyone in business needs to know a reader posted this question in the comments

If you’re not sure how to calculate margins, read that first.

Hi, how do you do multiple rebates % to give a true value of what the total discount would be.
I.e. 3% + 6% + 8%, is this 17%

TL;DR … probably not 17% but it depends on how your supplier intended you to do it, more likely
100*0.97*0.94*0.92 = 83.8856 remaining, so
100 – ( 100*0.97*0.94*0.92 ) = 16.1144%

The answer is a blog post in it’s own right, but the short version is: It depends.

The most common use I’ve seen in my industry (UK businesses supplying other businesses with kitchen furniture and appliances) is to Apply each discount in turn, using the last result with the next discount value. The only way to be certain is to ask. Even then, you need to make sure you ask the right person. It seems to get confusing to sales reps and accounts departments alike. In once case, I only figured out by checking past supply invoices.

Simple example:
Supplier gives terms of:

  • 30% off retail price to all customers
  • 10% for displaying 1 product
  • 5% for displaying 3 products

This is typical of the ambiguous wording we see.

Note: I’m using the decimal form to calculate the margin – see how and why on the original blog post
In my own industry;
£100 * 0.3 = £30 discount, £70 cost.
The next 10% is 10% off the remaining COST, not 10% off the original retail price
£70 * 0.1 = £7 = £63 cost
The next 5% is in addition to the prior two discounts and also taken off the remaining Cost, not the original retail price
£63 * 0.05 = £3.15 = £59.85

Therefore, the margin for this supplier is: 40.15%
If you only have one location to put the margin, then use 40.15.

However…. it’s not always this simple, which is why you NEED TO CHECK YOUR SOURCE.

Here’s another example of what I’ve seen.
Supplier gives terms of:

  • 30% off retail price to all customers
  • 10% for displaying 1 product
  • 5% for displaying 3 products

This is typical of the ambiguous wording we see.
Wait, that’s the same example….. That’s right. But this time the supplier thinks it’s obvious everyone knows that the first 30% is given to everyone and that the extra for displaying 10% is 10% off the cost and that the extra extra 5% makes it 15% for displaying 3 products.
Sigh.

£100 * 0.3 = £30 discount, £70 cost.
The next 10% is 10% off the remaining COST, not 10% off the original retail price
£70 * 0.1 = £7
The next 5% is off the remaining cost before we got our earlier discount.
£70 * 0.05 = £3.50
Or, we could just say, 15% off the remaining cost in this case, £70 * 0.15 = £10.50
Total cost: £59.50
Margin: 40.50%

These small differences add up and can get confusing quickly.

Why isn’t there one system? It depends on why the extra margins are being applied and used. I’ve seen it deliberately used to make the discount appear bigger. A sales rep will incorrectly say: “We’ll give you 40% off for displaying this product, and if you display another product too we’ll give you an extra 20% off, that’s 60% discount!”…. no, it’s 100-(100*0.6*0.8)= 52% discount.

Blog traffic

sep2016_cloudflare_traffic According to cloudflare, my blog traffic this month.

sep2016_analytics_trafficAccording to google analytics, my blog traffic this month.

The difference?
a) Cloudflare is counting requests to my server (each image, each file, each style sheet) whereas Google Analytics is joining requests from the same visitor into a session.
b) Hackers don’t always ask for a page, and don’t load the javascript that google analytics need to track the visitor.

What can we infer from this?
Computers in Ukraine that are visiting the blog aren’t reading this text, but they visit a lot.
We cannot state with certainty the users instigating this are in Ukraine though, just that the requests can be traced as far as there. Likewise, computers in the USA requesting items from my server are less likely to be people than computers in the UK.
My guess of the reason for this: Hacking attempts, search engines.
You cannot tell from the source (eg: Ukraine) that the people controlling the computer requesting files [be that hacking or search engine or other uses good and bad] are in Ukraine. For example, for a long time my web server lived in Paris, but I’m not in France to control it.

Upgrading my server to PHP7 broke wordpress admin interface (cloudflare)

wp-cli, a command line interface for WordPress, just saved me a big headache.

It all began when I updated my server to the latest ubuntu LTS. That removed PHP5 support and replaced it with PHP7. That meant several of my sites stopped working as the nginx configuration had to change. With that done I thought the blog as the pages were still displaying OK but for some reason I could no longer access the admin interface to make new posts or moderate comments. Instead, when I logged in I would see a message: Sorry, you are not allowed to access this page

I decided the most likely cause of this would be a plugin. However, I couldn’t use the admin interface to disable all the plugins so I needed to find another way. I read that I could start editing the database directly but then I stumbled upon wp-cli. Installing that allowed me to use an SSH shell to check, disable, enable and update the plugins. I quickly discovered that there was an update to cloudflare that hadn’t been applied. I applied it and it failed. I’m not entirely sure if it failed due to the background change of PHP5 to PHP7, or if my earlier fault finding had changed a file owner (I tried removing the plugins by moving the directory to a new name), but once I used wp-cli to update cloudflare everything started working perfectly, including the ability to preview theme changes which had stopped working a long time ago.

If I didn’t find wp-cli I would have been checking my backups, creating a test server in my office to install and test backups and assuming they worked removing and reinstalling wordpress.

Spam from Scottish Power, their response: we’ll carry on spamming (just not to you).

Spam annoys me.
More specifically, the effect of spam annoys me. It annoys me that from time to time I find emails I really need have been filtered to a spam folder. It annoys me that from time to time customers don’t receive emails from us because our email is filtered to their spam folder. It annoys me that I’ve had to jump through hoops to get email delivered to members of a community group I’m a part of ’cause Microsoft’s email servers overzealous approach to spam filtering (tell my server the email was accepted and delivered, but not deliver it to the member or even the spam folder of the member – took several hours to figure out they were doing that..)

Anyway, in the UK we have laws that ban companies from sending unsolicited email. Sure, it won’t stop the non UK spammers or those peddling dodgy things, but it’s a start at least. The rules are called The Privacy and Electronic Communications (EC Directive) Regulations 2003 – abbreviated to PECR. The sad thing is, there is essentially no enforcement of these[1]. Most of the spam I get is from small companies that have been sold my address and don’t realise that is not allowed by the regulations. Last month I got new spam email from Scottish Power. I’d expect them to know better than buy email addresses, but apparently they think it’s OK.

From the regulations:
a person shall neither transmit, nor instigate the transmission of, unsolicited communications for the purposes of direct marketing by means of electronic mail unless the recipient of the electronic mail has previously notified the sender that he consents for the time being to such communications being sent by, or at the instigation of, the sender.
(3) A person may send or instigate the sending of electronic mail for the purposes of direct marketing where—
(a)that person has obtained the contact details of the recipient of that electronic mail in the course of the sale or negotiations for the sale of a product or service to that recipient;
(b)the direct marketing is in respect of that person’s similar products and services only; and
(c)the recipient has been given a simple means of refusing (free of charge except for the costs of the transmission of the refusal) the use of his contact details for the purposes of such direct marketing, at the time that the details were initially collected, and, where he did not initially refuse the use of the details, at the time of each subsequent communication.

The text I put in bold, to me at least, makes it clear that if I’ve never communicated with your company I can’t possible have given consent to your company to send me email. The Information Commissioners Office (ICO) appear to agree – “You must not send marketing emails or texts to individuals without specific consent. There is a limited exception for your own previous customers, often called the ‘soft opt-in’.

Scottish Power made it clear in their replies to my emails (all below) that they think it is acceptable to buy email addresses. Even though they will stop sending to me (’cause I asked them to stop, and I believe they will), the people they bought it from will continue to sell my address to other companies. Scottish Power did not provide contact details for those companies (they named two companies) so did not provide a way for me to unsubscribe from future spam. They claim (in the emails below) they only send to corporate email addresses or opt-in addresses but on this occasion could not find proof of opt in. Maybe that’s true, maybe my address is the only one that slipped through a vetting process, however my instincts are that they’ve bought a load of addresses as ‘businesses’ and used them all.

A long time ago, I read a Mailchimp article (mailchimp are a company that provide email services for newsletters, marketing etc) about whether you should use bought in email lists, the answer: http://caniuseapurchasedemaillist.com/

Scottish Power – here’s a public message to you; Please stop sending spam!


For those who like to know the detail without my summary, here are the emails:


From: ScottishPower Business
Date: 28 April 2016 at 08:13
Subject: Let’s make your business energy simpler

If you are unable to view this email, click here to view online.
We know it takes a lot to run a business – being on
top of every detail matters. That's why, when you switch

If you are unable to view this email, click here
to view online.
[image: ScottishPower]



From: Steve Root
Date: 28.04.2016 07:58:31
To: [email protected]
Cc:
Subject: Fwd: ))) scottishpower1 ((( Fwd: Let ’s make your business energy simpler

Where did you get this email address from please?



From: [email protected] [mailto:[email protected]]
Sent: 28 April 2016 13:37
To: Data Protection (Energy Retail)
Subject: Fwd: Fwd: ))) scottishpower1 ((( Fwd : Let ’s make your business energy simpler

Hi ,
We have recieved an auto reponse and customer is quering where we got the email address from ,I have no way of tracing this as per email below ,Please advise or contact the customer if possible .
Regards
Kim McLaughlin
Scottish Power Business Energy Team Glasgow



29th April

Dear Mr Root

Thank you for your email of 28 April 2016.

Having contacted our business marketing team, I can advise you that your email address was purchased from a Third Party for use in marking activity.

Following on from your email, I have arranged to have your email address removed from any marketing activity so you will no longer receive emails from ScottishPower.

Many Thanks

Andrew Healing
Iberdrola Group|Global Retail Operations
Data Protection Adviser



30th APril

Thank you Andrew, can you provide contact details for this company please.

Regards
Steve



2nd May

Good Morning Mr Root

Thank you for your email.

I have received confirmation that your information was supplied to us from our Third Party provider Call Credit. They purchased your data from a Company called Blue Sheep.

Many Thanks

Andrew Healing
Iberdrola Group|Global Retail Operations
Data Protection Adviser



3 May
Thanks Andrew,

I'm really curious how your company's decision to buy addresses, like mine, correlates with the requirements of the PECR regulations. Can you help me understand your interpretation please?

Thanks



10th May
Thanks Andrew,

I'm really curious how your company's decision to buy addresses, like mine, correlates with the requirements of the PECR regulations. Can you help me understand your interpretation please?

Thanks



10th May
Good Morning Mr Root

I am awaiting a response to an email that has been sent to our marketing team. This has been chased up today. As soon as I receive a response I will be back in touch.

Thanks

Andrew Healing
Iberdrola Group|Global Retail Operations
Data Protection Adviser



16th May
Hello Andrew,
I'm still curious.
Thanks
Steve



18th May

Good Afternoon Mr Root

I am sorry that I have not been able to come back to you with the information you have requested.

We are keen to understand exactly how and when your details were collected so that we can provide an accurate response.

We are awaiting a full response from our data provider. I have asked for this to be pursued again today and have expressed the urgency. Thank you for your patience, I will be in touch with you as soon as we have the required information.

Kind Regards

Andrew Healing
Iberdrola Group|Global Retail Operations
Data Protection Adviser



26th May
Hello Andrew,

I restate my question:
"I'm really curious how your company's decision to buy addresses, like mine, correlates with the requirements of the PECR regulations. Can you help me understand your interpretation please?"

Maybe it's time to escalate this?

Thanks



Steve Root
31 May
to: Andrew Healing
to: richard.taylor, marion.venman

OK, still no response as to why Scottish Power think it's OK to ignore the PECR regulations.
I'll guess a couple of email addresses and see if that helps find an answer...



31st May

Dear Mr Root

We received your email address from our data provider as “consented data”.
We have noted your wishes that we do not contact this email address any further.
We are reviewing our processes to ensure we adhere to both the PECR and Data Protection requirements.

Kind Regards

Andrew Healing
Iberdrola Group|Global Retail Operations
Data Protection Adviser



1st June

Hello Andrew,
Thank you for replying but you still ignore my question:
"I'm really curious how your company's decision to buy addresses, like mine, correlates with the requirements of the PECR regulations. Can you help me understand your interpretation please?"

The PECR regulations are very clear and having a job title of 'data protection advisor' I'm sure you know this but to spell it out;

http://www.legislation.gov.uk/uksi/2003/2426/regulation/22/made

Use of electronic mail for direct marketing purposes
22.—(1) This regulation applies to the transmission of unsolicited communications by means of electronic mail to individual subscribers.
(2) Except in the circumstances referred to in paragraph (3), a person shall neither transmit, nor instigate the transmission of, unsolicited communications for the purposes of direct marketing by means of electronic mail unless the recipient of the electronic mail has previously notified the sender that he consents for the time being to such communications being sent by, or at the instigation of, the sender.
(3) A person may send or instigate the sending of electronic mail for the purposes of direct marketing where—
(a)that person has obtained the contact details of the recipient of that electronic mail in the course of the sale or negotiations for the sale of a product or service to that recipient;
(b)the direct marketing is in respect of that person’s similar products and services only; and
(c)the recipient has been given a simple means of refusing (free of charge except for the costs of the transmission of the refusal) the use of his contact details for the purposes of such direct marketing, at the time that the details were initially collected, and, where he did not initially refuse the use of the details, at the time of each subsequent communication.
(4) A subscriber shall not permit his line to be used in contravention of paragraph (2).

And from the ICO
https://ico.org.uk/for-organisations/guide-to-pecr/electronic-and-telephone-marketing/electronic-mail-marketing/

In brief…
You must not send marketing emails or texts to individuals without specific consent. There is a limited exception for your own previous customers, often called the ‘soft opt-in’.

The rules on electronic mail marketing are in regulation 22. In short, you must not send electronic mail marketing to individuals, unless:
they have specifically consented to electronic mail from you; or
they are an existing customer who bought (or negotiated to buy) a similar product or service from you in the past, and you gave them a simple way to opt out both when you first collected their details and in every message you have sent

... and so on.

Is your current answer "We are reviewing our processes to ensure we adhere to both the PECR and Data Protection requirements." an admission that Scottish Power currently do not abide by the PECR regulations?

As I said, on 26th May, perhaps it is time for you to pass this up your chain of management if the company wishes to make statements like that.

Regards



7th June
Fermie, Andrew
to me, Andrew
Dear Mr Root

I am writing further to the issue you have raised about PECR and your correspondence with Andrew Healing about it. Thank you for taking the time to raise this with us.

We have been unable to obtain the assurances we would expect in relation to this case. Specifically, we would expect either that the record can be demonstrated to be a corporate record or the gathering of a valid consent can be shown. I am sorry that this has been the case in this instance. We will be taking appropriate action to safeguard against any recurrence.

As well as doing all we can to make sure our practices are compliant with both PECR and the Data Protection Act, I also understand the real annoyance and inconvenience that can arise from unwanted marketing activity. And if we do get it wrong, I am keen to ensure we have the measures in place so that we don’t get it wrong again. If someone indicates they do not wish to receive any more marketing from us, we ensure we have processes in place to that end.

We have confirmed to you that you will not receive any further unwanted marketing from ScottishPower. I can also give you my personal assurance that we take the marketing rules seriously. I have answered the question you asked and, for the avoidance of doubt, this cannot be equated to an assertion that “ScottishPower do not currently abide by the PECR Regulations”. We have contractual arrangements in place designed to help ensure that we do and I cannot go into any more detail than that.

I would appreciate it if you could let me know if there is anything else that I can do.

Regards

Andrew
Iberdrola Group|Global Retail Operations
UK Operations - Data Protection Officer



Steve Root
7 Jun

to Andrew, Andrew
Thanks Andrew,
I've no other questions.

Regards
Steve


[1] I did email the ICO, but they make it clear they won’t investigate every instance of spam and will only start taking an interest if lots of people complain about a specific company.

Hot topic – our backup server (overheating!)

Ahh, computer backups. I’ve said before and learn’t first hand how important they are over the years.

My business backup routine is to copy the files from the server in another building. I used to have a ReadyNAS NV+ (image on wikipedia) and for many years it served us well. Actually, 8 years, which is long enough for any piece of hardware you rely on. Last Christmas (yeah… 6 months working on this between other tasks) I spent some time reading up on hardware and software solutions to replace our ReadyNAS. I did consider getting the latest ReadyNAS or equivalent but in the end decided to build my own solution. An excellent blog post by Brian Moses about FreeNAS set me on the right track and pretty much I followed the suggestions there along with reading the FreeNAS forums. Brian chose the the Silverstone DS380 case and so did I. I was really tempted by the 8 hot swap trays.

I’ll not repeat all the logic of Brian’s post but the one thing that didn’t work for me was the case. The airflow was so poor the disks were overheating. Reading forum posts, it seems the biggest difference between users is how many disks people were using. 4 disks 5 disks with space between each one and things are fine. Using all 8 slots for disks and they all get a touch on the hot side.

2pm Wed 11th May – with Stock Fan

Using the fans that came with the case, all 8 bays populated with disks, and an additional piece of cardboard to help guide the flowing air over the disks (which did help a little) these were the temperatures:

(nb: I wasn’t worried about the CPU temperatures, but I’ll share them anyway)

CPU 0: 47 C
CPU 1: 48 C
CPU 2: 48 C
CPU 3: 50 C
CPU 4: 49 C
CPU 5: 49 C
CPU 6: 48 C
CPU 7: 48 C

ada0 WD-WMC300111734: 37 C
ada1 WD-WMC300109616: 35 C
ada2 WD-WCC4N3KK2FF4: 43 C
ada3 W6A0JJ85 : 42 C
ada4 PK2234P9J6RM5Y : 45 C
ada5 WD-WCC4N4VA3V61: 40 C
ada6 W6A0FZ8V : 36 C
ada7 PK2234P9J95JKY : 43 C

Monday 17th May 17:30 – with PWM Fan

The stock fans were 3 pin but the motherboard supported 4 pin ‘PWM’ fans. This allows it to control the fan speed so if the system heats up it will speed up the fans. I thought maybe all I need to do is change the fans.

CPU 0: 51 C
CPU 1: 51 C
CPU 2: 52 C
CPU 3: 52 C
CPU 4: 51 C
CPU 5: 50 C
CPU 6: 53 C
CPU 7: 53 C

ada0 WD-WMC300111734: 41 C
ada1 WD-WMC300109616: 39 C
ada2 WD-WCC4N3KK2FF4: 47 C
ada3 W6A0JJ85 : 48 C
ada4 PK2234P9J6RM5Y : 52 C
ada5 WD-WCC4N4VA3V61: 44 C
ada6 W6A0FZ8V : 40 C
ada7 PK2234P9J95JKY : 46 C

So, £50 of fans later (I didn’t scrimp) and…. oh, it’s hotter. I guess the orignal fans were always running at full speed.

Wed 1st June, in the Fractal case – with fans that Fractal included.

I settled for buying a new case, a “Fractal Design ARC Midi R2 Black Mid Tower Quiet Performance Case with Side Window USB 3.0 w/o PSU”. The window wasn’t important but that’s what the supplier had in stock. It’s a much bigger case and reading reviews and looking at pictures cooling seemed better and there’s more air gap between each drive. The one thing I’ve given up by choosing this case is having a hot-swap facility for the drives. In truth, I’ve only ever swapped hard drives out about once a year or less so I decided I really don’t need hot swap.

So, what difference did it make?

CPU 0: 30 C
CPU 1: 31 C
CPU 2: 31 C
CPU 3: 31 C
CPU 4: 32 C
CPU 5: 31 C
CPU 6: 32 C
CPU 7: 31 C

ada0 WD-WMC300111734: 28 C
ada1 WD-WMC300109616: 28 C
ada2 WD-WCC4N3KK2FF4: 26 C
ada3 W6A0JJ85 : 26 C
ada4 PK2234P9J6RM5Y : 31 C
ada5 WD-WCC4N4VA3V61: 25 C
ada6 W6A0FZ8V : 27 C
ada7 PK2234P9J95JKY : 30 C

It made a lot of difference!

Why it’s time to move our business away from 2020 Fusion design software

My small business specialises in the design, supply and installation of kitchens, bedrooms and bathrooms. With my penchant for computing it’s no surprise we rely heavily on information technology. However, I don’t write and build all the software we use and that’s especially true when it comes to CAD [Computer Aided Design].

On the surface, CAD is really simple. Draw some lines on the screen. Colour in some spaces, print some pictures and maybe export some things for other software. So far, so good. Of course, the devil as always is in the detail. We need our software to draw the pictures based on products and materials we can buy from our suppliers. We need it to generate accurate plans and we need it to generate respectable pictures so our customers can visualise what they’re going to (hopefully) buy. Being the type of business we are, a lot of what we do includes unique and bespoke configurations. ‘Simple’ things like doors shaped to fit a ceiling and exceptional work like the Star Field ceiling.

From shortly after we began our business almost 20 years ago we’ve been using software called ‘Planit’, which then changed it’s name to became ‘Fusion’, which then changed it’s name to become ’20-20 Fusion’ (as Planit were bought out by another CAD company called 2020). This is an industry specific program. Rather than use a generic CAD program which just creates the graphics and images (maybe AutoCad or Sketchup are good comparisons to this), this program lets us choose and add cabinets quickly to the design and then also shows the price for all the items we’ve added. Being industry specific it also is set up to do things we need quickly. For example add and calculate cornice and pelmet lengths and quantities for a room in a few clicks of the mouse. All of these things can be done in products like sketchup, they just take longer.

The ranges we can add in Fusion represent the real world ranges, with accurate and ‘photo realistic’ representations of real products. Indeed, this is the whole reason for using Fusion – the accurate range of products and the prices. Without that, the product is no better than any other generic design program.

We pay over £1,000 a year to 2020 for ‘maintenance and support’ (not the same as the licence to use the software, which was several thousand pound as a one off charge a long time ago) but we’ve come to some disagreement recently over what that actually means. To me, that means they provide the software and updates to that software over time (eg: making sure it works with Windows 10, adding features to keep up with modern technology) and most importantly they’ll keep up to date with changes the manufacturers make to their products, be that new products, new pricing, new shapes of end panel… and so on. Most of the software updates over the last few years have been minor from our view, we simply don’t need bells and whistles as much as we need accurate 3D objects and prices. It’s the accurate 3D objects and prices that have become a problem.

The 2020 Fusion view of the arrangement is;

I understand your frustration with catalogue updates. However the maintenance & support monthly fees include software updates pertaining to the Fusion software, manufacturer catalogues updates are maintained as per the contract agreement with each manufacturer.
Pasquale Brancati, 2020 Director Customer Support & Retention

I don’t understand how that sits alongside the support renewal letter (2013-14 was the first I found) which says things like ‘Full access to our extensive library of supported manufacturers catalogues’ and ‘maintenance and updates to certain selected catalogues’.

I’m also struggling to reconcile that with their brochure, which says things like ‘Instantly produce customer quotes, with all manufacturer items accurately priced, based upon the features and options chosen.’ (page 2) and “We are continuously working with manufacturers to forge new and lasting partnerships, ensuring that our customers benefit from the widest range of options available, delivered in parallel with the release of their product manuals.” (page 6). Without the manufacturer catalogues, there is no useful program.

My experience is 2020 no longer provide accurate and up to date supplier catalogues or even timely corrections to error reports. A few examples:

  • Crown Kitchens ‘Rococo’ range, released almost a year ago (I have, to hand, a price list from May 2015 that offers this range) yet still not in the Fusion catalogue.
  • PWS end panel with a radius front profile, no graphic despite availability for a long time.
  • Daval Kitchens & Bedrooms… Years out of date, though to be fair, Fusion have *just* begun to provide an almost working kitchen catalogue but I’m pretty sure it’s only because Daval have paid them to do so. It still isn’t complete at the time of writing this

Those 3 suppliers are listed in the Fusion marketing brochure (page 6) and yet clearly Fusion are not updating the data. Why?

Well, I started asking questions (it appears I’m not the only one) and eventually I have a reply. Fusion are trying to charge the manufacturers as well as me for keeping the data up to date. Perhaps they always did and perhaps the manufacturers have decided Fusion want to charge too much. This means the updates aren’t done, this means the software is becoming useless. OK, useless is harsh, it’s becoming expensive and ineffective for it’s original use. It is no longer fit for purpose.

Here are extracts of the Fusion response emails

fusionsupport
24 Jan 2016

Thanks for getting in contact with us.
Yes there has not been a update on the PWS Second nature catalogue for a while, this is due to the manufacturer not providing us with the latest information. I am very certain that there is a update due soon, i will double check with my colleague in our data team and get back to you. I have looked through our outstanding data errors and i can see that you reported a missing item from PWS.

From: fusionsupport
Sent: 09 March 2016 10:37

Hello,
Data can you provide some info on the below concern please, as the customer is quite frustrated.

From: Sacha Smiley 
Sent: March-09-16 9:57 AM

Hi,
Sales are currently in discussion with Crown and PWS about updating the Fusion catalogues.
Gary, can you provide a status on Crown, PWS? Daval we’ve had approval for an update on bedrooms, but were instructed to hold off due to the Insight project.
Thanks

From: fusionsupport

Sent: 11 March 2016 11:12

Subject: RE: Yet more catalogues out of date… is it time we move software?

 
Hello,
I still haven’t received an update on this. We are in the midst of losing another customer. We need answers please.
Thanks

From: Gary Court 

Sent: 11 March 2016 12:03

Subject: RE: Yet more catalogues out of date… is it time we move software?

 
Sacha
I met with Crown at KBB he has asked me to look at outstanding SOW as he feels it is expensive and he is also sending me another update to analyse both sow’s will be completed at the same time
PWS I am in talks to use System Six cat and rename ranges as it is all PWS doors
Gary

So, there we have it. The public line always given to me, over the years, has been ‘The manufacturer isn’t supplying us the data’ but the real story is Fusion won’t do the update until the manufacturer pays. The manufacturers won’t tell us this, because I’m sure they don’t want to upset Fusion. Planit has a lot of businesses like mine using their software so it’s very important for manufacturers to have their products available there. Indeed, I wouldn’t consider replacing Daval, PWS or Crown with another supplier unless their products were available within the design software we use.

I don’t have a problem with manufacturers being charged, but I’m not keen on how it’s been hidden from view and I’m very displeased the current situation is that catalogues are out of date and updates and fixes not being carried out. If only the industry had a more open system and manufacturers could more easily create their own data. We have every supplier send us price lists by email, mostly excel with a few using CSV. I then import and reformat these to suit our needs and our business system. If only they could send us a file for design software in the same way. Maybe, one day, we’ll all be using software like Sketchup and manufacturers will send us catalogues directly. Could you imagine if you could only send a spreadsheet to your customer if you paid Microsoft to create and update it for you?

What to do now?
We’re going to move to another software solution. I’m absolutely certain there will be some annoyances with that software – maybe the pictures aren’t as nice, maybe it doesn’t allow us to import 3D objects we’ve designed in sketchup (for our more bespoke solutions), but the truth is I’d rather have the up to date catalogues. So important is the data to my business, we’ll be spending several thousand pounds a year for the alternative software. The data really is the valuable bit.

My biggest concern remains that the system we move too will at some point in the future also deteriorate into not maintaining manufacturer catalogues. We use different software for bathroom design (better 3D objects, no pricing but that isn’t so important in bathroom design) and they too go through phases of being slow to update some catalogues.

Solicitors who spam? That would be Edwards Duthie Solicitors

Ah, the spam wars. I haven’t posted to this topic for a long time. Not because the spam has stopped but because I’ve had other things to occupy me.

I thought I’d blog this bit of spam though, solicitors firm Edwards Duthie who decided to spam my email with the image below. I emailed them twice to ask where they got my email address from but they didn’t reply. I then thought I’d look for the email address of their data controller but it turns out the information commissioner only publishes a postal address to the public register of data controllers.

On their web site, they describe themselves as “With 13 Partners and some 100 staff operating out of two main offices and three satellite offices, we are one of the largest law firms in the East London/West Essex region.” so perhaps I’m naive to have expected a reply to my emails, or for them to follow the law. Or maybe they know the law has no teeth, so it’s OK to spam because it won’t affect their business negatively as much as they will gain from people that reply.

The email was sent via mailchimp. That means I can unsubscribe knowing mailchimp will block any more, perhaps even drop Edwards Duthie as a customer if they have lots of complaints. It also means it got through my spam filters (mailchimp do a good job of keeping spammers from their network) and there’s every chance wherever Edwards Duthie bought my email address from will continue selling it, I’ll continue getting spam and a couple of times a week I’ll be pulling email I want from my spam folder amongst the thousand or so others from people like Edwards Duthie.

edwardsduthiespamimage

Royalty free music and a time lapse video for work

Here’s a great way to start 2016, Win an award for “Best Domestic Bathroom Installer 2015“.
My brother David entered the Geberit Awards, Geberit being a large multinational manufacturer of bathroom products and out of all the entries from all of the UK, he won. We’re very proud 🙂

That prompted us to finish editing a timelapse video of the winning bathroom. Rather than a silent movie sound I went searching for suitable music to accompany the movie and found the track “Pamgaea” by Kevin McCleod. Best of all, the licence to use this sound track was ‘Royalty Free‘ as well as being free of cost on condition it was clearly attributed to the author. That’s very much like the software code I’ve written and shared, although Kevin is a master of his craft, whereas I’m just an amateur coding for fun.

As well as free when attributed, the music can also be licensed for a fee when an attribution is not possible or wanted. Example: Background music when you’re on hold. In my mind I always thought licensing that type of music was expensive, turns out to be a lot less than I expected.

Migrating from phpBB to Google Groups

For many years I’ve run a tiny web site for the village we live and work in. 8 years ago (or maybe more) I added a forum to the site using phpBB, as they say about themselves ‘THE #1 FREE, OPEN SOURCE BULLETIN BOARD SOFTWARE’.

It’s been very good software, regularly updated and very easy to maintain. However, the most interaction I have with the forum now is blocking spam registrations and migrating it to new servers every couple of years. There are only a couple of posts a year now, so I wanted to find a way of reducing my administration workload.

I decided to migrate it to a “google groups” group. Which is just like a forum with less customisation options. I couldn’t find any guides to migrate away from phpBB so I worked out my own method and here’s how I did it, in case you’re trying to do the same.

Steps in short form:
1) Get data from phpBB database tables as CSV file
2) Write script to process CSV file into multiple emails to the group

1) Get data from phpBB database tables as CSV file
I only needed to migrate each topic and all it’s replies. None of the other database content was important to me.
To do this, I wrote a SQL query:

SELECT po.post_subject, po.post_text, po.post_id, po.topic_id, po.post_time, us.username_clean, top.topic_title, top.topic_time
FROM phpbb_users as us, phpbb_posts as po, phpbb_topics as top
WHERE us.user_id = po.poster_id and po.topic_id = top.topic_id
ORDER BY po.topic_id ASC, post_time ASC

Essentially, this takes selected columns from the tables ‘phpbb_users’, ‘phpbb_posts’ and ‘phpbb_topics’. I’m not sure using ‘WHERE’ is very efficient and perhaps ‘INNER JOIN’/’OUTER JOIN’ would be technically better, but mine was a small database and this was more than fast enough for me (58ms for 114 rows).

Then, I saved the result as a CSV file. Opened it in LibreOffice to check. Several of the fields needed some hand editing, remove first line (headers), replacing some html characters, escaping speech marks, etc. I may have been able to fix those when saving the result of the query as CSV but I didn’t have many to do, so hand fix and move on was fastest.

2) Write script to process CSV file into multiple emails to the group

My script language of choice is ruby. Not because it’s any better than anything else, just what I happen to be using lately. I could have done the same in PHP if I spent a little more time on it.

This is the script:


# I saved file as: process.rb
# to run, "ruby process.rb" ... assuming you have ruby installed ;-)
# I had to install Pony from github, which i did using the specific install gem
# gem install specific_install
# gem specific_install -l https://github.com/benprew/pony
#
# If you're reading this later and forget where it came from,
# https://www.steveroot.co.uk/2015/11/migrating-from-phpbb-to-google-groups/
# Share any tips and fixes in the comments there to help others please!

require 'csv'
require 'date'
require 'Pony'

#initialise the topic counters
#some default text for the first email
#you will need to delete this manually in the google groups!
currenttopic = 0
lasttopic = 0
body = "initialise"
subject = "initialise"

CSV.foreach('phpbb_data.csv') do |row|

#get current topic
currenttopic = row[3]

if currenttopic == lasttopic
#This is a reply to the topic, add to the existing body
body = body+""+"n"
body = body+"-----------------------------------------------------"+"n"
body = body+"reply_by_username: "+row[5]+"n"
body = body+"reply_date: "+DateTime.strptime(row[7],'%s').strftime("%d/%^b/%Y")+"n"
body = body+""+"n"
body = body+row[1]+"n"
else
#This is a new topic. SEND the last group of messages
Pony.mail({
:to => [email protected]',
:from => 'YOUR-EMAIL-ADDRESS',
:subject => subject,
:via => :smtp,
:body => body,
:via_options => {
:address => 'smtp.gmail.com',
:port => '587',
:enable_starttls_auto => true,
:user_name => 'YOUR-EMAIL-ADDRESS',
:password => 'YOUR-PASSWORD',
:authentication => :plain, # :plain, :login, :cram_md5, no auth by default
:domain => "YOUR-SENDING-DOMAIN" # the HELO domain provided by the client to the server
}
})

#A message to terminal on every send, nice to know that something is happening!
puts "Sent "+subject

#Reset the body (subject is set only once, no need to clear)
body = ""
#Set subject, create standard header text and set subject for email.

#Set the subject as the topic name
subject = row[6]

#Put some generic header text in place
body = body+"-----------------------------------------------------"+"n"
body = body+"This post was transfered to the google group when the phpbb based forum was shutdown"+"n"
body = body+"You might find relevant information at YOUR-DOMAIN"+"n"
body = body+"This entry includes all replies to the original topic"+"n"
body = body+"-----------------------------------------------------"+"n"
body = body+""+"n"

body = body+"Topic: "+row[6]+"n"

body = body+"created_by_username: "+row[5]+"n"
body = body+"topic_date: "+DateTime.strptime(row[7],'%s').strftime("%d/%^b/%Y")+"n"
body = body+""+"n"
body = body+row[1]+"n"
end
#set the value of last topic ready for the next loop.
lasttopic = currenttopic

end

# These are the fields in order in the CSV. Here for easy reference whilst I coded
# numbers start from zero (so post_subject = row[0])
# "post_subject", "post_text", "post_id", "topic_id", "post_time", "username_clean", "topic_title", "topic_time"

Being very lazy, I didn’t write the code to understand the first pass should *NOT* be emailed to the group, so the first email to the group titled ‘initialise’ will need to be deleted manually.

You will need to enter your own values for: Forum name, your email address, your sending domain. You’ll need a password, but be aware that if you use 2 factor authentication you’ll need to get an app specific password from your apps account.

You will want to customise the text that is added to every email, perhaps correct the spelling of ‘transfered’ too 😉

The script isn’t particularly fast as it connects and sends each email individually. We use google apps and as there weren’t many topics to send it was well within my daily limit of gmail usage. However, if it was higher then I could have sent them directly via smtp. There are instructions for using different email methods on the ‘Pony’ github pages. The other problem I had was errors in the CSV causing the script to stop. For example some replies had no topic name and that made the script error when it encountered them. For me, I had fixed the CSV, deleted the posts already made to the forum, and run the whole script again. For others, you might like to set up a dummy group to send your messages too first to make sure everything works, then delete the dummy group and re-run the script to send messages to the new group.

To test the email messages, I suggest you take a few rows of your CSV file and send them to your own email to check formatting and content.

If you’re wondering what my results looked like, here’s one of the topics with a reply once posted to the google group

Birthday Calculator – in case you don't want to wait a whole year to celebrate being alive

We have a tradition where I live. We celebrate being alive with a party and that party generally coincides with being alive for another 31,557,600 seconds.  31,557,600 seconds happens to be just about equal to a solar year, which is a happy co-incidence as it’s not so easy to remember otherwise.

I decided I could really do with a good excuse to party before that arbitrary unit of time though.  The solution? Write a web application where I can put in my date of birth and it will tell me other dates that I can celebrate on.

Try it for yourself at http://birthday.sroot.eu and it will tell you amazing things like;

  • How old you would be if you were born on Mercury, Venus, Mars and the other planets in our solar system
  • When your next MegaSecond birthday is (so you can have a party when you survive another 1 million seconds of existence)
  • Or for a really big bash, celebrate the very infrequent in our lifetime GigaSecond birthdays.

If you’d like me to add another arbitrary repeating unit of time post a comment.