Episode 550: Automation for the Task of Spreadsheet Filling in

The Task: Weekly sales report

I have each store manager report sales of the week.

It sure is good to operate a simple "Sales report Process", which is

1. Each and every "Store manager" makes reporting
2. "Director" at the headquarter confirms

However, those store managers seem to be working only "watching their boss".

I suppose it could be better, for example, to produce "more chances to access data of other store".


If you are using G suite, "Google SpreadSheet" is available to edit the same document by all store managers (* up to 50 people). And it will reduce labor for "summing up" of "Headquarter director". Regarding "mistakes on input", store manager who made mistake could aware of it by him or herself for comparing with data of other stores. Or, maybe managers would mention about "input mistakes" each other! (It may lead to activate communications between stores, and that leads improving productivity...)

Challenge: Preparation of the new SpreadSheet is troublesome

However, then, someone is needed to prepare "Spreadsheets for reporting" every week.

It would be too much for "directors" in headquarter to ask to do. They are hard on others, and easy on themselves. Like, they never have tried to be in time for deadline even for claiming out-of-pocket expense. Hmm, the biggest challenge seems to be the first Step at which "to prepare a new SpreadSheet and announce it to every store managers every week precisely".

[Weekly Sales Report]

Episode 549: Let Information Disclosure be Automatic!

How to announce personnel information

The task of "announcing information on personnel changes (change of assignment or position) to in-house" is tricky.

There are various types, such as promotion / relegation / recruitment / retirement / leave of absence / department transfer / seconded ... and also individual circumstances differ from each other.

As a feeling of a HR staff, he or she may want to let everybody know about it a few months in advance, if was a case where, for example, "a happy retirement of an employee who has been trustworthy". Whereas, in the case where "being headhunted by a competitor", HR may want to keep that secret. Also in the case of "taking a leave for family care", there are people who want to positively let it known to their colleagues or related people in advance (in consideration of breach of confidentiality obligations), while some other people may want remain in silent.

Basically, we should solemnly switch the "information that is secret within the HR department" to "information publicly known inside the company", according to the "prescribed disclosing rules". (Personnel notice)

Challenges on practical operation

In Japan, many companies adopt "in-house posting" as a publication method.

However, with the way of printing on paper and stick it to "bulletin board" or "wall", there is only few chances of looking at if, for employees who go out often, who are on long leave or teleworkers. On the other hand for staffs of HR, the tasks of "posting the notice at a fixed timing" or "stopping the posting at the scheduled timing" is unexpectedly a big burden.

Whereas, other companies adopt the way of "announcing verbally at morning briefing". However, also it must be said that it is difficult for people who are on long leave and teleworkers to obtain the same amount of information as attendance at the morning briefing. Moreover, there are also risks that 'date of change' or 'change department' etc. are not accurately conveyed because of being verbal.

[Personnel Change Information Publication]

Episode 548: Analytics API Request In Monday Morning (3)

Analytics tells 'particular building company is in trouble'?

In the last article and the one before the last, I introduced you (nearly unmanned) Workflows that informs "trends of web access of the last week" to in-house.

Once this business process is in operation, employees will be able to check the latest information obtained through the "Google Analytics Reporting API" every Monday morning by e-mail. As the result, daily work for "Customer support" or "Sales" will be more efficient.

However, it might be better if "Information that is not in Analytics" is written as well. That is, it may be possible to gain deeper understanding and insight on trend information such as "links that a lot of inflows" and "Pages that referred by specific customers", if it also has included information which is not in Analytics such as;

• 'delivered a press release on last Tuesday'
• 'held a user seminar on last Thursday (in which the particular building company participated!)'.

API acquisition of calendar information as well

The following Business Process Definition is a mechanism in which information of the events of last week, that have been written in the in-house calendar for "Public relations schedule and exhibition schedule" (Google Calendar), is added to the email notification.

When operating this Workflow, employees who would receive notification emails will be able to see "related information that may have affected Web access" at the same time.

[Website Operation Report 3]

Episode 547: Analytics API Request In Monday Morning (2)

Ranking on popular pages

In the last article, I introduced you a way of automatic generation of "weekly reports" by automated communication with "Google Analytics Reporting API".

The point was the automated Step (Service Task Addon) which automatically retrieves data aggregated in the following rule.

• Dimension： ga:hostname, ga:pagePath, ga:pageTitle
• Metrics： ga:pageviews, ga:sessions

In this Step, "List of web pages that got a lot of access" is gathered into a multi-line text. If you set to "filter" something like "ga:pagePath=~/blog/" you can also automatically get the ranking of "web pages under blog folders".

Notification email that is embedded these texts would be very useful information for Marketing team.

Tens of thousands of other patterns of aggregation method

But now, I would like to aggregate data of Google Analytics in more different perspectives.

While in the previous example, ranking was retrieved with the above "three dimensions" and "two metrics", Google Analytics has about 260 dimensions and about 230 metrics. In other words, a wide variety of data aggregation is available, by changing its combination.

For example, when you aggregate not only on the aspect of site contents ("Behavior (BEHAVIOR)") but also "AUDIENCE" or "ACQUISITION", you should be able to extract information such as "What kind of people are accessing?" or "What kind of site has it been derived from?".

Reference: Dimensions & Metrics Explorer

[Website Operation Report 2]