Diamond Digital Marketing Group

Tag: Pivot Table

  • What is a Pivot Table

    Introduction

    Pivot Table is a feature which you can usually see in the Google Spreadsheet and Microsoft Excel. While alots of people can address it existence, seldom people can explain what it is and what it’s difference between the normal table. In this article, we are going to break it down and elaborate it in detail with illustration.

    Definition

    Pivot Table

    It is a good to start explaining what is Pivot Table by a normal Table.

    MonthSales Turnover (USD$)Salesperson
    Jan5,000Anna
    Feb2,000Anna
    Feb3,000Angela
    Mar1,000Ann
    Sales Turnover Normal (Salesperson , Month)

    You will not feel strange about the tabular data pattern which a normal table brings to you. In this Normal Sales Turnover Table, you can easily get 5 piece of information because there 5 records inside the table.

    The information, i.e. the 5 records, are in atomic level which means that they are already deintegrated into smallest granularity that if you further break down the record, say breaking down from information per Record to information per Cell, you no longer can get the useful information because it will be meaningless if you only know the value “Anna” without knowing what Month and how much Sales Turnover she get. In summary, a Record is the atomic level of a normal Table.

    This “Tree View” of a normal Table is good for recording due to the fact that each record can be input separately without affecting the meaning and usefulness of each other. For example, it is totally fine that there is missing information of March Sales Turnover from Joan if you are only interested in the February result of Anna.

    However, it is not good for “Forest View” when you want to see the consolidated result of the Sales Turnover Report. How about if you want to know the Total Sales Turnover Amount from Anna? Of course you can do the calculation by yourself each time you want to know the Total amount. But what if there are 10 million records?

    Besides, the normal Table only tells you what you can see , it does not tell you what you cannot see. For example, what if I want to know the Sales Turnover of Joan in Jan ? “No Value (or Null)” is also a valuable information that you want to know.

    Now, let’s see how we turn the Normal Table into a Pivot Table.

    JanFebMarTotal by Salesperson
    Angela3,0003,000
    Ann1,0001,000
    Anna5,0002,0007,000
    Total by Month5,0005,0001,000
    Sales Turnover Pivot Table (Salesperson , Month)

    In the Pivot Table , now not only you can see the consolidated total amount in terms of Month or Salesperson, but also can easily address the Cell with missing value.

    It’s glad to know, but still not impressive enough.

    Because in the previous example, it we only use 2 parameters (i.e. Month and Salesperson) to co-ordinate a value (i.e. Sales Turnover Amount). How about if you want to add an additional parameter , for example, Product ?

    MonthSales Turnover (USD$)SalespersonProduct
    Jan5,000AnnaApple
    Feb2,000AnnaBanana
    Feb3,000AngelaCherry
    Mar1,000AnnDragonfruit
    Sales Turnover Normal Table (Salesperson , Product , Month)

    While there is no big different in terms of the look and feel of the table except the additional 1 column added in the very right of the Normal Table, you can compare it with the new Pivot Table which added the Product parameter in below:

    SalespersonProductJanFebMar
    AngelaCherry3,000
    AnnDragonfruit1,000
    AnnaApple5,000
    Banana2,000
    Sales Turnover Pivot Table (Salesperson , Product , Month) , pivot by Salesperson

    Same as the Normal Table, an additional column Product is added immediately after the column Salesperson. And most important is that the Value Anna is shown only 1 time instead of 2 times (in Normal Table) due to the fact that the Pivot Table helps to “consolidate” the data to make it minimal.  

    While in the previous Pivot Table example there is the Total Amount , I skipped this information because it is not the focus on the essense of the Pivot Table. Feel free add the Total Amount any time you want to create a Pivot Table via using Google Spreadsheet ,Microsoft Excel or any Tabular tools.

    Now by referring to this Pivot Table , you can answer the question from your boss like:

    What is the performance of each Salesperson so far ?

    You can sense that the focus is on the Salesperson which the boss probably wants to decide the quarterly bonus based on the performance of each Salesperson.

    How about if the boss wants to see the profitability of his/her business? He may probably ask:

    What is the performance of each month since the beginning of the year?

    AppleBananaCherryDragonfruit
    MonthAnnaAnnaAngelaAnn
    Jan5,000
    Feb2,0003,000
    Mar1,000
    Sales Turnover Pivot Table (Salesperson , Product , Month) , pivot by Month

    Due to the fact that the focus of the question shifted from Salesperson to Month, we then put the parameter Month as the name of the row to present as the focus of the table. We name this action as pivot by Month. Compared with the previous Pivot Table which is pivoted by Salesperson, you can find that the name of the Salesperson Anna shows twice this time. Because as we want to consolidate the data of Month (i.e. pivot by Month), we can only sacrifice the neatness of the Salesperson.

    The term “Pivot” means whenever you want to rotate any one of the axises from 3 axises (for example), you need to use 1 axis to rely on. The axis you relied on is called the Pivot, while the axis is referring to the columns (or parameters, or dimensions , whatever you name it) in a normal Table. While in the physical world there can only be a maximum of 3 axises , you can add as infinity number of axises as you wish in logical world.

    Use Case of Pivot Table

    NOT for Recording

    Pivot Table is good for presentation and bad for atomic level recording. If you start recording by applying the data schema of the Pivot Table, you will realise that if you try to insert the value of the table like what “Sales Turnover Pivot Table (Salesperson , Product , Month) , pivot by Salesperson” Table did previously, eventually you will have some missing value in the cell of the row’s name. Besides, the Pivot Table can never serve the function of data processing in turns of filtering and sorting.

    Even worse, the client , including your boss, would like to derive different views for different scenarios in different periods of time. If you directly record the data in Pivot Table format, you will soon realise that whenever the client wants to change the pivot of the Pivot Table, say based on Month instead of based on Product, your previous effort on typing in the value , will be in vain. Therefore:

    Use Normal Table format to record

    Use Pivot Table format to present

  • Build a Business Process Management System – BFs-WAITER Pivot Table

    Build a Business Process Management System – BFs-WAITER Pivot Table

    Introduction

    Recap from the article Build a Business Process Management System – Stage of System Building that the MSC Matrix (Modelling – Standardisation – Coding) describing different stages of building a system. In this article, we dive into how we observe the real world to do the Modelling by using the BFs–WAITER Pivot Table.

    Definition

    What is Pivot Table

    The “pivot” part of the Pivot Table comes from the way you can “pivot” or rotate your data to view it from different angles. It’s like being able to look at the same set of numbers from multiple direction. Please find comprehensive explanation in the article What is a Pivot Table.

    What is BFs-WAITER Pivot Table

    BFs-WAITER is a DDM Terminology which is the acronym of all the dimensions when describing a Business Process Management System:

    Business Rules

    Business Rules are the if-then-else conditional logic which the user of the system , or the system itself should comply with.

    For example, when you insert an email address in the Email Address Field of an Contact Us Form, the system will show an alert of “please insert an valid email address” if the “@” character is absent inside the email address. This is an Email Validation Rule that the web application developer will usually use.

    Besides the rules in programming, we can also find other rules in the business world. For example, you want to adapt a bundle discount in your business that if the clients buy both 1 shoe and 1 skirt in 1 order, they will enjoy a 10% discount. This kind of if-then-else condition logic can be named as Discount Rules, which is classified as one of the Business Rules. While you can and should customise the own business rules specific for your own business, there are some common Business Rules that we should cater in most frequently:

    1. Shipping Rules
    2. Discount Rules
    3. Coupon Entitlement and Redeem Rules
    4. Email Validation Rules
    5. Password Validation Rules
    6. Return and Refund Rules
    7. Pricing Rules
    8. Traffic Routing Rules

    Fund Flow (or Cash Flow)

    To be precise , the term Cash not only refers to the currency note or the debit balance in your bank account, but also the credit balance or any payment token. As there are too many ways to represent a payment, we name it all under the term Fund.

    Infact , Fund (or Cash) Flow, Information Flow, Stock Flow and Work Flow are 4 flows which we mentioned very often when describing a business process. Without surprise, Cash Flow is not going to be absent in our model.

    Stock (or Service) Flow

    The lowercase “s” inside the acronym “BFs-WAITER” means it is optional. Whether you want to record the Stock or Service Flow depends on if you should do stock (or service) taking on the product or service you sell.

    Work Flow

    Quoted from Wiki the definition of Time:

    Time is the continued sequence of existence and events that occurs in an apparently irreversible succession from the past, through the present, and into the future.

    While I am not going to (and not capable of) triggering the debate on what Time really is, referring to wiki, time is a continued sequence of Events.

    In a Business Process Management System, there is no better way to describe the Business Process than in the aspect of Time. While time is best to be presented in terms of Events, Events can be regarded as Steps or Procedures inside a Business. For example, in a Self-serve Cashier Register Checkout System :

    1. Client press the Start Button
    2. Client scan the Barcode over the package of the goods
    3. Product Quantity and Price shown in the Screen
    4. Client press Checkout Button
    5. Client pick a Payment Method and Pay
    6. Client get the Receipt printed by the Cashier Register Machine.

    Above is a typical but straight forward example on describe a Business Process. In most of the time ,Events are far more complicated than we can imagine. Allow me to introduce some concepts that you should master before you move forward:

    Backbone Events

    Back to our previous Cash Register Checkout System example, allow me to detail it as below:

    1. Client press the Start Button
    2. Client scan the Barcode over the package of the goods
      • If the Client want to remove the items, then ….
    3. Product Quantity and Price shown in the Screen
      • If the Client buy more than one item of the same SKU, then ….
    4. Client press Checkout Button
    5. Client pick a Payment Method and Pay
      • If choosing Apple Pay, then ….
      • If choosing Credit Card, then ….
        • If the Credit Card failed to process the transaction , then….
    6. Client get the Receipt printed by the Cashier Register Machine.
      • If the Cashier Register Machine is out of receipt printing paper, then….

    You can imagine in reality there are tons of if-then-else branches which can be happening in any of the steps. In some cases you cannot even imagine this scenario will happen until it really happens in front of you in real life. For better communication and project management purposes, we should in first priority identify the shortest path from start to the destination of the business process and skip all the possible if-then-else branches. (i.e. Clients get the receipt in the above example) We called this shortest path Backbone Events.

    Scenario

    Scenario means one of paths within all possible combinations of outcome which can but may or may not happenⓘ. To make it simple, let’s illustrate with an example:

    1. Finished input the Product Quantity > Choose Apple Pay > Transaction Success
    2. Finished input the Product Quantity > Choose Apple Pay > Transaction Failed
    3. Finished input the Product Quantity > Choose Credit Pay > Transaction Success
    4. Finished input the Product Quantity > Choose Credit Pay > Transaction Failed

    In the example above, while there are 3 steps , which :

    1. First Step have 1 option value
    2. 2nd Step have 2 option values
    3. 3rd Step have 2 option values

    To do the maths, there will be 1 x 2 x 2 = 4 possbile outcomes, and we call each of the outcome a Scenario.

    Imagine if there are 10 steps and each step has its own 2 option values, there will be 210 , which is equal to 1,024 Scenario. Addressing all these scenarios is already a huge challenge , not to mention catering them one by one.

    Recall the article Build a Business Process Management System – System Index Concepts, A good System caters all the scenarios after interacting with the environment, which is exactly what we have mentioned in this paragraph.

    Authority

    When you login an application, you are authorized to login after you insert a valid password. When you want to open the door of your home, you are authorized to do so only if you a have valid key. The password and key in these examples, are the Authority that we should have to carry out the action.

    The Authority is usally presented in following ways:

    Role Base Access Control (RBAC)

    It literally means that the Access Control is based on the Role of the user. For example, Only the Role of Admin in the website can install a plugin, while the Role of Guest User cannot.

    Session (Time) Base Authority

    For example, you rent a meeting room in a shared office for 2 hours. That means you are only allowed to access the meeting room within these 2 hours. After this 2 hours (i.e. Session), you are not authorized to access the room.

    Authorization Token

    In ancient times when there was no Facebook or Instagram, it was extremely hard for everyone in the country to know (in terms of the real face) who you really are. The only way to justify that you are the king of the country is to show some kind of Totem, like a imperial jade seal, which is the emblem of the authority for the local officer of the country to recognize the king. 

    Imperial Jade Seal as a Authorization Token
    Imperial Jade Seal as a Authorization Token in Ancient China

    Authorization Token is the same logic as the Totem in the ancient world. No matter who possesses at that moment, he/she is authorised. In morden world, whoever get the remote control (i.e. the Totem) of the TV set in the living room, who is the king/queen.

    In the computer world, we usually apply Authorization Token during the communication between system and system (i.e. not between system and human). For example, if you want to let your WordPress website to fetch the Instagram Feed directly from your Instagram Business Account, you should generate a Access Token (i.e. Authorization Token) from Instagram Business Account and pass it to the WordPress Website.

    While there is a rare case the Authorization Token is presented in a physical form, most of the time it is in fact nothing more than a meaningless cluster of strings that you keep in a secret computer file.

    Information (Document) Flow

    Recall the article Relationship between Human Learning and Data Structure , Data is the signifier (i.e the pointer) of the signified (i.e underlying objects).

    For example, the Sales Invoice is the signifier of the happened event of the delivered material products (i.e. the signified) , while the Payment Receipts is the signifier of the Cash material currency note (i.e. signified) paid physically (i.e. pay offline) to settle the Sales Invoice.

    By simply operating the Data , rather than operating the physical underlying objects, we can speed up the analysis of Business Process a lot.

    When a large amount of pieces of related Data bonded together bringing the user a meaningful value, they become Information.

    When we describe the Business Process in terms of information, we call it Information Flow.

    While most of the time the information will be presented in or delivered via a Document as a media of convey, we therefore use the terms Information Flow or Document Flow interchangeable.

    Whenever you master the information flow, you can get use of them by facilitating your decision making process in a timely and accurate manner.

    Tools

    A computer , a laptop, a Cashier Register Machine, a Camera, a PDF Converter Online Website, a Credit Card , can all be regarded as Tools, whatever you name it.

    Entity

    In legal terms, a legal entity refers to any organisation or individual with legal rights and obligations under the law. Such entities can enter into contracts, initiate or face lawsuits, and own assets in their own name.

    We pick the term Entity (instead of the term Person) because it may have both individual person and organisation , or somtimes even a system or an algo (how complicated this world become!) involved in the Business Process.

    On top of that, while in rare cases we can specifically address a particular person in a Business Process (for example, Anna, Ann, Joan , etc) , very often we can only put the Entity Type (instead of the Entity Instance) inside the Business Process. For example, Client, Admin Manager and User are the Entity Types we will describe in the Business Process due to the fact that we don’t know in advance which name client really is in the planning phrase.

    Risk (Problem Pattern)

    While the concept Risk is easy to understand, the term Problem Pattern is used in Diamond Digital Marketing Group very often, which is indifferent to Risk.

    In order to manage the Risk, we cater the following aspects:

    Identity the Risk (i.e. Frequency of the Risk)

    Some risks are easy to address. For example, human error on data entry. Nevertheless, some risks are invisible and can hardly be addressed, or even hardly be classified as risks. For example, when your salary is USD$50,000 per month , you are unlikely to realise that your ability is in fact worth USD$120,000 , which means you are risking USD$70,000 per month and you have no idea at all because you have already satisifed when you reached USD$50,000 per month.

    Probability of Risk

    Even if there is a chance that your business may suffer from alien-attack to the planet, you are more likely to cater to the risk of a fire accident compared with the alien-attack scenario. The chance (i.e. probability) that the risk happens will highly affect the priority that we handle the risks.

    Magnitude of Risk

    While you can foresee there is a high chance that the plastic bags which can be freely grabbed in the cashier by the client will be abused by the clients, you may probably not to cater the risk, at least not in a higher priority than catering the risk of the Google Ads campaign which brings the sales turnover to your business being restricted.

    Radius of Risk

    In hollywood movies, whenever there is a public health affairs which the zombie virus is going to spread out from the lab, the supervisor of the hero will make the hard decision to quarantine the people to the lab in order to control the radius of the virus spreading distance, in sacrifice of the survival chance of the people who are stuck in the quarantine zone. At this moment, the hero will stand up and rescue the innocent.

    Quantifying the Risk

    The first step of automating anything is by quantifying it. Quantifying the Risk is a combination of the Frequency, Probability, Magnitude and Radius of the Risks. The arithmetic operations will be explained in future articles.

    Putting BFs-WAITER into Pivot Table

    You can visualize that all the 9 Components (i.e. B/F/s/W/A/I/T/E/R) are in fact the 9 dimensions in the Pviot Table.

    It also implies that whenever you want to mention a single Step (or Event), you should at the same time mention the other 8 dimensions in order to coordinate that single Step (or Event). While it is really sophisticated for human beings to comprehend all the data points by a normal Table, a Pivot Table is a good presentation which visualises the relationships among all dimensions in every Data Point (i.e. a Cell in a spreadsheet). This significantly assists the communication among the stakeholders including client , programmor, system engineer, web developer , salesperson , system users and a lot more.

    Separate between modelling and standardization

    In BFs-WAITER, while Modelling means to enumerate all the option values of all the components , says all option values of Documents and Entity Type, Standardization means how to analyse the relationships among each component and interconnect them.  

    Modelling and Standardization are 2 different processes which will be carried out by 2 different roles in different time. While Salesperson or Consultant are more prone to listen to the clients in a sales meeting in order to enumerate the system requirements , documentations or stakeholders (i.e. Entity), System Engineer will on the other hand focus on logically connecting them in order for a meaningful and logical relationship.

    While it is much easy for the Salesperson to adapt a normal Table to records all the option values of all the components during a consultation meeting, a Pivot Table is a much more helpful tool for a System Engineer to connect the relationships and switch it among different presentation views (i.e. Pivot by different dimensions) within a second.

    In Diamond Digital Marketing Group, we will apply the BFs-WAITER Pivot Table as the main tool for Business Process Management System development. While this article is mainly focusing on the Meta System level in System Index , and the Standardization in MSC Matrix, any hands-on operation will be left for future chapters.

Diamond Digital Marketing Group