Reporting and SQL Queries in Layout-8000
|
|
SQLAdBoss™ simplifies the process of exporting data from Layout-8000™ and storing it in an SQL database for reporting, analysis or extrapolation. This database allows you to gather Layout-8000 planning metrics and provides a means to generate reports tailored to fit specialized business needs.
HOW DOES IT WORK?
|
|
With SQLAdBoss, report queries can be written using industry-standard SQL query language and ODBC technology. Query its SQL database using Microsoft® Excel®, Crystal Reports®, Google Data Studio, Zoho® or any of the many applications which support ODBC or the SQL query language.
SQLAdBoss can execute scripted queries providing Business Intelligence (BI) reporting to "C" level executives and senior management in the advertising, marketing and production departments.
SQLAdBoss is separate from Layout-8000. There are no visible changes in production design activities nor are there additional steps for the Layout-8000 user. Layout-8000 users may ignore this subsystem entirely.
For most Layout-8000 sites, SQLAdBoss databases can be initialized with years worth of existing Layout-8000 output data. You can get an historical perspective on your products shortly after initial installation.
SQLAdBoss can execute scripted queries providing Business Intelligence (BI) reporting to "C" level executives and senior management in the advertising, marketing and production departments.
SQLAdBoss is separate from Layout-8000. There are no visible changes in production design activities nor are there additional steps for the Layout-8000 user. Layout-8000 users may ignore this subsystem entirely.
For most Layout-8000 sites, SQLAdBoss databases can be initialized with years worth of existing Layout-8000 output data. You can get an historical perspective on your products shortly after initial installation.
USES
Unlike the single-product focus of Layout-8000's many standard reports, SQLAdBoss presents aggregated data from multiple products. You can combine data from products for a date range for a single business unit or combine data from multiple business units. SQLAdBoss scales from individual newspapers to newspaper enterprises, especially those with newspaper design centers and/or business units with disparate front-ends. And, yes, you can aggregate data from all products and all your corporate business units into a single SQLAdBoss database.
Layout-8000 offers a variety of standard tools that report on a single publication, answering questions about which ads wound up on which pages, what the ads-to-editorial ratio is in the B Section, where color ads are placed, etc. These out-of-the-box reports can be printed, e-mailed or exported to a spreadsheet program.
This information is valuable to many departments, from finance to production. Stakeholders from these areas often have questions that can be answered by data from Layout-8000.
But SQLAdBoss is more than this.
It is an ideal tool for those looking for business metrics, key performance indicators (KPI) and trends in big data involving product design.
For example, what happens when a manager from outside of the Layout department wants to compare this year's average page counts to last year's and the year before? The standard solution was always to create a one-off custom report or Excel spreadsheet, but this reactive model of operation only answers the question it's being asked. Wouldn't it be more efficient to be proactive? To be ready to answer questions that haven't been asked yet?
SQLAdBoss helps to facilitate the transition to metrics-based decision making. It provides answers to the questions that are being asked today and is ready to answer questions that will be asked in the future. It allows publishers to easily compare user productivity, do month-over-month or year-over-year comparisons of page counts, ad counts, color details, filler or house ad usage, etc., and it also permits consolidated reporting across all pubs for larger corporations, including the weeklies and any other special sections you print.
Layout-8000 offers a variety of standard tools that report on a single publication, answering questions about which ads wound up on which pages, what the ads-to-editorial ratio is in the B Section, where color ads are placed, etc. These out-of-the-box reports can be printed, e-mailed or exported to a spreadsheet program.
This information is valuable to many departments, from finance to production. Stakeholders from these areas often have questions that can be answered by data from Layout-8000.
But SQLAdBoss is more than this.
It is an ideal tool for those looking for business metrics, key performance indicators (KPI) and trends in big data involving product design.
For example, what happens when a manager from outside of the Layout department wants to compare this year's average page counts to last year's and the year before? The standard solution was always to create a one-off custom report or Excel spreadsheet, but this reactive model of operation only answers the question it's being asked. Wouldn't it be more efficient to be proactive? To be ready to answer questions that haven't been asked yet?
SQLAdBoss helps to facilitate the transition to metrics-based decision making. It provides answers to the questions that are being asked today and is ready to answer questions that will be asked in the future. It allows publishers to easily compare user productivity, do month-over-month or year-over-year comparisons of page counts, ad counts, color details, filler or house ad usage, etc., and it also permits consolidated reporting across all pubs for larger corporations, including the weeklies and any other special sections you print.
AVAILABLE DATA
SQLAdBoss provides useful Layout-8000 information, including the following tables that:
There are also two tables which hold Layout-8000 session statistics; one for session timing by product and another by user. These can be used to report on designer productivity.
The transactional database which Layout-8000 uses for daily operations has hundreds of tables. SQLAdBoss helps simplify reporting by condensing the relevant data into only a few tables. Layout-8000's Final Report might join a dozen tables in order to provide the same information that can be obtained by querying one or two tables in the SQL reporting database.
- count pages per scope (e.g. the B section in today's newspaper has 12 pages)
- accumulate area for ads, news, blocks, fillers, etc. per scope (e.g. in the B section, ads use 452.25 column-inches or 2.43 pages)
- track which page every ad, block, and filler is placed upon (e.g. in today's newspaper, ad #12345 is placed on page ID 4)
- map Layout-8000 page ID's to report-friendly section and page numbers (e.g. in today's newspaper, page ID 4 is actually page B-2)
There are also two tables which hold Layout-8000 session statistics; one for session timing by product and another by user. These can be used to report on designer productivity.
The transactional database which Layout-8000 uses for daily operations has hundreds of tables. SQLAdBoss helps simplify reporting by condensing the relevant data into only a few tables. Layout-8000's Final Report might join a dozen tables in order to provide the same information that can be obtained by querying one or two tables in the SQL reporting database.
FEATURES
SQLAdBoss provides the tools to build and maintain its database. Since every enterprise and business unit's needs are individualized, no bundled or built-in reports are provided with SQLAdBoss. (Community sharing of generally useful report specifications could be provided.)
We have been experimenting with several reporting/charting tools to create sample reports. These range from quite inexpensive (and easy to use) to very expensive (and, ironically, difficult to use). We are also looking at some open-source options. Some sample charts are shown in the right hand column. Call us and ask for our recommendations.
SQLAdBoss is external to the highly redundant system architecture that supports the mission critical design function of Layout-8000. Further, just as you wouldn't use output data from design tools such as QuarkXPress® or Adobe® InDesign® (Creative Cloud®) on audited corporate financial reports, the same holds for data from Layout-8000. None seeks to be Sarbanes-Oxley compliant.
We have been experimenting with several reporting/charting tools to create sample reports. These range from quite inexpensive (and easy to use) to very expensive (and, ironically, difficult to use). We are also looking at some open-source options. Some sample charts are shown in the right hand column. Call us and ask for our recommendations.
SQLAdBoss is external to the highly redundant system architecture that supports the mission critical design function of Layout-8000. Further, just as you wouldn't use output data from design tools such as QuarkXPress® or Adobe® InDesign® (Creative Cloud®) on audited corporate financial reports, the same holds for data from Layout-8000. None seeks to be Sarbanes-Oxley compliant.
PREREQUISITES
The first prerequisite is the current, supported release of Layout-8000 licensed and accessible at each business unit.
The application requires the help of someone familiar with administering an SQL database to initially establish the ODBC connection. Users need skill in using their selected reporting tools.
The application requires the help of someone familiar with administering an SQL database to initially establish the ODBC connection. Users need skill in using their selected reporting tools.
NEXT STEPS
SQLAdBoss subscriptions start at $100 per month per business unit. For example, one SQLAdBoss customer is a large newspaper group that has nine different metro city-sites. Each site produces multiple publications. We say that this customer has nine business units.