International business intelligence and consulting company entered Russian market with a new offer for automotive manufacturers. Within short timeframes company needed to collect marketing data which included customer and dealer incentives.

Company had offices around the globe and had efficient processes of collecting, analyzing and data interpretation but every country had its own features which demanded customization of international procedures. After finishing training on company’s systems and instruments I started the process of mining data and constant consultations with European office about methods of coding the data.

Most of the information was gathered from official websites by continual monitoring of webpages related to incentives, including landing pages and media articles. I had to spend much time monitoring websites of 30 major brands on Russian market. Also, I had to keep an eye on financial incentives of 11 banks which offered auto loan financing.

On the next step it was obvious that I had to implement an instrument that automated that iterative process and after analyzing current offers we decided to use Website-Watcher which was installed on local machines and used scripts and macros to visit certain pages and collected changes on them. Then all those changes were analyzed by me, I interpreted them and coded them in company’s systems.

After inserting all links, storing macros and filters for all pages I managed to reduce daily time consumption from 2-4 hours (depending on amount of changes) to just 15 minutes. Thus, I could get rid of routine, increase quality of websites monitoring and pay more attention to data analysis and quality assurance.

The critical factor for proper data analysis is data consistency. Besides technical requirements towards data model, there was a demand for high quality of data itself, namely prevent mistakes in format, spelling and actual data.

I was one of the first users and engineers of Russian automotive marketing database in the company, I paid much attention to creation of templates for populating data in order to simplify the procedure data analysis in future. The particular feature of populating data in company’s system was multiple duplication of input data in different fields made by hand. It was obvious that without a template much time would be spent on data purification which would be more time consuming with the team growth.

Russian team wasn’t big at the moment and support by European office was limited and I undertook the process of creating the template. I had to find simple, universal and reliable method of generating template information basing on data input. For example, due to clients' requests and system’s limitations, analysts had to duplicate info about financial incentives in different types up to 4 times.

I paid attention on excellent instrument which was installed on every computer in the company — Microsoft Word and its functionality such as fields and content protection. For example, when populating financial incentives data, I could enter interest rate, deposit and terms only once and then could easily copy the string for further inserting into the system. Information in the template was created with close collaboration with European office and with company’s customers.

Thus, I could achieve data unification, drastic increase of data populating speed and get rid of mistakes which occured while manual printing as they negatively affected the attitude of analysts and clients.

As data analyst I had to generate reports from databases, created by Russian office. Because of large amount of data I had to write effective queries, which didn’t overload company’s servers.

Also, I created reports using LEFT JOIN which were then used for analysis, visualization and were provided to customers. Thanks to consistent data and correct data model, I could use reports with dozens of thousands of rows and dozens of columns for accurate analytical reports which were highly valued among automotive manufacturers. Clients created marketing campaigns using data and analytical reports of our company.

Moreover, in queries I actively used aggregation, sorting and grouping in order to work with relevant information and generate timely reports. I calculated values with direct queries to database using functions AVG, MIN, MAX, ROUND, COUNT and DISTINCT as well as WITH clauses. Part of information was analyzed directly from database, part was modeled in Excel — method was chosen basing on amount of data and its features.

Company paid much attention to knowledge portal where technical writers uploaded information about updates of internal systems, provided several instructions to tailor-made programs. But due to limited resourses they couldn’t take into consideration features of all countries, that’s why I took the responsibility to document all procedures used by the Russian team.

It turned out that thus I didn’t only help new members of the team, but also drastically increased my knowledge and skills. Firstly, I structured my experience in the document and started to see flaws of my own work. Secondly, I learned faster and better when transfered knowledge to others.

Moreover, I increased quality of work as I received an opportunity to show my groundwork to the management and obtain the feedback. Thus I could find mistakes in my work before clients of the company found them which positively affected the quality of services and data.

In addition, I found that there were particular characteristics in my work which required reporting to various stakeholders including myself. Uniformity of data and excessive iterativeness could not be memorized. At the same time analyst was obliged to report about adjustments to the database. Internal system tracked these changes with insufficient details and didn’t help to rapidly answer the question of key account managers.

I initiated the process of creation of internal report which I populated during my free time. This report was made in Word with tables-calendars where I stored my workflows. Also, in a while I started to add there other important information for analysts. Notwithstanding the volume of the document with more than 70 pages, it was easy to use it as I added index and hyperlinks in the file and it helped me out many times.

Thanks to this initiative the speed of response increased by several times, labor intensity decreased. Number of false answers or inability to answer cases were cut to zero.

One of the most difficult and boring tasks I was assigned to do was quality assurance of data. When I came to the company this work was done by the most experienced specialists in Italy and England. They manually checked the reports before sending them to the customers, trying to find errors and mistakes as well as flaws in calculations and logic. When they found them, they reported to analysts to make corrections in the database.

After training and fulfilling several audits of data, I found several trends and algorithms which in my opinion could be better done by macros. Due to insufficient support by European office I decided to embody several ideas in macros for our reports in Excel using VBA (Visual Basic for Applications).

Firstly, I made a design of required functionality for checking and comparing values painting the relations above current report scheme.

Then I wrote a business requirements document for internal use in order to store all functionality I expected from macros version 1.0 and the results it would produce.

It became obvious that macros should check if values in different columns match other values in a specific way. IF they matched, then fill the row with OK value, ELSE — fill with error.

In order to choose desired values from the text fields I used function InStr. Thanks to the fact that information was stored in the database using specific templates, data was consistent and it was not difficult to write effective macros. Macros parsed values from different columns, checked and matched them and added the result into the report which we could easily filter. Filtered rows pointed to mistakes made in the database.

Owing to implementation of macros version 1.0 in my work I could massively decrease timeframes for quality assurance. Instead of dozens of working hours of checking dozens of thousands of cells, macros created the result in just 1 second while working on local computer. Then I analyzed the filtered information, reported to analysts and investigated the roots of the mistakes.

Work with stored data implied visualization of reports in one of the most convenient and powerful tools — Pivot Charts which is part of Excel module Power Pivot. It allowed us to build many diagrams basing on big data, present it in different ways and use advanced filters and slicers.

I completed the curriculum of Analyzing and Visualizing Data with Excel program by Microsoft where I was trained to use Excel and Pivot Tables for analytical reports.

Apart of visualization I learned DAX language which helped me to work with Pivot tables and helped me to work with big data faster and count values in thousands of database rows.