InstaLOC. Wechsel der Datenerfassung und Konzepterweiterung

Um die Jahreswende veranlasste ich den angekündigten Wechsel der Datenerfassungstechnologie. Im Zuge dessen fanden konzeptionelle „Brainstormings“ bzgl. einer Abstrahierung sämtlicher Auswertungsansätze statt. Dies bedeutet, dass die Erkenntnisse aus dem Instagramuniversum auf weitere Plattformen und deren offenen Schnittstellen angewendet werden. Beispiele hierfür sind – aktuell – Twitter, Youtube, Pinterest, Tumblr und in bestimmten / eng gefassten Ansätzen Weibo + Facebook.

Interessant(er) geworden sind die Erkenntnisse aus den Zusammenführungen der anonymisierten InstaLOC-Rohdaten mit Openstreetmap. Folgende Datei / Tabelle verdeutlicht den aktuellen Datenbestand auf Basis von ca. 1.4Mio / 2.5 Mio abgeglichenen Geodaten:

Download: Instaloc_ Datenbestand/Statistik (Ergebnisse aus der Prüfung auf eine ca. 60GB umfassende SQLite-DB)

Die Datei beschreibt die Auflistung aller erfassten Länder nach Aufsummierung der codierten User, der Postings und – im experimentellen Status – der Aktiviäten in Form der Aufsummierten Zeitstempel. Hochinteressant ist die Position von Deutschland, was Rückschlüsse auf die Landesrolle im Sinne des Marketings auf der Plattform vermuten lässt.

In den nächsten 2 Wochen werde ich mich tiefergehender mit diversen Datamining- und Datenauswertungsvarianten auseinander setzen und nach Visualisierungsoptionen recherchieren. Die (Teil)fokussierung auf Geodaten erzwingt bspw. die Anbindung an die bekannten Maps-Services (Googlemaps, Openstreetmap etc.).

Gesprächsanfragen bitte an office(at)pontipix.de senden. Freigaben von Informationen und das Zulassen tiefergehender Gespräche werden in einem 4er Team diskutiert.

Projekt InstaLOC, Aktuelle Statistiken

Der aktuelle Datenbestand umfasst:

587.903 Locations inkl. Geocodes, IDs und Namen
12.669.502 Unique Tagclouds
5.633.457 Beiträge ohne Locationzuordnung
8.516.617 Beiträge mit Locationzuordnung
4.863.200 codierte User
10.700.170 Unique Zeitstempel (Beiträge)

Die Masterdatenbank umfasst alle erfassten Datensätze und die Scrapingtechnologie schafft das genannte Volumen innerhalb von 3-4 Werktagen.

InstaLOC: Freigabe der ersten Themendatenbank (Home&Living)

Gestern veranlasste ich die Freigabe der ersten Datenbank, welche den DACH-Raum und Teile der EU nach den Themenwelten der Sparte „Home & Living“ abbildet.

Die Grunddaten der SQLite-Datei sind:

Speicher: ca. 5GB
Beiträge: 8.532.446
User_innen: 2.495.311
Locations: 473.903
Medienzeitstempel: 4.863.183

Interessierte Leser_innen des Arbeitsblogs können sich zu der Datenbank unter den folgenden Auszügen weitergehende Eindrücke verschaffen:

(1) Locationliste + Sum(Postings), unverschlüsselt
https://drive.google.com/open?id=18rVNt2zTfKBmUl606O-L1mMsExvhI4iH
(2) Locationliste + Sum(Likes), unverschlüsselt
https://drive.google.com/open?id=1S3QMkwyo82w3SkH7YhrlZoixZRNlsSiI
(3) Medienpostzeitstempel, unverschlüsselt
https://drive.google.com/open?id=1EtRVSRuFhI5oyE9s6KHBcGKrDF_ARMRv
(4) Tagwolkenliste, unverschlüsselt
https://drive.google.com/open?id=14tXw59I3ky7_bbQ5znG_D4dSGo0rYfkw

Hinweise:
Hinter den CSV-Dateien stehen entsprechende SQL-Statements, die in diesem Blog dokumentiert wurden und dokumentiert werden. Die Datensätze sind unkategorisiert und nicht interpretiert: sie befinden sich quasi in einem Rohdatenzustand.

Supportanfragen, Interpretationsanfragen oder Anfragen zur Strategieableitung bitte an:
Anne Lehmann: office(at)pontipix.de (Wobus & Lehmann GbR), Preisliste unter: https://www.pontimania.de/
Dr. Klaus Holthausen: https://www.linkedin.com/in/dr-klaus-holthausen-1464b043/

Notiz: Diverse SQL-Abfragen für den Komplex „InstaLOC“

(A) Statistiken, Allgemeines
(1) Komplettüberblick
select count(url) as Beiträge, count(distinct(username)) as Nutzer_innen, count(distinct(location)) as Locations, count(distinct(filter)) as Fotofilter, count(distinct(erstellzeit)) as Medienzeitstempel, sum(likes) as Summe_Likes, sum(comments) as Summe_Kommentare from locations;

(2) Überblick nach Locationfilter
select count(url) as Beiträge, count(distinct(username)) as Nutzer_innen, count(distinct(location)) as Locations, count(distinct(filter)) as Fotofilter, count(distinct(erstellzeit)) as Medienzeitstempel, sum(likes) as Summe_Likes, sum(comments) as Summe_Kommentare from locations where location like '%germany%';

(3) Überblick nach Tagfilter
select count(url) as Beiträge, count(distinct(username)) as Nutzer_innen, count(distinct(location)) as Locations, count(distinct(filter)) as Fotofilter, count(distinct(erstellzeit)) as Medienzeitstempel, sum(likes) as Summe_Likes, sum(comments) as Summe_Kommentare from locations where tag like '%hausbau%'

(4) Überblick nach Tag- und Locationfilter
select count(url) as Beiträge, count(distinct(username)) as Nutzer_innen, count(distinct(location)) as Locations, count(distinct(filter)) as Fotofilter, count(distinct(erstellzeit)) as Medienzeitstempel, sum(likes) as Summe_Likes, sum(comments) as Summe_Kommentare from locations where (tag like '%hausbau%') and (location like '%germany%');

(5) Auflistung der Nutzer_innen mit Summe der „produzierten“ Likes
select distinct(username) , Anz_Likes from(
Select username, sum(likes) as Anz_likes from locations GROUP BY username ) as my_table order by Anz_likes DESC;

(6) Auflistung der Nutzer_innen mit Summe der verbundenen Locations, absteigend
select distinct(username) , Anz_Locations from(
Select username, count(location) as Anz_Locations from locations GROUP BY username ) as my_table order by Anz_Locations DESC;

(7) Auflistung der Nutzer_innen+Location zzgl. der Medienanzahl (Summe), absteigend
select username , location , Anz_Locations from(
Select username, location, count(location) as Anz_Locations from locations GROUP BY username ) as my_table where location like '%berlin%' order by Anz_Locations DESC;

(8) Tiefenprüfung auf einen Useraccount+Anzahl-Postings/Locations, absteigend
select username , location , Anzahl_Postings from(
Select username, location, count(location) as Anzahl_Postings from locations GROUP BY username ) as my_table where (location like '%berlin%') and (username = 'share') order by Anzahl_Postings DESC;

(9) Stadtrecherchen aus den Themenweltendatenbanken
select location from locations where (location like '%titude":51.339%') and (location like '%itude":12.377%')
Hinweise:
– Beispiel ist Leipzig
– weitere Städte unter http://www.fwiegleb.de/geo-a.htm

(10) Bereinigte Ausgabe der Tagwolken
select distinct(REPLACE(tag,'%22','"')) as Tagwolke from locations;

(11) Bereinigte Ausgabe der Locations inkl. Aufsummierung der Likes
SELECT distinct(location) as Orte, Anz_Likes from(
Select location, sum(likes) as Anz_likes from locations GROUP BY location ) as my_table order by Anz_Likes DESC;

(12) Bereinigte Ausgabe der Locations inkl. Aufsummierung der Postings
SELECT distinct(location) as Orte, Anz_User from(
Select location, count(username) as Anz_User from locations GROUP BY location ) as my_table order by Anz_User DESC

Hinweise:
– Durch die DB-Struktur werden die Beiträge „username“ zugeordnet und „username“ = „Beitrag“.

(B) Recherchen: Tagwolken
(1) Auflistung aller Tagwolken mit Aufsummierung der Likes, absteigend

SELECT distinct(tag) as Tagwolke, Anz_Likes from(
Select tag, sum(likes) as Anz_likes from locations GROUP BY tag ) as my_table order by Anz_Likes DESC

Hinweise:
(a) Bei Recherche nach den Comments, kann „likes“ zu „comments“ ausgetauscht werden.
(b) Die Abfrage listet alle Tagwolken – ungefiltert – auf und sortiert die Ergebnisse nach der Summe der Likes, welche durch die Tagwolken „produziert“ wurden.

(2) Auflistung der Tagwolken nach Suche mit Aufsummierung der Likes, absteigend

SELECT tag as Tagwolke, Anz_Likes from(
Select tag, sum(likes) as Anz_likes from locations GROUP BY username ) as my_table where tag like '%leipzig%' order by Anz_Likes DESC

Hinweise:
(a) Bei Recherche nach den Comments, kann „likes“ zu „comments“ ausgetauscht werden.
(b) Die Abfrage listet alle Tagwolken – ungefiltert – auf und sortiert die Ergebnisse nach der Summe der Likes, welche durch die Tagwolken „produziert“ wurden.

InstaLOC: Etablierung der Themenwelten-Datenbanken und Datenauszüge aus dem Komplex „Home & Living“

Inhalte der Datenbank: ca. 12.000 * 2000 Beiträge aus „Home&Living“-bezogenen Themenabfragen
Speichergröße der Datenbank: ca. 1.6GB, Format: SQLite

Beispielrohdatensätze
Abfrage via SQLiteStudio
select REPLACE(tag,'%22','"') as Tagwolke, likes, comments, location, erstellzeit from locations where tag like '%[keyword]%'

Datenlinks (CSV)
(1) Tagwolke: Carport.csv (2MB)
(2) Tagwolke: Hausbau.csv (gepackt: 10MB, entpackt: 64MB)
(3) Tagwolke: Haus.csv (gepackt: 27MB, entpackt: 147MB)

CSV-Schema
Tagwolke|Likes|Coments|Location(Geocode+ID+Titel)|Erstellzeitstempel

Allgemeine Datensätze
(1) Liste: Locations.csv (gepackt: 7MB, entpackt: 23MB)

Hinweise
Durch die modifizierte Abfrage wurde die Tagwolke etwas lesbarer gemacht. Man muss bei der Sichtung und Prüfung jedoch die Umlaute und Piktogramme bei Bedarf noch umwandeln, oder eben auf unseren Client zurückgreifen. Die Beispieldatensätze beinhalten nicht die Postingurl, Fotofilter und Usernames und erlauben daher keinerlei Rückschlüsse auf eventuelle Influencer. Diese Angaben werden bei Buchung der kostenpflichtigen Angebote (Workshops, Datenabfragen etc.) offen gelegt und – bei Bedarf – erklärt.

InstaLOC: letzte Bugfixes und Datenbankupdates

Per sofort wurden die Datenbankspalten „likes“ und „comments“ von „VARCHAR“ auf „INTEGER“ gewechselt.

Diese Modifikation erlaubt nun tiefergehende Location-, Themenwelten- und Hashtagbegutachtungen auf Basis der Werthaltigkeitsindikatoren „likes“ und „comments“.

Folgende Queries verdeutlichen das:

(1) Basis-Abfrage 01
select tag, likes,comments, location from locations where (location like '%leipzig%') and (likes > 10) order by likes DESC
Exportdatei: LE_likes (ZIP, CSV)
Erklärung: Die Datei beinhaltet die Daten „Tagwolke“, Anzahl Comments+Likes, die Locations aus der Datenbank, wo der Locationname die Zeichenkette „Leipzig“ trägt. Es werden Ergebnisse ausgespielt, welche mind. 10 Likes beinhalten.

(2) Basis-Abfrage 02
select tag, likes,comments, location from locations where (tag like '%thisis%') and (location like '%leipzig%') and (likes > 10) order by likes DESC
Exportdatei: LE_tags_likes (ZIP, CSV)
Erklärung: Die Datei beinhaltet die Daten „Tagwolke“, Anzahl Comments+Likes, die Locations aus der Datenbank, wo der Locationname die Zeichenkette „Leipzig“ trägt UND wo in der Tagwolke die Zeichenkette „thisis“ vorkommt. Es werden Ergebnisse ausgespielt, welche mind. 10 Likes beinhalten.

(3) Locationbewertung nach Likes / Comments
SELECT location as Ort, Anz_Likes from(
Select location, sum(likes) as Anz_likes from locations GROUP BY location ) as my_table
WHERE (Anz_Likes >= 2) AND (location like '%leipzig%') order by Anz_Likes DESC

Exportdatei: LE_locs_likes (ZIP, CSV)
Erklärung: Die Datei summiert alle vorhandenen Likes zu den Locations mit Zeichenkettenabschnitt „leipzig“ auf und erlaubt einen ersten Überblick dazu, wie „beliebt“ die recherchierten Locations – nach den Instagramdaten – sind. Die Abfrage muss hier – bei Bedarf – auf die Comments umgeschrieben werden. Das ist recht einfach so zu erledigen, dass aus „sum(likes)“ eben „sum(comments)“ geschrieben wird. Ich werde noch prüfen, wie die Aufsummierung der Likes UND Comments in einem Export / Report zu lösen ist.

(4) Tagwolkenbewertung nach Likes / Comments
SELECT tag as Ort, location, Anz_Likes from(
Select tag, location, sum(likes) as Anz_likes from locations GROUP BY tag ) as my_table
WHERE (tag like '%thisis%') order by Anz_Likes DESC

Exportdatei: LE_tagsumlikes (ZIP, CSV)
Erklärung: Die Ausgabedatei beinhaltet die Auflistung aller „unique“ Tagwolken, deren Locations und die Aufsummierung der Likes (optional: Comments).

Die neuen Recherchefunktionen werden zeitnah in den Client übertragen.

Anleitung: Datenbankmodifikation (YT)